MySQL vs PostgreSQL Performance Benchmark (Latency - Throughput - Saturation)

Показать описание

PostgreSQL vs MySQL Performance/Speed Benchmark.

▬▬▬▬▬ Experience & Location 💼 ▬▬▬▬▬
► I’m a Senior Software Engineer at Juniper Networks (12+ years of experience)
► Located in San Francisco Bay Area, CA (US citizen)

▬▬▬▬▬▬ Connect with me 👋 ▬▬▬▬▬▬

▬▬▬▬▬▬▬ Timestamps ⏰ ▬▬▬▬▬▬▬
0:00 Intro
0:50 Test Design
3:40 Benchmark Improvements
4:59 Database Configurations
5:51 Tables
6:32 Source Code
7:13 1st Test
10:39 2nd Test

▬▬▬▬▬▬▬ Source Code 📚 ▬▬▬▬▬▬▬

#postgresql #mysql #postgres

Рекомендации по теме

Комментарии

It is very interesting that not so many people tested and published DB comparisons. I don't know any other easily accessible test result. And for production applications DB performance is KEY! If you use Go or Java has a pretty low impact. But if your DB stales at a certain number of requests per seconds matters BIG time!

nothingisreal

Now, that's how you test a database. This was a good test, you covered all the bases (pun intended). Thumbs up !

InXLsisDeo

Awesome changes. Postgres is one of the technologies that hasn’t let me down over the years. The low memory usage is something I observed many times during production usage. Now we need benchmarks for Sqlserver and the big evil O. But I don’t think donations would cover the lawyers. 😅

yannick

U are the best man! Thank you for tune sites for databases

btw u forgot to separate 1st and 2nd tests in time codes

def-any

In the first test, postgres jumps to 100% cpu usage at about 400 requests per second. This is higher than mysql which makes postgres look like the winner, but what happens then is catastrophic. rps drops to 100 and the cpu is pinned (meaning you will have difficulty logging into the machine and diagnosing issues). One might think that the CPU spikes due to iops being saturated and it's spinning, but an SSD should have over 1k write iops available (as we see around 07:24) - so that doesn't appear to be a limiting factor here.

All the operations have a latency spike at the same time, so perhaps a compaction executed and had a bad interaction. Do any postgres expects know what could have happened? Do you have the autovacuum daemon running? (it's commented out in the config; but the config suggests default is 'on'). I wonder if that has an interesting interaction here.

ehfoss

While I was cheering for postgresql I think it's exceptionally bad behaviour to randomly stall, spike to 100% CPU instead of gradually degrading in performance when overloaded. It will be extremely hard to debug. Hope someone can explain why this happened and propose a solution.

zuzelstein

I wonder if Mariadb does better than Mysql in such a Benchmark

meniv

In my experience they aren't even close. I now always use postgres. And if you have particular patterns of use, you can tweak settings for that. Also, pg now supports semi structured data, so you can use it like a nosql db if you want to store lots of json.

mrpocock

We need a comparison now between PostgreSQL and CockroachDB)

And MySQL vs TiDB

sergeyshaykhullin

I have no adequate words to express my admiration and a gratitude for your work !

CenturionDobrius

Thanks! I'm a bit confused by why you excluded the Postgres failure at the end of Test 1 when looking at the graph for the full duration...

AGAU

I'd be interested in MySQL vs. Mariadb and also Postgres vs. Sqlite reading performance

dasten

Anton, thank you so much for making these comparisons!

For postgres it also would be nice to see the chart with Vacuum executions. It might have provided additional information about what happens behind the scenes.

YuryPastushenko

Indexes for foreign keys not only speed up queries, but they also enable deleting from a referenced table without table locks on the referencing table. This is a major gotcha when designing database schemas, and it's nice that MySQL creates them automatically.

dvk

Thanks for the video. The update/deletes I would expect be cheaper in Postgres but their full impact will come when auto-vacuum cleans up the dead tuples. I haven't looked through the settings yet but I wonder if that's what causing all the waves in the Postgres graph.

drpjuu