MySQL DBA常用技能集錦

1、MySQL DBA常用技能集錦技術(shù)創(chuàng)新，變革未來(lái)1.1主從又不一致了老生常談的參數(shù)一致性相關(guān)innodb_flush_log_at_trx_commit=1sync_binlog=1復(fù)制安全relay_log_info_repository = TABLEmaster_info_repository = TABLE sync_relay_log = 1sync_master_info = 1影響復(fù)制安全的參數(shù)故事背景MySQL5.6.24主實(shí)例Binlog磁盤滿磁盤清理影響面主從實(shí)例不同步，數(shù)據(jù)不一致現(xiàn)象；重做所有從實(shí)例原因ERRORCouldnotopen./master-bin.00006

2、4forlogging(error 13).TurningloggingoffforthewholedurationOftheMySQL serverprocess.Toturnitonagain:fixthecause,shutdownthe MySQLserverandrestartit.影響復(fù)制安全的參數(shù)分析binlog_error_actionignore_errorabort_server驗(yàn)證：ABORT_SERVERErrormessage:Binaryloggingnotpossible.Eitherdiskisfullorfilesystemisreadonlywhilerot

3、atingthe binlog.Abortingtheserver影響復(fù)制安全的參數(shù)結(jié)論5.6.22版本增加了Abort_server選項(xiàng)5.6版本默認(rèn)值為IGNORE_ERROR5.7版本默認(rèn)值為ABORT_SERVER該參數(shù)支持動(dòng)態(tài)修改推薦生產(chǎn)環(huán)境配置ABORT_SERVER選項(xiàng)1.22億行單表的故事一張2億行單表的故事InnoDB:Operatingsystemerrornumber0.InnoDB:Check that your OS andfile systemsupport files ofthissize.故事背景CPU：64c Mem：256G Disk：SSD2億行70G單表

4、上做了alter table加字段操作，報(bào)錯(cuò)如下：mysqlaltertablefriendaddcolumntagvarchar(255)comment好友對(duì)應(yīng)的 tags;InnoDB:Error:Writetofile(merge)failedatoffset3039821824.InnoDB:1048576bytesshouldhavebeenwritten,only471040werewritten.影響面不要跟我談?dòng)绊懨媾菝娑紱隽耍∫粡?億行單表的故事分析問(wèn)題直接原因：/tmp滿了，重新配置了tmpdir2億3000W條記錄:mysqlaltertablefriendaddcolu

5、mntagvarchar(255) comment好友對(duì)應(yīng)的tags;QueryOK,0rowsaffected(1hour48min36.33sec)Records:0Duplicates:0Warnings:0一張2億行單表的故事問(wèn)題1：何時(shí)產(chǎn)生臨時(shí)表？altertabletablecopy-#sql-INPLACE-#sql-ib/tmpdir復(fù)雜排序Orderby-SQL_*.Groupby-SQL_*.參考文獻(xiàn)：/doc/refman/5.7/en/temporary-files.html一張2億行單表的故事問(wèn)題2:altertable添加列屬于哪種操作參考文獻(xiàn)：/doc/refma

6、n/5.6/en/innodb-create-index-overview.html一張2億行單表的故事問(wèn)題3:是否有其他的DDL方式 mysql：onlineddlpt-osc：perconaonlineschemachangegh-ost：GitHubsonlineschemamigrationforMySQL參考文獻(xiàn)：/doc/refman/5.6/en/innodb-online-ddl.html /doc/percona-toolkit/2.1/pt-online-schema-change.html /github/gh-ost一張2億行單表的故事總結(jié)1.tmpdir在生產(chǎn)上線時(shí)提

7、前規(guī)劃，建議單獨(dú)配置足夠容量 的目錄2.操作ddl前建議根據(jù)altertable的類型，對(duì)照官方文檔 確認(rèn)是inplace還是tablecopy一張2億行單表的故事總結(jié)3. DDL操作方式的原則 表數(shù)據(jù)量大小最直接影響選擇ddl方式的因素，建議超過(guò)100萬(wàn)行的表，通過(guò)posc或gh-ost操作是否可以接受延遲接受延遲的環(huán)境，表小于100萬(wàn)，用原生 不接受延遲的環(huán)境，使用posc或gh-ost1.3瘋狂的Slowlog瘋狂的SlowLog瘋狂的SlowLoggrow quickly瘋狂的SlowLog正確的姿勢(shì)應(yīng)該是這樣的2.1偶遇Metadatalock偶遇metadata lockMetad

8、ata lock wait 出現(xiàn)的場(chǎng)景有大事務(wù)正在使用表存在有未提交的事物存在有未提交的事務(wù),且是失敗操作解決方案找到未釋放metadatalock的session，并killKillddl語(yǔ)句，等待下一次運(yùn)維時(shí)間變更偶遇metadata lockMetadata lock wait 出現(xiàn)的場(chǎng)景1有大事務(wù)正在使用表偶遇metadata lockMetadata lock wait 出現(xiàn)的場(chǎng)景2存在有未提交的事務(wù)偶遇metadata lockMetadata lock wait 出現(xiàn)的場(chǎng)景3存在有未提交的事務(wù),且是失敗操作2.2復(fù)制延遲，再延遲復(fù)制延遲的幾種可能主從服務(wù)器的配置或者壓力不一，從實(shí)

9、例的IO能 力太弱主庫(kù)的TPS太高導(dǎo)致備庫(kù)延遲備份WaitingforglobalreadlockDDL導(dǎo)致備庫(kù)延遲（altertable,create index,optimizetable,repairtable）運(yùn)行大事務(wù)對(duì)無(wú)主鍵的表進(jìn)行刪除或者更新運(yùn)行大事務(wù)背景max_binlog_size=512M-rw-r-1mysqlmysql512MMay1110:17mysql-bin.000063-rw-r-1mysqlmysql512MMay1115:59mysql-bin.000064-rw-r-1mysqlmysql1220MMay1222:23mysql-bin.000065缺少主

10、鍵背景Binlog：Row模式，沒(méi)有主鍵，表數(shù)據(jù)量3000萬(wàn)左右主上執(zhí)行DELETEFROM*_snsdb.*_gamedata_service缺少主鍵mysqlbinlog -vv -base64-output=decode-rows mysqld-relay- bin.000065 -start-position=542989459|less缺少主鍵3.0觀測(cè)視角，火焰圖背景知識(shí)Different types of flame graphs:On-CPUOff-CPUHot/ColdDifferentialMemory背景知識(shí)Profiling tools:Linux:Solaris, i

11、llumos, FreeBSD: Mac OS X:Windows:perf, SystemTap, DTraceDTrace and Instruments Xperf.exe參考文獻(xiàn)：Mysqld DTrace Probe Reference /doc/refman/5.7/en/dba-dtrace-mysqld-ref.html用 Systemtap 探 索 MySQL /docs/archives/168場(chǎng)景1：基準(zhǔn)測(cè)試Mysql5.7測(cè)試： 總規(guī)模1億行 數(shù)據(jù)量大小15G 單表只讀 4Gbuffer其他默認(rèn)場(chǎng)景1：基準(zhǔn)測(cè)試原生mysql測(cè)試，總數(shù)據(jù)量1億， 數(shù)據(jù)量大小15G, 4G

12、buffer，其他默認(rèn)場(chǎng)景1：基準(zhǔn)測(cè)試原生mysql測(cè)試，總數(shù)據(jù)量1億， 數(shù)據(jù)量大小15G, 8Gbuffer場(chǎng)景1：基準(zhǔn)測(cè)試Mysql5.7測(cè)試： 總規(guī)模1億行 數(shù)據(jù)量大小15G 單表只讀 4G/8G buffer場(chǎng)景2：中間件性能損失測(cè)試單實(shí)例5.7VS中間件+單實(shí)例5.7場(chǎng)景2：中間件性能損失測(cè)試MySQLProxy場(chǎng)景2：中間件性能損失測(cè)試Mysql_no_sslProxy場(chǎng)景2：中間件性能損失測(cè)試單實(shí)例5.7VS中間件+單實(shí)例5.74.0為神馬參數(shù)也不一致MySQL配置參數(shù)和生效參數(shù)不一致背景客戶端報(bào)錯(cuò)：Cant open file: .testmytable.frm (errno:

13、 24) shell perror 24OS error code 24: Too many open filesmysql的配置文件是這么寫的 open_files_limit =4000max_connections = 500table_open_cache = 1000系統(tǒng) ulimit -n是這樣子設(shè)的 1500mysql動(dòng)態(tài)生效的值是這樣子的 open_files_limit = 1500max_connections = 500table_open_cache = 495MySQL配置參數(shù)和生效參數(shù)不一致MySQL調(diào)整參數(shù)的方式根據(jù)配置(三個(gè)參數(shù)的配置值或默認(rèn)值)計(jì)算 Reque

14、st_open_files(需要的文件描述符)獲取有效的系統(tǒng)的限制值 effective_open_files根據(jù)effective_open_files調(diào)整request_open_files根據(jù)調(diào)整后的request_open_files，計(jì)算實(shí)際生效的參數(shù)值(showvariables)MySQL配置參數(shù)和生效參數(shù)不一致MySQL調(diào)整參數(shù)的方式1.根據(jù)配置(三個(gè)參數(shù)的配置值或默認(rèn)值)計(jì)算 request_open_files(需要的文件描述符)request_open_files=maxlimit_1= max_connections + table_cache_size * 2 + 1

15、0; limit_2= max_connections * 5;limit_3= open_files_limit ? open_files_limit : 5000;/mysqlmax_connections = 500table_open_cache = 1000 open_files_limit=4000/ulimit -n 15002510250050005000！MySQL配置參數(shù)和生效參數(shù)不一致MySQL調(diào)整參數(shù)的方式2. 獲取有效的系統(tǒng)的限制值effective_open_fileseffective_open_files:/mysqlmax_connections = 500t

16、able_open_cache = 1000 open_files_limit=4000/ulimit -n 150015005000?NO1500MySQL配置參數(shù)和生效參數(shù)不一致MySQL調(diào)整參數(shù)的方式3.根據(jù)effective_open_files 調(diào)整request_open_files/mysqlmax_connections = 500table_open_cache = 1000 open_files_limit=4000/ulimit -n 150015005000requested_open_files=min(effective_open_files，request_ope

17、n_files)結(jié)論1:open_files_limit=1500MySQL配置參數(shù)和生效參數(shù)不一致MySQL調(diào)整參數(shù)的方式4. 根據(jù)調(diào)整后的request_open_files，計(jì)算實(shí)際生效的參數(shù)值(show variables 可查看參數(shù)值)max_connections/mysql max_connections = 500table_open_cache = 1000 open_files_limit=4000/ulimit -n 1500Min（limit = requested_open_files - 10 - TABLE_OPEN_CACHE_MIN * 2 ，max_connections）1500-10-800=690500table_cache_sizeMin（max(TABLE_OPEN_CACHE_MIN,(requested_open_files 10 - max_connections)/2)，table_cache_size400（1500-10-500)/2=495結(jié)論： max_connections =500 table_open_cache=4951000默認(rèn)值為400MySQL配置參數(shù)和生效參數(shù)不一致mysql的配置文件是這么寫的 open_f