MySQL Problem

[viscousmemories]

For as long as this forum has been here, I've been backing up the db with MySQLdump and then when I want to test something I restore it on my home server. I haven't done this in a few months, and now when I try it the import just hangs forever. When I look at the process list it shows the status as "repair with keycache" on the post table. I realize that can take a long time for as many posts as we have, but I let it go for like 10 hours and it never proceeded. I think it's safe to say it's hanging up there.

So, is it a memory issue? MySQL version incompatibility? (The version on the live server is one rev lower than the one on my home server).

Something else?

Anyone?

Anyone?

Bueller?

[JoeP]

I have no idea whatsoever, but interesting question.

[fragment]

A quick google for "mysql repair keycache hang" turns up lots of archives of this post (http://archives.neohapsis.com/archives/mysql/2004-q1/1400.html) - someone had this repair running for five days and is being told that it just happens to be VERY slow, particularly for large files. Might want to give it a week or so to see what happens, vm.

[Leesifer]

I don't know a lot about mysql either vm, sorry. But on googling for info, there are quite a few hits mentioning that the "repair with keycache" option is VERY, VERY slow.

Also found this:

You should avoid "Repair with keycache" - because as you noticed it's too slow for large tables.

If MySQL started with "Repair by sorting" and then switched to "Repair with
keycache" it means the first repair method failed for some reason and MySQL
falled back to the slow and safe one. In this case error log should contain an
warning "Warning: Enabling keys got errno %d, retrying".

Knowing the error (most probably out of disk space) you should be able to fix it
and ensure that MySQL always uses "repair by sorting"

Don't know if that helps or not.

[Leesifer]

fragment :rarrow: :idea: :idea: :larrow: Lees.

[fragment]

OK, I'm getting way out of my depth here, but in the comments on this page (http://dev.mysql.com/doc/refman/4.1/en/repair.html) in the MySql manual, Kevin Burton (September 20 2004 12:27am) reports being able to change some configuration settings (key_buffer_size, myisam_sort_buffer_size and sort_buffer_size) and speed up the running of repair. I've never played with these before (or even heard of them, for that matter), so I can't say how it's done.

Just don't blame me if you try this and it destroys the whole site, 'kay?

edit -
fragment :rarrow: :idea: :idea: :larrow: Lees.:yup:

[viscousmemories]

Thanks for the brainstorming you guys.

I had followed a clue trail somewhere starting with your first post, frag, and found this:

myisam_max_sort_file_size

The maximum size of the temporary file MySQL is allowed to use while re-creating a MyISAM index (during REPAIR TABLE, ALTER TABLE, or LOAD DATA INFILE). If the file size would be bigger than this value, the index is created using the key cache instead, which is slower.

Problem is, the default setting is 2gb and I'm only importing a 300mb file. :chin:

[Leesifer]

Just don't blame me if you try this and it destroys the whole site, 'kay?

http://www.freethought-forum.com/forum/showthread.php?t=6332