Oracle Open World 2018 D1: Top Five MySQL Query Tuning Tips

Yesterday (22.10.2018) I participated to the Janis Griffin’s session about “Top Five Query Tuning Tips” at #OOW2018. Janis is Senior DBA /Performance Evangelist for SolarWinds and Ace Director. She is specialized in Performance Tuning.

She introduces her session by speaking about Challenges of Tuning. “Tuning takes time.”, “You cannot give enough power if SQL is ineficient”, “You therefore have to monitor wait time”. It sounds basic telling that it doesn’t worth adding CPU or memory when your SQL Statements have bad execution plan or are simply ineficient but that a common reflex that I already observed by customers.

But Tuning is hard, you do not always know where to start with (which statement you have to tune at first). It requires expertise in many areas, technical but also business. Of course tuning takes time and it’s not always the priority of the editor companies. Finally where to stop when you start tuning a statement ?

Let’s start with the tips…

1. Monitor Wait Time and understand the total time a Query spends in Database. MysQL helps by providing Wait Events and Thread States. Of course starting with MySQL 5.6 the Performance_Schema has been greatly improved and has 32 new tables in version 5.7. You can also access to the SYS Schema which is now provided by default with about 100 views.

2. Review the execution plan by using “explain”, “explain extended“, “explain FORMAT=JSON“, “Optimizer Trace” or “MySQL Workbench“. She also gave us some tips such as “Avoiding using table aliases since they don’t translate in plan”. “Optimizer trace” available since version 5.6.3+ can be used with:

set optimizer trace ="enabled=on"

3. Gather object information. Have a look on table definition and find if it’s really a table or if it’s a view. Get size of the table by using

mysqlshow --status database {table} {column}

Then Examine Columns in Where Clause and review selected Column and especially the usage of ‘*’ and scalar column. Have also a look on existing indexes (if multi-column, know the left leading column). Make sure the Optimizer can use the index, indeed functions on indexed columns can turn off index and look for implicit conversions. Her tip is to check keys and constraints, because they help creating better execution plan.

4. Find the driving table. You need to know the size of the actual data sets of each step:

• In Joins (Right, Left, Outer)
• What are the filtering predicates
• When is each filtering predicate applied

But also compare size of the final result set with data examined. The goal is to reduce rows examined.

You also have to check if you are using the best indexes. Keep in mind that adding indexes is not always the right thing to do since you have to consider insert, update and delete operations. Consider also usage of Covering and Partial indexes.

5. Engineer Out the Stupid. Look for performance inhibitors such as:

• Cursor or row by row processing
• Parallel query processing. Not always bad but have a look on this blog from Alex Rubin named “increasing slow query performance with parallel query execution“
• Hard-coded hints
• Nested views
• Abuse of Wild Cards(*) or No Where Clause
• Code-based SQL Generation (e.g. PHP generator, LINQ; nHibernate)
• implicit data conversions
• Non-sargable /scalar functions (eg. Select… where upper(first_name) = ‘JANIS’

Finally you can have a look on Janis Best practices with MySQL Tuning here.