Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

We have a database server that was running MySQL 5.0 on Debian etch (!), and decided it was time to upgrade. It is now running 5.1 on Debian squeeze.

This database server has about 1.2TB of MyISAM data on a SATA RAID array, and 2GB of ram. Normally speed is not a factor for the queries this server runs, it's mostly background stuff.

On upgrade, the Debian package ran a maintenance script to upgrade the tables, but it is taking an extremely long time to upgrade each table. By long, I mean it's taking about 18 hours per table, and to do the lot is going to take something like 6 weeks at the current speed. This is quite a big problem.

I've tried increasing the global key_buffer to 512MB, which seems to be in line with recommendations, to no effect.

The problem seems to be that it's using the "Repair with keycache" method, which is much slower than the sort method:

mysql> show processlist;
+-----+------------------+----------------------------------+------------------+---------+-------+----------------------+--------------------------------------------------------------------------+
| Id  | User             | Host                             | db               | Command | Time  | State                | Info                                                                     |
+-----+------------------+----------------------------------+------------------+---------+-------+----------------------+--------------------------------------------------------------------------+
|   5 | debian-sys-maint | localhost                        | xxxxxxxxxxxxxxxx | Query   | 45146 | Repair with keycache | REPAIR TABLE `xxxxxxxxxxxxxxxx`.`xxxxxxxxxxxxxxxxxxxx`     

Other tables are inaccessible due to requiring an upgrade:

mysql> check table xxxxxxxxxxxxxxxxxxxx fast quick;
+---------------------------------------+-------+----------+---------------------------------------------------------------------------------------------------+
| Table                                 | Op    | Msg_type | Msg_text                                                                                          |
+---------------------------------------+-------+----------+---------------------------------------------------------------------------------------------------+
| xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | check | error    | Table upgrade required. Please do "REPAIR TABLE `xxxxxxxxxxxxxxxxxxxx`" or dump/reload to fix it! |
+---------------------------------------+-------+----------+---------------------------------------------------------------------------------------------------+
1 row in set (1.09 sec)

The main question is, how can I upgrade these tables and get the data online faster?

Secondary questions would be:

  • Does using myisamchk with something like "myisamchk --sort-recover --analyze --sort_buffer_size=256M --key_buffer=256M --read_buffer=2M --write_buffer=2M tablename" upgrade the table (i.e. not using the keycache method)?
  • Can I safely kill the debian script and upgrade the tables with a more efficient method?
  • The server was initially started with a key_buffer_size of just 16M. I've since corrected this by setting the global variable, but is it possible that the debian script's session is still using some smaller value? If so can I alter it?
share|improve this question
add comment

2 Answers

The main question is, how can I upgrade these tables and get the data online more quickly?

The simple answer is run mysql_upgrade.

Does using myisamchk with something like "myisamchk --sort-recover --analyze --sort_buffer_size=256M --key_buffer=256M --read_buffer=2M --write_buffer=2M tablename" upgrade the table (i.e. not using the keycache method)?

No.

Can I safely kill the debian script and upgrade the tables with a more efficient method?

I need to know more details about this script.

The server was initially started with a key_buffer_size of just 16M. I've since corrected this by setting the global variable, but is it possible that the debian script's session is still using some smaller value? If so can I alter it?

You can do it for a session:

mysql> SET SESSION key_buffer_size = ... ;
share|improve this answer
    
Thanks. Turns out the debian script just the standard init script which checks for upgrade needing tables. –  Alex Forbes Oct 3 '11 at 10:43
add comment
up vote 1 down vote accepted

Turns out the debian script just the standard init script which checks for upgrade needing tables, thus killing it wasn't a problem as it simply re-runs on init.

The key buffer value wasn't the problem, as I suspected it was the keycache method it was using to repair the table - it's simply too slow for this much data.

Once we 'set global myisam_max_sort_file_size=21474836480;' and restarted mysql, it started using the sort method which is much faster. But then on another table it went back to keycache so I raised it to 80G and restarted again.

All tables are now upgraded.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.