Skip navigation links

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


Advanced Search

Re: Poor performance of MySQL 5.5
Posted by: Matteo Canella ()
Date: June 03, 2011 01:44AM

Hello,

I tried to install MySQL 5.6, but at the end of the installation has return an error. So I installed MySQL 5.5.13

Without HT and NUMA a better job.

But the strange thing is that when I set in applications to connect to the new server, for the first 10 minutes the processor works at 100%, then stabilized at an acceptable value.
Another strange thing is that the processors are working at 100% for 1-2 second when the data on the tables are modified. As long as the data in the tables are not changed the server works well.

Now, unlike before, all processors always work, but I would try to re-enable HT, why do not I always see all the processors to work.

I would try to install MySQL 5.1 to see if it works better. Why now the old server where there are 2 processors in less work even better than the new server with 2 processors more.

First I tried the default parameters: the processor work at 100% for more than 30 minutes, then worked at an acceptable level, but every 10/15 minutes to put 100% for 5 minutes.

So I changed the parameters:
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_log_file_size = 2000M
But it works a lot worse.
So I removed them and added
innodb_buffer_pool_instances = 2
But this also works very badly

Now the configuration that works best is this:
[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 = 150M
table_cache=1520
tmp_table_size=125M
thread_cache_size=38
myisam_max_sort_file_size = 100K
myisam_sort_buffer_size = 59M
key_buffer_size = 512M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
sort_buffer_size = 1M
innodb_data_home_dir="D:/MySQLDataFiles/"
innodb_additional_mem_pool_size = 14M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size = 6M
innodb_buffer_pool_size = 700M
innodb_log_file_size = 100M
innodb_thread_concurrency = 18
slow-query-log
long_query_time = 1
log-error = "D:/MySQLLog/Error/Error.log"
slow_query_log_file = "D:/MySQLLog/SlowQuery/SlowQuery.log"
skip-name-resolve
performance_schema = off
interactive_timeout = 10
connect_timeout = 10
wait_timeout = 10

But still I can not work permanently with the new server.
For example, right now the sites are very slow and the processor is 100% by 20 minutes.
In practice, the old server works better than new server

If I could not resolve the problem, do you think is worth seek professional help (eg Percorna)?
Or is it better that I move on MS SQL Server?

Thanks for your help,

Matteo.

Options: ReplyQuote


Subject Views Written By Posted
Poor performance of MySQL 5.5 8150 Matteo Canella 05/23/2011 04:43AM
Re: Poor performance of MySQL 5.5 2977 Rick James 05/24/2011 08:14AM
Re: Poor performance of MySQL 5.5 3199 Matteo Canella 05/25/2011 04:10AM
Re: Poor performance of MySQL 5.5 3335 Rick James 05/25/2011 10:40AM
Re: Poor performance of MySQL 5.5 2166 Merlotti Nicola 05/31/2011 07:15AM
Re: Poor performance of MySQL 5.5 1895 Rick James 05/31/2011 09:03AM
Re: Poor performance of MySQL 5.5 1703 Matteo Canella 05/31/2011 11:15AM
Re: Poor performance of MySQL 5.5 2208 Matteo Canella 06/03/2011 01:44AM
Re: Poor performance of MySQL 5.5 1384 Matteo Canella 06/03/2011 02:20AM
Re: Poor performance of MySQL 5.5 2261 Rick James 06/03/2011 09:33AM
Re: Poor performance of MySQL 5.5 1715 Nicola Merlotti 06/03/2011 11:25AM
Re: Poor performance of MySQL 5.5 2590 Matteo Canella 06/09/2011 05:36AM
Re: Poor performance of MySQL 5.5 1508 Nicola Merlotti 06/03/2011 11:38AM
Re: Poor performance of MySQL 5.5 1354 Rick James 06/03/2011 07:13PM
Re: Poor performance of MySQL 5.5 1238 Vladislav Vaintroub 06/11/2011 07:30PM
Re: Poor performance of MySQL 5.5 1125 Vladislav Vaintroub 06/11/2011 07:32PM
Re: Poor performance of MySQL 5.5 1433 Matteo Canella 06/12/2011 10:09AM
Re: Poor performance of MySQL 5.5 1185 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.