因應臺中市網路應用競賽的需求，
自己開發了一套校內初賽專用的系統，
因為是比賽的系統，所以會有短時間大量同時登入的情況，也就是所謂的「高併發」現象，
問題來了，每次只要帶著全班一起上線練打字，
常常會發生伺服器的 DISK I/O 瞬間飇高,
然後接著大約有 5~10分鐘, 系統就無法登入了。
怪的是, 把所有服務重啟, 無效! 把伺服器重新開機, 也無效! 就是得等 I/O 恢復正常。
卡死的時候 ssh 還是能登入系統, 而且 CPU 、網卡都是閒置狀態。
以前寫過的系統都是行政業務在用的，基本上不太會有幾十個人同時要登入的情況。
所以第一次遇到這個問題時真的是手足無措, 因為根本沒有經驗。
只能開始上網查找答案......
因為是無法連線, 出現 HTTP 504 的錯誤, 所以先從 Web Server 開始,
先是調校了 Apache 的設定, 發現沒用, 後來改用 NGINX , 狀況似乎好一點, 但是還是會卡死,
有一次發現, 當系統卡死時, 用 IP 連線純 HTML 網頁是可以的, 所以認定不是 Web Server 的問題。
接著開始調校 PHP-fpm
後來又發現 當系統卡死時, 用 IP 連線 , 執行 phpinfo(); 是可以看到 php 資訊的, 所以認定 PHP-fpm 是正常的。
最後, 關鍵原因只剩 MariaDB 了.
況且, DISK I/O 瞬間飇高 和資料庫的讀取/寫入較有關係, 所以調校 MariaDB 似乎也合理.
這期間, 重灌過不同版本的 MariaDB 、虛擬機移機、改用不同的作業系統 (FreeBSD 、CentOS) .....
都一直沒有解決這個狀況, 真的只能說是慘痛的經驗來形容。
前天, 花了一些時間把 MariaDB 與 cache 和 buffer 的相關設定徹底弄清楚, 並進行調校設定, 這兩天全班同時登入,
已經沒有發生 DISK I/O 異常飇高而導致無法登入的情況了。
問題解決了嗎? 不知道, 還要再觀察。
我使用的系統是 FreeBSD 13 + NGINX 1.20.1 + PHP-fpm 7.4 + MariaDB 10.3 , 16GB RAM
#以下是關於 MariaDB 設定的筆記, 記錄在這裡 , 所有的說明是來自網路上各方搜集而得.
#適用於主機 RAM 8GB-16GB , MariaDB 10.3 以上
#是否收集一般LOG , 平常設為 OFF , 需要觀察時再打開 , 不然 log 檔會很可怕
general_log = OFF
general_log_file = /var/log/mysql/mycustom.log
#CPU核心數
thread_concurrency = 8
#InnoDB核心内的允許線程数量 , 建議為 CPU*2
#10.5 版後沒有這個參數了
innodb_thread_concurrency = 16
#mysql的最大連線數
max_connections = 600
#如果有時網路不穩，或者應用配置錯誤，或者其他原因導致客戶端短時間內不斷的嘗試連接，
#客戶端可能會被列入黑名單，然後將無法連接，直到再次刷新主機緩存。這個選項默認值太小
#了，可以考慮設的足夠大（如果你的服務器配置夠強大的話）
max_connect_errors = 500
#Timeout 設定
wait_timeout = 60
connect_timeout=10
#資料插入上限 預設 16M
max_allowed_packet = 50M
#各種 buffer
#這個參數用來緩存MyISAM存儲引擎的索引參數。MySQL5.5默認爲InnoDB存儲引擎，所以這個參數可以設置小點，64MB即可
key_buffer_size = 32M
#各緩衝區 每個線程可用的量(不能設太大, 會吃掉所有的記憶體)
#該參數用於表的順序掃描，表示每個線程分配的緩衝區大小。比如在進行全表掃描時，
#MySQL會按照數據的存儲順序依次讀取數據塊，每次讀取的數據塊首先會暫存在read_buffer_size中，
#當buffer空間被寫滿或者全部數據讀取結束後，再將buffer中的數據返回給上層調用者，
#以提高效率。默認爲128K，這個參數不要設置過大，一般在128～256之間。
read_buffer_size = 768K
#該參數用於表的隨機讀取，表示每個線程分配的緩衝區大小
#比如，按照一個非索引字段做order by排序操作時，就會利用這
#個緩衝區來暫存讀取的數據。默認爲256KB，這個參數不要設置過大，一般在128～512KB
read_rnd_buffer_size = 512K
#當對MyISAM表執行repair table或創建索引時，用以緩存排序索引
myisam_sort_buffer_size = 8M
#在表進行order by和group by排序操作時，由於排序的字段沒有索引，會出現Using filesort，
#爲了提高性能，可用此參數增加每個線程分配的緩衝區大小。默認爲256KB，這個參數不要設置過大，一般在128～256KB即可
sort_buffer_size = 768K
#包消息緩衝區初始化爲net_buffer_length字節，但需要時可以增長到max_allowed_packet字節
net_buffer_length = 8K
#MySQL每打開一個表，都會讀入一些數據到table_open_cache緩存中，
#當MySQL在這個緩存中找不到相應信息時，纔會去磁盤上讀取。默認值64，
#假定系統有200個併發連接，則需將此參數設置爲200*N(N爲每個連接所需的文件描述符數目)；
#當把table_open_cache設置爲很大時，如果系統處理不了那麼多文件描述符，那麼就會出現客戶端失效，連接不上。
#通過檢查峰值時間的狀態值Open_tables和Opened_tables,可以決定是否需要增加table_open_cache的值。
#檢查方式 SHOW GLOBAL STATUS LIKE 'Open%tables';
#檢查變數的方式 show variables like '%table_open%';
#如果你發現open_tables等於table_open_cache,並且opened_tables在不斷增長,那麼你就需要增加
table_open_cache = 512
table_open_cache_instances = 8
#臨時表的大小，mysql查詢時會建立, 可提高查詢速度
#配置臨時表容量和內存表最大容量,這兩個變量的大小應該相同，它們可以讓你避免磁盤寫入。
#管理員通常建議在服務器上設置這兩個值為實體記憶體每 GB 內存給64M。
#注意! 以下, 這兩個變量的大小應該相同! 否則高併發時 DISK I/O 容易飇超高!
tmp_table_size = 128M
max_heap_table_size = 128M
#執行緒的設定
#線程池，線程緩存。用來緩存空閒的線程，以至於不被銷燬，如果線程緩存在的空閒線程，需要重新建立新連接，則會優先調用線程池中的緩存，
#很快就能響應連接請求。每建立一個連接，都需要一個線程與之匹配。
#可以利用 show global status like 'Thread%'; 觀察 Threads_created：建立過的執行緒數，
#如果發現Threads_created值過大的話，表明MySQL伺服器一直在建立執行緒，這也是比較耗資源
#，可以適當增加配置檔案中thread_cache_size值#
thread_cache_size = 48
#快取優化
#可以利用 show variables like '%query_cache%'; 觀察快取設定
#利用 show status like '%Qcache%'; 觀察命中率 Qcache_hits 了解效果
#
#通常設置為200-300 MB應該足夠,不要設置太大 ,設置為GB級，實際上會降低服務器的性能
#緩存select語句和結果集大小的參數。查詢緩存會存儲一個select查詢的文本與被傳送到客戶端的相應結果。如果之後接收到一個相同的查詢，服務器會從查詢緩存中檢索結果
#如果你的環境中寫操作很少，讀操作頻繁，那麼打開query_cache_type=1，會對性能有明顯提升。如果寫操作頻繁，則應該關閉它 query_cache_type=0
query_cache_type = 1
query_cache_limit = 256K
query_cache_min_res_unit = 2k
query_cache_size = 256M
#在你的伺服器上允許 InnoDB 使用實體記憶體數量的 60%~80%
#2021/09/27 不要設太大試試, 看看是否是因為緩衝滿了一次大量寫入硬碟造成 I/O 飇高
innodb_buffer_pool_size=1024M
join_buffer_size = 8M
#讓 InnoDB 多工執行 , 同時可執行幾個線程
innodb_buffer_pool_instances = 2
innodb_file_per_table = ON
innodb_stats_on_metadata = OFF
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
#把每一次 Deadlock 原因都輸出到 error log 中
innodb_print_all_deadlocks=ON;
#找出執行慢的 log
slow_query_log = 1
long_query_time = 0.8
slow_query_log_file = /var/log/mysql/slow_query.log
#忽略解DNS
skip-name-resolve
#跳過外部鎖定
skip-external-locking
#啓用二進制日誌後，保留日誌的天數。服務器會自動清理指定天數前的日誌文件，如果不設置則會導致服務器空間耗盡。一般設置爲7～14天
expire_logs_days = 7
