Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a heavily loaded LAMP system serving about 40 requests per second and doing about 300 mysql queries per second. Here is the output of mysqladmin status

Uptime: 5051  Threads: 1  Questions: 1418500  Slow queries: 0  Opens: 456  
Flush tables: 1  Open tables: 450  Queries per second avg: 280.835

However, the global status show about 5-10% table lock contention.

So, i want to figure out the queries that are taking longer time.

Is it possible to set the slow query log to less than one second? For my queries, even execution time of 100ms is high.

share|improve this question
add comment

migrated from serverfault.com May 26 '13 at 3:26

This question came from our site for professional system and network administrators.

1 Answer

up vote 6 down vote accepted

Just set long_query_time to the value you want. If you want to log queries that take longer than 100ms, you would set it to 0.1.

The ability to set this option to a value less than 1 was added in MySQL 5.1.21.

share|improve this answer
    
My host is using version 5.0.95-log, any solution for that? –  adwiv May 25 '13 at 18:31
3  
Yes, become the system administrator and run your server yourself. We're generally unable to help in situations where you are not the server administrator. –  Michael Hampton May 26 '13 at 0:53
    
@adwiv Are you on a shared host with that much traffic?? –  Wesley May 26 '13 at 1:42
    
@WesleyDavid No its a standard VPS with about 2GB RAM on centos 5.8. I have done the yum update and yum upgrade but it won't upgrade the mysql server version. I was not sure if installing from some other repository will break any dependencies. Anyways I am going to try the upgrade anyway. Thanks. –  adwiv May 26 '13 at 4:25
    
You must be running EL5, then. EL6 came with MySQL 5.1, or you can get a third party repository such as remi to use MySQL 5.5 or higher. –  Michael Hampton May 26 '13 at 4:32
show 3 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.