결론:
Repair with keycache => 82188 초 대기 후에도 작업이 완료되지 않아 도중에 포기
Repair by sorting => 15분만에 완료
seq,mail_id,sender_ip,sender_email,user_name,empno,dept,mail_type,att_action,filter,header_subject,virus_name,iscontent,user_group,user_domain,key_send_state,key_send_date,permit_state,permit_action,permit_date,do_permit_id,do_permit_name,date,hostname,mail_size,encrypt_key,link_active,comment,matched_prv,body_prv_cnt,receiver_ip
mysql> show processlist;
+-------+------+-----------+------+---------+-------+----------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+------+-----------+------+---------+-------+----------------------+------------------------------------------------------------------------------------------------------+
| 77750 | root | localhost | spam | Query | 82188 | Repair with keycache | insert into o(seq,mail_id,sender_ip,sender_email,user_name,empno,dept,mail_type,att_action,filter,he |
+-------+------+-----------+------+---------+-------+----------------------+------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
insert into o(seq,mail_id,sender_ip,sender_email,user_name,empno,dept,mail_type,att_action,filter,header_subject,virus_name,iscontent,user_group,user_domain,key_send_state,key_send_date,permit_state,permit_action,permit_date,do_permit_id,do_permit_name,date,hostname,mail_size,encrypt_key,link_active,comment,matched_prv,body_prv_cnt,receiver_ip)select seq,mail_id,sender_ip,sender_email,user_name,empno,dept,mail_type,att_action,filter,header_subject,virus_name,iscontent,user_group,user_domain,key_send_state,key_send_date,permit_state,permit_action,permit_date,do_permit_id,do_permit_name,date,hostname,mail_size,encrypt_key,link_active,comment,matched_prv,body_prv_cnt,receiver_ip from outbound;
-rw-rw---- 1 mysql mysql 18054 Jan 28 15:06 outbound.frm
-rw-rw---- 1 mysql mysql 1377832444 Feb 24 20:11 outbound.MYD
-rw-rw---- 1 mysql mysql 2125569024 Feb 24 20:13 outbound.MYI
mysql> show variables like '%sort%';
+---------------------------+------------+
| Variable_name | Value |
+---------------------------+------------+
| myisam_max_sort_file_size | 2146435072 |
+---------------------------+------------+
5 rows in set (0.55 sec)
mysql> show table status;
+---------------+--------+---------+------------+----------+----------------+-------------+-------------------+--------------+-----------+----------------+-------------------------------------------------------------------------------------------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time Comment |
+---------------+--------+---------+------------+----------+----------------+-------------+-------------------+--------------+-----------+----------------+-------------------------------------------------------------------------------------------+
| outbound | MyISAM | 10 | Dynamic | 9989521 | 137 | 1377832444 | 281474976710655 | 2125569024 | 1444904 | 10000201 | 2014-01-28 18:2 |
+---------------+--------+---------+------------+----------+----------------+-------------+-------------------+--------------+-----------+----------------+-------------------------------------------------------------------------------------------+
25 rows in set (0.07 sec)
아무리 기다려도 작업이 끝나지 않는다.
mysql> set global myisam_max_sort_file_size=10737418240;
set global myisam_max_sort_file_size=30737418240;
Query OK, 0 rows affected (0.01 sec)
mysql> show processlist;
+----+------+-----------+------+---------+------+-------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------------------+------------------------------------------------------------------------------------------------------+
| 3 | root | localhost | spam | Query | 259 | Repair by sorting | insert into o(seq,mail_id,sender_ip,sender_email,user_name,empno,dept,mail_type,att_action,filter,he |
+----+------+-----------+------+---------+------+-------------------+------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
mysql> insert into o(seq,mail_id,sender_ip,sender_email,user_name,empno,dept,mail_type,att_action,filter,header_subject,virus_name,iscontent,user_group,user_domain,key_send_state,key_send_date,permit_state,permit_action,permit_date,do_permit_id,do_permit_name,date,hostname,mail_size,encrypt_key,link_active,comment,matched_prv,body_prv_cnt,receiver_ip)select seq,mail_id,sender_ip,sender_email,user_name,empno,dept,mail_type,att_action,filter,header_subject,virus_name,iscontent,user_group,user_domain,key_send_state,key_send_date,permit_state,permit_action,permit_date,do_permit_id,do_permit_name,date,hostname,mail_size,encrypt_key,link_active,comment,matched_prv,body_prv_cnt,receiver_ip from outbound;
Query OK, 9989521 rows affected (15 min 57.47 sec)
Records: 9989521 Duplicates: 0 Warnings: 0
천명재 7년전 질문
