MySQL Forums
Forum List  »  Performance

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
15483
May 23, 2011 04:43AM
4934
May 24, 2011 08:14AM
5011
May 25, 2011 10:40AM
3205
May 31, 2011 09:03AM
3780
June 03, 2011 09:33AM
2357
June 03, 2011 07:13PM


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.