Mysql如何使用內存

　　每個session使用的內存：

　　Each thread that is used to manage client connections uses some thread-specific space. The following list indicates these and which variables control their size:

　　A stack (default 192KB, variable thread_stack)

　　A connection buffer (variable net_buffer_length)

　　A result buffer (variable net_buffer_length)

　　The connection buffer and result buffer both begin with a size given by net_buffer_length but are dynamically enlarged up to max_allowed_packet bytes as needed. The result buffer shrinks to net_buffer_length after each SQL statement. While a statement is running, a copy of the current statement string is also allocated.

　　每個session都有自己獨占的內存空間，存儲特定的信息，內存分配采用malloc(),free()，內存管理采用FIFO，應該也是增強版的先進先出，沒什么區別。

　　key_buffer_size：

　　索引緩沖區的大小，只用來緩存Myisam的索引，Myisam表的數據是不會放到這個緩存里面。

　　我們可以通過Key_read_requests和Key_read的比例來判斷設置是否合理，key_read/Key_read_requests比例要小越好，至少是在1:1000以上。

　　對表進行順序掃描的請求將分配一個緩存區(變量read_buffer_size)。

　　當按任意順序讀取行時(例如，按照排序順序)，將分配一個隨機讀 緩存區(變量read_rnd_buffer_size)以避免硬盤搜索。

　　Myisam表文件是每個session都會打開，多個session就是打開多次，而索引只需要打開一次。

　　For each MyISAM table that is opened, the index file is opened once; 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 are allocated (where N is the maximum row length, not counting BLOB columns). A BLOB column requires five to eight bytes plus the length of the BLOB data. The MyISAM storage engine maintains one extra row buffer for internal use.

　　tmp_table_size

　　If an internal heap table exceeds the size of tmp_table_size, MySQL handles this automatically by changing the in-memory heap table to a disk-based MyISAM table as necessary.--這個說的內部表和內存表(memory)是不一樣的，內存表是不會轉化的,文檔中說得太模糊，基本上我已經暈了。

　　看看這位老兄的描述，清楚多了。

　　The lower value from tmp_table_size and use max_heap_table_size was used to define when temporary table was converted from memory to disk.

　　Also tmp_table_size was used to limit the size of the temporary table - no matter whether it’s in memory or on disk.

　　And max_heap_table_size was also used to limit the size of explicitly created HEAP tables.

　　This variable determines the maximum size for a temporary table in memory. If the table becomes too large, a MYISAM table is created on disk. Try to avoid temporary tables by optimizing the queries where possible, but where this is not possible, try to ensure temporary tables are always stored in memory. Watching the processlist for queries with temporary tables that take too long to resolve can give you an early warning that tmp_table_size needs to be upped. Be aware that memory is also allocated per-thread. An example where upping this worked for more was a server where I upped this from 32MB (the default) to 64MB with immediate effect. The quicker resolution of queries resulted in less threads being active at any one time, with all-round benefits for the server, and available memory

原文轉自：http://blogread.cn/it/article/65