Ubuntu 22.04 MySQL InnoDB 調校

本文將深入介紹如何在 Ubuntu 22.04 上調校 MySQL InnoDB 儲存引擎，以獲得最佳的資料庫效能。我們將探討 InnoDB 的核心架構、關鍵參數設定，以及生產環境的最佳實踐。

1. InnoDB 架構概述

InnoDB 是 MySQL 預設的儲存引擎，具備 ACID 交易支援、行級鎖定（Row-level Locking）、外鍵約束等特性。了解其內部架構是進行效能調校的基礎。

InnoDB 核心元件

InnoDB 的架構主要由以下元件組成：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
┌─────────────────────────────────────────────────────────────┐
│                     MySQL Server Layer                       │
├─────────────────────────────────────────────────────────────┤
│                     InnoDB Storage Engine                    │
│  ┌─────────────────────────────────────────────────────┐    │
│  │                   Buffer Pool                        │    │
│  │  ┌──────────┐ ┌──────────┐ ┌──────────┐            │    │
│  │  │Data Pages│ │Index Pages│ │Undo Pages│            │    │
│  │  └──────────┘ └──────────┘ └──────────┘            │    │
│  │  ┌──────────┐ ┌──────────┐ ┌──────────┐            │    │
│  │  │Change Buf│ │Adaptive  │ │Lock Info │            │    │
│  │  │   fer    │ │Hash Index│ │          │            │    │
│  │  └──────────┘ └──────────┘ └──────────┘            │    │
│  └─────────────────────────────────────────────────────┘    │
│  ┌────────────────┐  ┌────────────────┐                     │
│  │   Log Buffer   │  │  Redo Log Files │                    │
│  └────────────────┘  └────────────────┘                     │
│  ┌────────────────────────────────────────────────────┐     │
│  │              Tablespace Files (.ibd)                │     │
│  └────────────────────────────────────────────────────┘     │
└─────────────────────────────────────────────────────────────┘

關鍵概念說明

元件	說明
Buffer Pool	記憶體中用於快取資料頁和索引頁的區域
Log Buffer	暫存 Redo Log 記錄的緩衝區
Redo Log	記錄資料變更的日誌，用於崩潰恢復
Undo Log	記錄交易的舊值，支援 MVCC 和回滾操作
Change Buffer	快取對非唯一次要索引的變更
Adaptive Hash Index	自動建立的雜湊索引，加速查詢

查看目前 InnoDB 狀態

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
# 登入 MySQL
mysql -u root -p

# 查看 InnoDB 狀態
SHOW ENGINE INNODB STATUS\G

# 查看 InnoDB 相關變數
SHOW VARIABLES LIKE 'innodb%';

# 查看 InnoDB 緩衝池統計
SHOW STATUS LIKE 'Innodb_buffer_pool%';

2. Buffer Pool 設定與調校

Buffer Pool 是 InnoDB 最重要的記憶體區域，用於快取資料頁、索引頁和其他結構。適當的 Buffer Pool 設定對效能有顯著影響。

計算建議的 Buffer Pool 大小

一般建議將 Buffer Pool 設定為可用記憶體的 50-80%。以下是計算方式：

1
2
3
4
5
# 查看系統總記憶體
free -h

# 查看目前 Buffer Pool 設定
mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"

設定 Buffer Pool

編輯 MySQL 設定檔：

1
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

在 [mysqld] 區段加入以下設定：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
[mysqld]
# Buffer Pool 設定
# 假設伺服器有 16GB 記憶體，設定為 10-12GB
innodb_buffer_pool_size = 10G

# Buffer Pool 實例數量
# 建議每個實例至少 1GB，多實例可減少鎖定競爭
innodb_buffer_pool_instances = 8

# 允許線上調整 Buffer Pool 大小的區塊單位
innodb_buffer_pool_chunk_size = 128M

Buffer Pool 預熱

MySQL 8.0 支援在關閉時保存 Buffer Pool 狀態，並在啟動時自動載入：

1
2
3
4
5
6
7
[mysqld]
# 啟用 Buffer Pool 狀態保存
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup = ON

# 保存的頁面比例（預設 25%）
innodb_buffer_pool_dump_pct = 40

線上調整 Buffer Pool

MySQL 8.0 允許在執行中動態調整 Buffer Pool 大小：

1
2
3
4
5
6
7
8
-- 查看目前大小（以位元組為單位）
SELECT @@innodb_buffer_pool_size / 1024 / 1024 / 1024 AS size_gb;

-- 動態調整 Buffer Pool 大小
SET GLOBAL innodb_buffer_pool_size = 12884901888;  -- 12GB

-- 監控調整進度
SHOW STATUS LIKE 'Innodb_buffer_pool_resize_status';

監控 Buffer Pool 效率

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
-- 計算 Buffer Pool 命中率
SELECT
    (1 - (
        (SELECT VARIABLE_VALUE FROM performance_schema.global_status
         WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads') /
        (SELECT VARIABLE_VALUE FROM performance_schema.global_status
         WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests')
    )) * 100 AS buffer_pool_hit_rate;

-- 查看詳細的 Buffer Pool 統計
SELECT * FROM information_schema.INNODB_BUFFER_POOL_STATS\G

理想的 Buffer Pool 命中率應該在 99% 以上。

3. Log File 與 Redo Log

Redo Log 是 InnoDB 確保交易持久性（Durability）的關鍵機制。適當的 Log 設定可以平衡效能與資料安全性。

Redo Log 設定

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
[mysqld]
# Redo Log 檔案大小（MySQL 8.0.30+）
innodb_redo_log_capacity = 2G

# 舊版 MySQL 設定方式（8.0.30 以前）
# innodb_log_file_size = 512M
# innodb_log_files_in_group = 2

# Log Buffer 大小
innodb_log_buffer_size = 64M

# Flush 行為設定
# 0: 每秒 flush 一次（最快，但可能遺失 1 秒資料）
# 1: 每次交易 commit 時 flush（最安全，預設值）
# 2: 每次 commit 寫入 OS buffer，每秒 flush 到磁碟
innodb_flush_log_at_trx_commit = 1

選擇適當的 Flush 策略

值	行為	效能	安全性
0	每秒 flush	最快	可能遺失 1 秒資料
1	每次 commit flush	最慢	完全 ACID 合規
2	commit 寫 OS buffer	中等	作業系統崩潰可能遺失資料

生產環境建議使用 innodb_flush_log_at_trx_commit = 1。若可接受些微資料遺失風險，可使用 2 以獲得更好效能。

監控 Redo Log

1
2
3
4
5
6
7
-- 查看 Redo Log 狀態
SHOW STATUS LIKE 'Innodb_log%';

-- 查看 Redo Log 等待統計
SHOW STATUS LIKE 'Innodb_log_waits';

-- 若 Innodb_log_waits > 0，表示 Log Buffer 太小

計算適當的 Redo Log 大小

Redo Log 應該足夠容納約 1 小時的寫入量。使用以下查詢估算：

1
2
3
4
5
6
7
8
-- 查看每小時的 Redo Log 寫入量
SELECT
    @a := (SELECT VARIABLE_VALUE FROM performance_schema.global_status
           WHERE VARIABLE_NAME = 'Innodb_os_log_written'),
    SLEEP(60),
    @b := (SELECT VARIABLE_VALUE FROM performance_schema.global_status
           WHERE VARIABLE_NAME = 'Innodb_os_log_written'),
    ROUND((@b - @a) * 60 / 1024 / 1024, 2) AS 'MB per hour';

4. Flush 方法與 I/O 優化

I/O 是資料庫效能的關鍵瓶頸。正確設定 Flush 方法和 I/O 相關參數可顯著提升效能。

Flush 方法設定

1
2
3
4
5
6
7
8
[mysqld]
# Linux 系統建議使用 O_DIRECT
# 可選值: fsync, O_DSYNC, O_DIRECT, O_DIRECT_NO_FSYNC
innodb_flush_method = O_DIRECT

# 雙寫緩衝區（Double Write Buffer）
# SSD 可考慮關閉以提升效能（需評估風險）
innodb_doublewrite = ON

I/O 容量設定

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
[mysqld]
# I/O 容量設定
# HDD: 200-400
# SSD: 2000-5000
# NVMe: 5000-10000
innodb_io_capacity = 2000

# 最大 I/O 容量（用於 flush 落後時）
innodb_io_capacity_max = 4000

# 讀寫執行緒數量
innodb_read_io_threads = 8
innodb_write_io_threads = 8

檔案系統優化

1
2
3
4
5
6
7
8
# 查看目前掛載選項
mount | grep '/var/lib/mysql'

# 建議的 fstab 設定（XFS 檔案系統）
# /dev/sdb1 /var/lib/mysql xfs noatime,nodiratime,nobarrier 0 0

# 建議的 fstab 設定（ext4 檔案系統）
# /dev/sdb1 /var/lib/mysql ext4 noatime,nodiratime,data=writeback 0 0

Linux I/O 排程器設定

對於 SSD，建議使用 none 或 mq-deadline 排程器：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
# 查看目前排程器
cat /sys/block/sda/queue/scheduler

# 暫時變更排程器（重開機後失效）
echo 'none' | sudo tee /sys/block/sda/queue/scheduler

# 永久設定（編輯 GRUB）
sudo nano /etc/default/grub
# 加入: GRUB_CMDLINE_LINUX_DEFAULT="elevator=none"
sudo update-grub

5. 併發參數調校

適當的併發設定可以確保系統在高負載下維持穩定效能。

執行緒與連線設定

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
[mysqld]
# 最大連線數
max_connections = 500

# 執行緒快取
thread_cache_size = 64

# InnoDB 併發執行緒限制
# 0 = 不限制（MySQL 8.0 預設值，適合多數情況）
innodb_thread_concurrency = 0

# 併發 tickets
innodb_concurrency_tickets = 5000

# 執行緒進入 InnoDB 前的等待時間（微秒）
innodb_thread_sleep_delay = 10000

鎖定相關設定

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
[mysqld]
# 鎖定等待逾時（秒）
innodb_lock_wait_timeout = 50

# 死鎖偵測
# 高併發 OLTP 可考慮關閉以減少開銷
innodb_deadlock_detect = ON

# 自動遞增鎖定模式
# 0 = traditional
# 1 = consecutive（預設）
# 2 = interleaved（最高效能，但可能產生間隔）
innodb_autoinc_lock_mode = 2

監控併發狀態

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
-- 查看目前連線狀態
SHOW STATUS LIKE 'Threads%';

-- 查看目前執行中的查詢
SHOW PROCESSLIST;

-- 查看 InnoDB 行鎖定統計
SHOW STATUS LIKE 'Innodb_row_lock%';

-- 查看鎖定等待
SELECT * FROM performance_schema.data_lock_waits\G

6. 索引與查詢優化

良好的索引設計和查詢優化是資料庫效能的基礎。

索引設計原則

1
2
3
4
5
6
7
8
-- 查看資料表的索引
SHOW INDEX FROM table_name;

-- 分析查詢使用的索引
EXPLAIN SELECT * FROM table_name WHERE column = 'value';

-- 使用 EXPLAIN ANALYZE 取得實際執行資訊
EXPLAIN ANALYZE SELECT * FROM table_name WHERE column = 'value';

常用索引優化建議

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
-- 為經常查詢的欄位建立索引
CREATE INDEX idx_column ON table_name(column);

-- 複合索引（遵循最左前綴原則）
CREATE INDEX idx_multi ON table_name(col1, col2, col3);

-- 覆蓋索引（Include 常用查詢欄位）
CREATE INDEX idx_covering ON table_name(col1) INCLUDE (col2, col3);

-- 刪除未使用的索引
DROP INDEX idx_unused ON table_name;

查詢優化設定

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
[mysqld]
# 查詢快取（MySQL 8.0 已移除，這裡僅作參考）
# query_cache_type = 0

# 排序緩衝區
sort_buffer_size = 4M

# Join 緩衝區
join_buffer_size = 4M

# 讀取緩衝區
read_buffer_size = 2M
read_rnd_buffer_size = 8M

# 臨時表大小
tmp_table_size = 64M
max_heap_table_size = 64M

識別慢查詢

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
[mysqld]
# 啟用慢查詢日誌
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log

# 慢查詢門檻（秒）
long_query_time = 2

# 記錄未使用索引的查詢
log_queries_not_using_indexes = ON

分析慢查詢日誌：

1
2
3
4
5
6
# 使用 mysqldumpslow 分析
sudo mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# 使用 pt-query-digest 進行更詳細分析
sudo apt install percona-toolkit
pt-query-digest /var/log/mysql/slow.log > slow_analysis.txt

7. 監控與診斷工具

有效的監控是維持資料庫效能的關鍵。

MySQL 內建監控

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
-- 啟用 Performance Schema（預設開啟）
SHOW VARIABLES LIKE 'performance_schema';

-- 查看最耗資源的查詢
SELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC LIMIT 10\G

-- 查看資料表 I/O 統計
SELECT * FROM performance_schema.table_io_waits_summary_by_table
ORDER BY SUM_TIMER_WAIT DESC LIMIT 10\G

-- 查看鎖定等待
SELECT * FROM performance_schema.events_waits_summary_global_by_event_name
WHERE EVENT_NAME LIKE '%lock%'
ORDER BY SUM_TIMER_WAIT DESC\G

InnoDB 狀態監控

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
-- 詳細的 InnoDB 狀態
SHOW ENGINE INNODB STATUS\G

-- Buffer Pool 統計
SELECT
    pool_id,
    pool_size * 16 / 1024 AS pool_size_mb,
    free_buffers * 16 / 1024 AS free_mb,
    database_pages * 16 / 1024 AS data_mb,
    modified_database_pages * 16 / 1024 AS dirty_mb
FROM information_schema.INNODB_BUFFER_POOL_STATS;

-- 交易狀態
SELECT * FROM information_schema.INNODB_TRX\G

系統層級監控

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
# 安裝監控工具
sudo apt install sysstat iotop htop

# CPU 和記憶體監控
htop

# I/O 監控
sudo iotop -o

# 磁碟 I/O 統計
iostat -x 1 10

# MySQL 專用監控指令
mysqladmin -u root -p extended-status | grep -i innodb

第三方監控工具

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
# 安裝 Percona Monitoring and Management (PMM)
# 下載並安裝 PMM Client
wget https://repo.percona.com/apt/percona-release_latest.generic_all.deb
sudo dpkg -i percona-release_latest.generic_all.deb
sudo apt update
sudo apt install pmm2-client

# 設定 PMM Client 連接到 PMM Server
pmm-admin config --server-insecure-tls --server-url=https://admin:admin@pmm-server:443
pmm-admin add mysql --query-source=perfschema --username=pmm --password=pass mysql-server

建立自訂監控腳本

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
#!/bin/bash
# /usr/local/bin/mysql-monitor.sh

# 設定變數
MYSQL_USER="monitor"
MYSQL_PASS="monitor_password"
LOG_FILE="/var/log/mysql/performance.log"

# 收集效能資料
mysql -u$MYSQL_USER -p$MYSQL_PASS -e "
SELECT
    NOW() AS timestamp,
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status
     WHERE VARIABLE_NAME = 'Questions') AS queries,
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status
     WHERE VARIABLE_NAME = 'Threads_connected') AS connections,
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status
     WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests') AS bp_reads,
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status
     WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads') AS bp_reads_disk;
" >> $LOG_FILE

# 設定 crontab 每分鐘執行
# */1 * * * * /usr/local/bin/mysql-monitor.sh

8. 生產環境設定範例

以下是針對不同規模伺服器的完整設定範例。

小型伺服器（4GB RAM）

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
[mysqld]
# 基本設定
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
log-error = /var/log/mysql/error.log

# 字元集
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# Buffer Pool（約 50% RAM）
innodb_buffer_pool_size = 2G
innodb_buffer_pool_instances = 2

# Log 設定
innodb_redo_log_capacity = 512M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 1

# I/O 設定
innodb_flush_method = O_DIRECT
innodb_io_capacity = 200
innodb_io_capacity_max = 400
innodb_read_io_threads = 4
innodb_write_io_threads = 4

# 連線設定
max_connections = 100
thread_cache_size = 16

# 查詢優化
sort_buffer_size = 2M
join_buffer_size = 2M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
tmp_table_size = 32M
max_heap_table_size = 32M

# 慢查詢日誌
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

中型伺服器（16GB RAM）

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
[mysqld]
# 基本設定
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
log-error = /var/log/mysql/error.log

# 字元集
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# Buffer Pool（約 70% RAM）
innodb_buffer_pool_size = 11G
innodb_buffer_pool_instances = 8
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup = ON
innodb_buffer_pool_dump_pct = 40

# Log 設定
innodb_redo_log_capacity = 2G
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 1

# I/O 設定
innodb_flush_method = O_DIRECT
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_doublewrite = ON

# 連線設定
max_connections = 300
thread_cache_size = 32
innodb_thread_concurrency = 0

# 鎖定設定
innodb_lock_wait_timeout = 50
innodb_deadlock_detect = ON
innodb_autoinc_lock_mode = 2

# 查詢優化
sort_buffer_size = 4M
join_buffer_size = 4M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
tmp_table_size = 64M
max_heap_table_size = 64M

# 慢查詢日誌
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = ON

# Performance Schema
performance_schema = ON

大型伺服器（64GB+ RAM）

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
[mysqld]
# 基本設定
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
log-error = /var/log/mysql/error.log

# 字元集
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# Buffer Pool（約 75% RAM）
innodb_buffer_pool_size = 48G
innodb_buffer_pool_instances = 16
innodb_buffer_pool_chunk_size = 1G
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup = ON
innodb_buffer_pool_dump_pct = 40

# Log 設定
innodb_redo_log_capacity = 8G
innodb_log_buffer_size = 256M
innodb_flush_log_at_trx_commit = 1

# I/O 設定
innodb_flush_method = O_DIRECT
innodb_io_capacity = 5000
innodb_io_capacity_max = 10000
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_doublewrite = ON

# Page Cleaner
innodb_page_cleaners = 8
innodb_lru_scan_depth = 2048

# Purge 設定
innodb_purge_threads = 4
innodb_purge_batch_size = 300

# 連線設定
max_connections = 1000
thread_cache_size = 128
innodb_thread_concurrency = 0
back_log = 512

# 鎖定設定
innodb_lock_wait_timeout = 50
innodb_deadlock_detect = ON
innodb_autoinc_lock_mode = 2

# 查詢優化
sort_buffer_size = 8M
join_buffer_size = 8M
read_buffer_size = 4M
read_rnd_buffer_size = 16M
tmp_table_size = 128M
max_heap_table_size = 128M

# 慢查詢日誌
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.5
log_queries_not_using_indexes = ON

# Performance Schema
performance_schema = ON

# Binary Log（用於複製）
log_bin = /var/lib/mysql/binlog
binlog_format = ROW
binlog_row_image = MINIMAL
expire_logs_days = 7
max_binlog_size = 500M
sync_binlog = 1

套用設定並重啟

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
# 備份現有設定
sudo cp /etc/mysql/mysql.conf.d/mysqld.cnf /etc/mysql/mysql.conf.d/mysqld.cnf.bak

# 編輯設定檔
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

# 檢查設定語法
mysqld --validate-config

# 重啟 MySQL
sudo systemctl restart mysql

# 確認服務狀態
sudo systemctl status mysql

# 查看錯誤日誌
sudo tail -f /var/log/mysql/error.log

效能驗證腳本

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
#!/bin/bash
# /usr/local/bin/mysql-health-check.sh

echo "=== MySQL InnoDB 健康檢查 ==="
echo ""

mysql -u root -p -e "
SELECT '=== Buffer Pool 統計 ===' AS '';
SELECT
    CONCAT(ROUND(@@innodb_buffer_pool_size/1024/1024/1024, 2), ' GB') AS 'Buffer Pool Size',
    CONCAT(ROUND((SELECT VARIABLE_VALUE FROM performance_schema.global_status
                  WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_data') * 16 / 1024, 2), ' MB') AS 'Data Pages',
    CONCAT(ROUND((SELECT VARIABLE_VALUE FROM performance_schema.global_status
                  WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_dirty') * 16 / 1024, 2), ' MB') AS 'Dirty Pages';

SELECT '=== Buffer Pool 命中率 ===' AS '';
SELECT
    CONCAT(ROUND((1 - (
        (SELECT VARIABLE_VALUE FROM performance_schema.global_status
         WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads') /
        NULLIF((SELECT VARIABLE_VALUE FROM performance_schema.global_status
         WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'), 0)
    )) * 100, 2), '%') AS 'Hit Rate';

SELECT '=== 連線狀態 ===' AS '';
SELECT
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status
     WHERE VARIABLE_NAME = 'Threads_connected') AS 'Connected',
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status
     WHERE VARIABLE_NAME = 'Threads_running') AS 'Running',
    @@max_connections AS 'Max Connections';

SELECT '=== 鎖定統計 ===' AS '';
SELECT
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status
     WHERE VARIABLE_NAME = 'Innodb_row_lock_waits') AS 'Row Lock Waits',
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status
     WHERE VARIABLE_NAME = 'Innodb_row_lock_time_avg') AS 'Avg Wait Time (ms)';
"

結論

MySQL InnoDB 效能調校是一個持續的過程，需要根據實際工作負載進行調整。本文介紹的關鍵調校領域包括：

Buffer Pool：設定適當的大小和實例數量，確保高命中率
Redo Log：平衡效能與資料安全性
I/O 優化：選擇正確的 flush 方法和 I/O 容量
併發控制：根據負載調整連線和執行緒設定
索引優化：設計高效的索引策略
持續監控：使用適當的工具監控效能指標

建議在進行任何調校之前先進行基準測試，並在變更後持續監控系統表現。每個生產環境都有其獨特的工作負載特性，需要根據實際情況進行微調。