How does MySQL use memory ?

• The key_buffer is shared by all threads, the rest are allocated when needed.
• Each connection uses some thread space, a stack (64K) and a connection buffer (variable net_buffer_length).
• All threads share the same base memory.
• Nothing is memmapped yet (except compressed tables but that's another story). This is because the 32-bit memory space of 4GB is not large enough for most large tables. When we get a system with 64-bit address space we may add general support for memmaping.
• When starting mysqld one can specify a key buffer. This will buffer all indexes in all tables on a FIFO basis (variable key_buffer).
• Each request doing a sequential scan over a table allocates a read buffer (variable record_buffer).
• Most requests doing a sort allocates a sortbuffer and one or two temporary files. See section Where MySQL stores temporary files.
• 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 recordlength (= sum of all column lengths) or that contains BLOBs are stored on disk. One current problem is that if the HEAP table exceeds the size of tmp_table_size, one will get the error: 'The table ### is full'. In the future we will fix this by automatically changing the in memory (HEAP) table to a disk based (NISAM) table if needed. To go around this problem one can increase the -O tmp_table_size=# option to mysqld or use the SQL option SQL_BIG_TABLES. See section SET OPTION syntax.. In MySQL 3.20 the maximum size of the temporary table was recordbuffer*16, so if you are using this version you have to increase recordbuffer. You can also start mysqld with --big-tables to always store temporary tables on disk, but this will affect the speed of all complicated queries.
• Almost all memory used when parsing and calculating is done in a local memory store. No memory overhead is needed for small items and the normal slow memory allocation/freeing is avoided. Memory is only allocated for unexpectedly large strings (this is done with malloc/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 that has the size of 3 * (maximum row length not counting BLOBs) is allocated. A BLOB uses 5 to 8 bytes +length of blob data.
• For each table having BLOBs, a buffer is enlarged dynamically to read in larger BLOBs. If one scans a table there will be a allocated buffer as large as the largest BLOB.
• All used tables are saved in a cache and managed as a FIFO. Normally the cache is 64 tables. If a table has been used by two running threads at the same time, there will be two entries of the table in the cache.
• A mysqladmin refresh closes all tables that are not in use and marks all used tables to be closed when the running thread finishes. This will effectively free most used memory. All log files are also closed and reopened.

When running mysqld ps and other programs may report that it uses a lot of memory. This may be caused by thread-stacks on different memory addresses. For example, the Solaris ps calculates 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 not be any memory leaks.

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