1

I have a problem with MySQL on Windows Server 2008 x64.

This is the first time I've installed MySQL 5.5 with performance problems. I decided to go back to the older version that worked well with my server on Windows Server 2003 x86.

Now the MySQL service is slower than before when used on Windows Server 2003.

What would be the reason for the lower performance ?

I have a server with 2 processors (4 cores), 12 GB RAM and hard disc in RAID 5.

my my.ini

port=3306
default-character-set=latin1
port=3306
basedir="C:/Program Files/MySQL/MySQL Server 5.0/"
datadir="D:/MySQL/MySQL Server 5.0/Data/"
default-character-set=latin1
default-storage-engine=INNODB
max_connections=160
query_cache_size=200M
table_cache=939
tmp_table_size=103M
thread_cache_size=16
myisam_max_sort_file_size=100G
myisam_max_extra_sort_file_size=100G
myisam_sort_buffer_size=339M
key_buffer_size=316M
read_buffer_size=64K
read_rnd_buffer_size=256K
sort_buffer_size=256K
innodb_additional_mem_pool_size=20M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=16M
innodb_buffer_pool_size=4096M
innodb_log_file_size=512M
innodb_thread_concurrency=10
log-slow-queries="D:/MySQLLog/SlowQuery/SlowQuery.log"
log-error="D:/MySQLLog/Error/error.log"

Thanks in advance!

Improve this question

1 Answer 1

Reset to default
1

There are three(3) aspects you need to consider

ASPECT #1

You may find this astonishing but did you know that some older versions of MySQL can outperform newer versions ? You must tune MySQL 5.5 properly. In fact, InnoDB has new options introduced into the InnoDB Plugin.

I have earlier articles in the StackExchange on this

ASPECT #2

Looking at the my.ini, something caught my attention: you have innodb_thread_concurrency=10. That's a definite no-no, especially with MySQL 5.5. I say this because MySQL 5.x thrives on setting the innodb_thread_concurrency to 0. At Percona Live NYC 2011, Ronald Bradford explicit told me as I sat in the audience not to set this to any other value than 0. By setting it to zero, it lets InnoDB figure out the correct number of threads it needs create.

ASPECT #3

You need to activate multiple cores for InnoDB. MySQL 5.0/5.1 are not designed to use multiple cores. The InnoDB Plugin (from MySQL 5.1.38 has new options for doing so. Those options are fully present in MySQL 5.5. Such include

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.