John Nagle <nagle@animats.com> wrote:
> Brian Wakem wrote:
>> John Nagle wrote:
>>
>>
>>> I loaded about 15 million records into a MySQL table, and
>>> then did an ALTER TABLE to add an index on a BIGINT field.
>>> The ALTER TABLE has been running for about 30 hours now.
>>> About how long should that take?

This clearly depends on your hardware and the configuration of the
MySQL server.

>> What does SHOW PROCESSLIST say?
>
> +-------+-----------+-----------+-----------+---------+-------+----------------------+------------------------------------------+
>| Id | User | Host | db | Command | Time | State
> | Info |
> +-------+-----------+-----------+-----------+---------+-------+----------------------+------------------------------------------+
>| 38101 | sitetruth | localhost | sitetruth | Query | 85334 | Repair with
> keycache | alter table infotus add primary key (id) |
> +-------+-----------+-----------+-----------+---------+-------+----------------------+------------------------------------------+

Repair (correctly: add index) with keycache is the slow method. The
faster one would use the sort buffer. But even the slower keycache
method should not take *so* long. Probably you have configured
key_buffer very small (or not at all, using the default of 8MB).

You should configure key_buffer to 25-50% of your memory and
myisam_sort_buffer to approx. half that size. If you intend to add
a lot of indexes, you can even temporarily make myisam_sort_buffer
the bigger setting. This will greatly increase performance of
indexing operations.

For 15.000.000 rows with a BIGINT sort column you will need
(8 Byte (BIGINT) + 4 Byte (row pointer)) * 15.000.000 = 180MB
sort buffer to do the sorting in memory. This should finish in
few minutes then.

> It's been running for about 30 hours now, on an otherwise-idle dedicated
> server. MySQL 5 on Fedora Core 6, default Plesk installation.

> -bash-3.1$ ps ax
> PID TTY STAT TIME COMMAND
> 1829 ? Sl 541:14 /usr/libexec/mysqld --defaults-file=/etc/my.cnf

The repair table is running since 85334 seconds while mysqld used only
541:14 = 32474 seconds of cpu time (and I guess mysqld consumed some
of this time even before you started that ALTER TABLE statement).
The computer is clearly I/O bound, waiting mostly for the disk.


XL
--
Axel Schwenke, Support Engineer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/