4 ms·

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

by 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
    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 12y ago
      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 12y ago
        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).
mikeytown2 12y ago
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-57-rocks-oltp_ro-pointselects-8tables-benchmark.html http://dimitrik.free.fr/blog/archives/2014/09/indeed-mysql-5... [2] http://dev.mysql.com/doc/refman/5.6/en/innodb-memcached.html http://dev.mysql.com/doc/refman/5.6/en/innodb-memcached.html [3] http://dimitrik.free.fr/blog/archives/2013/11/mysql-performance-over-1m-qps-with-innodb-memcached-plugin-in-mysql-57.html http://dimitrik.free.fr/blog/archives/2013/11/mysql-performa... [4] http://dimitrik.free.fr/blog/archives/2014/11/mysql-performance-57-and-rds-aurora-so-what.html http://dimitrik.free.fr/blog/archives/2014/11/mysql-performa...
- teraflop 12y ago
  Even if the hardware was the same, it's entirely meaningless to compare numbers from two different benchmarks performing different workloads.
  - pgaddict 12y ago
    Exactly.
  - mikeytown2 12y ago
    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 https://launchpad.net/sysbench. Also a guide on how to use it: http://wiki.mikejung.biz/Benchmarking#sysbench http://wiki.mikejung.biz/Benchmarking#sysbench
TylerE 12y ago
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.
- mdasen 12y ago
  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.

by inglor 12y ago

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

mikeytown2 12y ago

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-57-rocks-oltp_ro-pointselects-8tables-benchmark.html http://dimitrik.free.fr/blog/archives/2014/09/indeed-mysql-5... [2] http://dev.mysql.com/doc/refman/5.6/en/innodb-memcached.html http://dev.mysql.com/doc/refman/5.6/en/innodb-memcached.html [3] http://dimitrik.free.fr/blog/archives/2013/11/mysql-performance-over-1m-qps-with-innodb-memcached-plugin-in-mysql-57.html http://dimitrik.free.fr/blog/archives/2013/11/mysql-performa... [4] http://dimitrik.free.fr/blog/archives/2014/11/mysql-performance-57-and-rds-aurora-so-what.html http://dimitrik.free.fr/blog/archives/2014/11/mysql-performa...

teraflop 12y ago
Even if the hardware was the same, it's entirely meaningless to compare numbers from two different benchmarks performing different workloads.
- pgaddict 12y ago
  Exactly.
- mikeytown2 12y ago
  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 https://launchpad.net/sysbench. Also a guide on how to use it: http://wiki.mikejung.biz/Benchmarking#sysbench http://wiki.mikejung.biz/Benchmarking#sysbench

TylerE 12y ago

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.

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