標籤:

高性能mysql優化一之配置篇

一、基本配置

公共參數默認值:

max_connections = 151#同時處理最大連接數,推薦設置最大連接數是上限連接數的80%左右 sort_buffer_size = 2M#查詢排序時緩衝區大小,只對order by和group by起作用,可增大此值為16Mquery_cache_limit = 1M #查詢緩存限制,只有1M以下查詢結果才會被緩存,以免結果數據較大把緩存池覆蓋query_cache_size = 16M #查看緩衝區大小,用於緩存SELECT查詢結果,下一次有同樣SELECT查詢將直接從緩存池返回結果,可適當成倍增加此值open_files_limit = 1024 #打開文件數限制,如果show global status like open_files查看的值等於或者大於open_files_limit值時,程序會無法連接資料庫或卡死

MyISAM參數默認值:

key_buffer_size = 16M#索引緩存區大小,一般設置物理內存的30-40%read_buffer_size = 128K #讀操作緩衝區大小,推薦設置16M或32M

InnoDB參數默認值:

innodb_buffer_pool_size = 128M#索引和數據緩衝區大小,一般設置物理內存的60%-70%innodb_buffer_pool_instances = 1 #緩衝池實例個數,推薦設置4個或8個innodb_flush_log_at_trx_commit = 1 #關鍵參數,0代表大約每秒寫入到日誌並同步到磁碟,資料庫故障會丟失1秒左右事務數據。1為每執行一條SQL後寫入到日誌並同步到磁碟,I/O開銷大,執行完SQL要等待日誌讀寫,效率低。2代表只把日誌寫入到系統緩存區,再每秒同步到磁碟,效率很高,如果伺服器故障,才會丟失事務數據。對數據安全性要求不是很高的推薦設置2,性能高,修改後效果明顯。innodb_file_per_table = OFF #默認是共享表空間,共享表空間idbdata文件不斷增大,影響一定的I/O性能。推薦開啟獨立表空間模式,每個表的索引和數據都存在自己獨立的表空間中,可以實現單表在不同資料庫中移動。innodb_log_buffer_size = 8M #日誌緩衝區大小,由於日誌最長每秒鐘刷新一次,所以一般不用超過16M

更多詳細參數配置以及內存優化可以觀看:MySQL 大內存優化配置文件優化詳解

二、重要參數詳解

你需要經常察看以下3個配置項。不然,可能很快就會出問題。

innodb_buffer_pool_size:這是你安裝完InnoDB後第一個應該設置的選項。緩衝池是數據和索引緩存的地方:這個值越大越好,這能保證你在大多數的讀取操作時使用的是內存而不是硬碟。典型的值是5-6GB(8GB內存),20-25GB(32GB內存),100-120GB(128GB內存)。

innodb_log_file_size:這是redo日誌的大小。redo日誌被用於確保寫操作快速而可靠並且在崩潰時恢復。一直到MySQL 5.1,它都難於調整,因為一方面你想讓它更大來提高性能,另一方面你想讓它更小來使得崩潰後更快恢復。幸運的是從MySQL 5.5之後,崩潰恢復的性能的到了很大提升,這樣你就可以同時擁有較高的寫入性能和崩潰恢復性能了。一直到MySQL 5.5,redo日誌的總尺寸被限定在4GB(默認可以有2個log文件)。這在MySQL 5.6里被提高。

一開始就把innodb_log_file_size設置成512M(這樣有1GB的redo日誌)會使你有充裕的寫操作空間。如果你知道你的應用程序需要頻繁的寫入數據並且你使用的時MySQL 5.6,你可以一開始就把它這是成4G。

max_connections:如果你經常看到『Too many connections錯誤,是因為max_connections的值太低了。這非常常見因為應用程序沒有正確的關閉資料庫連接,你需要比默認的151連接數更大的值。max_connection值被設高了(例如1000或更高)之後一個主要缺陷是當伺服器運行1000個或更高的活動事務時會變的沒有響應。在應用程序里使用連接池或者在MySQL里使用進程池有助於解決這一問題。

InnoDB配置

從MySQL 5.5版本開始,InnoDB就是默認的存儲引擎並且它比任何其他存儲引擎的使用都要多得多。那也是為什麼它需要小心配置的原因。

innodb_file_per_table:這項設置告知InnoDB是否需要將所有表的數據和索引存放在共享表空間里(innodb_file_per_table = OFF) 或者為每張表的數據單獨放在一個.ibd文件(innodb_file_per_table = ON)。每張表一個文件允許你在drop、truncate或者rebuild表時回收磁碟空間。這對於一些高級特性也是有必要的,比如數據壓縮。但是它不會帶來任何性能收益。你不想讓每張表一個文件的主要場景是:有非常多的表(比如10k+)。

MySQL 5.6中,這個屬性默認值是ON,因此大部分情況下你什麼都不需要做。對於之前的版本你必需在載入數據之前將這個屬性設置為ON,因為它只對新創建的表有影響。

innodb_flush_log_at_trx_commit:默認值為1,表示InnoDB完全支持ACID特性。當你的主要關注點是數據安全的時候這個值是最合適的,比如在一個主節點上。但是對於磁碟(讀寫)速度較慢的系統,它會帶來很巨大的開銷,因為每次將改變flush到redo日誌都需要額外的fsyncs。將它的值設置為2會導致不太可靠(reliable)因為提交的事務僅僅每秒才flush一次到redo日誌,但對於一些場景是可以接受的,比如對於主節點的備份節點這個值是可以接受的。如果值為0速度就更快了,但在系統崩潰時可能丟失一些數據:只適用於備份節點。

innodb_flush_method: 這項配置決定了數據和日誌寫入硬碟的方式。一般來說,如果你有硬體RAID控制器,並且其獨立緩存採用write-back機制,並有著電池斷電保護,那麼應該設置配置為O_DIRECT;否則,大多數情況下應將其設為fdatasync(默認值)。sysbench是一個可以幫助你決定這個選項的好工具。

innodb_log_buffer_size: 這項配置決定了為尚未執行的事務分配的緩存。其默認值(1MB)一般來說已經夠用了,但是如果你的事務中包含有二進位大對象或者大文本欄位的話,這點緩存很快就會被填滿並觸發額外的I/O操作。看看Innodb_log_waits狀態變數,如果它不是0,增加innodb_log_buffer_size。

其他設置

query_cache_size: query cache(查詢緩存)是一個眾所周知的瓶頸,甚至在並發並不多的時候也是如此。 最佳選項是將其從一開始就停用,設置query_cache_size = 0(現在MySQL 5.6的默認值)並利用其他方法加速查詢:優化索引、增加拷貝分散負載或者啟用額外的緩存(比如memcache或redis)。如果你已經為你的應用啟用了query cache並且還沒有發現任何問題,query cache可能對你有用。這是如果你想停用它,那就得小心了。

log_bin:如果你想讓資料庫伺服器充當主節點的備份節點,那麼開啟二進位日誌是必須的。如果這麼做了之後,還別忘了設置server_id為一個唯一的值。就算只有一個伺服器,如果你想做基於時間點的數據恢復,這(開啟二進位日誌)也是很有用的:從你最近的備份中恢復(全量備份),並應用二進位日誌中的修改(增量備份)。二進位日誌一旦創建就將永久保存。所以如果你不想讓磁碟空間耗盡,你可以用 PURGE BINARY LOGS 來清除舊文件,或者設置 expire_logs_days 來指定過多少天日誌將被自動清除。

記錄二進位日誌不是沒有開銷的,所以如果你在一個非主節點的複製節點上不需要它的話,那麼建議關閉這個選項。

skip_name_resolve:當客戶端連接資料庫伺服器時,伺服器會進行主機名解析,並且當DNS很慢時,建立連接也會很慢。因此建議在啟動伺服器時關閉skip_name_resolve選項而不進行DNS查找。唯一的局限是之後GRANT語句中只能使用IP地址了,因此在添加這項設置到一個已有系統中必須格外小心。


推薦閱讀:

mongodb寫入數據要注意的一些細節
MySQL 和 PostgreSQL 相比,對 JSON 的支持如何?
mysql如何解決評論遞歸查詢?
InnoDB存儲引擎MVCC實現原理

TAG:MySQL |