12 Feb, 2016
PostgreSQL [9.5.0] vs MariaDB [10.1.11] vs MySQL [5.7.0] year 2016
Reason to Benchmark
On Dec 2015 I have done a similar benchmark on postgres vs mariadb vs mysql, however I had done some serious mistake on the benchmark and caused the result to be biased towards MySQL.
Therefore I had enhanced the benchmark. The benchmark script and how to setup is at this Github Repo.
Notice & FAQ
- Why using default database configuration / configuration between databases is difference:
- This benchmark is using default configuration that comes with the docker image.
- Time consuming to standardize databases configuration
- Default configuration should give a basic idea on how the database performs.
- Why missing benchmark on index, included data transfer time for benchmark, etc:
- Is in the To Do list, however IMHO these are good to have benchmark.
- Preferably others can help to enhance the script.
- Why run in VM which make this benchmark not so reliable:
- Do not want to spend money to buy a physical server or cloud instances.
- Make benchmark decentralized, everyone who had a laptop should be able to do their own benchmark instead of relying on others.
- A rough idea on how these databases perform is very important knowledge for all the developer.
Benchmark Environment (Vagrant)
- Ubuntu wily werewolf (15.10)
- 1 core CPU, 100% execution cap
- 1024 MB memory
Benchmark Environment (Docker)
Full detail is at this Github Repo Folder
Total Rows: 1 million
SELECT * FROM testing LIMIT 1000
SELECT * FROM testing WHERE int_col > 5000 LIMIT 1000
SELECT * FROM testing WHERE int_col + int_col2 > 12345
SELECT COUNT(*) FROM testing WHERE int_col + int_col2 > 12345
SELECT * FROM testing WHERE int_col > 5000 ORDER BY word_col ASC LIMIT 1000
SELECT * FROM testing WHERE word_col LIKE '%lim%' ORDER BY word_col DESC LIMIT 1000
average write time: 50.02ms/10000rows average query time (q1): 3.87ms average query time (q2): 4.36ms average query time (q3): 5.18ms average query time (q4): 261.69ms average query time (q5): 741.55ms average query time (q6): 639.75ms
average write time: 50.86ms/10000rows average query time (q1): 3.42ms average query time (q2): 3.91ms average query time (q3): 5.33ms average query time (q4): 246.77ms average query time (q5): 6686.11ms average query time (q6): 508.9ms
average write time: 79.2ms/10000rows average query time (q1): 3.27ms average query time (q2): 3.62ms average query time (q3): 4.0ms average query time (q4): 169.18ms average query time (q5): 229.33ms average query time (q6): 207.01ms
- MariabDB & MySQL has slight advantages at database writing
- PostgreSQL has slight advantages at simple database read query
- PostgreSQL has big advantages at complex database read query
As a result, I think that choosing PostgreSQL is a better options for RDBMS - provided that PostgreSQL comes with more features and following standard SQL.