MySQL versus PostgreSQL

I created and ran some simple tests on mysql and postgresql to figure out which one is faster. It is already known that postgresql is more stable and reliable than mysql. pgsql has a rich set of features. It is a complete RDBMS and also supports fulltext search.

All benchmarks were done on my laptop - Intel core 2 duo (2.0 GHz) with 4MB L2 cache & 2 GB ram. I have 64 Bit ubuntu system loaded with MySQL 5.1.24-rc (64 bit binary) and PostgreSQL 8.3.1 (compiled from source).

I used python as a scripting language for writing down my benchmark scripts. I used psycopg2 as a connector from python to postgres and mysql-python as a connector from python to mysql.

The benchmarking was done in phases. Firstly simple Insert, update and select queries were run to check the raw speed of these queries. Then threads were created to run simultaneous insert, update, select and delete queries. I checked the benchmark times for different number of concurrent threads.

I created a simple table on both mysql and pgsql. I used the MyISAM database engine to create table in mysql. :

ABC(id int not null auto_increment primary key, value varchar(250));

Queries that were run are:

Insert(I) : Insert ignore into ABC (id, value) ...(For pgsql, a rule has to be created to ignore duplicate inserts)
Update(U) : Update ABC set value=<something> where id=<random_id>
Select(S) : Select * from ABC where id=<random_id>
Delete(D) : Delete from ABC where id=<random_id>

• Insert - 100000 rows in 1 thread
  Time taken for Mysql : 20.8 seconds
  Time taken for Pgsql : 58.1 seconds
  So, raw insert speed of mysql is much better as compared to pgsql


• 100000 selects in 1 thread
  Time taken for Mysql : 21.76 seconds
  Time taken for Pgsql : 20.15 seconds
  Raw selects are better in pgsql as compared to mysql


• Selects - 2 threads of 100000 selects
  Time taken for Mysql : 40.46 seconds
  Time taken for Pgsql : 27.38 seconds
  So, if i increase the concurrency of selects, pgsql perfors much than mysql


• Update - 2 threads of 50000
  Time taken for Mysql : 23.97 seconds
  Time taken for Pgsql : 34.03 seconds
  Mysql looks better in handling updates here.


• 4 Threads
  Run 1 : [100000 Selects, 50000 Inserts, 50000 Updates, 20000 Deletes]
  Time taken for Mysql : 45.25 seconds
  Time taken for Pgsql : 54.58 seconds
  Run 2 : [100000 Selects, 100000 Inserts, 50000 Updates, 10000 Deletes]
  Time taken for Mysql : 59.05 seconds
  Time taken for Pgsql : 69.38 seconds
  Run 3 : [100000 Selects, 20000 Inserts, 20000 Updates, 1000 Deletes]
  Time taken for Mysql : 35.54 seconds
  Time taken for Pgsql : 31.23 seconds
  These runs show that Mysql is good when you have very large no of inserts/updates/deletes as compared to selects. But pgsql's performance surpasses that of mysql when the number of selects are much higher.


• Finally, lets approach the real life scenario where generally the number of selects are much more than the number of inserts and there are multiple threads performing selects and inserts.
  I will use the following notification here - <no_of_threads> X <no_of_operations(select/insert/update/delete)_per_thread>
  So, for example 3 X 20 Selects = 3 threads of 20 Selects in each thread
  
  Run 1 : [2 X 30000 selects, 3 X 20000 selects, 1 X 20000 inserts, 2 X 10000 inserts, 2 X 100000 updates, 2 X 1000 deletes] Total - 12 threads
  Time taken for Mysql : 42.28 seconds
  Time taken for Pgsql : 44.28 seconds
  Both Mysql and Pgsql are almost at par.
  
  Run 2 : [2 X 50000 selects, 2 X 40000 selects, 1 X 30000 selects, 1 X 20000 inserts, 2 X 15000 inserts, 2 X 15000 updates, 2 X 2000 deletes] Total - 12 threads but number of selects are quite high
  Time taken for Mysql : 61.02 seconds
  Time taken for Pgsql : 48.60 seconds
  So, as we increase the number of operations (specially selects) mysql's performance degrades, whereas pgsql's performance remains almost the same
  
  Run 3 : [4 X 50000 selects, 4 X 40000 selects, 2 X 30000 selects, 2 X 20000 inserts, 3 X 15000 inserts, 3 X 15000 updates, 2 X 3000 deletes] Total - 20 threads (10 threads for select, 5 for insert, 3 for update and 2 for delete) Which is the normal trend in database servers.
  Time taken for Mysql : 169.31 seconds
  Time taken for Pgsql : 128.7 seconds
  Bingo, so as concurrency increases pgsql becomes faster than mysql.

My earlier benchmarks with pgsql 7.x was not as good as this one. With postgresql 8.3.1, the speed of serving concurrent requests has increased a lot. So, in a high concurrency environment, i would generally recommend to go ahead with using postgresql rather than mysql.

Please check the comments section. We have some really interesting comments there...