Skip to content

README.md

Latest commit

 

History

History
58 lines (44 loc) · 4.11 KB

README.md

File metadata and controls

58 lines (44 loc) · 4.11 KB

mysql-slow-query-log-visualizer

This tool will help you find SQL queries that are taking to long to complete which are reducing your server's performance and in some cases can be a cause of the server's SQL service to crash because these queries are holding on too much RAM.

Use Online

Use this app online, no installation needed: https://shoulders.github.io/mysql-slow-query-log-visualizer/

Privacy Information

  • The data never leaves your computer as processing is all handled in the browser (Client Side).
  • To prove this, once you have loaded the page, disconnect from the internet before analysing your log.

Getting the Slow Log file

  • You need to make sure slow query logging is enabled and configured as required.
  • On Ubuntu the file is located here: /var/lib/mysql/slow-query.log, some OS might have it here /var/log/mysqld-slow.log.

Usage

There is a demo log file called demo_slow-query.log in the GitHub Repo for testing with and is based on MariaDB.

  • Upload your slow-query.log by dragging it in to the browser making sure you release it in to the dropzone.
    • If you need to load very large files, you will need to use Chrome, and launch it with the flags --js-flags="--max-old-space-size=5000" in order to allocate 5GB to chrome and avoid memory errors due to huge load of things in memory.
    • Another useful command if your file is large, is to split. Your can split your file into several files using this command : split --bytes=512M --additional-suffix=.log slow-query.log.
  • A Global Chart of all of the queries plotted against time will now be displayed along with a Query Table showing the actual queries themselves.
    • You can change the timescale of the chart by using the dropdown option called Group By.
  • Filter the results by clicking once on the chart to set a start time and again to set an end time.
    • This will now generate a second chart called Working Chart showing the filtered results.
    • The table of the queries is also filtered to reflect the results showing in the Working Chart.
    • You can now select the start and end time in either chart.
    • Not all chart types support filtering so the Working Chart and Query Table will not be present. The Aggregated graph types do not have a data drill down function (i.e. Workingchart) because of the nature of the data used.
    • If you choose an end date that is before your start date, then your start date will be used for both values.
    • If you double click a bar it will show only the results of that segment in the working chart.
    • While choosing your start time and end time, you can use mix and match using both charts to choose those times.
  • Analyse the fitlered results further by using the search and sort by options available in the table of queries.
    • The Tables column headings allow you to sort by acending or descending
    • The queries in the table can be further filtered by using the Filter input box. This will not change the results shown in the Working Chart.
    • The queries in the table will always reflect the results in the Working Chart unless you have just intitally loaded the page.
    • You can copy the queries using the Copy Button for each query.
    • The filter will return any query that has the filter term in any of the columns (Visible Fields).

In the displayed queries, some columns may be of interest:

  • query string : original query string executed, and being slow. Note that this query will display parameters as is, even if you used prepared statements
  • query with stripped where clauses : some transformations are applied on origin query string in order to remove hardcoded values (in order to facilitate identification of same queries with different criteria values)
  • query pattern occurences : Number of time query with stripped where clauses occurences are found First number is the number of occurences

Notes

  • The script:
    • Assumes your log file has \n line endings.
    • Uses local time and not time based on timestamps so the dates and times will be what you expect.
    • Has had extensive comments allowing easy reading of what the code does.