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