I am on a dedicated server with Intel Xeon D D-1520 4/8t 2,2 / 2,6 GHz, 32 GB of RAM DDR4 ECC 2133 MHz, and 2 x480GB of SSD SOFT. The problem I am encountering is that MySQL eats up all of my CPU power and slows down everything else. My server pilot says my total CPU usage is 92% right now, and these are the active processes.


As you can see, I have a lot of RAM left to be used, but nearly any CPU. This is my MySQL config my.cnf

port        = 3306
socket      = /var/run/mysqld/mysqld.sock

socket      = /var/run/mysqld/mysqld.sock
nice        = 0


user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql

bind-address        =

key_buffer      = 16M
max_allowed_packet  = 16M
thread_stack        = 192K
thread_cache_size       = 8
myisam-recover         = BACKUP
query_cache_type = 1
query_cache_size = 256M
query_cache_limit = 2M

log_error = /var/log/mysql/error.log

expire_logs_days    = 10
max_binlog_size         = 100M
#binlog_do_db       = include_database_name
#binlog_ignore_db   = include_database_name  

max_allowed_packet  = 16M

#no-auto-rehash # faster start of mysql but no tab completition

key_buffer      = 16M

!includedir /etc/mysql/conf.d/

my mysql_innodb.cnf only has this entry

innodb_file_per_table = 1

Now, is there anything I can do to optimize my situation?

I am completely new to managing my own server, but I figured I have to start sometime somewhere somehow. So I would sincerely appreciate any recommendation or advice.


  • @chrisPalmer...How many current connections open/how many threads? what is your innodb_buffer pool size? what kind of queries are running and how long? Are any of the queries un-optimized, using filesort, temp table, etc...?
    – BK435
    Dec 7, 2015 at 19:32
  • @BK435 earlier I killed every background process that had any MySQL queries. So right now, there are no connections, but the MySQL is still using about 30-40% of the CPU - for no queries. My innodb_buffer pool size is 134217728. No, the queries are all simple "select name from table where test=1" and normal insert queries. There are three connections and it says in the state column "Copying to tmp table".
    – Chris Palmer Breuer
    Dec 7, 2015 at 23:44
  • so your saying current cpu usage is above in the 30% range and mysql is doing nothing?
    – BK435
    Dec 8, 2015 at 0:47
  • that's right. I can restart mysql and it will go back up to the 30% range. The only thing it shows is when I SHOW PROCESSLIST it shows three connections and it says in the "state"-column "Copying to tmp table".
    – Chris Palmer Breuer
    Dec 8, 2015 at 0:52
  • 1
    High CPU almost always indicates an inefficient SELECT. Do SHOW PROCESSLIST to find it, then let's see it. Also provide SHOW CREATE TABLE; the solution may be as simple as adding a composite index.
    – Rick James
    Dec 11, 2015 at 23:58

1 Answer 1


Verify that you tables are using the InnoDB engine and not the MyISAM or ARIA engine. Concurrent writes on non InnoDB tables can cause huge CPU spikes from attempts to get full table locks.

  • I believe that locks are implemented a bit more efficiently than just busy waiting.
    – jkavalik
    Apr 27, 2016 at 5:51
  • ARIA 2.0 (next version) goals row level locking, right now it is just table level locks. So like of current writes to the same rows will certainly cause CPU spikes.
    – jozwikjp
    Apr 27, 2016 at 12:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.