Troubleshooting MySQL Performance with Sveta Smirnova

Within the Troubleshooting MySQL Performance seminar students learn how to identify and solve problems within a MySQL database. This seminar also provides an in depth insight into problems concerning performance tuning. Errors and misbehavior of database applications can lie in wait for DBAs or Database Developers. Things get even worse if an application stalls or is inherited and so you are not completely familiar with it.

In this class you learn how to identify such a situation and how to solve it effectively.

Sveta will start from basics, working towards more advanced cases that DBAs would usually need years of experience to identify or solve.

This seminar covers performance problems in MySQL applications. You learn why issues happen in the first place and how to react to and solve situations including:

• Query runs slow
• Query execution time increases during life of the application
• MySQL server uses more and more system resources
• Slave cannot catch up the master

Solving problems is critical skill for DBA or Database Developer. At the same time effectively finding source of issues is not the same task as creating good database from scratch. And while there are a lot of information about latter, former is not greatly presented. This seminar offers knowledge which is result of years of practical experience and enlightens methods which are not obvious for not experienced user.

While there are a lot of resources about High Performance best practices, they don't focus on troubleshooting techniques. If you hit a problem you need to run through all this information looking for a tool or technique which can help you in this specific case. This seminar brings all the key troubleshooting techniques together for you in one place.

A Live Virtual Class (LVC) is exclusively for registered students; unregistered individuals may not view an LVC at any time. Registered students must view the class from the country listed in the registration form. Unauthorized recording, copying, or transmission of LVC content may not be made.

Audience

• Database Administrators
• Developer

Course Topics How to test safely

• Overview of principles which help you to test without risk to breaking or slowing down a production environment

Problems repeatable with single thread (single or few queries)

• Issues which can be noticed at development stage
• Query which runs slowly from the start
• Queries which suddenly start to run slowly after months of successful working
• Tips on how to solve such problems

Reproducible problems repeatable with multiple threads running same time, e.g. concurrency issues

• Why issue is not repeatable with single thread
• How storage engine affects this (InnoDB and MyISAM)
• Effects of internal locking
• Resource fights
• How to troubleshoot
• Tips for how to solve

Effect of MySQL options

• When option takes effect
• When and how to tune performance-related options

Effects of operating system resources

• Operating system limits
• RAM
• Disk
• CPU
• Network
• We will discuss aspects such as:
• MySQL server uses a lot of resources
• MySQL server does not use necessary resources

Replication

• Specific replication issues
• How common queries are affected in replicated environment
• Why master runs slow
• Why slave cannot catch up with the master

What to do if issue happen on production only and cannot be restored in development environment

• Debugging techniques
• How to test safely
• What to do if methods we discussed earlier do not help
• Specific MySQL tools which can help to debug production issues

Overview of tools, which can help you to troubleshoot effectively. I will discuss all tools which I use in my job with focus on those included in MySQL distribution

• Must-have tools
• Internal MySQL tools
• Command-line tools
• GUI tools

Course Objectives

• Understand how to find a source of database error
• Understand effect of concurrency
• Learn how to use tools to find particular kind of error effectively
• Solve replication errors
• Solve production high load issues
• Test without risk
• Understand why a query can run slow, particularly concentrating on:
• Query itself, how it is written
• Indexes
• Data
• Understand the effect of concurrency
• Understand the effect of options
• See how MySQL server interacts with the operating system
• Solve replication slowdowns
• Determine how to choose the best tool
• Learn how to measure performance