Skip to content

Instantly share code, notes, and snippets.

@morphizer

morphizer/process_slow_logs.py

Created October 17, 2012 03:32
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save morphizer/3903557 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save morphizer/3903557 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. morphizer created this gist Oct 17, 2012.
    32 changes: 32 additions & 0 deletions process_slow_logs.py
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,32 @@
    #!/usr/bin/env python

    # Processes the files in the incoming directory for query analysis in anemometer
    # Supply the db user/pass/host

    import glob, os, shutil, sys
    from subprocess import Popen

    db_user = "anemometer"
    db_pass = "password"
    db_host = "mysqlserver"

    if not os.path.exists('incoming/'):
    print "Incoming directory doesn't exist"
    sys.exit(1)

    incoming_logs = glob.glob('incoming/*')

    for log in incoming_logs:
    # import each log file into anemometer mysql database using pt-query-digest command
    if log.endswith('-slow.log'):
    hostname = os.path.basename(log[:-9]) #get filename only, and trim the -slow.log part to get the hostname
    else:
    if os.path.exists('rejected/'):
    shutil.move(log,'rejected/')
    break # it's not the log file we're looking for
    else:
    print "Log file name incorrect: %s" % log
    sys.exit(1)
    digest = Popen(["pt-query-digest", "--user=%s" % db_user, "--password=%s" % db_pass, "--review", "h=%s,D=slow_query_log,t=global_query_review" % db_host, "--review-history", "h=%s,D=slow_query_log,t=global_query_review_history" % db_host, "--no-report", "--limit=0%", "--filter= $event->{Bytes} = length($event->{arg}) and $event->{hostname}=\"%s\"" % hostname, "%s" % log ] )
    digest.wait() # wait for it to process - can take a while sometimes
    os.remove(log)
    14 changes: 14 additions & 0 deletions send_to_anemometer.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,14 @@
    #!/bin/bash
    # Send slow logs to anemometer for analysis

    # Turn off the log
    mysql -u root -p -Be "SET GLOBAL slow_query_log = 'OFF';"

    # Move the file for transfering
    mv /data/mysql/$(hostname)-slow.log{,-transfer}
    scp /data/mysql/$(hostname)-slow.log-transfer anemometer@host:incoming/$(hostname)-slow.log
    rm -f /data/mysql/$(hostname)-slow.log-transfer

    # Turn on the log again
    mysql -u root -p -Be "SET GLOBAL slow_query_log = 'ON';"
    mysql -u root -p -Be "FLUSH LOGS;"