普通磁碟與LSI快閃記憶體卡測試對比

說明：併發較大，CPU較繁忙，但是IO不是太busy，說明瓶頸不在IO上。

最終結果：

Initializing random number generator from timer.

Doing OLTP test.

Running mixed OLTP test

Using Special distribution (12 iterations,  1 pct of values are returned in 75 pct cases)

Using "BEGIN" for starting transactions

Using auto_inc on the id column

Maximum number of requests for OLTP test is limited to 1000000

Threads started!

Done.

OLTP test statistics:

    queries performed:

        read:                            14014098

        write:                           5005024

        other:                           2002010

        total:                           21021132

    transactions:                        1001003 (3445.66 per sec.)

    deadlocks:                           4      (0.01 per sec.)

    read/write requests:                 19019122 (65467.84 per sec.)

    other operations:                    2002010 (6891.34 per sec.)

Test execution summary:

    total time:                          290.5109s

    total number of events:              1001003

    total time taken by event execution: 74348.2259

    per-request statistics:

         min:                                  3.41ms

         avg:                                 74.27ms

         max:                                756.14ms

         approx.  95 percentile:             144.95ms

Threads fairness:

    events (avg/stddev):           3910.1680/32.56

    execution time (avg/stddev):   290.4228/0.01

本地盤：

sysbench --num-threads=256 --test=oltp --mysql-user=root --mysql-password=mysql --mysql-table-engine=innodb  --init-rng=on --oltp-table-size=50000000 --max-time=$RT --max-requests=1000000 --mysql-socket=/var/lib/mysql/mysql.sock run > sysbench.log

執行過程中磁碟與CPU情況：

說明：與LSI儲存卡相比，本地磁碟進行OLTP模擬測試時，CPU相對空閒，IO成為瓶頸。

最終結果：

Initializing random number generator from timer.

Doing OLTP test.

Running mixed OLTP test

Using Special distribution (12 iterations,  1 pct of values are returned in 75 pct cases)

Using "BEGIN" for starting transactions

Using auto_inc on the id column

Maximum number of requests for OLTP test is limited to 1000000

Threads started!

Done.

OLTP test statistics:

    queries performed:

        read:                            14013118

        write:                           5004682

        other:                           2001873

        total:                           21019673

    transactions:                        1000936 (959.86 per sec.)

    deadlocks:                           1      (0.00 per sec.)

    read/write requests:                 19017800 (18237.34 per sec.)

    other operations:                    2001873 (1919.72 per sec.)

Test execution summary:

    total time:                          1042.7948s

    total number of events:              1000936

    total time taken by event execution: 266923.0217

    per-request statistics:

         min:                                  3.46ms

         avg:                                266.67ms

         max:                               6884.36ms

         approx.  95 percentile:             649.23ms

Threads fairness:

    events (avg/stddev):           3909.9062/30.04

execution time (avg/stddev):   1042.6681/0.03

結論：使用LSI快閃記憶體卡後，MySQL的TPS提升了260%。其實本測試中的磁碟也是相當高階的儲存，一般磁碟達不到這個數（具體配置我記不清了，可透過下面的IOPS可以看出來）

FIO測試：

進行8K的隨機讀，測試時間為10分鐘

LSI：

fio tmp.txt

tmp.txt

[global]

direct=1

iodepth=32

ioengine=libaio

norandommap

randrepeat=0

numjobs=128

runtime=600

ramp_time=60

thread

group_reporting

name=mytest

[randread]

filename=/dev/sda1

bs=8k

rw=randread

說明：FIO測試是直接對儲存裝置進行讀寫

最終結果：

 [1123MB/0KB/0KB /s] [144K/0/0 iops] [eta 00m:00s]

mytest: (groupid=0, jobs=128): err= 0: pid=9514: Fri Jan 17 15:30:26 2014

  read : io=671818MB, bw=1119.7MB/s, iops=143310, runt=600015msec

    slat (usec): min=3, max=136762, avg=871.03, stdev=4018.21

    clat (usec): min=77, max=237967, avg=27708.34, stdev=11974.67

     lat (usec): min=83, max=237985, avg=28578.69, stdev=12253.82

    clat percentiles (usec):

     |  1.00th=[ 1608],  5.00th=[16768], 10.00th=[17792], 20.00th=[19328],

     | 30.00th=[20352], 40.00th=[21376], 50.00th=[22400], 60.00th=[25984],

     | 70.00th=[35072], 80.00th=[37632], 90.00th=[40704], 95.00th=[45824],

     | 99.00th=[63744], 99.50th=[75264], 99.90th=[94720], 99.95th=[101888],

     | 99.99th=[125440]

    bw (KB  /s): min=    0, max=12931, per=0.78%, avg=8957.79, stdev=980.34

    lat (usec) : 100=0.01%, 250=0.01%, 500=0.01%, 750=0.01%, 1000=0.05%

    lat (msec) : 2=1.96%, 4=0.70%, 10=0.01%, 20=24.54%, 50=68.30%

    lat (msec) : 100=4.39%, 250=0.06%

  說明：60%的延遲在50ms

cpu          : usr=0.39%, sys=2.42%, ctx=6605219, majf=0, minf=8200

  IO depths    : 1=0.1%, 2=0.1%, 4=0.1%, 8=0.1%, 16=0.1%, 32=109.8%, >=64=0.0%

     submit    : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.0%, 64=0.0%, >=64=0.0%

     complete  : 0=0.0%, 4=100.0%, 8=0.0%, 16=0.0%, 32=0.1%, 64=0.0%, >=64=0.0%

     issued    : total=r=85988683/w=0/d=0, short=r=0/w=0/d=0

Run status group 0 (all jobs):

   READ: io=671818MB, aggrb=1119.7MB/s, minb=1119.7MB/s, maxb=1119.7MB/s, mint=600015msec, maxt=600015msec

Disk stats (read/write):

  sda: ios=94385676/0, merge=110/0, ticks=207649589/0, in_queue=207374825, util=100.00%

本地盤：

[global]

direct=1

iodepth=32

ioengine=libaio

norandommap

randrepeat=0

numjobs=128

runtime=600

ramp_time=60

thread

group_reporting

name=mytest

[randread]

filename=/dev/cciss/c0d0p2

bs=8k

rw=randread