Mysql
需要 MySQL 優化指導
2020 年 6 月 23 日更新：
最近沒有註意到任何奇怪的 CPU 峰值，但注意到 MySQL 記憶體佔用量無限增長，直到它進入 OOM 並被核心殺死。我應該首先從哪裡開始尋找？
我正在尋找一些 MySQL 調整幫助。似乎每隔一段時間 mysqld 程序的 CPU 消耗就會超過 100%，我認為這可能是由於配置不當造成的。
該伺服器用於託管大約 50 個帳戶以及電子郵件，主要是簡單的 wordpress/joomla 安裝，以及 5 個 Magento 1.9 安裝。
在 linode 專用伺服器上：
16x AMD EPYC 7501 Cores 32 GB RAM 640 GB SSD Space 7 TB Bandwidth程序列表：https ://pastebin.com/8y12Kbj5
全球狀態：https ://pastebin.com/LgY6RMT3
全域變數：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) unlimitediostat -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)我的.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 = 12MySQLtuner.pl 結果：（美國東部標準時間 2020 年 3 月 31 日晚上 8:48 更新）
>> 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)
您目前最大的問題是 table_open_cache 大小。這可以動態更改
SET GLOBAL table_open_cache = 10000（說明您的 open_file_limit 為 15/16k，因為每個打開的項目都需要一個文件描述符）。我看不到您使用的是什麼版本，但是如果是 mariadb，請查看其文件，否則請查看mysql 文件（將 URL 調整為主要版本 5.7/8.0）。
Innodb 緩衝池/innodb_buffer_pool_instances 沒有幫助池明顯大於您的數據大小。（可以減少到〜4G - 沒有害處很大，只是有點浪費）。
查看設置
long_query_time為 1 秒或更短，並啟用慢查詢日誌。mysql-tuner 對 tmp 表使用情況和未使用索引的連接的結果顯示，由於索引不佳，許多查詢性能不佳。一旦您確定了其中一些常見的問題，請詢問有關它們的新問題，顯示EXPLAIN {query}，查詢和SHOW CREATE TABLE {tablename}所涉及的表。https://www.percona.com/doc/percona-toolkit/LATEST/pt-query-digest.html將有助於優先考慮慢查詢日誌結果。如果您需要進一步說明，請說明您的 MySQL 版本和作業系統。
為了使 ulimit -n 128000 在作業系統關閉/重新啟動或電源故障時保持不變，此 URL 是一個概述。您的要求可能會有所不同，具體取決於作業系統的具體情況。
請不要將此 URL 中列出的限制設置為 500,000。你現在應該擅長 128000。
https://glassonionblog.wordpress.com/2013/01/27/increase-ulimit-and-file-descriptors-limit/
28,000 個緩衝是為 MySQL 以外的應用程序提供文件句柄。
每秒速率 = RPS
對您的 my.cnf 的建議
$$ mysqld $$部分
open_files_limit=100000 # from whatever you have today - I see varying #'s innodb_open_files=64000 # from 7420 reported in SHOW GLOBAL STATUS; table_open_cache=64000 # from 7420 reported in SHOW GLOBAL STATUS; max_connect_errors=10 # from 1000000 - no point in allowing hacker/cracker abuse thread_cache_size=100 # from 9 to reduce threads_created count read_rnd_buffer_size=128K # from 256K to reduce handler_read_rnd_next RPS of 109,046這些更改將最大程度地減少每小時打開約 6000 個表的工作量（目前 RPhr）
還有更多全域變數機會來提高您的實例性能。
訪問我們的網站 Utility Scripts 下載免費的 Utility Scripts 以幫助進行性能調整。您將需要 findfragtables.sql 和 find-redundant-indexes.sql 非常有助於減少數據表和索引所需的 CPU 週期使用和儲存。