More DBA job interview questions and answers at
http://dba.fyicenter.com/Interview-Questions/
(Continued from previous question...)
How MySQL Opens and Closes Tables
table_cache, max_connections, and max_tmp_tables affect the maximum number of files the server keeps open. If you increase
one or both of these values, you may run up against a limit imposed by your operating system on the per-process number of
open file descriptors. However, you can increase the limit on many systems. Consult your OS documentation to find out how to
do this, because the method for changing the limit varies widely from system to system.
table_cache is related to max_connections. For example, for 200 concurrent running connections, you should have a table
cache of at least 200 * n, where n is the maximum number of tables in a join.
The cache of open tables can grow to a maximum of table_cache (default 64; this can be changed with the -O table_cache=#
option to mysqld). A table is never closed, except when the cache is full and another thread tries to open a table or if you
use mysqladmin refresh or mysqladmin flush-tables.
When the table cache fills up, the server uses the following procedure to locate a cache entry to use:
Tables that are not currently in use are released, in least-recently-used order.
If the cache is full and no tables can be released, but a new table needs to be opened, the cache is temporarily extended as
necessary.
If the cache is in a temporarily-extended state and a table goes from in-use to not-in-use state, the table is closed and
released from the cache.
A table is opened for each concurrent access. This means that if you have two threads accessing the same table or access the
table twice in the same query (with AS) the table needs to be opened twice. The first open of any table takes two file
descriptors; each additional use of the table takes only one file descriptor. The extra descriptor for the first open is
used for the index file; this descriptor is shared among all threads.
You can check if your table cache is too small by checking the mysqld variable opened_tables. If this is quite big, even if
you haven't done a lot of FLUSH TABLES, you should increase your table cache.
(Continued on next question...)
