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.
Best Answer
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/