MySQL運(yùn)維利器percona-toolkit工具的pt-query-digest語(yǔ)法以及用法,很多新手對(duì)此不是很清楚,為了幫助大家解決這個(gè)難題,下面小編將為大家詳細(xì)講解,有這方面需求的人可以來(lái)學(xué)習(xí)下,希望你能有所收獲。
創(chuàng)新互聯(lián)專注于網(wǎng)站建設(shè),為客戶提供網(wǎng)站制作、網(wǎng)站建設(shè)、網(wǎng)頁(yè)設(shè)計(jì)開(kāi)發(fā)服務(wù),多年建網(wǎng)站服務(wù)經(jīng)驗(yàn),各類網(wǎng)站都可以開(kāi)發(fā),成都品牌網(wǎng)站建設(shè),公司官網(wǎng),公司展示網(wǎng)站,網(wǎng)站設(shè)計(jì),建網(wǎng)站費(fèi)用,建網(wǎng)站多少錢,價(jià)格優(yōu)惠,收費(fèi)合理。
mysql版本5.7.21
redhat 6.8
啟用慢查詢
mysql> set global slow_query_log=on; Query OK, 0 rows affected (0.01 sec)
啟用未使用索引慢查詢
mysql> set global log_queries_not_using_indexes=on; Query OK, 0 rows affected (0.00 sec)
創(chuàng)建測(cè)試表
mysql> use zxydb; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> insert into t_slow select * from t_slow; Query OK, 8388608 rows affected (35.04 sec) Records: 8388608 Duplicates: 0 Warnings: 0
執(zhí)行sql查詢
mysql> select count(*) from zxydb.t_slow; +----------+ | count(*) | +----------+ | 16777216 | +----------+ 1 row in set (6.48 sec)
查詢pt-query-digest選項(xiàng)
[root@three57 percona-toolkit-3.1.0]# pt-query-digest --help pt-query-digest analyzes MySQL queries from slow, general, and binary log files. It can also analyze queries from C<SHOW PROCESSLIST> and MySQL protocol data from tcpdump. By default, queries are grouped by fingerprint and reported in descending order of query time (i.e. the slowest queries first). If no C<FILES> are given, the tool reads C<STDIN>. The optional C<DSN> is used for certain options like L<"--since"> and L<"--until">. For more details, please use the --help option, or try 'perldoc /usr/local/bin/pt-query-digest' for complete documentation. Usage: pt-query-digest [OPTIONS] [FILES] [DSN] Options: --ask-pass Prompt for a password when connecting to MySQL --attribute-aliases=a List of attribute|alias,etc (default db|Schema) --attribute-value-limit=i A sanity limit for attribute values (default 0) --charset=s -A Default character set --config=A Read this comma-separated list of config files; if specified, this must be the first option on the command line
獲取完整pt-query-digest全部語(yǔ)義信息
[root@three57 percona-toolkit-3.1.0]# man pt-query-digest>/pt-query.log
[root@three57 percona-toolkit-3.1.0]# pt-query-digest /var/lib/mysql/three57-slow.log 用戶時(shí)間分布及進(jìn)程內(nèi)程內(nèi)存 # 100ms user time, 10ms system time, 21.88M rss, 173.04M vsz 當(dāng)前系統(tǒng)時(shí)間 # Current date: Wed Nov 13 15:29:44 2019 主機(jī)名稱 # Hostname: three57 慢查詢?nèi)罩? # Files: /var/lib/mysql/three57-slow.log QPS及并發(fā) # Overall: 1 total, 1 unique, 0 QPS, 0x concurrency ______________________ # Time range: all events occurred at 2019-11-13T07:28:59 屬性:全部,最小,最大,平均,95%,平均利差,中差 # Attribute total min max avg 95% stddev median # ============ ======= ======= ======= ======= ======= ======= ======= SQL執(zhí)行時(shí)間 # Exec time 7s 7s 7s 7s 7s 0 7s 鎖定時(shí)間 # Lock time 127us 127us 127us 127us 127us 0 127us SQL產(chǎn)生結(jié)果個(gè)數(shù) # Rows sent 1 1 1 1 1 0 1 SQL掃描表記錄 # Rows examine 16.00M 16.00M 16.00M 16.00M 16.00M 0 16.00M 查詢大小 # Query size 33 33 33 33 33 0 33 符合慢查詢SQL的概述,極重要,排版一個(gè)字,棒 # Profile RANK為排名編號(hào) query id為具體SQL response time為sql響應(yīng)時(shí)間 calls為SQL執(zhí)行次數(shù) r/call為每次SQL執(zhí)行的響應(yīng)時(shí)間,具體的SQL語(yǔ)句 # Rank Query ID Response time Calls R/Call V/M # ==== ================================== ============= ===== ====== ===== # 1 0xAC104A376C0A55B1F56FDA6E706E555F 6.7433 100.0% 1 6.7433 0.00 SELECT zxydb.t_slow 具體慢查詢SQL語(yǔ)句 # Query 1: 0 QPS, 0x concurrency, ID 0xAC104A376C0A55B1F56FDA6E706E555F at byte 0 # This item is included in the report because it matches --limit. # Scores: V/M = 0.00 # Time range: all events occurred at 2019-11-13T07:28:59 # Attribute pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 100 1 SQL執(zhí)行時(shí)間為7s # Exec time 100 7s 7s 7s 7s 7s 0 7s SQL鎖定時(shí)間為127us,此值如極高,需要針對(duì)性分析 # Lock time 100 127us 127us 127us 127us 127us 0 127us # Rows sent 100 1 1 1 1 1 0 1 全表掃描相關(guān) # Rows examine 100 16.00M 16.00M 16.00M 16.00M 16.00M 0 16.00M # Query size 100 33 33 33 33 33 0 33 # String: 產(chǎn)生SQL的主機(jī)及用戶 # Hosts localhost # Users root SQL不同時(shí)間范圍分布,極好極好 可見(jiàn)如上SQL執(zhí)行時(shí)間在1S左右 # Query_time distribution # 1us # 10us # 100us # 1ms # 10ms # 100ms # 1s ################################################################ # 10s+ # Tables # SHOW TABLE STATUS FROM `zxydb` LIKE 't_slow'\G # SHOW CREATE TABLE `zxydb`.`t_slow`\G # EXPLAIN /*!50100 PARTITIONS*/ select count(*) from zxydb.t_slow\G [root@three57 percona-toolkit-3.1.0]#
看完上述內(nèi)容是否對(duì)您有幫助呢?如果還想對(duì)相關(guān)知識(shí)有進(jìn)一步的了解或閱讀更多相關(guān)文章,請(qǐng)關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道,感謝您對(duì)創(chuàng)新互聯(lián)的支持。
當(dāng)前名稱:mysql運(yùn)維利器percona-toolkit工具的pt-query-digest語(yǔ)法以及用法
本文鏈接:http://muchs.cn/article18/ijoogp.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián),為您提供外貿(mào)網(wǎng)站建設(shè)、微信小程序、云服務(wù)器、定制開(kāi)發(fā)、App開(kāi)發(fā)、外貿(mào)建站
聲明:本網(wǎng)站發(fā)布的內(nèi)容(圖片、視頻和文字)以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主,如果涉及侵權(quán)請(qǐng)盡快告知,我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如需處理請(qǐng)聯(lián)系客服。電話:028-86922220;郵箱:631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載,或轉(zhuǎn)載時(shí)需注明來(lái)源: 創(chuàng)新互聯(lián)