Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

We have a Windows 2003 Enterprise Edition server (64bit) running only MySQL 5.1.45 64-bit. It has 16G RAM and 10T of hard-drive space in RAID 10. We are having horrible performance from mysqld (85-100% CPU utilization). We were running a smaller machine with better performance, so I am assuming our my.ini file is not correct for our current machine. The my.ini file is as follows:

[client]
port=3306

[mysql]
default-character-set=latin1

[mysqld]
port=3306
basedir="D:/MySQL/"
datadir="D:/MySQL/data"

default-character-set=latin1
default-storage-engine=MYISAM
sql-mode=""

skip-innodb
skip-locking

max_allowed_packet = 1M
max_connections=800
myisam_max_sort_file_size=5G
myisam_sort_buffer_size=500M

table_open_cache = 512
table_cache=8000
tmp_table_size=30M

query_cache_size=50M
thread_cache_size=128
key_buffer_size=3072M
read_buffer_size=2M
read_rnd_buffer_size=16M
sort_buffer_size=2M

#replication settings (this is the master)
log-bin=log
server-id = 1

Does anyone see anything wrong with this setup? For a machine with this much RAM, why in the world would mysqld eat up so much CPU? I know we can optimize some queries, etc., but it did run okay on a smaller machine, so I am pretty sure it is the config.

Thanks in advance for any help.

share|improve this question
    
It may help to know that "opened_tables" is currently 10,250 and is going up about 100 per minute. It was over 1 million this morning before I restarted the mysqld process. –  Jonathon May 21 '10 at 18:55

2 Answers 2

up vote 1 down vote accepted

Are you using innodb or myisam tables? If you are using InnoDB then you should set the InnoDB buffer pool size. This will help by caching common queries in RAM. As Exception suggested, considering you have 16GB of RAM you should try setting MySQL to use 10-14GB of the RAM to cache common result sets and quires.

MySQL Performance Blog - For more info: http://www.mysqlperformanceblog.com/2007/11/03/choosing-innodb_buffer_pool_size/

share|improve this answer
    
We have been using strictly MyISAM tables, but we are converting several tables to InnoDB this weekend to take advantage of the row-level locking feature. Thanks for the link and advice. –  Jonathon May 24 '10 at 1:29

You should increase the read_buffer_size and sort_buffer_size. For a machine with 16GB of RAM, there's no reason to set them to the pitiful size of 2MB.

share|improve this answer
    
We updated the settings and still had pretty miserable performance. Evidently our biggest problem is with disk I/O speeds on the new server. The tweaks did help though, so thanks for all the advice. –  Jonathon May 24 '10 at 1:30

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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