| Bug #30293 | memory tables with btree indexes .. very weird situation | ||
|---|---|---|---|
| Submitted: | 8 Aug 2007 7:00 | Modified: | 18 Aug 2007 8:48 |
| Reporter: | Jeff C. | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Memory storage engine | Severity: | S3 (Non-critical) |
| Version: | 5.1.20, 5.0 , 4.1 BK | OS: | Any |
| Assigned to: | Timothy Smith | CPU Architecture: | Any |
| Tags: | BTREE, heap, max_heap_table_size, Memory | ||
[8 Aug 2007 7:03]
Jeff C.
Applies to 5.0.45 as well.. (root@localhost) [test]> select version(); +----------------------+ | version() | +----------------------+ | 5.0.45-community-log | +----------------------+ 1 row in set (0.00 sec)
[8 Aug 2007 7:32]
Sveta Smirnova
Thank you for the report. Verified as described.
[18 Aug 2007 8:48]
Timothy Smith
Hello. I'm flagging this as "Not a bug", because it's a consequence of the extra storage requirements for a btree index. See the formula at the bottom of the MEMORY engine description in the manual: http://dev.mysql.com/doc/refman/5.0/en/memory-storage-engine.html "The memory needed for one row in a MEMORY table is calculated using the following expression: SUM_OVER_ALL_BTREE_KEYS(max_length_of_key + sizeof(char*) × 4) + SUM_OVER_ALL_HASH_KEYS(sizeof(char*) × 2) + ALIGN(length_of_row+1, sizeof(char*))" In particular, note that the memory needed to store the btree key depends on the max length of the key field, whereas the size of the hash key is constant relative to the length of the field. Thanks to senpablo on freenode #mysql-dev for bringing this up.
Description: Strange behavior between hash/btree indexes in memory tables. When using: ... primary key(name)) engine=memory; Max_data_length: 945590785 When using: ... primary key using btree(name)) engine=memory; Max_data_length: 477054815 I believe this to be a bug. Example below: How to repeat: (root@localhost) [test]> select version(); +-----------------+ | version() | +-----------------+ | 5.1.20-beta-log | +-----------------+ 1 row in set (0.00 sec) (root@localhost) [test]> show variables like 'max_heap%'; +---------------------+------------+ | Variable_name | Value | +---------------------+------------+ | max_heap_table_size | 1048576000 | +---------------------+------------+ 1 row in set (0.00 sec) (root@localhost) [test]> create table testing (name varchar(100) not null , primary key(name)) engine=memory; Query OK, 0 rows affected (0.02 sec) (root@localhost) [test]> show table status like 'testing'\G *************************** 1. row *************************** Name: testing Engine: MEMORY Version: 10 Row_format: Fixed Rows: 0 Avg_row_length: 101 Data_length: 0 Max_data_length: 945590785 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) (root@localhost) [test]> drop table testing; Query OK, 0 rows affected (0.00 sec) (root@localhost) [test]> create table testing (name varchar(100) not null , primary key using btree(name)) engine=memory; Query OK, 0 rows affected (0.03 sec) (root@localhost) [test]> show table status like 'testing'\G *************************** 1. row *************************** Name: testing Engine: MEMORY Version: 10 Row_format: Fixed Rows: 0 Avg_row_length: 101 Data_length: 0 Max_data_length: 477054815 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) Suggested fix: n/a