MySQL如何充分利用系統資源？

littlevigra發表於2024-03-07
MySql

1.如果採取“先把資料放在記憶體，然後集中寫入磁碟”的辦法，可以節省 CPU 資源和磁碟讀取的時間，但是也會面臨系統故障時會丟失資料的風險；

相反，如果每次都寫入磁碟，資料最安全，但是頻繁的磁碟讀寫，會導致系統效率低下。這就需要我們提升最佳化資源配置的能力。

2. 調整系統引數 InnoDB_flush_log_at_trx_commit

預設的值是 1，意思是每次提交事務的時候，都把資料寫入日誌，並把日誌寫入磁碟。
2 表示，每次提交事務的時候都將資料寫入日誌，但是日誌每間隔 1 秒寫入磁碟。

這樣做的好處是資料安全性最佳，不足之處在於每次提交事務，都要進行磁碟寫入的操作。
在大併發的場景下，過於頻繁的磁碟讀寫會導致 CPU 資源浪費，系統效率變低。

引數的值改成了 2。這樣一來，就不用每次提交事務的時候都啟動磁碟讀寫了，
在大併發的場景下，可以改善系統效率，降低 CPU 使用率。即便出現故障，損失的資料也比較小。

3. 調整系統引數 InnoDB_buffer_pool_size

InnoDB 儲存引擎使用快取來儲存索引和資料。這個值越大，可以載入到快取區的索引和資料量就越多，需要的磁碟讀寫就越少。

50%

4. 調整系統引數 InnoDB_buffer_pool_instances

將 InnoDB 的快取區分成幾個部分，這樣一來，就可以提高系統的並行處理能力，因為可以允許多個程序同時處理不同部分的快取區。

把 InnoDB_buffer_pool_instances 的值修改為 64，意思就是把 InnoDB 的快取區分成 64 個分割槽，這樣就可以同時有多個程序進行資料操作，CPU 的效率就高多了。

利用監控資訊診斷問題

performance_schema.events_statements_history_long

先用下面的語句，來看一下哪些查詢消耗的時間多。這個表的資料量很大

mysql> SELECT
-> TRUNCATE(TIMER_WAIT / 1000000000000, 6) AS duration, -- 計算查詢的時長，單位是微微秒，需要轉換成秒
-> sql_text,
-> EVENT_ID
-> FROM
-> performance_schema.events_statements_history_long
-> WHERE
-> TRUNCATE(TIMER_WAIT / 1000000000000, 6) <> 0
-> AND sql_text IS NOT NULL
-> ORDER BY TRUNCATE(TIMER_WAIT / 1000000000000, 6) DESC
-> LIMIT 1,2;
+----------+---------------------------------+----------+
| duration | sql_text | EVENT_ID |
+----------+---------------------------------+----------+
| 137.2529 | select count(*) from demo.trans | 17 |
| 137.2420 | select count(*) from demo.trans | 907 |
+----------+---------------------------------+----------+
2 rows in set (0.00 sec)

相關文章