MySQL Forums
Forum List  »  Memory Storage Engine

Debugging Memory Engine Problem
Posted by: Charles Earl
Date: May 28, 2013 12:44PM

I am getting a table full error when attempting to create a btree index for a Memory engine table. The table is able to load and I'm able to generate a Hash index.
I have 1 billion points in the table. I'm able to store 100M points in the indexed table, but not much more than that. I've tried setting the tmp_table_size and max_heap_table_size both in the client and in the my.cnf file with no luck.
Any thoughts?
Below are the particulars of the table and system

-- I create a table:
CREATE TABLE points_mem (val int, x int, y int) ENGINE = MEMORY MAX_ROWS=5000000000;
-- Then insert the points
mysql> INSERT INTO points_mem SELECT * FROM points;
Query OK, 999950884 rows affected (17 min 20.99 sec)
Records: 999950884 Duplicates: 0 Warnings: 0
-- Fails when creating the Btree index

mysql> CREATE INDEX xy_index ON points_mem (x,y) USING BTREE;
ERROR 1114 (HY000): The table '#sql-f7f_23' is full


-- I have set the system variables in the my.cnf file

tmp_table_size=150G
max_heap_table_size=150G

-- The system is Linux Ubuntu 11.10 and the MySQL is 5.1

-- It seems that there is memory, looking at output of "free -g" cmd

free -g
total used free shared buffers cached
Mem: 240 14 225 0 0 12
-/+ buffers/cache: 2 238
Swap: 0 0 0


Thanks for the help

Options: ReplyQuote


Subject
Views
Written By
Posted
Debugging Memory Engine Problem
6407
May 28, 2013 12:44PM


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.