Performance since PostgreSQL 7.4 with pgbench | Hacker News Reader

Performance since PostgreSQL 7.4 with pgbench

blog.pgaddict.com

166 pointsradimm32 comments

I'm just wondering - where does PostgreSQL stand with those numbers vs MySQL? Did someone perform similar benchmarks comparing the two in such workloads?

Searching finds surprisingly outdated information.. Lots of articles say things like "For simple read-heavy operations, PostgreSQL can be an over-kill and might appear less performant than the counterparts, such as MySQL." but don't really back them up... [1]

[1] https://www.digitalocean.com/community/tutorials/sqlite-vs-m...

That'd be an interesting benchmark, but I don't have the MySQL expertise to do that (and I don't want to publish bullshit results).

I appreciate the straight-forwardness of this comment :)

One of the things that I think would be interesting to show, is how the consistency of performance has improved over time too. There has been a lot of focus on removing any stalls in recent versions of InnoDB, and this something that is incredibly valuable doesn't always show up in benchmarks :( I assume that PostgreSQL has gone through the same evolution in managing garbage collection, etc.

Peter Boros @ Percona has done some nice benchmark visualization, for example: http://www.percona.com/blog/2014/08/12/benchmarking-exflash-...

Slides on how to produce these graphs: https://fosdem.org/2015/schedule/event/benchmark_r_gpplot2/

(Disclaimer: I work on the MySQL team.)

You mean how the transaction rate changes when things like checkpoint or vacuum are triggered in the background? Yeah, I do have those numbers, and the consistency improved a lot (especially when autovacuum was introduced in 8.1, and when spread checkpoints were introduced in 8.3).

The other part of the story are filesystems - back in 8.x days the filesystem was usually ext3, and that handled sync pretty badly (and that made the impact of checkpoints / autovacuum much worse). Today we have xfs/ext4 which do handle that much better.

Checkpoint and vacuum are good examples. There are other stalls we have had to address: extending the size of tablespace files, dropping tables and scanning the LRU to free pages.

PostgreSQL stores the data in a different way (each object gets a different file, or several files), so extending the size of tablespace files is not a big issue and dropping objects is as simple as deleting the file.

The LRU is basically the shared_buffers, and that's where most of the 9.2 improvements were (more or less).

Actually, if there's someone with MySQL (or MariaDB) tuning skills, it'd be interesting to do a proper comparison. Might be a nice conference talk too, so let me know.

My general experience with the two systems is that when performing trivial queries (e.g. simple selects or updates that only hit a single table), MySQL is faster, but not enormously, and that gap can be narrowed with good tuning on the PG server.

As soon as you get into anything fancy, pg eats mysql for lunch. The query optimizer is MUCH smarter.

If people are curious, some of it comes down to the techniques and algorithms that each implement. This information might be a little out of date and I'm doing this from memory so please pardon/correct any inaccuracies.

So, with joins, MySQL uses loop joins. These are often expensive compared to hash or merge joins. PostgreSQL's query planner can choose which of the three join strategies will have the lowest estimated cost. I believe MariaDB has implemented hash joins. I'm sure there are cool visualizations of the different join types that you could watch in action and see how they'd perform differently.

MySQL (with InnoDB) uses a clustered (index-oriented) table layout. Basically, it keeps the table in a B-tree and so querying by the primary key becomes nice and fast since the physical data layout of the table is in that lookup order and successive rows are likely to be in the same disk block. But this means that if you're looking up something in a secondary index, you then have to traverse the primary index (going through two trees). Databases like SQL Server and Oracle let you choose your table layout, but MySQL and PostgreSQL only support one (MySQL giving you clustered and PostgreSQL giving you heap).

MySQL has supported covering indexes for a while, but PostgreSQL has only added this capability recently. A covering index means that you don't have to read data from the underlying table if you only need columns that are included in the index (since all of the data is in the index).

Ultimately with databases, the key is limiting the amount of time wasted waiting for the disk. If you can cut down on the number of disk blocks you need to fetch to fulfil a query, you win! There are lots of things like these that databases do to try and get better performance (some doing more, some doing less, and some that involve trade-offs) and they're often quite reasonable to understand.

Some benchmarks put MySQL 5.7 at 645k QPS [1] and if you're using the MySQL memcache plugin [2] then you got well over 1m QPS [3]. Update performance is up to 100k QPS as well [4]. Note that in these benchmarks they are using some expensive hardware (40 core ht).

[1] http://dimitrik.free.fr/blog/archives/2014/09/indeed-mysql-5...

[2] http://dev.mysql.com/doc/refman/5.6/en/innodb-memcached.html

[3] http://dimitrik.free.fr/blog/archives/2013/11/mysql-performa...

[4] http://dimitrik.free.fr/blog/archives/2014/11/mysql-performa...

Even if the hardware was the same, it's entirely meaningless to compare numbers from two different benchmarks performing different workloads.

I totally agree that benchmarks with different parameters are fairly meaningless. Was just trying to give people a ballpark figure in reply to the question about how to 2 compare with benchmarks; even if they are not equivalent.

One interesting point about this is that the PostgreSQL benchmark tops out at 8 users whereas the MySQL ones start around 8 users and go up from there. Right at the 8 user point, both SQL engines are within the same range, just under 100k QPS on read; with the hardware (# of cores) not changing this value much, as well as the MySQL version 5.5-5.7 not having any effect either. That seems like a limit that these 2 SQL engines (PostgreSQL & MySQL) are hitting; most likely from parsing the SQL. Thus the only way to improve single threaded performance is to use something other than SQL; like the memcache protocol.

Link to the benchmarking tool they used: https://launchpad.net/sysbench. Also a guide on how to use it: http://wiki.mikejung.biz/Benchmarking#sysbench

IMHO the assumption that parsing the SQL is the bottleneck here is unfounded - I'd like to see some analysis supporting that first. The queries used in those benchmarks are extremely trivial, and there are far more expensive operations involved. I don't think it can be deduced from comparison of two quite different benchmarks (different workloads, different dataset sizes, different hardware, ...)

Also, relational databases are general-purpose query engines, not caching solutions like memcached, with very limited set of commands. It might be possible to invent a more efficient binary protocol to send queries/receive results, but then you still have things like optimization, planning, permission checks, ... that are usually more expensive than parsing the data.

It's almost certainly not SQL parsing that is introducing a serial component and an upper bound on parallelism. Actually, that is a task that is almost perfectly parallelizable. Parsing has no serial component, planning may have one if you somehow need locks on shared data structures needed for plan optimization.

MySQL and Postgresql are insanely different. To a point that it's uninteresting to compare them. MySQL is a keystore with an SQL frontend, which eventually got ACID features bolted on. Postgresql is a full-fledged RDBMS that eventually got refactored with performance in mind. While the end goal seems the same, the results are really different.

All of this text to say something which I think is common knowledge and is found interspersed in the comments here. For any kind of moderately complex query, Postgresql will eat Mysql's lunch. For any kind of concurrent locking, highly parallel, use case, Postgresql is much faster and/or safer. For any kind of very simple query, Mysql will eat Postgresql's lunch. It's a result of different upbringings.

If your use case can be satisfied with memcache, you aren't using a relational database and, yes, Mysql is the best choice, out of these two. Anyhow, in this scenario, today, there is a vast choice of good persistent keystores that are better than mysql.

Exactly.

Yay Postgre and thanks for running the numbers. However, it would be nice to see a summary of the major changes that made a impact on the performance on each version. I like geeking out on db internals.

I was considering that when writing that post, but that'd be a much longer post, especially if I was to explain the logic behind the changes. And without that, it's be just a list of release notes, so not really useful IMHO.

That's why I included a link to Heikki's FOSDEM talk, discussing the changes in 9.2 in more detail.

The best way to do identify the changes (in general) is probably by looking at release notes:

http://www.postgresql.org/docs/9.4/static/release.html

The large changes should be in major releases, because minor releases are supposed to be just bugfixes, so don't bother with anything except 9.x release notes (effectively 9.x.0).

There's usually a section called "performance", which for 9.2 contains for example these items:

* Allow queries to retrieve data only from indexes, avoiding heap access (Robert Haas, Ibrar Ahmed, Heikki Linnakangas, Tom Lane) * Allow group commit to work effectively under heavy load (Peter Geoghegan, Simon Riggs, Heikki Linnakangas) * Allow uncontended locks to be managed using a new fast-path lock mechanism (Robert Haas) * Reduce overhead of creating virtual transaction ID locks (Robert Haas) * Improve PowerPC and Itanium spinlock performance (Manabu Ori, Robert Haas, Tom Lane) * Move the frequently accessed members of the PGPROC shared memory array to a separate array (Pavan Deolasee, Heikki Linnakangas, Robert Haas)

There are other items, but these seem the most related to the pgbench improvements in this particular release. Each of these changes should be easy to track to a handful of commits in git.

Measuring the contribution of each of these changes would require a much more testing, and would be tricky to do (because some of the bottlenecks were tightly related).

FWIW, I just posted the next part, comparing performance with analytical workloads (TPC-DS-like, DWH/DSS, ...).

http://blog.pgaddict.com/posts/performance-since-postgresql-...

I wonder what OS (and version and settings) was being used, since I imagine that'd have a major performance impact on this test result.

Good question. It was mentioned in the talk (http://www.slideshare.net/fuzzycz/performance-archaeology-40...), but I forgot to put that into the post. Both machines were running Linux.

The DL380 machine (the 'main' one) was running Scientific Linux 6.5 with 2.6.32 kernel and XFS.

The i5-2500k machine was running Gentoo (so mostly 'bleeding edge' packages), with kernel 3.12 and ext4.

Update: Actually, I've just noticed it's mentioned in the previous 'introduction' post in the series (http://blog.pgaddict.com/posts/performance-since-postgresql-...).

Kernel 2.6.32 on that DL380 is ancient regardless of whatever patches are shoehorned in by Redhat it's very old tech - you'd notice considerably better performance when using a modern kernel.

We also noticed a huge gain in performance from upgrading from Kernel 3.14 to 3.18 especially with regards to storage and network latency.

Well, you're right. The thing is - it's the kernel version for that particular distribution, tracking RH 6. That's one of the reasons why I did the tests on the Gentoo box with a much newer kernel.

Yeah the ecosystem for Redhat and variants becomes legacy very quickly - don't get me wrong Redhat are an amazing company and make quality software but when you need performance or newer features they aren't the best. Debian wheezy has had 3.16 stable in Backports since its release as that's what Debain Jessie will be released with shortly.

We (Infoxchange) actually CI our kernel builds daily so we end up with whatever the latest stable kernel is as its released, we also use that as an oppertunity to add the GRSecurity patches to the kernel.

Different distributions for different users/customers, I guess. People who run RH often require stability over long periods of time, but if you want faster update cycle, RH (or derived distros) might not be the right choices.

Hmmm, but maybe I could use the kernel from epel. I see there's 3.10 (lt), 3.18 and 3.19 (ml) for el6. It's a bit tricky though, because I have absolutely no access to this machine except for ssh (no KVM or anything), so a botched kernel upgrade means a few days of outage before it's taken care of (it's hosted at a local university for free).

Generally speaking newer kernels are considerably more stable than older kernels - the main reason Redhat and variants have such old Kernels is because of the proprietary driver compatibility and patches for 'enterprise' hardware such as RAID controllers.

Additionally - there is also a trap where software is deployed that has been developed without the idea of being modernised (and in some cases maintained) over time - Vendors will often charge large amounts of money to modernise and test these applications.

I've observed this as both a means of increasing profitability, out of lack of quality engineering / development skills or practices or by misinforming customers to think that risk aversion and a slow rate of change is the least costly option over time which leads to or is caused by pathological or bureaucratic organisational culture.

Generally speaking newer kernels are considerably more stable than older kernels

That may be generally true of kernels, but when you are guaranteeing compatibility and support for a set of hardware, I would argue that after you've confirmed they work for a kernel, freezing that version and back-porting fixes and features that are worth it with another testing cycle is generally more stable.

Can you give me an example of some hardware that is in general use that is no longer supported in Kernel 3.18/3.19? (I'm not saying you can't - I'm genuinely interested in this question)

It's not just about removing support for hardware - that happens from time to time, but generally not with reasonably recent hardware. It's also about minimizing risk of bugs in the new parts - even if you do a very thorough testing, you can't really eliminate the risks, so the more conservative deployments simply keep the old kernel versions.

I do have my own experience with this, particularly with 3.10 - we've thoroughly evaluated it, yet after a few days on production all the machines crashed because of deadlock in the I/O stack. Very time consuming to debug where the actual problem is (virtualised environment, ...).

So while I run Gentoo on most of my personal machines, I do understand why people are conservative.

It's not that it's not supported, it's that the driver may have been modified to support other devices, or a portion could have been rewritten, and that could lead to short-term instability until they identify and fix any problems introduced.

Does the difference in kernel an filesystem have any effect on the jumps you saw between the xeon and i5? Really curious about how the benchmarks comparing the two play out if the hardwares were both running Gentoo with 3.12 and ext4. (or the older kernel and xfs I guess...).

Good point. For example the 9.2 changes were done after in-kernel lseek() optimizations, which improved the performance in some workloads. So yes, that might be a factor here (and I should have point that out in the post).

I can't easily reinstall the OS on the Xeon machine (it's in a server room that I don't have direct access to), but installing an older kernel on the i5 machine should not be a problem.

Hmmm, a nice topic for another benchmarking quest ;-)

This looks like excellent science! I love this.

Reply on news.ycombinator.com