Re: prelimiary performance comparison pgsql vs mysql

Richard Huxton <dev(at)archonet(dot)com> writes:
> Rick Schumeyer wrote:
>> Below are some PRELIMINARY results in comparing the performance of pgsql and
>> mysql.

> Take 30 minutes to read through the article below. It covers the basics
> of how to manage your configuration settings.
> http://www.powerpostgresql.com/PerfList

I have been fooling with the sql-bench stuff that MySQL ships with their
database. Not because I take it seriously ;-) but because I thought it
would be useful to understand in detail why we look so spectacularly bad
on it. I'll write a more complete report when I'm done, but what's
relevant to Rick's testing is that I have found that a few simple
configuration adjustments make a huge difference. Specifically,
I've got

shared_buffers = 10000 # 10x the default
checkpoint_segments = 30 # 10x the default
work_mem = 100000 # ~100x the default
maintenance_work_mem = 100000 # ~6x the default

(The *work_mem numbers are probably excessive but I've not bothered to
fine-tune them.) A stock out-of-the-box PG 8.0.1 RPM is about 10x slower
overall than MySQL according to this benchmark, but these adjustments
bring it to something like 2x slower. Which is at least in the ballpark.

Most of the tables that this benchmark uses have about 300K
not-super-wide rows, so what this says is that you need numbers in this
vicinity to work on tables of that size.

Bottom line is that you *must* adjust at least these settings if you
want a high-performance PG server.

regards, tom lane