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.

| improve this question | | | | |

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.

| 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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