How Mysql server use memory

上一篇 / 下一篇 2008-04-25 16:06:44 / 个人分类：LAMP

The following list indicates some of the ways that themysqldserver uses memory. Where applicable, the name of the system variable relevant to the memory use is given:

s3P^;m#_-X!{0

The key buffer (variablekey_buffer_size) is shared by all threads; other buffers used by the server are allocated as needed. SeeSection 7.5.2, “Tuning Server Parameters”.
Each connection uses some thread-specific space:
- A stack (default 64KB before MySQL 4.0.10 and 192KB thereafter, variablethread_stack)
- A connection buffer (variablenet_buffer_length)
- A result buffer (variablenet_buffer_length)
木铎校园 BBS 社区F{-f@X
The connection buffer and result buffer are dynamically enlarged up tomax_allowed_packetwhen needed. While a query is running, a copy of the current query string is also allocated.
I;Ss*TBMzd!uRL0
All threads share the same base memory.
When a thread is no longer needed, the memory allocated to it is released and returned to the system unless the thread goes back into the thread cache. In that case, the memory remains allocated.
Only compressedISAMandMyISAMtables 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 that performs a sequential scan of a table allocates aread buffer(variableread_buffer_size).
When reading rows in an arbitrary sequence (for example, following a sort), arandom-read buffer(variableread_rnd_buffer_size) may be allocated in order to avoid disk seeks.
All joins are executed in a single pass, and most joins can be done without even using a temporary table. Most temporary tables are memory-based hash tables. Temporary tables with a large row length (calculated as the sum of all column lengths) or that containBLOBcolumns are stored on disk.One problem before MySQL 3.23.2 is that if an internal in-memory heap table exceeds the size oftmp_table_size, the errorThe tabletbl_nameis fulloccurs. From 3.23.2 on, this is handled automatically by changing the in-memory heap table to a disk-basedMyISAMtable as necessary. To work around this problem for older servers, you can increase the temporary table size by setting thetmp_table_sizeoption tomysqld, or by setting the SQL optionSQL_BIG_TABLESin the client program. SeeSection 13.5.3, “SETSyntax”.In MySQL 3.20, the maximum size of the temporary table isrecord_buffer*16; if you are using this version, you have to increase the value ofrecord_buffer. You can also startmysqldwith the--big-tablesoption to always store temporary tables on disk. However, this affects the speed of many complicated queries.
Most requests that perform a sort allocate a sort buffer and zero to two temporary files depending on the result set size. SeeSection A.4.4, “Where MySQL Stores Temporary Files”.
Almost all parsing and calculating is done in a local memory store. No memory overhead is needed for small items, so the normal slow memory allocation and freeing is avoided. Memory is allocated only for unexpectedly large strings. This is done withmalloc()andfree().
For eachMyISAMorISAMtable that is opened, the 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 size3 ×Nare allocated (whereNis the maximum row length, not countingBLOBcolumns). ABLOBcolumn requires five to eight bytes plus the length of theBLOBdata. TheMyISAMandISAMstorage engines maintain one extra row buffer for internal use.
For each table havingBLOBcolumns, a buffer is enlarged dynamically to read in largerBLOBvalues. If you scan a table, a buffer as large as the largestBLOBvalue is allocated.
Handler structures for all in-use tables are saved in a cache and managed as a FIFO. By default, 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. SeeSection 7.4.8, “How MySQL Opens and Closes Tables”.
AFLUSH TABLESstatement ormysqladmin flush-tablescommand closes all tables that are not in use at once and marks all in-use tables to be closed when the currently executing thread finishes. This effectively frees most in-use memory.FLUSH TABLESdoes not return until all tables have been closed.

psand other system status programs may report thatmysqlduses a lot of memory. This may be caused by thread stacks on different memory addresses. For example, the Solaris version ofpscounts the unused memory between stacks as used memory. You can verify this by checking available swap withswap -s. We testmysqldwith several memory-leakage detectors (both commercial and Open Source), so there should be no memory leaks.

关于作者

平凡的香草

How Mysql server use memory

相关阅读:

关于作者