29
MySQL Stored Procedures Performance
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 diagnositic 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.
- Explain Command - reveals what the optimizer intends to do
- Explain Extended
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.
- STRAIGHT_JOIN
- USE INDEX(…)
- FORCE INDEX(…)
- 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
- In MySQL Index is the best tool to improve performance, however sometimes it’s better to access the entire table.
- Indexes generally effective when between 5% and 20% of rows are accessed.
- Subqueries need to be satisified by an index or performance will be quite inefficent.
- Overloading indexes with additional columns when key queries only use a few columns can enable improved performance.
Not all indexes are created equal. In the following examples, each advancement improved performance.
- No indexes ()
- Single Index (customer)
- multiple indexes (customer, product)
- concatenated indexes (customer + product)
- covering index (including required columns, customer+product+qty)
Examples of SQL that can’t benefit from Indexes.
- Derived tables - SELECT table in a from clause, creates a temporary table and will never get an index.
- Views with UNIONS/GROUP BY
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.
- Can improved perfomrance when high network overhead.
- Some improvement on parsing.
- Breaking up complex queries may provide benefits.
- SQL is highly optimized for SET operations.
- SP is not optimized for number crunching. Computionally not a fast language.
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.
- Optimize iterations
- Optimize Logic/Testing
- Avoid recursion
Loop Management
- Only perform necessary code within iterations
- LEAVE or CONTINUE when possible in loops
- Test the most likely IF/THEN statements first
- extract if comparisions duplicated to produce nested if’s (within reason)
Some guidelines for Triggers.
- Triggers will have a non-trival overhead for even the simplest trigger.
- Due to FOR EACH ROW only, don’t have expensive SQL in any trigger.
- Very carefully tune SQL in triggers.
- Empty trigger produced 12% overhead.
For more information check out www.quest.com/mysql
