Linux操作文件——MySQL優化（5.7.26）

---

一、記憶體優化

[root@mysql-1 ~]# echo 0 >/proc/sys/vm/swappiness     //臨時
[root@mysql-1 ~]# vim /etc/sysctl.conf     //永久
vm.swappiness=0
[root@mysql-1 ~]# sysctl -p

二、引數優化

引數	解釋	用法
Max_connections	Mysql的最大連線數，如果伺服器的併發請求量比較大，可以調高這個值	Max_connections=1024
back_log	mysql能暫存的連線數量，只有如果期望在一個短時間內有很多連線的時候需要增加它	back_log=1024
wait_timeout	指的是mysql在關閉一個非互動的連線之前所要等待的秒數	wait_timeout=60
interactive_timeout	指的是mysql在關閉一個互動的連線之前所需要等待的秒數	interactive_timeout=1200
key_buffer_size	指定索引緩衝區的大小，它決定索引處理的速度，尤其是索引讀的速度	key_buffer_size=64M
query_cache_size	查詢快取簡稱QC，使用查詢緩衝，mysql將查詢結果存放在緩衝區中，今後對於同樣的select語句（區分大小寫）,將直接從緩衝區中讀取結果。	query_cache_size=128M
query_cache_type	快取型別，決定快取什麼樣的查詢，設定為0，相當於禁用，設定為1，將會快取所有的結果，設定為2，則只快取在select語句中通過SQL_CACHE指定需要快取的查詢	query_cache_type=1
max_connect_errors	負責阻止過多嘗試失敗的客戶端以防止暴力破解密碼等情況，當超過指定次數，mysql伺服器將禁止host的連線請求，直到mysql伺服器重啟或通過flush hosts命令清空此host的相關資訊	max_connect_errors=2000
sort_buffer_size	每個需要進行排序的執行緒分配該大小的一個緩衝區	sort_buffer_size=1M
max_allowed_packet	mysql根據配置檔案會限制，server接受的資料包大小	max_allowed_packet=32M
join_buffer_size	用於表間關聯快取的大小，和sort_buffer_size一樣，該引數對應的分配記憶體也是每個連線獨享	join_buffer_size=2M
thread_cache_size	伺服器執行緒快取，這個值表示可以重新利用儲存在快取中執行緒的數量,當斷開連線時,那麼客戶端的執行緒將被放到快取中以響應下一個客戶而不是銷燬(前提是快取數未達上限)，如果執行緒重新被請求，那麼請求將從快取中讀取,如果快取中是空的或者是新的請求，那麼這個執行緒將被重新建立,如果有很多新的執行緒，增加這個值可以改善系統效能	thread_cache_size=32
innodb_buffer_pool_size	InnoDB使用該引數指定大小的記憶體來緩衝資料和索引。對於單獨的MySQL資料庫伺服器，最大可以把該值設定成實體記憶體的80%,一般我們建議不要超過實體記憶體的70%	innodb_buffer_pool_size=2048M
innodb_flush_log_at_trx_commit	控制了innodb將log buffer中的資料寫入日誌檔案並flush磁碟的時間點，取值分別為0、1、2。0，表示當事務提交時，不做日誌寫入操作，而是每秒鐘將log buffer中的資料寫入日誌檔案並flush磁碟一次；1，每次事務的提交都會引起redo日誌檔案寫入、flush磁碟的操作，確保了事務的ACID；2，每次事務提交引起寫入日誌檔案的動作,但每秒鐘完成一次flush磁碟操作。	innodb_flush_log_at_trx_commit=1
innodb_thread_concurrency	設定innodb執行緒的併發數量，預設值為0表示不限制	innodb_thread_concurrency=8
innodb_log_buffer_size	確定日誌檔案所用的記憶體大小，以M為單位。緩衝區更大能提高效能，對於較大的事務，可以增大快取大小。	innodb_log_buffer_size=128M
innodb_log_file_size	確定資料日誌檔案的大小，以M為單位，更大的設定可以提高效能	innodb_log_file_size = 100M
innodb_log_files_in_group	以迴圈方式將日誌檔案寫到多個檔案	nnodb_log_files_in_group = 3
read_buffer_size	MySql讀入緩衝區大小	read_buffer_size = 1M
read_rnd_buffer_size	MySql的隨機讀（查詢操作）緩衝區大小。當按任意順序讀取行時將分配一個隨機讀快取區	read_rnd_buffer_size = 1M
bulk_insert_buffer_size	批量插入資料快取大小，可以有效提高插入效率，預設為8M	bulk_insert_buffer_size = 8M
binary log	什麼時候重新整理binlog到磁碟，每次事務commit	sync_binlog=1

max_connections=1024
back_log=128
wait_timeout=60
interactive_timeout=7200
key_buffer_size=16M
query_cache_size=64M
query_cache_type=1
query_cache_limit=50M
max_connect_errors=20
sort_buffer_size=2M
max_allowed_packet=32M
join_buffer_size=2M
thread_cache_size=200
innodb_buffer_pool_size=1024M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=32M
innodb_log_file_size=128M
innodb_log_files_in_group=3
binlog_cache_size=2M
max_binlog_cache_size=8M
max_binlog_size=512M
expire_logs_days=7
read_buffer_size=2M
read_rnd_buffer_size=2M
bulk_insert_buffer_size=8M

三、鎖等待

1、檢視鎖等待

mysql01 [(none)]>SHOW  STATUS LIKE 'innodb_row_lock%';

2、檢視事務阻塞情況

mysql01 [(none)]>USE information_schema;
mysql01 [(none)]>SELECT * FROM information_schema.INNODB_TRX WHERE trx_state='LOCK WAIT';

引數	說明
trx_id	事務ID號
trx_state	當前事務的狀態
trx_mysql_thread_id	連線層的,連線執行緒ID(SHOW PROCESSLIST ===>Id或trx_id )
trx_query	當前被阻塞的操作

3、檢視鎖源

mysql01 [(none)]>SELECT * FROM sys.innodb_lock_waits;

引數	說明
locked_table	哪張表出現的等待
waiting_trx_id	等待的事務(與上個檢視trx_id 對應)
waiting_pid	等待的執行緒號(與上個檢視trx_mysql_thread_id)
blocking_trx_id	鎖源的事務ID
blocking_pid	鎖源的執行緒號

檢視鎖源的thread_id

mysql01 [(none)]>SELECT * FROM performance_schema.threads WHERE processlist_id=15;

檢視鎖源的SQL語句

mysql01 [(none)]>SELECT * FROM performance_schema.`events_statements_current` WHERE thread_id=41;      //當前在執行的語句
mysql01 [(none)]>SELECT * FROM performance_schema.`events_statements_history` WHERE thread_id=41;      //執行語句的歷史

四、死鎖監控

死鎖寫入錯誤日誌

[root@mysql-1 ~]# vim /etc/my.cnf
innodb_print_all_deadlocks = 1
mysql01 [(none)]>show engine innodb status\G
mysql01 [(none)]>show variables like '%deadlock%';

五、主從優化

gtid_mode=ON
enforce_gtid_consistency=ON
log_slave_updates=ON
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=16        //cpu核心數作為標準（50％）
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON