"Alternate methods for finding problem query.": chapter 5 of "Methods for searching errors in SQL application" just published

Translation of chapter 5 which describes alternate methods for searching problem query just published. It starts like:



Chapter 5. Alternate methods for finding problem query.



I already wrote about using of general query log requires resources.
Part of the problem can be solved if use new feature of MySQL 5.1:
online logging which allows to turn general query log to on or to off
without stopping the MySQL server.


Unfortunately this doesn't always work: you can have old
version of MySQL server which has not such a feature, general query log
can contain very large amount of information, so you can just miss the
problem in thousands of correct queries, you can have some other own
reason.



But what to do if you can not use general query log?


One of the methods is write log files using your application.
Add a code which will write queries which your application sends to
MySQL to the log file.


With this method you can tune how and what to write to the log
file. Would be good if you will write return value and error messages
in the same log file.



Method #9: tune your application in such a way so it will write queries to log files itself.



Yet another method is using proxy which will get queries and write them to the file.


One of most preffered variants is MySQL Proxy, because this is
scriptable proxy made for working with MySQL server. It uses MySQL
client-server protocol. You can write programs for MySQL Proxy using
Lua programming language.



Below you can see example how to write general query log using MySQL Proxy:


...


 Rest of the chapter is here


Comments:

i miss a tool which simply shows what indexes would be optimal for a query.
often i have so complex queries thats hard to find out which part of it makes to whole query slow and needs to be optimized.

Posted by ekle on August 26, 2010 at 09:13 AM MSD #

If you use complex query it is hard to determine which index should be added without human interference. Use EXPLAIN, there is good chapter in MySQL user manual about how to use it at http://dev.mysql.com/doc/refman/5.1/en/using-explain.html

Posted by Sveta Smirnova on August 26, 2010 at 12:50 PM MSD #

MSSQL has such a function based on a query log, so it should be possible to do this with MySQL too.

Posted by ekle on August 28, 2010 at 03:09 PM MSD #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Working blog of Sveta Smirnova - MySQL Senior Principal Support Engineer working in Bugs Analysis Support Group

Search

Categories
Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today