mysql alter table 終わらない

質問

ALTER TABLEの処理が全然終わらなくなってしまったので、質問させて下さい。
下記のような状態です。

・テーブルの行数は962362
・topコマンドで見たとき、普段mysqldのCPU使用率が50～100%なのですが、ALTER TABLE後に確認すると2%前後になる
・試した処理はdrop indexとadd column
・mysqlを再起動しようにもstopするのに数分かかる
・nginxを一度stopし、負荷の少ない状態でも同じことを試しましたが結果はかわりませんでした
・検索してみると、key_buffer_sizeが少なすぎることが原因という記事をみかけたので、試しにkey_buffer=512Mとして、設定を読み込み直してから試してみましたが結果はかわりませんでした

このような場合の原因と対策を教えて頂ければ幸いです。
足りていない情報は随時補足させていただきます。

是非回答宜しくお願い致します。

回答No.3

MOBY_MAKOTO

key_buffer_sizeをチューニングしようとされているので、ストレージエンジンは、MyISAMでしょうか？
InnoDBなら、チューニングするパラメータが異なります。

alter tableでテーブルを変更する場合において、
基本的にデータが多いときは、indexの再作成に時間がかかります。
（使用しているハードウェアのスペックに依存しますが）

CPU使用率ですが、普段が2%前後で、alter table実行時に50～100%になるのでしょうか？

教えていただきたいのが、下記の項目です。
・ハードウェアのスペック
・ストレージエンジン
・テーブルに張っているインデックスの数とインデックスの合計データサイズ(1行あたり）

補足

ストレージエンジンはmroongaを使っています。

mysqldのCPU使用率ですが、普段が50～100%でalter table実行時に2%前後になってしまいます。

サーバーのスペックは、さくらVPSの8Gのプランになります。
http://vps.sakura.ad.jp/

・テーブルに張っているインデックスの数とインデックスの合計データサイズ(1行あたり）
すみません、これはどの部分を見ればよいでしょうか…

回答No.2

yambejp

・対象テーブルと同じ構成の変更用テーブルをつくる
・あらたにインデックスをつける
・対象テーブルから変更用テーブルにデータを流し込む
・変更用テーブルをつかってテスト
・問題なければ対象テーブルを削除し、変更用テーブルを対象テーブルにリネーム

という流れでやるとよいでしょう

・対象テーブルから全データを別テーブルコピー
・対象テーブルをトランケート
・インデックスを設定
・別テーブルから対象テーブルにデータを戻し入れる

でもよいかも

補足

いつもお世話になります。

教えていただいた方法を早速試してみようと思ったのですが、CREATE TABLE自体が処理が終わらないようです…
CREATE TABLEで処理が返ってこない状態で別windowからSHOW TABLESとしてみたところ、CREATE TABLEしたテーブルの名前があったので、処理が返ってこないだけでできているのかと思ったら、そのテーブルに対してINSERTやSELECTをしてみても何も返ってきませんでした。（emptyではなく処理待ち状態）
この補足を書いている現在、DROP TABLE `table_`;の処理待ち中です…

この件に関してはmysqldのCPU使用率は2%まで落ちることはなかったのですが、なにか根本的に直さなければならない部分があるように思ったのですがどうなのでしょうか…

回答No.1

maiko0318

９万６千件・・・多すぎます。時間がかかるのは当然です。
mysqlをストップすると、処理中のものが終わるまでまち、コミットが終わるか、
処理中のものを元に戻し、ロールバックが終了するまで待たされます。

それを待たずに終了してしまったら、myqlが立ち上がらなくなってしまいます。

私、前に大型コンピュータで１００万件のUpdateをかけました。
何十分たっても終わらないのでキャンセルしたのです。

そしたらログがパンクし、ログの処理を間違え、何もできなくなってしまったので強制終了したのです。
データベースを再インストールするはめに陥りました。

補足

回答ありがとうございます。

別サイトでほとんど同じテーブル（直近の数千件の中身が違う程度であとは全く同じ）に対して全く同じクエリを投げたところ15秒程度で終わっていたので今回の件に疑問を感じました。10分以上たっても処理が終わる気配がありません。

また、msqldのCPUusageが2%程度まで落ち込んでしまうというのはどういう現象なのでしょうか。

ALTER TABLEの処理では、元のテーブルの一時的なコピーが作成されるとのことなので、処理を中断しても影響が出ないという記事を見たような記憶があり処理を中断した次第です。

key_bufferの上限を上げるのもそのためなのかな、と思っていました。

mysqlに関しての知識がまだまだ浅いため疑問ばかりがうかびます。

原因と対応策をはっきりすることができればと考えていますので、引き続き回答の受付を続けさせて頂ければと思います。
宜しくお願い致します。