We are using MySQL 5.6 on rhel 7. Planning to migrate to version 8.xx soon.

We have a mysql database of size 4TB. It has around 3000 tables with few tables having size of 200-300GB. Sometimes queries takes longer than 60 to 100 seconds on these bigger tables.

Sometimes due to less search conditions, select query executes for longer time but sometimes regular queries also take long times.

It could be due to MySQL was busy doing something else .. we tried using Oracle MySQL enterprises monitor for few days. It shows long running queries but it doesn't have time stamp when a query was started. So we are not able to identify what other queries were running when one query took long time. MySQL monitor didn't report any issues too with configuration or settings..

how to identify what caused a table lock or what caused delayed response ?

  • The is no Version MySQL 5.8.nn. 8.nn is the next version with General Available dates. Caution, if the version has not been General Available for 6 months, you should avoid it, unless you are willing to report bugs, wait on corrective updates. Always review change logs before installing, please. 5.7 is stable and has been for many months with HIGH VOLUME servers. – Wilson Hauck Apr 26 '19 at 13:54
  • 8.0.11 (GA) was released 2018-04-19. There have been a few ugly bugs in corner situations, but the new Data Dictionary seems to be solid. (If the new Dada Dictionary had a bug, it could be a serious disaster for dataset integrity.) – Rick James Apr 27 '19 at 14:34
  • There are essentially no "table locks" for DML statements (INSERT, etc), just for DDL (ALTER, etc). Hopefully, you are not doing big DDL in production? – Rick James Apr 27 '19 at 14:37

The best way to do this is to enable the slow query log. It will log in a file queries that took more than X number of seconds to perform, as well as, the lock time it took.

You need to edit your /etc/my.cnf and add the following lines (you'll need to restart your mysql service for the changes to take effect):

slow_query_log = 1
slow_query_log_file = /var/log/slowmysql.log
long_query_time = 2

A small explanation of each:

  1. The first line enables the log.
  2. The second tells the server where to store it.
  3. The third one tells it how many seconds to wait in order to log the query. I recommend you start with a high number (say 10 seconds), and once your queries get optimized, reduce that number
  4. The fourth line will enable you to log every query not properly using an index. Although this might log quick running queries, it will also help you spot which queries are not truly optimized using the correct indexes.
  • Thank you.. Will try this – Sravan Apr 26 '19 at 22:25
  • Don't turn on log-queries-not-using-indexes, it just clutters the log with fast queries on tiny tables. – Rick James Apr 27 '19 at 14:35
  • After a while, use pt-query-digest or mysqldumpslow -s t to summarize the log. – Rick James Apr 27 '19 at 14:38

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.