MySQL Bugs: #80737: "How MySQL Uses Memory" is myisam centric

Bug #80737	"How MySQL Uses Memory" is myisam centric
Submitted:	14 Mar 2016 21:24	Modified:	16 Mar 2016 11:47
Reporter:	Daniel Price	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.7	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Morgan Tocker request:

Hi Docs Team,

This manual page is written for MyISAM:
http://dev.mysql.com/doc/refman/5.7/en/memory-use.html

I think it’s a useful page, because I’ve noticed a lot of people misunderstand it.

My suggested alternative is:

—————

8.12.5.1 How MySQL Uses Memory

MySQL allocates a number of buffers and caches to improve the performance of database operations. The default configuration is designed to allow a MySQL Server to start on a virtual machine with approximately 512M RAM. By increasing the size of certain settings you can improve the performance of MySQL. Similarly the default configuration can be modified to allow MySQL to start on systems with less memory.

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

* InnoDB Buffer Pool (not currently present; needs mentioning)
Should mention that once cache is too small, InnoDB uses a modified LRU algorithm for eviction.
Should mention that for systems where swapping is occurring and the buffer pool can not be kept in memory, it should be reduced.

* Performance Schema (not currently present; needs mentioning)
Memory is dynamically allocated in 5.7.

* For MyISAM, I would merge "All threads share the MyISAM key buffer;" and the part that starts with "For each MyISAM table that is opened, the index file is opened once” into one section.

This bit is inaccurate:
If an internal in-memory temporary table becomes too large, MySQL handles this automatically by changing the table from in-memory to on-disk format, to be handled by the MyISAM storage engine. (it now defaults innodb.)

This part is incomplete:
Handler structures for all in-use tables are saved in a cache and managed as a FIFO. (does not mention table_definition_cache)

How to repeat:
See above.

Some added details (when memory allocation happens):

InnoDB allocates the full size of the InnoDB buffer pool upon server startup.  From a diagnostic perspective this may be seen as several malloc operations as the buffer pool is partitioned into several partitions via innodb_buffer_pool_instances.  As of MySQL 5.7, the innodb buffer pool size can be dynamically increased or decreased while the server is running.

As of MySQL 5.7, Performance Schema will by default dynamically allocate memory in scalable increments.  Once memory has been allocated to performance schema, it will not be freed until the server is restarted.

Posted by developer:
 
"How MySQL Uses Memory" has been revised for all versions of the MySQL manual:

http://dev.mysql.com/doc/refman/5.7/en/memory-use.html

Thank you for the bug report.