MySQL Forums
Forum List  »  Newbie

High CPU use (spikes) and Page Faults in VM
Posted by: Jack Kenyon
Date: April 18, 2009 01:28AM

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

Options: ReplyQuote


Subject
Written By
Posted
High CPU use (spikes) and Page Faults in VM
April 18, 2009 01:28AM


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.