MYSQL OLTP测试

128G内存
24核
3块INTEL 480G SSD RAID 5
在本机测试
20个表,每个表1000万测试数据
sysbench /usr/share/sysbench/oltp_read_write.lua –mysql-user=root –mysql-password=123 –mysql-host=127.0.0.1 –threads=2000 –table_size=10000000 –tables=20 –db-driver=mysql –mysql-db=test cleanup

sysbench /usr/share/sysbench/oltp_read_write.lua –mysql-user=root –mysql-password=123 –mysql-host=127.0.0.1 –threads=1000 –table_size=10000000 –tables=20 –db-driver=mysql –mysql-db=test prepare

sysbench /usr/share/sysbench/oltp_read_write.lua –mysql-user=root –mysql-password=123 –mysql-host=127.0.0.1 –threads=100 –table_size=10000000 –tables=20 –db-driver=mysql –mysql-db=test –time=60 –report-interval=10 run

[webadmin@APP-SERVER01 ~]$ sysbench /usr/share/sysbench/oltp_read_only.lua  --mysql-user=root --mysql-password=123 --mysql-host=127.0.0.1 --threads=100 --table_size=10000000 --tables=20 --db-driver=mysql --mysql-db=test --time=60  --report-interval=10 run    
sysbench 1.0.5 (using system LuaJIT 2.0.4)

Running the test with following options:
Number of threads: 100
Report intermediate results every 10 second(s)
Initializing random number generator from current time


Initializing worker threads...

Threads started!

[ 10s ] thds: 100 tps: 119.24 qps: 2020.85 (r/w/o: 1772.38/0.00/248.47) lat (ms,95%): 1213.57 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 100 tps: 124.00 qps: 2000.27 (r/w/o: 1752.26/0.00/248.01) lat (ms,95%): 1304.21 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 100 tps: 202.62 qps: 3190.85 (r/w/o: 2785.62/0.00/405.23) lat (ms,95%): 1050.76 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 100 tps: 366.57 qps: 5893.47 (r/w/o: 5160.34/0.00/733.13) lat (ms,95%): 816.63 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 100 tps: 160.00 qps: 2569.86 (r/w/o: 2249.87/0.00/320.00) lat (ms,95%): 1258.08 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 100 tps: 115.09 qps: 1838.35 (r/w/o: 1608.17/0.00/230.18) lat (ms,95%): 1401.61 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            153664
        write:                           0
        other:                           21952
        total:                           175616
    transactions:                        10976  (182.00 per sec.)
    queries:                             175616 (2912.02 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.3048s
    total number of events:              10976

Latency (ms):
         min:                                 10.30
         avg:                                548.84
         max:                               1949.48
         95th percentile:                   1191.92
         sum:                            6024071.43

Threads fairness:
    events (avg/stddev):           109.7600/2.47
    execution time (avg/stddev):   60.2407/0.08

写入测试

[webadmin@APP-SERVER01 ~]$ sysbench /usr/share/sysbench/oltp_insert.lua  --mysql-user=root --mysql-password=123 --mysql-host=127.0.0.1 --threads=100 --table_size=10000000 --tables=20 --db-driver=mysql --mysql-db=test --time=60  --report-interval=10 run 
sysbench 1.0.5 (using system LuaJIT 2.0.4)

Running the test with following options:
Number of threads: 100
Report intermediate results every 10 second(s)
Initializing random number generator from current time


Initializing worker threads...

Threads started!

[ 10s ] thds: 100 tps: 57906.96 qps: 57906.96 (r/w/o: 0.00/57906.96/0.00) lat (ms,95%): 5.37 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 100 tps: 53038.91 qps: 53039.01 (r/w/o: 0.00/53039.01/0.00) lat (ms,95%): 5.57 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 100 tps: 46636.91 qps: 46636.81 (r/w/o: 0.00/46636.81/0.00) lat (ms,95%): 5.57 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 100 tps: 45.20 qps: 45.20 (r/w/o: 0.00/45.20/0.00) lat (ms,95%): 2680.11 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 100 tps: 0.00 qps: 0.00 (r/w/o: 0.00/0.00/0.00) lat (ms,95%): 0.00 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 100 tps: 4777.08 qps: 4777.08 (r/w/o: 0.00/4777.08/0.00) lat (ms,95%): 6.79 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            0
        write:                           1624470
        other:                           0
        total:                           1624470
    transactions:                        1624470 (27069.45 per sec.)
    queries:                             1624470 (27069.45 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.0091s
    total number of events:              1624470

Latency (ms):
         min:                                  0.08
         avg:                                  3.69
         max:                              33185.19
         95th percentile:                      5.57
         sum:                            5997778.86

Threads fairness:
    events (avg/stddev):           16244.7000/166.43
    execution time (avg/stddev):   59.9778/0.00


读写测试

[webadmin@APP-SERVER01 ~]$ sysbench /usr/share/sysbench/oltp_read_write.lua  --mysql-user=root --mysql-password=123 --mysql-host=127.0.0.1 --threads=50 --table_size=10000000 --tables=20 --db-driver=mysql --mysql-db=test --time=60  --report-interval=10 run   
sysbench 1.0.5 (using system LuaJIT 2.0.4)

Running the test with following options:
Number of threads: 50
Report intermediate results every 10 second(s)
Initializing random number generator from current time


Initializing worker threads...

Threads started!

[ 10s ] thds: 50 tps: 770.39 qps: 15472.47 (r/w/o: 10842.93/3083.76/1545.78) lat (ms,95%): 204.11 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 50 tps: 384.32 qps: 7687.82 (r/w/o: 5383.32/1535.86/768.63) lat (ms,95%): 434.83 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 50 tps: 149.49 qps: 2988.91 (r/w/o: 2091.60/598.34/298.97) lat (ms,95%): 719.92 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 50 tps: 174.32 qps: 3481.74 (r/w/o: 2433.40/699.69/348.64) lat (ms,95%): 590.56 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 50 tps: 110.81 qps: 2222.20 (r/w/o: 1558.14/442.44/221.62) lat (ms,95%): 773.68 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 50 tps: 114.89 qps: 2291.80 (r/w/o: 1605.26/456.76/229.78) lat (ms,95%): 759.88 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            239330
        write:                           68380
        other:                           34190
        total:                           341900
    transactions:                        17095  (284.23 per sec.)
    queries:                             341900 (5684.67 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.1421s
    total number of events:              17095

Latency (ms):
         min:                                  7.67
         avg:                                175.82
         max:                               1387.08
         95th percentile:                    569.67
         sum:                            3005721.30

Threads fairness:
    events (avg/stddev):           341.9000/7.10
    execution time (avg/stddev):   60.1144/0.01


[webadmin@APP-SERVER01 ~]$ sysbench /usr/share/sysbench/oltp_read_write.lua  --mysql-user=root --mysql-password=212 --mysql-host=127.0.0.1 --threads=200 --table_size=10000000 --tables=20 --db-driver=mysql --mysql-db=test --time=60  --report-interval=10 run 
sysbench 1.0.5 (using system LuaJIT 2.0.4)

Running the test with following options:
Number of threads: 200
Report intermediate results every 10 second(s)
Initializing random number generator from current time


Initializing worker threads...

Threads started!

[ 10s ] thds: 200 tps: 158.67 qps: 3417.32 (r/w/o: 2445.04/634.97/337.32) lat (ms,95%): 3208.88 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 200 tps: 221.00 qps: 4388.88 (r/w/o: 3061.86/885.02/442.01) lat (ms,95%): 1903.57 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 200 tps: 172.90 qps: 3503.66 (r/w/o: 2466.64/691.21/345.81) lat (ms,95%): 2159.29 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 200 tps: 307.42 qps: 6109.70 (r/w/o: 4259.21/1235.66/614.83) lat (ms,95%): 1678.14 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 200 tps: 298.23 qps: 5953.67 (r/w/o: 4168.70/1188.51/596.46) lat (ms,95%): 1589.90 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 200 tps: 252.02 qps: 5058.85 (r/w/o: 3546.55/1008.27/504.04) lat (ms,95%): 2880.27 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            200242
        write:                           57212
        other:                           28606
        total:                           286060
    transactions:                        14303  (233.82 per sec.)
    queries:                             286060 (4676.47 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          61.1674s
    total number of events:              14303

Latency (ms):
         min:                                  8.03
         avg:                                849.84
         max:                               7311.23
         95th percentile:                   2238.47
         sum:                            12155277.54

Threads fairness:
    events (avg/stddev):           71.5150/5.63
    execution time (avg/stddev):   60.7764/0.34


[webadmin@APP-SERVER01 ~]$ sysbench /usr/share/sysbench/oltp_read_write.lua  --mysql-user=root --mysql-password=123 --mysql-host=127.0.0.1 --threads=100 --table_size=10000000 --tables=20 --db-driver=mysql --mysql-db=test --time=60  --report-interval=10 run 
sysbench 1.0.5 (using system LuaJIT 2.0.4)

Running the test with following options:
Number of threads: 100
Report intermediate results every 10 second(s)
Initializing random number generator from current time


Initializing worker threads...

Threads started!

[ 10s ] thds: 100 tps: 206.47 qps: 4257.69 (r/w/o: 3007.19/827.57/422.93) lat (ms,95%): 1213.57 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 100 tps: 148.77 qps: 2976.03 (r/w/o: 2084.32/594.17/297.53) lat (ms,95%): 1376.60 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 100 tps: 195.79 qps: 3899.98 (r/w/o: 2724.43/783.97/391.58) lat (ms,95%): 977.74 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 100 tps: 217.31 qps: 4334.21 (r/w/o: 3031.77/867.82/434.61) lat (ms,95%): 1170.65 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 100 tps: 158.30 qps: 3171.17 (r/w/o: 2221.28/633.29/316.60) lat (ms,95%): 1327.91 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 100 tps: 163.24 qps: 3277.62 (r/w/o: 2297.10/654.04/326.47) lat (ms,95%): 1280.93 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            153944
        write:                           43984
        other:                           21992
        total:                           219920
    transactions:                        10996  (181.81 per sec.)
    queries:                             219920 (3636.13 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          60.4797s
    total number of events:              10996

Latency (ms):
         min:                                 12.75
         avg:                                548.68
         max:                               3123.89
         95th percentile:                   1213.57
         sum:                            6033237.94

Threads fairness:
    events (avg/stddev):           109.9600/4.20
    execution time (avg/stddev):   60.3324/0.15

发表评论

电子邮件地址不会被公开。