Bug #25007 memory tables with dynamic rows format
Submitted: 12 Dec 2006 13:05 Modified: 21 Mar 2011 4:01
Reporter: Roberto Spadim (Basic Quality Contributor) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Memory storage engine Severity:S4 (Feature request)
Version:5.0, 5.1, 5.5 OS:Any
Assigned to: CPU Architecture:Any
Tags: gc, Memory Engine

[12 Dec 2006 13:05] Roberto Spadim 
Description:
MEMORY tables use a fixed-length row storage format.

ok, could we implement an dynamic style? just when create table with FORMAT=DYNAMIC ??? with this BLOB and TEXT could be implemented, today i'm creating myisam tables into an tmpfs filesystem

thankx guys!

today this table (in how to repeat) get 9000 bytes per row, with dynamic it could get 255 bytes or less i think that it's good for big memory tables.. thanks again!

How to repeat:
CREATE TABLE channels_current (
channel_id INT(10) unsigned NOT NULL DEFAULT '0',
alarm_on ENUM('Y','N') NOT NULL DEFAULT 'N',
alarm_log_type ENUM('estabilized','current') NOT NULL DEFAULT 'current',
alarm_log_write_type ENUM('time','fixed_time','read','chane') NOT NULL DEFAULT 'read',
alarm_log_write_value DOUBLE NOT NULL DEFAULT 0,
need_zero ENUM('0','1') NOT NULL DEFAULT 0,
counter BIGINT(20) unsigned NOT NULL DEFAULT 0,
value_sum DOUBLE NOT NULL DEFAULT 0,
cur_deriv DOUBLE NOT NULL DEFAULT 0,
zero_timer DOUBLE NOT NULL DEFAULT 0,
last_read_time DOUBLE NOT NULL DEFAULT 0,
last_read VARCHAR(255) NOT NULL DEFAULT '',
last_saved_read_time DOUBLE NOT NULL DEFAULT 0,
last_saved_read VARCHAR(255) NOT NULL DEFAULT '',
last_estabilized_read_time DOUBLE NOT NULL DEFAULT 0,
last_estabilized_read VARCHAR(255) NOT NULL DEFAULT '',
last_read_ref_time DOUBLE NOT NULL DEFAULT 0,
last_read_ref VARCHAR(255) NOT NULL DEFAULT '',
last_saved_read_ref_time DOUBLE NOT NULL DEFAULT 0,
last_saved_read_ref VARCHAR(255) NOT NULL DEFAULT '',
last_estabilized_read_ref_time DOUBLE NOT NULL DEFAULT 0,
last_estabilized_read_ref VARCHAR(255) NOT NULL DEFAULT '' ,
last_read_time_1 DOUBLE NOT NULL DEFAULT 0,
last_read_1 VARCHAR(255) NOT NULL DEFAULT '',
last_saved_read_time_1 DOUBLE NOT NULL DEFAULT 0,
last_saved_read_1 VARCHAR(255) NOT NULL DEFAULT '',
last_estabilized_read_time_1 DOUBLE NOT NULL DEFAULT 0,
last_estabilized_read_1 VARCHAR(255) NOT NULL DEFAULT '',
last_read_ref_time_1 DOUBLE NOT NULL DEFAULT 0,
last_read_ref_1 VARCHAR(255) NOT NULL DEFAULT '',
last_saved_read_ref_time_1 DOUBLE NOT NULL DEFAULT 0,
last_saved_read_ref_1 VARCHAR(255) NOT NULL DEFAULT '',
last_estabilized_read_ref_time_1 DOUBLE NOT NULL DEFAULT 0,
last_estabilized_read_ref_1 VARCHAR(255) NOT NULL DEFAULT '' ,
last_read_time_2 DOUBLE NOT NULL DEFAULT 0,
last_read_2 VARCHAR(255) NOT NULL DEFAULT '',
last_saved_read_time_2 DOUBLE NOT NULL DEFAULT 0,
last_saved_read_2 VARCHAR(255) NOT NULL DEFAULT '',
last_estabilized_read_time_2 DOUBLE NOT NULL DEFAULT 0,
last_estabilized_read_2 VARCHAR(255) NOT NULL DEFAULT '',
last_read_ref_time_2 DOUBLE NOT NULL DEFAULT 0,
last_read_ref_2 VARCHAR(255) NOT NULL DEFAULT '',
last_saved_read_ref_time_2 DOUBLE NOT NULL DEFAULT 0,
last_saved_read_ref_2 VARCHAR(255) NOT NULL DEFAULT '',
last_estabilized_read_ref_time_2 DOUBLE NOT NULL DEFAULT 0,
last_estabilized_read_ref_2 VARCHAR(255) NOT NULL DEFAULT '' ,
last_read_time_3 DOUBLE NOT NULL DEFAULT 0,
last_read_3 VARCHAR(255) NOT NULL DEFAULT '',
last_saved_read_time_3 DOUBLE NOT NULL DEFAULT 0,
last_saved_read_3 VARCHAR(255) NOT NULL DEFAULT '',
last_estabilized_read_time_3 DOUBLE NOT NULL DEFAULT 0,
last_estabilized_read_3 VARCHAR(255) NOT NULL DEFAULT '',
last_read_ref_time_3 DOUBLE NOT NULL DEFAULT 0,
last_read_ref_3 VARCHAR(255) NOT NULL DEFAULT '',
last_saved_read_ref_time_3 DOUBLE NOT NULL DEFAULT 0,
last_saved_read_ref_3 VARCHAR(255) NOT NULL DEFAULT '',
last_estabilized_read_ref_time_3 DOUBLE NOT NULL DEFAULT 0,
last_estabilized_read_ref_3 VARCHAR(255) NOT NULL DEFAULT '' ,
last_read_time_4 DOUBLE NOT NULL DEFAULT 0,
last_read_4 VARCHAR(255) NOT NULL DEFAULT '',
last_saved_read_time_4 DOUBLE NOT NULL DEFAULT 0,
last_saved_read_4 VARCHAR(255) NOT NULL DEFAULT '',
last_estabilized_read_time_4 DOUBLE NOT NULL DEFAULT 0,
last_estabilized_read_4 VARCHAR(255) NOT NULL DEFAULT '',
last_read_ref_time_4 DOUBLE NOT NULL DEFAULT 0,
last_read_ref_4 VARCHAR(255) NOT NULL DEFAULT '',
last_saved_read_ref_time_4 DOUBLE NOT NULL DEFAULT 0,
last_saved_read_ref_4 VARCHAR(255) NOT NULL DEFAULT '',
last_estabilized_read_ref_time_4 DOUBLE NOT NULL DEFAULT 0,
last_estabilized_read_ref_4 VARCHAR(255) NOT NULL DEFAULT '' ,
last_read_time_5 DOUBLE NOT NULL DEFAULT 0,
last_read_5 VARCHAR(255) NOT NULL DEFAULT '',
last_saved_read_time_5 DOUBLE NOT NULL DEFAULT 0,
last_saved_read_5 VARCHAR(255) NOT NULL DEFAULT '',
last_estabilized_read_time_5 DOUBLE NOT NULL DEFAULT 0,
last_estabilized_read_5 VARCHAR(255) NOT NULL DEFAULT '',
last_read_ref_time_5 DOUBLE NOT NULL DEFAULT 0,
last_read_ref_5 VARCHAR(255) NOT NULL DEFAULT '',
last_saved_read_ref_time_5 DOUBLE NOT NULL DEFAULT 0,
last_saved_read_ref_5 VARCHAR(255) NOT NULL DEFAULT '',
last_estabilized_read_ref_time_5 DOUBLE NOT NULL DEFAULT 0,
last_estabilized_read_ref_5 VARCHAR(255) NOT NULL DEFAULT '' ,
PRIMARY KEY (channel_id)
)  ENGINE=MEMORY ROW_FORMAT=DYNAMIC
[17 Dec 2006 20:07] Roberto Spadim 
if you do:

select * from channels_current
procedure analyse(1,1)

we get TINYTEXT values on column optimal_fieldtype
buttttt MEMORY tables don't allow TINYTEXT (yet)
[9 Mar 2007 13:14] Valeriy Kravchuk 
Thank you for a reasonable feature request.
[17 Apr 2008 15:38] Igor Chernyshev 
This feature has been implemented as a patch for 5.0.45. See http://code.google.com/p/mysql-heap-dynamic-rows
[25 Sep 2009 19:10] Lukas Kummer 
Hello, I have two questiones: 1. In which version the bug was correcte? I tried the new 6.0 alpha and noticed lower memory usage.
2. Why is the memory usage for varchar rows still so high? Compared to the MyIsam Engine memory Usage of the Heap engine is nearly 1.8 times higher.
[21 Mar 2011 4:01] Roberto Spadim 
this is closed or not?
i didn't tested yet...
i will test and report again (with more time)