Mysql
求關於mysql優化的建議
我正在執行一個包含大約 20-25 個站點的專用伺服器，幾乎所有站點都執行 wordpress 安裝。通過 cpanel 設置執行它。有一段時間它主要是 mysql 吃掉了大部分的 cpu 並達到了高負載時間
mysql 0 61.69(cpu) 6.92(ram) /usr/sbin/mysqld伺服器配置是
Uptime 70 days Operating System CentOS Linux 7 (Core) x64 File Handles 14560 of 6511967 Processes 342 CPU Model AMD Ryzen 5 3600 6-Core Processor Ram 64GB我正在嘗試改進這一點並遇到了 mysql 調諧器，這是在使用性能模式執行 mysql 2 天或更長時間後必須說的。這不完全是我的強項，所以 mycnf 只是我嘗試使用舊的 mysqltuner 建議的混合體，但我聽說該應用程序仍然需要人工操作。
希望能在優化設置方面提供一些幫助。
>> 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 10.3.27-MariaDB-log [OK] Operating on 64-bit architecture -------- Log file Recommendations ------------------------------------------------------------------ [OK] Log file /var/lib/mysql/server.name.here.err exists [--] Log file: /var/lib/mysql/server.name.here.err(4M) [OK] Log file /var/lib/mysql/server.name.here.err is readable. [OK] Log file /var/lib/mysql/server.name.here.err is not empty [OK] Log file /var/lib/mysql/server.name.here.err is smaller than 32 Mb [!!] /var/lib/mysql/server.name.here.err contains 31430 warning(s). [!!] /var/lib/mysql/server.name.here.err contains 23132 error(s). [--] 60 start(s) detected in /var/lib/mysql/server.name.here.err [--] 1) 2020-12-07 7:35:16 0 [Note] /usr/sbin/mysqld: ready for connections. [--] 2) 2020-12-01 14:35:35 0 [Note] /usr/sbin/mysqld: ready for connections. [--] 3) 2020-11-30 16:10:14 0 [Note] /usr/sbin/mysqld: ready for connections. [--] 4) 2020-11-30 16:07:53 0 [Note] /usr/sbin/mysqld: ready for connections. [--] 5) 2020-11-01 1:57:12 0 [Note] /usr/sbin/mysqld: ready for connections. [--] 6) 2020-10-10 19:28:45 0 [Note] /usr/sbin/mysqld: ready for connections. [--] 7) 2020-10-10 19:28:32 0 [Note] /usr/sbin/mysqld: ready for connections. [--] 8) 2020-09-30 3:36:14 0 [Note] /usr/sbin/mysqld: ready for connections. [--] 9) 2020-09-28 17:58:16 0 [Note] /usr/sbin/mysqld: ready for connections. [--] 10) 2020-09-25 18:38:33 0 [Note] /usr/sbin/mysqld: ready for connections. [--] 51 shutdown(s) detected in /var/lib/mysql/server.name.here.err [--] 1) 2020-12-07 7:35:07 0 [Note] /usr/sbin/mysqld: Shutdown complete [--] 2) 2020-12-01 14:35:27 0 [Note] /usr/sbin/mysqld: Shutdown complete [--] 3) 2020-11-30 16:09:53 0 [Note] /usr/sbin/mysqld: Shutdown complete [--] 4) 2020-11-30 16:07:33 0 [Note] /usr/sbin/mysqld: Shutdown complete [--] 5) 2020-11-01 1:57:09 0 [Note] /usr/sbin/mysqld: Shutdown complete [--] 6) 2020-10-10 19:28:39 0 [Note] /usr/sbin/mysqld: Shutdown complete [--] 7) 2020-10-10 19:28:26 0 [Note] /usr/sbin/mysqld: Shutdown complete [--] 8) 2020-09-30 3:34:34 0 [Note] /usr/sbin/mysqld: Shutdown complete [--] 9) 2020-09-28 17:56:38 0 [Note] /usr/sbin/mysqld: Shutdown complete [--] 10) 2020-09-25 18:36:55 0 [Note] /usr/sbin/mysqld: Shutdown complete -------- Storage Engine Statistics ----------------------------------------------------------------- [--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE [--] Data in MyISAM tables: 2.1G (Tables: 1387) [--] Data in InnoDB tables: 3.2G (Tables: 2207) [--] Data in MEMORY tables: 586.4K (Tables: 3) [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: 2d 5h 57m 24s (167M q [862.613 qps], 2M conn, TX: 21024G, RX: 379G) [--] Reads / Writes: 98% / 2% [--] Binary logging is disabled [--] Physical Memory : 62.8G [--] Max MySQL memory : 43.5G [--] Other process memory: 0B [--] Total buffers: 5.0G global + 260.7M per thread (151 max threads) [--] P_S Max memory usage: 104M [--] Galera GCache Max memory usage: 0B [OK] Maximum reached memory usage: 26.5G (42.17% of installed RAM) [OK] Maximum possible memory usage: 43.5G (69.35% of installed RAM) [OK] Overall possible memory usage with other process is compatible with memory available [OK] Slow queries: 0% (324K/167M) [OK] Highest usage of available connections: 55% (84/151) [OK] Aborted connections: 0.00% (55/2878495) [!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance [!!] Query cache may be disabled by default due to mutex contention. [OK] Query cache efficiency: 40.4% (102M cached / 254M selects) [!!] Query cache prunes per day: 3479297 [OK] Sorts requiring temporary tables: 0% (11K temp sorts / 6M sorts) [!!] Joins performed without indexes: 12813 [!!] Temporary tables created on disk: 66% (2M on disk / 3M total) [OK] Thread cache hit rate: 98% (40K created / 2M connections) [OK] Table cache hit rate: 95% (4K open / 4K opened) [OK] table_definition_cache(2097152) is upper than number of tables(3862) [OK] Open file limit used: 7% (2K/40K) [OK] Table locks acquired immediately: 99% (13M immediate / 13M locks) -------- Performance schema ------------------------------------------------------------------------ [--] Memory used by P_S: 104.0M [--] Sys schema is installed. -------- ThreadPool Metrics ------------------------------------------------------------------------ [--] ThreadPool stat is enabled. [--] Thread Pool Size: 12 thread(s). [--] Using default value is good enough for your version (10.3.27-MariaDB-log) -------- MyISAM Metrics ---------------------------------------------------------------------------- [!!] Key buffer used: 24.9% (182M used / 734M cache) [OK] Key buffer size / total MyISAM indexes: 700.0M/460.2M [OK] Read Key buffer hit rate: 100.0% (1B cached / 41K reads) [!!] Write Key buffer hit rate: 69.5% (804K cached / 558K writes) -------- InnoDB Metrics ---------------------------------------------------------------------------- [--] InnoDB is enabled. [--] InnoDB Thread Concurrency: 0 [OK] InnoDB File per table is activated [OK] InnoDB buffer pool / data size: 4.0G/3.2G [!!] Ratio InnoDB log file size / InnoDB Buffer pool size (0.78125 %): 16.0M * 2/4.0G should be equal to 25% [!!] InnoDB buffer pool instances: 8 [--] Number of InnoDB Buffer Pool Chunk : 32 for 8 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% (102924116296 hits/ 102924220151 total) [!!] InnoDB Write Log efficiency: 73.75% (4679039 hits/ 6344450 total) [OK] InnoDB log waits: 0.00% (0 waits / 1665411 writes) -------- AriaDB Metrics ---------------------------------------------------------------------------- [--] AriaDB is enabled. [OK] Aria pagecache size / total Aria indexes: 128.0M/1B [OK] Aria pagecache hit rate: 99.1% (277M cached / 2M reads) -------- 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: MIXED [--] XA support enabled: ON [--] Semi synchronous replication Master: OFF [--] Semi synchronous replication Slave: OFF [--] This is a standalone server -------- Recommendations --------------------------------------------------------------------------- General recommendations: Control warning line(s) into /var/lib/mysql/server.name.here.err file Control error line(s) into /var/lib/mysql/server.name.here.err file Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1 Increasing the query_cache size over 128M may reduce performance 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). When making adjustments, make tmp_table_size/max_heap_table_size equal Reduce your SELECT DISTINCT queries which have no LIMIT clause Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU Variables to adjust: query_cache_size (=0) query_cache_type (=0) query_cache_size (> 128M) [see warning above] join_buffer_size (> 2.0M, or always use indexes with JOINs) tmp_table_size (> 32M) max_heap_table_size (> 32M) innodb_log_file_size should be (=512M) if possible, so InnoDB total log files size equals to 25% of buffer pool size. innodb_buffer_pool_instances(=4)以下是 my.cnf 目前的內容 **注意：**現在根據 Wilson 和 Rick 的建議進行編輯，將保存並重新啟動 mysql 並通過更新回复給大家。
[client-server] # # include all files from the config directory # !includedir /etc/my.cnf.d [mysqld] # Logging and performance measurement here log-error=/var/lib/mysql/ryzen.dogestream.com.err # for enhanced slow query log log_slow_verbosity=query_plan,explain performance-schema=1 max_allowed_packet=268435456 max_heap_table=32M tmp_table_size=32M open_files_limit=40000 # Buffer sizes join_buffer_size=2M key_buffer_size=700M sort_buffer_size=2M # InnoDB stuff goes here innodb_file_per_table=1 innodb_buffer_pool_size=15G innodb_log_file_size=16M local-infile=0 # from 1024 to conserve 90% of CPU cycles used for function innodb_lru_scan_depth=100 # should always match table_open_cache innodb_open_files=9000 # Query stuff goes here # from 128M to conserve RAM for more useful purposes query_cache_size=0 # from 1 for OFF to avoid 3 million+ query cache prunes per day query_cache_type=0 # from 2M to conserve RAM query_cache_limit=0 slow_query_log=1 slow_query_log_file=mysql-slow.log long_query_time=0.1 # It says cache it is here table_definition_cache=-1 # from 3000 to reduce tables opened_tables count table_open_cache=9000 # from 16 to accomodate your 84 concurrent users thread_cache_size=256
你不能（通常）調整 CPU 問題。
改為15G
innodb_buffer_pool_size。您正在使用混合引擎（InnoDB/MyISAM）。您應該只使用 InnoDB。更改時，減小 key_buffer_size 並增加 innodb_buffer_pool_size。 http://mysql.rjweb.org/doc.php/memory
您可以改進中的索引
postmeta；這將減少 CPU。如有必要，也在 I/O 上。詳情：http: //mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta
您已經打開了慢速日誌。用
pt-query-digest它來概括。然後提出前幾個查詢。（我希望你會發現postmeta大多數慢查詢都涉及到它。）數字表明，您是查詢記憶體幫助與傷害之間的屏障。如果您確實將其關閉，請仔細衡量更改前後的性能——看看更改是幫助還是傷害。
為您的 my.cnf 考慮的建議
$$ mysqld $$部分
query_cache_size=0 # from 128M to conserve RAM for more useful purposes query_cache_type=0 # from 1 for OFF to avoid 3 million+ query cache prunes per day query_cache_limit=0 # from 2M to conserve RAM從您的 my.cnf 中刪除
max_heap_table=32M not a valid configuration item table_cache = 6K not a valid configuration item thread_cache = 256 not a valid configuration item改變你的價值觀，
table_open_cache=9000 # from 3000 to reduce tables opened_tables countadd innodb_open_files=9000 # 應該總是匹配 table_open_cache
thread_cache_size=256 # from 16 to accomodate your 84 concurrent users log_slow_verbosity=query_plan,explain # for enhanced slow query log innodb_lru_scan_depth=100 # from 1024 to conserve 90% of CPU cycles used for function預計這些更改將減少 CPU 繁忙。查看配置文件、網路配置文件以獲取聯繫資訊和免費下載的實用程序腳本，以幫助提高性能。