Hi Ingo,
thanks for your response. I'm sorry about the mistake, it should actually read "just before the server normally leaves the 'copy to tmp table' state".
I did it again and checked some things in parallel: In one screen I had "top" running, on an other "mytop" and on a third a "watch" command with an "ls -lah" in the database directory (to see the progress on the temporary files) and a "grepped" processlist showing me the current state of the "ALTER" command. On a fourth screen I did some other checks, e.g. checks for available file handles on the system.
As you said: "copy to tmp table" was the main part (the only one detectable). I could see how the server built the temporary table files during the first 40 to 50 minutes. First it copied part of the data to the MYD file, then it spent some time on the MYI, then some MYD again (same thing repeated).
The memory usage of mysql according to top was always around 30% (2 GB physical RAM available), the server didn't swap. The mysqld process doing the ALTER TABLE started using almost 100% of one of the two CPUs (actually four, as they are hyper-threaded). Of course the server load increased (to between 1.5 and a short time maximum of about 3) during the operation, but it was always very responsive and had no problems handling all the other connections in parallel (we actually never encounter any performance problems on that server, even if I do some I/O and CPU intensive tasks such as tar-gzipping a backup or copying gigabytes of data).
Now, what I realized was that the CPU usage of the ALTER TABLE process actually decreased (!) throughout the whole building of the temporary tables. Just as the temporary MYD file reached the size of the original MYD file (the temporary MYI file was still missing about 100 MB), the mysqld process doing the ALTER TABLE came to an almost complete halt. It only used around 0.2% of the CPU and spent a lot of its time in "uninterruptible sleep" state. That's when a lot of other connections were blocked in the "Opening tables" state. I checked the available file handles - that was not the problem. Then I had to take down and restart the server process (it's a production machine...).
So my thought is, that it's not an I/O or CPU bottleneck (in terms of hardware) but could indeed be a fight for resources on the software level (I just excluded file handles so far).
What do you (or anybody!) think?
Thanks again,
Beat
Ingo Strüwing wrote:
> Hi,
>
> this sounds odd. "copy to tmp table" should be the
> main part of ALTER TABLE. Here the data is copied
> from the old table to the new table and the
> indexes are created.
>
> I wonder what the state is during the other time
> of the 40 minutes.
>
> Queries that don't use this table should not be
> blocked. Is it possible that your machine is so
> busy during the table copy that it feels like
> completely locked up? Is it possible that you have
> too less RAM and/or too slow disk for your
> application?
>
> Regards
>
Beat Vontobel
http://www.futhark.ch/mysql