1.主庫性能預警，CPU LOADING 過高a. 讀寫分離：把讀操作（查詢類，不是寫入/修改后即查詢數據的）放到DB2節點中進行； 2.日志文件、數據文件同放在一個磁盤中 /data ，沒有分離 。暫時不處理，以后項目可考慮分開。3.開啟了 doublewrite ，在SSD,XFS 磁盤存儲數據，可以關閉 mysql> show variables like '%doublewrite%';ERROR 2006 (HY000): MySQL server has gone awayNo connection. Trying to reconnect...Connection id: 39223899Current database: *** NONE ***+----------------------------------+----------------+| Variable_name | Value |+----------------------------------+----------------+| innodb_doublewrite | ON || innodb_parallel_doublewrite_path | xb_doublewrite |+----------------------------------+----------------+2 rows in set (0.01 sec)mysql> 4.參數 table_open_cache 調整為 4096#配置文件中已設置成 4096 ,但數據庫沒設置 。此設置可以動態設置，不用重啟MYSQL 實例。命令： set global table_open_cache=4096;[root@db-1 ~]# cat /etc/my.cnf|grep table_open_cachetable_open_cache = 4096mysql> show variables like '%table_open_cache%';+----------------------------+-------+| Variable_name | Value |+----------------------------+-------+| table_open_cache | 447 || table_open_cache_instances | 16 |+----------------------------+-------+2 rows in set (0.00 sec)mysql> show global status like 'open%tables%';+---------------+----------+| Variable_name | Value |+---------------+----------+| Open_tables | 432 || Opened_tables | 39578950 |+---------------+----------+Opened_tables數值非常大，說明cache太小，導致要頻繁地open table5.query_cache_size 查詢緩存參數5.1配置文件：query_cache_size = 05.2 MYSQL mysql> SHOW STATUS LIKE 'Qcache%'; ERROR 2006 (HY000): MySQL server has gone awayNo connection. Trying to reconnect...Connection id: 41556755Current database: *** NONE ***+-------------------------+-------+| Variable_name | Value |+-------------------------+-------+| Qcache_free_blocks | 0 || Qcache_free_memory | 0 || Qcache_hits | 0 || Qcache_inserts | 0 || Qcache_lowmem_prunes | 0 || Qcache_not_cached | 0 || Qcache_queries_in_cache | 0 || Qcache_total_blocks | 0 |+-------------------------+-------+8 rows in set (0.00 sec)mysql> mysql> SHOW VARIABLES LIKE 'have_query_cache';+------------------+-------+| Variable_name | Value |+------------------+-------+| have_query_cache | YES |+------------------+-------+1 row in set (0.00 sec)可配置為 query_cache_size = 128M6.隔離級別： 現在配置為 可重復讀transaction_isolation = REPEATABLE-READ修改語句： set transaction isolation level read committed; //設置讀提交級別 可動態修改。7.從節點延遲問題：現有參數：mysql> show variables like 'slave_parallel_workers';+------------------------+-------+| Variable_name | Value |+------------------------+-------+| slave_parallel_workers | 0 |+------------------------+-------+1 row in set (0.00 sec)mysql> show variables like 'slave_parallel_type';+---------------------+----------+| Variable_name | Value |+---------------------+----------+| slave_parallel_type | DATABASE |+---------------------+----------+1 row in set (0.01 sec)為了減少延遲現象slave-parallel-type=LOGICAL_CLOCK #基于組提交的并行復制方式 默認值為 DATABASE slave-parallel-workers=4 #并行數量8.兩個從節點可以把一個設置成延遲1天（或12小時）用于做數據備份使用。登陸到Slave數據庫服務器 ，設置延遲12小時(12*3600)mysql>stop slave; mysql> CHANGE MASTER TO MASTER_DELAY = 43200;mysql>start slave; mysql>show slave status\G查看SQL_Delay的值為600，表示設置成功。

總結

以上是生活随笔為你收集整理的一次MYSQL 服务器性能优化之旅的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。