MySQL is not so heavily-instrumented as Oracle. (Back to tuning using ratios rather than the wait interface… sigh…) But it does offer (among other tools) the slow query log. Turn it on (put ‘log_slow_queries‘ in your config file), and all queries that take longer than long_query_time seconds (also set in your config file) will be logged to a slow query log file, along with the time they took to execute. Then you can use mysqldumpslow to analyze the output. You can see, for example, which slow queries are taking the most cumulative time on your server, or are being executed most frequently. (The manual doesn’t say much about it, and mysqldumpslow ––help doesn’t give too much help, but I think mysqldumpslow -s t and mysqldumpslow -s c do that.)
If you use InnoDB, though (as we do), be aware that the “lock time” logged in the slow query log only counts time for table-level locks that are taken at the MySQL top level, not InnoDB locks taken at the storage-engine level. (The logging is done at the “top” level.) So the “lock time” in the log is pretty useless for you. (You can use innodb_lock_monitor for that, but that’s another story…)
September 5, 2007 at 9:00 am |
You’re very right.
Though normal selects do not wait on any Innodb locks anyway.
But anyway we’re working on the patch now which would log time spent on Innodb locks in slow query log as well as time spend waiting in innodb thread queue and few other pieces of info.
September 5, 2007 at 6:17 pm |
Peter – thanks for the good news!
May 28, 2009 at 1:47 pm |
This was great. I am coming from an Oracle Env. One thing I am trying to find is dealing with the error log. Is there a list of errors I can find like withing Oracle I get the ORA messages. Looking to scrap my error log.
Thanks