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 ?