Thread: Performance evaluation of PostgreSQL's historic releases

Hello,

I've done a benchmark of recent versions of PostgreSQL's last five major releases to see, how performance has changed during the past years from version to version.
You can find the article here:
http://suckit.blog.hu/2009/09/26/postgresql_history

Thanks for working on this great piece of software!

--
http://suckit.blog.hu/

any chance you can test the recent postgresql Cvs-head build (to be 8.5).

Hello

Thanks for the benchmark. Did you configure FreeBSD kernel parameters too, or used the default values?

With regards

Amitabh

On Tue, 29 Sep 2009, Gy?rgy Vilmos wrote:

> I've done a benchmark of recent versions of PostgreSQL's last five major
> releases to see, how performance has changed during the past years from
> version to version.

Your comments suggest V8.4 moves backwards as far as performance goes,
which is a bit misleading.  A more fair characterization would be to
disclaim 8.4 as potentially being slower on the very simple benchmarks you
ran, not necessarily in general.

What actually happened is some features were retuned to give better
results on difficult queries (increasing default_statistics_target is the
main example there), and one of the major maintenance tasks was removed
(adjusting the max_fsm_* parameters).  These and the other 8.4 changes
that touched performance added a small amount of overhead for simple
queries, but in the situations where they help the gain can be big.

Had you instead benchmarked a complicated query where the statistics
change caused the default behavior to provide better query plans, or you
had a deletion-heavy workload where 8.3 had trouble maintaining database
free space, you could have seen significantly better performance on 8.4.
The improvements in that version just don't help trivial examples like the
sysbench ones you ran.

P.S. On your write-heavy tests, increasing checkpoint_segments a lot
should improve overall performance, if you re-test at some point.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

On Tue, Sep 29, 2009 at 4:47 PM, Greg Smith <gsmith@gregsmith.com> wrote:
> P.S. On your write-heavy tests, increasing checkpoint_segments a lot should
> improve overall performance, if you re-test at some point.

Just wanted to add that in order to really test a db, you need a
benchmark that runs a lot longer than a few minutes.  I routinely use
tests that run for hours to get an idea how things like write ahead
logging and such affect the db under heavy, long term load.

kern.ipc.shmall=393216
kern.ipc.shmmax=1610612736
kern.ipc.semmap=256
kern.ipc.shm_use_phys=1
kern.smp.topology="8"
kern.timecounter.smp_tsc=1
kern.timecounter.hardware="TSC"

smp.topology was needed because the current kernel doesn't recognize the correct cache topology on this machine.

--
http://suckit.blog.hu/

Sadly, no, the machine was not mine and I had to give it back.


--
http://suckit.blog.hu/

Thank you very much for the valuable comments, I will keep them in mind for the next test.

BTW, this wasn't a "how could I get the maximum out of PostgreSQL", that would need much more time and research (or inner knowledge about the program).

--
http://suckit.blog.hu/

Agreed, but even with 5x1 minutes of test runs for each threads it took a week to do all the tests.

I've once heard that a company have spent three man years for performance testing one of its products...

--
http://suckit.blog.hu/

On Tue, 2009-09-29 at 09:36 +0200, György Vilmos wrote:

> I've done a benchmark of recent versions of PostgreSQL's last five
> major releases to see, how performance has changed during the past
> years from version to version.
> You can find the article here:
> http://suckit.blog.hu/2009/09/26/postgresql_history
>
> Thanks for working on this great piece of software!

Thanks for doing the benchmarks. I'd been meaning to write up something
about performance increases over that period from a development
perspective. It's good to see some numbers around that.

Your graphs tail off steeply as # threads increases. I guess they would
on a logarithmic graph, though I would guess that has more to do with
using 24 cores and contention than with a true limitation of capacity.

Do the FreeBSD folk got Dtrace working yet in userspace? Maybe we can
examine the contention. Not right now though, fairly busy.

8.4 numbers seem about right, though the #threads at peak seems slightly
off. I think you should look at the point where performance drops down
to 95% or less of peak, which would give a more stable and comparable
figure than just looking at a single peak value.

--
 Simon Riggs           www.2ndQuadrant.com