5 ms·

Performance since PostgreSQL 7.4 with pgbench

166 points by radimm 12y ago

mtanski 12y ago
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 12y ago
  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).
inglor 12y ago
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-mysql-vs-postgresql-a-comparison-of-relational-database-management-systems https://www.digitalocean.com/community/tutorials/sqlite-vs-m...
- pgaddict 12y ago
  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).
  - pgaddict 12y ago
    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.
  - morgo 12y ago
    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-with-sysbench-fileio/ 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/ https://fosdem.org/2015/schedule/event/benchmark_r_gpplot2/ (Disclaimer: I work on the MySQL team.)
    - pgaddict 12y ago
tiffanyh 12y ago
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 12y ago
  Good question. It was mentioned in the talk (http://www.slideshare.net/fuzzycz/performance-archaeology-40583681 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-7-4-to-9-4 http://blog.pgaddict.com/posts/performance-since-postgresql-...).
  - sophacles 12y ago
    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...).
    - pgaddict 12y ago
      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 ;-)
  - mrmondo 12y ago
    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.
sitkack 12y ago
This looks like excellent science! I love this.
pgaddict 12y ago
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-7-4-to-9-4-tpc-ds http://blog.pgaddict.com/posts/performance-since-postgresql-...

166 points by radimm 12y ago

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 12y ago
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).

inglor 12y ago

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

pgaddict 12y ago
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).
- pgaddict 12y ago
  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.
- morgo 12y ago
  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-with-sysbench-fileio/ 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/ https://fosdem.org/2015/schedule/event/benchmark_r_gpplot2/ (Disclaimer: I work on the MySQL team.)
  - pgaddict 12y ago

tiffanyh 12y ago

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 12y ago
Good question. It was mentioned in the talk (http://www.slideshare.net/fuzzycz/performance-archaeology-40583681 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-7-4-to-9-4 http://blog.pgaddict.com/posts/performance-since-postgresql-...).
- sophacles 12y ago
  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...).
  - pgaddict 12y ago
    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 ;-)
- mrmondo 12y ago
  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.

sitkack 12y ago

This looks like excellent science! I love this.

pgaddict 12y ago

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