Re: Postgres benchmarking with pgbench

On Thu, Mar 19, 2009 at 3:25 PM, ml(at)bortal(dot)de <ml(at)bortal(dot)de> wrote:
> Hi Greg,
>
> thanks a lot for your hints. I changed my config and changed raid6 to
> raid10, but whatever i do, the benchmark breaks down at a scaling factor 75
> where the database is "only" 1126MB big.
>
> Here are my benchmark Results (scaling factor, DB size in MB, TPS) using:
>  pgbench -S -c  X  -t 1000 -U pgsql -d benchmark -h MYHOST

-t 1000 is WAY too short to judge, you'll be seeing a lot of caching
effects and no WAL flushing. Try a setting that gets you a run of at
least 5 or 10 minutes, preferably a half an hour for more realistic
results. Also what is -c X ??? Are you following the -c with the
same scaling factor that you used to create the test db? And why the
select only (-S)???

> 1 19 8600
> 5 79 8743
> 10 154 8774
> 20 303 8479
> 30 453 8775
> 40 602 8093
> 50 752 6334
> 75 1126 3881
> 150 2247 2297
> 200 2994 701
> 250 3742 656
> 300 4489 596
> 400 5984 552
> 500 7479 513
>
> I have no idea if this is any good for a QuardCore Intel(R) Xeon(R) CPU
>  E5320  @ 1.86GHz with 4GB Ram and 6 SATA disk (7200rpm) in raid 10.
>
> Here is my config (maybe with some odd setting):
> http://pastebin.com/m5d7f5717
>
> I played around with:
> - max_connections
> - shared_buffers

You've got this set to 1/2 your memory (2G). I've found that for
transactional work it's almost always better to set this much lower
and let the OS do the caching, especially once your db is too big to
fit in memory. Try setting lowering it and see what happens to your
performance envelope.

> - work_mem
> - maintenance_work_mem
> - checkpoint_segments
> - effective_cache_size

This is set to 3G, but with shared_mem set to 2G, you can't cache more
than 2G, because the OS will just be caching the same stuff as pgsql,
or less. No biggie. Just not realistic

> ..but whatever i do, the graph looks the same. Any hints or tips what my
> config should look like? Or are these results even okay? Maybe i am driving
> myself crazy for nothing?

Could be. What do top and vmstat say during your test run?