In mysql how to identify what caused a table lock 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 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
log-queries-not-using-indexes


A small explanation of each:


The first line enables the log.
The second tells the server where to store it.
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
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.