mysql innodb,MyISAM 两种发动机测试
日期：2014-05-16 浏览次数：20567 次
mysql innodb,MyISAM 两种引擎测试
准备工作：
?
CREATE TABLE `test` ( `id` int(11) NOT NULL, `name` varchar(1000) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
?
delimiter |
?
?
create procedure p_test()
begin
declare max int default 100000;
declare i int default 1;
while i<max do
insert 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 = InnoDB
sho tables status:
mysql> show table status \G
*************************** 1. row ***************************
Name: test
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 20375
Avg_row_length: 77
Data_length: 1589248
Max_data_length: 0
Index_length: 0
Data_free: 6291456
Auto_increment: NULL
Create_time: 2012-05-09 18:34:27
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_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 wa 1 1 16 2073808 114884 1740028 0 0 18 122 86 45 7 13 79 1 1 1 16 2073248 115068 1740500 0 0 0 368 3282 3067 4 13 59 24 1 1 16 2072624 115276 1740620 0 0 0 420 3006 3110 4 15 58 23 0 1 16 2073076 115428 1740384 0 0 0 4400 3070 3250 4 15 57 25 1 1 16 2072732 115572 1740232 0 0 0 508 2973 3048 3 15 58 23 0 1 16 2073132 115780 1740184 0 0 0 460 3819 4080 4 15 58 24 1 1 16 2072240 115956 1740644 0 0 0 352 3340 3220 5 13 59 23 1 1 16 2072272 116140 1740648 0 0 0 372 3584 3105 3 14 59 24 0 1 16 2072136 116332 1740424 0 0 0 388 3310 3120 3 14 60 23 0 1 16 2071928 116508 1740496 0 0 0 524 3566 3224 4 14 58 24 1 1 16 2071620 116708 1740912 0 0 0 404 3777 4114 5 13 60 22 1 1 16 2071512 116900 1740632 0 0 0 428 3335 3268 3 15 58 24 1 1 16 2071200 117068 1740656 0 0 0 336 3038 3190 3 14 59 24 1 1 16 2071668 117276 1740204 0 0 0 420 3633 3308 3 13 60 24 2 1 16 2071420 117436 1740220 0 0 0 4792 4610 3681 3 14 62 21
?
InnoDB,关闭autocommit
?
set?autocommit=0;?
sel
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
