| Bug #29178 | new memory chunks for heap tables depend on read_buffer_size | ||
|---|---|---|---|
| Submitted: | 18 Jun 2007 14:30 | Modified: | 12 Aug 2010 17:12 |
| Reporter: | Shane Bester (Platinum Quality Contributor) | Email Updates: | |
| Status: | Won't fix | Impact on me: | |
| Category: | MySQL Server: Documentation | Severity: | S3 (Non-critical) |
| Version: | 5.0.44 | OS: | Any |
| Assigned to: | Paul Dubois | CPU Architecture: | Any |
| Tags: | bfsm_2007_11_15, bfsm_2007_12_06, heap, Memory, read_buffer_size | ||
| Triage: | Needs Triage: D2 (Serious) | ||
[18 Jun 2007 14:35]
Shane Bester
My conclusions came from here: The chunk that is allocated on first insert is determined like this: alloc_length = sizeof(HP_PTRS)*i+block->records_in_block* block->recbuffer; alloc_length = 512 * 0 + 168*772 alloc_length = 129696 -------------------------> block->records_in_block is calculated like this: records_in_block = (my_default_record_cache_size - sizeof(HP_PTRS) * HP_MAX_LEVELS) / recbuffer + 1; records_in_block = (131072 - 512 * 4) / 772 + 1 records_in_block = (131072 - 2048) / 773 records_in_block = 168 ------------------------> my_default_record_cache_size can be set via --read_buffer_size option to mysqld or in my.cnf. block->recbuffer is calculated like this: recbuffer = (uint) (reclength + sizeof(byte**) - 1) & ~(sizeof(byte**) - 1); recbuffer = (770 + 4 - 1) & 4294967292 recbuffer = 772 -------------------->
[3 Dec 2007 17:47]
Ingo Strüwing
The analyze from Shane is correct. The size for allocating memory chunks is computed so that it is slightly below read_buffer_size. But it is limited to what is necessary for max_records. I think it is intended behavior because the use of read_buffer_size is very explicit in the formula. However, I do not know, why it is so. I guess there was no better system variable available when this was implemented. That way the chunk size is tunable at least, though not independent from the I/O buffer size. I also don't know if we have a better system variable today.
[12 Aug 2010 17:12]
Paul Dubois
Per discussion with Ingo, Shane, no change needed to manual.
Description: not sure if this is a bug or not - at least I couldn't find where this behaviour is stated in the manual. memory storage engine allocates memory in chunks when needed. the size of the chunks seems to depend on the read_buffer_size variable. See: test 1) mysql> drop table if exists t1; Query OK, 0 rows affected (0.00 sec) mysql> create table t1(c1 varchar(255),c2 varchar(255),c3 varchar(255))engine=memory Query OK, 0 rows affected (0.00 sec) mysql> insert into t1 values ('aaaaaaaaaaa','bbbbbbbbbbb','cccccccccc'); Query OK, 1 row affected (0.00 sec) mysql> show table status\G *************************** 1. row *************************** Name: t1 Engine: MEMORY Version: 10 Row_format: Fixed Rows: 1 Avg_row_length: 769 Data_length: 1677556 <---------- Max_data_length: 16711908 Index_length: 0 Data_free: 0 Auto_increment: NULL Create_time: NULL Update_time: NULL Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: Comment: 1 row in set (0.02 sec) mysql> show global variables like 'read_buffer_size'; +------------------+----------+ | Variable_name | Value | +------------------+----------+ | read_buffer_size | 10481664 | +------------------+----------+ 1 row in set (0.00 sec) test 2) mysql> create table t1(c1 varchar(255),c2 varchar(255),c3 varchar(255))engine=memory Query OK, 0 rows affected (0.00 sec) mysql> insert into t1 values ('aaaaaaaaaaa','bbbbbbbbbbb','cccccccccc'); Query OK, 1 row affected (0.00 sec) mysql> show table status\G *************************** 1. row *************************** Name: t1 Engine: MEMORY Version: 10 Row_format: Fixed Rows: 1 Avg_row_length: 769 Data_length: 129696 <---------- Max_data_length: 16711908 Index_length: 0 Data_free: 0 Auto_increment: NULL Create_time: NULL Update_time: NULL Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec) mysql> show global variables like 'read_buffer_size'; +------------------+--------+ | Variable_name | Value | +------------------+--------+ | read_buffer_size | 131072 | +------------------+--------+ 1 row in set (0.02 sec) How to repeat: start mysqld --read_buffer_size=1048576 drop table if exists t1; create table t1(c1 varchar(255),c2 varchar(255),c3 varchar(255))engine=memory; insert into t1 values ('aaaaaaaaaaa','bbbbbbbbbbb','cccccccccc'); show table status\G show global variables like 'read_buffer_size'; restart mysqld without the --read_buffer_size so default applies drop table if exists t1; create table t1(c1 varchar(255),c2 varchar(255),c3 varchar(255))engine=memory; insert into t1 values ('aaaaaaaaaaa','bbbbbbbbbbb','cccccccccc'); show table status\G show global variables like 'read_buffer_size'; Suggested fix: not sure if this is docs or server issue or both.