Wednesday, September 29, 2010

Database speed tests (mysql and postgresql) - part 2

Here is the comparison between mysql and postgresql for selects (only). I had used the same table that i had created earlier http://jayant7k.blogspot.com/2010/09/database-speed-tests-mysql-and.html while comparing insertion speed. I have created approximately 1,000,000 records in the table and ran selects on them. I also modified the configuration of both mysql and postgresql to enable faster selects.

Mysql

In mysql I specially disabled query_cache - the reason being that I would use innodb for tables with large number of inserts - due to its support for row level locking. And with every insert t
he query cache is marked as dirty. So enabling query cache would lead to caching of queries which will not be the scenario in a live setup.

Mysql Configuration :
innodb_buffer_pool_size = 256MB
key_buffer_size = 256MB
read_buffer_size = 512KB
sort_buffer_size = 512KB
query_cache_size = 0
thread_cache_size = 32
table_open_cache = 64

table information :
No of records : 9755366
data (idb) size : 692 MB

Mysql : (time for 100000 selects)

Avg time for 500000 selects with concurrency = 5 : 58.67
Avg time for 1000000 selects with concurrency = 10 : 122.8
Avg time for 2000000 selects with concurrency = 20 : 225.67
Avg time for 3000000 selects with concurrency = 30 : 351.66
Avg time for 4000000 selects with concurrency = 40 : 452.3


PostgreSQL :

Mysql has better table compression as compared to postgres. Same data in innodb is of around 700 MB while that in Postgres is of around 900 MB.

Postgres configuration :
shared_buffers = 128MB
work_mem = 1MB
random_page_cost = 4.0
effective_cache_size = 256MB


table information :
No of records : 9755366
data size : 912 MB

Pgsql : (time for 100000 selects)

Avg time for 500000 selects with concurrency = 5 : 86.8
Avg time for 1000000 selects with concurrency = 10 : 144.74
Avg time for 2000000 selects with concurrency = 20 : 274.37
Avg time for 3000000 selects with concurrency = 30 : 402.92
Avg time for 4000000 selects with concurrency = 40 : 528.17



Mysql seems to perform better with selects. The graph also shows that with increase in concurrency, selects in innodb take lesser time than that in postgresql.

So, why would you switch from mysql to postgresql - only if you have a very high ratio of inserts as compared to selects. The benefit in inserts outweigh the loss in selects to some extent.

8 comments:

Andy said...

Did you use
ROW_FORMAT=COMPRESSED
for InnoDB?

f so, what value of KEY_BLOCK_SIZE did you use?

Also what type of selects did you use - by primary key or by secondary key?

Jayant Kumar said...

Sorry for not mentioning the type of selects. The selects were not on primary key, but on secondary index

Table structure:
id int primary key auto_increment
val int indexed
txt varchar

Query being fired :
select * from table where val = '$x'

With different values of $x

I have not changed the ROW_FORMAT in innodb configuration. Am using the default setting for these variables.

Matthew Montgomery said...

Recommending switching to PostgreSQL from MySQL for superior insert performance seems a bit odd considering the controversy surrounding your insert benchmark. If you still require very fast and high volume INSERT as opposed to SELECT there are alternative storage engines which address this within MySQL. NDB Cluster and TokuDB are examples.

BTW, having key_buffer_size = innodb_buffer_pool_size is a bit wasteful in this test since key_buffer_size only applies to MyISAM tables. Otherwise, Nice benchmark.

Andrew said...

I see the MySQL employees will say "nice benchmark" when it favors their platform. ;)

I recommend posting the benchmark code, as it's difficult to take these numbers (especially the troubled insert benchmark) at face value. It would be useful to see exactly what you're doing.

Jayant Kumar said...

sure will make it a point to publish the code as well..

Jayant Kumar said...

Have published the code, please refer : http://jayant7k.blogspot.com/2010/10/database-speed-tests-mysql-and.html

Anonymous said...

There are other reasons to switch to postgresql, speed is not the only reason, there are technical aspects in which mysql is not food enough. And do you think Oracle will support mysql development as Sun did? And postgresql will continue to develop, I am sure in short time it will be faster then mysql on selects too.

Robert said...

I don't know much about MySQL configuration, but it looks like you've set the MySQL buffer pool to twice the size of the PostgreSQL shared buffers arena, which seems like it might matter.