Tuesday, December 11. 2007
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.
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 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.
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 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.
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.
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
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
Display comments as (Linear | Threaded)
can you tell what parameters that you change in the default config to get the tuned version?
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.
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?
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.
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
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!
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.
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.
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.
*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.
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...
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
yeah all the testing was done with pgbench and the same pgbench settings
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.
Postgresql 8.3 has also another big perfomance improvement - delayed wal logs. Did switch it on while testing?
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.
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!
IIUC, this only reduces COMMIT latency. At high concurrency, it won't boost throughput much.
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.
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.
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.
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)
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.
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.
Is anyone else able to replicate this? I am just not seeing that performance gain from increasing shared_buffers.
this should be fairly easy to replicate - what workload are you using and how large is the working set ?
I'm trying exactly your workload as you described it, on similar hardware.
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.