How can I get status messages during a large re-indexing operation with MySQL 5.5?

Question

I get large data transfers periodically that I used to completely replace the contents of a MySQL table. Each time I do this, I truncate the table, disable keys, add several million rows of data, and then enable keys. As expected, once I enable keys, there is a lengthy delay while the table is being reindexed. The table has several active indices and it takes about 30 minutes to complete.

Unfortunately, I can't figure out how to get status messages that would give me progress updates on how the operation is proceeding. I found an old thread from 2001 on the MySQL forum that basically says you can't get progress updates.

I was hoping that was no longer the case. I am running MySQL Server 5.5 and MySQL Workbench 5.2, both community editions. Perhaps there's something I can do with workbench?

Side question: Currently, I do my updates from a Windows program that sequentially parses the transfer data file into SQL INSERT statements that I immediately dispatch to the MySQL client over a socket. Would it be faster to generate all the INSERT statements to a file and then do a bulk import by executing the SQL script from Workbench instead?

-- roschler

Answer 1

You need to monitor that progress outside of all mysql client programs.

For this example, let's say your MyISAM table is named workingset in the database mydata and the datadir is the default, /var/lib/mysql

Sidenote : If you are using Windows, run this SQL command to get your datadir

SHOW VARIABLES LIKE 'datadir';

You will have three files representing the table

/var/lib/mysql/mydata/workingset.frm
/var/lib/mysql/mydata/workingset.MYD
/var/lib/mysql/mydata/workingset.MYI

Try a temp table approach to loading the table workingset

use mydata
DROP TABLE IF EXIST workingsetload;
CREATE TABLE workingsetload LIKE workingset;
ALTER TABLE workingsetload DISABLE KEYS;
Perform 7 million row insert into workingsetload here, preferably LOAD DATA INFILE
ALTER TABLE workingsetload ENABLE KEYS;
ALTER TABLE workingset RENAME workingsetold;
ALTER TABLE workingsetload RENAME workingset;
DROP TABLE workingsetold;

During the load of the table workingsetload, there will be a temp table whose first five(5) characters are "#sql-". You can visually monitor in the Linux OS the progress of the loading of workingsetload like this:

cd /var/lib/mysql/mydata
watch -n 1 "ls -l workingsetload.MY[ID] *sql-*.MY[ID] | awk '{print $5,$9}'"

You could script this in linux like this:

cd /var/lib/mysql/mydata
ls -l workingsetload.MY[ID] *sql-*.MY[ID] | awk '{print $5,$9}' > /tmp/IndexRebuild.txt

You could then parse the columns accordingly and subtract the .MYD sizes to know when the data portion of the MyISAM table is complete. Subsequently, you could then parse the columns accordingly and subtract the .MYI sizes to know when the index portion of the MyISAM table is complete.

For Windows, you could script this using Perl (ActivePerl for Windows) or just have a DOS Batch loop over and over again.

Regardless of OS, once the "ls -l #sql-*.MY[ID]" or "if not exist #sql-*.MY*" comes back with nothing, the reload and indexing are complete.

Give it a Try !!!

UPDATE 2011-07-20 16:55 EDT

Monty has successfully implemented this feature in MariaDB. Check it out !!!