Performance evaluation of PostgreSQL's historic releases

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

On Tue, Sep 29, 2009 at 1:06 PM, György Vilmos <vilmos.gyorgy@gmail.com>wrote:

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/&gt;

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.

2009/9/29 Grzegorz Jaśkiewicz <gryzman@gmail.com>

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

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

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

2009/9/29 Amitabh Kant <amitabhkant@gmail.com>

On Tue, Sep 29, 2009 at 1:06 PM, György Vilmos <vilmos.gyorgy@gmail.com>wrote:

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/&gt;

Hello

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

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/

2009/9/30 Greg Smith <gsmith@gregsmith.com>

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.

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/

2009/9/30 Scott Marlowe <scott.marlowe@gmail.com>

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.

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