No, not that history which tells about how Ingres became PostgreSQL, but the one, which tells us how much faster got the RDBMS, which is widely known as the slower one.

PostgreSQL doesn't need any introduction because the two camps, which have already met with it has a solid point of view about its righteousness. One of them think that PostgreSQL is slow and because MySQL also has stored procedures (never used them) it already knows everything, which PostgreSQL does. The other thinks that MySQL is dumb and with every new feature it's getting slower. Of course PostgreSQL is also getting a tons of new features every release, so according to this theory it should have degrading performance over the time as well.

This is what I want to go after by testing the last five releases of each RDBMS's on a static hardware and software environment.

The static hardware and software environment in our case is a 24 core (six core, 2.4 GHz Intel E7450 CPUs with 3x3 MiB L2 and 12 MiB L3 cache), 128 GiB RAM peesee server and a FreeBSD 8/amd64 operating system running on it.

The "Dunnington" code named CPU's architecture and the number of CPUs are both an important factor for the OS and the database, because in this CPU a lot of cores are fighting for the spare bandwidth of the FSB, through which the slow memory and peripheral devices are reached. The multi level cache hierarchy and the slow FSB makes a hard job for the OS scheduler and memory allocator, which need to keep the CPUs busy by feeding them the most data from the caches.

This is a hard jobs and FreeBSD isn't yet at the end of the road (to be honest, it just stepped on it), but here it's not that important, because we were curious about what database version produce how much performance. Sadly there weren't too much time and resources for tuning the OS and the databases.

FreeBSD was chosen -along the fact that I like it- because from its ports it's easy to install various PostgreSQL (and MySQL) versions. The test itself was made with sysbench 0.4.12, and it'S OLTP benchmark on a 1 million row database, which of course fit into the machine's RAM.
The data was on two 15kRPM SAS HDDs (RAID1), which is far from a heavy loaded database's storage background, but approximately shows a typical web server environment's characteristics, where you have to serve from the built in disks.

Each of the tests ran for one minute, five times, then we choose one result from the collected data, with a 95% confidence level.

Let's see the results:

At the right hand of the graph, you can see the color codes of the used PostgreSQL versions:: 8.0.21, 8.1.17, 8.2.13, 8.3.7 and 8.4.1.

Sysbench accessed the database server on localhost, via a UNIX socket, therefore it ran on the same machine. It can be important that sysbench used the same version of the PostgreSQL client library as the server, so the 8.1.17 server was tested with a version 8.1.17 libpq.

The above graph shows the results of sysbench's OLTP read only test. One example for a typical command line (this is read write though):

/usr/local/bin/sysbench --test=oltp --db-driver=pgsql --pgsql-host=/tmp --oltp-read-only=off --oltp-test-mode=complex --oltp-table-size=1000000 --num-threads=2 --max-time=60 --max-requests=0 run

If you see the graph, you can clearly see that PostgreSQL is not slowing, instead it's getting more performing with every releases.

It could be important to take a look at each release's date:

The differences between the releases can be seen in the "Release Notes", or in the "Feature Matrix".

As you can see, PostgreSQL 8.0 while starts performing great on on thread, loses strength at as low as two threads, and after fourinstead of getting more performance, declines sharply. Practically the database can't scale beyond two cores (CPUs).

8.1 otherwise shows a much nicer picture, the decline comes at 14 threads instead of four, where from it decreases until 32 threads, after which it can keep its performance, well more or less, until 1024 threads.

The 8.2 means the next step, it's peak performance tops at 18 threads, but also starts to loose performance after that.

A massive speedup comes with version 8.3, which almost pushes the maximum performance (about 14000 TPS) to the number of cores in the server (24), it does its best at 22 threads. Greater absolute performance and better scalability.

Sadly 8.4 is a step backwards -at least performance wise-, it could only beat 8.3 above 256 threads, otherwise it lost the battle.

The above results in a tabular format:

PostgreSQL therefore has shown us that it can read pretty much fast. Let's see what happens when we also want to write!

Results of the sysbench RW tests:

Of course the TPS numbers are a lot more smaller, because here the disks have had to move data. The proportions remained the same between the versions, which is quite a good news, because it means PostgreSQL had got faster during the release of the last five releases (except 8.4), and 8.3 produced an unbelievable more than 100% speed increase!

The same in columns:

The picture here is not so clear, because 8.3 got farther on from the ideal 24 thread performance maximum and 8.4 has lost not just the performance, but the scalability game too. Otherwise the difference is just 3 percents and this sligthly lower performance maximum has come at 12 client threads, which means during the 8.4 lifecycle it's quite possible that it will beat its predecessor.

To conclude the above, I think it's safe to say that if we have known PostgreSQL as a slow beast, it's time to re-think (or measure) that, because it has gained quite much performance and scalability in the last three years. Not to speak about its features.

Update: postgresql.conf

datestyle = 'iso, mdy'
default_text_search_config = 'pg_catalog.english'
lc_messages = 'C'                       # locale for system error message
lc_monetary = 'C'                       # locale for monetary formatting
lc_numeric = 'C'                        # locale for number formatting
lc_time = 'C'                           # locale for time formatting
listen_addresses = ''           # what IP address(es) to listen on;
log_destination = 'syslog'
maintenance_work_mem = 64MB
max_connections = 1064                  # (change requires restart)
shared_buffers = 1024MB
silent_mode = on
unix_socket_directory = '/tmp'          # (change requires restart)
unix_socket_group = 'pgsql'                     # (change requires restart)
unix_socket_permissions = 0777          # begin with 0 to use octal notation
update_process_title = off
work_mem = 16MB

A bejegyzés trackback címe:

Kommentek:

A hozzászólások a vonatkozó jogszabályok  értelmében felhasználói tartalomnak minősülnek, értük a szolgáltatás technikai  üzemeltetője semmilyen felelősséget nem vállal, azokat nem ellenőrzi. Kifogás esetén forduljon a blog szerkesztőjéhez. Részletek a  Felhasználási feltételekben és az adatvédelmi tájékoztatóban.