14

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
1

1 Answer 1

Reset to default
21

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
8
  • My host is using version 5.0.95-log, any solution for that?
    – adwiv
    May 25, 2013 at 18:31
  • 4
    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, 2013 at 0:53
  • @adwiv Are you on a shared host with that much traffic??
    – Wesley
    May 26, 2013 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, 2013 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, 2013 at 4:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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