# High Concurrency 問題

###### tags: `其他`

以下記錄一些處理問題時常用的參數設定

高並發問題最有名是來自 Dan Kegel 1999 年提出的 [The C10K problem](http://www.kegel.com/c10k.html)，C 代表著 clients，也就是 web server 同時遇到 10 K clients 連線請求會遇到的問題。

雖然隨著技術進步，問題會得到解決，但也隨著網路應用普及，這個問題的數量級會越來越大，從 C10K 增加到 C10M

不管是 HTTP 或 WebSocket，都依賴 TCP 傳輸協議，也就是使用者瀏覽網頁或呼叫 API 都必須與 server 保持 TCP 連線。而每個 TCP 連線需要分配 process(or thread) 處理，process 會佔用 CPU 跟記憶體，以及不同 process 間 I/O block 問題，當然可以讓一個 process 儘量多處理 TCP 連線，但這跟 web server 架構和硬體上限有關係。

## Nginx

可參考 [Tuning NGINX for Performance](https://www.nginx.com/blog/tuning-nginx/)

### 參數

`worker_processes`：worker process 數目，建議依核心數設置
`worker_connections`: 最大連線數，default 是 512，不能大於 ulimit 數量
`keepalive_requests`: client 可要求 keep-alive 最大連線數，一般來說，serve 回應 client 端 HTTP 請求後就會斷開 TCP 連線，HTTP keep-alive 設置可以讓 TCP 連線維持著，下一次通訊就不需要再花時間進行 TCP 交握程序，但這會佔用到 TCP 連線
`keepalive_timeout`: 承上，設定一個 keepalive connection 最大閒置時間
`access_log`: 指令 log 路徑，記錄每一個 request 消耗的 CPU and I/O cycles
`sendfile`: 設定 on 可加速 TCP 資料的傳遞，Linux 標準 I/O 是基於資料複製過程：`硬碟 —> Kernel Sapce —> User Sapce —> socket buffer —> 取得資料`，藉由 cache 機制減少硬碟 I/O 次數，但大量複製的數據會不段地在 Kernel Sapce 跟 User Sapce 切換，而 sendfile 可減少複製次數，數據資料複製過程變成：`硬碟 —> Kernel Sapce —> socket buffer —> 取得資料`，但由於繞過 User Sapce，有些在 User Sapce 處理內文的模組將無法使用。
`limit_conn`: 限制每個 ip 或 virtual server 連線上限，限制內容可參考 [limit_conn](https://nginx.org/en/docs/http/ngx_http_limit_conn_module.html?&_ga=2.196020332.1146027932.1625709921-416106766.1625709921#limit_conn)

另外使用 [Content Caching](https://docs.nginx.com/nginx/admin-guide/content-cache/content-caching/#overview) 跟 [responses compression](https://docs.nginx.com/nginx/admin-guide/web-server/compression/?_ga=2.58073962.1146027932.1625709921-416106766.1625709921) 也能提升效能

## MYSQL

### 參數

- thread_cache_size: 每個連線都有對應的 thread，mysql 有 thread cache pool 存放空閑的 thread，如果 pool 裡沒空閒的 thread 才會建立新的 thread，`thread_cache_size` 即設定存放空閒的 thread 數目
- thread_stack: The stack size for each thread，每個 thread 被分配到的記憶體空間，如果太小，複雜的 SQL statements 將無法被處理
- net_buffer_length(bytes): 每個 client thread 會分配到一組 connection buffer & result buffer，初始值是 net_buffer_length，可動態擴充至 max_allowed_packet 大小，SQL statement 執行後將會收縮回原本 net_buffer_length 大小，通常機器記憶體太少才會去調整這數值
- max_allowed_packet(bytes): 上述 buffer 可擴展的最大值，有時候大的插入/更新/查詢會被 max_allowed_packet 限制導致失敗，另外資料表中有 BLOG or TEXT 類型欄位也建議加大上限，通常遇到 `communication packets` 相關的 error 就代表超過上限(EX: Got an error reading communication packets)
- innodb_buffer_pool_size: 為了加速數據讀取效率，MYSQL 會把常訪問數據放到 buffer pool 裡，避免每次都訪問磁碟，可根據記憶體大小配置 buffer pool，通常會設定記憶體的 50 ~ 75%，相對的，如果資料量大，需要給予更多的記憶體。

### 常用狀態檢查

- `show status like 'connections'`;
查看DB啟用至今累積的連線次數

- `show status like '%thread%'`;
查看 thread 使用狀況
- Threads_connected: 目前 thread 連線數
- Threads_created: 累積新建的 thread 連線數，如果數值太大，要增加 `thread_cache_size` 數值，將此數值除上 connections 數目可計算出 `cache miss rate`，意即沒有使用的 cache pool 的連線數
- Threads_running: 非 sleeping 狀態的 thread

### Logs
logs 包含 error log、slow query log、general log、audit log，查看 log 有助於釐清 DB 問題，尤其 high concurrency 下的 slow query 跟 locking 問題

#### Error log
紀錄錯誤的 log

- log_error: error log 儲存位置

#### Slow query log

開啟 slow query log，可釐清慢查詢的語句

- slow_query_log: `ON` or `OFF`，slow query log 開關，預設是關閉，記得要打開
- long_query_time: 慢查詢秒數，超過這秒數即視為慢查詢(預設 10 s)
- log_output: 與 general log 共用，可以設定 `FILE` or `TABLE`，`FILE` 可以在下面的參數中指定路徑，`TABLE` 會儲存在 mysql.slow_log
- slow_query_log_file: log 儲存位置，AWS RDS 有開啟 [Publishing MySQL logs to Amazon CloudWatch Logs](https://docs.amazonaws.cn/en_us/AmazonRDS/latest/UserGuide/USER_LogAccess.MySQLDB.PublishtoCloudWatchLogs.html) 會將預設路徑的 log 檔轉拋 CloudWatch

#### General log

general query log，開啟後會紀錄每一句 query，開啟的話要注意可能會塞爆硬碟

- general_log: `ON` or `OFF`，slow query log 開關，預設是關閉
- log_output: 與 general log 共用，可以設定 `FILE` or `TABLE`，`FILE` 可以在下面的參數中指定路徑，`TABLE` 會儲存在 mysql.general_log
- general_log_file: log 儲存位置，AWS RDS 有開啟 [Publishing MySQL logs to Amazon CloudWatch Logs](https://docs.amazonaws.cn/en_us/AmazonRDS/latest/UserGuide/USER_LogAccess.MySQLDB.PublishtoCloudWatchLogs.html) 會將預設路徑的 log 檔轉拋 CloudWatch

#### Audit log
要另外安裝 Audit Plugin，它會紀錄誰跟 DB 連線(user name, host 等)、執行哪些 SQL、server 參數異動等，詳情可參考 [MySQL Enterprise Audit](https://dev.mysql.com/doc/refman/8.0/en/audit-log.html) 或 [MariaDB Audit Plugin](https://mariadb.com/kb/en/mariadb-audit-plugin/)

以下指令可以查詢有無安裝 Audit Plugin

SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'audit%';

AWS RDS 可透過 [Option Group](https://docs.aws.amazon.com/AWSCloudFormation/latest/UserGuide/aws-resource-rds-optiongroup.html) 設置 `MARIADB_AUDIT_PLUGIN` option，套用會安裝 audit plugin，設定流程可參考 [Configuring an audit log to capture database activities](https://aws.amazon.com/tw/blogs/database/configuring-an-audit-log-to-capture-database-activities-for-amazon-rds-for-mysql-and-amazon-aurora-with-mysql-compatibility/)

- server_audit_logging: `ON` or `OFF`，audit log 開關，有安裝 audit plugin 該屬性才會有作用

### 優化 SQL statement

#### EXPLAIN

`SELECT` 前加入 `EXPLAIN` 可以進行 SQL 語句分析，會顯示 mysql 讀取順序，以及分析結果，幫助我們近一步優化 SQL

EXPLAIN SELECT * FROM <Table> WHERE ...

以下介紹欄位意思

- select_type: 查詢類型
- SIMPLE: 簡單查詢
- PRIMARY: 主查詢，有 SUBQUERY 時才會出現
- SUBQUERY: 子查詢
- UNION: 有執行 UNION 會出現
- UNION RESULT: 有執行 UNION 會出現，顯示連接幾個表的查詢結果
- type: 有十種類型，效率快至慢依序為

    NULL > system > const > eq_ref > ref > ref_or_null > index_merge > range > index > ALL

最慢的是 `ALL`，代表這 SQL 進行全表搜索，這時就必須設定 `index` 增加搜尋效率，`range` 是索引的範圍搜索，效率也較 `index` 高，如果使用 INNER JOIN，被 JOIN 的表會出現 `eq_ref` type，實務上有發生 `range` + `eq_ref` 兩次搜索的效率高於只有單次搜索的 `index`
- key: 使用到的索引，沒有用到會顯示 `NULL`
- rows: 要檢查的筆數，越小越好囉，代表只搜索小範圍資料就找到結果了

更多內容參考 [官方 EXPLAIN Output Format 章節](https://dev.mysql.com/doc/refman/8.0/en/explain-output.html)

### Index
MySQL Innodb 主鍵(PK)索引資料儲存在 B+ Tree，是 B Tree 延伸，層級更少，查詢效率更均衡，結構差異可以參考 https://ithelp.ithome.com.tw/articles/10221111

另外，資料在進行新增刪除時，tree 節點可能會進行整併或分割，規則可以參考 https://z1nhouse.github.io/post/5lQAWUQWk/

如果 Innodb 新增其他索引，也會形成另一個 B+ Tree 的索引結構，只是最底層的節點存的是 PK 值，索引找到 PK 值後，再去主鍵索引查實際資料。如果查詢的欄位剛好在索引中(EX：只查詢 Id & Name)，則可以不用再去主鍵索引查實際資料


了解上面特性後，創建索引有幾個原則：
- 每筆資料對該欄位的差異性越大越好，通過索引可以得到更小的範圍，需要查詢的行數也越小
- 複合索引有最左匹配原則(leftmost prefix)，例如 index 欄位為 (col1, col2, col3)，那 (col1, col2)、(col1, col3)、(col1, col2, col3) 可以透過索引匹配，而 (col2, col3) 則無法匹配，所以 col1 最好是常被搜尋到且差異性更大的欄位
- `ORDER BY`、`GROUP BY` 欄位順序要符合最左匹配原則，`ORDER BY` 各欄位要統一 `ASC` or `DESC`
- 增加索引會增加硬碟使用空間，而且會降低新增/刪除速度，所以要合理的設計索引數量