8.3 vs. 8.2 - a simple benchmark

mastermind's weblog

Tuesday, December 11. 2007

8.3 vs. 8.2 - a simple benchmark

PostgreSQL

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

82vs83b4.gif83b4shm.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):

83b4shm.gif

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.

Posted by Stefan Kaltenbrunner in PostgreSQL, work at 22:33 | Comments (28) | Trackbacks (3)

Trackbacks
Trackback specific URI for this entry

PostgreSQL 8.3 is just around the Corner
PostgreSQL 8.3 is currently in Beta 4 and promises to offer some whoppingly neat features. First before we go over the new features we are excited about in this upcoming release, we'd like to briefly cover what was added in past releases. The big 8.
Weblog: Postgres OnLine Journal
Tracked: Dec 12, 10:33
feature matrix - or "what is new in version XY?"
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 an
Weblog: mastermind's weblog
Tracked: Jan 31, 22:33
PostgreSQL
DCM4CHEE will work with PostgreSQL 8.x. You may download the database software from from
Weblog: Confluence: dcm4chee-2.x
Tracked: Oct 09, 06:15

Comments
Display comments as (Linear | Threaded)

can you tell what parameters that you change in the default config to get the tuned version?
#1 mark on 2007-12-11 23:53 (Reply)
All the parameters changed are already described in the article--checkpoint_segments, maintenance_work_mem, etc. pgbench results aren't actually impacted by some of these: maintenance_work_mem and effective_cache_size have no real impact on the TPS results for example, but it's good to get into the habit of setting them correctly anyway because they matter for real applications. All the other parameters are really critical to getting good results from pgbench or any update-intensive workload.
#1.1 Greg Smith on 2007-12-12 04:39 (Reply)
thanks for the reply.. so what about for a database in which is mostly going to have inserts.. should i still have these same parameters?
#1.1.1 mark on 2007-12-12 07:13 (Reply)
Doing inserts instead of updates doesn't need checkpoint_segments and wal_buffers to be set quite as high, but there's not a lot of difference between the overall server tuning. Generally you might as well tune for reasonable update performance and inserts will work fine as a side-effect of that.
#1.1.1.1 Greg Smith on 2007-12-12 22:35 (Reply)
i think this is nice but there is one thing i found a little bit difficult with postgresql - its articles about it! you can find a lot of material on mysql, and quite a lot on sqlite... on postgresql there are also tutorials but you often have the feeling that postgre is a lot BIGGER and there is only a small fraction taught to you
#2 she on 2007-12-12 03:48 (Reply)
I have to disagree with you on this. The postgres documentation is excellant and whilst there might not be as many articles/blog posts written about postgres they are generally well written and I've never really had any problem finding out information I need. "... that postgre is a lot BIGGER and there is only a small fraction taught to you". I think this is an illusion, there is also a lot to mysql if you dig deeply enough. Getting postgres up and running is as trivial as mysql quite possibly more so. Tuning it takes a bit more effort but tuning mysql is not a trivial affair. I would say give it a go you've got nothing to lose and a great deal to gain!
#2.1 rgh (Homepage) on 2007-12-12 04:07 (Reply)
The PostgreSQL manual is 1692 pages of great information as of the current version. If you're new to the system, there's very little you need to know that's not already in there. The biggest problem with the documentation for PostgreSQL is actually that there's too much of it, and as such there's little motivation for other people to duplicate in shorter forms like articles or blogs.
#2.2 Greg Smith on 2007-12-12 04:33 (Reply)
I agree to some extent with what She is saying. The thing about PostgreSQL is that there are so many interesting side projects going on. To me the side projects is what makes PostgreSQL stand out from the pack. Things like TSearch, PostGIS, PL/R and other PLs to name a few. When you think about it - as big as the manual is - it barely touches these or doesn't even cover them at all nor do I think it can without really scaring people away. We definitely need to encourage more bloggers to join. The number of MySQL bloggers seriously outways the number of PostgreSQL bloggers.
#2.2.1 Regina (Homepage) on 2007-12-12 10:17 (Reply)
By the way...even on a server of this class, the pgbench client itself can be a limiting factor with 100 clients once you're in the multi-thousand TPS area. Since it's a single process it tends to hog a single CPU and how it services all the clients is not efficient. Doesn't change your 8.2 vs. 8.3 comparison, but one day you might want to run that with the pgbench client on another system to see if you can squeeze even higher TPS numbers.
#3 Greg Smith on 2007-12-12 04:28 (Reply)
*hrm* I should have noticed that in the article but I also did some tests using an external box(same hardware) to generate the load without a real difference (within error margin of the usual pgbench results) in the results.
#3.1 Stefan Kaltenbrunner (Homepage) on 2007-12-12 09:13 (Reply)
Just what i'v come to expect from every major postgres release these days, something big every time. I would be curious to see other factors affecting the server at this time myself, though there was enough ram for everything to be cached I would still be interested in the disk load etc to get some idea of if anything has changed in that area...
#4 Trophaeum (Homepage) on 2007-12-12 05:37 (Reply)
Stefan, which benchmark you used for the first comparison of 8.2 vs. 8.3? It is not clear whether you use pgbench for all of your tests on this page. Best regards, Hakan
#5 Hakan Kuecuekyilmaz (Homepage) on 2007-12-12 12:58 (Reply)
yeah all the testing was done with pgbench and the same pgbench settings
#5.1 Stefan Kaltenbrunner (Homepage) on 2007-12-12 16:31 (Reply)
The thing that makes all these comments irrelevant is that when I write to the pgsql mailing lists, I get a response--and usually a VERY informed one--within an hour. Often within 10 minutes. Try getting that response time from the MySQL community on their forums online. You're lucky if your thread gets answered in that huge labyrinth of forums at all. As for the documentation, the website has a great deal of stuff, and whatever isn't there, just search the email archives (many such archives exist), and if that's a pain too, just drop a note to the pgsql list. You'll have an answer or excellent pointers more quickly than you imagine. I'm a recent convert to PgSQL (8.2.3) and while it took me a month to really figure things out and tune my database, I am simply amazed at the way things are structured, at the performance, and at the resilience/functionality offered by this stupendous db. (Having been a devout MySQL fan for over six years now). There's a reason a product creates passionate zealots.
#6 Shanx (Homepage) on 2007-12-12 15:45 (Reply)
Postgresql 8.3 has also another big perfomance improvement - delayed wal logs. Did switch it on while testing?
#7 Eratothene on 2007-12-12 17:20 (Reply)
HOT probably is responsible for most of this gain, though there are also many other tuning features in 8.3. Before HOT pgbench would slow down over time, so you can really decide how big a gain you want to show and then run tests for long enough until you get it. I regard HOT more as an architectural change that provides consistent performance for Updates.
#8 Simon Riggs (Homepage) on 2007-12-12 17:23 (Reply)
Please, please, please! Try this test one again with new options in posgresql.conf: synchronous_commit = off I am eager to see the results of this new feature. It is described here http://www.postgresql.org/docs/8.3/static/wal-async-commit.html Thank you in advance!
#9 Eratothene on 2007-12-12 17:48 (Reply)
IIUC, this only reduces COMMIT latency. At high concurrency, it won't boost throughput much.
#9.1 b1ff on 2007-12-13 09:38 (Reply)
Async commit doesn't give a serious improvement when you have this type of hardware. The 512MB battery-backed write cache in Stefan's test server will give nearly instant commits already. The real case where async commit helps is where you don't have such a caching controller, like when you're using a typical PC without a serious RAID controller.
#9.2 Greg Smith on 2007-12-13 23:58 (Reply)
that's true - some testing here shows that the impact of async commits (or even fsync=off) is not giving a further speedup on this kind of hardware.
#9.2.1 Stefan Kaltenbrunner (Homepage) on 2007-12-14 08:42 (Reply)
What disks are you using in this server. The SAS or SATA drives. Were the WAL logs on the same RAID as the DB? Thanks.
#10 Jon on 2007-12-17 02:01 (Reply)
this testing was done with six 146GB 10k SAS drives, data and WAL were on the same partition (some earlier testing I did showed no noticeable benefit to split them with this kind of disk/controller combination)
#10.1 Stefan Kaltenbrunner (Homepage) on 2007-12-17 08:39 (Reply)
Hi, I performed a similar test on an almost identical hardware (DL580G5, 8G Ram, same disks/controller, 2x2.4GHz quad Xeons). The test was run on centos5 / amd64 + postgresql 8.3 form the official postgresql yum repository. However, I get more or less around 2k tps. the relevant postgresql variables are: max_connections = 100 shared_buffers = 2GB temp_buffers = 128MB work_mem = 8MB maintenance_work_mem = 128MB max_fsm_pages = 1000000 bgwriter_delay = 200ms fsync = on synchronous_commit = on wal_sync_method = fdatasync wal_buffers = 1024kB commit_delay = 100 checkpoint_segments = 128 effective_cache_size = 5GB constraint_exclusion = on Any advice? Thanks, e.
#11 Enrico Sirola on 2008-03-12 15:13 (Reply)
hmm well - what scaling factor are you using during your tests? it is important to note that my testcase (scaling factor 100 which is ~1,4GB) easily fits RAM and shared_buffers. If you used a larger or a way smaller scalling setting results could differ.
#11.1 Stefan Kaltenbrunner (Homepage) on 2008-03-13 07:54 (Reply)
Is anyone else able to replicate this? I am just not seeing that performance gain from increasing shared_buffers.
#12 Kris on 2008-03-16 20:45 (Reply)
this should be fairly easy to replicate - what workload are you using and how large is the working set ?
#12.1 Stefan Kaltenbrunner (Homepage) on 2008-03-16 21:08 (Reply)
I'm trying exactly your workload as you described it, on similar hardware.
#12.1.1 Kris on 2008-03-16 21:45 (Reply)
Weird - but a common pitfall is what happened to Enrico in comment #11. one needs to be very carefully with pgbench to not miss that one has to use -i -s during initialization of the test database (not runtime) otherwise one will end up with a scaling factor of 1.
#12.1.1.1 Stefan Kaltenbrunner (Homepage) on 2008-03-16 21:50 (Reply)

Add Comment


To prevent automated Bots from commentspamming, please enter the string you see in the image below in the appropriate input box. Your comment will only be submitted if the strings match. Please ensure that your browser supports and accepts cookies, or your comment cannot be verified correctly.
CAPTCHA
 
Submitted comments will be subject to moderation before being displayed.
 

Blog Administration

Open login screen

Categories



All categories

Syndicate This Blog

Top Exits

Top Referrers

www.google.com (899)
www.google.co.uk (233)
www.planetpostgresql.org (220)
www.postgresql.org (140)
www.google.de (118)
aimtrust.com (96)
www.google.ca (86)
www.google.nl (76)
www.netvibes.com (70)
www.blackhatworld.com (56)