MySQL vs PostgreSQL Performance: throughput & latency, reads & writes
20 points by BinaryIgor
20 points by BinaryIgor
I suspect that what is really being measured here is these configuration of the databases. MySQL outperforming PostgreSQL on joins but not on simple selects seems suspicious, and the fact that simple inserts on a table is different between them as opposed to being limited by the hardware is troubling.
and the fact thatj simple inserts on a table is different between them as opposed to being limited by the hardware is troubling.
MySQL defaults to binlogging, which is also required for replication. It makes every commit need 2 fsyncs, one for the InnoDB redo log and one for the binlog.
Hmm, interesting - and MariaDB has a different default, without replication support? Or does it work differently there?
AFAIK it works the same but I’m not sure if binlog is enabled and fsynced by default.
Yeah looks like it defaults to sync_binlog=0.
If you want to use binlog replication and have it work properly, you have to enable it.
Thanks for pointing this out! It means then that by default MariaDB does not have real durability - interesting; there is a possibility of data loss, in case of power outage.
It might very well also be case that MySQL implementation is simply worse :) I have since repeated these tests with MariaDB and it performs much better than MySQ, with a similar config (there once shared the codebase); which points me to conclude that MySQL is just badly implemented in these (and others) regards.
I am happy to be corrected - if you happen to know more about MySQL and have specific config options in mind that might bump its performance - let me know! Happy to retry the benchmarks for it.
Big missing thing from Postgres configuration from what I see is random_page_cost which defaults to 4, which is configured for HDDs. If you are using SSD/NVMe for storage there, then it will cause heavy penalty for random access and may reduce indices utilisation.
Thanks for pointing this out - I played a bit with the option; rerunning tests with random_page_cost=1.5, it does improve performance for pretty much all selects :) Not dramatically, but noticeably. Writes are not affected; more or less same results there.
I wasn't aware that PostgreSQL perf had improved so much from the days of "well, its better on writes, better w/ data integrity, but mysql wins hands-down in read-heavy workloads". Impressive.
Yes! Add to this performance Unlogged Tables for cache, amazing JSON/JSONB and Full Text Search support and it's really, really hard to find a more universal database ;)