MySQL分區和分布性能測試[轉]

MySQL分區性能測試

By 成江東, on 八月 31st, 2010

一，測試伺服器配置：

Cpu: 2*4核Intel(R) Xeon(R) E5405 @ 2.00GHz

記憶體：4G

存儲：36塊1T的7.2K的SATA組成raid0。使用其中的1T

Mysql版本：5.1.45-community-log MySQL Community Server (GPL)

二，建測試表：

Myisam不分區不分表，單表10億資料量：

create table biguser

(

   ID                   bigint not null auto_increment comment ‘自增ID’,

   Username             varchar(30) not null comment ‘使用者名’,

   sex                  tinyint not null comment ‘性别’,

   birthday             datetime not null comment ‘生日’,

   email                varchar(50) not null comment ‘郵箱’,

   tel                  int not null comment ‘手機’,

   intro                varchar(255) not null comment ‘個人簡介’,

   primary key (ID)

)

type = MYISAM

default charset=utf8;

Myisam不分區，分10個表，單表1億資料量：

create table user01

*一共10個表,user01,user02,…,user10

Myisam用id進行hash分區，分10個區，每個分區1億資料：

create table puser

default charset=utf8

partition by hash(ID)

partitions 10;  

Innodb用id進行hash分區，分10個區，每個分區1億資料：

create table iuser

type = Innodb

partitions 10;

Innodb不分區不分表，單表10億資料量：

create table ibiguser

   email                varchar(50) not null comment ‘郵箱’,

三，資料生成

利用power design生成100萬不重複的資料的基礎表，然後将此表資料用mysqlslap工具重複插入到目标表，可以快速生成海量數量表。最終資料檔案大小都在170G左右(分表情況單表17G資料檔案)

插入100萬記錄到目标表的性能如下

機關（秒）：

Myisam不分區

Myisam分區

Innodb不分區

Innodb分區

10.514

9.114

16.258

16.893

可見非并發的大量資料插入myisam分區性能最佳，myisam不分區次之，innodb分區較差。

四，壓力測試

利用mysql5.1自帶的壓力測試工具mysqlslap對以上四種情況進行了壓力測試，測試結果如下(機關：秒)：

　

myisam

未分區

分區

Myisam

分表

Innodb

innodb

100連續随機查詢

4.55

4.11

5.389

4.445

5.893

并發50随機查詢

0.052

0.049

0.075

0.07

0.038

并發100随機查詢

0.071

0.078

0.11

0.088

0.055

并發250随機查詢

0.137

0.153

0.239

0.175

0.124

并發500随機查詢

0.266

0.273

0.447

0.283

0.24

并發1000随機查詢

0.5

0.533

0.849

0.485

0.46

并發50随機插入

0.037

0.03

0.04

0.042

0.025

并發100随機插入

0.06

0.057

并發250随機插入

0.179

0.159

0.144

0.135

0.132

并發500随機插入

0.384

0.327

0.332

0.248

0.25

并發1000随機插入

0.82

0.73

0.737

0.542

0.526

并發50随機删除

1.204

1.536

0.196

0.041

0.039

并發100随機删除

2.333

2.945

0.362

0.062

0.063

并發250随機删除

5.568

5.718

0.831

0.14

0.173

并發500随機删除

9.663

9.705

1.42

0.275

0.308

并發1000随機删除

19.877

17.254

2.655

0.589

0.634

并發50随機更新

1.008

0.887

0.298

0.044

0.046

并發100随機更新

1.789

1.688

0.064

并發250随機更新

4.291

3.986

1.029

0.134

0.145

并發500随機更新

8.357

7.872

1.783

0.306

0.288

并發1000随機更新

15.963

15.126

3.33

0.575

0.563

*運作了一個100條語句的批查詢，看非并發情況下的查詢效率，查詢語句見附一。

*利用存儲過程進行一個hash處理，去對不同的表進行資料操作，見附二。

五，結論：

一，在高并發情況下，innodb的分區表的增，删，改，查的性能都優于myisam引擘，特别是在高并發删除和更新的場合下，兩者相差可以達到20-30倍。

二，myisam的分區表(10個分區)和未分區表的性能相差不大，略優于未分區表。

三，myisam分區表(10個分區)的插入性能與分表(10個表)差不多，但查詢性能要好30%左右。但删除與更新的性能分表要遠遠好于分區。

四，innodb分區表和未分區表查詢和插入在低并發下，相差40%。但在并發性不斷增加的情況下，性能相差不大。删除和更新性能則一直相近。

附一：

select sql_no_cache * from biguser where id=       14988469         ;

select sql_no_cache * from biguser where id=       57611709         ;

select sql_no_cache * from biguser where id=       88737417         ;

select sql_no_cache * from biguser where id=       5487431  ;

select sql_no_cache * from biguser where id=       75912772         ;

select sql_no_cache * from biguser where id=       39680341         ;

select sql_no_cache * from biguser where id=       33444232         ;

select sql_no_cache * from biguser where id=       53937959         ;

select sql_no_cache * from biguser where id=       29031225         ;

select sql_no_cache * from biguser where id=       1256108  ;

select sql_no_cache * from biguser where id=       25314369         ;

select sql_no_cache * from biguser where id=       24296361         ;

select sql_no_cache * from biguser where id=       65966713         ;

select sql_no_cache * from biguser where id=       14395018         ;

select sql_no_cache * from biguser where id=       69197350         ;

select sql_no_cache * from biguser where id=       90962562         ;

select sql_no_cache * from biguser where id=       67908690         ;

select sql_no_cache * from biguser where id=       34352967         ;

select sql_no_cache * from biguser where id=       76458078         ;

select sql_no_cache * from biguser where id=       9986401  ;

select sql_no_cache * from biguser where id=       92969693         ;

select sql_no_cache * from biguser where id=       21688034         ;

select sql_no_cache * from biguser where id=       23534033         ;

select sql_no_cache * from biguser where id=       49682571         ;

select sql_no_cache * from biguser where id=       70230582         ;

select sql_no_cache * from biguser where id=       23174504         ;

select sql_no_cache * from biguser where id=       54671332         ;

select sql_no_cache * from biguser where id=       60255403         ;

select sql_no_cache * from biguser where id=       33064209         ;

select sql_no_cache * from biguser where id=       30657185         ;

select sql_no_cache * from biguser where id=       98478707         ;

select sql_no_cache * from biguser where id=       27247625         ;

select sql_no_cache * from biguser where id=       20287058         ;

select sql_no_cache * from biguser where id=       67371577         ;

select sql_no_cache * from biguser where id=       18519687         ;

select sql_no_cache * from biguser where id=       55236080         ;

select sql_no_cache * from biguser where id=       21413273         ;

select sql_no_cache * from biguser where id=       2928926  ;

select sql_no_cache * from biguser where id=       82738229         ;

select sql_no_cache * from biguser where id=       47525876         ;

select sql_no_cache * from biguser where id=       19732304         ;

select sql_no_cache * from biguser where id=       63153463         ;

select sql_no_cache * from biguser where id=       1685048  ;

select sql_no_cache * from biguser where id=       4675229  ;

select sql_no_cache * from biguser where id=       14525956         ;

select sql_no_cache * from biguser where id=       86972953         ;

select sql_no_cache * from biguser where id=       21576455         ;

select sql_no_cache * from biguser where id=       5341705  ;

select sql_no_cache * from biguser where id=       66182739         ;

select sql_no_cache * from biguser where id=       54662774         ;

select sql_no_cache * from biguser where id=       59012070         ;

select sql_no_cache * from biguser where id=       52293127         ;

select sql_no_cache * from biguser where id=       29983640         ;

select sql_no_cache * from biguser where id=       21078779         ;

select sql_no_cache * from biguser where id=       14937593         ;

select sql_no_cache * from biguser where id=       17458594         ;

select sql_no_cache * from biguser where id=       7928885  ;

select sql_no_cache * from biguser where id=       73174679         ;

select sql_no_cache * from biguser where id=       21741457         ;

select sql_no_cache * from biguser where id=       86001336         ;

select sql_no_cache * from biguser where id=       52555158         ;

select sql_no_cache * from biguser where id=       23325556         ;

select sql_no_cache * from biguser where id=       63580254         ;

select sql_no_cache * from biguser where id=       83699681         ;

select sql_no_cache * from biguser where id=       10742003         ;

select sql_no_cache * from biguser where id=       81051582         ;

select sql_no_cache * from biguser where id=       45963004         ;

select sql_no_cache * from biguser where id=       95947261         ;

select sql_no_cache * from biguser where id=       19606015         ;

select sql_no_cache * from biguser where id=       24078829         ;

select sql_no_cache * from biguser where id=       676655     ;

select sql_no_cache * from biguser where id=       30713754         ;

select sql_no_cache * from biguser where id=       29905848         ;

select sql_no_cache * from biguser where id=       99763851         ;

select sql_no_cache * from biguser where id=       1495837  ;

select sql_no_cache * from biguser where id=       71316631         ;

select sql_no_cache * from biguser where id=       88798686         ;

select sql_no_cache * from biguser where id=       94214594         ;

select sql_no_cache * from biguser where id=       54310537         ;

select sql_no_cache * from biguser where id=       81828596         ;

select sql_no_cache * from biguser where id=       89927714         ;

select sql_no_cache * from biguser where id=       72684768         ;

select sql_no_cache * from biguser where id=       50764835         ;

select sql_no_cache * from biguser where id=       50720945         ;

select sql_no_cache * from biguser where id=       63690819         ;

select sql_no_cache * from biguser where id=       14890423         ;

select sql_no_cache * from biguser where id=       94762181         ;

select sql_no_cache * from biguser where id=       45487268         ;

select sql_no_cache * from biguser where id=       50795204         ;

select sql_no_cache * from biguser where id=       43415976         ;

select sql_no_cache * from biguser where id=       44953453         ;

select sql_no_cache * from biguser where id=       83626479         ;

select sql_no_cache * from biguser where id=       42730160         ;

select sql_no_cache * from biguser where id=       15229715         ;

select sql_no_cache * from biguser where id=       22058075         ;

select sql_no_cache * from biguser where id=       35277893         ;

select sql_no_cache * from biguser where id=       90205735         ;

select sql_no_cache * from biguser where id=       12501310         ;

select sql_no_cache * from biguser where id=       24653162         ;

select sql_no_cache * from biguser where id=       34248456         ;

附二：

use nopart ;

drop procedure p_hash_select;

delimiter //

create procedure p_hash_select(vid bigint)

begin

set @i=mod(vid,10);

if @i=1 then

  select * from user01 where;

end if;

if @i=2 then

  select * from user02 where;

if @i=3 then

  select * from user03 where;

if @i=4 then

  select * from user04 where;

if @i=5 then

  select * from user05 where;

if @i=6 then

  select * from user06 where;

if @i=7 then

  select * from user07 where;

if @i=8 then

  select * from user08 where;

if @i=9 then

  select * from user09 where;

if @i=0 then

  select * from user10 where;

end//

d