How does MySQL open & close tables?

The cache of open tables can grow to a maximum of table-cache (default 64, changeable with -O table_cache=#). A table is never closed, except when the cache is full and another thread tries to open a table or if one uses 'mysqladmin refresh'.

When the limit is reached, MySQL closes as many tables as possible, until the cache size has been reached or there are no more unused tables. This means that if all tables are in use by some threads, there will be more open tables than the cache limit, but the extra tables will be closed eventually. Tables are closed according to last-used order.

A table is opened (again) for each concurrent access. This means that if one has two threads running on 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 following use of the table takes only one file descriptor.

If MySQL notices that a table is a symbolic linked it will resolve the symlink and use the table it points to instead. This works on all system that supports the realpath() call (At least Linux and Solaris supports realpath()! On system that doesn't supports realpath() you should not use the symlink and the table at the same time! The tables will be inconsistent after any update to the tables.

MySQL doesn't support linking of databases by default. Things will work fine as long as you don't make a symbolic link between databases. The following shows a case that DOES NOT work:

db2->db1
db1/

If you really need this you have to change in mysys/mf_format.c:

    if (!lstat(to,&stat_buff))		/* Check if it's a symbolic link */
      if (S_ISLNK(stat_buff.st_mode) && realpath(to,buff))

to
      if (realpath(to,buff))

What are the drawbacks of creating possibly thousands of tables in a database?

Each table is actually three files. If you have many files in a directory open, close and create will be slow. If you also do selects on many different tables there will be a little overhead because when the table cache is full, for every table that has to be opened another has to be closed. One can make the overhead smaller by making the table cache larger.

Go to the first, previous, next, last section, table of contents.