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
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
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.
MySQL - Drawbacks to Creating Large Numbers of Tables in
the Same Database
If you have many files in a directory, open, close, and
create operations will be slow. If you execute SELECT
statements on many different tables, there will be a
little overhead when the table cache is full, because
for every table that has to be opened, another must be
closed. You can reduce this overhead by making the table
MySQL - Why So Many Open tables?
When you run mysqladmin status, you'll see something
Uptime: 426 Running threads: 1 Questions: 11082 Reloads:
1 Open tables: 12
This can be somewhat perplexing if you only have 6
MySQL is multithreaded, so it may have many queries on
the same table simultaneously. To minimize the problem
with two threads having different states on the same
file, the table is opened independently by each
concurrent thread. This takes some memory and one extra
file descriptor for the data file. The index file
descriptor is shared between all threads.
How MySQL Uses Memory ?
The list below indicates some of the ways that the
mysqld server uses memory. Where applicable, the name of
the server variable relevant to the memory use is given:
The key buffer (variable key_buffer_size) is shared by
all threads; Other buffers used by the server are
allocated as needed.
Each connection uses some thread-specific space: A stack
(default 64K, variable thread_stack), a connection
buffer (variable net_buffer_length), and a result buffer
(variable net_buffer_length). The connection buffer and
result buffer are dynamically enlarged up to
max_allowed_packet when needed. When a query is running,
a copy of the current query string is also allocated.
All threads share the same base memory.
Only the compressed ISAM / MyISAM tables are memory
mapped. This is because the 32-bit memory space of 4GB
is not large enough for most big tables. When systems
with a 64-bit address space become more common we may
add general support for memory mapping.
Each request doing a sequential scan over a table
allocates a read buffer (variable record_buffer).
All joins are done in one pass, and most joins can be
done without even using a temporary table. Most
temporary tables are memory-based (HEAP) tables.
Temporary tables with a big record length (calculated as
the sum of all column lengths) or that contain BLOB
columns are stored on disk. One problem in MySQL
versions before Version 3.23.2 is that if a HEAP table
exceeds the size of tmp_table_size, you get the error
The table tbl_name is full. In newer versions this is
handled by automatically changing the in-memory (HEAP)
table to a disk-based (MyISAM) table as necessary. To
work around this problem, you can increase the temporary
table size by setting the tmp_table_size option to
mysqld, or by setting the SQL option SQL_BIG_TABLES in
the client program.
In MySQL Version 3.20, the maximum size of the temporary
table was record_buffer*16, so if you are using this
version, you have to increase the value of record_buffer.
You can also start mysqld with the --big-tables option
to always store temporary tables on disk. However, this
will affect the speed of many complicated queries.
Most requests doing a sort allocates a sort buffer and
0-2 temporary files depending on the result set size.
Almost all parsing and calculating is done in a local
memory store. No memory overhead is needed for small
items and the normal slow memory allocation and freeing
is avoided. Memory is allocated only for unexpectedly
large strings (this is done with malloc() and free()).
Each index file is opened once and the data file is
opened once for each concurrently running thread. For
each concurrent thread, a table structure, column
structures for each column, and a buffer of size 3 * n
is allocated (where n is the maximum row length, not
counting BLOB columns). A BLOB uses 5 to 8 bytes plus
the length of the BLOB data. The ISAM/MyISAM table
handlers will use one extra row buffer for internal
For each table having BLOB columns, a buffer is enlarged
dynamically to read in larger BLOB values. If you scan a
table, a buffer as large as the largest BLOB value is
Table handlers for all in-use tables are saved in a
cache and managed as a FIFO. Normally the cache has 64
entries. If a table has been used by two running threads
at the same time, the cache contains two entries for the
A mysqladmin flush-tables command closes all tables that
are not in use and marks all in-use tables to be closed
when the currently executing thread finishes. This will
effectively free most in-use memory. ps and other system
status programs may report that mysqld uses a lot of
memory. This may be caused by thread-stacks on different
memory addresses. For example, the Solaris version of ps
counts the unused memory between stacks as used memory.
You can verify this by checking available swap with swap
-s. We have tested mysqld with commercial memory-leakage
detectors, so there should be no memory leaks.
Page Numbers : 1