Poor performance of MySQL 5.5
I have a problem with MySQL 5.5.12
Install on Windows Server 2008 R2 64 bit
4 x Intel Xeon E7520 1.87Ghz
The Server is dedicated to MySQL.
I transferred the database from a less powerful server, where it was installed version 5.1 and MySQL running on Windows Server 2003 with 2 x Intel Xeon processors and 4GB of RAM.
10 databases installed, the total weight of the db is about 500MB, all the tables within them are of type InnoDB, but I do not use transitions, I choice innodb only for performance.
The old server was responding to about 300 queries per second, with 80 simultaneous connections and a traffic of about 700KB/s.
Before transferring the database on the new server, I set the same parameters of the old server to my.ini, increasing as some paramatri query_cache_size, key_buffer_size, ecc.. after installation already proposed higher.
When I transferred the database to the new server, to my great surprise I noticed that the processor remains at 100% and the database was very slow.
Then I modified the my.ini file with the following parameters:
basedir="C:/Program Files/MySQL/MySQL Server 5.5/"
datadir = "D:/MySQLDataFiles/data/"
sort_buffer_size = 1M
innodb_buffer_pool_size = 10G
innodb_log_file_size = 2000M
innodb = ON
innodb_log_files_in_group = 2
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_io_capacity = 500
innodb_max_dirty_pages_pct = 60
innodb_purge_threads = 1
innodb_adaptive_flushing = 0
innodb_doublewrite = 1
long_query_time = 5
slow_query_log_file = "D:/Log/MySQL/SlowQuery.log"
log-error = "D:/Log/MySQL/Error.log"
The situation seems to be slightly improved, but when you run SELECT queries on large tables, including one of about 300MB, the query is returned immediately, but later MySQL deals with all the resources to at least 5 minutes, so MySQL is very slow for all other requests.
How can I fix it?
It seems very strange that with a more powerful server MySQL deal more resources.