運維角度淺談MySQL數(shù)據(jù)庫優(yōu)化
一個成熟的數(shù)據(jù)庫架構(gòu)并不是一開始設(shè)計就具備高可用、高伸縮等特性的,它是隨著用戶量的增加,基礎(chǔ)架構(gòu)才逐漸完善。這篇博文主要談MySQL數(shù)據(jù)庫發(fā)展周期中所面臨的問題及優(yōu)化方案
一個成熟的數(shù)據(jù)庫架構(gòu)并不是一開始設(shè)計就具備高可用、高伸縮等特性的,它是隨著用戶量的增加,基礎(chǔ)架構(gòu)才逐漸完善。這篇博文主要談MySQL數(shù)據(jù)庫發(fā)展周期中所面臨的問題及優(yōu)化方案,暫且拋開前端應(yīng)用不說,大致分為以下五個階段:
1
2
3
4
5
6
|
max_connections = 151 #同時處理最大連接數(shù),推薦設(shè)置最大連接數(shù)是上限連接數(shù)的80%左右 sort_buffer_size = 2M #查詢排序時緩沖區(qū)大小,只對order by和group by起作用,可增大此值為16M open_files_limit = 1024 #打開文件數(shù)限制,如果show global status like 'open_files'查看的值等于或者大于open_files_limit值時,程序會無法連接數(shù)據(jù)庫或卡死 |
1
2
3
4
5
6
7
8
9
10
|
key_buffer_size = 16M #索引緩存區(qū)大小,一般設(shè)置物理內(nèi)存的30-40% read_buffer_size = 128K #讀操作緩沖區(qū)大小,推薦設(shè)置16M或32M query_cache_type = ON #打開查詢緩存功能 query_cache_limit = 1M #查詢緩存限制,只有1M以下查詢結(jié)果才會被緩存,以免結(jié)果數(shù)據(jù)較大把緩存池覆蓋 query_cache_size = 16M #查看緩沖區(qū)大小,用于緩存SELECT查詢結(jié)果,下一次有同樣SELECT查詢將直接從緩存池返回結(jié)果,可適當(dāng)成倍增加此值 |
1
2
3
4
5
6
7
8
9
10
|
innodb_buffer_pool_size = 128M #索引和數(shù)據(jù)緩沖區(qū)大小,一般設(shè)置物理內(nèi)存的60%-70% innodb_buffer_pool_instances = 1 #緩沖池實例個數(shù),推薦設(shè)置4個或8個 innodb_flush_log_at_trx_commit = 1 #關(guān)鍵參數(shù),0代表大約每秒寫入到日志并同步到磁盤,數(shù)據(jù)庫故障會丟失1秒左右事務(wù)數(shù)據(jù)。1為每執(zhí)行一條SQL后寫入到日志并同步到磁盤,I/O開銷大,執(zhí)行完SQL要等待日志讀寫,效率低。2代表只把日志寫入到系統(tǒng)緩存區(qū),再每秒同步到磁盤,效率很高,如果服務(wù)器故障,才會丟失事務(wù)數(shù)據(jù)。對數(shù)據(jù)安全性要求不是很高的推薦設(shè)置2,性能高,修改后效果明顯。 innodb_file_per_table = OFF #默認是共享表空間,共享表空間idbdata文件不斷增大,影響一定的I/O性能。推薦開啟獨立表空間模式,每個表的索引和數(shù)據(jù)都存在自己獨立的表空間中,可以實現(xiàn)單表在不同數(shù)據(jù)庫中移動。 innodb_log_buffer_size = 8M #日志緩沖區(qū)大小,由于日志最長每秒鐘刷新一次,所以一般不用超過16M |
1
2
3
4
5
6
7
8
9
10
|
net.ipv4.tcp_fin_timeout = 30 #TIME_WAIT超時時間,默認是60s net.ipv4.tcp_tw_reuse = 1 #1表示開啟復(fù)用,允許TIME_WAIT socket重新用于新的TCP連接,0表示關(guān)閉 net.ipv4.tcp_tw_recycle = 1 #1表示開啟TIME_WAIT socket快速回收,0表示關(guān)閉 net.ipv4.tcp_max_tw_buckets = 4096 #系統(tǒng)保持TIME_WAIT socket最大數(shù)量,如果超出這個數(shù),系統(tǒng)將隨機清除一些TIME_WAIT并打印警告信息 net.ipv4.tcp_max_syn_backlog = 4096 #進入SYN隊列最大長度,加大隊列長度可容納更多的等待連接 |
1
2
3
4
|
# vi /etc/security/limits.conf #加入以下配置,*代表所有用戶,也可以指定用戶,重啟系統(tǒng)生效 * soft nofile 65535 * hard nofile 65535 # ulimit -SHn 65535 #立刻生效 |
1
2
|
mysql> show global status like 'Questions' ; mysql> show global status like 'Uptime' ; |
1
2
3
|
mysql> show global status like 'Com_commit' ; mysql> show global status like 'Com_rollback' ; mysql> show global status like 'Uptime' ; |
1
|
mysql> show global status where Variable_name in ( 'com_select' , 'com_insert' , 'com_delete' , 'com_update' ); |
1
|
mysql> show global status where Variable_name in ( 'com_insert' , 'com_delete' , 'com_update' ); |
1
2
3
4
|
mysql> set global slow-query-log=on #開啟慢查詢功能 mysql> set global slow_query_log_file= '/var/log/mysql/mysql-slow.log' ; #指定慢查詢?nèi)罩疚募恢?/code>
|
責(zé)任編輯:售電衡衡
免責(zé)聲明:本文僅代表作者個人觀點,與本站無關(guān)。其原創(chuàng)性以及文中陳述文字和內(nèi)容未經(jīng)本站證實,對本文以及其中全部或者部分內(nèi)容、文字的真實性、完整性、及時性本站不作任何保證或承諾,請讀者僅作參考,并請自行核實相關(guān)內(nèi)容。
我要收藏
個贊
-
碳中和戰(zhàn)略|趙英民副部長致辭全文
2020-10-19碳中和,碳排放,趙英民 -
兩部門:推廣不停電作業(yè)技術(shù) 減少停電時間和停電次數(shù)
2020-09-28獲得電力,供電可靠性,供電企業(yè) -
國家發(fā)改委、國家能源局:推廣不停電作業(yè)技術(shù) 減少停電時間和停電次數(shù)
2020-09-28獲得電力,供電可靠性,供電企業(yè)
-
碳中和戰(zhàn)略|趙英民副部長致辭全文
2020-10-19碳中和,碳排放,趙英民 -
深度報告 | 基于分類監(jiān)管與當(dāng)量協(xié)同的碳市場框架設(shè)計方案
2020-07-21碳市場,碳排放,碳交易 -
碳市場讓重慶能源轉(zhuǎn)型與經(jīng)濟發(fā)展并進
2020-07-21碳市場,碳排放,重慶
-
兩部門:推廣不停電作業(yè)技術(shù) 減少停電時間和停電次數(shù)
2020-09-28獲得電力,供電可靠性,供電企業(yè) -
國家發(fā)改委、國家能源局:推廣不停電作業(yè)技術(shù) 減少停電時間和停電次數(shù)
2020-09-28獲得電力,供電可靠性,供電企業(yè) -
2020年二季度福建省統(tǒng)調(diào)燃煤電廠節(jié)能減排信息披露
2020-07-21火電環(huán)保,燃煤電廠,超低排放
-
四川“專線供電”身陷違法困境
2019-12-16專線供電 -
我國能源替代規(guī)范法律問題研究(上)
2019-10-31能源替代規(guī)范法律 -
區(qū)域鏈結(jié)構(gòu)對于數(shù)據(jù)中心有什么影響?這個影響是好是壞呢!