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)

• mariadb:10.1.11
• mysql:5.7.10
• postgres:9.5.0

Benchmark Details

Full detail is at this Github Repo Folder

Total Rows: 1 million

Queries:

1. SELECT * FROM testing LIMIT 1000
2. SELECT * FROM testing WHERE int_col > 5000 LIMIT 1000
3. SELECT * FROM testing WHERE int_col + int_col2 > 12345
4. SELECT COUNT(*) FROM testing WHERE int_col + int_col2 > 12345
5. SELECT * FROM testing WHERE int_col > 5000 ORDER BY word_col ASC LIMIT 1000
6. SELECT * FROM testing WHERE word_col LIKE '%lim%' ORDER BY word_col DESC LIMIT 1000

MariaDB Summary:

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

MySQL Summary:

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

PostgreSQL Summary:

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

Conclusion

• 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.