

Performance since PostgreSQL 7.4 with pgbench - radimm
http://blog.pgaddict.com/posts/performance-since-postgresql-7-4-to-9-4-pgbench

======
inglor
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...](https://www.digitalocean.com/community/tutorials/sqlite-vs-mysql-vs-
postgresql-a-comparison-of-relational-database-management-systems)

~~~
pgaddict
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).

~~~
morgo
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-...](http://www.percona.com/blog/2014/08/12/benchmarking-exflash-with-
sysbench-fileio/)

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

(Disclaimer: I work on the MySQL team.)

~~~
pgaddict
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.

~~~
morgo
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.

~~~
pgaddict
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).

------
mtanski
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.

~~~
pgaddict
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](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).

------
pgaddict
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-...](http://blog.pgaddict.com/posts/performance-since-
postgresql-7-4-to-9-4-tpc-ds)

------
tiffanyh
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.

~~~
pgaddict
Good question. It was mentioned in the talk
([http://www.slideshare.net/fuzzycz/performance-
archaeology-40...](http://www.slideshare.net/fuzzycz/performance-
archaeology-40583681)), 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-...](http://blog.pgaddict.com/posts/performance-since-
postgresql-7-4-to-9-4)).

~~~
mrmondo
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.

~~~
pgaddict
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.

~~~
mrmondo
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.

~~~
pgaddict
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).

~~~
mrmondo
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.

~~~
kbenson
_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.

~~~
mrmondo
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)

~~~
pgaddict
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.

------
sitkack
This looks like excellent science! I love this.

