資料庫是個比較大的話題，有各種各樣資料庫常見的關系型資料庫如Mysql 、oracle、非關系型資料庫，還有圖資料庫等，資料庫性能會跟許多部分有關聯，從硬體底層存盤設備、作業系統、資料庫配置引數、資料庫架構、資料庫表結構、應用層面的連接池設定、以及SQL索引等，
資料庫架構
對Mysql資料庫進行分析，首先需要了解MySql的系統架構，如下圖所示：
從這個架構圖，來看Mysql系統架構分為應用層、MySql服務層、存盤引擎層，
-
應用層，應用層是MySQL體系架構的最上層，它和其他client-server架構一樣，主要包含：連接處理、用戶鑒權、安全管理
-
MySQL服務層：該層是MysqlServer的核心層，提供了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中找不到資料的話，就得通過檔案系統到磁盤中進行尋找，
性能分析基本監控指標
了解了mysql系統架構和mysql執行程序還不夠，在進行性能分析時，需要找出mysql的問題所得先了解一些基礎知識和相應的監控工具，
首先需要了解的兩個Schema 分別是information_schema和performance_schema，information_schema，它們保存了資料庫中的所有表、列、索引、權限、配置引數、狀態引數等資訊，像我們常執行的show processlist;就來自于這個schema 中的 processlist 表，performance_schema提供了資料庫運行時的資源消耗情況，它以較低的代價收集資訊， 可以提供不少性能資料，
還有在分析mysql是需要知道的兩個命令:showglobal variables ;和show global status ;前一個用來查看配置的引數值，后一個用來查詢狀態值，不過這些命令只是簡單的羅列資訊，并沒有統計分析，接下來我們介紹兩個個比較好的監控工具，
全域分析: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盡量不要在磁盤上創建臨時表，上面分析結果來看從臨時表創建在磁盤(Disktable)和臨時檔案(File) 上的 量級來說，還是有點偏大了，所以，可以增大tmp_table_size，
其它全域資訊可以查下資料
全域分析結果
通過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-HiResperl-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-ROWSmysql-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 fromproject_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, isGroupFROM 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 WHEREappstatus 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 fromti_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 andtype>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 whereuser_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 ascicon,mmc.level as clevel, mmc.parent_id as cparent,mmc.visible ascvisible,mmc.order_num as corder,mm.id asmid, mm.name as mname,mm.permission as mpermission from mlstudio_module_category mmc left joinmlstudio_modules mm on mmc.id =mm.type) as t group by cid, cname, cdesc, cicon, clevel, cparent, cvisibleorder 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 asmemory6_1_, queuequota0_.name as name7_1_, queuequota0_.gmt_modified asgmt_modi8_1_, queuequota0_.uuid as uuid9_1_ from queue_quota queuequota0_ wherequeuequota0_.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 asmodifier9_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_idin (1157 , 1913 , 2078);
分析全表掃描：目前太極任務資料表比較小 ，資料量才9條，對性能影響較小，如果預期后面資料量變大，考慮增加索引，
慢查詢隨著某個工程下作業流越多越慢，性能影響很大
select flow_id,max(id * 1000 + status) % 1000 as last_user_drive_status frommlstudio_execution_jobflow where (drive_type = 1 or drive_type is null) andproject_id in (24529) group by flow_id
存在問題掃描大量資料，拷貝到臨時表，在執行檔案排序，
修改為：
select f.flow_id,f.status from mlstudio_model_flowt 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;
總結
本文簡單介紹了資料庫優化的相關方法，通過兩個工具全域分析:mysqlreport對show status 這些參考資料加以融合計算，整理成一個個優化參考點，然后就可以根據這個優化參考點的值以及該點的衡量標準，進行對應的調整，
pt-query-digest 工具，可以從logs、processlist、和tcpdump 來分析MySQL的狀況，logs包括slow log、general log、binlog，可以借助分析結果找出問題進行優化，通過這兩個工具可以在資料庫配置層，對mysql進行相對比較優化的配置還可以找出性能比較慢的陳述句，通過profiling 詳細分析sql執行的程序進行優化，
本文由博客一文多發平臺 OpenWrite 發布！
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/187662.html
標籤：其他