UPDATE 6/23/2020:
Haven't noticed any odd CPU spikes lately, but noticed that the MySQL memory footprint grows indefinitely until it goes OOM and is killed by the kernel. Where should I start looking first?
I'm looking for some MySQL tuning help. It seems that every so often mysqld process' CPU consumption shoots to way over 100%, and I'm thinking it's likely due to poor configuration.
This server is used to host about 50 accounts along with emails, mostly simple wordpress/joomla installs, along with 5 Magento 1.9 installations.
On a linode dedicated server:
16x AMD EPYC 7501 Cores
32 GB RAM
640 GB SSD Space
7 TB Bandwidth
PROCESSLIST: https://pastebin.com/8y12Kbj5
GLOBAL STATUS: https://pastebin.com/LgY6RMT3
GLOBAL VARIABLES: https://pastebin.com/VafyvKaQ
ulimit -a
core file size (blocks, -c) 0
data seg size (kbytes, -d) unlimited
scheduling priority (-e) 0
file size (blocks, -f) unlimited
pending signals (-i) 128365
max locked memory (kbytes, -l) 64
max memory size (kbytes, -m) unlimited
open files (-n) 1024
pipe size (512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
real-time priority (-r) 0
stack size (kbytes, -s) 8192
cpu time (seconds, -t) unlimited
max user processes (-u) 128365
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited
iostat -xm 5 3
avg-cpu: %user %nice %system %iowait %steal %idle
0.92 0.13 0.23 0.02 0.01 98.69
Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
loop0 0.00 0.00 0.00 0.59 0.00 0.00 13.75 0.00 0.28 1.15 0.27 0.49 0.03
sda 0.61 18.91 12.09 13.38 0.56 0.69 100.41 0.00 0.56 0.29 0.81 0.48 1.22
sdb 0.00 0.03 0.00 0.00 0.00 0.00 70.58 0.00 2.53 0.39 3.41 3.46 0.00
avg-cpu: %user %nice %system %iowait %steal %idle
7.01 0.00 0.89 0.01 0.01 92.07
Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
loop0 0.00 0.00 0.00 1.80 0.00 0.01 8.00 0.00 0.22 0.00 0.22 0.78 0.14
sda 0.00 31.60 0.00 24.60 0.00 0.39 32.33 0.00 0.66 0.00 0.66 0.46 1.12
sdb 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
avg-cpu: %user %nice %system %iowait %steal %idle
0.51 0.00 0.13 0.01 0.00 99.35
Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await r_await w_await svctm %util
loop0 0.00 0.00 0.00 1.80 0.00 0.01 8.00 0.00 0.33 0.00 0.33 0.33 0.06
sda 0.00 11.20 0.00 29.80 0.00 0.30 20.83 0.00 0.92 0.00 0.92 0.20 0.60
sdb 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
Server version: 5.7.29-log MySQL Community Server (GPL)
My.cnf:
[mysqld]
# Required Settings
basedir = /usr/
bind_address = *
datadir = /var/lib/mysql/
max_allowed_packet = 256M
max_connect_errors = 1000000
port = 3306
skip_external_locking
socket = /var/lib/mysql/mysql.sock
tmpdir = /tmp
user = mysql
performance_schema = 1 # FROM 0 to enable better diagnostics
sql_mode = ""
# InnoDB Settings
default_storage_engine = InnoDB
innodb_buffer_pool_instances = 18 # FROM 1
# Update from 1G > 12G
innodb_buffer_pool_size = 18G
# ADDED 3/23/2020 IGNT
innodb_log_file_size = 2G
innodb_file_per_table = 1
innodb_flush_method = O_DIRECT
innodb_log_buffer_size = 16M
innodb_log_files_in_group = 2
innodb_stats_on_metadata = 0
#innodb_thread_concurrency = 15
innodb_read_io_threads = 8
innodb_write_io_threads = 8
# MyISAM Settings
query_cache_limit = 0 # from 4M
query_cache_size = 0 # from 128M
query_cache_type = 0 # from 1
low_priority_updates = 1
concurrent_insert = 2
# Connection Settings
max_connections = 150
# Buffer Settings
# IGNT DISABLED ALL TO CHECK
join_buffer_size = 128M
#read_buffer_size = 32M
#read_rnd_buffer_size = 64M
#sort_buffer_size = 64M
open_files_limit = 20000 # should be greater than table_open_cache
# Search Settings
ft_min_word_len = 4
# Logging
log_error = /var/lib/mysql/mysql_error.log
long_query_time = 5
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/mysql_slow.log
# Custom ADDS IGNT
#skip-name-resolve
innodb_flush_log_at_trx_commit = 2
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
key_buffer_size = 256M
table_open_cache = 10000
table_definition_cache = 10000
tmp_table_size = 256M
max_heap_table_size = 256M
innodb_buffer_pool_instances = 12
MySQLtuner.pl results: (UPDATED 3/31/2020 8:48PM EST)
>> MySQLTuner 1.7.19 - 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] Currently running supported MySQL version 5.7.29-log
[OK] Operating on 64-bit architecture
-------- Log file Recommendations ------------------------------------------------------------------
[OK] Log file /var/lib/mysql/mysql_error.log exists
[--] Log file: /var/lib/mysql/mysql_error.log(692K)
[OK] Log file /var/lib/mysql/mysql_error.log is readable.
[OK] Log file /var/lib/mysql/mysql_error.log is not empty
[OK] Log file /var/lib/mysql/mysql_error.log is smaller than 32 Mb
[!!] /var/lib/mysql/mysql_error.log contains 2700 warning(s).
[!!] /var/lib/mysql/mysql_error.log contains 344 error(s).
[--] 32 start(s) detected in /var/lib/mysql/mysql_error.log
[--] 1) 2020-03-24T09:58:01.690843Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 2) 2020-03-24T04:50:09.880286Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 3) 2020-03-24T04:48:14.253324Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 4) 2020-03-24T04:33:47.976873Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 5) 2020-03-24T04:20:51.868881Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 6) 2020-03-24T04:20:46.399280Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 7) 2020-03-23T12:05:22.733706Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 8) 2020-03-23T05:52:41.442704Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 9) 2020-03-23T05:48:46.305524Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 10) 2020-03-23T05:47:42.353869Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 47 shutdown(s) detected in /var/lib/mysql/mysql_error.log
[--] 1) 2020-03-24T09:57:57.724742Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 2) 2020-03-24T04:50:06.601039Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 3) 2020-03-24T04:48:04.688209Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 4) 2020-03-24T04:33:44.609318Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 5) 2020-03-24T04:20:48.549320Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 6) 2020-03-24T04:20:42.966162Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 7) 2020-03-23T12:05:19.945269Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 8) 2020-03-23T05:52:38.732313Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 9) 2020-03-23T05:48:43.664346Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 10) 2020-03-23T05:47:39.754910Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in MyISAM tables: 169.9M (Tables: 811)
[--] Data in InnoDB tables: 1.6G (Tables: 5313)
[--] Data in MEMORY tables: 0B (Tables: 169)
[OK] Total fragmented tables: 0
-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.
-------- 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: 7d 14h 46m 59s (35M q [53.712 qps], 464K conn, TX: 62G, RX: 9G)
[--] Reads / Writes: 96% / 4%
[--] Binary logging is disabled
[--] Physical Memory : 31.3G
[--] Max MySQL memory : 74.9G
[--] Other process memory: 0B
[--] Total buffers: 18.5G global + 384.9M per thread (150 max threads)
[--] P_S Max memory usage: 72B
[--] Galera GCache Max memory usage: 0B
[!!] Maximum reached memory usage: 67.4G (214.94% of installed RAM)
[!!] Maximum possible memory usage: 74.9G (238.92% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (3K/35M)
[!!] Highest connection usage: 86% (130/150)
[OK] Aborted connections: 0.03% (134/464170)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (3K temp sorts / 9M sorts)
[!!] Joins performed without indexes: 70704
[!!] Temporary tables created on disk: 56% (3M on disk / 6M total)
[OK] Thread cache hit rate: 99% (658 created / 464K connections)
[!!] Table cache hit rate: 0% (7K open / 1M opened)
[OK] table_definition_cache(10000) is upper than number of tables(6572)
[OK] Open file limit used: 7% (1K/15K)
[OK] Table locks acquired immediately: 99% (9M immediate / 9M locks)
-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 72B
[--] Sys schema is installed.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 23.3% (62M used / 268M cache)
[OK] Key buffer size / total MyISAM indexes: 256.0M/27.0M
[OK] Read Key buffer hit rate: 100.0% (90M cached / 36K reads)
[!!] Write Key buffer hit rate: 73.2% (447K cached / 327K writes)
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 18.0G/1.6G
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 2.0G * 2/18.0G should be equal to 25%
[!!] InnoDB buffer pool instances: 12
[--] Number of InnoDB Buffer Pool Chunk : 144 for 12 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 100.00% (11369995313 hits/ 11370050077 total)
[!!] InnoDB Write Log efficiency: 68.92% (1625414 hits/ 2358577 total)
[OK] InnoDB log waits: 0.00% (0 waits / 733163 writes)
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.
-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.
-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.
-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.
-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] Binlog format: ROW
[--] XA support enabled: ON
[--] Semi synchronous replication Master: Not Activated
[--] Semi synchronous replication Slave: Not Activated
[--] This is a standalone server
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Control warning line(s) into /var/lib/mysql/mysql_error.log file
Control error line(s) into /var/lib/mysql/mysql_error.log file
Reduce your overall MySQL memory footprint for system stability
Dedicate this server to your database for highest performance.
Reduce or eliminate persistent connections to reduce connection usage
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.
See https://dev.mysql.com/doc/internals/en/join-buffer-size.html
(specially the conclusions at the bottom of the page).
Temporary table size is already large - reduce result set size
Reduce your SELECT DISTINCT queries without LIMIT clauses
Increase table_open_cache gradually to avoid file descriptor limits
Read this before increasing table_open_cache over 64: https://bit.ly/2Fulv7r
Read this before increasing for MariaDB https://mariadb.com/kb/en/library/optimizing-table_open_cache/
This is MyISAM only table_cache scalability problem, InnoDB not affected.
See more details here: https://bugs.mysql.com/bug.php?id=49177
This bug already fixed in MySQL 5.7.9 and newer MySQL versions.
Beware that open_files_limit (15000) variable
should be greater than table_open_cache (7420)
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
max_connections (> 150)
wait_timeout (< 28800)
interactive_timeout (< 28800)
join_buffer_size (> 128.0M, or always use indexes with JOINs)
table_open_cache (> 7420)
innodb_buffer_pool_instances(=18)
Best Answer
Your biggest problem currently is the table_open_cache size. This can be change dynamicly
SET GLOBAL table_open_cache = 10000(account for your open_file_limit of 15/16k as each open item needs a file descriptor).I can't see what version you are using however if mariadb, look at its documentation otherwise mysql documentation (adjust URL to major version 5.7/8.0).
Innodb buffer pool/innodb_buffer_pool_instances isn't helping the pool is significantly larger than your datasize. (could be reduced down to ~4G - no harm being big, just a bit wasteful).
Look at setting
long_query_timeto 1 second or less and enable the slow query log. The mysql-tuner results on tmp table usage and joins not using index show there are a number of queries performing badly due to poor indexing. Once you have some common ones of these identified ask new questions about them, showingEXPLAIN {query}, the query andSHOW CREATE TABLE {tablename}for the tables involved. https://www.percona.com/doc/percona-toolkit/LATEST/pt-query-digest.html will help prioritize the slow query log results.Please state your MySQL version and OS if you need further clarification.