Mysql
流量減少，但 Mysql 仍然達到 200%
我用較少的流量執行 WordPress，線上 5 或 6，但 mysql 直到達到 200%，雖然有時它會下降到 100 或更少，但它仍然會再次上升到 200%，最有趣的是有沒有查詢達到 2，它只有 0 或 1，但我仍然達到 200%。
下面是我的.cnf
[mysqld] log-error=/var/lib/mysql/vmi195323.contaboserver.net.err #slow_query_log=1 performance_schema = ON #collation-server = utf8_unicode_ci #init-connect='SET NAMES utf8' #character-set-server = utf8 #default-storage-engine=MyISAM #max_allowed_packet=268435456 max_allowed_packet = 32M open_files_limit=30000 symbolic-links=0 skip-name-resolve=1 #skip-external-locking key_buffer_size = 2G #table_open_cache = 4192 #join_buffer_size = 256M #sort_buffer_size = 8M #read_buffer_size = 8M #read_rnd_buffer_size = 8M myisam_sort_buffer_size = 16M query_cache_type = 0 #query_cache_limit = 4M query_cache_size = 0 expire_logs_days=5 # from 0 for limited historical logs key_cache_age_threshold=64800 # from 300 seconds discard to RD again key_cache_division_limit=50 # for HOT and WARM usage boundary key_cache_block_size=16384 #thread_concurrency = 8 #thread_cache_size = 100 tmp_table_size = 72M max_heap_table_size = 72M innodb_flush_neighbors=0 innodb_io_capacity=15000 innodb_buffer_pool_size = 7G # (adjust value here, 50%-70% of total RAM) innodb_log_file_size = 960M innodb_log_buffer_size=96M innodb_log_files_in_group = 2 innodb_lru_scan_depth=100 log_warnings=2 max_connect_errors=100 #have_symlink=NO innodb_purge_threads=4 innodb_read_io_threads=64 innodb_write_io_threads=64 innodb_thread_concurrency=0 innodb_flushing_avg_loops=5 innodb_adaptive_max_sleep_delay=10000 innodb_buffer_pool_instances = 7 innodb_flush_log_at_trx_commit = 1 # may change to 2 or 0 innodb_flush_method = O_DIRECT max_connections=300 #max_user_connections=30 #wait_timeout=40 #interactive_timeout=10 #long_query_time=5下面是mysql mysqltuner
>> MySQLTuner 1.7.10 - 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 10.2.17-MariaDB-log [OK] Operating on 64-bit architecture -------- Log file Recommendations ------------------------------------------------------------------ [--] Log file: /var/lib/mysql/vmi195323.contaboserver.net.err(12M) [OK] Log file /var/lib/mysql/vmi195323.contaboserver.net.err exists [OK] Log file /var/lib/mysql/vmi195323.contaboserver.net.err is readable. [OK] Log file /var/lib/mysql/vmi195323.contaboserver.net.err is not empty [OK] Log file /var/lib/mysql/vmi195323.contaboserver.net.err is smaller than 32 Mb [!!] /var/lib/mysql/vmi195323.contaboserver.net.err contains 73427 warning(s). [!!] /var/lib/mysql/vmi195323.contaboserver.net.err contains 107 error(s). [--] 29 start(s) detected in /var/lib/mysql/vmi195323.contaboserver.net.err [--] 1) 2018-11-09 23:43:25 140033329612992 [Note] /usr/sbin/mysqld: ready for connections. [--] 2) 2018-11-09 23:14:40 140049123117248 [Note] /usr/sbin/mysqld: ready for connections. [--] 3) 2018-10-31 17:44:53 140575945570496 [Note] /usr/sbin/mysqld: ready for connections. [--] 4) 2018-10-31 0:52:21 139742272645312 [Note] /usr/sbin/mysqld: ready for connections. [--] 5) 2018-10-29 15:40:13 140332307847360 [Note] /usr/sbin/mysqld: ready for connections. [--] 6) 2018-10-22 14:11:57 140273926736064 [Note] /usr/sbin/mysqld: ready for connections. [--] 7) 2018-10-21 4:45:56 139694061492416 [Note] /usr/sbin/mysqld: ready for connections. [--] 8) 2018-10-20 21:58:09 139845201819840 [Note] /usr/sbin/mysqld: ready for connections. [--] 9) 2018-10-05 6:53:08 139745408678080 [Note] /usr/sbin/mysqld: ready for connections. [--] 10) 2018-10-05 4:28:22 140625873856704 [Note] /usr/sbin/mysqld: ready for connections. [--] 17 shutdown(s) detected in /var/lib/mysql/vmi195323.contaboserver.net.err [--] 1) 2018-11-09 23:07:31 140567153977088 [Note] /usr/sbin/mysqld: Shutdown complete [--] 2) 2018-10-31 18:44:15 139731651454720 [Note] /usr/sbin/mysqld: Shutdown complete [--] 3) 2018-09-18 12:04:37 140170985395968 [Note] /usr/sbin/mysqld: Shutdown complete [--] 4) 2018-09-09 23:12:43 140009693865728 [Note] /usr/sbin/mysqld: Shutdown complete [--] 5) 2018-09-09 23:11:36 140024525231872 [Note] /usr/sbin/mysqld: Shutdown complete [--] 6) 2018-09-09 23:10:42 139645632177920 [Note] /usr/sbin/mysqld: Shutdown complete [--] 7) 2018-09-09 23:05:23 140597407938304 [Note] /usr/sbin/mysqld: Shutdown complete [--] 8) 2018-09-09 22:51:00 139671733114624 [Note] /usr/sbin/mysqld: Shutdown complete [--] 9) 2018-09-09 22:47:41 140515561195264 [Note] /usr/sbin/mysqld: Shutdown complete [--] 10) 2018-09-09 22:46:17 139697642653440 [Note] /usr/sbin/mysqld: Shutdown complete -------- Storage Engine Statistics ----------------------------------------------------------------- [--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE [--] Data in MyISAM tables: 408.5M (Tables: 33) [--] Data in InnoDB tables: 2.4G (Tables: 56) [OK] Total fragmented tables: 0 -------- Security Recommendations ------------------------------------------------------------------ [OK] There are no anonymous accounts for any database users [!!] User 'root@localhost' has no password set. [!!] User 'oybqgudp_mimo@%' does not specify hostname restrictions. [!!] There is no basic password file list! -------- CVE Security Recommendations -------------------------------------------------------------- [--] Skipped due to --cvefile option undefined -------- Performance Metrics ----------------------------------------------------------------------- [--] Up for: 1d 8h 19m 34s (9M q [83.127 qps], 148K conn, TX: 35G, RX: 2G) [--] Reads / Writes: 89% / 11% [--] Binary logging is disabled [--] Physical Memory : 15.5G [--] Max MySQL memory : 10.3G [--] Other process memory: 1.4G [--] Total buffers: 9.3G global + 2.9M per thread (300 max threads) [--] P_S Max memory usage: 112M [--] Galera GCache Max memory usage: 0B [OK] Maximum reached memory usage: 9.4G (60.86% of installed RAM) [OK] Maximum possible memory usage: 10.3G (66.08% of installed RAM) [OK] Overall possible memory usage with other process is compatible with memory available [OK] Slow queries: 0% (3/9M) [OK] Highest usage of available connections: 5% (15/300) [OK] Aborted connections: 0.12% (176/148645) [OK] Query cache is disabled by default due to mutex contention on multiprocessor machines. [OK] Sorts requiring temporary tables: 0% (1 temp sorts / 1M sorts) [OK] No joins without indexes [!!] Temporary tables created on disk: 54% (350K on disk / 644K total) [OK] Thread cache hit rate: 99% (19 created / 148K connections) [OK] Table cache hit rate: 97% (228 open / 234 opened) [OK] Open file limit used: 1% (107/10K) [OK] Table locks acquired immediately: 100% (65K immediate / 65K locks) -------- Performance schema ------------------------------------------------------------------------ [--] Memory used by P_S: 112.5M [--] Sys schema is installed. -------- ThreadPool Metrics ------------------------------------------------------------------------ [--] ThreadPool stat is enabled. [--] Thread Pool Size: 6 thread(s). [--] Using default value is good enough for your version (10.2.17-MariaDB-log) -------- MyISAM Metrics ---------------------------------------------------------------------------- [!!] Key buffer used: 1.4% (30M used / 2B cache) [OK] Key buffer size / total MyISAM indexes: 2.0G/16.5M [OK] Read Key buffer hit rate: 99.9% (31K cached / 22 reads) [OK] Write Key buffer hit rate: 98.9% (8K cached / 8K writes) -------- InnoDB Metrics ---------------------------------------------------------------------------- [--] InnoDB is enabled. [--] InnoDB Thread Concurrency: 0 [OK] InnoDB File per table is activated [OK] InnoDB buffer pool / data size: 7.0G/2.4G [OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 960.0M * 2/7.0G should be equal 25% [OK] InnoDB buffer pool instances: 7 [--] Number of InnoDB Buffer Pool Chunk : 56 for 7 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% (39506897899 hits/ 39506970499 total) [!!] InnoDB Write Log efficiency: 59.16% (373182 hits/ 630764 total) [OK] InnoDB log waits: 0.00% (0 waits / 257582 writes) -------- AriaDB Metrics ---------------------------------------------------------------------------- [--] AriaDB is enabled. [OK] Aria pagecache size / total Aria indexes: 128.0M/1B [OK] Aria pagecache hit rate: 95.3% (7M cached / 350K reads) -------- 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. [--] Binlog format: MIXED [--] 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/vmi195323.contaboserver.net.err file Control error line(s) into /var/lib/mysql/vmi195323.contaboserver.net.err file Set up a Password for user with the following SQL statement ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); ) Restrict Host for user@% to user@SpecificDNSorIp When making adjustments, make tmp_table_size/max_heap_table_size equal Reduce your SELECT DISTINCT queries which have no LIMIT clause Variables to adjust: tmp_table_size (> 72M) max_heap_table_size (> 72M)
高 CPU 幾乎總是意味著查詢效率低下。
一種定位方法是通過
SHOW FULL PROCESSLIST;；你可能會抓住它。另一種方法是打開slowlog，等待一天，分析日誌（pt-query-digest），然後尋求幫助。更多在這裡。由於你沒有使用 MyISAM，所以
key_buffer_size從 2G 降低到 50M。這不會加快速度，但可能會避免浪費可以更好地用於其他用途的 RAM。WP 的一個已知問題是它的 postmeta 模式非常低效。請參閱this了解如何加快速度。（在不知道您的特定慢查詢的情況下，我不能說這是否是“答案”。）
建議您的 MySQL 實例執行一整天
SET GLOBAL innodb_stats_auto_recalc=0; SET GLOBAL innodb_stats_persistent=0;根據 Schwartz、Zaitsev 和 Thachenko (O’Reilly) 第 197 頁的高性能 MySQL，以避免在工作日期間自動重新索引（CPU 峰值）。使用 OPTIMIZE（建議每週一次）繼續定期管理您的 TABLE DEFRAG。
和 SET GLOBAL innodb_stats_sample_pages=32;
更準確的索引基數。
如果沒有造成任何傷害，請對您的 my.cnf 進行這些更改，以使下一次 STOP/START 生效。