MySQL Forums
Forum List  »  Performance

MySQL InnoDB Performance vs Postgres
Posted by: Ram Gudavalli
Date: July 20, 2005 03:42PM

Hello,

I've been benchmarking a Postgres installation vs a MySQL InnoDB installation to determine how best to configure/tune MySQL.

The query I'm using to benchmark is:

(select ID
from TABLE
where (TABLE.USERID = ?) and TABLE.STATUS = 1)
union
(select ID
from TABLE
where (TABLE.FRIENDID = ?) and TABLE.STATUS = 1)
order by ID

There are indexes on USERID and FRIENDID. I randomly generate a value for the parameters (using the same in both of the individual selects). The table size is about 12GB (over 200 million rows). The query plan is sufficiently good in both databases (both indexes are used).

Now the problem is I literally get 5x performance from Postgres compared to what I get on MySQL. This is using the exact same hardware, dual Xeon w/ 12GB RAM and a Powervault drive cage w/ 8 disks in a RAID 10 configuration (our data partition). Basically a pretty hefty machine.

In Postgres, I am able to get about 250 queries/sec max, whereas I get about 50 queries/sec in MySQL w/ InnoDB. I always run the stress testing clients from another machine so they don't contend for resources.

This leads me to believe that I haven't tuned MySQL correctly to get this big of a discrepancy.

My question is, besides the innodb_log_buffer_size, what are the primay configuration settings I should be looking at to get the performance to be closer to that of Postgres for this specific query?

Does it matter that I have the MySQL table stored in 1 file only? Are there other ways to optimize for this query?

I've listed the important parts of our my.cnf configuration file below.

Thanks for you help,

Ram


---------------------------------------------

table_cache=600
sort_buffer_size=1M
join_buffer_size=1M

innodb_buffer_pool_size=8G
innodb_additional_mem_pool_size=64M
innodb_log_group_home_dir = /data01
innodb_log_files_in_group=2
innodb_log_file_size=1G
innodb_log_buffer_size=8M

max_connections=600
max_user_connections=600
max_allowed_packet=32M

lower_case_table_names=1

query_cache_type=1
query_cache_size=64M

Options: ReplyQuote


Subject
Views
Written By
Posted
MySQL InnoDB Performance vs Postgres
4225
July 20, 2005 03:42PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.