mathiasfk mathiasfk - 1 month ago 20
MySQL Question

MySQL Server with high CPU and "kernel time" usage

I noticed that a mysql server is with CPU at 100%, and the "kernel time" (I'm not sure what it means) is unusually high, about 70%.

There are many connections on this server (around 400) and some active queries (about 40). Would that explain this behavior? Is there something wrong or this is expected?

High kernel time
MySQL CPU usage

Edit:

As suggested by a comment, I checked the 'handler_read%' variables:

show global status like 'handler_read%'
. Here are the results:


Handler_read_first 248684
Handler_read_key 3081370400
Handler_read_last 83333
Handler_read_next 3520958058
Handler_read_prev 330
Handler_read_rnd 2210158755
Handler_read_rnd_deleted 60107588
Handler_read_rnd_next 929907565


The complete
show status
and
show variables
result is here:
https://www.dropbox.com/s/98pnd1rzgfp4jtf/server_status.txt?dl=0
https://www.dropbox.com/s/rh0m8np0mosx6tp/server_variables.txt?dl=0

Answer

The high values for handler_read_rnd* indicate that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have.

Due to syscall overhead and context switches table scans use more CPU.

Before changing parameters or invest money in hardware, I would suggest to optimize your database:

  • Activate the slow query log (additionally you might specify parameters log_queries_not_using_indexes and min_examined_row_limit) for a limited time (size of slow query log might grow very fast).
  • Analyze the queries in query log with EXPLAIN or EXPLAIN EXTENDED
  • If the problems occurs on a production server, replicate the content first to a test system