On 07/22/2011 08:15 PM, David Fetter wrote:
Do you have any theories as to how indexing on SSD speeds things up? IIRC you found only marginal benefit in putting WALs there. Are there cases that SSD helps more than others when it comes to indexing?
Yes, I've found a variety of workloads where using a SSD turns out to be slower than the old-school array of drives with a battery-backed write cache. Tiny commits are slower, sequential writes can easily be slower, and if there isn't a random I/O component to the job the SSD won't get any way to make up for that.
In the standard pgbench case, the heavy UPDATE traffic does a lot of random writes to the index blocks of the pgbench_accounts table. Even in cases where the whole index fits into RAM, having the indexes backed by a faster store can end up speeding those up, particularly at checkpoint time. And if you can't quite fit the whole index in RAM, but it does fit on the SSD, being able to shuffle it in/out of flash as needed to look pointers to data blocks is a whole lot better than seeking around a regular drive. That case is where the biggest win seems to be at.
I'd like to publish some hard numbers on all this, but have realized I need to relocate just the pgbench indexes to do a good simulation. And I'm getting tired of doing that manually. If I'm going to put time into testing this unlogged table variation that Robert has submitted, and I expect to, I'm just pointing out I'd like to that the "index on alternate tablespace" one available then too.
-- Greg Smith 2ndQuadrant US g...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
