Slow query < 1s?

[Dani]

Is there any way to set the long_query_time for the slow query log to less than < 1s? We consider anything over 0.5s something wrong. Or can you recommend any MySQL monitoring software that can handle a super high load without a super amount of storage space?

[LastMitch]

@Dani

Is there any way to set the long_query_time for the slow query log to less than < 1s?

You want ot set the long_query_time to less than .5 second? I never seen less than .5 second before.

I have a feeling you done this already but I'm not sure:

You need to edit this file called: my.cnf

You can edit:

long_query_time=0.4;
log-slow-queries = /var/log/mysql/mysql-slow-query.log

For sql you should use time in sec:

set long_query_time=0.4;

I got that from here:

http://www.openlogic.com/wazi/bid/195905/Tips-and-Tricks-to-Optimize-MySQL

Regardin about a MySQL monitoring software. You can try this:

http://www.quest.com/spotlight-on-mysql/

It's a freeware unless you want to buy a shareware.

[pritaeas]

MySQL's own solution would be my choice.

[Dani]

According to MySQL documentation, long_query_time has to be a value between 1 and 10, inclusive. I was wondering if anyone knew of a way to track queries in the sub-1s range??

Unfortunately that MySQL solution you posted, pritaeas, is for Java and .NET apps only. I use PHP.

[pritaeas]

Oh crap. Didn't remember that... sorry. The only other thing I've used is Zend. Not sure what it's called now, but it tracks everything.

[Dani]

There's a company that has advertised with us in the past called New Relic but slow query monitoring is only available with the pro version and not sure I want to pay for something.

[pritaeas]

Well, then no Zend. There must be free or open tools available to check the slow query log files. Never used any other than grep personally.

From the MySQL manual:

5.2.5. The Slow Query Log

The slow query log consists of SQL statements that took more than long_query_time seconds to execute and required at least min_examined_row_limit rows to be examined. The minimum and default values of long_query_time are 0 and 10, respectively. The value can be specified to a resolution of microseconds. For logging to a file, times are written including the microseconds part. For logging to tables, only integer times are written; the microseconds part is ignored.