MySQL InnoDB Performance vs Postgres
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
Subject
Views
Written By
Posted
MySQL InnoDB Performance vs Postgres
4225
July 20, 2005 03:42PM
1838
July 20, 2005 04:16PM
1782
July 20, 2005 05:12PM
2052
July 20, 2005 05:27PM
1963
July 20, 2005 07:57PM
1979
July 21, 2005 12:53PM
1797
July 24, 2005 11:36PM
1929
August 03, 2005 12:34PM
1952
July 21, 2005 05:37AM
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.