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?

Recommended Answers

All 6 Replies

Member Avatar for 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.

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.

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.

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.

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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.