Category: UC2006

Tuning MySQL5 SQL And Stored Procedures

by Guy Harrison

[edit] Notes by Sheeri Kritzer

Un-tuned SQL or stored procedures often fail to scale as table volumes increase, inefficiency increases exponentially with size.

Tune SQL/stored procedures and then buy new hardware.

use EXPLAIN to help optimize queries. Also use the slow query log.

EXPLAIN EXTENDED shows sql that was actually used — ie, optimizer may rewrite query, so it’s a neat tool.

you can always give optimizer hints, but they’re not recommended — keep checking them as your app grows — STRAIGHT_JOIN, FORCE INDEX, USE INDEX, and one other one.

SHOW STATUS gives you status variables. innodb_buffer_pool_read_requests and innodb_data_read will show how much data is being read from the buffer pool vs. data.

Index isn’t always used, if more than 20% or so of rows, MySQL will use a full table scan. There’s usually a range where MySQL will choose a full table scan when an index is more appropriate, or vice versa, so that’s when you’d use hints. Hey, nobody’s perfect!

think indexes — joining tables of non-trivial size Subqueries ( [NOT] EXISTS, [NOT] IN) in WHERE clause. Use index to avoid a sort, use “covering” indexes.

Establish the best set of multi-column indexes along with singular indexes.

Derived tables (subqueries in FROM cause) can’t use an index. VIEWs with UNION or GROUP BY also can’t use index — all these use TEMPTABLE view algorithm. (temp table created, and then reads from temp table).

Sorts can be improved by increasing memory (sort_buffer_size) or using an index.

Use procedures to:

Performance of SQL within a stored routine that dominates the performance. When SQL is tuned, optimize the routine using traditional techniques:

Recursion:

TRIGGERS non-trivial (12% at least) to even simplest trigger. No trigger should EVER contain expensive SQL, because they are done for each row.

Quest free software for MySQL — http://www.quest.com/mysql/

[edit] Notes by Ronald Bradford

Another one of the sessions at the MySQL Users Conference I attended was Tuning MySQL5 SQL and Stored Procedures by Guy Harrison from Quest Software. A global company with 6000+ customers.

Guy has written a number of Oracle Performance Books in the past. His work now is on the “Spotlight” product family - Database diagnostic tools converting data to graphical representations. For these products, MySQL 5 and InnoDB only is necessary, simply due to accessing the right internal information for presentation. There are Freeware MySQL product downloads.

In this presentation he stated, nothing he was talking about specifically was relatively new. He did make quite a funny comment, “He is now seeking refugee status in the MySQL Community”.

Guy is author of O’Reilly “MySQL Stored Procedure Programming” Book. I managed to get for free at the conference from the MySQL Quiz night, in addition to a shirt and cap for stumping a Guru.

His talk were on tools and techniques for tuning MySQL.

mysql> explain extended select ...;
mysql> show warnings \G
Shows what the optimizer actually did. In this example, An IN was converted to EXISTS

There were 4 ways to provide optimizer hints.

1. STRAIGHT_JOIN

2. USE INDEX(…)

3. FORCE INDEX(…)

4. IGNORE INDEX(…)

In addition to the Show Query Log, there are Innodb specific commands, two in particular.

show status like 'innodb%'
* innodb_buffer_pool_read_requests
* innodb_data_read

Indexing and the optimizer

Not all indexes are created equal. In the following examples, each advancement improved performance.

Examples of SQL that can’t benefit from Indexes.

A comment from the audience was that derived tables can be of a benefit to a correlated sub-query in specific examples.

Stored Procedures provided a mixed blessing for performance.

A written routine to calculate prime numbers provided the following performance (from most expensive to least) MySQL SP, Oracle SP,PHP,Perl,Java ,VB.NET ,C (gcc). This showed an example that was excessively inefficient. On the other hand, if the program is network dependent (e.g. access a million rows, perform some statisical aggretation). Comparatively the same between Java and SP locally, but much better in a remote host mode.

Performance of SQL in a SP will dominate overall performance. Where SQL is tuned, goto tried and proven traditional optimisation techniques.

Loop Management

Some guidelines for Triggers.

Retrieved from "http://forge.mysql.com/wiki/Tuning_MySQL5_SQL_And_Stored_Procedures"

This page has been accessed 15,196 times. This page was last modified 21:44, 24 January 2011.

Find

Browse
MySQLForge
Main Page
Current events
Recent changes
Random page
Help
Edit
Edit this page
Editing help
This page
Discuss this page
Post a comment
Printable version
Context
Page history
What links here
Related changes
My pages
Special pages
New pages
File list
Statistics
Bug reports
More...