Unable to restore from mysql database dump

Question

Hello All,

I have made a dump of my database in which one table is pretty huge (about 4 million records) and has good number of indices(One full text index along with a primary key and 3 unique indices). When i try to restore this dump the restore hangs(its been running for five days now) when it tries to execute the statement which enables keys (ALTER table x ENABLE keys).

Any suggestions on how to go about restoring this dump are more than welcome. The table in question is a myisam table.

Here is the output of showprocesslist

mysql> show processlist;
+------+----------+-------------------------------------------------+---------------+---------+------+----------------------+------------------------------------------------+
| Id   | User     | Host                                            | db            | Command | Time | State                | Info                                           |
+------+----------+-------------------------------------------------+---------------+---------+------+----------------------+------------------------------------------------+
| 2350 | rdsadmin | localhost:52508                                 | NULL          | Sleep   | 3701 |                      | NULL                                           |
| 3331 | root     | <remote_ip>                            | <db> | Query   | 7971 | Repair with keycache | /*!40000 ALTER TABLE `entities` ENABLE KEYS */ |
| 3810 | root     | <remote_ip> | NULL          | Query   |    0 | NULL                 | show processlist                               |
+------+----------+-------------------------------------------------+---------------+---------+------+----------------------+------------------------------------------------+
3 rows in set (0.00 sec)

Thanks

Answer 1

PostgreSQL does not support "alter table x enable keys" statement:

alter table a enable keys;
ERROR:  syntax error at or near "keys"
LINE 1: alter table a enable keys;
                             ^

Try to dump your MySQL database with mysqldump --compatible=postgresql - maybe it will load just fine then.

You can also try to dump and import only schema first with mysqldump --compatible=postresql --no-data. It would be easier to correct any incompatibilities in dump file by hand. If successfully imported then dump and import data only with mysqldump --compatible=postresql --no-create-info.

Answer 2

The reason it is so slow is that the alter table is using "Repair with keycache" to build the index. This builds the index by adding one entry at a time into the index.

The much faster way is to use "Repair with sort". However, MySQL needs to have lots of memory and temporary disk space to make this work, and what will usually happen is that MySQL will start by using the "sort" method, run out of space, and fall back to the "keycache" method.

The way around that is to allow your MySQL server to use more space for sorting. This is controlled by some server variables:

• myisam_max_sort_file_size - set this to as big a value as you can safely afford - eg. 150G
• myisam_sort_buffer_size - again, big is better - eg. 1G

These are both dynamic variables, but I doubt that changing them once the alter table has started will have much benefit. If you don't want to alter your system config, then an alternative approach is to build the indexes using myisamchk in repair mode, allocating large sort buffers.