1,105,534 Community Members

Slow query < 1s?

Member Avatar
Dani
The Queen of DaniWeb
20,571 posts since Feb 2002
Reputation Points: 1,356 [?]
Q&As Helped to Solve: 931 [?]
Skill Endorsements: 204 [?]
Administrator
Featured
Sponsor
 
0
 

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
Deleted Member
 
4
 

@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.

Member Avatar
pritaeas
mod_pritaeas
11,317 posts since Jul 2006
Reputation Points: 1,420 [?]
Q&As Helped to Solve: 1,835 [?]
Skill Endorsements: 156 [?]
Moderator
Featured
Sponsor
 
0
 

MySQL's own solution would be my choice.

Member Avatar
Dani
The Queen of DaniWeb
20,571 posts since Feb 2002
Reputation Points: 1,356 [?]
Q&As Helped to Solve: 931 [?]
Skill Endorsements: 204 [?]
Administrator
Featured
Sponsor
 
0
 

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.

Member Avatar
pritaeas
mod_pritaeas
11,317 posts since Jul 2006
Reputation Points: 1,420 [?]
Q&As Helped to Solve: 1,835 [?]
Skill Endorsements: 156 [?]
Moderator
Featured
Sponsor
 
0
 

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.

Member Avatar
Dani
The Queen of DaniWeb
20,571 posts since Feb 2002
Reputation Points: 1,356 [?]
Q&As Helped to Solve: 931 [?]
Skill Endorsements: 204 [?]
Administrator
Featured
Sponsor
 
0
 

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.

Member Avatar
pritaeas
mod_pritaeas
11,317 posts since Jul 2006
Reputation Points: 1,420 [?]
Q&As Helped to Solve: 1,835 [?]
Skill Endorsements: 156 [?]
Moderator
Featured
Sponsor
 
0
 

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.

You
This article has been dead for over three months: Start a new discussion instead
Post:
Start New Discussion
Tags Related to this Article