MySQL Forums :: Memory Storage Engine :: No Limits - Memory Engine


Advanced Search

No Limits - Memory Engine
Posted by: Raghunandan Sastry ()
Date: March 04, 2013 12:59AM

Hello,

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!

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)

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)


Is the memory engine broken or is this a edge case issue? But nonetheless, it is working for me as I wanted!

Regards,
Raghu

Options: ReplyQuote


Subject Views Written By Posted
No Limits - Memory Engine 5192 Raghunandan Sastry 03/04/2013 12:59AM
Re: No Limits - Memory Engine 1893 Rick James 03/04/2013 09:28PM
Re: No Limits - Memory Engine 1635 Raghunandan Sastry 03/05/2013 12:02PM
Re: No Limits - Memory Engine 1661 Rick James 03/06/2013 09:17AM
Re: No Limits - Memory Engine 1722 Raghunandan Sastry 03/07/2013 03:21AM
Re: No Limits - Memory Engine 1644 Rick James 03/07/2013 11:25PM
Re: No Limits - Memory Engine 1616 Raghunandan Sastry 03/09/2013 09:28AM
Re: No Limits - Memory Engine 1766 Rick James 03/10/2013 12:04PM
Re: No Limits - Memory Engine 1617 Raghunandan Sastry 03/11/2013 10:39PM
Re: No Limits - Memory Engine 1503 Rick James 03/20/2013 07:19PM
Re: No Limits - Memory Engine 1568 Shane Bester 03/18/2013 07:38AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.