Analyzing SQL Queries with Percona Toolkit

Master the tools and techniques of finding the busiest MySQL queries on your database server. Learn how to analyze production workloads, and use your time in the best way for your bottom-line performance optimization.

Upon successful completion of this 8-hour course, delivered over four days (2-hours per day), attendees will:

• Be aware of the numerous causes of slow queries
• Conduct analysis of a server and determine ways to improve query performance
• Collect query logs
• Generate a query digest report
• Interpret a query digest report
• Save query report results for later analysis or trending
• Customize query report with filtering and grouping
• Add query analysis support to your application
• Optimize database buffers for query traffic
• Sample query traffic
• Analyze table usage
• Use custom configuration files
• Automate query analysis processes

Who should attend:

Database Administrators and System Administrators who are responsible for MySQL servers.

Prerequisites:

We recommend prior experience with MySQL. The instructor assumes that every student is familiar with application instrumentation and how to optimize queries in MySQL.

• Linux command-line shell familiarity, common POSIX commands
• Logging on to a remote server with ssh
• Basic MySQL client usage
• Familiarity with MySQL query optimizer reports with EXPLAIN

Hands-on exercises:

The following hands-on labs will be completed by students:

• Locate and inspect query logs
• Install Percona Toolkit
• Use the tool to process logs of different types
• Compare reports and find specific entries
• Query MySQL for information about tables and queries
• Review and annotate query classes
• Search trend reports for queries
• Try visual tools for browsing query reports
• Define filters to produce customized reports
• Define grouping criteria to produce customized reports
• Synthesize new fields for logs
• Add application context to logs
• Warm up MySQL buffers based on query traffic
• Analyze table usage
• Create scheduled jobs for automatic query analysis

Applies to MySQL Versions:

MySQL and Percona Server 5.1 and newer