Working with MySQL Hints

A number of developers look at SQL tuning hints as a way to performance tune queries. Where the best way to tune SQL code is not to use hints. Hints add high maintenance code and try to influence the Cost Based Optimizer which can have negative impacts in the future. What if the data distributions change or the optimizer adds new features in the future? The best way to tune SQL code is to look at how the database server environment can be optimized so the optimizer can make the best decisions as possible. However, if you cannot get the optimizer to make the right decision a SQL hint may be required but it should usually be the choice of last resort.

Examples:
SELECT * FROM mytab IGNORE INDEX (col1_idx)
WHERE col1='A' AND col2='B';

SELECT * FROM mytab USE INDEX (col1_idx,col2_idx)
WHERE col1='A' AND col2='B' AND col3=3;

Here are some of MySQL SQL Tuning Hints:

• STRAIGHT_JOIN
• IGNORE INDEX [FOR JOIN]
• FORCE INDEX [FOR JOIN]
• USE INDEX [FOR JOIN]
• SQL_NO_CACHE
• SQL_CACHE
• The HIGH_PRIORITY
• The LOW_PRIORITY
• INSERT DELAYED
• INSERT LOW_PRIORITY
• INSERT DELAYED (MyISAM, MEMORY, and ARCHIVE tables)
• SQL_BUFFER_RESULT
• SQL_BIG_RESULT
• The SQL_BIG_RESULT
• SQL_SMALL_RESULT

MySQL Scalability and Performance Directions

MySQL is continuing to grow at a rapid pace in the market place. Continued high growth areas for MySQL continue to include the web application, gaming and embedded systems space. For small and medium sized OLTP environments MySQL continues to increase in popularity. MySQL can have incredible scalability as long as it scales horizontally. However with today's hardware adding more CPU and memory capability, it is important that MySQL be able to grow to much larger sizes through vertical scalability.

There are a lot of upcoming changes in the MySQL world that are going to add significant performance, scalability and feature/functionality. The key areas I will be talking about include:

• MySQL Version 6 - (currently in alpha) will add significant performance, availability, scalability and online features.
• Falcon - (currently in alpha) New storage engine with MySQL 6 that will add increased scalability, availability and online features to replace the InnoDB storage engine.
  
• Maria - (currently in alpha) New crash safe storage engine that will add increased scalability, availability and transaction safe features to replace the MYISAM storage engine.
• InnoDB Plugin - (close to end of beta) Oracle offers an InnoDB plug-in that provides a number of important performance, scalability, online functionality and reduced maintenance features.
  

Each storage engine: Falcon, Maria and the InnoDB Plugin provide a lot of important enhancements that customers are going to want. It will be important to watch the maturity of each of these storage engines as they move from alpha and release candidate versions to general availability (production).

Performance Tuning Knowledge is Important for the New DBA

As MySQL continues to expand in the market place, I am seeing a common mistake new DBAs make. They are waiting too long to develop their performance tuning skills. Whenever I work with new DBAs I always tell them it is important to focus on three areas:

1. Understand the architecture and how things work. It is not good to guess.
   
2. Get very good at back up and recovery if you want to keep your job.
3. Get very good at performance tuning if you want to enjoy your job.
   

It is important that in your future as a DBA you plan on taking a Performance Tuning class. The reasons are:

• Most databases get bigger (add more data) and have more users over time. So performance tuning will always be a factor in managing your systems as your systems grow.
• It's important to take a performance tuning class sooner than later. Reason is you could be making decisions that will have very negative performance ramifications in the future, if you do not understand performance factors. Future scalability is impacted by performance decisions made today.
  
• Understanding performance tuning makes you a better DBA. Being good at performance tuning requires you understand how a database server works in more detail. This understanding will help with diagnostics and trouble shooting for everything you do as a DBA.

Being good at performance tuning helps you be a more proactive DBA versus being a reactive DBA. Reactive DBAs are always putting out fires and fixing things after the fact. If you want to enjoy your work as a DBA focus on being a proactive DBA.

MySQL Profiling: SQL Tuning

MySQL provides a number of different tools for tuning SQL statements. Some of the key SQL tuning tools include:

• EXPLAIN - Displays execution plans generated by the MySQL Cost Based Optimizer.
• Status Variables - Contains statistics on SQL run time activity.
• Profiling - Contains run time statistics on each phase in the execution of individual SQL statements.
  

Main Phases in Processing a SQL Statement

Profiling allows access to very detailed run time statistics on each phase of processing a SQL command. The main phases include:

• Parsing the SQL statement.
• Generating an execution plan.
• Performing an execution and fetch.
• Cleaning up resources.

Profiling Benefits

Profiling provides the following benefits:

• Ability to understand time spent in each phase of processing a SQL command.
• Displays if the OS performed a disk read because the requested block is not in memory.
• The number of waits that occurred while the thread processed the SQL command.
• Disk activity generated by the SQL statement.
• Resources allocated for processing the SQL command.
• Approximations of time required to process each step of the SQL statement.
• Comparision of execution times relative to other SQL statements.
• If you're into the source code, each C function and line number where it is executed for each step of processing the SQL statement.
  

Profiling Resources in MySQL

The profiling resources available beginning in MySQL 5.1.28 include:

• SHOW PROFILE command.
• SHOW PROFILES command.
• Information_schema.profiling table.

Turning Profiling ON

Profiling is turned OFF (0) by default. Profiling is turned ON (1) at the session level. The PROFILING_HISTORY_SIZE parameter is used to determine how many statements are kept in the history. The default is 15 statements. The query id will change for each SQL statement executed in a session.

mysql> SET PROFILING=1;


SHOW PROFILES

The SHOW PROFILES command will display the query id, the duration and the SQL command executed.

mysql> SHOW PROFILES;
+----------+------------+------------------------------------------------------
| Query_ID | Duration | Query
+----------+------------+--------------------------------------------------------
| 1 | 0.00073900 | SELECT Co.Name, Ci.Name, Ci.Population FROM CountryList Co, CityList Ci WHERE Co.Code = Ci.CountryCode AND Ci.Population > 8000000 |
| 2 | 0.00086100 | SELECT query_id, seq,state, duration, source_function FROM information_schema.profiling WHERE query_id = 1 |
...


SHOW PROFILE

SHOW PROFILE [type [, type] ... ]
 [FOR QUERY n]
 [LIMIT row_count [OFFSET offset]]

type:
 ALL
| BLOCK IO
| CONTEXT SWITCHES
| CPU
| IPC
| MEMORY
| PAGE FAULTS
| SOURCE
| SWAPS

mysql> SHOW PROFILE FOR QUERY 3;
+--------------------+----------+
| Status | Duration |
+--------------------+----------+
| starting | 0.000091 |
| freeing items | 0.000040 |
| logging slow query | 0.000007 |
| cleaning up | 0.000007 |
+--------------------+----------+
4 rows in set (0.00 sec)

INFORMATION_SCHEMA.PROFILING

There are much more interersting queries you can get out of this database object, but this simple query fits in the blog eaily and provides a hint of detailed information available.

mysql> SELECT query_id, duration, state
FROM information_schema.profiling WHERE query_id = 8;
+----------+----------+--------------------+
| query_id | duration | state |
+----------+----------+--------------------+
| 8 | 0.000100 | starting |
| 8 | 0.000068 | Opening tables |
| 8 | 0.000008 | System lock |
| 8 | 0.000013 | Table lock |
| 8 | 0.000028 | init |
| 8 | 0.000015 | optimizing |
| 8 | 0.000016 | statistics |
| 8 | 0.000017 | preparing |
| 8 | 0.001850 | executing |
| 8 | 0.000162 | Sending data |
| 8 | 0.000008 | end |
| 8 | 0.000006 | query end |
| 8 | 0.000069 | freeing items |
| 8 | 0.000034 | removing tmp table |
| 8 | 0.000007 | closing tables |
| 8 | 0.000005 | logging slow query |
| 8 | 0.000007 | cleaning up |
+----------+----------+--------------------+
17 rows in set (0.01 sec)

Thoughts on Profiling

The profiling feature needs to mature more in MySQL, yet this early implementation does provide information that can be useful for evaluating SQL statements. Combining profile information, thread information and session state information together provides different perspectives into the evaluation of SQL processing.