2011年2月17日 星期四

MySQL MEMORY TABLE

DROP TABLE IF EXISTS `etf50`;
CREATE TABLE `etf50` (
  `SDATE` varchar(10) default NULL,
  `STIME` varchar(10) default NULL,
  `PRICE` varchar(10) default NULL,
  `BID` varchar(10) default NULL,
  `ASK` varchar(10) default NULL,
  `CHANGES` varchar(10) default NULL,
  `VOLUME` varchar(10) default NULL,
  `CUMULATIVE` varchar(10) default NULL,
  `ROWID` int(11) NOT NULL auto_increment,
  PRIMARY KEY  (`ROWID`)
) ENGINE=MEMORY AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

修改mysql.ini
max_heap_table_size=2048M 
tmp_table_size=2048M 
重啟mysql


 After using MySQL memory engine table as middle cache table, we got this error when application inserting the data to the table. Seems there is some limits on the table. After searching on Google, we know that we need to change some default settings.
    The global system variable "max_heap_table_size" define the maximum size memeory table can reach. The default values is 16384 (Maybe it's OS dependant). So you can change it with the following steps.
    Run the following command to change it at system level.
set global max_heap_table_size=1048576000
    Then modify the MySQL configuration file, adding a new line at the end. So when next time database get restarted, the change can be permanent.
max_heap_table_size=1048576000
    Finally, you need to reconnect to MySQL, and rebuild all the tables.
ALTER TABLE ... ENGINE MEMORY;
    The last step is to let application reconnect to MySQL database, to take the change effective.

http://www.dbatools.net/experience/mysql_table_is_full.html

沒有留言: