MySQL（Percona）：多個同時長時間執行的連接

我在雲中執行一個分佈式應用程序，客戶端保持打開多個與 MySQL (Percona) 伺服器的長期執行連接。

更新到最新的 Percona 版本後，同時連接的客戶端的最大數量顯著下降。過去，在之前的 Percona 版本中，它成功使用了高達 8K 的連接，現在它很難達到 3K 以上。

“掙扎”是指當達到連接瓶頸時，即使在命令行中連接到 MySQL 也會超時。當我設法連接時，show processlist不會顯示任何掛起的查詢或鎖定。所以建立的連接工作得很好。為用於監控項目的 PHP webapp 提供服務的 Apache 網路伺服器也無限期掛起。

這是my.cnf我使用的，由各種指南拼湊而成。

[mysqld]
open_files_limit               = 16384
table_open_cache               = 16384
character_set_server           = utf8mb4
max_connections                = 16384
expire_logs_days               = 10
max_binlog_size                = 100M
innodb_open_files              = 16384
innodb_file_per_table          = 1
innodb_read_io_threads         = 4
innodb_write_io_threads        = 4
innodb_thread_concurrency      = 0
innodb_log_file_size           = 128M
innodb_open_files              = 4000
innodb_flush_method            = O_DIRECT
innodb_buffer_pool_instances   = 1
thread_pool_size               = 16
local_infile                   = 1
skip-name-resolve
thread_cache_size              = 16384
thread_handling                = pool-of-threads
innodb_buffer_pool_size        = 512M
innodb_buffer_pool_instances   = 1
innodb_log_buffer_size         = 64M

我正在執行的 Percona 版本：

mysqld --version
Ver 8.0.15-6 for debian-linux-gnu on x86_64 (Percona Server (GPL), Release '6', Revision '63abd08')

你：Ubuntu 18.04.2 LTS

硬體：

• 中央處理器：Intel(R) Xeon(R) Platinum 8175M CPU @ 2.50GHz
• 記憶體：32G

我正在執行的查詢都是基於唯一索引（沒有連接），表都是 InnoDB。在更新到 Percona 8 之前，相同的配置執行良好。

my.cnf由於不再受支持，升級後我必須刪除的設置是：

• innodb_locks_unsafe_for_binlog = 0
• query_cache_size = 0
• query_cache_type = 0

我嘗試過的事情：

• 我跑了mysqltuner.pl，但沒有得到任何相關的建議。
• ulimit -n並ulimit -s顯示明顯高於我正在使用的值（1048576特別16384是）。

還有其他建議嗎？

Connections5小時內只有92個Uptime，而且一次不超過4個（Max_used_connections）。所以我不明白你關於在 3K 連接上苦苦掙扎的問題。

觀察：

• 版本：8.0.15-6
• 32 GB 記憶體
• 正常執行時間 = 04:54:20；一些 GLOBAL STATUS 值可能還沒有意義。
• 你確定這是一個 SHOW GLOBAL STATUS 嗎？
• 您沒有在 Windows 上執行。
• 執行 64 位版本
• 您似乎完全（或大部分）執行 InnoDB。

更重要的問題：

buffer_pool 上的磁碟活動非常高。由於您有 32GB，並且假設它主要用於 MySQL，請更改innodb_buffer_pool_size為22G.

為什麼有這麼多 CHECK、SET、DELETE、SHOW COLUMNS 語句？

您似乎有一些非常大的疑問。打開慢日誌以捕捉最糟糕的情況。例如，也許 DELETE 缺少有用的索引？

表記憶體不是很有效，table_open_cache增加到2000.

Max_connections = 15574相當高。特別是因為最常用的只有 4 個。把它降到 100。

細節和其他觀察：

( Innodb_buffer_pool_reads ) = 28,825,842 / 17660 = 1632 /sec– InnoDB buffer_pool I/O 讀取率 – 檢查 innodb_buffer_pool_size

( Innodb_buffer_pool_pages_flushed ) = 61,160,824 / 17660 = 3463 /sec– 寫入（刷新） – 檢查 innodb_buffer_pool_size

( innodb_buffer_pool_size / _ram ) = 512M / 32768M = 1.6%– 用於 InnoDB buffer_pool 的 RAM 百分比

( Table_open_cache_misses / (Table_open_cache_hits + Table_open_cache_misses) ) = 979 / (8530 + 979) = 10.3%– table_open_cache 的有效性。– 增加table_open_cache 並檢查table_open_cache_instances。

( innodb_lru_scan_depth ) = 1,024 – “InnoDB: page_cleaner: 1000ms 預期循環佔用了……”可以通過降低 lru_scan_depth 來修復

( Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests ) = 28,825,842 / 997216830 = 2.9%– 必須命中磁碟的讀取請求 – 如果您有足夠的 RAM，請增加 innodb_buffer_pool_size。

( Innodb_pages_read / Innodb_buffer_pool_read_requests ) = 50,177,127 / 997216830 = 5.0%– 必須命中磁碟的讀取請求 – 如果您有足夠的 RAM，請增加 innodb_buffer_pool_size。

( (Innodb_buffer_pool_reads + Innodb_buffer_pool_pages_flushed) ) = ((28825842 + 61160824) ) / 17660 = 5095 /sec– InnoDB I/O – 增加 innodb_buffer_pool_size？

( innodb_log_buffer_size / innodb_log_file_size ) = 64M / 48M = 133.3%– 緩衝區在 RAM 中；文件在磁碟上。– buffer_size 應該更小和/或 file_size 應該更大。

( Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group / innodb_log_file_size ) = 248,832 / (17660 / 3600) / 2 / 48M = 0.0005– 比率 – (見分鐘)

( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 17,660 / 60 * 48M / 248832 = 59,535– InnoDB 日誌輪換之間的分鐘數從 5.6.8 開始，可以動態更改；請務必同時更改 my.cnf。– （輪換間隔 60 分鐘的建議有些隨意。）調整 innodb_log_file_size。（不能在 AWS 中更改。）

( Innodb_rows_deleted / Innodb_rows_inserted ) = 201 / 0 = INF——流失——“不要排隊，就去做。” （如果 MySQL 被用作隊列。）

( ( Innodb_pages_read + Innodb_pages_written ) / Uptime / innodb_io_capacity ) = ( 50177127 + 224 ) / 17660 / 200 = 1420.6%– 如果 > 100%，需要更多的 io_capacity。– 如果驅動器可以處理它，則增加 innodb_io_capacity。

( innodb_io_capacity ) = 200- 磁碟上每秒的 I/O 操作數。100 用於慢速驅動器；200 用於旋轉驅動器；SSD 1000-2000；乘以 RAID 係數。

( expand_fast_index_creation ) = expand_fast_index_creation = OFF– 使用 ON 可以大大加快 ALTER 和 OPTIMIZE。- 可能更好的是。

( innodb_thread_concurrency ) = 0– 0 = 讓 InnoDB 決定 concurrency_tickets 的最佳值。– 設置為 0 或 64。這可能會減少 CPU。

( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF– 是否記錄所有死鎖。– 如果你被死鎖困擾，打開它。注意：如果你有很多死鎖，這可能會寫入很多磁碟。

( max_connections ) = 15,574– 最大連接數（執行緒）。影響各種分配。– 如果 max_connections 太高並且各種記憶體設置很高，您可能會用完 RAM。

( (Com_show_create_table + Com_show_fields) / Questions ) = (0 + 436) / 1804 = 24.2%– 頑皮的框架 – 花費大量精力重新發現模式。– 向第 3 方供應商投訴。

( local_infile ) = local_infile = ON – local_infile = ON 是一個潛在的安全問題

( Select_scan / Com_select ) = 217 / 199 = 109.0%– % 的選擇進行全表掃描。（可能被儲存常式愚弄。）——添加索引/優化查詢

( slow_query_log ) = slow_query_log = OFF– 是否記錄慢查詢。

( long_query_time ) = 10– 用於定義“慢”查詢的截止時間（秒）。– 建議 2

( back_log ) = 15,574– （從 5.6.6 開始自動調整大小；基於 max_connections） – 提高到 min(150, max_connections) 在進行大量連接時可能會有所幫助。

( thread_cache_size ) = 16,384– 要保留多少額外程序（使用執行緒池時不相關）（自 5.6.8 起自動調整；基於 max_connections） – 大於 100 可能導致 OOM。

( thread_cache_size / Max_used_connections ) = 16,384 / 4 = 409600.0%

• 讓執行緒記憶體大於您可能的連接數沒有任何優勢。浪費空間是缺點。

異常小：

Bytes_received = 6.2 /sec
Bytes_sent = 145 /sec
Com_insert = 0
Com_select = 41 /HR
Handler_read_key = 0.45 /sec
Handler_write = 0.33 /sec
Innodb_background_log_sync = 0
Innodb_data_writes = 77 /HR
Innodb_data_written = 222 /sec
Innodb_pages0_read = 0
Innodb_rows_inserted = 0
Innodb_secondary_index_triggered_cluster_reads = 0.32 /sec
Max_used_connections = 4
Open_files = 2
Select_range = 0
Table_locks_immediate = 2.7 /HR
Table_open_cache_hits = 0.48 /sec
innodb_default_encryption_key_id = 0

異常大：

( Innodb_pages_read + Innodb_pages_written ) / Uptime = 2,841
Com_check = 22 /HR
Com_create_db = 0.2 /HR
Handler_read_next / Handler_read_key = 51,277
Innodb_buffer_pool_pages_flushed / max(Questions, Queries) = 33,884
Innodb_buffer_pool_pages_made_not_young = 12596 /sec
Innodb_buffer_pool_read_ahead = 1209 /sec
Innodb_data_pending_reads = 0.82 /HR
Innodb_data_read = 41774567 /sec
Innodb_data_reads = 2841 /sec
Innodb_pages_read = 2841 /sec
Threadpool_idle_threads = 9
Threadpool_threads = 11
gtid_executed_compression_period = 0.057 /sec
host_cache_size = 1,381
innodb_max_dirty_pages_pct_lwm = 10
innodb_undo_tablespaces = 2
max_error_count = 1,024
max_length_for_sort_data = 4,096
slave_pending_jobs_size_max = 128MB

異常字元串：

bind_address = 0.0.0.0
default_authentication_plugin = caching_sha2_password
event_scheduler = ON
have_ssl = YES
have_symlink = DISABLED
innodb_fast_shutdown = 1
innodb_undo_log_truncate = ON
optimizer_trace = enabled=off,one_line=off
slave_rows_search_algorithms = INDEX_SCAN,HASH_SCAN
thread_handling = pool-of-threads
transaction_write_set_extraction = XXHASH64

每秒速率 = RPS - 為您的 my.cnf 考慮的建議

$$ mysqld $$部分

max_connections=4000  # from 15574 to get above your 3,000 stall
thread_cache_size=100  # from 16384 to be capped at 100, per 5.7 to avoid OOM
innodb_buffer_pool_size=22G  # from 512M to reduce innodb_buffer_pool_reads RPS of 1,632
innodb_buffer_pool_instances=8  # from 1 to reduce mutex contention
innodb_lru_scan_depth=100  # from 1024 to 90% of CPU cycles used for function
innodb_log_file_size=1G  # from 50M, should always be greater than innodb_log_buffer_size
table_open_cache=4000  # from 400 to reduce opened_tables RPHr of 200

Rick James 已經提到了其中一些建議。

免責聲明：我是我的個人資料中提到的網站的內容作者，網路個人資料可以下載免費實用腳本，聯繫資訊可用。

引用自：https://dba.stackexchange.com/questions/240712