But after all those comments i ran the benchmarks again using the same scripts and the same technology on the same machine (my laptop) and here are the results. I created a new table in both Mysql (using InnoDB engine) and pgsql. And i disabled the binary logging in mysql to speed up insert/update/delete queries. Please refer to the earlier post for the setup information.
Following notification would be used :
<operation(select/insert/update/delete)> : <no_of_threads> X <operations_per_thread>
- Firstly i ran single thread with inserts both before and after disabling binary logging in mysql
Mysql Insert : 1 X 100000
Time : 65.22 Sec (binary logging enabled)
Time : 32.62 Sec (binary logging disabled)
So disabling binary logging in mysql would make your insert/update/delete queries take half the time.
Pgsql Insert : 1 X 100000
Time : 53.07 Sec
Inserts in mysql are very fast.
- Selects : 2 X 100000
Mysql time : 30.1 Sec
Pgsql time : 29.92 Sec
Both are same
- Updates : 2 X 50000
Mysql time : 29.38 Sec
Pgsql time : 36.98 Sec
Mysql updates are faster
- Ran 4 threads with different no_of_operations/thread
Run 1 [Select : 1 X 100000, Insert : 1 X 50000, Update : 1 X 50000, Delete : 1 X 20000]
Mysql time : 40.86 Sec
Pgsql time : 45.03 Sec
Run 2 [Select : 1 X 100000, Insert : 1 X 100000, Update : 1 X 50000, Delete : 1 X 10000]
Mysql time : 49.91 Sec
Pgsql time : 63.38 Sec
Run 3 [Select : 1 X 100000, Insert : 1 X 20000, Update : 1 X 20000, Delete : 1 X 1000]
Mysql time : 29.83 Sec
Pgsql time : 29.3 Sec
It could be seen that increasing the amount of insert/update/delete queries affects the performance of pgsql. Pgsql would perform better if number of selects are very high. Whereas mysql-innodb performs better in all cases
- Had 4 runs with different no of threads.
Run 1: 12 threads [Select : 2X30000 + 3X20000, Insert : 1X20000 + 2X10000, Update : 2X10000, Delete : 2X1000]
Mysql time : 31.16 Sec
Pgsql time : 30.46 Sec
Run 2: 12 threads [Select : 2X50000 + 2X40000 + 1X30000, Insert : 1X20000 + 2X15000, Update : 2X15000, Delete : 2X2000]
Mysql time : 52.25 Sec
Pgsql time : 53.03 Sec
Run 3: 20 Threads [Select : 4X50000 + 4X40000 + 2X30000, Insert : 2X20000 + 3X15000, Update : 2X20000 + 1X15000, Delete : 2X5000]
Mysql time : 169.81 Sec
Pgsql time : 136.04 Sec
Run 4: 30 Threads [Select : 2X50000 + 3X40000 + 3X30000 + 3X20000 + 4X10000, Insert : 1X30000 + 2X20000 + 3X10000, Update : 3X20000 + 3X10000, Delete : 1X10000 + 2X5000]
Mysql time : 200.25 Sec
Pgsql time : 156.9 Sec
So, it can be said that for a small system with less concurrency, mysql would perform better. But as concurrency increases, pgsql would perform better. I also saw that while running the pgsql benchmark, the system load was twice than while running mysql benchmark.
Enabling mysql binary logging for replication would ofcourse add an over head. Similarly enabling trigger based replication in pgsql would be another overhead. The fact that replication in mysql is very closely linked with the database server helps in making a high availability system easier. Whereas creating slaves using replication in pgsql is not that easy. All available products for replication in pgsql are external - 3rd party softwares. Still, for a high concurrency system pgsql would be a better choice.