提高 Percona 8 上大量同時讀取/寫入的性能

環境和案例的一般描述：

技術的：

• 我們正在執行一個具有單個伺服器和約 25 個客戶端的分佈式應用程序；每個客戶端都在執行約 75 個執行緒。
• 在每個執行緒一個連接的情況下，這會導致從所有客戶端執行緒到持續讀取、寫入和刪除數據的一台伺服器的多達約 1900 個始終線上連接。
• 託管數據庫的伺服器具有 32G RAM 和 8 個核心，執行 Linux。

涉及的表格（經過混淆但未修改，以使該問題盡可能通用和有用，同時保留相關資訊）：

CREATE TABLE `input` (
 `id` int unsigned NOT NULL AUTO_INCREMENT,
 `user` varchar(191) COLLATE utf8mb4_0900_bin DEFAULT NULL,
 `domain` varchar(191) COLLATE utf8mb4_0900_bin DEFAULT NULL,
 `mail_hash` varchar(191) COLLATE utf8mb4_0900_bin DEFAULT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `f12` (`field1`,`field2`),
 KEY `field1` (`field1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;

CREATE TABLE `output` (
 `id` int unsigned NOT NULL AUTO_INCREMENT,
 `field1` varchar(191) COLLATE utf8mb4_0900_bin DEFAULT NULL,
 `field2` varchar(191) COLLATE utf8mb4_0900_bin DEFAULT NULL,
 [...various other output fields],
 `dt` datetime DEFAULT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `f12` (`field1`,`field2`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;

應用：

1. 典型工作的主要過程持續數天。它涉及從包含約 100M 到 200M 行的輸入表中批量讀取多達 99,999 條記錄，處理它們，然後將輸出寫入現在約 500M 行的表（保存來自舊作業的數據）。
2. 輸入數據恰好由兩個欄位 , 組成field1，field2它們也是輸出的一部分。field1和之間存在一對多的關係field2。
3. 從輸入表中讀取數據的查詢是這樣的形式SELECT * FROM input WHERE field1 = ? LIMIT 99999，每個field1.
4. 處理每條記錄需要幾毫秒到幾秒鐘的時間，處理完後，它會從輸入表中批量刪除（DELETE FROM input WHERE field1 = ? AND field2 IN(?, ..., ?);
5. 在處理數據之前，該過程將檢查輸出表以查看是否尚未處理相同的數據；對此的查詢一次SELECT ... FROM output WHERE field1 = ? AND field2 IN(?, ..., ?);在構造中最多包含 1,000 個元素。IN()
6. 寫入輸出表使用REPLACE INTO output [all fields]，並且可能會或可能不會覆蓋現有數據（我們也在考慮更改REPLACE INTOwith INSERT ... ON DUPLICATE KEY UPDATE，因為我們沒有修改主鍵）。

我們最近從 Percona 5.7 升級到 Percona 8.0，我們注意到性能顯著下降以及死鎖，這是我們以前沒有經歷過的。慢查詢日誌顯示某些數據可能需要長達 40 秒才能從input表中讀取。該output表在執行上面第 5 點所述的查詢版本時被鎖定（SELECT ... FROM output WHERE field1 = ? AND field2 IN(?, ..., ?) AND dt > ?;裡面有大約 15K 的元素IN()。為了解決這個問題，我們必須將其分塊為多個請求，每個請求限制為 1,000 個並刪除dt > ?（我們現在正在過濾申請中的日期），因為這需要幾分鐘。

除了我們所做的升級之外，唯一的其他更改是將預設表CHARSET從更改utf8為utf8mb4（我們讀到版本 8 中消除了性能問題；使用它 5.7 會使處理停止）。

這是我們在過去幾個小時調整後最終得到的配置my.cnf，至少可以讓應用程序在不鎖定的情況下執行：

[mysqld]

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

secure_file_priv   = ""
bind-address       = 0.0.0.0
loose-local-infile = 1
local-infile = 1
skip-name-resolve
disable-log-bin

max_allowed_packet      = 128M
max_connections         = 2048
transaction-isolation   = READ-UNCOMMITTED
open_files_limit        = 2048
table_open_cache        = 2048
innodb_open_files       = 2048
thread_cache_size       = 2048
join_buffer_size        = 128M
sort_buffer_size        = 16M
read_rnd_buffer_size    = 2M
max_prepared_stmt_count = 20000
thread_handling         = pool-of-threads
max_heap_table_size     = 8G
tmp_table_size          = 4G
key_buffer_size         = 1G
read_buffer_size        = 1M

[InnoDB]
innodb_file_per_table          = 1
innodb_log_file_size           = 2G
innodb_log_buffer_size         = 512M
innodb_undo_tablespaces        = 2
innodb_locks_unsafe_for_binlog = 1
innodb_buffer_pool_size        = 8G
innodb_buffer_pool_instances   = 8
innodb_use_native_aio          = 1
innodb_io_capacity             = 250
innodb_io_capacity_max         = 500
innodb_purge_threads           = 1
innodb_adaptive_hash_index     = 0
innodb_page_cleaners           = 4
innodb_lru_scan_depth          = 256

我非常感謝任何關於什麼可以使在 Percona 5.7 中工作的相同應用程序在 Percona 8.0 中突然停止的提示。我們應該回去utf8嗎？此外，如果配置中的任何內容看起來不合適，請隨時指出。謝謝！

~1900 個永遠線上的連接

他們到底在做什麼？的價值是SHOW GLOBAL STATUS LIKE 'Threads_running';多少？多次檢查該號碼；如果它超過了，比如說，20，你可能會遇到爭用問題。通常很多“空閒”執行緒不是問題。

UNIQUE KEY `f12` (`field1`,`field2`),
KEY `field1` (`field1`)  -- This is redundant and can be dropped.

你的ROW_FORMAT=COMPRESSED待遇如何？（我不是它的粉絲。）

持續幾天

匯總表會有所幫助嗎？

... input ...
PRIMARY KEY (`id`),
UNIQUE KEY `f12` (`field1`,`field2`),

如果你不需要id，扔掉它並將 UNIQUE 提升為 PRIMARY。

LIMIT 99999

你會使用OFFSET嗎？如果是這樣，我們應該討論一個性能問題。

(DELETE FROM input WHERE field1 = ? AND field2 IN(?, ..., ?);

我不知道轉折點在哪裡，但是 IN 列表中的 99999 個項目可能會阻塞處理。在其他情況下，我發現最佳分塊在 100 到 1000 之間，而不是 99999。更多討論：http: //mysql.rjweb.org/doc.php/deletebig#deleting_in_chunks

REPLACE INTO with INSERT ... ON DUPLICATE KEY UPDATE

是的。IODKU 的插入或更新是一個“步驟”。 REPLACE是兩個重要的步驟： DELETE，那麼即使其他列不變INSERT. Note that that means the ， AUTO_INCREMENT` 的值也會改變。

死鎖

你想分享一個嗎？ SHOW ENGINE=InnoDB STATUS;

2048

由於您正在談論〜1900個連接，請注意一個連接可能需要table_open_cache等中的多個條目。也就是說，設置為2048的其餘內容可能設置得太低。

join_buffer_size = 128M max_heap_table_size = 8G tmp_table_size = 4G

許多設置是“每個連接”。例如，如果某些查詢在您的 1900 個連接中的許多連接中“同時”發生，則此連接可能會嚴重崩潰。降低它。

key_buffer_size = 1G

MySQL 8 不處理 MyISAM；將此值降低到 10M。

如果您想對您的設置和/或慢日誌進行更多分析，請參閱http://mysql.rjweb.org/doc.php/mysql_analysis

你的 my.cnf 不應該有雜散的部分名稱

$$ innodb $$ 請刪除它。

首先是容易辨識的問題，然後在停止/啟動之後，24 小時的實例正常執行時間為工作負載分析提供所需的數據。

謝謝

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