幫我實現 MySQL 調諧器建議
我的是 Digitalocean，配備 1vCPU、1GB -25GB SSD Ubuntu 伺服器，配備 nginx、MariaDB、phpmyadmin 並執行 wordpress 部落格。Mysql 佔用了太多的 cpu，在執行 MySqlTuner 時，我得到了以下建議：
Variables to adjust: *** MySQL's maximum memory usage is dangerously high *** *** Add RAM before increasing MySQL buffer variables *** query_cache_size (=0) query_cache_type (=0) query_cache_size (> 16M) tmp_table_size (> 16M) max_heap_table_size (> 16M) performance_schema = ON enable PFS innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.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) 3788 max locked memory (kbytes, -l) 65536 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) 3788 virtual memory (kbytes, -v) unlimited file locks (-x) unlimited完整的 MySqlTuner 報告如下：
>> MySQLTuner 1.8.1 - Major Hayden <major@mhtx.net> >> Bug reports, feature requests, and downloads at http://mysqltuner.pl/ >> Run with '--help' for additional options and output filtering [--] Skipped version check for MySQLTuner script [OK] Logged in using credentials from Debian maintenance account. [!!] failed to execute: SHOW REPLICA STATUS\G [!!] FAIL Execute SQL / return code: 256 [OK] Currently running supported MySQL version 10.3.31-MariaDB-0ubuntu0.20.04.1 [OK] Operating on 64-bit architecture -------- Log file Recommendations ------------------------------------------------------------------ [!!] Log file /var/log/mysql/error.log doesn't exist -------- Storage Engine Statistics ----------------------------------------------------------------- [--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE [--] Data in MyISAM tables: 267.8M (Tables: 54) [--] Data in InnoDB tables: 416.0K (Tables: 20) [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: 1d 12h 39m 35s (5M q [39.556 qps], 41K conn, TX: 196G, RX: 657M) [--] Reads / Writes: 96% / 4% [--] Binary logging is disabled [--] Physical Memory : 981.3M [--] Max MySQL memory : 3.2G [--] Other process memory: 0B [--] Total buffers: 432.0M global + 18.9M per thread (151 max threads) [--] P_S Max memory usage: 0B [--] Galera GCache Max memory usage: 0B [OK] Maximum reached memory usage: 564.4M (57.51% of installed RAM) [!!] Maximum possible memory usage: 3.2G (335.01% of installed RAM) [!!] Overall possible memory usage with other process exceeded memory [OK] Slow queries: 0% (8/5M) [OK] Highest usage of available connections: 4% (7/151) [OK] Aborted connections: 0.00% (1/41165) [!!] 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: 33.8% (2M cached / 7M selects) [!!] Query cache prunes per day: 13820 [OK] Sorts requiring temporary tables: 1% (9K temp sorts / 532K sorts) [OK] No joins without indexes [!!] Temporary tables created on disk: 77% (65K on disk / 84K total) [OK] Thread cache hit rate: 99% (7 created / 41K connections) [OK] Table cache hit rate: 99% (2M hits / 2M requests) [OK] table_definition_cache(400) is upper than number of tables(235) [OK] Open file limit used: 0% (189/32K) [OK] Table locks acquired immediately: 99% (2M immediate / 2M locks) -------- Performance schema ------------------------------------------------------------------------ [--] Performance schema is disabled. [--] Memory used by P_S: 0B [--] Sys schema isn't installed. -------- ThreadPool Metrics ------------------------------------------------------------------------ [--] ThreadPool stat is enabled. [--] Thread Pool Size: 1 thread(s). [--] Using default value is good enough for your version (10.3.31-MariaDB-0ubuntu0.20.04.1) -------- MyISAM Metrics ---------------------------------------------------------------------------- [!!] Key buffer used: 28.5% (38M used / 134M cache) [OK] Key buffer size / total MyISAM indexes: 128.0M/46.4M [OK] Read Key buffer hit rate: 100.0% (230M cached / 12K reads) [OK] Write Key buffer hit rate: 99.5% (744K cached / 740K writes) -------- InnoDB Metrics ---------------------------------------------------------------------------- [--] InnoDB is enabled. [--] InnoDB Thread Concurrency: 0 [OK] InnoDB File per table is activated [OK] InnoDB buffer pool / data size: 128.0M/416.0K [!!] Ratio InnoDB log file size / InnoDB Buffer pool size (75 %): 48.0M * 2/128.0M should be equal to 25% [OK] InnoDB buffer pool instances: 1 [--] Number of InnoDB Buffer Pool Chunk : 1 for 1 Buffer Pool Instance(s) [OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances [!!] InnoDB Read buffer efficiency: 83.72% (1661 hits/ 1984 total) [!!] InnoDB Write Log efficiency: 41.67% (5 hits/ 12 total) [OK] InnoDB log waits: 0.00% (0 waits / 7 writes) -------- Aria Metrics ------------------------------------------------------------------------------ [--] Aria Storage Engine is enabled. [OK] Aria pagecache size / total Aria indexes: 128.0M/0B [!!] Aria pagecache hit rate: 87.4% (515K cached / 65K 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: Reduce your overall MySQL memory footprint for system stability Dedicate this server to your database for highest performance. Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1 When making adjustments, make tmp_table_size/max_heap_table_size equal Reduce your SELECT DISTINCT queries which have no LIMIT clause Performance schema should be activated for better diagnostics Consider installing Sys schema from https://github.com/mysql/mysql-sys for MySQL Consider installing Sys schema from https://github.com/FromDual/mariadb-sys for MariaDB Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU Variables to adjust: *** MySQL's maximum memory usage is dangerously high *** *** Add RAM before increasing MySQL buffer variables *** query_cache_size (=0) query_cache_type (=0) query_cache_size (> 16M) tmp_table_size (> 16M) max_heap_table_size (> 16M) performance_schema = ON enable PFS innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.htop
為了提高 MySQL 實例的性能並減少 cpu usgae，我需要進行哪些調整？
全球狀態和變數分析：
觀察：
- 版本：10.3.31-MariaDB-0ubuntu0.20.04.1
- 0.98 GB 記憶體
- 正常執行時間 = 2d 17:09:21
- 您沒有在 Windows 上執行。
- 執行 64 位版本
- 您似乎完全（或大部分）執行 MyISAM。
更重要的問題：
強烈建議遷移到 MyISAM。
如今，1GB 是一個很小的 RAM 大小。不過，它應該可以在 1GB 中執行。下面的一些評論將有助於在 1GB 中執行。
如果你繼續不使用 InnoDB，減少
innodb_buffer_pool_size到 16M。降到
max_connections10。細節和其他觀察：
( Key_blocks_used * 1024 / key_buffer_size ) = 13,886 * 1024 / 128M = 10.6%– 使用的 key_buffer 的百分比。高水位線。– 降低 key_buffer_size（現在為 134217728）以避免不必要的記憶體使用。
( innodb_buffer_pool_size ) = 128M– InnoDB 數據 + 索引記憶體 – 128M（舊的預設值）小得可憐。
( innodb_buffer_pool_size ) = 128M / 1053340729.344 = 12.7%– InnoDB buffer_pool 使用的 RAM 百分比 – 設置為可用 RAM 的 70% 左右。（太低效率低；太高風險交換。）
( innodb_lru_scan_depth ) = 1,024– “InnoDB: page_cleaner: 1000ms 預期循環佔用了……”可以通過降低 lru_scan_depth 來修復
( Innodb_buffer_pool_pages_free * 16384 / innodb_buffer_pool_size ) = 7,735 * 16384 / 128M = 94.4%– 緩衝池空閒 – buffer_pool_size 大於工作集；可以減少它
( innodb_io_capacity ) = 200– 沖洗時，使用這麼多的 IOP。– 讀取可能緩慢或尖刺。
( Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total ) = 7,735 / 8192 = 94.4%– 目前未使用的 buffer_pool 的百分比 – innodb_buffer_pool_size（現在為 134217728）是否大於所需？
( (Innodb_pages_written + Innodb_pages_read) / ((Innodb_pages_written + Innodb_pages_read) + (Key_writes + Key_reads)) ) = (151 + 322) / ((151 + 322) + (1375700 + 13062)) = 0.03%– % InnoDB I/O (vs MyISAM) – InnoDB 是目前首選的引擎。
( Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests ) = 323 / 1984 = 16.3%– 必須命中磁碟的讀取請求 – 如果您有足夠的 RAM，則增加 innodb_buffer_pool_size（現在為 134217728）。
( Innodb_pages_read / Innodb_buffer_pool_read_requests ) = 322 / 1984 = 16.2%– 必須命中磁碟的讀取請求 – 如果您有足夠的 RAM，則增加 innodb_buffer_pool_size（現在為 134217728）。
( Innodb_pages_written / Innodb_buffer_pool_write_requests ) = 151 / 928 = 16.3%– 必須命中磁碟的寫入請求 – 檢查 innodb_buffer_pool_size（現在為 134217728）
( Innodb_dblwr_pages_written/Innodb_pages_written ) = 20/151 = 13.2%- 似乎這些值應該相等？
( sync_binlog ) = 0– 使用 1 來增加安全性，以 I/O 為代價 =1 可能會導致大量“查詢結束”；=0 可能會導致“binlog 在不可能的位置”並在崩潰中失去事務，但速度更快。0 對 Galera 來說是可以的。
( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF– 是否記錄所有死鎖。– 如果你被死鎖困擾，打開它。注意：如果你有很多死鎖，這可能會寫入很多磁碟。
( min( tmp_table_size, max_heap_table_size ) ) = (min( 16M, 16M )) / 1053340729.344 = 1.6%– 當需要 MEMORY 表（每個表）或 SELECT 內的臨時表（每個 SELECT 的每個臨時表）時分配的 RAM 百分比。太高可能會導致交換。– 將 tmp_table_size（現在為 16777216）和 max_heap_table_size（現在為 16777216）減少到記憶體的 1%。
( 176000 * max_connections ) = (176000 * 151) / 1053340729.344 = 2.5%– 由於 max_connections 的大小估計 ram 使用情況。– max_connections（現在是 151）有點高
( myisam_sort_buffer_size ) = 134,216,704 / 1053340729.344 = 12.7%– 用於 ALTER、CREATE INDEX、OPTIMIZE、LOAD DATA；需要時設置。也適用於 MyISAM 的修復表。– 減少 myisam_sort_buffer_size（現在為 134216704）以防止記憶體溢出。
( innodb_ft_result_cache_limit ) = 2,000,000,000 / 1053340729.344 = 189.9%– FULLTEXT 結果集的字節限制。（可能沒有預先分配，但會增長？） – 降低設置。
( (Com_show_create_table + Com_show_fields) / Questions ) = (0 + 114700) / 9647512 = 1.2%– 頑皮的框架 – 花費大量精力重新發現模式。– 向第 3 方供應商投訴。
( local_infile ) = local_infile = ON– local_infile (now ON) = ON 是一個潛在的安全問題
( Key_blocks_used * 1024 / key_buffer_size ) = 13,886 * 1024 / 128M = 10.6%- 使用的 key_buffer 的百分比。高水位線。– 降低 key_buffer_size（現在為 134217728）以避免不必要的記憶體使用。
( (Key_writes + Key_reads) / ((Innodb_pages_written + Innodb_pages_read) + (Key_writes + Key_reads)) ) = (1375700 + 13062) / ((151 + 322) + (1375700 + 13062)) = 100.0%– % MyISAM I/O (vs InnoDB) – InnoDB 是目前首選的引擎。
( Key_writes / Key_write_requests ) = 1,375,700 / 1381646 = 99.6%– 寫入的 key_buffer 有效性 – 如果你有足夠的 RAM，增加 key_buffer_size（現在是 134217728）是值得的。
( (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache / query_alloc_block_size ) = (16M - 6071944) / 2482 / 16384 = 0.263– query_alloc_block_size vs formula – 調整 query_alloc_block_size (現在 16384)
( Created_tmp_disk_tables / Created_tmp_tables ) = 121,975 / 156296 = 78.0%– 溢出到磁碟的臨時表的百分比 – 可能增加 tmp_table_size（現在為 16777216）和 max_heap_table_size（現在為 16777216）；改進指標；避免斑點等
( binlog_format ) = binlog_format = MIXED– 聲明/行/混合。– ROW 是 5.7 (10.3) 的首選
( innodb_autoinc_lock_mode ) = 1– Galera: 慾望 2 – 2 = “interleaved”; 1 =“連續”是典型的；0 =“傳統”。– 加萊拉的願望 2；2 需要 BINLOG_FORMAT=ROW 或 MIXED
( slow_query_log ) = slow_query_log = OFF– 是否記錄慢查詢。(5.1.12)
( long_query_time ) = 10– 用於定義“慢”查詢的截止時間（秒）。– 建議 2
( Subquery_cache_hit / ( Subquery_cache_hit + Subquery_cache_miss ) ) = 23,792 / ( 23792 + 61652 ) = 27.8%– 子查詢記憶體命中率
( back_log ) = 80– （自 5.6.6 起自動調整大小；基於 max_connections） – 提高到 min(150, max_connections (現在為 151)) 在進行大量連接時可能會有所幫助。
( Max_used_connections / max_connections ) = 7 / 151 = 4.6%– 連接的峰值百分比 – 由於幾個記憶體因子可以基於 max_connections（現在為 151）擴展，因此最好不要將該設置設置得太高。
( thread_cache_size / Max_used_connections ) = 151 / 7 = 2157.1%
- 讓執行緒記憶體大於您可能的連接數沒有任何優勢。浪費空間是缺點。
異常小：
( Innodb_pages_read + Innodb_pages_written ) / Uptime = 0.00202 Acl_users = 3 Handler_commit = 0.031 /HR Handler_commit/Questions = 0.00% Handler_read_first = 1.5 /HR Handler_read_rnd_next / Handler_read_rnd = 0.312 Innodb_buffer_pool_bytes_data = 32 /sec Innodb_buffer_pool_pages_data = 457 Innodb_buffer_pool_pages_flushed / max(Questions, Queries) = 1.6e-5 Innodb_buffer_pool_pages_misc = 0 Innodb_buffer_pool_pages_misc * 16384 / innodb_buffer_pool_size = 0 Innodb_buffer_pool_read_requests = 30 /HR Innodb_buffer_pool_read_requests / (Innodb_buffer_pool_read_requests + Innodb_buffer_pool_reads ) = 86.0% Innodb_buffer_pool_write_requests = 14 /HR Innodb_data_fsyncs = 0.26 /HR Innodb_data_read = 23 /sec Innodb_data_reads = 5.5 /HR Innodb_data_writes = 2.5 /HR Innodb_data_writes - Innodb_log_writes - Innodb_dblwr_writes = 2.4 /HR Innodb_data_written = 12 /sec Innodb_log_write_requests = 0.18 /HR Innodb_num_open_files = 28 Innodb_os_log_fsyncs = 0.15 /HR Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group = 0.0MB Innodb_pages_created = 2.1 /HR Innodb_pages_read = 4.9 /HR Innodb_pages_read + Innodb_pages_written = 7.3 /HR Innodb_pages_written = 2.3 /HR Innodb_rows_deleted + Innodb_rows_inserted = 0 Innodb_rows_inserted = 0 Innodb_rows_read = 0 Innodb_rows_updated = 0 Innodb_secondary_index_triggered_cluster_reads = 0 eq_range_index_dive_limit = 0 innodb_spin_wait_delay = 4異常大：
Handler_discover = 0.8 /HR Handler_read_next / Handler_read_key = 317 Handler_read_rnd = 17988 /sec Key_blocks_warm = 7,622 Memory_used = 28.3% Sort_rows = 17988 /sec Tc_log_page_size = 4,096異常字元串：
Innodb_have_snappy = ON aria_recover_options = BACKUP,QUICK innodb_fast_shutdown = 1 log_slow_admin_statements = ON myisam_stats_method = NULLS_UNEQUAL old_alter_table = DEFAULT
每秒速率 = RPS
建議為您的 my.cnf 考慮
$$ mysqld $$提高性能的部分
max_connections=32 # from 151 for your 1G server until addl RAM available read_buffer_size=512K # from 128K to reduce handler_read_next RPS of 18,512 read-rnd_buffer_size=128K # from 256K to reduce handler_read_rnd_next RPS of 5,621 net_buffer_length=64K # from 16K to reduce sent packet count還有更多的機會來提高性能。查看個人資料以獲取聯繫資訊和可免費下載的實用程序腳本，以幫助進行性能調整。
觀察：大多數 Linux/Ubuntu 系統對 innodb_flush_method 使用 O_DIRECT 而不是 fsync。