Re: Optimize Table and Flush Table contention on different databases

[Aftab Khan]

You can determine whether your table cache is too small by checking the mysqld status variable Opened_tables, which indicates the number of table-opening operations since the server started:

mysql> SHOW GLOBAL STATUS LIKE 'Opened_tables';

If the value is very large or increases rapidly, even when you have not issued many FLUSH TABLES statements, you should increase the table cache size.

Questions

1) What MySQL version are you using?
3) Provide us FLUSH TABLES command you have used.
Note: if you use this command
mysql> flush tables;
This will try to close all tables across all databases.

Also you need to optimize this SQL
mysql> delete from table_a where month(dt_status) = 12;

Add index on dt_status (if its not indexed) and try using following sql instead
delete from table_a where dt_status BETWEEN 'dATE1' AND 'dATE2'