How MySQL Uses Memory
The server allocates memory for many kinds of caches and buffers :
Buffer Pool
The memory area where InnoDB caches data and indexes. The default value is 128MB. The value is set using the innodb_buffer_pool_size.
mysql> show variables like 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+
You can also see the status of the InnoDB buffer pool and memory usage.
mysql> show engine innodb status ;
Check memory usage under the section BUFFER POOL AND MEMORY
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137428992
Dictionary memory allocated 422961
Buffer pool size 8192
Free buffers 7901
Database pages 291
Old database pages 0
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 250, created 41, written 69
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 291, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
On a dedicated server, you can set the pool size to 80% of the machine's physical memory. The buffer pool size can be set dynamically as of MySQL 5.7.5 without restarting the server.
MySQL Performance Schema
Is a feature for monitoring MySQL server execution at a low level. The Performance Schema dynamically allocates memory incrementally, scaling memory use to actual server load, instead of allocating required memory during server startup. Once memory is allocated it is not free until the server is restared.
Client Connections Threads
Each thread that is used to manage client connections uses some thread-specific space :
Thread Stack
The stack for each thread. The default siaze is 192KB(256KB for 64-bit systems). The stack is large enough for normal operations. If the thread stack is too small, it limits the complexity of the SQL statements that the server can handle, the recursion depth of stored procedures.
mysql> show variables where variable_name = 'thread_stack';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| thread_stack | 196608 |
+---------------+--------+
Connection and Result Buffers
Each client thread is associated with a connection buffer and result buffer. Both begin with a given size by net_buffer_length but are dynamically enlarged to max_allowed_packet bytes as needed.
mysql> show variables like 'net_buffer_length';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| net_buffer_length | 16384 |
+-------------------+-------+
MyISAM Key Buffer
The index blocks for the MyISAM tables are buffered and shared by all thread. The key_buffer_size is the size of the buffer used for index blocks.
Temporary Tables
Most temporary tables memory-based hash tables. If an internal in-memory table becomes too large, MySQL handles this automatically by changing the table from in-memory disk format, handled by the storage engine defined by internal_tmp_disk_storage_engine.
mysql> show variables like 'internal_tmp_disk_storage_engine';
+----------------------------------+--------+
| Variable_name | Value |
+----------------------------------+--------+
| internal_tmp_disk_storage_engine | InnoDB |
+----------------------------------+--------+
Memory Storage Engine
Tables explicitly created with CREATE TABLE, only the max_heap_table_size determines how large the table is permitted to grow and there is no conversion to on-disk format.
Grant Tables
The grant tables store information about MySQL user accounts and privileges they have. The server loads a copy of the grant tables in memory for fast access. The memory is not released and can only be freed by FLUSH PRIVILEGES.
Open Tables
A tabke cache holds descriptors of open tables. For frequently used tables, keeping the descriptors in memory avoids having to open the again and again.
mysqladmin status shows the number of open tables.
root@ubuntu:/var/lib/mysql# mysqladmin -u joseph -p status
Enter password:
Uptime: 52024 Threads: 2 Questions: 243 Slow queries: 0 Opens: 257 Flush tables: 1 Open tables: 98 Queries per second avg: 0.004
or from the global system variables
mysql> show global status like 'Open_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables | 99 |
+---------------+-------+