Home / server / Questions / 317256
Accepted
Alex Forbes
Asked: 2011-10-01 06:19:13 +0800 CST

MySQL 5.0 -> 5.1 upgrade, table upgrades taking a very long time

  • 772

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?
mysql myisam

2 Answers

  1. 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 = ... ;
    • 1
  2. Best Answer

    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.

    • 1