fengttt commented 5 months ago

Parent Issue

14469

Detail of Subtask

Profiling of SSB point select with prepared statement. We have a profiling not using prepared statement. For this case, prepared statement is more appropriate.

Describe implementation you've considered

No response

Additional information

No response

gouhongshen commented 4 months ago

commit id: main 143dacad30b9fb236caaf0456c57bc456bcdb2b4
1 table, 10000 rows

point select on pk, 3mins, prepared

test method

./start.sh -n 1 -s 10000 -m SYSBENCH -b sbtest
./start.sh -c cases/sysbench/point_select_1_10000_prepare -d 3 -t 150 -g -b sbtest

|

50 terminal cpu usage: 1350+/2400

VUSER : 50
TPS : 42033
QPS : 42033
SUCCESS : 7567250
ERROR : 0
RT_MAX : 104
RT_MIN : 0
RT_AVG : 1.19
SUC_RATE : 1.0
EXP_RATE : 1.0
RESULT : SUCCEED

|

100 terminal cpu usage: 1450+/2400

VUSER : 100
TPS : 48010
QPS : 48010
SUCCESS : 8647335
ERROR : 0
RT_MAX : 131
RT_MIN : 0
RT_AVG : 2.08
SUC_RATE : 1.0
EXP_RATE : 1.0
RESULT : SUCCEED

|

150 terminal cpu usage: 1450+/2400

VUSER : 150
TPS : 52896
QPS : 52896
SUCCESS : 9525735
ERROR : 0
RT_MAX : 150
RT_MIN : 0
RT_AVG : 2.83
SUC_RATE : 1.0
EXP_RATE : 1.0
RESULT : SUCCEED

profiles.zip

继续增大 rpc server worker 和 max active txn 无法继续提升 cpu 利用率

point select on index, 3mins, prepared

mysql> describe sbtest1;
+-------+-----------+------+------+---------+-------+---------+
| Field | Type      | Null | Key  | Default | Extra | Comment |
+-------+-----------+------+------+---------+-------+---------+
| id    | INT(32)   | NO   | PRI  | NULL    |       |         |
| k     | INT(32)   | YES  | MUL  | NULL    |       |         |
| c     | CHAR(120) | YES  |      | NULL    |       |         |
| pad   | CHAR(60)  | YES  |      | NULL    |       |         |
+-------+-----------+------+------+---------+-------+---------+

test method

create table sbtest1(id int primary key, k int, c char(120), pad char(60), index(k));
insert into sbtest1 select *,*,'28348607720-82308235343-61766700902-17865586738-65137628671-11247199627-08955302215-19562417538-32146707472-43659467191', '28811013588-84732693547-67107900519-26538179060-18134790438' from generate_series(1, 10000)g
;
./start.sh -c cases/sysbench/point_select_on_index_1_10000_prepare -d 3 -t 150 -g -b sbtest
(i.e. select id, k from sbtest1 where k = ?)

150 terminals, 2200/2400 cpu usage

[point_select_1_10000_prepare]
START : 2024-03-20 10:38:42
END : 2024-03-20 10:41:54
VUSER : 150
TPS : 1003
QPS : 1003
SUCCESS : 180871
ERROR : 0
RT_MAX : 1399
RT_MIN : 13
RT_AVG : 149.40
SUC_RATE : 1.0
EXP_RATE : 1.0
RESULT : SUCCEED

profile_150_index.out.zip

gouhongshen commented 3 months ago

 1 tbl, 10000 rows
commit id: main 2fbb9ea7295d01d2a9de30932be951bb2bfcd5fc

point select on pk, prepared

150 terminals, 1450/2400 cpu usage

[point_select_1_10000_prepare]
START : 2024-03-20 09:49:49
END : 2024-03-20 09:53:01
VUSER : 150
TPS : 47297
QPS : 47297
SUCCESS : 8518062
ERROR : 0
RT_MAX : 288
RT_MIN : 0
RT_AVG : 3.17
SUC_RATE : 1.0
EXP_RATE : 1.0
RESULT : SUCCEED

200 terminals, 1550/2400 cpu usage

[point_select_1_10000_prepare]
START : 2024-03-20 09:44:28
END : 2024-03-20 09:47:41
VUSER : 200
TPS : 47028
QPS : 47028
SUCCESS : 8471274
ERROR : 0
RT_MAX : 330
RT_MIN : 0
RT_AVG : 4.25
SUC_RATE : 1.0
EXP_RATE : 1.0
RESULT : SUCCEED

profiles: profiles.zip

point select on index, prepared

150 terminals, 1600/2400 cpu usage

[point_select_1_10000_prepare]
START : 2024-03-20 10:49:56
END : 2024-03-20 10:53:08
VUSER : 150
TPS : 29760
QPS : 29760
SUCCESS : 5358225
ERROR : 0
RT_MAX : 445
RT_MIN : 0
RT_AVG : 5.04
SUC_RATE : 1.0
EXP_RATE : 1.0
RESULT : SUCCEED

profile_150_index.out.zip

200 terminals, 1650/2400 cpu usage

[point_select_1_10000_prepare]
START : 2024-03-20 10:54:38
END : 2024-03-20 10:57:41
VUSER : 200
TPS : 29273
QPS : 29273
SUCCESS : 4176264
ERROR : 200
RT_MAX : 487
RT_MIN : 0
RT_AVG : 6.83
SUC_RATE : 1.0
EXP_RATE : 1.0
RESULT : SUCCEED

profile_200_index.out.zip