Skip navigation links

MySQL Forums :: Performance :: Poor performance of MySQL 5.5


Advanced Search

Poor performance of MySQL 5.5
Posted by: Matteo Canella ()
Date: May 23, 2011 04:43AM

I have a problem with MySQL 5.5.12

Install on Windows Server 2008 R2 64 bit
4 x Intel Xeon E7520 1.87Ghz
16GB RAM

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:


[client]
port=3306
[mysql]
default-character-set=latin1
[mysqld]
port=3306
basedir="C:/Program Files/MySQL/MySQL Server 5.5/"
datadir = "D:/MySQLDataFiles/data/"
character-set-server=latin1
default-storage-engine=INNODB
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
max_connections=800
query_cache_size=350M
table_cache=1520
tmp_table_size=125M
thread_cache_size=38
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=125M
key_buffer_size=548M
read_buffer_size=64K
read_rnd_buffer_size=256K
sort_buffer_size = 1M
innodb_data_home_dir="D:/MySQLDataFiles/"
innodb_additional_mem_pool_size=22M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=11M
innodb_buffer_pool_size = 10G
innodb_log_file_size = 2000M
innodb_thread_concurrency=66
skip-name-resolve
innodb = ON
innodb_log_files_in_group = 2
innodb_read_io_threads = 16
innodb_buffer_pool_instances=1
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
slow-query-log
long_query_time = 5
slow_query_log_file = "D:/Log/MySQL/SlowQuery.log"
log-slow-slave-statements
log-slow-admin-statements
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.


Thanks,

Matteo

Options: ReplyQuote


Subject Views Written By Posted
Poor performance of MySQL 5.5 7939 Matteo Canella 05/23/2011 04:43AM
Re: Poor performance of MySQL 5.5 2891 Rick James 05/24/2011 08:14AM
Re: Poor performance of MySQL 5.5 3123 Matteo Canella 05/25/2011 04:10AM
Re: Poor performance of MySQL 5.5 3257 Rick James 05/25/2011 10:40AM
Re: Poor performance of MySQL 5.5 2119 Merlotti Nicola 05/31/2011 07:15AM
Re: Poor performance of MySQL 5.5 1854 Rick James 05/31/2011 09:03AM
Re: Poor performance of MySQL 5.5 1655 Matteo Canella 05/31/2011 11:15AM
Re: Poor performance of MySQL 5.5 2163 Matteo Canella 06/03/2011 01:44AM
Re: Poor performance of MySQL 5.5 1342 Matteo Canella 06/03/2011 02:20AM
Re: Poor performance of MySQL 5.5 2204 Rick James 06/03/2011 09:33AM
Re: Poor performance of MySQL 5.5 1670 Nicola Merlotti 06/03/2011 11:25AM
Re: Poor performance of MySQL 5.5 2530 Matteo Canella 06/09/2011 05:36AM
Re: Poor performance of MySQL 5.5 1484 Nicola Merlotti 06/03/2011 11:38AM
Re: Poor performance of MySQL 5.5 1311 Rick James 06/03/2011 07:13PM
Re: Poor performance of MySQL 5.5 1213 Vladislav Vaintroub 06/11/2011 07:30PM
Re: Poor performance of MySQL 5.5 1104 Vladislav Vaintroub 06/11/2011 07:32PM
Re: Poor performance of MySQL 5.5 1394 Matteo Canella 06/12/2011 10:09AM
Re: Poor performance of MySQL 5.5 1159 Jerry Richart 06/15/2011 01:08PM


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.