3

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.

error

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

[client]
port        = 3306
socket      = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]

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
skip-external-locking

bind-address        = 127.0.0.1

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  

[mysqldump]
quick
quote-names
max_allowed_packet  = 16M

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

[isamchk]
key_buffer      = 16M

!includedir /etc/mysql/conf.d/

my mysql_innodb.cnf only has this entry

[mysqld]
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.

Thanks!

migrated from stackoverflow.com Dec 9 '15 at 14:39

This question came from our site for professional and enthusiast programmers.

  • @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 '15 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 '15 at 23:44
  • so your saying current cpu usage is above in the 30% range and mysql is doing nothing? – BK435 Dec 8 '15 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 '15 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 '15 at 23:58
-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 '16 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 '16 at 12:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy