您好,登錄后才能下訂單哦!
本篇內(nèi)容主要講解“MySQL慢SQL采集方案分析”,感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷,實用性強。下面就讓小編來帶大家學(xué)習(xí)“MySQL慢SQL采集方案分析”吧!
作為一名MySQL DBA,首要的任務(wù)是維持?jǐn)?shù)據(jù)庫的可用性和穩(wěn)定性,在生產(chǎn)中,有時候一條慢SQL會拖垮整個系統(tǒng)的響應(yīng)和體驗,所以慢SQL治理至關(guān)重要。
首先我們采用pt-query-digest
來進(jìn)行慢sql采集和分析
mysql> show global variables where variable_name in ('slow_query_log','long_query_time','slow_query_log_file','log_queries_not_using_indexes','log_throttle_queries_not_using_indexes','min_examined_row_limit','log_slow_admin_statements','log_slow_slave_statements','log_output'); +----------------------------------------+----------------------------+ | Variable_name | Value | +----------------------------------------+----------------------------+ | log_output | FILE | | log_queries_not_using_indexes | ON | | log_slow_admin_statements | OFF | | log_slow_slave_statements | ON | | log_throttle_queries_not_using_indexes | 100 | | long_query_time | 0.500000 | | min_examined_row_limit | 0 | | slow_query_log | ON | | slow_query_log_file | slowquery_201908161156.log | +----------------------------------------+----------------------------+ 9 rows in set (0.01 sec)
該mysql版本為percona5.7.21版本,控制slow log的參數(shù)主要為這幾個:
log_output: 控制慢sql是記錄在文件還是記錄在table,FILE|TABLE可選擇
log_queries_not_using_indexes:控制沒有使用索引的sql也將被記錄到慢查詢?nèi)罩局?
log_slow_admin_statements:管理語句執(zhí)行時間大于閾值也將寫入到慢查詢?nèi)罩局?,管理語句包括alter table, check table等;
log_slow_slave_statements:從庫應(yīng)用binlog,如果binlog格式是statement,執(zhí)行時間超過閾值時,將寫入從庫的慢查詢?nèi)罩荆?對于ROW格式binlog,不管執(zhí)行時間有沒有超過閾值,都不會寫入到從庫的慢查詢?nèi)罩?
log_throttle_queries_not_using_indexes:如果log_queries_not_using_indexes打開,沒有使用索引的sql將會寫入到慢查詢?nèi)罩局?,該參?shù)將限制每分鐘寫入的sql數(shù)量;
long_query_time:慢查詢閾值,單位秒,執(zhí)行時間超過這個值的將被記錄為慢查詢?nèi)罩局?
min_examined_row_limit:對于查詢掃描行數(shù)小于此參數(shù)的sql,將不會記錄到慢查詢?nèi)罩局校?/p>
slow_query_log:控制是否打開慢查詢;
slow_query_log_file:慢查詢?nèi)罩居涗浳募?/p>
這些參數(shù)我們目前主要使用log_output
,slow_query_log
,slow_query_log_file
,min_examined_row_limit
,log_throttle_queries_not_using_indexes
這幾個
mysql> set global log_output = 'FILE'; Query OK, 0 rows affected (0.00 sec) mysql> set global slow_query_log = 1; Query OK, 0 rows affected (0.00 sec) mysql> set global min_examined_row_limit = 0; Query OK, 0 rows affected (0.00 sec) mysql> set global log_throttle_queries_not_using_indexes = 1; Query OK, 0 rows affected (0.00 sec) mysql> set global slow_query_log_file = 'slowquery_201908161203.log'; Query OK, 0 rows affected (0.00 sec)
設(shè)置參數(shù)后開始安裝pt工具
我們這里直接安裝個全家桶(percona-toolkit
)吧,首先安裝依賴包:
yum install -y perl perl-IO-Socket-SSL perl-DBD-MySQL perl-Time-HiRes perl-Digest-MD5 perl-ExtUtils-MakeMaker
然后下載percona-toolkit:
https://www.percona.com/downloads/percona-toolkit/LATEST/
這里是cenos7環(huán)境,如果是其他環(huán)境另行下載
我們直接安裝
rpm -ivh percona-toolkit-3.0.13-1.el7.x86_64.rpm
pt-query-digest [OPTIONS] [FILES] [DSN] --create-review-table 當(dāng)使用--review參數(shù)把分析結(jié)果輸出到表中時,如果沒有表就自動創(chuàng)建。 --create-history-table 當(dāng)使用--history參數(shù)把分析結(jié)果輸出到表中時,如果沒有表就自動創(chuàng)建。 --filter 對輸入的慢查詢按指定的字符串進(jìn)行匹配過濾后再進(jìn)行分析 --limit 限制輸出結(jié)果百分比或數(shù)量,默認(rèn)值是20,即將最慢的20條語句輸出,如果是50%則按總響應(yīng)時間占比從大到小排序,輸出到總和達(dá)到50%位置截止。 --host mysql服務(wù)器地址 --user mysql用戶名 --password mysql用戶密碼 --history 將分析結(jié)果保存到表中,分析結(jié)果比較詳細(xì),下次再使用--history時,如果存在相同的語句,且查詢所在的時間區(qū)間和歷史表中的不同,則會記錄到數(shù)據(jù)表中,可以通過查詢同一CHECKSUM來比較某類型查詢的歷史變化。 --review 將分析結(jié)果保存到表中,這個分析只是對查詢條件進(jìn)行參數(shù)化,一個類型的查詢一條記錄,比較簡單。當(dāng)下次使用--review時,如果存在相同的語句分析,就不會記錄到數(shù)據(jù)表中。 --output 分析結(jié)果輸出類型,值可以是report(標(biāo)準(zhǔn)分析報告)、slowlog(Mysql slow log)、json、json-anon,一般使用report,以便于閱讀。 --since 從什么時間開始分析,值為字符串,可以是指定的某個”yyyy-mm-dd [hh:mm:ss]”格式的時間點,也可以是簡單的一個時間值:s(秒)、h(小時)、m(分鐘)、d(天),如12h就表示從12小時前開始統(tǒng)計。 --until 截止時間,配合—since可以分析一段時間內(nèi)的慢查詢。
1、分析慢查詢文件:
pt-query-digest slow-query.log > slow-query-report.log
2、分析最近1小時內(nèi)的查詢:
pt-query-digest --since=1h slow-query.log > slow-before_1h.log
3、分析指定時間范圍內(nèi)的查詢:
pt-query-digest slow-query.log --since '2019-08-08 10:30:00' --until '2019-08-08 10:35:00' > slow-query-5min.log
4、針對某個用戶的慢查詢
pt-query-digest --filter '($event->{user} || "") =~ m/^dba/i' slow-query.log > slow-query-fordbauser.log
5、查詢所有所有的全表掃描或full join的慢查詢
pt-query-digest --filter '(($event->{Full_scan} || "") eq "yes") ||(($event->{Full_join} || "") eq "yes")' slow-query.log> slow_query-full.log
6、把查詢保存到t_slowq_review表
pt-query-digest --user=dbms –password=admin --review h=localhost,D=test,t=t_slowq_review --create-review-table slow-query.log
7、把查詢保存到t_slowq_details表
pt-query-digest --user=dbms –password=admin --history h=localhost,D=test,t=t_slowq_details --create-history-table slow-query.log
8、分析指含有select語句的慢查詢
pt-query-digest --filter '$event->{fingerprint} =~ m/^select/i' slow-query.log> slow-query-justselect.log
9、通過tcpdump抓取mysql的tcp協(xié)議數(shù)據(jù),然后再分析
tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > mysql.tcp3306.txt pt-query-digest --type tcpdump mysql.tcp3306.txt> slow-query-3306.log
10、分析binlog
mysqlbinlog mysql-bin.000011 > mysql-bin.000011.sql pt-query-digest --type=binlog mysql-bin.000011.sql > mysql-bin.000011.log
11.分析general log
pt-query-digest --type=genlog general_3306.log > generallog_3306.log
這里采用直接入庫的方式,然后會采用腳本去切割,可以使用時間間隔切割,然后刪除,嘗試過since util,發(fā)現(xiàn)文本越大,每次的解析時間都會很長,不太適合生產(chǎn)部署,可以采用切割日志,如果需要保留本地的話,可以拷貝走
pt-query-digest \ --user=user --password=password --port=port \ --review h=ip,D=dbname,t=t_slowq_review \ --history h=ip,D=dbname,t=t_slowq_details \ --no-report --limit=100% --charset=utf8 \ --filter="\$event->{Bytes}=length(\$event->{arg}) and \$event->{instanceid}=15 and \$event->{hostname}='idc-mysql18' and \$event->{client}=\$event->{ip}" \ slow-query.log
在生產(chǎn)中由于可能存在分庫,所以sql的checksum值可能會一致,所以我們加了每個實例的id進(jìn)去來標(biāo)識不同的數(shù)據(jù)庫,完全靠checksum值來標(biāo)記可能混淆
CREATE TABLE `t_slowq_details` ( `instanceid_max` int(11) NOT NULL, `hostname_max` varchar(64) NOT NULL, `client_max` varchar(64) DEFAULT NULL, `user_max` varchar(64) NOT NULL, `db_max` varchar(64) DEFAULT NULL, `checksum` char(32) NOT NULL, `sample` longtext NOT NULL, `ts_min` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `ts_max` datetime(6) NOT NULL, `ts_cnt` float DEFAULT NULL, `Query_time_sum` float DEFAULT NULL, `Query_time_min` float DEFAULT NULL, `Query_time_max` float DEFAULT NULL, `Query_time_pct_95` float DEFAULT NULL, `Query_time_stddev` float DEFAULT NULL, `Query_time_median` float DEFAULT NULL, `Lock_time_sum` float DEFAULT NULL, `Lock_time_min` float DEFAULT NULL, `Lock_time_max` float DEFAULT NULL, `Lock_time_pct_95` float DEFAULT NULL, `Lock_time_stddev` float DEFAULT NULL, `Lock_time_median` float DEFAULT NULL, `Rows_sent_sum` float DEFAULT NULL, `Rows_sent_min` float DEFAULT NULL, `Rows_sent_max` float DEFAULT NULL, `Rows_sent_pct_95` float DEFAULT NULL, `Rows_sent_stddev` float DEFAULT NULL, `Rows_sent_median` float DEFAULT NULL, `Rows_examined_sum` float DEFAULT NULL, `Rows_examined_min` float DEFAULT NULL, `Rows_examined_max` float DEFAULT NULL, `Rows_examined_pct_95` float DEFAULT NULL, `Rows_examined_stddev` float DEFAULT NULL, `Rows_examined_median` float DEFAULT NULL, `Rows_affected_sum` float DEFAULT NULL, `Rows_affected_min` float DEFAULT NULL, `Rows_affected_max` float DEFAULT NULL, `Rows_affected_pct_95` float DEFAULT NULL, `Rows_affected_stddev` float DEFAULT NULL, `Rows_affected_median` float DEFAULT NULL, `Rows_read_sum` float DEFAULT NULL, `Rows_read_min` float DEFAULT NULL, `Rows_read_max` float DEFAULT NULL, `Rows_read_pct_95` float DEFAULT NULL, `Rows_read_stddev` float DEFAULT NULL, `Rows_read_median` float DEFAULT NULL, `Merge_passes_sum` float DEFAULT NULL, `Merge_passes_min` float DEFAULT NULL, `Merge_passes_max` float DEFAULT NULL, `Merge_passes_pct_95` float DEFAULT NULL, `Merge_passes_stddev` float DEFAULT NULL, `Merge_passes_median` float DEFAULT NULL, `InnoDB_IO_r_ops_min` float DEFAULT NULL, `InnoDB_IO_r_ops_max` float DEFAULT NULL, `InnoDB_IO_r_ops_pct_95` float DEFAULT NULL, `InnoDB_IO_r_ops_stddev` float DEFAULT NULL, `InnoDB_IO_r_ops_median` float DEFAULT NULL, `InnoDB_IO_r_bytes_min` float DEFAULT NULL, `InnoDB_IO_r_bytes_max` float DEFAULT NULL, `InnoDB_IO_r_bytes_pct_95` float DEFAULT NULL, `InnoDB_IO_r_bytes_stddev` float DEFAULT NULL, `InnoDB_IO_r_bytes_median` float DEFAULT NULL, `InnoDB_IO_r_wait_min` float DEFAULT NULL, `InnoDB_IO_r_wait_max` float DEFAULT NULL, `InnoDB_IO_r_wait_pct_95` float DEFAULT NULL, `InnoDB_IO_r_wait_stddev` float DEFAULT NULL, `InnoDB_IO_r_wait_median` float DEFAULT NULL, `InnoDB_rec_lock_wait_min` float DEFAULT NULL, `InnoDB_rec_lock_wait_max` float DEFAULT NULL, `InnoDB_rec_lock_wait_pct_95` float DEFAULT NULL, `InnoDB_rec_lock_wait_stddev` float DEFAULT NULL, `InnoDB_rec_lock_wait_median` float DEFAULT NULL, `InnoDB_queue_wait_min` float DEFAULT NULL, `InnoDB_queue_wait_max` float DEFAULT NULL, `InnoDB_queue_wait_pct_95` float DEFAULT NULL, `InnoDB_queue_wait_stddev` float DEFAULT NULL, `InnoDB_queue_wait_median` float DEFAULT NULL, `InnoDB_pages_distinct_min` float DEFAULT NULL, `InnoDB_pages_distinct_max` float DEFAULT NULL, `InnoDB_pages_distinct_pct_95` float DEFAULT NULL, `InnoDB_pages_distinct_stddev` float DEFAULT NULL, `InnoDB_pages_distinct_median` float DEFAULT NULL, `QC_Hit_cnt` float DEFAULT NULL, `QC_Hit_sum` float DEFAULT NULL, `Full_scan_cnt` float DEFAULT NULL, `Full_scan_sum` float DEFAULT NULL, `Full_join_cnt` float DEFAULT NULL, `Full_join_sum` float DEFAULT NULL, `Tmp_table_cnt` float DEFAULT NULL, `Tmp_table_sum` float DEFAULT NULL, `Tmp_table_on_disk_cnt` float DEFAULT NULL, `Tmp_table_on_disk_sum` float DEFAULT NULL, `Filesort_cnt` float DEFAULT NULL, `Filesort_sum` float DEFAULT NULL, `Filesort_on_disk_cnt` float DEFAULT NULL, `Filesort_on_disk_sum` float DEFAULT NULL, `Bytes_sum` float DEFAULT NULL, `Bytes_min` float DEFAULT NULL, `Bytes_max` float DEFAULT NULL, `Bytes_pct_95` float DEFAULT NULL, `Bytes_stddev` float DEFAULT NULL, `Bytes_median` float DEFAULT NULL, PRIMARY KEY (`instanceid_max`,`checksum`,`ts_min`,`ts_max`) USING BTREE, KEY `idx_hostname_max_ts_min` (`instanceid_max`,`ts_min`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 /*!50100 PARTITION BY RANGE (UNIX_TIMESTAMP(ts_min)) (PARTITION p201908 VALUES LESS THAN (1567267200) ENGINE = InnoDB, PARTITION p201909 VALUES LESS THAN (1569859200) ENGINE = InnoDB, PARTITION p201910 VALUES LESS THAN (1572537600) ENGINE = InnoDB, PARTITION p201911 VALUES LESS THAN (1575129600) ENGINE = InnoDB, PARTITION p201912 VALUES LESS THAN (1577808000) ENGINE = InnoDB) */ CREATE TABLE `t_slowq_review` ( `checksum` char(32) NOT NULL, `fingerprint` longtext NOT NULL, `sample` longtext NOT NULL, `first_seen` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `last_seen` datetime(6) DEFAULT NULL, `reviewed_by` varchar(20) DEFAULT NULL, `reviewed_on` datetime(6) DEFAULT NULL, `comments` longtext, `reviewed_status` varchar(24) DEFAULT NULL, PRIMARY KEY (`checksum`) USING BTREE, KEY `idx_last_seen` (`last_seen`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8
這個腳本只是提供一個思路,邏輯就是,會自動去切割slowlog,然后解析上傳,切割后解析玩會刪除前一個日志,如果需要保留本地日志可以把代碼里面的刪除改成拷貝走,其中我提出的instance_id是我們覺得生產(chǎn)中只有checksum沒法和系統(tǒng)進(jìn)行結(jié)合和日常使用,各位可以參考使用,并進(jìn)行改造,其中有啥問題可以留言????
#!/usr/bin/env python # -*- coding:utf-8 -*- # create_time: 2019-08-08 11:20 import sys import os import socket import time from multiprocessing import Process import subprocess import pymysql as MySQLdb DBMS_HOST = "" # 存儲數(shù)據(jù)庫的ip DBMS_NAME = "" # 存儲數(shù)據(jù)庫的dbname DBMS_PORT = # 存儲數(shù)據(jù)庫的端口 DBMS_PWD = "" # 存儲數(shù)據(jù)庫的賬號密碼 DBMS_USER = "" # 存儲數(shù)據(jù)庫的賬號 DBA_HOST = "" # 被采集實例的ip,一般默認(rèn)為 127.0.0.1 DBA_USER = "" # 被采集實例的 用戶 DBA_PASSWORD = "" # 被采集實例 用戶密碼 # 一般數(shù)據(jù)實例上建立一個 dba管理賬號來使用 class MySQLConnection(object): def __init__(self, host, port, user, passwd, db=None, charset="utf8"): self.host = host self.port = port self.user = user self.passwd = passwd self.db = db self.charset = charset self.error = "" def Success(self): return self.error == "" def Connect(self): self.error = "" try: self.conn = MySQLdb.connect(host=self.host, port=self.port, user=self.user, passwd=self.passwd, charset=self.charset) if self.db: self.conn.select_db(self.db) self.cursor = self.conn.cursor() except Exception as e: self.error = str(e) def Close(self): self.error = "" try: self.cursor.close() self.conn.close() except Exception as e: self.error = str(e) def Fileds(self): res = [] try: for r in self.cursor.description: res.append(r[0]) except: pass return res def Query(self, sql, params=None, rownum=[0], fetch=0): self.error = "" data = () try: if params: rownum[0] = self.cursor.execute(sql, params) else: rownum[0] = self.cursor.execute(sql) if rownum[0] > 0: if fetch == 0: data = self.cursor.fetchall() elif fetch == 1: data = self.cursor.fetchone() except Exception as e: self.error = str(e) return data def Execute(self, sql, params=None): self.error = "" try: if params: self.cursor.execute(sql, params) else: self.cursor.execute(sql) self.conn.commit() except Exception as e: self.error = str(e) return False return True def RunCommand(c): p = subprocess.Popen(c, stdout=subprocess.PIPE, stderr = subprocess.PIPE, shell = True) (stdout, stderr) = p.communicate() return (stdout.decode("utf-8"), stderr.decode("utf-8")) def log_out(instance_id, start = time.time(), error = None): """ 這里是用來記錄慢sql解析情況的,會上報相關(guān)狀態(tài) """ dbms_con = MySQLConnection(DBMS_HOST, DBMS_PORT, DBMS_USER, DBMS_PWD, DBMS_NAME) dbms_con.Connect() if dbms_con.error: return start_time = time.strftime('%Y-%m-%d %T', time.localtime(start)) dbms_con.Execute("replace into t_slowq_message(instance_id, error, start_time) values(%s, %s, %s)", (instance_id, error, start_time)) dbms_con.Close() def GetSlowlogfile(instanceid,port): """ slowlog = datadir+slowlog :param port: :return: """ local_conn = MySQLConnection(host=DBA_HOST, port=port, user=DBA_USER, passwd=DBA_PASSWORD) local_conn.Connect() if not local_conn.error: data = local_conn.Query("show global variables where variable_name in ('long_query_time', 'slow_query_log_file','datadir')") if data: res = { data[0][0]: data[0][1], data[1][0]: data[1][1], data[2][0]: data[2][1], } local_conn.Close() return res local_conn.Close() error = "get slowlog_file paremeters failed" log_out(instance_id=instanceid, error=error) return None else: log_out(instance_id=instanceid, error=str(local_conn.error)) local_conn.Close() return None def SwitchSlowlogfile(port): local_conn = MySQLConnection(host=DBA_HOST, port=port, user=DBA_USER, passwd=DBA_PASSWORD) local_conn.Connect() if local_conn.error: return False tmp_log = "slowquery_%s.log" % (time.strftime('%Y%m%d%H%M', time.localtime(time.time())),) res = local_conn.Execute("set global slow_query_log_file = %s", tmp_log) return True def CollectSlowlog(instanceid,port,hostname): res = GetSlowlogfile(instanceid,port) if res: slowlog_time = eval(res["long_query_time"]) slowlog_file = res["slow_query_log_file"] data_dir = res['datadir'] slow_log = data_dir+slowlog_file if SwitchSlowlogfile(port): try: pt_query_digest = RunCommand("whereis pt-query-digest")[0].split()[1] except: pt_query_digest = "/usr/bin/pt-query-digest" cmd = "%s --user=%s --password=%s --port=%s --review h=%s,D=%s,t=%s --history h=%s,D=%s,t=%s --no-report --limit=100%% --charset=utf8 " \ "--filter=\"\\$event->{Bytes} = length(\\$event->{arg}) and \\$event->{instanceid}=%s and \\$event->{hostname}='%s'" \ " and \$event->{client}=\$event->{ip}\" %s " % ( pt_query_digest, DBMS_USER, DBMS_PWD, DBMS_PORT, DBMS_HOST, DBMS_NAME, "t_slowq_review", DBMS_HOST, DBMS_NAME, "t_slowq_details", instanceid, hostname,slow_log) out, error = RunCommand(cmd) if not error: os.remove(slow_log) log_out(instance_id=instanceid) else: error = "parse slowlog_file failed" log_out(instance_id=instanceid,error=error) else: error = "switch slowlog_file failed" log_out(instance_id=instanceid, error=error) def Main(): """ 該部分可以自己進(jìn)行改造,我這邊的邏輯是我有一張記錄實例的元數(shù)據(jù)庫,來查詢當(dāng)前主機上面的實例,然后獲取實例id和port,各位可以自行改造 """ try: hostname = socket.gethostname() dbms_con = MySQLConnection(DBMS_HOST, DBMS_PORT, DBMS_USER, DBMS_PWD, DBMS_NAME) dbms_con.Connect() if dbms_con.error: return sql1 = "SELECT a.instance_id,a.port FROM t_info_instance AS a JOIN t_info_machine AS b ON a.m_id = b.m_id WHERE b.hostname = %s" res1 = dbms_con.Query(sql1, (hostname,)) if res1 != None and len(res1) > 0: for instance in res1: instance_id = instance[0] port = instance[1] p = Process(target=CollectSlowlog,args=(instance_id,port,hostname)) p.start() dbms_con.Close() except Exception as e: print (e) if __name__ == "__main__": Main()
另一張狀態(tài)表表結(jié)構(gòu):
CREATE TABLE `t_slowq_message` ( `instance_id` int(11) NOT NULL, `error` text, `start_time` datetime NOT NULL, `end_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`instance_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
到此,相信大家對“MySQL慢SQL采集方案分析”有了更深的了解,不妨來實際操作一番吧!這里是億速云網(wǎng)站,更多相關(guān)內(nèi)容可以進(jìn)入相關(guān)頻道進(jìn)行查詢,關(guān)注我們,繼續(xù)學(xué)習(xí)!
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進(jìn)行舉報,并提供相關(guān)證據(jù),一經(jīng)查實,將立刻刪除涉嫌侵權(quán)內(nèi)容。