Database Performance Analyzer (DPA) 2024.4 Tuning Advisors for MySQL and Percona MySQL

Table Tuning Advisor for MySQL/Percona MySQL

Every database has inefficient queries—some perform many reads (either logical or physical) but retrieve a relatively small number of rows. In other words, they work hard for a small return. This inefficiency can result in higher input/output, longer wait times, greater amounts of blocking, and increased resource contention.

Tuning inefficient queries can be difficult, and many questions tend to surface as part of the process. Questions such as:

• Should you tune the query​? Add a new index? Or maybe add columns to an existing index?
• Plans are complex and challenging to analyze; which steps should you consider?
• Which predicates in the plans are causing inefficient data access and high amounts of reads?
• Are there recommendations you can use as a starting point?
• Are there other inefficient queries that access the same table and could be affected by indexing decisions?
• How many indexes currently exist on the table and how are they structured?
• How transactional is the data in the table in the form of data churn (inserts, deletes, and sometimes updates)?

Table Tuning Advisors can help resolve these questions.

 Table Tuning Advisor analyzes expensive queries to help identify tables with inefficient workloads running against them— regardless of the reason. For each table, the advisor page displays aggregated information about the table and the inefficient queries. You can use this information to make informed decisions about database performance optimization opportunities and to weigh the potential costs and benefits of adding an index.

Navigation

There are two ways to get to the advisor page:

•  After clicking into an instance, a "Tuning" super-tab appears near the top of the page. Clicking on this will take you to a page combining the Query, Index, and Table Tuning Advisors.
  
  

• Once drilled into a specific Query Performance Analysis (QPA) page​, scroll down to the Table Tuning Advisors section. This section summarizes the advice aggregated to the table level and includes links to the advisor detail page.
  

Advisor page layout

The Table Tuning Advisor page is densely packed with key intelligence related to the object selected. It has three main areas:

• Inefficient SQL—a list of queries accessing the table ranked by relative workload
• SQL and Plan Details—SQL and Plan details for the selected query
• Table and Index Information—current table information, existing indexes on the table, and the table's columns

Table Tuning Advisor example

Let's assume we are being proactive and want to tune something to have a significant impact. At a summary level, the tuning tab shows the tables with inefficient queries and ranks them based on inefficient workloads. The list includes an aggregated view of wait time for each table and the number of queries with inefficient plan steps on the table. These are prime opportunities for tuning.

Are there any recommendations to use as a starting point? 
Clicking on the "orders" table takes us to the Table Tuning Advisor page, which details inefficient queries accessing the table. This page pulls together what you need to know about the table regarding inefficient usage patterns, statistical information, the design of current indexes, and more.

Which steps in the plans are inefficient? 
DPA uses a proprietary algorithm to find inefficient plan steps and causes of issues. Inefficient "producer" steps (Ex. full table/index scans) read data to be processed later by subsequent consumer plan steps. While consumer steps (Ex. sorts) can have a high cost, they are usually affected by a preceding producer step reading too much data. DPA can point out the inefficient producer plan steps as the focus of tuning efforts.

In the image above, DPA identified two plans used, as well as an inefficient step in each plan:

1. SEQ SCAN—Step 7: A sequential scan of the "customer" table. Notice the predicate value shows a similar comparison which can be inefficient. The sequential scan caused an examination of 150K rows for a matching phone number.

Getting this information via manual plan analysis would probably take hours. Plan analysis is challenging, so let Table Tuning Advisor help get you to a great starting place.

Are there other inefficient queries with access to this table? 
The left pane of the Table Tuning Advisor page shows other inefficient queries, ranked by relative workload, accessing the "customer" table. Pay attention to the queries near the top of this list because they cause more workload against the table. Conversely, you should not spend as much time on queries near the bottom with small relative workloads. Sometimes, a slight adjustment like an added or modified index can make a big difference for many inefficient queries.

How many indexes exist on the table, and how are they designed?
Toward the bottom of the Table Tuning Advisor page, you’ll find the current indexes and their columns along with information about statistics and usage. Other table and index metadata is displayed after a real-time query to reflect the most up-to-date information. This is important for several reasons:

• Is the data churn for the table high? If so, insert/update/delete activity is high, and a new index could cause more harm than good.
• Could an existing index containing the column called out in the predicate help? Can the index be modified to benefit this query versus creating a new index?
• Were optimizer statistics generated recently? If not, and churn is high, updating the statistics for the table may be an excellent first step.
• Are indexes bloated? If so, and if scans are performing against them, check the health of the vacuuming process.
  
  
  

What did you find? Our development team uses DPA to help our code perform well. When using the Table Tuning Advisor, it pointed them to a problematic set of tables. Within a few hours, they tuned the queries with a simple rewrite and saved hours of database time every night during the cleaning process. 

If you find interesting stories in your environment, let us know by leaving comments on this blog post. We would love to hear feedback about the following:

• Does this improve your workflow when tuning a query? How much time does it save you?
• Are there tuning questions the page does not answer?
• Is all of the assembled data important to you when tuning?