MySQL Forums
Forum List  »  InnoDB

mysql 4.1.7 innodb index creation problems
Posted by: Sebastien Boving
Date: January 06, 2005 03:53PM

i have 4x 2gb innodb datafiles with the last one set to auto_increment, on a 300gb partition with nothing else on it. the tables are loaded with data and the last datafile has not incremented yet.

i then create an index on a table:

alter table NODES add INDEX whatever_blows_up (node_hierarchy_level, path_segment_name(50));

and it slowly fills up the whole disk, 300gb!

there doesn't seem to be anything i can do at this point to stop it, killing the thread doesn't work either.

only solution (sigh) is to kill -9 the mysqld's, delete all my datafiles (thank god this is not production) and reload the data.

so i recreate the datafiles and this time specify a max on the last (autoincrement) file, which i had hoped to avoid. This time the index creation again fills up the datafile until the max, and then just keeps running. Only if i kill -9 those mysql'd, restart mysql and run the query again does it eventually complain about a table full error (but the first time,it always keeps spinning).

i also get this when i simplify the index creation to:

create index IDX_PATH_SEGMENT_NAME_BIS on NODES (PATH_SEGMENT_NAME(50));

where i already have an index IDX_PATH_SEGMENT_NAME that is exactly the same!

mysql> show index from NODES;
+-------+------------+-------------------------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+-------------------------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+
| NODES | 0 | PRIMARY | 1 | JOBSYS_NODE_ID | A | 8959401 | NULL | NULL | | BTREE | |
| NODES | 0 | PRIMARY | 2 | SYMBOLIC_LINK_NUMBER | A | 8959401 | NULL | NULL | | BTREE | |
| NODES | 1 | IDX_HIERARCHY_LEVEL__PARENT_NODE_ID | 1 | NODE_HIERARCHY_LEVEL | A | 8959401 | NULL | NULL | | BTREE | |
| NODES | 1 | IDX_HIERARCHY_LEVEL__PARENT_NODE_ID | 2 | PARENT_NODE_ID | A | 8959401 | NULL | NULL | YES | BTREE | |
| NODES | 1 | UIDX_FULL_PATH_NAME | 1 | FULL_PATH_NAME | A | 8959401 | 50 | NULL | | BTREE | |
| NODES | 1 | IDX_TOP_NODE_ID | 1 | TOP_NODE_ID | A | 8959401 | NULL | NULL | YES | BTREE | |
| NODES | 1 | IDX_PARENT_NODE_ID | 1 | PARENT_NODE_ID | A | 8959401 | NULL | NULL | YES | BTREE | |
| NODES | 1 | IDX_PATH_SEGMENT_NAME | 1 | PATH_SEGMENT_NAME | A | 8959401 | 50 | NULL | | BTREE | |
| NODES | 1 | IDX_ACTIVE_IND | 1 | ACTIVE_IND | A | 8959401 | NULL | NULL | | BTREE | |
+-------+------------+-------------------------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+
9 rows in set (0.30 sec)

so how is this possible? the only difference i see is that one index has been created before i started inserting (about 9 million) rows in the table, the other one i create after the inserts.

what am i doing wrong?

this is mysql 4.1.7 running on AMD64 btw.

thanks,
-seb.

Options: ReplyQuote


Subject
Views
Written By
Posted
mysql 4.1.7 innodb index creation problems
3849
January 06, 2005 03:53PM


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.