關於減少 MYSQL 磁碟活動
按照這個主題，我想減少我的 Moodle 數據庫的磁碟活動。目前，設置了以下變數
innodb_buffer_pool_size 8589934592 innodb_buffer_pool_chunk_size 134217728 innodb_ft_cache_size 8000000 key_buffer_size 16777216 key_cache_age_threshold 300 open_files_limit 5000 query_cache_limit 1048576和
Open_tables 2000 Table_open_cache_hits 10705086 Table_open_cache_misses 137377 Table_open_cache_overflows 135369 Threads_cached 2 Threads_connected 65 Threads_created 29751 Threads_running 4 Uptime 96267我已經上傳了SHOW GLOBAL STATUS和SHOW VARIABLES的完整輸出。我特別想知道，減少磁碟活動並在記憶體中保留更多內容的重要因素是什麼。
從上一個主題，我想增加
Open_tables但不知道增加多少（2k到4k或2k到10k）以及如何監控它的效果。我也想知道我是否可以收集一段時間內的讀寫查詢數。那可能嗎？
更新1：
感謝 Rick 的解釋，我用過
Open_tables | 4000，現在我明白了| Uptime | 199784 | | Table_open_cache_misses | 210777 | | Table_open_cache_overflows | 206768 |因此，現在的未命中率和溢出率分別為 1.05 和 1.03，它們比以前更小，我希望它們能低於 1。
在伺服器上，我已經安裝了 Moodle 和 Zabbix。所以以下數據庫可用
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | moodle | | mysql | | performance_schema | | sys | | zabbixdb | +--------------------+我不知道正在使用哪個數據庫，
SHOW VARIABLES而且CREATE INDEX很多。但我很好奇是否有辦法找到它。也許那裡有一個錯誤。我還使用了慢查詢，並看到很少有 Moodle 超過 5 秒的實例。
這裡解釋了開啟
query_cache_type有積極的影響。我已經打開了。不知道這樣做有什麼副作用。我也
max_connections漲到了300。不知道你為什麼說，評論裡越低越好。你是對 Apache 這麼說的嗎？這將成為一個瓶頸，因為如果伺服器有資源，有指南可以增加 Apache 可以處理的連接數。我也
thread_cache_size漲到20了，不知道能不能走得更遠。那有什麼期待呢？更新 2：
我打開“通用日誌”幾秒鐘，此處
CREATE INDEX顯示了範例，此處SHOW VARIABLES顯示了範例。因此，Moodle 似乎經常使用這些查詢。
觀察：
- 版本：5.7.30-0ubuntu0.18.04.1
- 16 GB RAM – 這是正確的嗎？
- 正常執行時間 = 1d 02:44:27
- 您沒有在 Windows 上執行。
- 執行 64 位版本
- 您似乎完全（或大部分）執行 InnoDB。
更重要的問題：
你有幾張桌子？顯然
table_open_cache = 2000不夠高。設置為4000；Table_open_cache_overflows / Uptime然後看看是否Table_open_cache_misses / Uptime低於每秒1。如果您使用的是 SSD，請增加到
innodb_io_capacity500。對於生產伺服器，通常最好關閉查詢記憶體。
使用慢日誌來發現“最差”的查詢。似乎有一些頑皮的問題。
你為什麼
SHOW VARIABLES每秒做兩次？為什麼CREATE INDEX一天要一百次？即使在伺服器啟動的 26 小時內，您也達到了
max_connections(151)。你能解釋為什麼會這樣嗎？是的，可以增加該設置，但這可能會使事情變得更糟。因此，我們應該嘗試找出根本原因。將
thread_cache_size（從 8 個）增加到 20 個。（我不知道您的伺服器的最佳數量，但顯然 8 太低了。）細節和其他觀察：
( Table_open_cache_overflows ) = 135,369 / 96267 = 1.4 /sec– 可能需要增加table_open_cache（現在是2000）
( Table_open_cache_misses ) = 137,377 / 96267 = 1.4 /sec– 可能需要增加table_open_cache（現在是2000）
( innodb_lru_scan_depth * innodb_page_cleaners ) = 1,024 * 4 = 4,096– 頁面清理器每秒的工作量。– “InnoDB: page_cleaner: 1000ms 預期循環佔用了……”可以通過降低 lru_scan_depth 來修復：考慮 1000 / innodb_page_cleaners（現在是 4）。還要檢查交換。
( innodb_page_cleaners / innodb_buffer_pool_instances ) = 4 / 8 = 0.5– innodb_page_cleaners – 建議將 innodb_page_cleaners (現在 4) 設置為 innodb_buffer_pool_instances (現在 8)
( innodb_lru_scan_depth ) = 1,024– “InnoDB: page_cleaner: 1000ms 預期循環佔用了……”可以通過降低 lru_scan_depth 來修復
( innodb_io_capacity_max / innodb_io_capacity ) = 2,000 / 200 = 10– 容量：max/plain – 推薦 2. Max 應該大約等於您的 I/O 子系統可以處理的 IOP。（如果驅動器類型未知，2000/200 可能是合理的一對。）
( innodb_flush_method ) = innodb_flush_method =– InnoDB 應該如何要求作業系統寫入塊。建議使用 O_DIRECT 或 O_ALL_DIRECT (Percona) 以避免雙重緩衝。（至少對於 Unix。）有關 O_ALL_DIRECT 的警告，請參閱 chrischandler
( innodb_flush_neighbors ) = 1– 將塊寫入磁碟時的小優化。– 使用 0 表示 SSD 驅動器；1 用於硬碟。
( innodb_io_capacity ) = 200- 磁碟上每秒的 I/O 操作數。100 用於慢速驅動器；200 用於旋轉驅動器；SSD 1000-2000；乘以 RAID 係數。
( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF– 是否記錄所有死鎖。– 如果你被死鎖困擾，打開它。注意：如果你有很多死鎖，這可能會寫入很多磁碟。
( character_set_server ) = character_set_server = latin1– 將 character_set_server（現在是 latin1）設置為 utf8mb4 可以幫助解決字元集問題。那是未來的預設值。
( local_infile ) = local_infile = ON– local_infile (now ON) = ON 是一個潛在的安全問題
( Qcache_lowmem_prunes ) = 1,739,291 / 96267 = 18 /sec– QC 空間不足 – 增加 query_cache_size（現在為 16777216）
( Qcache_lowmem_prunes/Qcache_inserts ) = 1,739,291/5746238 = 30.3%– Removal Ratio（由於記憶體不足而需要修剪的頻率）
( (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache / query_alloc_block_size ) = (16M - 6135552) / 4715 / 8192 = 0.276– query_alloc_block_size vs formula – 調整 query_alloc_block_size (現在 8192)
( Created_tmp_disk_tables ) = 226,136 / 96267 = 2.3 /sec– 作為複雜 SELECT 的一部分創建磁碟“臨時”表的頻率 – 增加 tmp_table_size（現在為 16777216）和 max_heap_table_size（現在為 16777216）。檢查何時使用 MEMORY 而不是 MyISAM 的臨時表規則。也許較小的模式或查詢更改可以避免 MyISAM。更好的索引和查詢的重新制定更有可能有所幫助。
( Com_show_variables ) = 200,230 / 96267 = 2.1 /sec– SHOW VARIABLES … – 你為什麼這麼頻繁地請求 VARIABLES？
( Select_scan ) = 1,067,468 / 96267 = 11 /sec– 全表掃描 – 添加索引/優化查詢（除非它們是小表）
( Select_scan / Com_select ) = 1,067,468 / 5869892 = 18.2%– % 的選擇進行全表掃描。（可能被儲存常式愚弄。）——添加索引/優化查詢
( slow_query_log ) = slow_query_log = OFF– 是否記錄慢查詢。(5.1.12)
( long_query_time ) = 10– 用於定義“慢”查詢的截止時間（秒）。– 建議 2
( log_slow_slave_statements ) = log_slow_slave_statements = OFF– (5.6.11, 5.7.1) 預設情況下，複製的語句不會出現在慢日誌中；這導致他們顯示。– 在慢日誌中查看可能干擾副本讀取的寫入會很有幫助。
( back_log ) = 80– （自 5.6.6 起自動調整大小；基於 max_connections） – 提高到 min(150, max_connections (現在為 151)) 在進行大量連接時可能會有所幫助。
( Max_used_connections / max_connections ) = 152 / 151 = 100.7%– 連接的峰值百分比 – 增加 max_connections（現在 151）和/或減少 wait_timeout（現在 28800）
( Connections ) = 911,023 / 96267 = 9.5 /sec– Connections – 增加wait_timeout（現在是28800）；使用池化？異常小：
Open_files = 0異常大：
Com_create_index = 4.2 /HR Innodb_buffer_pool_pages_misc = 143,234 Innodb_buffer_pool_pages_misc * 16384 / innodb_buffer_pool_size = 27.3% Innodb_os_log_pending_fsyncs = 1異常字元串：
external_user = root innodb_fast_shutdown = 1 optimizer_trace = enabled=off,one_line=off optimizer_trace_features = greedy_search=on, range_optimizer=on, dynamic_range=on, repeated_subselect=on slave_rows_search_algorithms = TABLE_SCAN,INDEX_SCAN