MySQL optimizing – use ram instead of CPU

MySQL

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!

Best Answer

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.

Related Question