Re: write scalability

On Mon, Jul 25, 2011 at 10:14 PM, Greg Smith <greg(at)2ndquadrant(dot)com> wrote:
> On 07/25/2011 04:07 PM, Robert Haas wrote:
>>
>> I did 5-minute pgbench runs with unlogged tables and with permanent
>> tables, restarting the database server and reinitializing the tables
>> between each run.
>
> Database scale?  One or multiple pgbench worker threads?  A reminder on the
> amount of RAM in the server would be helpful for interpreting the results
> too.

Ah, sorry. scale = 100, so small. pgbench invocation is:

pgbench -T $time -c $clients -j $clients

128GB RAM.

> The other thing I'd recommend if you're running more write-heavy tests is to
> turn off autovacuum.  Whether or not it kicks in depends on the test
> duration and the TPS rate, which adds a source of variability better avoided
> here. It also means that faster tests end up getting penalized by having it
> run near their end, which makes them no longer look like fast results.

Well, I kind of don't want to do that, because surely you wouldn't run
that way in production. Anyway, it seems clear that nearly all the
contention is in CLogControlLock, ProcArrayLock, and WALInsertLock,
and I don't think shutting off autovacuum is going to change that.
The point of my benchmarking here may be a bit different from the
benchmarking you normally do: I'm not trying to figure out whether
I've got the best hardware, or how good it is, so I don't really care
about squeezing out every last tps. I just need number that can be
reproduced quickly with different patches to see whether a given code
change helps and by how much. I wasn't sure that 5-minute pgbench
runs would be good enough for that, but so far the results have been
stable enough that I'm fairly confident I'm seeing real effects rather
than random noise.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company