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:
None 
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:30] Shane Bester
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.
[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.