mysql myisam 测试_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

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)

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;

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)

分享到：

2012-05-09 12:20

浏览 1011

分类:数据库

评论