More DBA job interview questions and answers at
http://dba.fyicenter.com/Interview-Questions/
(Continued from previous question...)
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 usage.
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 allocated.
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 table.
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.
(Continued on next question...)
