MySQL taking up nearly100% CPU all the time - how to diagnose what's wrong?

Question

MySQL is eating up nearly all the CPU all the time. How does one go about diagnosing this issue? Here are the vital statistics:

Our machine:

Windows Server 2008 R2 Enterprise

Intel G6950 2.80GHz 

4GB RAM

64-bit 

MySQL Server 5.5

ASP.NET v2.0

From my.ini:

character-set-server=latin1

default-storage-engine=INNODB

sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

max_connections=500

query_cache_size=256M

table_cache=320

tmp_table_size=195M

thread_cache_size=8

myisam_max_sort_file_size=100G

myisam_sort_buffer_size=256M

key_buffer_size=1024M

read_buffer_size=64K

read_rnd_buffer_size=256K

sort_buffer_size=256k

innodb_additional_mem_pool_size=12M

innodb_flush_log_at_trx_commit=1

innodb_log_buffer_size=7M

innodb_buffer_pool_size=1024M

innodb_log_file_size=117M

innodb_thread_concurrency=8

external-locking

The table that is accessed most often and seems to be slowing down the works is asset_search_asr

The following screenshots are the results I get from running EXPLAIN:

And SHOW INDEX:

Thank you in advance all who take the time to answer offer guidance. I am a novice so even a general idea of what to look for will be of enormous assistance.

It is essential to know what the database is being used for. Your .ini file allows 500 connections. How many connections are typically in use? What queries are being done against the "hot" table? Do the indexes on that table support the queries efficiently? Are there triggers? Etc. — hardmath, Apr 29 '13 at 01:15
Hi hardsmith - thank you for the excellent questions. The database contains metadata for assets on an ecommerce site. Typically we'll have no more than 20 concurrent users searching at any one time. The queries on the hot table are users searching for assets to purchase. I am very confused about how that table is indexed, if it is at all, which is why I posted those screenshots. I do not know if the table is set up correctly, but I expect it isn't. I do not believe there are any triggers in place at this time. — Shannon D, Apr 29 '13 at 01:31
How do you know that it is CPU-bound rather than IO-bound, or network-bound? It is very unusual for a DB server to become CPU-bound unless something very silly is going on. You have a primary key index and two full-text indices on asset_search_asr. Can you identify what queries are causing the performance bottleneck? — Pieter Geerkens, Apr 29 '13 at 02:32
A related (now closed) question is [here](http://stackoverflow.com/q/1282232/487781), with some useful suggestions and links (esp. for the case where high CPU utilization suddenly appears). The closure of that (highly rated) question is (I suspect) tied to the opening of [forum DBA.SE](http://dba.stackexchange.com/). To me there's certainly an overlap here with database programming & troubleshooting of the same, but certainly that forum represents a concentration of expertise, and it might be worth flagging your Question for moderator attention, to see if it can be tranferred. — hardmath, Apr 29 '13 at 02:49
Hi Pieter - These are great questions. I have been told by our server host that the CPU is the source of the bottleneck. I would love to know if and what kinds of queries could be responsible - and you asking that question helps me to know that that is a thing to look into. Thank you - these questions really help me to know what to look for! — Shannon D, Apr 29 '13 at 02:50
One simple task you could do is turn on slow queries loggin and see what you get! — Alexandre Lavoie, Apr 29 '13 at 03:43
Hi its a bit of work but you can use : vmware Hyperic (open source free program) http://www.hyperic.com/products/mysql-monitoring you need to install agent on your machine and server somewhere ... (can be on the same machine) — Nimrod007, Apr 29 '13 at 07:09
Hi Nimrod007 - Thank you for this recommendation - this looks great! — Shannon D, Apr 29 '13 at 17:41

MySQL taking up nearly100% CPU all the time - how to diagnose what's wrong?

0 Answers0