• 締切済み
  • すぐに回答を!

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として、設定を読み込み直してから試してみましたが結果はかわりませんでした このような場合の原因と対策を教えて頂ければ幸いです。 足りていない情報は随時補足させていただきます。 是非回答宜しくお願い致します。

共感・応援の気持ちを伝えよう!

  • MySQL
  • 回答数3
  • ありがとう数1

みんなの回答

  • 回答No.3

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
  • ベストアンサー率51% (3827/7415)

・対象テーブルと同じ構成の変更用テーブルをつくる ・あらたにインデックスをつける ・対象テーブルから変更用テーブルにデータを流し込む ・変更用テーブルをつかってテスト ・問題なければ対象テーブルを削除し、変更用テーブルを対象テーブルにリネーム という流れでやるとよいでしょう ・対象テーブルから全データを別テーブルコピー ・対象テーブルをトランケート ・インデックスを設定 ・別テーブルから対象テーブルにデータを戻し入れる でもよいかも

共感・感謝の気持ちを伝えよう!

質問者からの補足

いつもお世話になります。 教えていただいた方法を早速試してみようと思ったのですが、CREATE TABLE自体が処理が終わらないようです… CREATE TABLEで処理が返ってこない状態で別windowからSHOW TABLESとしてみたところ、CREATE TABLEしたテーブルの名前があったので、処理が返ってこないだけでできているのかと思ったら、そのテーブルに対してINSERTやSELECTをしてみても何も返ってきませんでした。(emptyではなく処理待ち状態) この補足を書いている現在、DROP TABLE `table_`;の処理待ち中です… この件に関してはmysqldのCPU使用率は2%まで落ちることはなかったのですが、なにか根本的に直さなければならない部分があるように思ったのですがどうなのでしょうか…

  • 回答No.1

9万6千件・・・多すぎます。時間がかかるのは当然です。 mysqlをストップすると、処理中のものが終わるまでまち、コミットが終わるか、 処理中のものを元に戻し、ロールバックが終了するまで待たされます。 それを待たずに終了してしまったら、myqlが立ち上がらなくなってしまいます。 私、前に大型コンピュータで100万件のUpdateをかけました。 何十分たっても終わらないのでキャンセルしたのです。 そしたらログがパンクし、ログの処理を間違え、何もできなくなってしまったので強制終了したのです。 データベースを再インストールするはめに陥りました。

共感・感謝の気持ちを伝えよう!

質問者からの補足

回答ありがとうございます。 別サイトでほとんど同じテーブル(直近の数千件の中身が違う程度であとは全く同じ)に対して全く同じクエリを投げたところ15秒程度で終わっていたので今回の件に疑問を感じました。10分以上たっても処理が終わる気配がありません。 また、msqldのCPUusageが2%程度まで落ち込んでしまうというのはどういう現象なのでしょうか。 ALTER TABLEの処理では、元のテーブルの一時的なコピーが作成されるとのことなので、処理を中断しても影響が出ないという記事を見たような記憶があり処理を中断した次第です。 key_bufferの上限を上げるのもそのためなのかな、と思っていました。 mysqlに関しての知識がまだまだ浅いため疑問ばかりがうかびます。 原因と対応策をはっきりすることができればと考えていますので、引き続き回答の受付を続けさせて頂ければと思います。 宜しくお願い致します。

関連するQ&A

  • Mysqlに項目を追加するとき順番をかえるには

    こんにちは、 今mysqlを使用しています。 例えば、tbl_EXというテーブルがあり、 そこにはid,name,flgが入っているとしたときに addという項目を追加したい場合は ALTER TABLE tbl_EX ADD add int NULL DEFAULT 0 などとすれば追加できると思うのですが、 その追加を id, name, (add), flg というように 真ん中に入れたい場合は、どうすればいいのでしょうか? 一度テーブルをdropして作り替える以外に方法がある場合、 教えてください。

    • ベストアンサー
    • MySQL
  • ALTER文を使用してプライマリキーのlengthを変更

    はじめまして. 1点、ご質問がございます。 mysqlにてテーブルを既に構築しています。 テーブルのプPROMARY KEYの型がVARCHARの255バイトで定義 しています。 その長さを変更したく思いますが、DROP文は使わず、 ALTER文で更新するような方法で、できないものかと模索中です。 ご存知の方がいらっしゃいましたら、お知恵をお貸し願えないでしょうか? よろしくお願いします。

  • alter tableすると、処理が止まってしまい困っています

    表題の件で質問させて下さい。 以前までは特に問題なく、alter tableなどで列を追加出来ていたのですが、ここ最近、データ量が増えてきたためか、列追加にすごく時間がかかってしまっています。 それが原因なのかはわからないのですが、alter tableで列を追加すると、処理が止まってしまい、プロセスをkillして、とりあえず対処する・・・ と言うような対応が続いています。 調べたところ、alter table はテーブルロックがかかってしまうらしいのですが、これは begin でトランザクションを開始させても特に意味はないのでしょうか? いろいろと調べてはいるのですが、基本的な事は見つかるのですが、運用する時にどういった注意点があるか等が今いち、調べ切れませんでしたので、どんな運用をしていけばいいのかご教授して頂けると助かります。 宜しくお願いいたします。

  • MYSQLデータベースの移行

    現在WordPressで運用しているサイトをサーバーにあげているのですが、ローカルでCSS等の微調整をしたいのでローカルのXAMPP環境に移行させたいと思っています。 サーバーのMYSQLからエクスポートした後ローカルでインポートしようと試みたのですがwp_commentmeta以外のテーブルができません。エラーメッセージは下記のものです。 ALTER TABLE `wp_commentmeta` ADD PRIMARY KEY (`meta_id`), ADD KEY `comment_id` (`comment_id`), ADD KEY `meta_key` (`meta_key`(191)) 単純なことなのかもしれませんがどこをどうすればいいのか分かりません。ご教授いただければ幸いです。

  • 重複を許すキーの構文がわかりません。

    データベース自体はPostgreSQLを使っています。そして、アクセス2003を使って、テーブルリンクで閲覧しています。 あるフィールドに検索が早くなるようにキーを設けたいのですが、 重複を許すキーの構文がわかりません。 PRIMARY KEY, UNIQUEの設定はわかるのですが、 どのように記述すればよいのでしょうか? 以下、アクセスのプロパティです。 インデックス ●いいえ デフォルト、無指定 ●はい(重複あり)[***** ここの部分のSQLが知りたい *****] ALTER TABLE テーブル名 ADD ???????? (フィールド); ●はい(重複なし)UNIQUE もしくは値要求混みのPRIMARY KEY ALTER TABLE テーブル名 ADD UNIQUE (フィールド); ALTER TABLE テーブル名 ADD PRIMARY KEY (フィールド);

  • SQL Server(MSDE2000) : ALTER TABLE

    SQL Server(MSDE2000) : ALTER TABLE した項目に対し、直後に UPDATE で値をセットできない 既存のテーブルを仕様変更するため、列を追加し、初期値で埋める SQL 文を書こうとしていますが、うまく動かず、エラーとなってしまい、原因が判らず困っています。 サンプル SQL 文: ------ CREATE TABLE TEST_TABLE ( TEST_FIELD1 SMALLINT, TEST_FIELD2 SMALLINT) INSERT INTO TEST_TABLE VALUES( 1, 2 ) ALTER TABLE TEST_TABLE ADD TEST_FIELD3 SMALLINT UPDATE TEST_TABLE SET TEST_FIELD3 = 3 ------ ※テスト毎に、必ず DROP TABLE TEST_TABLE されている事が前提です。 上記 SQL 文のうち、1行目~3行目までを抜粋して実行すると、ちゃんと CREATE TABLE され、INSERT され、ALTER TABLE される事を確認しました。 ところが、4行目までを一気に実行しようとすると、 ------ SQL実行中に以下のエラーが発生しました。 エラーコード:207 [Microsoft][ODBC SQL Server Driver][SQL Server]列名 'TEST_FIELD3' は無効です。 SQLステータス:S0022 ------ となってしまい、UPDATE で初期値を埋める事ができません。 しかも、UPDATE に失敗するどころか、2行目の INSERT から以降が結果に反映されなくなるという状況に陥ってしまいます。 また、既存のテーブルの仕様変更が目的なので、その状況に近づけるために、まず、 ------ CREATE TABLE TEST_TABLE ( TEST_FIELD1 SMALLINT, TEST_FIELD2 SMALLINT) INSERT INTO TEST_TABLE VALUES( 1, 2 ) ------ を実行し、既存のテーブル(とレコード内容)が存在する状態を作り出された事を、ツール等で確認してから、 ------ ALTER TABLE TEST_TABLE ADD TEST_FIELD3 SMALLINT UPDATE TEST_TABLE SET TEST_FIELD3 = 3 ------ の2行を実行してみると、やはり UPDATE は失敗し、前述と同じエラーが発生します。 またこの場合、ALTER TABLE の実行結果も反映されていません。(つまり、TEST_FIELD3 が列追加されていない) もちろん、ALTER TABLE だけを実行した場合には、ちゃんと列は追加されます。 その後に、UPDATE を実行すれば、ちゃんと追加列に初期値がセットされます。 どうやら、「一回の SQL 文の実行の中で、ALTER TABLE によって新設した列に対しては、UPDATE などでのアクセスはすぐにはできない」のではないか?という状況のようなのです。一回の SQL 文の実行の中において、何らかのトランザクション動作っぽい挙動を感じます。 つまり、ALTER TABLE で追加された列は、その時点ではまだ完全にシステムに認知されていないため、直後の UPDATE 文で認識できずに失敗するのではないか?と。そして、そこでのエラー発生が、ロールバック的に実行した処理をキャンセルしてしまうため、結果として、ALTER TABLE が実行されなかった事になったり、INSERT が実行されなかった事になったりしているのではないか?と思う次第です。 考えられる回避策としては、SQL 文を別々に作成し、個別に実行すれば良いだけの事なのですが、できれば、SQL 文一つにまとめたいと考えています。 どなたか、こういった現象に対する原因・理由の説明、或いは回避策など、何か情報をお持ちの方はいらっしゃいませんでしょうか? 宜しくお願いします。

  • mysqlでSELECTの速度を上げる方法

    以下のようなSQLを発行すると、mysqlの処理時間が非常に多くかかるため、なんとか最適化を行いたいと考えています。 どのような方法があるのか教えていただけませんしょうか。 SELECT user_id,comment,comment_id,date,study_time,study,source FROM data_temp t1 WHERE NOT EXISTS (select comment_id from data t2 where t1.comment_id = t2.comment_id) ■補足 ・dataとdata_tempのテーブル構造は全く同じです。 ・SQLで実現したいことは、両テーブルのcomment_idをキーとして、dataに含まれないdata_tempの差分データを表示させたい。 なお、以下のインデックス作成は行いましたが、結果変わらずでした。 alter table data t1 ADD INDEX_t1 (user_id,comment,comment_id,date,study_time,study,source); alter table data_temp t1 ADD INDEX_t1 (user_id,comment,comment_id,date,study_time,study,source); よろしくお願いします。

    • ベストアンサー
    • MySQL
  • MySQLに参照整合性制約を実装したテーブルを作成したい

    MySQL4.1.17に、 外部キーで関連付けたテーブルを作成したいのですが、 生成時にエラーが発生してしまいます。 <Error Message> 37000:[MySQL][ODBC 3.51 Driver][mysqld-4.0.17-nt]You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 流しているDDLは以下のとおりです。 テーブル型は両方ともINNODBに設定しています。 どなたか原因がわかる方がいたら、ご教授いただけます でしょうか? よろしくお願いいたします。 CREATE TABLE Entity1( att1 CHAR(10) NOT NULL, PRIMARY KEY (att1) )TYPE=INNODB ; CREATE TABLE Entity2( att1 CHAR(10) NOT NULL, PRIMARY KEY (att1) )TYPE=INNODB ; CREATE INDEX FKEnt2 ON Entity2(att1) ; ALTER TABLE Entity2 ADD FOREIGN KEY (att1) REFERENCES Entity1(att1) ;

    • ベストアンサー
    • MySQL
  • 主キーを追加しようとするとエラー

    MYSQL4.0を使っていますが、 テーブル作成後、主キーを追加しようと ALTER TABLE MYTABLE ADD PRIMARY KEY ID; などと書くと、syntaxエラーになってしまいます。 どこが悪いのでしょうか、 分かる人がいましたら教えてください。

    • ベストアンサー
    • MySQL
  • MySQLのチューニングについて

    MySQLのチューニングについて パソコンのスペック ・windows Vista Home Premium ・プロセッサ Intel(R) Core(TM)2 CPU 4300 @ 1,80GHz 900 MHz ・メモリ(RAM) 2.00 GB ・システムの種類 32ビットオペレーティングシステム XAMPP ・PHP Version 5.2.3 ・Apache 2.2.4(Win32) ・MySQL 5.0.45 phpmyadmin ・フィールドが「名前」「郵便番号」「電話番号」「住所1」「住所2」「住所3」の6つです。 種別がすべてvarchar(30)、照合順序がutf8-unicode-ci, ディスクの使用量が、データが600MB、インデックスが1000バイト。 700万行あります。今後、行数を増やす予定です。 MySQLの設定について(my.cnf) # The MySQL server [mysqld] default-character-set=utf8 skip-character-set-client-handshake basedir="C:/xampp/mysql" tmpdir="C:/xampp/tmp" datadir="C:/xampp/mysql/data" old-passwords character-set-server = utf8 collation-server = utf8_general_ci init-connect = SET NAMES utf8 skip-locking key_buffer = 500M max_allowed_packet = 1M table_cache = 256 sort_buffer_size = 64k read_buffer_size = 256k read_rnd_buffer_size = 256k net_buthread_stack = 64k thread_stack = 64k [mysqldump] default-character-set = utf8 quick max_allowed_packet=100M [mysql] default-character = utf8 no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] key_buffer = 500M sort_buffer_size = 8M [myisamchk] key_buffer = 500M sort_buffer_size = 8M [mysqlhotcopy] interactive-timeout 上記のような状況です。 検索速度を速くするために、チューニングが必要だと思われますが、 どこをどう修正したらよいか教えてください。