MySQL常規最佳化

標籤：監控   網卡   解決方案   流量   頻寬   

MySQL最佳化

                                                                                    --何旭東

650) this.width=650;" src="http://s5.51cto.com/wyfs02/M01/82/F8/wKioL1dnnZ2BRTIHAABANBXUs0E228.jpg" title="123.jpg" alt="wKioL1dnnZ2BRTIHAABANBXUs0E228.jpg" />

MySQL使用記憶體

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M02/82/F8/wKiom1dnnXvwWlwdAAC-RhqEAwc760.jpg" title="122.jpg" alt="wKiom1dnnXvwWlwdAAC-RhqEAwc760.jpg" />

引擎的特點

     Comment: Supports transactions, row-level locking, and foreign keys

 

Supports transactions：事務

 row-level locking：行鎖

foreign keys:外鍵。

 

Transactions：表示是否支援交易處理，YES表示支援，NO表示不支援。

XA：表示是否分布式交易處理XA規範，YES表示支援，NO表示不支援。

Savepoints：表示是否支援儲存點，以便交易回復到儲存點，YES表示支援，NO表示不支援。

 

目前InnoDB採用雙軌授權，一個是GPL授權，另一個則是專有軟體授權。

InnoDB儲存引擎給MySQL資料庫提供了具有事務，復原和崩潰修複能力，多版本並發控制的事務安全性表。InnoDB儲存引擎也提供了行級鎖。

優勢：良好的交易管理，崩潰修複能力和並發控制。

缺點：讀寫效率稍差，佔用資料空間相對較大。

 

3個方面介紹InnoDB的特點：

1 InnoDB儲存引擎中儲存表和索引有兩種方式：共用資料表空間儲存和多資料表空間儲存。

共用資料表空間儲存。建立的表結構儲存在尾碼是.frm的檔案中，資料和索引存在innodb_data_home_dir和innodb_data_file_path定義的資料表空間中。

多資料表空間儲存。建立的表結構儲存在尾碼名是.frm的檔案中，但是每個表的資料和索引單獨儲存在.ibd檔案中。如果為分區表，則每個分區表對應單獨的.ibd檔案，檔案名稱是表明+分區名。如果為分區表，則每個分區表對應單獨的.ibd檔案，檔案名稱是表名+分區名。使用多資料表空間儲存需要設定參數innodb_file_per_table，並且重啟服務才會生效，只對建立表有效。

2 InnoDB儲存引擎支援外鍵，外鍵所在的表為子表，外鍵所依賴的表為夫表。夫表被子表外部索引鍵關聯的欄位必須為主鍵。如果刪除，修改夫表中的某條資訊時，子表也必須有相應改變。

3 InnoDB儲存引擎支援自動成長列AUTO_INCREMENT,自動成長列的值不可為空，而且值必須唯一。另外在MySQL資料庫中規定自增列必須為主鍵，再插入值時，自動成長分為3種情況。

一、如果自動成長列不輸入值，則插入的列自動成長後的值。

二、如果輸入的值為0或空（NULL），則插入的值也為自動成長後的值。

三、如果插入某個確定的值，且該值在前面的資料沒有出現過，則可以直接插入。

線程特點

#在5.1的4個FILE_iO 線程包括: 
insert buffer thread
log thread 
read thread
write thread

#而在5.5, 總共有10個FILE_IO線程
(insert buffer thread) * 1
(log thread) * 1 
(read thread) * 4 
(write thread) * 4

常規最佳化的十個參數

(1)、max_connections： 
(2)、record_buffer： 
(3)、key_buffer_size： 
4)、back_log： 
(5)、interactive_timeout： 
(6)、sort_buffer： 
(7)、table_cache： 
(8)、thread_cache_size： 
(9)、mysql的搜尋功能 
(10)、wait_timeout： 

系統層IO調度演算法

noop anticipatory deadline [cfq] 
deadline :    deadline 演算法保證對既定的IO請求以最小的延遲時間。
anticipatory：    有個IO發生後，如果又有進程請求IO，則產生一個預設6ms猜測時間，猜測下一個進程請求IO是幹什麼。這對於隨機讀取會造成較大的延時。
        對資料庫應用很糟糕，而對於Web Server等則會表現不錯。
cfq:        對每個進程維護一個IO隊列，各個進程發來的IO請求會被cfq以輪循方式處理，對每一個IO請求都是公平。適合離散讀的應用。
noop:        對所有IO請求都用FIFO隊列形式處理。預設IO不會存在效能問題。

最佳化swap分區

預設60，當swap空間使用達到60%的時候，開始釋放實體記憶體中的cache/buffers。

刷磁碟機制

mysql日誌操作步驟 ：log_buffer ---mysql寫 (write)---> log_file ---OS重新整理(flush)---> disk

0：log buffer中的資料將以每秒一次的頻率寫入到log file中，且同時會進行檔案系統到磁碟的同步操作，但是每個事務的commit並不會觸發任何log buffer 到log file的重新整理或者檔案系統到磁碟的重新整理操作；

1：在每次事務提交的時候將log buffer 中的資料都會寫入到log file，同時也會觸發檔案系統到磁碟的同步；

2：事務提交會觸發log buffer 到log file的重新整理，但並不會觸發磁碟檔案系統到磁碟的同步。此外，每秒會有一次檔案系統到磁碟同步操作。

具體的展示

0（延遲寫）： log_buffer  --每隔1秒--> log_file  —即時—> disk

1（即時寫，即時刷）： log_buffer  —即時—>  log_file  —即時—> disk

2（即時寫，延遲刷）： log_buffer  —即時—> log_file --每隔1秒-->disk

進程管理思想

殭屍進程：一個子進程在其父進程沒有調用wait()或waitpid()的情況下退出。這個子進程就是殭屍進程。如果其父進程還存在而一直不調用wait，則該殭屍進程將無法回收，等到其父進程退出後該進程將被init回收。
孤兒進程：一個父進程退出，而它的一個或多個子進程還在運行，那麼那些子進程將成為孤兒進程。孤兒進程將被init進程(進程號為1)所收養，並由init進程對它們完成狀態收集工作。
殭屍進程將會導致資源浪費，而孤兒則不會。

 

最多可以多少個進程同時存在

[email protected] ~]$ cat/proc/sys/kernel/pid_max
32768

linux負載，是核心數的3倍

Linux允許的最大進程數可以通過 ulimit -u來查看

 

本文出自 “晴空” 部落格，謝絕轉載！

MySQL常規最佳化