MySQL taking too much CPU

MySQLoptimizationperformancequery-performance

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 <major@mhtx.net>
 >>  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)

Best Answer

Ignore the "fragmented" alarm; it's bogus. Ignore the max "possible" memory; it's bogus.

Focus on the slow queries; they are causing the high CPU. What are the slow queries?

Plan A: Glance at SHOW FULL PROCESSLIST; frequently. You will see one or two queries there most of the time.

Plan B: Turn on the slowlog; set long_query_time = 2; and (after a day), run pt_query_digest. It will tell you the worst queries.

Given a bad query (or two), show us

SHOW CREATE TABLE
EXPLAIN SELECT ...

We should be able to help you dramatically shrink the CPU load.

(Contrary to what @dborba suggested, I ruled out swapping and I/O. Those tend to show up as low CPU, but high latency for queries. We may need to address it again after adding some INDEXes, etc.)

Related Solutions

Thesqld_safe version different than thesqld

As we all know, mysqld_safe and mysqld are very different

mysqld : The database server instance daemon

mysqld_safe : Control program that examines and sets the environment for mysqld to execute. The mysqld executable is actually launched in a loop. When mysqld terminates, the mysqld_safe program will examine the return results and decide whether

mysqld terminated normally (intentional shutdown), leaves mysqld_safe
mysqld terminated abnormally (crash or kill -9 of mysqld)
- Loop back, mysqld fails on retry, leaves mysqld_safe
- Loop back, mysqld starts up, stays in the mysqld_safe loop

Why is it important to have mysqld and mysqld_safe using the same MySQL version?

Let me illustrate it this way: Percona Server sometimes has additional features in mysqld_safe for manipulating the OS. For example, I have seen numactl --interleave=all in a Percona Server mysqld_safe. If that line was not there, the mysqld for Percona Server may run into issues with memory and swapping.

The same scenario could possibly be the case for Oracle's (ugh, still hate saying that) mysqld and mysqld_safe. There could be improvements from one major release to another that would be removed if the mysqld_safe was older.

Rather than exploring the possibilities of using a old mysqld_safe and a new mysqld (or vica versa), please make your life simple and reinstall MySQL 5.5.30 from scratch.

Before doing so, please run

updatedb
locate mysqld_safe

in Linux and see if there are two lingering. If there are, get the paths straightened out. Otherwise, you may have to reinstall MySQL 5.5.30.

MySQL high CPU usage (MyISAM table indexes)

SUGGESTION

Your key_buffer_size is only 16MB, but your MyISAM indexes total 59MB. In a highly-trafficked website, I can see index pages getting kicked out of and pushed into the MyISAM keycache frequently. This could have an affect on indexes being updated and overall write performance on the MyISAM tables.

You could just bump up key_buffer_size to 64 MB.

You must add this to /etc/my.cnf

[mysqld]
key_buffer_size=64M

You don't even need to restart mysql. Just run this:

SET @MB = 1024 * 1024;
SET GLOBAL key_buffer_size = @MB * 64;

You can also convert the row format of all your MyISAM tables. This will just about double the disk space used by will increase read performance 20-25%. I wrote about this before as well:

Here is a script to convert all your MyISAM tables to Fixed Row Format

SQLSTMT="SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ROW_FORMAT=Fixed;')"
SQLSTMT="${SQLSTMT} FROM information_schema.tables WHERE engine='MyISAM' AND"
SQLSTMT="${SQLSTMT} table_schema NOT IN ('information_schema','mysql','performance_schema')"
mysql -uroot -p -A --skip-column-names -e"${SQLSTMT}" > /root/MakeFixedRows.sql
mysql -uroot -p < /root/MakeFixedRows.sql

ALTERNATE SUGGESTION

Given you have a total of 488 MB of of MyISAM data and indexes, I would like to suggest the following: Switch Everything to InnoDB. I have two reasons:

REASON # 1

MyISAM only caches indexes. InnoDB caches data and indexes. See my post : What are the main differences between InnoDB and MyISAM?

This will reduce disk I/O for reading data and indexes. In your particular case, you can easily handle an InnoDB Buffer Pool of 512M and basically fit everything into RAM.

REASON # 2

The MyISAM storage engine cannot access multiple CPUs. InnoDB can. In fact, I have many posts on tweeking InnoDB for this:

Jul 23, 2012 : How to get the most out of MySQL on a QuadCore machine with 16 GB of RAM?
Sep 20, 2011 : Multi cores and MySQL Performance
Sep 12, 2011 : Possible to make MySQL use more than one core?
May 26, 2011 : About single threaded versus multithreaded databases performance

EPILOGUE

I would like you really consider the conversion from MyISAM to InnoDB