Author avatar

Liam

12 Feb, 2016

blogpost banner

PostgreSQL [9.5.0] vs MariaDB [10.1.11] vs MySQL [5.7.0] year 2016

Postgres vs Maria vs Mysql Postgres vs Maria vs Mysql ln

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.