# Memory Engine - Broken or Undiscovered feature?

**URL:** <https://forums.percona.com/t/memory-engine-broken-or-undiscovered-feature/2005>\
**Category:** Other MySQL® Questions\
**Created:** [March 4, 2013, 11:41am UTC](https://forums.percona.com/t/memory-engine-broken-or-undiscovered-feature/2005 "2013-03-04T11:41:43Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![sastryraghu](https://avatars.discourse-cdn.com/v4/letter/s/48db29/32.png) [@sastryraghu](https://forums.percona.com/u/sastryraghu)\
**Post date:** [March 4, 2013, 11:41am UTC](https://forums.percona.com/t/memory-engine-broken-or-undiscovered-feature/2005/1 "2013-03-04T11:41:43Z")

</div>

I have a setup that kind of mimic in-memory database. I have a table created as memory engine and the same server is configured to act as master and slave. The slave is another database which has the same table structure, but the engine is innodb. Now, we all know that the maximum limits of memory engine and that is restricted to max\_heap\_table\_size. Now that, we have this limitation,I had to ahve some kind of work around. My application was a logger that continuously wrote to this table (no updates/deletes) and then the reporting module read it off the slave for graphs and analytical information.  
So I decided to partition the memory engine based table on the master by primary key (hash partition) and there after even after 4M records, i dont see table full error!

Setup uses  
MySQL 5.5 / Linux

here is how it is done:-

CREATE TABLE `callstack` (  
`instance_id` bigint(20) NOT NULL AUTO\_INCREMENT,  
`t_thread_id` varchar(64) DEFAULT NULL,  
`s_id` varchar(64) DEFAULT NULL,  
`t_host` varchar(30) DEFAULT NULL,  
`s_t_id` varchar(64) DEFAULT NULL,  
`p_s_t_id` varchar(64) DEFAULT NULL,  
`service` varchar(30) DEFAULT NULL,  
`ipa` varchar(50) DEFAULT NULL,  
`c_time` bigint(20) DEFAULT NULL,  
`duration` smallint(6) DEFAULT NULL,  
`s_host` varchar(30) DEFAULT NULL,  
`event` varchar(15) DEFAULT NULL,  
`mode` varchar(10) DEFAULT NULL,  
PRIMARY KEY (`instance_id`)  
) ENGINE=MEMORY DEFAULT CHARSET=latin1  
/\*!50100 PARTITION BY HASH (instance\_id)  
PARTITIONS 100 \*/

# Current Table status:=

mysql\> show table status like ‘callstack%’;  
±----------±-------±--------±-----------±--------±---- -----------±------------±----------------±-------------± ----------±---------------±--------------------±--------- —±-----------±------------------±---------±----------- ----±--------+  
| Name | Engine | Version | Row\_format | Rows | Avg\_row\_length | Data\_length | Max\_data\_length | Index\_length | Data\_free | Auto\_increment | Create\_time | Update\_time | Check\_time | Collation | Checksum | Create\_options | Comment |  
±----------±-------±--------±-----------±--------±---- -----------±------------±----------------±-------------± ----------±---------------±--------------------±--------- —±-----------±------------------±---------±----------- ----±--------+  
| callstack | MEMORY | 10 | Fixed | 4370554 | 479 | 2095729600 | 0 | 90993600 | 0 | 4370555 | 2013-02-28 10:20:41 | NULL | NULL | latin1\_swedish\_ci | NULL | partitioned | |  
±----------±-------±--------±-----------±--------±---- -----------±------------±----------------±-------------± ----------±---------------±--------------------±--------- —±-----------±------------------±---------±----------- ----±--------+  
1 row in set (0.00 sec)

If you observe, the max\_data\_length shows 0.

# max\_heap\_table\_size variable

mysql\> show variables like ‘%heap%’;  
±--------------------±----------+  
| Variable\_name | Value |  
±--------------------±----------+  
| max\_heap\_table\_size | 268435456 |  
±--------------------±----------+  
1 row in set (0.00 sec)  
(256 MB)

Is the memory engine broken or is this a edge case issue? But nonetheless, it is working for me as I wanted! I am trying to figure out how this is working inspite of the heap table restriction. Checked on the disk, if the partitions have created data, but there are only 2 files,callstack.frm and callstack.par.  
Will post more details, but if you know anything on the internals, let me know!

Regards,

---

<div class="post-metadata">

**Author:** ![gmouse](https://avatars.discourse-cdn.com/v4/letter/g/b9e5f3/32.png) [@gmouse](https://forums.percona.com/u/gmouse)\
**Post date:** [March 4, 2013, 2:14pm UTC](https://forums.percona.com/t/memory-engine-broken-or-undiscovered-feature/2005/2 "2013-03-04T14:14:24Z")

</div>

Each partition is a separate table, so currently your maximum table size (if the hash is perfectly uniform) will be 100 times 256 MB.

See [http://dev.mysql.com/doc/refman/5.1/en/partitioning-overview](http://dev.mysql.com/doc/refman/5.1/en/partitioning-overview) .html  
“In effect, different portions of a table are stored as separate tables in different locations.”

---

<div class="post-metadata">

**Author:** ![sastryraghu](https://avatars.discourse-cdn.com/v4/letter/s/48db29/32.png) [@sastryraghu](https://forums.percona.com/u/sastryraghu)\
**Post date:** [March 4, 2013, 2:16pm UTC](https://forums.percona.com/t/memory-engine-broken-or-undiscovered-feature/2005/3 "2013-03-04T14:16:40Z")

</div>

Thanks for the response 🙂 Even for Memory Engine tables? That is what a bit perplexing for me… As the entire data is in memory and nothing on disk…