[精讚] [會員登入]
361

MariaDB 的效能調整筆記

因應臺中市網路應用競賽的需求, 自己開發了一套校內初賽專用的系統, 因為是比賽的系統,所以會有短時間大量同時登入的情況,

分享此文連結 //n.sfs.tw/15411

分享連結 MariaDB 的效能調整筆記@杜子的網管筆記
(文章歡迎轉載,務必尊重版權註明連結來源)
2021-09-29 12:43:01 最後編修
2021-09-29 12:31:55 By 杜子
 

因應臺中市網路應用競賽的需求,

自己開發了一套校內初賽專用的系統,

因為是比賽的系統,所以會有短時間大量同時登入的情況,也就是所謂的「高併發」現象,

問題來了,每次只要帶著全班一起上線練打字,

常常會發生伺服器的 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
 

END

你可能感興趣的文章

Let's encrypt 在 FreeBSD 裡自動更新的方式 稍早收到 Let's Emcrypt 的 E-mail 通知, 說我的憑證有效期剩 19 天, 要趕快更新。 這

MariaDB 的效能調整筆記 因應臺中市網路應用競賽的需求, 自己開發了一套校內初賽專用的系統, 因為是比賽的系統,所以會有短時間大量同時登入的情況,

我有話要說


限制:留言最高字數1000字。 限制:未登入訪客,每則留言間隔需超過10分鐘,每日最多5則留言。

訪客留言

[無留言]

隨機好文

Windows 10 網芳功能失效 今天有位同事會著筆電過來,說他的電腦無法連線網路磁碟, 以往在 Windows 系統裡,只要在檔案總管的網趾列,打上 「

Windows 手動設定 IP ,重開機後自動消失 今天幫同事處理一部 Windows 10 的 IP 設定問題, 手動設定完,重開機後原本設定好的 IP 會憑空消失, 全

如何安裝辦公室印表機 本篇是針對豐南國中所寫的,其他網友其參考一下就好。 學校各導師辦公室及專任辦公室的印表機使用年限大概都有 8年以上了,廠

Let's encrypt 在 FreeBSD 裡自動更新的方式 稍早收到 Let's Emcrypt 的 E-mail 通知, 說我的憑證有效期剩 19 天, 要趕快更新。 這

Windown 10 新增「微軟新倉頡」輸入法的方法 Windown 10 的中文輸入法一直是我覺得微軟做得很爛的一個地方, 微軟注音輸入法還好,有細部的選項可以進行設定,