So, today, I did the following: - Swapped out the 5410's (2.3Ghz) for 5470's (3.33Ghz) - Set the ext4 mount options to be noatime,barrier=0,data=writeback - Installed PG 9.1 from the yum repo
Item one: With the accelerator cache set to 0/100 (all 512MB for writing), loading the db / creating the indexes was about 8 minutes faster. Was hoping for more, but didn't get it. If I split the CREATE INDEXes into separate psql instances, will that be done in parallel? Item two: I'm still getting VERY strange results in my SELECT queries. For example, on the new server: http://explain.depesz.com/s/qji - This takes 307ms, all the time. Doesn't matter if it's "cached", or fresh from a reboot. Same query on the live / old server: http://explain.depesz.com/s/8Pd - This can take 2-3s the first time, but then takes 42ms once it's cached. Both of these servers have the same indexes, and almost identical data. However, the old server is doing some different planning than the new server. What did I switch (or should I unswitch)? -- Anthony On Sun, Sep 11, 2011 at 9:12 PM, Alan Hodgson <ahodg...@simkin.ca> wrote: > On September 11, 2011 03:44:34 PM Anthony Presley wrote: > > First thing I noticed is that it takes the same amount of time to load > the > > db (about 40 minutes) on the new hardware as the old hardware. I was > > really hoping with the faster, additional drives and a hardware RAID > > controller, that this would be faster. The database is only about 9GB > > with pg_dump (about 28GB with indexes). > > Loading the DB is going to be CPU-bound (on a single) core, unless your > disks > really suck, which they don't. Most of the time will be spent building > indexes. > > I don't know offhand why the queries are slower, though, unless you're not > getting as much cached before testing as on the older box. > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance
