Question :
After running the following queries:
CREATE TABLE mem_test (i int NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=MEMORY;
INSERT INTO mem_test () VALUES ();
SELECT table_rows,avg_row_length,data_length,index_length FROM INFORMATION_SCHEMA.TABLES WHERE table_name='mem_test';
I get this in MySQL 5.7.28:
+------------+----------------+-------------+--------------+
| table_rows | avg_row_length | data_length | index_length |
+------------+----------------+-------------+--------------+
| 1 | 8 | 126992 | 126984 |
+------------+----------------+-------------+--------------+
and this in MariaDB 10.4.10:
+------------+----------------+-------------+--------------+
| table_rows | avg_row_length | data_length | index_length |
+------------+----------------+-------------+--------------+
| 1 | 5 | 1677712 | 2093064 |
+------------+----------------+-------------+--------------+
My table will never grow anywhere close to the size of these pre-allocated buffers, so how can I get MySQL and MariaDB to waste less RAM? Or aren’t they actually using what this query indicates??
Additional comparisons, for reference:
+-------+--------+------------+----------------+-------------+--------------+
| DB | Engine | table_rows | avg_row_length | data_length | index_length |
+-------+--------+------------+----------------+-------------+--------------+
| MySQL | MEMORY | 1 | 8 | 126992 | 126984 |
| Maria | MEMORY | 1 | 5 | 1677712 | 2093064 |
| Both | InnoDB | 1 | 16384 | 16384 | 0 |
| Both | MyISAM | 1 | 7 | 7 | 2048 |
+-------+--------+------------+----------------+-------------+--------------+
I found a note on the MySQL forums that “memory tables grow by ‘read_buffer_size’ bytes when they need extending” (https://forums.mysql.com/read.php?92,405702,417606#msg-417606), but my numbers don’t agree: I have read_buffer_size = 131072
in MySQL and 2097152
in MariaDB.
Answer :
Although data_length
and index_length
aren’t exact multiples of read_buffer_size
, they are based on it. So adjusting read_buffer_size
in my.cnf is the solution (thanks @Wilson Hauck), as Shane Bester pointed out on the MySQL forums:
memory tables grow by ‘read_buffer_size’ bytes when they need extending (https://forums.mysql.com/read.php?92,405702,417606#msg-417606)
Based on performance tests by Peter Zaitsev in 2007 (https://www.percona.com/blog/2007/09/17/mysql-what-read_buffer_size-value-is-optimal/), the default 128K setting is generally best, and using smaller values brings very little penalty.