Very detailed and excellent explanation. Kudos. \

Regarding ‘Contention at the Table Cache level’, you’ve stated that ‘this impacts the number of file descriptors used by MySQL‘ meaning that the same table opened in multiple instances will have different file descriptors for the same file.

I tried to recreate the above scenario by executing a ‘select * from tablename’ on a table which has 18 crore records in one session and another select query ‘select * from tablename order by id desc limit 1000’ on the same table in another session. I monitored the system calls using strace. From the strace output, we found out that session2 used the same file descriptor as session1 to retrieve data while the query in session1 was still running. Please note that the value of table_open_cache_instances was 16.

If a table is open, another session accessing the same table should open the table .ibd file using a new file descriptor. But that is not the case here.

When we say increasing table_open_cache_instances reduces contention, what contention do we exactly mean here? table level? In source code, it is mentioned that by increasing instances we can make sure to lock ‘m_lock’ instead of having to lock the ‘LOCK_OPEN’. From my understanding, this lock is unlocked in a very small span of time and don’t think it causes any overhead.

Please share your insights on this. Also, what do you think should be the ideal value for table_open_cache_instances?