MySQL Forums
Forum List  »  Memory Storage Engine

Problem creating indices, autoincrement id's on HEAP engine.
Posted by: Michael Paik
Date: April 07, 2005 08:43AM

All,

I have a relatively large table I am trying to load into a HEAP table. At the moment I have reserved approximately 3GB of main memory for it, and am using ~2GB. 222 attributes, with average row width of 4259. Here are the results of ;show table status;':

mysql> show table status;
+-------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-------------------+----------+--------------------------------------+---------+
| 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 |
+-------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-------------------+----------+--------------------------------------+---------+
| cache | HEAP | 9 | Dynamic | 500000 | 4259 | 2138450360 | 3095270840 | 0 | 0 | NULL | NULL | NULL | NULL | latin1_swedish_ci | NULL | max_rows=2100000 avg_row_length=8000 | |
+-------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-------------------+----------+--------------------------------------+---------+

As you can see, I have half a million rows (500,000). I can try to create an index on any column, or create an autoincrement id with alter table, and the operations always fail with:

ERROR 1030 (HY000): Got error 12 from storage engine

I am told that ERROR 1030 usually implies a lack of storage space, but seeing as how I can fit another 207K rows (this demonstrated by experiment), it seems unusual that there would be no more space to create a column with an integer value, where the size would be (4B * 500000) * 1.05 (assuming 5% table overhead) ~= 2.1MB.

Any ideas? I notice there isn't much traffic on this forum, but hopefully someone has done this before. In the meantime I will try to do the operation with 50,000 rows to see if it's some size limitation.

Options: ReplyQuote


Subject
Views
Written By
Posted
Problem creating indices, autoincrement id's on HEAP engine.
8600
April 07, 2005 08:43AM


Sorry, you can't reply to this topic. It has been closed.
This forum is currently read only. You can not log in or make any changes. This is a temporary situation.

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.