2011年1月14日

[mysql 15477] ALTER TABLE ENGINEを実行後の内部処理の変化について質問

はじめまして、秋元と申します。

MySQL(InnoDB)+オープンソースのSugarCRMを使用しており、
メンテナンスとして次のコマンドをmysql上で実行した所、極端に処理が重くな
りました。

> ALTER TABLE accounts ENGINE = 'InnoDB';
(DB内の全てのテーブルに対し実行)

調べてみるとテンポラリファイル(/tmp/に4.2GB程のMyISAMファイル)を都度生成
していました。
show processlistなどより問題のSQLクエリを探し出し、EXPLAINを掛けてみた所
メンテ前とメンテ後ではEXPLAINの結果に次の相違点がある事が解りました。
・Extraの部分に、Using temporary; Using filesort が付く様になった
・対象tableの順番が1番目と2番目が入れ替わっている
・使用されているkeyが異なっている

ALTER TABLE〜を掛けると、MySQLの内部処理の順番やテンポラリテーブルを使用
する/しないなど、変わる物なのでしょうか?

---EXPLAINの結果の為、行が長くなっております---
■メンテ前EXPLAIN結果
+----+--------------------+------------------+--------+---------------------------------------------------------------------------------+-----------------------+---------+---------------------------------------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+------------------+--------+---------------------------------------------------------------------------------+-----------------------+---------+---------------------------------------+-------+-------------+
| 1 | PRIMARY | contacts | ref | idx_contacts_del_last,idx_cont_del_reports,idx_team_del_id_user,idx_del_id_user | idx_contacts_del_last | 2 | const | 61185 | Using where |
| 1 | PRIMARY | team_memberships | ref | idx_team_membership,idx_teammemb_team_user | idx_team_membership | 218 | const,sugarcrm_db.contacts.team_id | 1 | Using where |
| 1 | PRIMARY | contacts_cstm | eq_ref | PRIMARY | PRIMARY | 108 | sugarcrm_db.contacts.id | 1 | |
| 1 | PRIMARY | jtl0 | ref | idx_contid_del_accid | idx_contid_del_accid | 112 | sugarcrm_db.contacts.id,const | 1 | Using index |
| 1 | PRIMARY | accounts | eq_ref | PRIMARY,idx_accnt_id_del,idx_accnt_assigned_del | PRIMARY | 108 | sugarcrm_db.jtl0.account_id | 1 | |
| 1 | PRIMARY | jt1 | eq_ref | PRIMARY | PRIMARY | 108 | sugarcrm_db.contacts.assigned_user_id | 1 | |
| 2 | DEPENDENT SUBQUERY | eabr | ref | idx_email_address_id,idx_bean_id | idx_bean_id | 108 | func | 1 | Using where |
| 2 | DEPENDENT SUBQUERY | ea | eq_ref | PRIMARY,idx_ea_opt_out_invalid | PRIMARY | 108 | sugarcrm_db.eabr.email_address_id | 1 | Using where |
+----+--------------------+------------------+--------+---------------------------------------------------------------------------------+-----------------------+---------+---------------------------------------+-------+-------------+
8 rows in set (0.13 sec)

■メンテ後EXPLAIN結果
+----+--------------------+------------------+--------+---------------------------------------------------------------------------------+----------------------+---------+--------------------------------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+------------------+--------+---------------------------------------------------------------------------------+----------------------+---------+--------------------------------------------+------+----------------------------------------------+
| 1 | PRIMARY | team_memberships | ref | idx_team_membership,idx_teammemb_team_user | idx_team_membership | 109 | const | 69 | Using where; Using temporary; Using filesort |
| 1 | PRIMARY | contacts | ref | idx_contacts_del_last,idx_cont_del_reports,idx_team_del_id_user,idx_del_id_user | idx_team_del_id_user | 111 | sugarcrm_db.team_memberships.team_id,const | 467 | Using where |
| 1 | PRIMARY | contacts_cstm | eq_ref | PRIMARY | PRIMARY | 108 | sugarcrm_db.contacts.id | 1 | |
| 1 | PRIMARY | jtl0 | ref | idx_contid_del_accid | idx_contid_del_accid | 112 | sugarcrm_db.contacts.id,const | 1 | Using index |
| 1 | PRIMARY | accounts | eq_ref | PRIMARY,idx_accnt_id_del,idx_accnt_assigned_del | PRIMARY | 108 | sugarcrm_db.jtl0.account_id | 1 | |
| 1 | PRIMARY | jt1 | eq_ref | PRIMARY | PRIMARY | 108 | sugarcrm_db.contacts.assigned_user_id | 1 | |
| 2 | DEPENDENT SUBQUERY | eabr | ref | idx_email_address_id,idx_bean_id | idx_bean_id | 108 | func | 1 | Using where |
| 2 | DEPENDENT SUBQUERY | ea | eq_ref | PRIMARY,idx_ea_opt_out_invalid | PRIMARY | 108 | sugarcrm_db.eabr.email_address_id | 1 | Using where |
+----+--------------------+------------------+--------+---------------------------------------------------------------------------------+----------------------+---------+--------------------------------------------+------+----------------------------------------------+

■環境
MySQL:5.1.3
InnoDBの実データは共有ファイル(ibdata1)内に存在
PHP:5.2.9
OS:CentOS5.3 32bit
連携しているアプリ:SugarCRM


以上、宜しくお願い致します。

-----
秋元 訓 akimoto@xxxxx

投稿者 xml-rpc : 2011年1月14日 12:11
役に立ちました?:
過去のフィードバック 平均:(0) 総合:(0) 投票回数:(0)
本記事へのTrackback: http://hoop.euqset.org/blog/mt-tb2006.cgi/101309
トラックバック
コメント
コメントする




画像の中に見える文字を入力してください。