導語 騰訊機器學習平台太極後端資料庫是自己運維的Mysql，曆史原因沒有用公司CDB、TDSQL等，之後還是要進行資料庫遷移把db維護交給專業的人去運維，這塊太極平台沒有專門的dba運維出現了不少問題，如Mysql主節點硬碟故障，備機切主導緻系統中斷半小時；後端接口調用不合理導緻循環調用資料庫緻使資料庫cpu持續維持在高位以及前端接口資料傳回緩慢等問題。這塊Mysql優化就需要開發自己去多了解Mysql系統架構、性能調優相關問題，監控Mysql 機器運作狀态，本文就簡單介紹下Mysql系統分析思路和采用的工具。
1
概述
資料庫是個比較大的話題，有各種各樣資料庫常見的關系型資料庫如Mysql 、oracle、非關系型資料庫，還有圖資料庫等。資料庫性能會跟許多部分有關聯，從硬體底層儲存設備、作業系統、資料庫配置參數、資料庫架構、資料庫表結構、應用層面的連接配接池設定、以及SQL索引等。
資料庫架構
對Mysql資料庫進行分析，首先需要了解MySql的系統架構，如下圖所示：
從這個架構圖，來看Mysql系統架構分為應用層、MySql服務層、存儲引擎層。
- 應用層，應用層是MySQL體系架構的最上層，它和其他client-server架構一樣，主要包含：連接配接處理、使用者鑒權、安全管理
- MySQL服務層：該層是Mysql Server的核心層，提供了Mysql Server 資料庫所有邏輯功能
-
存儲引擎層
存儲引擎是MySQL中具體與檔案打交道的子系統，也是MySQL最有特色的地方。MySQL差別于其他資料庫的最重要特點是其插件式的表存儲引擎。他根據MySQL AB公司提供的檔案通路層抽象接口來定制一種檔案通路的機制（該機制叫存儲引擎）。
實體檔案包括：redolog、undolog、binlog、errorlog、querylog、slowlog、data、index等
SQL運作過程
知道資料庫架構後，在性能分析時候需要知道這些子產品的功能及運作邏輯，明白一個具體的sql所需要經曆的過程：一個sql首先經過Connection Pool到達系統後，需要先進入Sql interface子產品判斷這個語句，是什麼類型。然後通過Parser 子產品進行文法與語義檢查，并生成相應的執行計劃；接着到Optimizer子產品進行優化，判斷走什麼索引，執行順序等，然後就到Cache中找資料，如果Caches中找不到資料的話，就得通過檔案系統到磁盤中進行尋找。
2
性能分析基本監控名額
了解了mysql系統架構和mysql執行過程還不夠，在進行性能分析時，需要找出mysql的問題所得先了解一些基礎知識和相應的監控工具。
首先需要了解的兩個Schema 分别是information_schema和performance_schema，information_schema，它們儲存了資料庫中的所有表、列、索引、權限、配置參數、狀态參數 等資訊。像我們常執行的show processlist;就來自于這個 schema 中的 processlist 表。performance_schema提供了資料庫運作時的資源消耗情況，它以較低的代價收集資訊， 可以提供不少性能資料。
還有在分析mysql是需要知道的兩個指令:show global variables ;和show global status ;前一個用來檢視配置的參數值，後一個用來查詢狀态值。不過這些指令隻是簡單的羅列資訊，并沒有統計分析，接下來我們介紹兩個個比較好的監控工具。
3
全局分析:mysqlreport
show status 輸出的報告是用來計算性能瓶頸的參考資料，但是資料隻是簡單的羅列，不好一下子看出性能問題，而mysqlreport 不像show status簡單的羅列資料，而是對這些參考資料加以融合計算，整理成一個個優化參考點，然後就可以根據這個優化參考點的值以及該點的衡量标準，進行對應的調整。
linux 環境下mysqlreport安裝
- 步驟一：yum -y install perl-DBD-MySQL 依賴包
- 步驟二：yum -y install perl-DBI #依賴包
-
步驟三 ：yum -y install mysqlreport
在linux系統上經過這三步就安裝好了這個工具。接下來就可以對資料庫運作狀況進行分析了。
mysqlreport使用
使用比較簡單，直接執行：mysqlreport --user tesla --password xxx@2015 --host 127.0.0.1 --no-mycnf --flush-status --outfile ./result.txt 就可以把資料庫整體情況儲存到目前目錄中。
具體指令參數檢視 mysqlreport —help
mysqlreport結果分析：
- 資料庫操作報表和查詢排序報表
這個表反映資料庫使用情況，608每秒操作量有點大，slow 這個參數挺重要，隻是因為這裡設定的慢查詢10s太長了，正常情況下盡量設定在1s左右，這塊需要對db 進行配置，把慢查詢統計設定的短些。
DMS部分告訴我們這個資料庫中各種 SQL 所占的比例，這個例子中，SELECT多，要做 SQL 優化的話，肯定優先考慮SELECT語句，才會起到立竿見影的效果。
-
select and sort 查詢和排序報表
這塊的報表資料具有極大的參考性，一下就能看出問題的所在，這裡的Scan（代表全表掃描）每秒48次執行全表掃描，實在是太多了，需要對語句進行修改，也是我們後面優化的重點内容。
-
InnoDB 緩存池報表
InnoDB 緩存池報表，Innodb Buffer Pool size 定義了Innodb 存儲引擎的表資料和索引資料的最大記憶體緩存大小。這部分對MySQL來說很重要，這裡使用已經達到100% 這種情況下就必須要增加Innodb緩存池了。這裡的Read hit達到 92.57%，這個值越大越好，盡量達到100% 這裡的值與Innodb buffer太小有關。
-
連接配接報表
從這裡可以看出資料連接配接還完全夠用。
-
表鎖報表
Waited表示有多少次查詢需要等待表鎖定；Immediate表示有多少次查詢可以立即獲得表鎖定，同時後面還有一個比例
對資料庫來說『等待』幾乎可以肯定是一件很不好的事情，是以 Waited 的值應該要越小越好。最具有代表性的是第三個字段 (Waited 占所有 table lock 的百分比)這裡是0.00%，非常好，沒有發送過表鎖。
- 臨時表報表
執行explain 在sql分析時出現Using temporary的狀态，這意味着查詢過程中需要建立臨時表來存儲中間資料，我們需要通過合理的索引來避免它。另一方面，當臨時表在所難免時，也要盡量減少臨時表本身的開銷，MySQL可以将臨時表建立在磁盤（Disk table）、記憶體（Table）以及臨時檔案（File）中，顯然，在磁盤上建立臨時表的開銷最大，是以我們希望MySQL盡量不要在磁盤上建立臨時表，上面分析結果來看從臨時表建立在磁盤(Disk table)和臨時檔案(File) 上的 量級來說，還是有點偏大了，是以，可以增大tmp_table_size。
其它全局資訊可以查下資料
4
全局分析結果
通過mysqlreport這個工具反應的結果，有以下問題需要去解決下：
- 總體資料庫操作達到600多每秒,對于内網系統使用者不太多，操作有點太頻繁，看下能夠減少不必要的資料庫操作。
- 慢查詢未開啟，而且設定的時間太長長達10s，通常一個語句大于100ms 可任務需要進行優化，這裡需要設定較短分析下慢查詢
- 全表掃描48.5/s 這塊要分析下具體的sql寫法
- Innodb 緩存占用使用100% ，而且設定大小太小，需要增加緩存大小。
pt-query-digest 工具
作為分析mysql工具的首選，因為它可以從logs、processlist、和tcpdump 來分析MySQL的狀況，logs包括slow log、general log、binlog。也可以把分析結果輸出到檔案中，或則把檔案寫到表中。分析過程是先對查詢語句的條件進行參數化，然後對參數化以後的查詢進行分組統計，統計出各查詢的執行時間、次數、占比等，可以借助分析結果找出問題進行優化。
安裝方法
下載下傳 ：https://www.percona.com/downloads/percona-toolkit/LATEST/
安裝：centos依賴包 yum -y install perl-TermReadKey perl-Time-HiRes perl-IO-Socket-SSL.noarch
pt-query-digest --help
pt-query-digest分析 slow /bin log 時産生的報告邏輯非常清晰，并且資料也比較完整。執 行指令後就會生成一個報告,因為線網沒開啟slow log日志，這裡我們分析下線網bin log日志
使用方法
對binlog日志進行轉換：mysqlbinlog --no-defaults -vv --base64-output=DECODE-ROWS mysql-bin.000818 > mysql-bin.000818.txt
pt-query-digest --type=binlog mysql-bin.000818.txt > 818.report.log
篩選出全表掃描語句
設定資料庫設定開啟 log_queries_not_using_indexes=on；就會輸出全表掃描語句到慢查詢日志當中。值得注意的是，執行時間超過long_query_time的SQL語句也将記錄到slow log中，無論該SQL語句是否使用索引。
profiling的操作步驟：檢視詳細執行計劃
- 步驟一 ：set profiling=1; //這一步是為了打開profiling功能
- 步驟二 ：執行語句 //執行你從慢日志中看到的語句
- 步驟三 ：show profiles; //這一步是為了查找步驟二中執行的語句的ID
- 步驟四 ：show profile all for query id; //這一步是為了顯示出profiling的結果
修改表結構增加索引：索引名一般是表名加字段名
show index from project_permissions;
ALTER table project_permissions ADD INDEX idex_project (project_id);
ALTER table tableName ADD INDEX indexName(columnName)
create index 索引名 on 表名（字段名1，字段名2）
分析：執行頻率非常高的語句以及全表掃描
-
1）explain SELECT project_id, modified_time, name, permissions, isGroup FROM project_permissions WHERE project_id=2076;
根據執行計劃和查詢條件分析，需要對project_id 建立索引，建立索引後需要注意where條件中值的類型，這裡需要把project_id 改成字元串，mysql隐式的将數值類型轉換成了字元串類型
- 2）explain SELECT id, model_name, model_type, job_id, properties, gmt_create, owner, last_execution_model, gmt_modified, published, status, module_id from mlstudio_model where job_id=13788; 資料庫表記錄9000條，沒有增加索引，可以适當對job_id增加索引，也因為資料較小優先級比較低 ALTER table mlstudio_model ADD INDEX index_model(job_id) 有2倍性能能提升
-
3）explain SELECT id, name, user_id, property, gmt_create, gmt_modified, appstatus, execution_info FROM mlstudio_deployed_notebooks WHERE appstatus in (10,140,20,120) ORDER BY gmt_modified desc;
分析及方案：資料庫表記錄200多條，沒有增加索引，會全表掃描，優先級不太高，隻不過property字段和execution_info資訊資料比較大，建議如果property字段沒有用到 查詢語句就不指定property
-
4）explain select id, algorithm_id, version, create_time, modify_time, module_id, shared, type, source_algorithm_version_id from ti_user_algorithm_version where module_id = 813;
解決方式：資料表記錄目前較少 資料庫字段比較短
ALTER table ti_user_algorithm_version ADD INDEX index_algorithm(module_id)
-
5）explain select id, gmt_create, gmt_modified, name, type, description, checked, permission, user_id, nick_name, config_file_name, config_file_res, module_res, module_dependencies, job_type, user_coded, has_model, icon, module_jars from mlstudio_modules where module_res=0 and type>0 and type <1001 and job_type=2;
資料記錄不多，字段值相對都比較短，查詢出來占據空間相對較小 625條影響較小
- 6）explain SELECT id, name, type, gmt_create, owner, gmt_modified, published, status, module_id, properties from mlstudio_dataset where module_id = 229;
資料記錄不多，字段值相對都比較短，查詢出來占據空間相對較小 55條影 響較小，對module_id加索引處理，查詢很少可以不用處理
-
7）explain select algorithm_id from ti_user_algorithm_favorite where user_id = ‘jianfehuang’ and algorithm_id = 101;
create index algorithm on ti_user_algorithm_favorite (user_id,algorithm_id);
解決方案 ：建立聯合索引，索引後速度有一定提升，隻會查出一行記錄對緩存占用小。目前資料庫記錄196條
- 8）explain select cid, cname, cdesc, cicon, clevel, cparent, cvisible, group_concat(mid order by mname), sum(mpermission) as public_num from (select mmc.id as cid, mmc.name as cname,mmc.desc as cdesc,mmc.icon as cicon,mmc.level as clevel, mmc.parent_id as cparent,mmc.visible as cvisible,mmc.order_num as corder,mm.id as mid, mm.name as mname, mm.permission as mpermission from mlstudio_module_category mmc left join mlstudio_modules mm on mmc.id = mm.type) as t group by cid, cname, cdesc, cicon, clevel, cparent, cvisible order by corder;
-
9）select queuequota0_.id as id1_1_, queuequota0_.cpu as cpu2_1_, queuequota0_.gmt_create as gmt_crea3_1_, queuequota0_.gpu_map as gpu_map4_1_, queuequota0_.jizhi_business_flag as jizhi_bu5_1_, queuequota0_.memory as memory6_1_, queuequota0_.name as name7_1_, queuequota0_.gmt_modified as gmt_modi8_1_, queuequota0_.uuid as uuid9_1_ from queue_quota queuequota0_ where queuequota0_.name=‘g_teg_teslaml_appgroup04’;
分析全表掃描：目前資料表比較小 ，資料量才155條，對性能影響較小，如果預期後面資料量變大，考慮增加索引。
-
10）select task0_.id as id1_0_, task0_.admin_group as admin_gr2_0_, task0_.alert_group as alert_gr3_0_, task0_.business_flag as business4_0_, task0_.gmt_create as gmt_crea5_0_, task0_.creator as creator6_0_, task0_.description as descript7_0_, task0_.flag as flag8_0_, task0_.modifier as modifier9_0_, task0_.name as name10_0_, task0_.project_id as project11_0_, task0_.props as props12_0_, task0_.type as type13_0_, task0_.gmt_modified as gmt_mod14_0_, task0_.view_group as view_gr15_0_ from tj_task task0_ where task0_.project_id in (1157 , 1913 , 2078);
分析全表掃描：目前太極任務資料表比較小 ，資料量才9條，對性能影響較小，如果預期後面資料量變大，考慮增加索引。
- 11）慢查詢随着某個工程下工作流越多越慢，性能影響很大 select flow_id, max(id * 1000 + status) % 1000 as last_user_drive_status from mlstudio_execution_jobflow where (drive_type = 1 or drive_type is null) and project_id in (24529) group by flow_id
-
存在問題掃描大量資料，拷貝到臨時表，在執行檔案排序。
修改為：select f.flow_id,f.status from mlstudio_model_flow t inner join mlstudio_execution_jobflow f on t.last_jobflow_id=f.id where t.project_id in (24529)
MySQL調優之innodb_buffer_pool_size大小設定
查詢線上配置：
sql> show global variables like ‘innodb_buffer_pool_size’;
sql> show global status like ‘Innodb_buffer_pool_pages_data’;
sql> show global status like ‘Innodb_page_size’;
sql> show global status like ‘Innodb_buffer_pool_pages_total’;
内網查詢資料結果：
Innodb_buffer_pool_pages_total | 8191
Innodb_buffer_pool_pages_data | 8116
Innodb_page_size | 16384
innodb_buffer_pool_size | 134217728
調優參考計算方法：
val = Innodb_buffer_pool_pages_data / Innodb_buffer_pool_pages_total * 100%
val > 95% 則考慮增大 innodb_buffer_pool_size， 建議使用實體記憶體的75%
val < 95% 則考慮減小 innodb_buffer_pool_size， 建議設定為：Innodb_buffer_pool_pages_data * Innodb_page_size * 1.05 / (102410241024)
内網計算出來：8116/8190=99% 需要加大這個資料
資料庫配置修改: 測試環境修改的/etc/my.cnf
1、開啟慢查詢日志，慢查詢記錄為1秒 ，這個對資料庫性能有1%的影響,可以開啟一段時間收集一段時間資料後關閉
slow_query_log = ON
long_query_time = 1
2、Innodb緩存增大
innodb_buffer_pool_size = 2G #設定2G
3、臨時表目前64M 需要加大
tmp_table_size = 256M;
max_heap_table_size = 256M;
5
總結
本文簡單介紹了資料庫優化的相關方法，通過兩個工具全局分析:mysqlreport對show status 這些參考資料加以融合計算，整理成一個個優化參考點，然後就可以根據這個優化參考點的值以及該點的衡量标準，進行對應的調整。
pt-query-digest 工具，可以從logs、processlist、和tcpdump 來分析MySQL的狀況，logs包括slow log、general log、binlog，可以借助分析結果找出問題進行優化。通過這兩個工具可以在資料庫配置層，對mysql進行相對比較優化的配置還可以找出性能比較慢的語句，通過profiling 詳細分析sql執行的過程進行優化。
作者:張浩
來源:微信公衆号:騰訊大講堂
出處:https://mp.weixin.qq.com/s/UOa7tXqk0m4SvfZL0ikgLA