Re: pgbench --unlogged-tables

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 greg(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us