2020-09-29T11:43:29.749694+08:00 17 Connect root@192.168.0.19 on sysbench using TCP/IP
2020-09-29T11:43:29.750392+08:00 17 Query CREATE TABLE sbtest1(
id INTEGER NOT NULL AUTO_INCREMENT,
k INTEGER DEFAULT '0' NOT NULL,
c CHAR(120) DEFAULT '' NOT NULL,
pad CHAR(60) DEFAULT '' NOT NULL,
PRIMARY KEY (id)
) /*! ENGINE = innodb */
2020-09-29T11:43:29.760654+08:00 17 Query INSERT INTO sbtest1(k, c, pad) VALUES(5, '83868641912-28773972837-60736120486-75162659906-27563526494-20381887404-41576422241-93426793964-56405065102-33518432330', '67847967377-48000963322-62604785301-91415491898-96926520291'),(6, '38014276128-25250245652-62722561801-27818678124-24890218270-18312424692-92565570600-36243745486-21199862476-38576014630', '23183251411-36241541236-31706421314-92007079971-60663066966'),(6, '33973744704-80540844748-72700647445-87330233173-87249600839-07301471459-22846777364-58808996678-64607045326-48799346817', '38615512647-91458489257-90681424432-95014675832-60408598704'),(6, '37002370280-58842166667-00026392672-77506866252-09658311935-56926959306-83464667271-94685475868-28264244556-14550208498', '63947013338-98809887124-59806726763-79831528812-45582457048'),(5, '44257470806-17967007152-32809666989-26174672567-29883439075-95767161284-94957565003-35708767253-53935174705-16168070783', '34551750492-67990399350-81179284955-79299808058-21257255869'),(6, '37216201353-39109531021-11197415756-87798784755-02463049870-83329763120-57551308766-61100580113-80090253566-30971527105', '05161542529-00085727016-35134775864-52531204064-98744439797'),(6, '33071042495-29920376648-91343430102-79082003121-73317691963-02846712788-88069761578-14885283975-44409837760-90760298045', '91798303270-64988107984-08161247972-12116454627-22996445111'),(5, '73754818686-04889373966-18668178968-56957589012-31352882173-91882653509-59577900152-88962682169-52981807259-62646890059', '76460662325-41613089656-42706083314-81833284991-17063140920'),(5, '26482547570-00155460224-12388481921-23289186371-78242522654-77998886134-73270876420-50821093220-31442690639-11588920653', '30508501104-50823269125-88107014550-70202920684-95842308929'),(6, '05677017559-47107518969-97509137401-28934334557-14497052050-61906823704-44077628507-24840441785-05187301456-27797851637', '29489382504-13697582598-09964978366-26554639515-36136545002')
2020-09-29T11:43:29.763016+08:00 17 Query CREATE INDEX k_1 ON sbtest1(k)
2020-09-29T11:43:29.771401+08:00 17 Quit
sysbench --config-file=config oltp_update_index --time=10 --tables=1 --table-size=10 --threads=1 run
2020-09-29T11:46:40.199948+08:00 20 Connect root@192.168.0.19 on sysbench using TCP/IP
2020-09-29T11:46:40.211026+08:00 20 Prepare UPDATE sbtest1 SET k=k+1 WHERE id=?
2020-09-29T11:46:40.211239+08:00 20 Execute UPDATE sbtest1 SET k=k+1 WHERE id=5
2020-09-29T11:46:40.254278+08:00 20 Execute UPDATE sbtest1 SET k=k+1 WHERE id=4
2020-09-29T11:46:40.255556+08:00 20 Execute UPDATE sbtest1 SET k=k+1 WHERE id=6
2020-09-29T11:46:40.256682+08:00 20 Execute UPDATE sbtest1 SET k=k+1 WHERE id=5
2020-09-29T11:46:40.257741+08:00 20 Execute UPDATE sbtest1 SET k=k+1 WHERE id=6
2020-09-29T11:46:40.258642+08:00 20 Execute UPDATE sbtest1 SET k=k+1 WHERE id=6
2020-09-29T11:46:40.263014+08:00 20 Execute UPDATE sbtest1 SET k=k+1 WHERE id=6
2020-09-29T11:46:40.269928+08:00 20 Execute UPDATE sbtest1 SET k=k+1 WHERE id=5
2020-09-29T11:46:40.271010+08:00 20 Execute UPDATE sbtest1 SET k=k+1 WHERE id=6
2020-09-29T11:46:40.271982+08:00 20 Execute UPDATE sbtest1 SET k=k+1 WHERE id=6
2020-09-29T11:46:40.272904+08:00 20 Execute UPDATE sbtest1 SET k=k+1 WHERE id=6
2020-09-29T11:46:40.273860+08:00 20 Execute UPDATE sbtest1 SET k=k+1 WHERE id=6
2020-09-29T11:46:40.274745+08:00 20 Execute UPDATE sbtest1 SET k=k+1 WHERE id=5
2020-09-29T11:46:40.275723+08:00 20 Execute UPDATE sbtest1 SET k=k+1 WHERE id=6
2020-09-29T11:46:40.276628+08:00 20 Execute UPDATE sbtest1 SET k=k+1 WHERE id=6
2020-09-29T11:46:40.277582+08:00 20 Execute UPDATE sbtest1 SET k=k+1 WHERE id=6
2020-09-29T11:46:40.278623+08:00 20 Execute UPDATE sbtest1 SET k=k+1 WHERE id=6
2020-09-29T11:46:40.280033+08:00 20 Execute UPDATE sbtest1 SET k=k+1 WHERE id=6
2020-09-29T11:46:40.280925+08:00 20 Execute UPDATE sbtest1 SET k=k+1 WHERE id=6
2020-09-29T11:46:40.281893+08:00 20 Execute UPDATE sbtest1 SET k=k+1 WHERE id=5
......
2020-09-29T11:47:00.211067+08:00 20 Execute UPDATE sbtest1 SET k=k+1 WHERE id=5
2020-09-29T11:47:00.214376+08:00 20 Close stmt
2020-09-29T11:47:00.214410+08:00 20 Quit
2020-09-29T11:44:45.276845+08:00 19 Connect root@192.168.0.19 on sysbench using TCP/IP
2020-09-29T11:44:45.277497+08:00 19 Query CREATE TABLE sbtest1(
id INTEGER NOT NULL AUTO_INCREMENT,
k INTEGER DEFAULT '0' NOT NULL,
c CHAR(120) DEFAULT '' NOT NULL,
pad CHAR(60) DEFAULT '' NOT NULL,
PRIMARY KEY (id)
) /*! ENGINE = innodb */
2020-09-29T11:44:45.287625+08:00 19 Query INSERT INTO sbtest1(k, c, pad) VALUES(5, '83868641912-28773972837-60736120486-75162659906-27563526494-20381887404-41576422241-93426793964-56405065102-33518432330', '67847967377-48000963322-62604785301-91415491898-96926520291'),(6, '38014276128-25250245652-62722561801-27818678124-24890218270-18312424692-92565570600-36243745486-21199862476-38576014630', '23183251411-36241541236-31706421314-92007079971-60663066966'),(6, '33973744704-80540844748-72700647445-87330233173-87249600839-07301471459-22846777364-58808996678-64607045326-48799346817', '38615512647-91458489257-90681424432-95014675832-60408598704'),(6, '37002370280-58842166667-00026392672-77506866252-09658311935-56926959306-83464667271-94685475868-28264244556-14550208498', '63947013338-98809887124-59806726763-79831528812-45582457048'),(5, '44257470806-17967007152-32809666989-26174672567-29883439075-95767161284-94957565003-35708767253-53935174705-16168070783', '34551750492-67990399350-81179284955-79299808058-21257255869'),(6, '37216201353-39109531021-11197415756-87798784755-02463049870-83329763120-57551308766-61100580113-80090253566-30971527105', '05161542529-00085727016-35134775864-52531204064-98744439797'),(6, '33071042495-29920376648-91343430102-79082003121-73317691963-02846712788-88069761578-14885283975-44409837760-90760298045', '91798303270-64988107984-08161247972-12116454627-22996445111'),(5, '73754818686-04889373966-18668178968-56957589012-31352882173-91882653509-59577900152-88962682169-52981807259-62646890059', '76460662325-41613089656-42706083314-81833284991-17063140920'),(5, '26482547570-00155460224-12388481921-23289186371-78242522654-77998886134-73270876420-50821093220-31442690639-11588920653', '30508501104-50823269125-88107014550-70202920684-95842308929'),(6, '05677017559-47107518969-97509137401-28934334557-14497052050-61906823704-44077628507-24840441785-05187301456-27797851637', '29489382504-13697582598-09964978366-26554639515-36136545002')
2020-09-29T11:44:45.289943+08:00 19 Query CREATE INDEX k_1 ON sbtest1(k)
2020-09-29T11:44:45.297935+08:00 19 Quit
sysbench --config-file=config oltp_update_non_index --time=10 --tables=1 --table-size=10 --threads=1 run
2020-09-29T11:48:38.755135+08:00 21 Connect root@192.168.0.19 on sysbench using TCP/IP
2020-09-29T11:48:38.755642+08:00 21 Prepare UPDATE sbtest1 SET c=? WHERE id=?
2020-09-29T11:48:38.755799+08:00 21 Execute UPDATE sbtest1 SET c='93855409018-05443591948-95934315223-25378453343-98937654180-99240342242-54560557178-66768044893-61822982968-10191318264' WHERE id=4
2020-09-29T11:48:38.757078+08:00 21 Execute UPDATE sbtest1 SET c='51617964333-19392754039-19563921260-67680410655-86832183244-18232034648-01404551345-21972820067-97852884822-68822555025' WHERE id=6
2020-09-29T11:48:38.758075+08:00 21 Execute UPDATE sbtest1 SET c='18505394656-62668299199-79762003822-05321399172-52497042693-10083775015-80247780617-21365074477-75984188826-09206027913' WHERE id=6
2020-09-29T11:48:38.759063+08:00 21 Execute UPDATE sbtest1 SET c='32245529940-35254542933-09154255070-96630006956-99090213312-58160219671-86949576707-42265451559-50981795137-25414412614' WHERE id=6
......
2020-09-29T11:48:48.750256+08:00 21 Execute UPDATE sbtest1 SET c='78679909603-12390493961-64641699147-30306486762-06433134412-51508994602-74403283667-46792270429-18337311560-77935951247' WHERE id=6
2020-09-29T11:48:48.752938+08:00 21 Execute UPDATE sbtest1 SET c='67996478101-56640004906-53140274615-11682690229-60862054955-38314842523-31441459264-62160056044-05459964024-18449379559' WHERE id=5
2020-09-29T11:48:48.753785+08:00 21 Execute UPDATE sbtest1 SET c='25561241799-52711347378-69004196165-18065561107-63981595593-08312461811-87396784555-43415829652-48134841593-96237575828' WHERE id=6
2020-09-29T11:48:48.754936+08:00 21 Execute UPDATE sbtest1 SET c='08262162811-51780299374-23994191457-69218654799-55129075709-13980785279-11142004787-56254696955-57839938319-86801947991' WHERE id=6
2020-09-29T11:48:48.758916+08:00 21 Close stmt
2020-09-29T11:48:48.758957+08:00 21 Quit
测试环境初始化
sysbench 工具安装
# yum -y install make automake libtool pkgconfig libaio-devel
# yum -y install mariadb-devel openssl-devel
# mkdir sysbench
# cd sysbench
# wget https://github.com/akopytov/sysbench/archive/1.0.20.tar.gz
# tar -zxvf 1.0.20.tar.gz
# cd sysbench-1.0.20/
# ./autogen.sh
# ./configure
# make -j
# make install
# sysbench --version
sysbench 1.0.20
Running the test with following options:
Number of threads: 1
Report intermediate results every 10 second(s)
Initializing random number generator from current time
Running the test with following options:
Number of threads: 8
Report intermediate results every 10 second(s)
Initializing random number generator from current time
Running the test with following options:
Number of threads: 16
Report intermediate results every 10 second(s)
Initializing random number generator from current time
Running the test with following options:
Number of threads: 32
Report intermediate results every 10 second(s)
Initializing random number generator from current time
Running the test with following options:
Number of threads: 64
Report intermediate results every 10 second(s)
Initializing random number generator from current time
Running the test with following options:
Number of threads: 128
Report intermediate results every 10 second(s)
Initializing random number generator from current time
Running the test with following options:
Number of threads: 256
Report intermediate results every 10 second(s)
Initializing random number generator from current time
Running the test with following options:
Number of threads: 512
Report intermediate results every 10 second(s)
Initializing random number generator from current time
Running the test with following options:
Number of threads: 1024
Report intermediate results every 10 second(s)
Initializing random number generator from current time
Running the test with following options:
Number of threads: 1
Report intermediate results every 10 second(s)
Initializing random number generator from current time
Running the test with following options:
Number of threads: 8
Report intermediate results every 10 second(s)
Initializing random number generator from current time
Running the test with following options:
Number of threads: 16
Report intermediate results every 10 second(s)
Initializing random number generator from current time
Running the test with following options:
Number of threads: 32
Report intermediate results every 10 second(s)
Initializing random number generator from current time
Running the test with following options:
Number of threads: 64
Report intermediate results every 10 second(s)
Initializing random number generator from current time
Running the test with following options:
Number of threads: 128
Report intermediate results every 10 second(s)
Initializing random number generator from current time
Running the test with following options:
Number of threads: 512
Report intermediate results every 10 second(s)
Initializing random number generator from current time
Running the test with following options:
Number of threads: 1024
Report intermediate results every 10 second(s)
Initializing random number generator from current time
Running the test with following options:
Number of threads: 1
Report intermediate results every 10 second(s)
Initializing random number generator from current time
Running the test with following options:
Number of threads: 8
Report intermediate results every 10 second(s)
Initializing random number generator from current time
Running the test with following options:
Number of threads: 16
Report intermediate results every 10 second(s)
Initializing random number generator from current time
Running the test with following options:
Number of threads: 32
Report intermediate results every 10 second(s)
Initializing random number generator from current time
Running the test with following options:
Number of threads: 64
Report intermediate results every 10 second(s)
Initializing random number generator from current time
Running the test with following options:
Number of threads: 128
Report intermediate results every 10 second(s)
Initializing random number generator from current time
Running the test with following options:
Number of threads: 256
Report intermediate results every 10 second(s)
Initializing random number generator from current time
Running the test with following options:
Number of threads: 512
Report intermediate results every 10 second(s)
Initializing random number generator from current time
Running the test with following options:
Number of threads: 1024
Report intermediate results every 10 second(s)
Initializing random number generator from current time
xiaodong-ji
commented
4 years ago
环境描述
sysbench 性能测试
sysbench 不同场景对应的 SQL
sysbench --config-file=config oltp_update_index --tables=1 --table-size=10 prepare
sysbench --config-file=config oltp_update_index --time=10 --tables=1 --table-size=10 --threads=1 run
sysbench --config-file=config oltp_update_non_index --tables=10 --table-size=10000 prepare
sysbench --config-file=config oltp_update_non_index --time=10 --tables=1 --table-size=10 --threads=1 run
测试环境初始化
sysbench 工具安装
sysbench 测试准备
创建配置文件
准备数据与预热
// 使用 sysbench 工具生成数据 // oltp_update_index
sysbench --config-file=config oltp_update_index --tables=10 --table-size=10000 prepare
// 导入结束后再设置回悲观模式 mysql> set global tidb_disable_txn_auto_retry=on; mysql> set global tidb_txn_mode="pessimistic";
// 数据预热与统计信息收集 SELECT COUNT(pad) FROM sbtest1 USE INDEX(k_1); ...... SELECT COUNT(pad) FROM sbtest10 USE INDEX(k_1);
ANALYZE TABLE sbtest1; ...... ANALYZE TABLE sbtest10;
sysbench --config-file=config oltp_update_index --time=60 --tables=10 --table-size=10000 --threads=1 run
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options: Number of threads: 1 Report intermediate results every 10 second(s) Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 10s ] thds: 1 tps: 1773.54 qps: 1773.54 (r/w/o: 0.00/138.56/1634.97) lat (ms,95%): 2.18 err/s: 0.00 reconn/s: 0.00 [ 20s ] thds: 1 tps: 1783.25 qps: 1783.25 (r/w/o: 0.00/132.80/1650.44) lat (ms,95%): 2.14 err/s: 0.00 reconn/s: 0.00 [ 30s ] thds: 1 tps: 1817.80 qps: 1817.80 (r/w/o: 0.00/135.90/1681.90) lat (ms,95%): 2.14 err/s: 0.00 reconn/s: 0.00 [ 40s ] thds: 1 tps: 1771.00 qps: 1771.00 (r/w/o: 0.00/142.10/1628.90) lat (ms,95%): 2.14 err/s: 0.00 reconn/s: 0.00 [ 50s ] thds: 1 tps: 1726.80 qps: 1726.80 (r/w/o: 0.00/136.70/1590.10) lat (ms,95%): 2.22 err/s: 0.00 reconn/s: 0.00 SQL statistics: queries performed: read: 0 write: 8211 other: 98414 total: 106625 transactions: 106625 (1776.93 per sec.) queries: 106625 (1776.93 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.)
General statistics: total time: 60.0028s total number of events: 106625
Latency (ms): min: 0.35 avg: 0.56 max: 25.40 95th percentile: 2.14 sum: 59972.87
Threads fairness: events (avg/stddev): 106625.0000/0.00 execution time (avg/stddev): 59.9729/0.00
删除 sysbench 库并重复准备数据与预热步骤
sysbench --config-file=config oltp_update_index --time=60 --tables=10 --table-size=10000 --threads=8 run
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options: Number of threads: 8 Report intermediate results every 10 second(s) Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 10s ] thds: 8 tps: 10155.44 qps: 10155.44 (r/w/o: 0.00/780.40/9375.03) lat (ms,95%): 3.36 err/s: 0.00 reconn/s: 0.00 [ 20s ] thds: 8 tps: 10115.78 qps: 10115.78 (r/w/o: 0.00/787.81/9327.97) lat (ms,95%): 3.43 err/s: 0.00 reconn/s: 0.00 [ 30s ] thds: 8 tps: 10221.29 qps: 10221.29 (r/w/o: 0.00/770.90/9450.39) lat (ms,95%): 3.36 err/s: 0.00 reconn/s: 0.00 [ 40s ] thds: 8 tps: 9968.72 qps: 9968.72 (r/w/o: 0.00/767.90/9200.81) lat (ms,95%): 3.36 err/s: 0.00 reconn/s: 0.00 [ 50s ] thds: 8 tps: 10210.89 qps: 10210.89 (r/w/o: 0.00/781.40/9429.49) lat (ms,95%): 3.36 err/s: 0.00 reconn/s: 0.00 [ 60s ] thds: 8 tps: 10000.07 qps: 10000.07 (r/w/o: 0.00/771.70/9228.37) lat (ms,95%): 3.36 err/s: 0.00 reconn/s: 0.00 SQL statistics: queries performed: read: 0 write: 46607 other: 560147 total: 606754 transactions: 606754 (10111.22 per sec.) queries: 606754 (10111.22 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.)
General statistics: total time: 60.0058s total number of events: 606754
Latency (ms): min: 0.34 avg: 0.79 max: 54.77 95th percentile: 3.36 sum: 479838.78
Threads fairness: events (avg/stddev): 75844.2500/404.41 execution time (avg/stddev): 59.9798/0.00
删除 sysbench 库并重复准备数据与预热步骤
sysbench --config-file=config oltp_update_index --time=60 --tables=10 --table-size=10000 --threads=16 run
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options: Number of threads: 16 Report intermediate results every 10 second(s) Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 10s ] thds: 16 tps: 17901.01 qps: 17901.01 (r/w/o: 0.00/1362.47/16538.54) lat (ms,95%): 3.82 err/s: 0.00 reconn/s: 0.00 [ 20s ] thds: 16 tps: 18006.39 qps: 18006.39 (r/w/o: 0.00/1381.01/16625.37) lat (ms,95%): 3.89 err/s: 0.00 reconn/s: 0.00 [ 30s ] thds: 16 tps: 17496.41 qps: 17496.41 (r/w/o: 0.00/1368.80/16127.61) lat (ms,95%): 3.96 err/s: 0.00 reconn/s: 0.00 [ 40s ] thds: 16 tps: 17608.90 qps: 17608.90 (r/w/o: 0.00/1355.90/16253.00) lat (ms,95%): 3.96 err/s: 0.00 reconn/s: 0.00 [ 50s ] thds: 16 tps: 17530.11 qps: 17530.11 (r/w/o: 0.00/1351.20/16178.91) lat (ms,95%): 3.89 err/s: 0.00 reconn/s: 0.00 SQL statistics: queries performed: read: 0 write: 81974 other: 980592 total: 1062566 transactions: 1062566 (17708.00 per sec.) queries: 1062566 (17708.00 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.)
General statistics: total time: 60.0026s total number of events: 1062566
Latency (ms): min: 0.35 avg: 0.90 max: 203.54 95th percentile: 3.89 sum: 959706.77
Threads fairness: events (avg/stddev): 66410.3750/292.46 execution time (avg/stddev): 59.9817/0.00
删除 sysbench 库并重复准备数据与预热步骤
sysbench --config-file=config oltp_update_index --time=60 --tables=10 --table-size=10000 --threads=32 run
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options: Number of threads: 32 Report intermediate results every 10 second(s) Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 10s ] thds: 32 tps: 30658.90 qps: 30658.90 (r/w/o: 0.00/2323.44/28335.46) lat (ms,95%): 4.65 err/s: 0.00 reconn/s: 0.00 [ 20s ] thds: 32 tps: 30839.49 qps: 30839.49 (r/w/o: 0.00/2341.01/28498.48) lat (ms,95%): 4.65 err/s: 0.00 reconn/s: 0.00 [ 30s ] thds: 32 tps: 30879.88 qps: 30879.88 (r/w/o: 0.00/2345.60/28534.28) lat (ms,95%): 4.65 err/s: 0.00 reconn/s: 0.00 [ 40s ] thds: 32 tps: 30541.32 qps: 30541.32 (r/w/o: 0.00/2374.80/28166.52) lat (ms,95%): 4.65 err/s: 0.00 reconn/s: 0.00 [ 50s ] thds: 32 tps: 30843.78 qps: 30843.78 (r/w/o: 0.00/2380.70/28463.09) lat (ms,95%): 4.57 err/s: 0.00 reconn/s: 0.00 [ 60s ] thds: 32 tps: 31246.40 qps: 31246.40 (r/w/o: 0.00/2415.07/28831.33) lat (ms,95%): 4.57 err/s: 0.00 reconn/s: 0.00 SQL statistics: queries performed: read: 0 write: 141822 other: 1708380 total: 1850202 transactions: 1850202 (30833.73 per sec.) queries: 1850202 (30833.73 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.)
General statistics: total time: 60.0035s total number of events: 1850202
Latency (ms): min: 0.41 avg: 1.04 max: 201.07 95th percentile: 4.65 sum: 1919467.58
Threads fairness: events (avg/stddev): 57818.8125/378.33 execution time (avg/stddev): 59.9834/0.00
删除 sysbench 库并重复准备数据与预热步骤
sysbench --config-file=config oltp_update_index --time=60 --tables=10 --table-size=10000 --threads=64 run
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options: Number of threads: 64 Report intermediate results every 10 second(s) Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 10s ] thds: 64 tps: 50234.80 qps: 50234.80 (r/w/o: 0.00/3824.65/46410.15) lat (ms,95%): 5.67 err/s: 0.00 reconn/s: 0.00 [ 20s ] thds: 64 tps: 51166.95 qps: 51166.95 (r/w/o: 0.00/3912.02/47254.93) lat (ms,95%): 5.57 err/s: 0.00 reconn/s: 0.00 [ 30s ] thds: 64 tps: 51661.06 qps: 51661.06 (r/w/o: 0.00/3946.40/47714.66) lat (ms,95%): 5.47 err/s: 0.00 reconn/s: 0.00 [ 40s ] thds: 64 tps: 51050.12 qps: 51050.12 (r/w/o: 0.00/3947.80/47102.32) lat (ms,95%): 5.57 err/s: 0.00 reconn/s: 0.00 [ 50s ] thds: 64 tps: 49958.32 qps: 49958.32 (r/w/o: 0.00/3828.91/46129.41) lat (ms,95%): 5.57 err/s: 0.00 reconn/s: 0.00 [ 60s ] thds: 64 tps: 50624.32 qps: 50624.32 (r/w/o: 0.00/3879.46/46744.86) lat (ms,95%): 5.57 err/s: 0.00 reconn/s: 0.00 SQL statistics: queries performed: read: 0 write: 233428 other: 2813731 total: 3047159 transactions: 3047159 (50776.02 per sec.) queries: 3047159 (50776.02 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.)
General statistics: total time: 60.0095s total number of events: 3047159
Latency (ms): min: 0.38 avg: 1.26 max: 245.15 95th percentile: 5.57 sum: 3839135.87
Threads fairness: events (avg/stddev): 47611.8594/300.06 execution time (avg/stddev): 59.9865/0.00
删除 sysbench 库并重复准备数据与预热步骤
sysbench --config-file=config oltp_update_index --time=60 --tables=10 --table-size=10000 --threads=128 run
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options: Number of threads: 128 Report intermediate results every 10 second(s) Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 10s ] thds: 128 tps: 68364.40 qps: 68364.40 (r/w/o: 0.00/5210.67/63153.73) lat (ms,95%): 8.28 err/s: 0.00 reconn/s: 0.00 [ 20s ] thds: 128 tps: 68044.51 qps: 68044.51 (r/w/o: 0.00/5183.79/62860.72) lat (ms,95%): 8.43 err/s: 0.00 reconn/s: 0.00 [ 30s ] thds: 128 tps: 63138.96 qps: 63138.96 (r/w/o: 0.00/4860.15/58278.81) lat (ms,95%): 8.58 err/s: 0.00 reconn/s: 0.00 [ 40s ] thds: 128 tps: 67210.22 qps: 67210.22 (r/w/o: 0.00/5159.17/62051.05) lat (ms,95%): 8.28 err/s: 0.00 reconn/s: 0.00 [ 50s ] thds: 128 tps: 69416.53 qps: 69416.53 (r/w/o: 0.00/5290.69/64125.84) lat (ms,95%): 8.13 err/s: 0.00 reconn/s: 0.00 [ 60s ] thds: 128 tps: 67467.57 qps: 67467.57 (r/w/o: 0.00/5184.12/62283.45) lat (ms,95%): 8.43 err/s: 0.00 reconn/s: 0.00 SQL statistics: queries performed: read: 0 write: 308967 other: 3727783 total: 4036750 transactions: 4036750 (67264.99 per sec.) queries: 4036750 (67264.99 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.)
General statistics: total time: 60.0104s total number of events: 4036750
Latency (ms): min: 0.37 avg: 1.90 max: 332.29 95th percentile: 8.28 sum: 7678795.64
Threads fairness: events (avg/stddev): 31537.1094/276.20 execution time (avg/stddev): 59.9906/0.00
删除 sysbench 库并重复准备数据与预热步骤
sysbench --config-file=config oltp_update_index --time=60 --tables=10 --table-size=10000 --threads=256 run
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options: Number of threads: 256 Report intermediate results every 10 second(s) Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 10s ] thds: 256 tps: 86419.28 qps: 86419.18 (r/w/o: 0.00/6616.44/79802.74) lat (ms,95%): 13.70 err/s: 0.00 reconn/s: 0.00 [ 20s ] thds: 256 tps: 85697.63 qps: 85697.73 (r/w/o: 0.00/6527.33/79170.40) lat (ms,95%): 13.70 err/s: 0.00 reconn/s: 0.00 [ 30s ] thds: 256 tps: 80924.09 qps: 80924.09 (r/w/o: 0.00/6227.59/74696.50) lat (ms,95%): 13.70 err/s: 0.00 reconn/s: 0.00 [ 40s ] thds: 256 tps: 85596.99 qps: 85596.99 (r/w/o: 0.00/6559.51/79037.49) lat (ms,95%): 13.46 err/s: 0.00 reconn/s: 0.00 [ 50s ] thds: 256 tps: 84626.84 qps: 84626.84 (r/w/o: 0.00/6535.80/78091.04) lat (ms,95%): 12.75 err/s: 0.00 reconn/s: 0.00 [ 60s ] thds: 256 tps: 85998.51 qps: 85998.51 (r/w/o: 0.00/6617.52/79380.99) lat (ms,95%): 12.52 err/s: 0.00 reconn/s: 0.00 SQL statistics: queries performed: read: 0 write: 390974 other: 4702225 total: 5093199 transactions: 5093199 (84743.65 per sec.) queries: 5093199 (84743.65 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.)
General statistics: total time: 60.0990s total number of events: 5093199
Latency (ms): min: 0.38 avg: 3.02 max: 381.01 95th percentile: 13.46 sum: 15360545.86
Threads fairness: events (avg/stddev): 19895.3086/229.92 execution time (avg/stddev): 60.0021/0.01
删除 sysbench 库并重复准备数据与预热步骤
sysbench --config-file=config oltp_update_index --time=60 --tables=10 --table-size=10000 --threads=512 run
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options: Number of threads: 512 Report intermediate results every 10 second(s) Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 10s ] thds: 512 tps: 123507.69 qps: 123507.69 (r/w/o: 0.00/9458.25/114049.45) lat (ms,95%): 13.70 err/s: 0.00 reconn/s: 0.00 [ 20s ] thds: 512 tps: 123280.58 qps: 123280.58 (r/w/o: 0.00/9461.32/113819.26) lat (ms,95%): 14.21 err/s: 0.00 reconn/s: 0.00 [ 30s ] thds: 512 tps: 118297.95 qps: 118297.95 (r/w/o: 0.00/9051.81/109246.14) lat (ms,95%): 14.21 err/s: 0.00 reconn/s: 0.00 [ 40s ] thds: 512 tps: 113567.52 qps: 113567.52 (r/w/o: 0.00/8664.30/104903.22) lat (ms,95%): 13.70 err/s: 0.00 reconn/s: 0.00 [ 50s ] thds: 512 tps: 111249.24 qps: 111249.04 (r/w/o: 0.00/8546.50/102702.54) lat (ms,95%): 13.95 err/s: 0.00 reconn/s: 0.00 [ 60s ] thds: 512 tps: 114282.01 qps: 114282.21 (r/w/o: 0.00/8732.88/105549.34) lat (ms,95%): 13.46 err/s: 0.00 reconn/s: 0.00 SQL statistics: queries performed: read: 0 write: 539348 other: 6503703 total: 7043051 transactions: 7043051 (117078.53 per sec.) queries: 7043051 (117078.53 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.)
General statistics: total time: 60.1544s total number of events: 7043051
Latency (ms): min: 0.43 avg: 4.36 max: 690.83 95th percentile: 13.95 sum: 30723088.77
Threads fairness: events (avg/stddev): 13755.9590/148.05 execution time (avg/stddev): 60.0060/0.01
删除 sysbench 库并重复准备数据与预热步骤
sysbench --config-file=config oltp_update_index --time=60 --tables=10 --table-size=10000 --threads=1024 run
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options: Number of threads: 1024 Report intermediate results every 10 second(s) Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 10s ] thds: 1024 tps: 106801.32 qps: 106801.22 (r/w/o: 0.00/8133.94/98667.28) lat (ms,95%): 33.72 err/s: 0.00 reconn/s: 0.00 [ 20s ] thds: 1024 tps: 103933.40 qps: 103933.50 (r/w/o: 0.00/7988.49/95945.01) lat (ms,95%): 35.59 err/s: 0.00 reconn/s: 0.00 [ 30s ] thds: 1024 tps: 101355.69 qps: 101355.69 (r/w/o: 0.00/7789.94/93565.75) lat (ms,95%): 35.59 err/s: 0.00 reconn/s: 0.00 [ 40s ] thds: 1024 tps: 102554.25 qps: 102554.15 (r/w/o: 0.00/7927.05/94627.10) lat (ms,95%): 34.33 err/s: 0.00 reconn/s: 0.00 [ 50s ] thds: 1024 tps: 103955.25 qps: 103955.25 (r/w/o: 0.00/7943.21/96012.04) lat (ms,95%): 30.81 err/s: 0.00 reconn/s: 0.00 [ 60s ] thds: 1024 tps: 103929.68 qps: 103929.78 (r/w/o: 0.00/7930.20/95999.58) lat (ms,95%): 27.17 err/s: 0.00 reconn/s: 0.00 SQL statistics: queries performed: read: 0 write: 477913 other: 5755493 total: 6233406 transactions: 6233406 (103557.46 per sec.) queries: 6233406 (103557.46 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.)
General statistics: total time: 60.1904s total number of events: 6233406
Latency (ms): min: 0.60 avg: 9.86 max: 589.86 95th percentile: 33.72 sum: 61445524.56
Threads fairness: events (avg/stddev): 6087.3105/85.94 execution time (avg/stddev): 60.0054/0.01
// 导入前设置为乐观事务模式 mysql> set global tidb_disable_txn_auto_retry=off; mysql> set global tidb_txn_mode="optimistic";
// 使用 sysbench 工具生成数据 // oltp_update_non_index
sysbench --config-file=config oltp_update_index --tables=10 --table-size=10000 prepare
// 导入结束后再设置回悲观模式 mysql> set global tidb_disable_txn_auto_retry=on; mysql> set global tidb_txn_mode="pessimistic";
// 将 k_1 重置为多列的唯一索引 alter table sbtest1 drop index k_1; ...... alter table sbtest10 drop index k_10;
alter table sbtest1 add unique key
k_1(k,c); ...... alter table sbtest10 add unique keyk_10(k,c);// 数据预热与统计信息收集 SELECT COUNT(pad) FROM sbtest1 USE INDEX(k_1); ...... SELECT COUNT(pad) FROM sbtest10 USE INDEX(k_1);
ANALYZE TABLE sbtest1; ...... ANALYZE TABLE sbtest10;
删除 sysbench 库并重复准备数据与预热步骤
sysbench --config-file=config oltp_update_index --time=60 --tables=10 --table-size=10000 --threads=1 run
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options: Number of threads: 1 Report intermediate results every 10 second(s) Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 10s ] thds: 1 tps: 1562.49 qps: 1562.49 (r/w/o: 0.00/115.67/1446.82) lat (ms,95%): 2.91 err/s: 0.00 reconn/s: 0.00 [ 20s ] thds: 1 tps: 1561.34 qps: 1561.34 (r/w/o: 0.00/118.70/1442.64) lat (ms,95%): 2.91 err/s: 0.00 reconn/s: 0.00 [ 30s ] thds: 1 tps: 1530.30 qps: 1530.30 (r/w/o: 0.00/119.70/1410.60) lat (ms,95%): 2.91 err/s: 0.00 reconn/s: 0.00 [ 40s ] thds: 1 tps: 1552.70 qps: 1552.70 (r/w/o: 0.00/121.10/1431.60) lat (ms,95%): 2.91 err/s: 0.00 reconn/s: 0.00 [ 50s ] thds: 1 tps: 1523.50 qps: 1523.50 (r/w/o: 0.00/123.30/1400.20) lat (ms,95%): 2.91 err/s: 0.00 reconn/s: 0.00 SQL statistics: queries performed: read: 0 write: 7155 other: 85594 total: 92749 transactions: 92749 (1545.69 per sec.) queries: 92749 (1545.69 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.)
General statistics: total time: 60.0026s total number of events: 92749
Latency (ms): min: 0.39 avg: 0.65 max: 23.17 95th percentile: 2.91 sum: 59973.64
Threads fairness: events (avg/stddev): 92749.0000/0.00 execution time (avg/stddev): 59.9736/0.00
删除 sysbench 库并重复准备数据与预热步骤
sysbench --config-file=config oltp_update_index --time=60 --tables=10 --table-size=10000 --threads=8 run
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options: Number of threads: 8 Report intermediate results every 10 second(s) Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 10s ] thds: 8 tps: 10011.91 qps: 10011.91 (r/w/o: 0.00/747.81/9264.10) lat (ms,95%): 3.36 err/s: 0.00 reconn/s: 0.00 [ 20s ] thds: 8 tps: 9794.72 qps: 9794.72 (r/w/o: 0.00/746.62/9048.10) lat (ms,95%): 3.43 err/s: 0.00 reconn/s: 0.00 [ 30s ] thds: 8 tps: 9972.01 qps: 9972.01 (r/w/o: 0.00/743.60/9228.41) lat (ms,95%): 3.36 err/s: 0.00 reconn/s: 0.00 [ 40s ] thds: 8 tps: 9840.20 qps: 9840.20 (r/w/o: 0.00/766.30/9073.90) lat (ms,95%): 3.43 err/s: 0.00 reconn/s: 0.00 [ 50s ] thds: 8 tps: 9627.70 qps: 9627.70 (r/w/o: 0.00/745.10/8882.60) lat (ms,95%): 3.43 err/s: 0.00 reconn/s: 0.00 [ 60s ] thds: 8 tps: 9962.37 qps: 9962.37 (r/w/o: 0.00/754.40/9207.97) lat (ms,95%): 3.36 err/s: 0.00 reconn/s: 0.00 SQL statistics: queries performed: read: 0 write: 45043 other: 547077 total: 592120 transactions: 592120 (9867.80 per sec.) queries: 592120 (9867.80 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.)
General statistics: total time: 60.0030s total number of events: 592120
Latency (ms): min: 0.39 avg: 0.81 max: 43.30 95th percentile: 3.36 sum: 479839.41
Threads fairness: events (avg/stddev): 74015.0000/201.03 execution time (avg/stddev): 59.9799/0.00
删除 sysbench 库并重复准备数据与预热步骤
sysbench --config-file=config oltp_update_index --time=60 --tables=10 --table-size=10000 --threads=16 run
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options: Number of threads: 16 Report intermediate results every 10 second(s) Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 10s ] thds: 16 tps: 16680.54 qps: 16680.54 (r/w/o: 0.00/1281.43/15399.10) lat (ms,95%): 4.25 err/s: 0.00 reconn/s: 0.00 [ 20s ] thds: 16 tps: 15877.86 qps: 15877.86 (r/w/o: 0.00/1217.94/14659.93) lat (ms,95%): 4.49 err/s: 0.00 reconn/s: 0.00 [ 30s ] thds: 16 tps: 16273.31 qps: 16273.31 (r/w/o: 0.00/1242.08/15031.24) lat (ms,95%): 4.33 err/s: 0.00 reconn/s: 0.00 [ 40s ] thds: 16 tps: 16529.39 qps: 16529.39 (r/w/o: 0.00/1270.52/15258.87) lat (ms,95%): 4.33 err/s: 0.00 reconn/s: 0.00 [ 50s ] thds: 16 tps: 16461.21 qps: 16461.21 (r/w/o: 0.00/1240.78/15220.43) lat (ms,95%): 4.18 err/s: 0.00 reconn/s: 0.00 [ 60s ] thds: 16 tps: 16502.48 qps: 16502.48 (r/w/o: 0.00/1293.51/15208.97) lat (ms,95%): 4.18 err/s: 0.00 reconn/s: 0.00 SQL statistics: queries performed: read: 0 write: 75472 other: 907830 total: 983302 transactions: 983302 (16386.55 per sec.) queries: 983302 (16386.55 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.)
General statistics: total time: 60.0044s total number of events: 983302
Latency (ms): min: 0.40 avg: 0.98 max: 143.09 95th percentile: 4.33 sum: 959744.35
Threads fairness: events (avg/stddev): 61456.3750/272.00 execution time (avg/stddev): 59.9840/0.00
删除 sysbench 库并重复准备数据与预热步骤
sysbench --config-file=config oltp_update_index --time=60 --tables=10 --table-size=10000 --threads=32 run
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options: Number of threads: 32 Report intermediate results every 10 second(s) Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 10s ] thds: 32 tps: 28989.41 qps: 28989.41 (r/w/o: 0.00/2231.06/26758.35) lat (ms,95%): 5.09 err/s: 0.00 reconn/s: 0.00 [ 20s ] thds: 32 tps: 28545.75 qps: 28545.75 (r/w/o: 0.00/2198.11/26347.64) lat (ms,95%): 5.09 err/s: 0.00 reconn/s: 0.00 [ 30s ] thds: 32 tps: 28920.31 qps: 28920.31 (r/w/o: 0.00/2228.10/26692.21) lat (ms,95%): 5.09 err/s: 0.00 reconn/s: 0.00 [ 40s ] thds: 32 tps: 26727.18 qps: 26727.18 (r/w/o: 0.00/2057.40/24669.78) lat (ms,95%): 5.28 err/s: 0.00 reconn/s: 0.00 [ 50s ] thds: 32 tps: 28863.12 qps: 28863.12 (r/w/o: 0.00/2199.60/26663.52) lat (ms,95%): 5.18 err/s: 0.00 reconn/s: 0.00 [ 60s ] thds: 32 tps: 28283.64 qps: 28283.64 (r/w/o: 0.00/2177.16/26106.48) lat (ms,95%): 5.18 err/s: 0.00 reconn/s: 0.00 SQL statistics: queries performed: read: 0 write: 130929 other: 1572481 total: 1703410 transactions: 1703410 (28386.90 per sec.) queries: 1703410 (28386.90 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.)
General statistics: total time: 60.0047s total number of events: 1703410
Latency (ms): min: 0.36 avg: 1.13 max: 204.02 95th percentile: 5.09 sum: 1919490.17
Threads fairness: events (avg/stddev): 53231.5625/237.75 execution time (avg/stddev): 59.9841/0.00
删除 sysbench 库并重复准备数据与预热步骤
sysbench --config-file=config oltp_update_index --time=60 --tables=10 --table-size=10000 --threads=64 run
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options: Number of threads: 64 Report intermediate results every 10 second(s) Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 10s ] thds: 64 tps: 47836.81 qps: 47836.81 (r/w/o: 0.00/3667.62/44169.19) lat (ms,95%): 6.43 err/s: 0.00 reconn/s: 0.00 [ 20s ] thds: 64 tps: 47456.21 qps: 47456.21 (r/w/o: 0.00/3649.91/43806.30) lat (ms,95%): 6.32 err/s: 0.00 reconn/s: 0.00 [ 30s ] thds: 64 tps: 47417.31 qps: 47417.31 (r/w/o: 0.00/3612.20/43805.11) lat (ms,95%): 6.32 err/s: 0.00 reconn/s: 0.00 [ 40s ] thds: 64 tps: 47080.97 qps: 47080.97 (r/w/o: 0.00/3641.10/43439.87) lat (ms,95%): 6.32 err/s: 0.00 reconn/s: 0.00 [ 50s ] thds: 64 tps: 46291.41 qps: 46291.41 (r/w/o: 0.00/3565.80/42725.61) lat (ms,95%): 6.43 err/s: 0.00 reconn/s: 0.00 [ 60s ] thds: 64 tps: 46127.46 qps: 46127.46 (r/w/o: 0.00/3522.84/42604.62) lat (ms,95%): 6.55 err/s: 0.00 reconn/s: 0.00 SQL statistics: queries performed: read: 0 write: 216631 other: 2605655 total: 2822286 transactions: 2822286 (47024.15 per sec.) queries: 2822286 (47024.15 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.)
General statistics: total time: 60.0156s total number of events: 2822286
Latency (ms): min: 0.41 avg: 1.36 max: 223.42 95th percentile: 6.43 sum: 3839169.53
Threads fairness: events (avg/stddev): 44098.2188/310.68 execution time (avg/stddev): 59.9870/0.00
删除 sysbench 库并重复准备数据与预热步骤
sysbench --config-file=config oltp_update_index --time=60 --tables=10 --table-size=10000 --threads=128 run
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options: Number of threads: 128 Report intermediate results every 10 second(s) Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 10s ] thds: 128 tps: 63403.90 qps: 63403.90 (r/w/o: 0.00/4832.31/58571.59) lat (ms,95%): 9.91 err/s: 0.00 reconn/s: 0.00 [ 20s ] thds: 128 tps: 59965.21 qps: 59965.21 (r/w/o: 0.00/4596.53/55368.68) lat (ms,95%): 10.27 err/s: 0.00 reconn/s: 0.00 [ 30s ] thds: 128 tps: 58780.02 qps: 58780.02 (r/w/o: 0.00/4490.19/54289.83) lat (ms,95%): 9.73 err/s: 0.00 reconn/s: 0.00 [ 40s ] thds: 128 tps: 62509.41 qps: 62509.41 (r/w/o: 0.00/4813.80/57695.61) lat (ms,95%): 9.56 err/s: 0.00 reconn/s: 0.00 [ 50s ] thds: 128 tps: 61707.97 qps: 61707.97 (r/w/o: 0.00/4720.28/56987.69) lat (ms,95%): 9.22 err/s: 0.00 reconn/s: 0.00 [ 60s ] thds: 128 tps: 63003.86 qps: 63003.86 (r/w/o: 0.00/4832.76/58171.10) lat (ms,95%): 9.39 err/s: 0.00 reconn/s: 0.00 SQL statistics: queries performed: read: 0 write: 282960 other: 3411544 total: 3694504 transactions: 3694504 (61560.51 per sec.) queries: 3694504 (61560.51 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.)
General statistics: total time: 60.0119s total number of events: 3694504
Latency (ms): min: 0.38 avg: 2.08 max: 309.43 95th percentile: 9.73 sum: 7679151.20
Threads fairness: events (avg/stddev): 28863.3125/325.65 execution time (avg/stddev): 59.9934/0.00
删除 sysbench 库并重复准备数据与预热步骤
sysbench --config-file=config oltp_update_index --time=60 --tables=10 --table-size=10000 --threads=512 run
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options: Number of threads: 512 Report intermediate results every 10 second(s) Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 10s ] thds: 512 tps: 112911.93 qps: 112911.83 (r/w/o: 0.00/8627.75/104284.08) lat (ms,95%): 18.61 err/s: 0.00 reconn/s: 0.00 [ 20s ] thds: 512 tps: 112277.07 qps: 112277.07 (r/w/o: 0.00/8605.97/103671.09) lat (ms,95%): 18.28 err/s: 0.00 reconn/s: 0.00 [ 30s ] thds: 512 tps: 107447.80 qps: 107447.90 (r/w/o: 0.00/8292.05/99155.85) lat (ms,95%): 17.63 err/s: 0.00 reconn/s: 0.00 [ 40s ] thds: 512 tps: 108254.41 qps: 108254.41 (r/w/o: 0.00/8347.97/99906.44) lat (ms,95%): 17.32 err/s: 0.00 reconn/s: 0.00 [ 50s ] thds: 512 tps: 106643.01 qps: 106643.01 (r/w/o: 0.00/8206.02/98436.98) lat (ms,95%): 17.32 err/s: 0.00 reconn/s: 0.00 [ 60s ] thds: 512 tps: 97040.33 qps: 97040.33 (r/w/o: 0.00/7405.61/89634.71) lat (ms,95%): 20.74 err/s: 0.00 reconn/s: 0.00 SQL statistics: queries performed: read: 0 write: 495081 other: 5951959 total: 6447040 transactions: 6447040 (107242.30 per sec.) queries: 6447040 (107242.30 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.)
General statistics: total time: 60.1143s total number of events: 6447040
Latency (ms): min: 0.41 avg: 4.77 max: 937.08 95th percentile: 18.28 sum: 30725540.93
Threads fairness: events (avg/stddev): 12591.8750/200.91 execution time (avg/stddev): 60.0108/0.01
删除 sysbench 库并重复准备数据与预热步骤
sysbench --config-file=config oltp_update_index --time=60 --tables=10 --table-size=10000 --threads=1024 run
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options: Number of threads: 1024 Report intermediate results every 10 second(s) Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 10s ] thds: 1024 tps: 124154.54 qps: 124154.54 (r/w/o: 0.00/9435.19/114719.35) lat (ms,95%): 30.26 err/s: 0.00 reconn/s: 0.00 [ 20s ] thds: 1024 tps: 123363.44 qps: 123363.24 (r/w/o: 0.00/9529.97/113833.27) lat (ms,95%): 30.26 err/s: 0.00 reconn/s: 0.00 [ 30s ] thds: 1024 tps: 115883.96 qps: 115884.16 (r/w/o: 0.00/8886.04/106998.12) lat (ms,95%): 30.26 err/s: 0.00 reconn/s: 0.00 [ 40s ] thds: 1024 tps: 113666.60 qps: 113666.60 (r/w/o: 0.00/8771.36/104895.24) lat (ms,95%): 27.66 err/s: 0.00 reconn/s: 0.00 [ 50s ] thds: 1024 tps: 108837.97 qps: 108837.97 (r/w/o: 0.00/8329.64/100508.32) lat (ms,95%): 31.37 err/s: 0.00 reconn/s: 0.00 [ 60s ] thds: 1024 tps: 112968.27 qps: 112968.27 (r/w/o: 0.00/8587.33/104380.93) lat (ms,95%): 27.66 err/s: 0.00 reconn/s: 0.00 SQL statistics: queries performed: read: 0 write: 536093 other: 6454774 total: 6990867 transactions: 6990867 (110693.87 per sec.) queries: 6990867 (110693.87 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.)
General statistics: total time: 63.1526s total number of events: 6990867
Latency (ms): min: 0.62 avg: 8.79 max: 3601.46 95th percentile: 29.72 sum: 61479208.45
Threads fairness: events (avg/stddev): 6827.0186/113.77 execution time (avg/stddev): 60.0383/0.15
// 导入前设置为乐观事务模式 mysql> set global tidb_disable_txn_auto_retry=off; mysql> set global tidb_txn_mode="optimistic";
// 使用 sysbench 工具生成数据 // oltp_update_non_index
sysbench --config-file=config oltp_update_non_index --tables=10 --table-size=10000 prepare
// 导入结束后再设置回悲观模式 mysql> set global tidb_disable_txn_auto_retry=on; mysql> set global tidb_txn_mode="pessimistic";
// 数据预热与统计信息收集 SELECT COUNT(pad) FROM sbtest1 USE INDEX(k_1); ...... SELECT COUNT(pad) FROM sbtest10 USE INDEX(k_1);
ANALYZE TABLE sbtest1; ...... ANALYZE TABLE sbtest10;
删除 sysbench 库并重复准备数据与预热步骤
sysbench --config-file=config oltp_update_non_index --time=60 --tables=10 --table-size=10000 --threads=1 run
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options: Number of threads: 1 Report intermediate results every 10 second(s) Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 10s ] thds: 1 tps: 1625.07 qps: 1625.07 (r/w/o: 0.00/127.57/1497.50) lat (ms,95%): 2.22 err/s: 0.00 reconn/s: 0.00 [ 20s ] thds: 1 tps: 1641.75 qps: 1641.75 (r/w/o: 0.00/121.00/1520.75) lat (ms,95%): 2.22 err/s: 0.00 reconn/s: 0.00 [ 30s ] thds: 1 tps: 1632.10 qps: 1632.10 (r/w/o: 0.00/126.70/1505.40) lat (ms,95%): 2.22 err/s: 0.00 reconn/s: 0.00 [ 40s ] thds: 1 tps: 1658.00 qps: 1658.00 (r/w/o: 0.00/125.40/1532.60) lat (ms,95%): 2.22 err/s: 0.00 reconn/s: 0.00 [ 50s ] thds: 1 tps: 1651.80 qps: 1651.80 (r/w/o: 0.00/125.30/1526.50) lat (ms,95%): 2.22 err/s: 0.00 reconn/s: 0.00 SQL statistics: queries performed: read: 0 write: 7517 other: 91169 total: 98686 transactions: 98686 (1644.63 per sec.) queries: 98686 (1644.63 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.)
General statistics: total time: 60.0028s total number of events: 98686
Latency (ms): min: 0.39 avg: 0.61 max: 19.86 95th percentile: 2.22 sum: 59973.80
Threads fairness: events (avg/stddev): 98686.0000/0.00 execution time (avg/stddev): 59.9738/0.00
删除 sysbench 库并重复准备数据与预热步骤
sysbench --config-file=config oltp_update_non_index --time=60 --tables=10 --table-size=10000 --threads=8 run
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options: Number of threads: 8 Report intermediate results every 10 second(s) Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 10s ] thds: 8 tps: 10161.16 qps: 10161.16 (r/w/o: 0.00/786.59/9374.58) lat (ms,95%): 3.25 err/s: 0.00 reconn/s: 0.00 [ 20s ] thds: 8 tps: 10311.22 qps: 10311.22 (r/w/o: 0.00/792.42/9518.80) lat (ms,95%): 3.19 err/s: 0.00 reconn/s: 0.00 [ 30s ] thds: 8 tps: 10410.11 qps: 10410.11 (r/w/o: 0.00/786.30/9623.81) lat (ms,95%): 3.19 err/s: 0.00 reconn/s: 0.00 [ 40s ] thds: 8 tps: 10338.69 qps: 10338.69 (r/w/o: 0.00/788.20/9550.49) lat (ms,95%): 3.19 err/s: 0.00 reconn/s: 0.00 [ 50s ] thds: 8 tps: 10361.31 qps: 10361.31 (r/w/o: 0.00/797.40/9563.91) lat (ms,95%): 3.19 err/s: 0.00 reconn/s: 0.00 [ 60s ] thds: 8 tps: 10434.38 qps: 10434.38 (r/w/o: 0.00/790.10/9644.28) lat (ms,95%): 3.19 err/s: 0.00 reconn/s: 0.00 SQL statistics: queries performed: read: 0 write: 47415 other: 572783 total: 620198 transactions: 620198 (10335.66 per sec.) queries: 620198 (10335.66 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.)
General statistics: total time: 60.0034s total number of events: 620198
Latency (ms): min: 0.35 avg: 0.77 max: 174.09 95th percentile: 3.19 sum: 479823.47
Threads fairness: events (avg/stddev): 77524.7500/313.81 execution time (avg/stddev): 59.9779/0.00
删除 sysbench 库并重复准备数据与预热步骤
sysbench --config-file=config oltp_update_non_index --time=60 --tables=10 --table-size=10000 --threads=16 run
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options: Number of threads: 16 Report intermediate results every 10 second(s) Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 10s ] thds: 16 tps: 17958.90 qps: 17958.90 (r/w/o: 0.00/1380.35/16578.55) lat (ms,95%): 3.68 err/s: 0.00 reconn/s: 0.00 [ 20s ] thds: 16 tps: 17845.79 qps: 17845.79 (r/w/o: 0.00/1372.42/16473.37) lat (ms,95%): 3.68 err/s: 0.00 reconn/s: 0.00 [ 30s ] thds: 16 tps: 17936.70 qps: 17936.70 (r/w/o: 0.00/1374.80/16561.90) lat (ms,95%): 3.68 err/s: 0.00 reconn/s: 0.00 [ 40s ] thds: 16 tps: 18019.57 qps: 18019.57 (r/w/o: 0.00/1393.10/16626.48) lat (ms,95%): 3.75 err/s: 0.00 reconn/s: 0.00 [ 50s ] thds: 16 tps: 18241.63 qps: 18241.63 (r/w/o: 0.00/1401.60/16840.03) lat (ms,95%): 3.68 err/s: 0.00 reconn/s: 0.00 [ 60s ] thds: 16 tps: 17772.03 qps: 17772.03 (r/w/o: 0.00/1360.69/16411.33) lat (ms,95%): 3.68 err/s: 0.00 reconn/s: 0.00 SQL statistics: queries performed: read: 0 write: 82839 other: 994967 total: 1077806 transactions: 1077806 (17960.90 per sec.) queries: 1077806 (17960.90 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.)
General statistics: total time: 60.0062s total number of events: 1077806
Latency (ms): min: 0.36 avg: 0.89 max: 205.31 95th percentile: 3.68 sum: 959747.16
Threads fairness: events (avg/stddev): 67362.8750/273.73 execution time (avg/stddev): 59.9842/0.00
删除 sysbench 库并重复准备数据与预热步骤
sysbench --config-file=config oltp_update_non_index --time=60 --tables=10 --table-size=10000 --threads=32 run
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options: Number of threads: 32 Report intermediate results every 10 second(s) Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 10s ] thds: 32 tps: 30736.44 qps: 30736.44 (r/w/o: 0.00/2359.19/28377.25) lat (ms,95%): 4.25 err/s: 0.00 reconn/s: 0.00 [ 20s ] thds: 32 tps: 30333.09 qps: 30333.09 (r/w/o: 0.00/2335.93/27997.16) lat (ms,95%): 4.33 err/s: 0.00 reconn/s: 0.00 [ 30s ] thds: 32 tps: 30195.72 qps: 30195.72 (r/w/o: 0.00/2332.98/27862.74) lat (ms,95%): 4.41 err/s: 0.00 reconn/s: 0.00 [ 40s ] thds: 32 tps: 30872.46 qps: 30872.46 (r/w/o: 0.00/2363.70/28508.76) lat (ms,95%): 4.33 err/s: 0.00 reconn/s: 0.00 [ 50s ] thds: 32 tps: 31329.05 qps: 31329.05 (r/w/o: 0.00/2401.40/28927.65) lat (ms,95%): 4.33 err/s: 0.00 reconn/s: 0.00 [ 60s ] thds: 32 tps: 31484.88 qps: 31484.88 (r/w/o: 0.00/2406.10/29078.78) lat (ms,95%): 4.25 err/s: 0.00 reconn/s: 0.00 SQL statistics: queries performed: read: 0 write: 142008 other: 1707607 total: 1849615 transactions: 1849615 (30823.17 per sec.) queries: 1849615 (30823.17 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.)
General statistics: total time: 60.0051s total number of events: 1849615
Latency (ms): min: 0.37 avg: 1.04 max: 204.08 95th percentile: 4.33 sum: 1919444.15
Threads fairness: events (avg/stddev): 57800.4688/300.39 execution time (avg/stddev): 59.9826/0.00
删除 sysbench 库并重复准备数据与预热步骤
sysbench --config-file=config oltp_update_non_index --time=60 --tables=10 --table-size=10000 --threads=64 run
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options: Number of threads: 64 Report intermediate results every 10 second(s) Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 10s ] thds: 64 tps: 48867.75 qps: 48867.75 (r/w/o: 0.00/3756.59/45111.16) lat (ms,95%): 5.28 err/s: 0.00 reconn/s: 0.00 [ 20s ] thds: 64 tps: 49198.40 qps: 49198.40 (r/w/o: 0.00/3790.63/45407.77) lat (ms,95%): 5.28 err/s: 0.00 reconn/s: 0.00 [ 30s ] thds: 64 tps: 49356.97 qps: 49356.97 (r/w/o: 0.00/3777.97/45579.00) lat (ms,95%): 5.37 err/s: 0.00 reconn/s: 0.00 [ 40s ] thds: 64 tps: 47521.97 qps: 47521.97 (r/w/o: 0.00/3638.94/43883.04) lat (ms,95%): 5.37 err/s: 0.00 reconn/s: 0.00 [ 50s ] thds: 64 tps: 48292.67 qps: 48292.67 (r/w/o: 0.00/3721.07/44571.61) lat (ms,95%): 5.37 err/s: 0.00 reconn/s: 0.00 [ 60s ] thds: 64 tps: 50330.84 qps: 50330.84 (r/w/o: 0.00/3878.09/46452.75) lat (ms,95%): 5.09 err/s: 0.00 reconn/s: 0.00 SQL statistics: queries performed: read: 0 write: 225670 other: 2710294 total: 2935964 transactions: 2935964 (48912.60 per sec.) queries: 2935964 (48912.60 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.)
General statistics: total time: 60.0224s total number of events: 2935964
Latency (ms): min: 0.37 avg: 1.31 max: 285.86 95th percentile: 5.28 sum: 3839110.51
Threads fairness: events (avg/stddev): 45874.4375/260.94 execution time (avg/stddev): 59.9861/0.00
删除 sysbench 库并重复准备数据与预热步骤
sysbench --config-file=config oltp_update_non_index --time=60 --tables=10 --table-size=10000 --threads=128 run
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options: Number of threads: 128 Report intermediate results every 10 second(s) Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 10s ] thds: 128 tps: 70478.94 qps: 70478.94 (r/w/o: 0.00/5367.83/65111.11) lat (ms,95%): 7.56 err/s: 0.00 reconn/s: 0.00 [ 20s ] thds: 128 tps: 67395.08 qps: 67395.08 (r/w/o: 0.00/5198.21/62196.87) lat (ms,95%): 7.70 err/s: 0.00 reconn/s: 0.00 [ 30s ] thds: 128 tps: 67285.07 qps: 67285.07 (r/w/o: 0.00/5137.80/62147.27) lat (ms,95%): 7.70 err/s: 0.00 reconn/s: 0.00 [ 40s ] thds: 128 tps: 68581.12 qps: 68581.12 (r/w/o: 0.00/5235.80/63345.32) lat (ms,95%): 7.70 err/s: 0.00 reconn/s: 0.00 [ 50s ] thds: 128 tps: 70525.92 qps: 70525.92 (r/w/o: 0.00/5416.10/65109.81) lat (ms,95%): 7.30 err/s: 0.00 reconn/s: 0.00 [ 60s ] thds: 128 tps: 70618.49 qps: 70618.49 (r/w/o: 0.00/5397.96/65220.53) lat (ms,95%): 7.30 err/s: 0.00 reconn/s: 0.00 SQL statistics: queries performed: read: 0 write: 317590 other: 3831576 total: 4149166 transactions: 4149166 (69139.18 per sec.) queries: 4149166 (69139.18 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.)
General statistics: total time: 60.0095s total number of events: 4149166
Latency (ms): min: 0.38 avg: 1.85 max: 377.15 95th percentile: 7.56 sum: 7678619.41
Threads fairness: events (avg/stddev): 32415.3594/274.75 execution time (avg/stddev): 59.9892/0.00
删除 sysbench 库并重复准备数据与预热步骤
sysbench --config-file=config oltp_update_non_index --time=60 --tables=10 --table-size=10000 --threads=256 run
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options: Number of threads: 256 Report intermediate results every 10 second(s) Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 10s ] thds: 256 tps: 87955.19 qps: 87955.19 (r/w/o: 0.00/6729.73/81225.46) lat (ms,95%): 9.73 err/s: 0.00 reconn/s: 0.00 [ 20s ] thds: 256 tps: 96628.06 qps: 96628.06 (r/w/o: 0.00/7407.02/89221.04) lat (ms,95%): 9.56 err/s: 0.00 reconn/s: 0.00 [ 30s ] thds: 256 tps: 95062.89 qps: 95062.89 (r/w/o: 0.00/7270.08/87792.82) lat (ms,95%): 9.73 err/s: 0.00 reconn/s: 0.00 [ 40s ] thds: 256 tps: 94046.04 qps: 94046.04 (r/w/o: 0.00/7194.41/86851.63) lat (ms,95%): 9.56 err/s: 0.00 reconn/s: 0.00 [ 50s ] thds: 256 tps: 93017.80 qps: 93017.80 (r/w/o: 0.00/7122.09/85895.71) lat (ms,95%): 10.27 err/s: 0.00 reconn/s: 0.00 [ 60s ] thds: 256 tps: 93112.56 qps: 93112.56 (r/w/o: 0.00/7092.97/86019.59) lat (ms,95%): 10.46 err/s: 0.00 reconn/s: 0.00 SQL statistics: queries performed: read: 0 write: 428257 other: 5170527 total: 5598784 transactions: 5598784 (93271.57 per sec.) queries: 5598784 (93271.57 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.)
General statistics: total time: 60.0244s total number of events: 5598784
Latency (ms): min: 0.41 avg: 2.74 max: 472.21 95th percentile: 9.91 sum: 15359682.43
Threads fairness: events (avg/stddev): 21870.2500/254.24 execution time (avg/stddev): 59.9988/0.01
删除 sysbench 库并重复准备数据与预热步骤
sysbench --config-file=config oltp_update_non_index --time=60 --tables=10 --table-size=10000 --threads=512 run
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options: Number of threads: 512 Report intermediate results every 10 second(s) Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 10s ] thds: 512 tps: 123330.73 qps: 123330.73 (r/w/o: 0.00/9443.01/113887.73) lat (ms,95%): 14.21 err/s: 0.00 reconn/s: 0.00 [ 20s ] thds: 512 tps: 114401.02 qps: 114401.02 (r/w/o: 0.00/8766.02/105635.00) lat (ms,95%): 15.83 err/s: 0.00 reconn/s: 0.00 [ 30s ] thds: 512 tps: 113775.16 qps: 113775.16 (r/w/o: 0.00/8666.30/105108.86) lat (ms,95%): 14.73 err/s: 0.00 reconn/s: 0.00 [ 40s ] thds: 512 tps: 113804.98 qps: 113804.98 (r/w/o: 0.00/8701.90/105103.08) lat (ms,95%): 14.21 err/s: 0.00 reconn/s: 0.00 [ 50s ] thds: 512 tps: 112099.29 qps: 112099.19 (r/w/o: 0.00/8601.19/103498.00) lat (ms,95%): 13.95 err/s: 0.00 reconn/s: 0.00 [ 60s ] thds: 512 tps: 111256.23 qps: 111256.33 (r/w/o: 0.00/8513.00/102743.33) lat (ms,95%): 13.70 err/s: 0.00 reconn/s: 0.00 SQL statistics: queries performed: read: 0 write: 527106 other: 6360786 total: 6887892 transactions: 6887892 (114541.03 per sec.) queries: 6887892 (114541.03 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.)
General statistics: total time: 60.1324s total number of events: 6887892
Latency (ms): min: 0.42 avg: 4.46 max: 549.31 95th percentile: 14.46 sum: 30726510.75
Threads fairness: events (avg/stddev): 13452.9141/147.46 execution time (avg/stddev): 60.0127/0.02
删除 sysbench 库并重复准备数据与预热步骤
sysbench --config-file=config oltp_update_non_index --time=60 --tables=10 --table-size=10000 --threads=1024 run
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options: Number of threads: 1024 Report intermediate results every 10 second(s) Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 10s ] thds: 1024 tps: 110784.90 qps: 110784.80 (r/w/o: 0.00/8463.96/102320.85) lat (ms,95%): 30.81 err/s: 0.00 reconn/s: 0.00 [ 20s ] thds: 1024 tps: 109123.75 qps: 109123.85 (r/w/o: 0.00/8322.68/100801.17) lat (ms,95%): 31.94 err/s: 0.00 reconn/s: 0.00 [ 30s ] thds: 1024 tps: 106744.77 qps: 106744.67 (r/w/o: 0.00/8183.87/98560.81) lat (ms,95%): 31.94 err/s: 0.00 reconn/s: 0.00 [ 40s ] thds: 1024 tps: 107987.95 qps: 107988.05 (r/w/o: 0.00/8220.79/99767.26) lat (ms,95%): 30.26 err/s: 0.00 reconn/s: 0.00 [ 50s ] thds: 1024 tps: 110048.73 qps: 110048.63 (r/w/o: 0.00/8452.61/101596.02) lat (ms,95%): 24.83 err/s: 0.00 reconn/s: 0.00 [ 60s ] thds: 1024 tps: 110101.85 qps: 110101.65 (r/w/o: 0.00/8448.46/101653.19) lat (ms,95%): 23.10 err/s: 0.00 reconn/s: 0.00 SQL statistics: queries performed: read: 0 write: 501246 other: 6048782 total: 6550028 transactions: 6550028 (108652.73 per sec.) queries: 6550028 (108652.73 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.)
General statistics: total time: 60.2818s total number of events: 6550028
Latency (ms): min: 0.59 avg: 9.38 max: 548.50 95th percentile: 29.19 sum: 61455837.91
Threads fairness: events (avg/stddev): 6396.5117/78.66 execution time (avg/stddev): 60.0155/0.03