1

Currently have a MySQL database and experiencing an issue with MySQL running at 600% of the CPU usage.

How to reduce CPU usage? Changes to be made in the my.cnf file?

Specs: 2.3 GHz Intel Xeon® E5-2686 v4 (Broadwell) processors or 2.4 GHz Intel Xeon® E5-2676 v3 (Haswell) processors

8 vCPU's

32GB of RAM

100GB Hard Drive.

Instance is currently hosted with AWS, running Ubuntu 14.04.4 LTS and MySQL version 5.6.33-0ubuntu0.14.04.1-log.

Please see below my.cnf configuration:

# The MySQL database server configuration file.

# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
# 
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port        = 3306
socket      = /var/run/mysqld/mysqld.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice        = 0
log_error=/var/log/mysql/mysql_error.log

[mysqld]
#
# * Basic Settings
#
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

innodb_io_capacity = 2000
innodb_read_io_threads = 64
innodb_thread_concurrency = 0
innodb_write_io_threads = 64
innodb_buffer_pool_size = 2G
innodb_log_file_size = 1G

# Skip reverse DNS lookup of clients
skip-name-resolve

#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address        = 127.0.0.1
#
# * Fine Tuning
#
key_buffer      = 11G
max_allowed_packet  = 16M
thread_stack        = 192K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover         = BACKUP
#max_connections        = 100
#table_cache            = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit   = 1M
query_cache_size        = 16M
query_cache_type    = 0
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
general_log_file        = /var/log/mysql/mysql.log
general_log             = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/mysql_error.log
#
# Here you can see queries with especially long duration
#log_slow_queries   = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id      = 1
#log_bin            = /var/log/mysql/mysql-bin.log
expire_logs_days    = 10
max_binlog_size         = 100M
#binlog_do_db       = include_database_name
#binlog_ignore_db   = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem

[mysqldump]
quick
quote-names
max_allowed_packet  = 16M

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

[isamchk]
key_buffer      = 512M

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/

MySQLTuner output:

 >>  MySQLTuner 1.7.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

[--] Skipped version check for MySQLTuner script
[OK] Logged in using credentials from debian maintenance account.
[OK] Currently running supported MySQL version 5.6.33-0ubuntu0.14.04.1
[OK] Operating on 64-bit architecture

-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: /var/log/mysql/mysql_error.log(970K)
[OK] Log file /var/log/mysql/mysql_error.log exists
[OK] Log file /var/log/mysql/mysql_error.log is readable.
[OK] Log file /var/log/mysql/mysql_error.log is not empty
[OK] Log file /var/log/mysql/mysql_error.log is smaller than 32 Mb
[!!] /var/log/mysql/mysql_error.log contains 99 warning(s).
[!!] /var/log/mysql/mysql_error.log contains 4175 error(s).
[--] 24 start(s) detected in /var/log/mysql/mysql_error.log
[--] 1) 2017-02-24 19:42:32 2345 [Note] /usr/sbin/mysqld: ready for connections.
[--] 2) 2017-02-24 19:36:18 1132 [Note] /usr/sbin/mysqld: ready for connections.
[--] 3) 2017-02-24 19:27:45 29185 [Note] /usr/sbin/mysqld: ready for connections.
[--] 4) 2017-02-24 18:03:03 21178 [Note] /usr/sbin/mysqld: ready for connections.
[--] 5) 2017-02-24 17:51:50 19525 [Note] /usr/sbin/mysqld: ready for connections.
[--] 6) 2017-02-24 17:42:33 18098 [Note] /usr/sbin/mysqld: ready for connections.
[--] 7) 2017-02-24 17:34:56 17246 [Note] /usr/sbin/mysqld: ready for connections.
[--] 8) 2017-02-24 11:19:30 16031 [Note] /usr/sbin/mysqld: ready for connections.
[--] 9) 2017-02-24 09:12:46 28867 [Note] /usr/sbin/mysqld: ready for connections.
[--] 10) 2017-02-24 04:53:33 4854 [Note] /usr/sbin/mysqld: ready for connections.
[--] 65 shutdown(s) detected in /var/log/mysql/mysql_error.log
[--] 1) 2017-02-24 19:42:31 1132 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 2) 2017-02-24 19:35:35 29185 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 3) 2017-02-24 19:27:36 21178 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 4) 2017-02-24 18:03:01 19525 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 5) 2017-02-24 17:51:32 18098 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 6) 2017-02-24 17:42:32 17246 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 7) 2017-02-24 17:34:55 16031 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 8) 2017-02-24 11:19:28 15845 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 9) 2017-02-24 11:19:25 15659 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 10) 2017-02-24 11:19:22 15473 [Note] /usr/sbin/mysqld: Shutdown complete

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in InnoDB tables: 1G (Tables: 262)
[--] Data in MyISAM tables: 137K (Tables: 28)
[!!] Total fragmented tables: 1

-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] There is no basic password file list!

-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 1h 29m 57s (158K q [29.435 qps], 6K conn, TX: 393M, RX: 49M)
[--] Reads / Writes: 77% / 23%
[--] Binary logging is disabled
[--] Physical Memory     : 31.4G
[--] Max MySQL memory    : 5.1G
[--] Other process memory: 113.0M
[--] Total buffers: 4.5G global + 1.1M per thread (151 max threads)
[--] P_S Max memory usage: 403M
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 5.0G (15.83% of installed RAM)
[OK] Maximum possible memory usage: 5.1G (16.22% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/158K)
[OK] Highest usage of available connections: 21% (32/151)
[!!] Aborted connections: 4.62%  (299/6466)
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 61K sorts)
[OK] No joins without indexes
[OK] Temporary tables created on disk: 0% (0 on disk / 1K total)
[OK] Thread cache hit rate: 90% (588 created / 6K connections)
[OK] Table cache hit rate: 94% (123 open / 130 opened)
[OK] Open file limit used: 0% (18/5K)
[OK] Table locks acquired immediately: 100% (145K immediate / 145K locks)

-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 403.9M
[--] Sys schema isn't installed.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.2% (97M used / 536M cache)
[OK] Key buffer size / total MyISAM indexes: 512.0M/191.0K
[!!] Read Key buffer hit rate: 25.0% (4 cached / 3 reads)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 4.0G/1.1G
[OK] InnoDB log file size / InnoDB Buffer pool size: 512.0M * 2/4.0G should be equal 25%
[!!] InnoDB buffer pool instances: 8
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 100.00% (3052068189 hits/ 3052070121 total)
[!!] InnoDB Write Log efficiency: 63.45% (12468 hits/ 19649 total)
[OK] InnoDB log waits: 0.00% (0 waits / 32117 writes)

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.

-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.

-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.

-------- RocksDB Metrics ---------------------------------------------------------------------------
[--] RocksDB is disabled.

-------- Spider Metrics ----------------------------------------------------------------------------
[--] Spider is disabled.

-------- Connect Metrics ---------------------------------------------------------------------------
[--] Connect is disabled.

-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] This is a standalone server.

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Control warning line(s) into /var/log/mysql/mysql_error.log file
    Control error line(s) into /var/log/mysql/mysql_error.log file
    Run OPTIMIZE TABLE to defragment tables for better performance
      OPTIMIZE TABLE miwifi.wireless_sessions; -- can free 1791 MB
    Total freed space after theses OPTIMIZE TABLE : 1791 Mb
    MySQL started within last 24 hours - recommendations may be inaccurate
    Reduce or eliminate unclosed connections and network issues
    Consider installing Sys schema from https://github.com/mysql/mysql-sys
Variables to adjust:
    innodb_buffer_pool_instances(=4)
  • What are some of the errors in /var/log/mysql/mysql_error.log? Lower key_buffer_size to 100M; 11G is excessive. Change long_query_time to 1. – Rick James Feb 25 '17 at 5:03
2

First of all, on 32Gb machine You give for InnoDB (which I hope You use primary, if not - time to start :-) )

innodb_buffer_pool_size = 2G

change it for

innodb_buffer_pool_size = 24G (recommended for dedicated server), You can start from 10G

than after this look at loading again high cpu loading normally come from a lot of sorting operations

  • enable slow log, check what queries You have
  • even when You fix all from slow log, server can continue have high loading by the many fast, but still without proper indexes queries

You must identify them by total number of executions, and profile individually. It normally not too much, often 10-20 different queries kill 99% of server performance

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.