How long does indexing via ALTER TABLE take?

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? It took less than
24 hours to create the table with INSERT statements.

Does an ALTER TABLE force a rebuild of all the table
indices, or are the existing ones copied?

Thanks.

John Nagle

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?


I would say 3-20 minutes depending on the hardware, so long as box is doing
nothing else.

What does SHOW PROCESSLIST say?


--
Brian Wakem
Email: http://homepage.ntlworld.com/b.wakem/myemail.png

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?
>
>
>
> I would say 3-20 minutes depending on the hardware, so long as box is doing
> nothing else.
>
> 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) |
| 38268 | sitetruth | localhost | NULL | Query | 0 | NULL
| show processlist |
+-------+-----------+-----------+-----------+---------+-------+----------------------+------------------------------------------+
2 rows in set (0.04 sec)

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
1793 ? S 0:00 /bin/sh /usr/bin/mysqld_safe
--defaults-file=/etc/my.cnf --pid-file=/var/run/mysqld/mysqld.pid
--log-error=/var/log/mysqld.log
1829 ? Sl 541:14 /usr/libexec/mysqld --defaults-file=/etc/my.cnf
--basedir=/usr --datadir=/var/lib/mysql --user=mysql
--pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking
--socket=/var/lib/mysql/mysql.sock

It's continuing to use CPU time, and the MySQL server is responding properly
to other requests. The "Time" value in SHOW PROCESSLIST continues to go up.

John Nagle

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/

John Nagle 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?

Apparently MySQL is known to be very slow when "Repair with keycache"
is being used, and it will use that when the MySQL configuration parameters
or the disk space situation prevent sorting. See

http://forums.mysql.com/read.php?21,...6150#msg-86150

MySQL support recommended making

myisam_max_sort_file_size
myisam_max_extra_sort_file_size

large enough to allow a full sort of the database. SHOW VARIABLES gives me

myisam_max_sort_file_size 2147483647

which is only 2GB. There's no value for

myisam_max_extra_sort_file_size

but that didn't do anything anyway and was removed, per

http://bugs.mysql.com/bug.php?id=10163

So apparently I need to restart the server, then, as SUPER,

SET GLOBAL myisam_max_sort_file_size = 30000000000;

then try the ALTER TABLE again. Is that correct?

What's the proper way to shut down the long-running ALTER TABLE
job?

John Nagle

John Nagle 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?
>>
>>
>>
>> I would say 3-20 minutes depending on the hardware, so long as box is
>> doing nothing else.
>>
>> 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) |
> | 38268 | sitetruth | localhost | NULL | Query | 0 | NULL
> | show processlist |
>
+-------+-----------+-----------+-----------+---------+-------+----------------------+------------------------------------------+
> 2 rows in set (0.04 sec)
>
> It's been running for about 30 hours now, on an otherwise-idle
> dedicated
> server. MySQL 5 on Fedora Core 6, default Plesk installation.


'Repair with keycache' is the key to this problem - it is VERY slow.

If you google for it you should find some info about 'Repair by sort' being
much faster, you need to increase your myisam_max_sort_file_size.



--
Brian Wakem
Email: http://homepage.ntlworld.com/b.wakem/myemail.png

Axel Schwenke wrote:
> John Nagle <nagle@animats.com> wrote:

>>>>The ALTER TABLE has been running for about 30 hours now.
>>>>About how long should that take?

> 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.

I'll definitely reconfigure for next time. Thanks.

The ALTER TABLE finally finished successfully after 32 hours.

John Nagle