准备工作：
CREATE TABLE `test` (`id` int(11) NOT NULL, `name` varchar(1000) NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8;
delimiter |
create procedure p_test()
begindeclare max int default 100000;declare i int default 1;while i<max doinsert into test values(1,'helloworldfucksd');set i=i+1;end while;end |
执行 call p_test()，报错了：
ERROR 1436 (HY000): Thread stack overrun: 6656 bytes used of a 131072 byte stack, and 128000 bytes needed. Use 'mysqld --thread_stack=#' to specify a bigger stack.
错误原因是 thread_stack太小，默认的128K，建议调整到192K
测试结果
MyISAM:
mysql> call p_test();
Query OK, 1 row affected (1.97 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> truncate table test;
Query OK, 0 rows affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> optimize table test;
+-------------+----------+----------+-----------------------------+
| Table | Op | Msg_type | Msg_text |
+-------------+----------+----------+-----------------------------+
| python.test | optimize | status | Table is already up to date |
+-------------+----------+----------+-----------------------------+
1 row in set (0.00 sec)
Query OK, 1 row affected (1.97 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> truncate table test;
Query OK, 0 rows affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> optimize table test;
+-------------+----------+----------+-----------------------------+
| Table | Op | Msg_type | Msg_text |
+-------------+----------+----------+-----------------------------+
| python.test | optimize | status | Table is already up to date |
+-------------+----------+----------+-----------------------------+
1 row in set (0.00 sec)
InnoDB
首先切换到InnoDB，
ALTER TABLE test ENGINE = InnoDBsho tables status:
mysql> show table status \G
*************************** 1. row ***************************Name: testEngine: InnoDBVersion: 10Row_format: CompactRows: 20375Avg_row_length: 77Data_length: 1589248
Max_data_length: 0Index_length: 0Data_free: 6291456Auto_increment: NULLCreate_time: 2012-05-09 18:34:27Update_time: NULLCheck_time: NULLCollation: utf8_general_ciChecksum: NULLCreate_options: Comment:
1 row in set (0.00 sec)
测试了下，非常慢，10分钟左右才2W行：
yunpeng@yunpeng-duitang:/duitang/dist/conf/mysql$ vmstat 1
procs -----------memory---------- ---swap-- -----io---- -system-- ----cpu----r b swpd free buff cache si so bi bo in cs us sy id wa1 1 16 2073808 114884 1740028 0 0 18 122 86 45 7 13 79 11 1 16 2073248 115068 1740500 0 0 0 368 3282 3067 4 13 59 241 1 16 2072624 115276 1740620 0 0 0 420 3006 3110 4 15 58 230 1 16 2073076 115428 1740384 0 0 0 4400 3070 3250 4 15 57 251 1 16 2072732 115572 1740232 0 0 0 508 2973 3048 3 15 58 230 1 16 2073132 115780 1740184 0 0 0 460 3819 4080 4 15 58 241 1 16 2072240 115956 1740644 0 0 0 352 3340 3220 5 13 59 231 1 16 2072272 116140 1740648 0 0 0 372 3584 3105 3 14 59 240 1 16 2072136 116332 1740424 0 0 0 388 3310 3120 3 14 60 230 1 16 2071928 116508 1740496 0 0 0 524 3566 3224 4 14 58 241 1 16 2071620 116708 1740912 0 0 0 404 3777 4114 5 13 60 221 1 16 2071512 116900 1740632 0 0 0 428 3335 3268 3 15 58 241 1 16 2071200 117068 1740656 0 0 0 336 3038 3190 3 14 59 241 1 16 2071668 117276 1740204 0 0 0 420 3633 3308 3 13 60 242 1 16 2071420 117436 1740220 0 0 0 4792 4610 3681 3 14 62 21
InnoDB,关闭autocommit
set autocommit=0;
select @@autocommit; //查看一下autocommit的设置
mysql> call p_test();
Query OK, 1 row affected (2.54 sec)mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
| 99999 |
+----------+
1 row in set (0.05 sec)mysql> commit;
Query OK, 0 rows affected (0.04 sec)