【MySQL資料庫】第三章解讀：伺服器效能剖析（上）

前言：

    保持空杯精神，使用效能剖析，專注於測量伺服器的時間花費在哪裡，思考1、如何確認伺服器是否達到了效能最佳狀態，2、某條語句為什麼不夠快，診斷被使用者描述為「停頓、堆積、卡死」的某些間歇性疑難故障；

   接下來將介紹一些工具、技巧優化整機效能、優化單一語句執行速度，診斷解決那些很難觀察到的問題，展示如何測量系統並產生剖析報告、如何分析系統的堆疊；

3.1簡介

效能：為完成某件任務所需的時間度量，in other words 效能即回應時間

吞吐量：單位時間內的查詢資料（效能定義的倒數）

第一步：弄清楚時間都去哪了，在哪裡消耗了時間

     如果通測量沒有找到答案，測量方式錯了或不夠完善，只測量需要優化的活動

#不要在錯誤的時間啟動或停止測試，測量的是聚合後的信息而不是目標活動本身；需要定位和優化子任務

原則：無法測量便無法有效優化

3.1.1透過效能剖析進行最佳化

效能剖析：測量、分析時間花費在哪裡的主要方法

       1、測量任務所花費的時間；2、對結果統計、排序（重要前排）

可將相似任務分組匯總，透過效能剖析報告獲需要的結果；報告會列出all任務，每行記錄一個任務：

       任務名稱、執行時間、消耗時間、平均執行時間，執行佔全部時間的百分比；依照任務的消耗時間降序排序；

效能剖析類型：

基於執行時間的分析：什麼任務的執行時間最長

基於等待的分析：判斷任務在什麼地方唄阻塞的時間最長

3.1.2理解性能剖析

效能分析中缺少但重要的資訊：

1、值得最佳化的查詢

        佔總回應時間比重很小的查詢不值得最佳化；成本大於效益、停止最佳化

2、異常狀況

        沒有明確要最佳化的也要最佳化，如執行次數少但每次都特別慢的任務

3、未知的未知

        遺失時間：任務總時間與實際測量到的時間的差，即使沒有發現也要注意這類問題存在的可能性

#4、被遮蔽的細節

        無法顯示all回應時間的分佈，更多資訊、直方圖、百分比、標準差、偏差指數

#5、無法再更高層次的堆疊中進行互動式分析

#3.2對應用程式進行效能剖析：自上而下

效能瓶頸的影響因素：

1、外部資源，呼叫外部web服務或搜尋引擎

2、應用需要處理大量數據，分析一個超大的xml檔案

3、循環中執行昂貴的操作：濫用正規

4、使用低效的演算法：暴力搜尋演算法

建議：新的專案應考慮包含效能剖析的程式碼

3.2.1測量PHP應用程式：空

3.3剖析MySQL查詢

3.3.1剖析伺服器負載

鋪上MySQL查詢到日誌檔案：

     1、慢查詢日誌：開銷低、精確度高，大的磁碟空間，長期開啟注意部署日誌輪轉工具，只在收集負載樣本期間開啟即可，5.1後微秒等級；

     2、通用日誌，查詢請求到伺服器時進行記錄，不包含回應時間和執行計畫

##分析查詢日誌

自頂向下，先生成剖析報告（pt-query-digest），查看特別關注的部分

3.3.2剖析單條查詢

思考為什麼花費這麼長時間、如何去最佳化

使用SHOW PROFILE:MySQL5.1後

檢視： show variables like "%pro%";【來源】

##預設停用，開啟：set profiling =1；然後在伺服器執行語句（關閉 set profiling=off;）

語法：

SHOW PROFILE [type [, type] ... ]  
    [FOR QUERY n]  
    [LIMIT row_count [OFFSET offset]]  
  
type:  
    ALL                --显示所有的开销信息  
  | BLOCK IO           --显示块IO相关开销  
  | CONTEXT SWITCHES   --上下文切换相关开销  
  | CPU                --显示CPU相关开销信息  
  | IPC                --显示发送和接收相关开销信息  
  | MEMORY             --显示内存相关开销信息  
  | PAGE FAULTS        --显示页面错误相关开销信息  
  | SOURCE             --显示和Source_function，Source_file，Source_line相关的开销信息  
  | SWAPS              --显示交换次数相关开销的信息

实质是这些开销信息被记录到information_schema.profiling表

show profiles；查看
show profile for query 2; 获取指定查询的开销（第二条查询开销明细）
show profile cpu for query 2 ;查看特定部分的开销，如下为CPU部分的开销 
show profile block io,cpu for query 2;  同时查看不同资源开销

使用SHOW STATUS:計數器

全域show global status、基於某個連接會話級別，作用域要注意

計數器顯示活動的頻繁程度，常用：句柄計數器、臨時檔案、表計數器

會建立臨時表，透過句柄操作（引用、指針？ ）存取此臨時表，影響show status結果中對應的數字

使用慢查詢日誌：【來源】【來源】

將MySQL中回應時間

超過

閾值long_query_time的語句記錄到慢查詢日誌中（日誌可以寫入檔案或資料庫表，如果對效能要求高的話，建議寫檔案），預設是10s，需要手動開啟檢視：

       

（1）slow_query_log的值为ON为开启慢查询日志，OFF则为关闭慢查询日志。

（2）slow_query_log_file 的值是记录的慢查询日志到文件中（注意：默认名为主机名.log，慢查询日志是否写入指定文件中，需要指定慢查询的输出日志格式为文件，相关命令为：show variables like ‘%log_output%’；去查看输出的格式）。

（3）long_query_time 指定了慢查询的阈值，即如果执行语句的时间超过该阈值则为慢查询语句，默认值为10秒。

（4）log_queries_not_using_indexes 如果值设置为ON，则会记录所有没有利用索引的查询（注意：如果只是将log_queries_not_using_indexes设置为ON，而将slow_query_log设置为OFF，此时该设置也不会生效，即该设置生效的前提是slow_query_log的值设置为ON），一般在性能调优的时候会暂时开启，开启后使用full index scan的sql也会被记录到慢查询日志。

//上述命令只对当前生效，当MySQL重启失效，如果要永久生效，需要配置my.cnf

查看输出格式：文件？表show variables like ‘%log_output%’;

开启通用日志查询： set global general_log=on;

关闭通用日志查询： set globalgeneral_log=off;

设置通用日志输出为表方式： set globallog_output=’TABLE’;

设置通用日志输出为文件方式： set globallog_output=’FILE’;

设置通用日志输出为表和文件方式：set global log_output=’FILE,TABLE’;

查询慢查询语句的个数：show global status like ‘%slow%’;

日志部分内容简介：

哪条语句导致慢查询（sql_text），该慢查询语句的查询时间（query_time），锁表时间（Lock_time），以及扫描过的行数（rows_examined）等信息。

利用自带的慢查询日志分析工具：mysqldumpslow

perl mysqldumpslow –s c –t 10 slow-query.log

-s 表示按何种方式排序，c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序，ac、at、al、ar，表示相应的倒叙；-t 表示top的意思，后面跟着的数据表示返回前面多少条；-g 后面可以写正则表达式匹配，大小写不敏感。

使用Performance Schema:【源】【源】

监视MySQL服务器，收集性能参数，且表的存储引擎PERFORMANCE_SCHEMA，低耗能

本地服务器，表是内存表，表内容在服务器启动时重新填充，关闭时丢弃，更改不会被复制或写入二进制日志

特性：

性能方案配置可被动态的执行SQL修改，立即影响到数据收集

监控服务事件：事件是服务做并被感知到的任何事，时间信息可被收集

数据库性能方案，提供对运行时数据库服务进行内部检查的方式，关注性能数据

特定于一个数据库服务，数据库表关联到数据服务，修改不会被备份也不写进二进制日志

存储引擎用“感知点”收集事件数据，且存储在performance_schema数据库，可通过select语句进行查询

补充：数据库初始安装有三个基本库

mysql

    包含权限配置，事件，存储引擎状态，主从信息，日志，时区信息，用户权限配置等

information_schema

    对数据库元数据的抽象分析，由此提供了SQL语句方式来查询数据库运行时状态，每次对information_schema的查询都产生对metadata的互斥访问，影响其他数据库的访问性能。

performance_schema

    内存型数据库，使用performance_schema 存储引擎，通过事件机制将mysql服务的运行时状态采集并存储在performace_schema数据库。注意，两个单词之间用下划线连接时，表示performance_schema是一个数据库；用空格分开时，表示一个数据库性能方案，也表示一个存储引擎。

 相关文章：

【MySQL数据库】第三章解读：服务器性能剖析 （下）

【MySQL数据库】第二章解读：MySQL基准测试

以上是【MySQL資料庫】第三章解讀：伺服器效能剖析（上）的詳細內容。更多資訊請關注PHP中文網其他相關文章！