MySQL taking too much CPU

Question

Below is my .cnf file and if I see my CPU consumption 99.99% of it my the mysqld commands. The MySQL server is connected from remote machines that update data in it frequently, but I make sure that the remote server's open a connection, read/write/update and then close it. Also the remote server reads a lot.

What can I do to reduce my cpu consumption. FYI, I am using 2 core CPU with 4GB RAM.

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

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

[mysqld]
user          = mysqluser
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
wait_timeout    = 20
interactive_timeout = 60

bind-address            = <IP-ADDRESS>
key_buffer            = 16M
max_allowed_packet  = 16M
thread_stack            = 192K
thread_cache_size   = 8
myisam-recover      = BACKUP
max_connections     = 300

query_cache_limit   = 20M
query_cache_size        = 128M

log_error = /var/log/mysql/error.log
log_slow_queries    = /var/log/mysql/mysql-slow.log
long_query_time = 4
log-queries-not-using-indexes

expire_logs_days    = 10
max_binlog_size         = 100M

[mysqldump]
quick
quote-names
max_allowed_packet  = 16M

[mysql]

[isamchk]
key_buffer      = 16M

Also pasting output from mysqltuner.

 >>  MySQLTuner 1.4.0 - Major Hayden <[email protected]>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
[OK] Logged in using credentials from debian maintenance account.
[OK] Currently running supported MySQL version 5.5.38-0ubuntu0.14.04.1-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM 
[--] Data in InnoDB tables: 2G (Tables: 26)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 26

-------- Performance Metrics -------------------------------------------------
[--] Up for: 21m 51s (37K q [28.525 qps], 31K conn, TX: 6M, RX: 7M)
[--] Reads / Writes: 97% / 3%
[--] Total buffers: 304.0M global + 2.7M per thread (5000 max threads)
[!!] Maximum possible memory usage: 13.4G (347% of installed RAM)
[!!] Slow queries: 18% (6K/37K)
[OK] Highest usage of available connections: 0% (16/5000)
[OK] Key buffer size / total MyISAM indexes: 16.0M/100.0K
[OK] Query cache efficiency: 20.2% (7K cached / 36K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 6K sorts)
[OK] Temporary tables created on disk: 25% (54 on disk / 215 total)
[OK] Thread cache hit rate: 99% (16 created / 31K connections)
[OK] Table cache hit rate: 25% (74 open / 289 opened)
[OK] Open file limit used: 0% (49/25K)
[OK] Table locks acquired immediately: 100% (29K immediate / 29K locks)
[!!] InnoDB  buffer pool / data size: 128.0M/2.9G
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Reduce your overall MySQL memory footprint for system stability
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    innodb_buffer_pool_size (>= 2G)

I wasn't getting this issue at all, but when I started connecting like 10 remote servers to this MySQL server and when those ten servers started doing SELECT and UPDATE queries on this single MySQL server, I see this rise in CPU consumption.

Answer 1

I suspect that you have several issues here that are stacking up and causing problems.

1. Disk i/o is fragmented, and probably inefficient. OPTIMIZE TABLE on each of your tables should sort that out and help a bit.
2. Memory is tuned horribly, likely causing issues with having to swap frequently.
3. Slow queries - this is the big suspect for your high CPU usage. I'd check your slow log and see what you can optimize by adding indexes/reorganizing queries/etc. There's probably a fairly common query thats doing tens or hundreds of thousands of comparisons that are killing your performance.

If none of those bump up performance to what you want, it may be time to scale up your database server. 2 cores doesn't tell me anything about the processing power of the machine, and 4 GB of ram is pretty tiny these days.

For more specific advice, please add more detail to your description. SSDs or rotating disks? What CPU? What class of ram? How many queries/min? How many clients, etc?

Answer 2

If you are able to play around with settings a little, I would try this in my.cnf:

innodb_buffer_pool_size = 3000M

then restart mysql and check your performance. This would allow you to keep much more of your database in memory, reducing the disk/memory thrashing that you may be seeing. Given that you have 4Gb memory, and assuming this server is ONLY a db server, you can increase the buffer pool size up to about 80% of the system memory.

Answer 3

What happens: you have try to put an elefant into the hamster's ball.

What to do: decrease the memory footprint for mysql

. . . . .
. . . . .
[mysqld]
user                    = mysqluser
pid-file                = /var/run/mysqld/mysqld.pid
socket                  = /var/run/mysqld/mysqld.sock
log_error               = /var/log/mysql/error.log
log_slow_queries        = /var/log/mysql/mysql-slow.log
long_query_time         = 10
log-queries-not-using-indexes
expire_logs_days        = 10
max_binlog_size         = 100M

port                    = 3306
basedir                 = /usr
datadir                 = /var/lib/mysql
tmpdir                  = /tmp
lc-messages-dir         = /usr/share/mysql
skip-external-locking
wait_timeout            = 20
interactive_timeout     = 60

bind-address            = <IP-ADDRESS>
myisam-recover          = BACKUP

## REASONABLE MEMORY PARAMETERS
max_connections         = 64
thread_cache_size       = 2
thread_concurrency      = 4
key_buffer_size         = 64M
join_buffer_size        = 2M
sort_buffer_size        = 2M
read_buffer_size        = 2M
read_rnd_buffer_size    = 8M
myisam_sort_buffer_size = 16M
query_cache_limit       = 2M
query_cache_size        = 32M
table_open_cache        = 512
max_allowed_packet      = 1M
. . . . .
. . . . .

Answer 4

You should analyze your most frequent queries with EXPLAIN command. The output will tell you how your queries use table indices, which directly affects the performance.