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)

 

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)