Revisions
-
diafygi revised this gist
Dec 16, 2011 . 1 changed file with 1 addition and 1 deletion.There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -8,7 +8,7 @@ <root_pass> to your mysql root password (e.g. "hunter2") <host_domain> to your mysql root password (e.g. "prod-01.w3rfs2.us-east-1.rds.amazonaws.com") Run this script by: python /path/to/slow_query_log_dump.py > /path/to/slow_query_dump.log Then you can run the normal mysqldumpslow parser on the output file (slow_query_dump.log) -
diafygi revised this gist
Dec 16, 2011 . 1 changed file with 2 additions and 1 deletion.There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -1,6 +1,7 @@ """ Queries the slowlog database table maintained by Amazon RDS and outputs it in the normal MySQL slow log text format. Modified version of the script by memonic (Thanks!) at https://gist.github.com/1481025 Things to change in this script for your own setup: <root_user> to your mysql root user (e.g. "root") @@ -15,7 +16,7 @@ mysqldumpslow -t 40 -s t /path/to/slow_query_dump.log To clear the slow_log table on the RDS run the following command: mysql -u'<root_user>' -p'<root_pass>' -h <host_domain> mysql -e 'CALL rds_rotate_slow_log' """ -
diafygi revised this gist
Dec 16, 2011 . 1 changed file with 9 additions and 1 deletion.There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -1,6 +1,6 @@ """ Queries the slowlog database table maintained by Amazon RDS and outputs it in the normal MySQL slow log text format. Modified version of the script by Things to change in this script for your own setup: <root_user> to your mysql root user (e.g. "root") @@ -9,6 +9,14 @@ Run this command by: python /path/to/slow_query_log_dump.py > /path/to/slow_query_dump.log Then you can run the normal mysqldumpslow parser on the output file (slow_query_dump.log) Example (print the top 40 slow queries by time): mysqldumpslow -t 40 -s t /path/to/slow_query_dump.log To clear the slow_log table on the RDS run the following command: mysql -u'<root_user>' -p'<root_pass>' -h <host_domain> mysql -e '' """ import _mysql -
diafygi revised this gist
Dec 16, 2011 . 2 changed files with 13 additions and 13 deletions.There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -1,11 +0,0 @@ This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -1,12 +1,23 @@ """ Queries the slowlog database table maintained by Amazon RDS and outputs it in the normal MySQL slow log text format. Things to change in this script for your own setup: <root_user> to your mysql root user (e.g. "root") <root_pass> to your mysql root password (e.g. "hunter2") <host_domain> to your mysql root password (e.g. "prod-01.w3rfs2.us-east-1.rds.amazonaws.com") Run this command by: python /path/to/slow_query_log_dump.py > /path/to/slow_query_dump.log """ import _mysql root_user = "<root_user>" root_pass = "<root_pass>" host_domain = "<host_domain>" db = _mysql.connect(db="mysql", host=host_domain, user=root_user, passwd=root_pass) db.query("""SELECT * FROM slow_log ORDER BY start_time""") r = db.use_result() -
memonic revised this gist
Dec 15, 2011 . 1 changed file with 11 additions and 0 deletions.There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -0,0 +1,11 @@ #!/bin/bash (/usr/local/bin/db2log | \ mk-query-digest --fingerprints \ --filter '$event->{user} !~ m/^(bi|memonic)$/') 2>&1 | \ mail -s "MySQL slow logs" root # Rotate slow logs. Will move them into the backup table slow_log_backup. If # that table exists it's overwritten with the primary slow log. # So with this strategy we can still access yesterday's slow log by querying # slow_log_backup. mysql mysql -e 'CALL rds_rotate_slow_log' -
memonic created this gist
Dec 15, 2011 .There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -0,0 +1,46 @@ #!/usr/bin/env python """ Queries the slowlog database table maintained by Amazon RDS and outputs it in the normal MySQL slow log text format. """ import _mysql db = _mysql.connect(db="mysql", read_default_file="/root/.my.cnf") db.query("""SELECT * FROM slow_log ORDER BY start_time""") r = db.use_result() print """/usr/sbin/mysqld, Version: 5.1.49-3-log ((Debian)). started with: Tcp port: 3306 Unix socket: /var/run/mysqld/mysqld.sock Time Id Command Argument """ while True: results = r.fetch_row(maxrows=100, how=1) if not results: break for row in results: row['year'] = row['start_time'][2:4] row['month'] = row['start_time'][5:7] row['day'] = row['start_time'][8:10] row['time'] = row['start_time'][11:] hours = int(row['query_time'][0:2]) minutes = int(row['query_time'][3:5]) seconds = int(row['query_time'][6:8]) row['query_time_f'] = hours * 3600 + minutes * 60 + seconds hours = int(row['lock_time'][0:2]) minutes = int(row['lock_time'][3:5]) seconds = int(row['lock_time'][6:8]) row['lock_time_f'] = hours * 3600 + minutes * 60 + seconds if not row['sql_text'].endswith(';'): row['sql_text'] += ';' print '# Time: {year}{month}{day} {time}'.format(**row) print '# User@Host: {user_host}'.format(**row) print '# Query_time: {query_time_f} Lock_time: {lock_time_f} Rows_sent: {rows_sent} Rows_examined: {rows_examined}'.format(**row) print 'use {db};'.format(**row) print row['sql_text']