Entries by Stefan Kaltenbrunner

As promised in the previous post this is the second part in a series of testing/benchmarking 8.4 under various circumstances.
The topic in this post is bulk loading of data. Knowing about expected and theoretical bulk loading performance is a very important thing for an DBA. It affects not only data warehouse style operations but also plays an important part in disaster recovery scenarios because the total time it takes to restore your database from your backup is directly related to its bulk loading performance.

Computing platforms are constantly changing, evolving and improving. This holds true for both the hardware and the software running on that hardware.
With PostgreSQL 8.4 just around the corner and a 2U Nehalem based IBM x3650M2 at my hands I thought I would do some benchmarking and see how well PostgreSQL does on that kind of hardware under a range of different workloads. This is planned as a series of posts starting with read-only benchmarking, followed up by bulk load testing and maybe some OLTP benchmarks as well.

Long time no post - but I just got back home from the Netways Nagios Conference 2008 where I was invited to give a talk on "PostgreSQL Monitoring - Introduction, Internals And Monitoring Strategies for postgresql.org" so I figured it is time to say something again ...

FOSDEM was a really nice and successful event for Opensource in general and especially PostgreSQL - we had a really impressive booth and a huge number of talks with a lot of attendances.
We also managed to get the initial board for the european user group voted and that will pave to wya to get the group set up as a legal entity within the next few months - YEAH!

this feels strange - my sister just told me that she is expecting a baby and that I'm about to become uncle which is feeling really strange.
However I'm really really happy for Andrea and Harry (see my brothers blog for a picture of them) and I wish them the best.
It now looks that Harry will have to take on the burden of doing all the hard work on their new apartment - but I guess thats ok :-)

PostgreSQL is a thriving and fast evolving project with regular releases that add new features, improve existing functionality and increase speed.
Most of those improvements are mentioned in a rather brief from in the release announcement/press release and or in a lengthy form in the release notes.
The new feature matrix on the main website is an attempt to provide a quick tabular overview about "what is new in release XY" starting from 7.3 to the about to be released 8.3.

With 8.3 just around the corner more and more people are actually starting to test 8.3 with their code base and wondering if it will be worth to switch/upgrade and so did I.
The following is not really an in-depth benchmark but meant as a simple testing of 8.3 on a very specific (but not uncommon) workload and with small set of different configuration parameters.

All the testing is done on a DL380 G5 with two Quadcore Intel Xeon CPUs (X5345 - so 2,33Ghz per core) 12GB of RAM and 6 disks in a RAID1+0 with 512MB BBWC on a Smartarray P400.
The box is running Debian/Etch AMD/64 with the debian supplied kernel (2.6.18 more or less) and a current -HEAD snapshot of PostgreSQL (more or less BETA4 code) compiled as a 64bit binary.

The benchmark database is initialized with a scaling factor of 100 (equals to 10M rows) which seems to be a reasonable size for a table in an OLTP style database) all testing was done with 100 clients and 100000 transactions/client which comes out to 10M transactions and an average runtime of about 1,5 hours.
The first test is a simple comparison of 8.2 vs 8.3B4 in both the default (ie. unchanged postgresql.conf) and one with a somewhat reasonable tuning:

• checkpoint_segments=192
• maintenance_work_mem=128MB
• shared_buffers=1536MB
• wal_buffers=1024kB
• effective_cache_size=3084MB
• filesystem(ext3) mounted with noatime

83b4shm.gif

what we can see here is that 8.3B4 is about 2.2x faster than 8.2 out-of-the-box for this very update heavy workload and is able to keep that advantage after similar tuning is applied to both instances.
The main reason for this rather large improvement is HOT. pgbench is one of the workloads that benefit most from it and a bit of preliminary testing with similiar real-life workloads (session tables or stuff like the SQL-Bayes support in Spamassassin) show equally impressive gains.

the second thing I tried to test was the effect of various shared_buffer settings on the transaction rate(the other parts of the configuration stayed the same as in the "tuned" variant above):

there are two main things to note here - one is the obvious one that the scaling on the x-axis is not linear.
The other one is that the best performance seems to be around 1,5GB of shared memory which is about the size of the database on-disk in 8.3. Higher settings to not help and in fact cause a slight performance degeneration.

All in all 8.3 seems to become a rather impressive release and as always proper tuning is very critical to getting the best out of (dedicated) database server instances.

Joshua Drake just announced that the first machine is finally available for a PostgreSQL Performance Test lab donated by a range of commercial sponsors.
Ever since Andrew Dunstan invented the buildfarm and seeing the smashing success it had on improving the PostgreSQL development process I have been toying with the idea of doing a similar thing on the performance front. What the past few releases have shown is that PostgreSQL is gaining more and more complex performance related features and planner improvements.
However some of this things change fundamental infrastructure in PostgreSQL (for example the outer join reordering stuff in 8.2) and while often causing dramatic improvements on the one side we also a fair number of regressions which largely went undetected during BETA and even the first few minor releases.
The current buildfarm setup is very good in covering a large range of platforms and architectures for testing portability and basic regression testing but due to its nature is not really suitable for benchmarking of any kind.

Some of the reasons why it is not so well suited for that purpose are:

• a lot of the buildfarm clients are not really dedicated installations (or just VMs)
• capabilities/resources on some of the boxes are quite restricted (lionfish for example has only a few hundred megabytes of disk space and 48MB of RAM available which is barely enough to get through the regression tests ...)
• limited control over the change management on those boxes (updates/upgrades) which might make it difficult to compare historic results

What I would like to see in the future is a threefold approach:

• We need continuous and long term benchmarking of at least -HEAD using defined benchmark tools (sysbench, marc wongs dbt implementations or Jans tpc-w stuff) done at least weekly if not daily so we can get an idea on how our overall performance improves (or degrades) on various workloads on a relative scale.
• Tracking of query plans - the current regression test framework is designed to give very deterministic results and therefor uses a lot of ORDER BY and similar stuff get get consistent results. This however tends to hide subtle regressions in the planner for large and complex queries and on large databases (the typical regression database is very very small compared to what people use in the wild). What we need is a collection of real life test cases that load data and execute queries on them comparing the plans AND execution times over a long period of time on small and large datasets and notify on changes.
• Dedicated boxes for individual hackers to work on new features or experimental patches

pgday.it in Prato was a truly marvelous conference, but everything is eventually coming to an end and so did this conference. On sunday Gabriele and Andrea took us one a tour through Florence ending in a great dinner and a bit of a challenge to catch the last train^bus back to prato ...

Today we started at around 09:30 in the morning trying to get Magnus to the airport which turned into quite a long trip mostly due to the fact that andreas navigation system developed something that can only be described as "artificial (un)intelligence".

After we left the airport we thought we were good for going back up to Salzburg (and Munch) but the navigation system acted up again and caused so much confusion that we actually managed to get up the highway WITHOUT(!) getting a ticket from the toll station.
So just imagine what it might take to get off a highway with a fully automated toll collection system without a ticket and neither the remote assistance nor the one lone guy that was responsible for some 18+ exit gateways and not even they guy in the main office is capable of understanding a single word of german or english ...

After a somewhat interesting discussion which mainly consisted of well wild gestures and "i have no idea what you are talking about" faces we managed to get a form that probably claims (again all Italian) that we turned around on the highway or something like that - but well it seems we don't have to pay anything for "hacking" the italian highway system.

On the way back to the car from the autostrada office we met the guy manning the 18+ toll houses again telling us that there is a completely unlabeled underground crossing to the other side of the highway which would have been a good thing to to know earlier for well "making crossing a highway "easier".

So well - the official part of pgday.it is over now and today we had a lot of interesting talks from a number of international people covering everything from encoding(Susanne) related stuff over to full text search(Oleg) and PITR(Simon).
Josh did his keynote on new features in 8.3 which is something I'm a "bit" familiar with - but nevertheless it contained quite a lot of information especially the announcement of the official benchmarks sun did was very impressive and clearly how powerful and also cost effective PostgreSQL really is.
Again we got ton's of food and well there was also this guy doing interviews for a satellite TV station (boy do I hope that mine will not get broadcasted ...).
We will soon meet up at the bar of the prato linux usergroup for doing a bit of brainstorming and discussions on a possible european postgresql usergroup and afterwards we are going to have beer, grappa, wine and lot's and lot's of fun with our new Italian friends.