mysql:15477
From: 秋元 訓 <秋元 訓 <akimoto@xxxxxxxxxx>>
Date: Fri, 14 Jan 2011 12:11:40 +0900
Subject: [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@xxxxxxxxxx