我們的組織有一個相當先進的（就像許多移動部件一樣）Web 應用程序，直到最近一直運行良好，但沒有做出明顯的更改。
有 Apache Web 伺服器、mySQL 伺服器（資料處理），然後是另一個處理公眾重複請求的 mySQL 伺服器。流入主 Web 伺服器的流量幾乎只會影響輔助 SQL 伺服器。重複請求伺服器是資料處理伺服器的從屬伺服器，但不會向重複請求伺服器發送寫入查詢 - 唯讀。
我面臨的問題是，資料處理 mySQL 伺服器的 CPU 使用率通常在 15-20% 下運行，即使在重負載下，CPU 使用率似乎也會隨機飆升至 100%。 100% CPU 使用率持續約 8 秒，有時每 2-3 分鐘一次，直到它自行隨機消失。
除了在 100% 時嘗試處理的查詢之外，慢查詢日誌中沒有任何查詢。導致日誌的查詢與通常在隨機峰值之前運行且沒有問題的查詢相同。
HTOP 顯示的唯一活動是 mySQL，伺服器上也沒有規劃排程任務，且在這些時間內其他查詢活動沒有增加。開啟的連線執行緒帳戶保持穩定在 3-5 左右，且 PROCESS LIST 在之前、期間或之後也只有 3-5 個查詢。
SELECT * FROM audio
WHERE associated_incident IS null
AND archive IS NULL
AND temp_skip IS null
AND length >= 3
AND (locked <> 1 or locked IS NULL)
AND timestamp > (NOW() - INTERVAL 4 HOUR)
ORDER BY `audio`.`id` DESC
LIMIT 1;
UPDATE audio SET locked=1, lockexpr = '$ulockexpr', lockuser = '$ulockuser' WHERE id = $audio_id;
上面的查詢在我們員工使用的軟體中重複運行，但在給定時間每秒不超過 1-2 個，但這是我可以縮小問題範圍的最接近的查詢。在高峰值期間，此查詢也會顯示在 Slow_query_log 中。但需要澄清的是，這個查詢整天都在使用，我們並不總是遇到這些問題。
我可以使用此查詢對伺服器進行壓力測試，在 5 秒內運行 100 次，並且返回時間仍然得到 0.002 到 0.005 秒的平均結果。
在 /var/log/mysql 中，我有 8.2Gb 的 mysql-bin 檔案。
只能對 my.cnf 進行修改
key_buffer_size = 16M
myisam-recover-options = BACKUP
log_error = /var/log/mysql/error.log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
max_binlog_size = 100M
binlog_do_db = incident_log
其他一切都未指定或已註解掉。
Mysql 調優器
>> MySQLTuner 2.1.1
* Jean-Marie Renouard <[email protected]>
* Major Hayden <[email protected]>
>> 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.
[OK] Currently running supported MySQL version 8.0.32-0ubuntu0.20.04.2
[OK] Operating on 64-bit architecture
-------- Log file Recommendations ------------------------------------------------------------------
[OK] Log file /var/log/mysql/error.log exists
[--] Log file: /var/log/mysql/error.log (0B)
[--] Log file /var/log/mysql/error.log is empty. Assuming log-rotation. Use --server-log={file} for explicit file
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in InnoDB tables: 1.5G (Tables: 107)
[OK] Total fragmented tables: 0
-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.
-------- Views Metrics -----------------------------------------------------------------------------
-------- Triggers Metrics --------------------------------------------------------------------------
-------- Routines Metrics --------------------------------------------------------------------------
-------- Security Recommendations ------------------------------------------------------------------
[--] Skipped due to unsupported feature for MySQL 8.0+
-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 12h 41m 34s (3M q [73.289 qps], 1M conn, TX: 5G, RX: 445M)
[--] Reads / Writes: 81% / 19%
[--] Binary logging is enabled (GTID MODE: OFF)
[--] Physical Memory : 1.9G
[--] Max MySQL memory : 10.1G
[--] Other process memory: 0B
[--] Total buffers: 176.0M global + 65.9M per thread (151 max threads)
[--] Performance_schema Max memory usage: 239M
[--] Galera GCache Max memory usage: 0B
[!!] Maximum reached memory usage: 10.2G (528.62% of installed RAM)
[!!] Maximum possible memory usage: 10.1G (525.28% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (3/3M)
[!!] Highest connection usage: 100% (152/151)
[OK] Aborted connections: 0.09% (1203/1391547)
[!!] Name resolution is active: a reverse name resolution is made for each new connection which can reduce performance
[--] Query cache has been removed since MySQL 8.0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 10K sorts)
[!!] Joins performed without indexes: 5915
[OK] Temporary tables created on disk: 0% (0 on disk / 8K total)
[OK] Thread cache hit rate: 94% (75K created / 1M connections)
[OK] Table cache hit rate: 99% (1M hits / 1M requests)
[OK] table_definition_cache (2000) is greater than number of tables (434)
[OK] Open file limit used: 0% (3/10K)
[OK] Table locks acquired immediately: 100% (1K immediate / 1K locks)
[OK] Binlog cache memory access: 100.00% (346520 Memory / 346520 Total)
-------- Performance schema ------------------------------------------------------------------------
[--] Performance_schema is activated.
[--] Memory used by Performance_schema: 239.2M
[--] Sys schema is installed.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.
-------- MyISAM Metrics ----------------------------------------------------------------------------
[--] MyISAM Metrics are disabled since MySQL 8.0.
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[!!] InnoDB buffer pool / data size: 128.0M / 1.5G
[!!] 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
[OK] InnoDB Read buffer efficiency: 99.87% (403976971 hits / 404504329 total)
[!!] InnoDB Write Log efficiency: 59.62% (1713194 hits / 2873563 total)
[OK] InnoDB log waits: 0.00% (0 waits / 1160369 writes)
-------- Aria Metrics ------------------------------------------------------------------------------
[--] Aria Storage Engine not available.
-------- 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: ROW
[--] XA support enabled: ON
[--] Semi synchronous replication Master: Not Activated
[--] Semi synchronous replication Slave: Not Activated
[--] This is a standalone server
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
MySQL was started within the last 24 hours: recommendations may be inaccurate
Reduce your overall MySQL memory footprint for system stability
Dedicate this server to your database for highest performance.
Reduce or eliminate persistent connections to reduce connection usage
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
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).
Buffer Key MyISAM set to 0, no MyISAM table detected
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 ***
max_connections (> 151)
wait_timeout (< 28800)
interactive_timeout (< 28800)
skip-name-resolve=1
join_buffer_size (> 256.0K, or always use indexes with JOINs)
key_buffer_size=0
innodb_buffer_pool_size (>= 1.5G) if possible.
innodb_log_file_size should be (=16M) if possible, so InnoDB total log file size equals 25% of buffer pool size.
開放接受所有建議。
編輯：
mysql> SHOW TABLE STATUS WHERE name LIKE "audio";
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
| audio | InnoDB | 10 | Dynamic | 969276 | 164 | 159039488 | 0 | 90898432 | 7340032 | 1644653 | 2023-04-13 14:41:27 | 2023-04-17 14:56:21 | NULL | utf8mb4_0900_ai_ci | NULL | | |
PHP
$sql = "SELECT * FROM audio
WHERE associated_incident IS null
AND archive IS NULL
AND temp_skip IS null
AND length >= 3
AND (locked <> 1 or locked IS NULL)
ORDER BY `audio`.`id` DESC
LIMIT 1;
";
$result = mysqli_query($conn, $sql) or die(mysqli_error());
$audio = mysqli_fetch_assoc($result);
if ($audio) {
$audio_id = $audio['id'];
$audio_timestamp = $audio['timestamp'];
$audio_hash = $audio['hash'];
$audio_length = $audio['length'];
$ulockexpr = date("Y-m-d H:i:s", strtotime('+ 5min'));
$ulockuser = $_SESSION['login_user'];
$sql_lock = "UPDATE audio SET locked=1, lockexpr = '$ulockexpr', lockuser = '$ulockuser' WHERE id = $audio_id";
答案1
更改為innodb_buffer_pool_size = 500M-- 舊的預設值 128M 非常低。
如果這樣SELECT做是為了找到要處理的行，然後您這樣做UPDATE，請使用事務：
START TRANSACTION;
SELECT ... FROM audio ... FOR UPDATE;
... process ...
UPDATE audio SET ... WHERE id = ...;
COMMIT;
這START - FOR UPDATE - COMMIT將阻止兩個線程抓取同一個項目來處理。
考慮使用ORDER BY timestamp DESC而不是ORDER BY id DESC.
可能需要此配置才能查看有問題的語句：
log_slow_admin_statements = ON
桌上有哪些索引？請提供SHOW CREATE TABLE。
降低[是的，降低]max_connections至僅50。 和降低 Apache 設置，使其不會產生超過 40 個並發子進程。我看到那一Max_used_connections擊max_connections。當這種情況發生時，MySQL 將會崩潰，直到問題解決。降低限額將使其更快開始平倉。