High CPU use (spikes) and Page Faults in VM
Hi All,
Having huge issues trying to resolve a problem. Not sure if this is a VM / MySql / Win problem, just know its a problem :) Any help would be appreciated, as this is making us re-think MySQL and look towards MSSQL (uggh, did I really say that?)
The problem is that our DB is running sloowww, and we cant seem to fix it.
We have a medium sized DB application with about 400 concurrent users, most doing small number (100 per 20 minute session) of small select/updates. Not a huge load. Most queries are unique, hence we have no query-cache. Have a Win 2003 Virtual server with 4Gb memory and 1 processor on a largish Xenon 8 processor host machine. The host is only running two other VMs, which are small compared to this one. We are using the latest MySQL community build - downloaded less than a week ago.
We are getting huge spikes in the virtual CPU under load, commonly going to 100% for 1/2 second, averaging about 60% load. Digging deeper we find that there are huge numbers of page faults, even tho we have used less than 1G of the 4G. On the real machine, the 8 processors are running fairly evenly at about 30% load. Also getting a large number (2000) threads created, even with a cache of 400 and even tho the number of cache threads is small (<100). If I turn off MySQL the VM load drops to about 3%.
Things we have tried:
- 'tuning' lots of MySQL parameters (our current setup is listed below)
( generally these have made subtle differences, but the basic problem remains and overshadows these differences )
- setting Windows page file size to 6G
- Trying two virtual processors instead of 1, in the VM
- using the affinity settings in the host to give more processors to the relevant VM
- using thread priority int he host to give more to the VM
- checking that all tables use suitable indexes
- semtex (ha)
Our current my.ini has these settings
default_storage_engine=INNODB
sql-mode="STRICT_TRANS_TABLES....
max_connections=800
table_cache=2400 (tried 1200)
tmp_table_size=16M
thread_cache_size=100 (tried up to 1000)
myisam_max_sort_file_size=100k ( this was 100G in ini, but 100k in Admin?)
myisam_max_extra_sort_file_size=100k ( this was 100G in ini, but 100k in Admin?)
myisam_sort_buffer_size=10M
key_buffer_size=256M (total of our isam indexes = about 40M)
read_rnd_buffer_size=256k
sort_buffer-size=256k
innodb_additional_memory_pool_size=20M
innodb_flush_log_at_trx_commit=0
innodb_log_buffer_size=1M
innodb_buffer_pool_size=500M ( tried 750M, 1000M ) (total of our inno indexes = 750M)
innodb_log_file_size=16M
connect_timeout=30 ( tried 20 )
wait_timeout=5 (tried 10, 30 )
skip-name-resolve
skip-host-cache
query_cache_type=2
query_cache_size=8k
record_buffer=256k
Can anyone out there suggest where the problem may lie and perhaps how to fix it? Has anyone else had a similar issue and found a workaround?
All help gratefully received.
Jack