Greenplum RT高的原因分析 和 优化方法
背景
本文测试Greenplum的SELECT, INSERT, UPDATE, DELETE并发性能。
为了方便观察日志,打开SQL审计和时间统计。
$gpconfig -c log_duration -v on
$gpconfig -c log_statement -v all
$gpconfig -c log_error_verbosity -v verbose
$gpconfig -c log_connections -v on
$gpconfig -c log_disconnections -v on
$gpstop -u
创建测试表
digoal=# create table test(id int primary key,info text,crt_time timestamp default now()) distributed by(id);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" for table "test"
CREATE TABLE
往test表插入5000万记录
digoal=# create or replace function f_test() returns void as $$
declare
begin
for i in 1..10 loop
execute 'insert into test select generate_series('||(i-1)*5000000+1||','||i*5000000||'),md5(random()::text),clock_timestamp()';
end loop;
end;
$$ language plpgsql;
digoal=# select f_test();
digoal=# select count(*) from test;
count
----------
50000000
(1 row)
Time: 1247.616 ms
digoal=# select pg_size_pretty(pg_total_relation_size('test'));
pg_size_pretty
----------------
4698 MB
(1 row)
Time: 98.021 ms
digoal=# select * from test where id=1;
id | info | crt_time
----+----------------------------------+----------------------------
1 | 9beb4821d0e80d0c1a559cb214a2aec6 | 2015-11-18 15:58:08.046341
(1 row)
Time: 8.290 ms
并发查询测试
测试case, 基于主键的查询
$cat test.sql
\setrandom id 1 50000000
select * from test where id=:id;
使用绑定变量,RT非常高,达到了350毫秒左右,简单的基于主键的查询,不应该这么差。因为直接查询RT只有8毫秒左右。
$./pgsql9.5/bin/pgbench -M prepared -n -r -f ./test.sql -P 1 -c 10 -j 10 -T 10
progress: 1.0 s, 19.0 tps, lat 436.853 ms stddev 85.672
progress: 2.0 s, 32.0 tps, lat 303.730 ms stddev 44.311
progress: 3.0 s, 25.0 tps, lat 407.980 ms stddev 155.949
progress: 4.0 s, 30.0 tps, lat 316.631 ms stddev 37.998
progress: 5.0 s, 15.0 tps, lat 533.454 ms stddev 168.532
progress: 6.0 s, 26.0 tps, lat 468.524 ms stddev 180.508
progress: 7.0 s, 37.0 tps, lat 281.583 ms stddev 64.004
progress: 8.0 s, 32.0 tps, lat 290.920 ms stddev 46.006
progress: 9.0 s, 34.0 tps, lat 318.505 ms stddev 93.583
progress: 10.0 s, 39.0 tps, lat 266.336 ms stddev 46.304
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 10
number of threads: 10
duration: 10 s
number of transactions actually processed: 299
latency average: 341.109 ms
latency stddev: 122.844 ms
tps = 29.077564 (including connections establishing)
tps = 29.121370 (excluding connections establishing)
statement latencies in milliseconds:
0.004977 \setrandom id 1 50000000
341.103719 select * from test where id=:id;
换成非绑定变量的模式,测试结果相对更理想一些。
$./pgsql9.5/bin/pgbench -M simple -n -r -f ./test.sql -P 1 -c 10 -j 10 -T 10
progress: 1.0 s, 866.8 tps, lat 11.301 ms stddev 16.360
progress: 2.0 s, 1099.2 tps, lat 9.066 ms stddev 6.553
progress: 3.0 s, 1088.0 tps, lat 9.223 ms stddev 2.377
progress: 4.0 s, 1048.0 tps, lat 9.491 ms stddev 3.672
progress: 5.0 s, 1014.0 tps, lat 9.901 ms stddev 7.344
progress: 6.0 s, 1114.0 tps, lat 8.969 ms stddev 2.185
progress: 7.0 s, 1054.9 tps, lat 9.228 ms stddev 9.006
progress: 8.0 s, 1092.1 tps, lat 9.398 ms stddev 8.967
progress: 9.0 s, 1132.0 tps, lat 8.851 ms stddev 6.297
progress: 10.0 s, 1101.1 tps, lat 9.072 ms stddev 2.497
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 10
duration: 10 s
number of transactions actually processed: 10620
latency average: 9.404 ms
latency stddev: 7.447 ms
tps = 1060.509136 (including connections establishing)
tps = 1062.070585 (excluding connections establishing)
statement latencies in milliseconds:
0.004292 \setrandom id 1 50000000
9.398578 select * from test where id=:id;
分析为什么查询使用绑定变量会慢?
跟踪任何一个segment,发现使用绑定变量,一个select * from test where id=?查询要执行这么多。
一个segment被连接了两次,并且使用了分布式提交。
2015-11-18 16:44:06.679631 CST,,,p20059,th-1976764640,,,2015-11-18 16:44:06 CST,0,,,seg-1,,,,,"LOG","00000","connection received: host=digoal port=29419",,,,,,,0,,"postmaster.c",6827,
2015-11-18 16:44:06.680069 CST,"digoal","digoal",p20059,th-1976764640,"digoal","29419",2015-11-18 16:44:06 CST,0,con495,,seg-1,,,,,"LOG","00000","connection authorized: user=digoal database=digoal",,,,,,,0,,"postmaster.c",6886,
2015-11-18 16:44:06.681030 CST,"digoal","digoal",p20059,th-1976764640,"digoal","29419",2015-11-18 16:44:06 CST,306540,con495,,seg-1,,,x306540,sx1,"LOG","00000","connection authorized: user=digoal database=digoal",,,,,,,0,,"postinit.c",260,
2015-11-18 16:44:06.861170 CST,"digoal","digoal",p20059,th-1976764640,"digoal","29419",2015-11-18 16:44:06 CST,306541,con495,,seg4,,,x306541,sx1,"LOG","00000","statement: select pg_relation_size('public.test')",,,,,,"select pg_relation_size('public.test')",0,,"postgres.c",1552,
2015-11-18 16:44:06.864331 CST,"digoal","digoal",p20059,th-1976764640,"digoal","29419",2015-11-18 16:44:06 CST,0,con495,,seg4,,,,,"LOG","00000","duration: 3.366 ms",,,,,,"select pg_relation_size('public.test')",0,,"postgres.c",1813,
2015-11-18 16:44:06.867662 CST,"digoal","digoal",p20059,th-1976764640,"digoal","29419",2015-11-18 16:44:06 CST,306542,con495,,seg4,,,x306542,sx1,"LOG","00000","statement: select pg_relation_size('public.test_pkey')",,,,,,"select pg_relation_size('public.test_pkey')",0,,"postgres.c",1552,
2015-11-18 16:44:06.868117 CST,"digoal","digoal",p20059,th-1976764640,"digoal","29419",2015-11-18 16:44:06 CST,0,con495,,seg4,,,,,"LOG","00000","duration: 0.497 ms",,,,,,"select pg_relation_size('public.test_pkey')",0,,"postgres.c",1813,
2015-11-18 16:44:06.872087 CST,"digoal","digoal",p20059,th-1976764640,"digoal","29419",2015-11-18 16:44:06 CST,306543,con495,cmd1,seg4,,dx155312,x306543,sx1,"LOG","00000","statement: set gp_write_shared_snapshot=true",,,,,,"set gp_write_shared_snapshot=true",0,,"postgres.c",1552,
2015-11-18 16:44:06.872327 CST,"digoal","digoal",p20059,th-1976764640,"digoal","29419",2015-11-18 16:44:06 CST,306543,con495,cmd1,seg4,,dx155312,x306543,sx1,"LOG","00000","duration: 0.338 ms",,,,,,"set gp_write_shared_snapshot=true",0,,"postgres.c",1813,
2015-11-18 16:44:06.902412 CST,,,p20083,th-1976764640,,,2015-11-18 16:44:06 CST,0,,,seg-1,,,,,"LOG","00000","connection received: host=digoal port=29443",,,,,,,0,,"postmaster.c",6827,
2015-11-18 16:44:06.902839 CST,"digoal","digoal",p20083,th-1976764640,"digoal","29443",2015-11-18 16:44:06 CST,0,con495,,seg-1,,,,,"LOG","00000","connection authorized: user=digoal database=digoal",,,,,,,0,,"postmaster.c",6886,
2015-11-18 16:44:06.903765 CST,"digoal","digoal",p20083,th-1976764640,"digoal","29443",2015-11-18 16:44:06 CST,306544,con495,,seg-1,,,x306544,sx1,"LOG","00000","connection authorized: user=digoal database=digoal",,,,,,,0,,"postinit.c",260,
2015-11-18 16:44:07.059133 CST,"digoal","digoal",p20059,th-1976764640,"digoal","29419",2015-11-18 16:44:06 CST,306543,con495,cmd1,seg4,,dx155312,x306543,sx1,"LOG","00000","statement: set gp_write_shared_snapshot=true",,,,,,"set gp_write_shared_snapshot=true",0,,"postgres.c",1552,
2015-11-18 16:44:07.059181 CST,"digoal","digoal",p20059,th-1976764640,"digoal","29419",2015-11-18 16:44:06 CST,306543,con495,cmd1,seg4,,dx155312,x306543,sx1,"LOG","00000","duration: 0.286 ms",,,,,,"set gp_write_shared_snapshot=true",0,,"postgres.c",1813,
2015-11-18 16:44:07.070201 CST,"digoal","digoal",p20059,th-1976764640,"digoal","29419",2015-11-18 16:44:06 CST,306543,con495,,seg4,,dx155312,x306543,sx1,"LOG","00000","DTM protocol command 'Distributed Prepare' for gid = 1447825268-0000155312",,,,,,,0,,"postgres.c",1406,
2015-11-18 16:44:07.112960 CST,"digoal","digoal",p20059,th-1976764640,"digoal","29419",2015-11-18 16:44:06 CST,0,con495,,seg4,,,,,"LOG","00000","DTM protocol command 'Distributed Commit Prepared' for gid = 1447825268-0000155312",,,,,,,0,,"postgres.c",1406,
2015-11-18 16:44:07.137484 CST,"digoal","digoal",p20083,th-1976764640,"digoal","29443",2015-11-18 16:44:06 CST,0,con495,,seg4,,,,,"LOG","00000","disconnection: session time: 0:00:00.235 user=digoal database=digoal host=digoal port=29443",,,,,,,0,,"postgres.c",5376,
2015-11-18 16:44:07.138548 CST,"digoal","digoal",p20059,th-1976764640,"digoal","29419",2015-11-18 16:44:06 CST,0,con495,,seg4,,,,,"LOG","00000","disconnection: session time: 0:00:00.459 user=digoal database=digoal host=digoal port=29419",,,,,,,0,,"postgres.c",5376,
使用simple query则不需要分布式提交,但是每个节点还是有一个这样的查询,比如查询表的大小,索引的大小。
2015-11-18 16:50:17.016604 CST,,,p24716,th-1976764640,,,2015-11-18 16:50:17 CST,0,,,seg-1,,,,,"LOG","00000","connection received: host=digoal port=30716",,,,,,,0,,"postmaster.c",6827,
2015-11-18 16:50:17.017063 CST,"digoal","digoal",p24716,th-1976764640,"digoal","30716",2015-11-18 16:50:17 CST,0,con556,,seg-1,,,,,"LOG","00000","connection authorized: user=digoal database=digoal",,,,,,,0,,"postmaster.c",6886,
2015-11-18 16:50:17.018025 CST,"digoal","digoal",p24716,th-1976764640,"digoal","30716",2015-11-18 16:50:17 CST,315601,con556,,seg-1,,,x315601,sx1,"LOG","00000","connection authorized: user=digoal database=digoal",,,,,,,0,,"postinit.c",260,
2015-11-18 16:50:17.205188 CST,"digoal","digoal",p24716,th-1976764640,"digoal","30716",2015-11-18 16:50:17 CST,315602,con556,cmd1,seg4,,,x315602,sx1,"LOG","00000","statement: select pg_relation_size('public.test')",,,,,,"select pg_relation_size('public.test')",0,,"postgres.c",1552,
2015-11-18 16:50:17.207785 CST,"digoal","digoal",p24716,th-1976764640,"digoal","30716",2015-11-18 16:50:17 CST,0,con556,cmd1,seg4,,,,,"LOG","00000","duration: 2.157 ms",,,,,,"select pg_relation_size('public.test')",0,,"postgres.c",1813,
2015-11-18 16:50:17.212605 CST,"digoal","digoal",p24716,th-1976764640,"digoal","30716",2015-11-18 16:50:17 CST,315603,con556,cmd1,seg4,,,x315603,sx1,"LOG","00000","statement: select pg_relation_size('public.test_pkey')",,,,,,"select pg_relation_size('public.test_pkey')",0,,"postgres.c",1552,
2015-11-18 16:50:17.212903 CST,"digoal","digoal",p24716,th-1976764640,"digoal","30716",2015-11-18 16:50:17 CST,0,con556,cmd1,seg4,,,,,"LOG","00000","duration: 0.397 ms",,,,,,"select pg_relation_size('public.test_pkey')",0,,"postgres.c",1813,
2015-11-18 16:50:17.224415 CST,"digoal","digoal",p24716,th-1976764640,"digoal","30716",2015-11-18 16:50:17 CST,0,con556,,seg4,,,,,"LOG","00000","disconnection: session time: 0:00:00.207 user=digoal database=digoal host=digoal port=30716",,,,,,,0,,"postgres.c",5376,
接下来测试一下insert, update, delete
并发更新测试
update,单进程基准性能
digoal=# update test set info=info where id=1;
UPDATE 1
Time: 21.575 ms
update 并发性能测试
$vi test.sql
\setrandom id 1 50000000
update test set info=info where id=:id;
$./pgsql9.5/bin/pgbench -M prepared -n -r -f ./test.sql -P 1 -c 10 -j 10 -T 10
progress: 1.0 s, 20.0 tps, lat 410.439 ms stddev 98.644
progress: 2.0 s, 34.0 tps, lat 304.004 ms stddev 27.661
progress: 3.0 s, 29.0 tps, lat 338.387 ms stddev 44.731
progress: 4.0 s, 31.0 tps, lat 311.467 ms stddev 40.406
progress: 5.0 s, 29.0 tps, lat 347.463 ms stddev 34.026
progress: 6.0 s, 27.0 tps, lat 373.772 ms stddev 37.217
progress: 7.0 s, 21.0 tps, lat 447.816 ms stddev 125.024
progress: 8.0 s, 29.0 tps, lat 351.473 ms stddev 60.039
progress: 9.0 s, 25.0 tps, lat 423.574 ms stddev 45.458
progress: 10.0 s, 37.0 tps, lat 275.829 ms stddev 27.409
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 10
number of threads: 10
duration: 10 s
number of transactions actually processed: 292
latency average: 346.487 ms
latency stddev: 77.228 ms
tps = 28.440704 (including connections establishing)
tps = 28.486822 (excluding connections establishing)
statement latencies in milliseconds:
0.005226 \setrandom id 1 50000000
346.480860 update test set info=info where id=:id;
$./pgsql9.5/bin/pgbench -M simple -n -r -f ./test.sql -P 1 -c 10 -j 10 -T 10
progress: 1.0 s, 4.0 tps, lat 598.394 ms stddev 256.699
progress: 2.0 s, 4.0 tps, lat 1497.661 ms stddev 247.360
progress: 3.0 s, 21.0 tps, lat 927.265 ms stddev 707.750
progress: 4.0 s, 29.0 tps, lat 359.723 ms stddev 42.065
progress: 5.0 s, 29.0 tps, lat 351.328 ms stddev 23.978
progress: 6.0 s, 33.0 tps, lat 302.434 ms stddev 14.584
progress: 7.0 s, 29.0 tps, lat 340.723 ms stddev 11.129
progress: 8.0 s, 30.0 tps, lat 328.687 ms stddev 23.626
progress: 9.0 s, 29.0 tps, lat 349.512 ms stddev 30.368
progress: 10.0 s, 30.0 tps, lat 339.405 ms stddev 40.958
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 10
duration: 10 s
number of transactions actually processed: 248
latency average: 409.842 ms
latency stddev: 304.170 ms
tps = 23.951046 (including connections establishing)
tps = 23.987521 (excluding connections establishing)
statement latencies in milliseconds:
0.005238 \setrandom id 1 50000000
409.835895 update test set info=info where id=:id;
update 并发性能分析
使用1个连接或多个连接,最终的TPS几乎一致。和GP在处理update SQL的锁有关。
例如:
会话A
digoal=# begin;
BEGIN
digoal=# update test set info=info where id=100000;
UPDATE 1
digoal=# select pg_backend_pid();
pg_backend_pid
----------------
32357
(1 row)
会话B
digoal=# update test set info=info where id=100001;
等待。
原因是会话A持有表锁。
digoal=# select * from pg_locks where pid=32357;
-[ RECORD 1 ]-+-----------------
locktype | relation
database | 17144
relation | 17530
page |
tuple |
transactionid |
classid |
objid |
objsubid |
transaction | 163903
pid | 32357
mode | RowExclusiveLock
granted | t
mppsessionid | 603
mppiswriter | t
gp_segment_id | -1
-[ RECORD 2 ]-+-----------------
locktype | relation
database | 17144
relation | 17501
page |
tuple |
transactionid |
classid |
objid |
objsubid |
transaction | 163903
pid | 32357
mode | ExclusiveLock
granted | t
mppsessionid | 603
mppiswriter | t
gp_segment_id | -1
-[ RECORD 3 ]-+-----------------
locktype | transactionid
database |
relation |
page |
tuple |
transactionid | 163903
classid |
objid |
objsubid |
transaction | 163903
pid | 32357
mode | ExclusiveLock
granted | t
mppsessionid | 603
mppiswriter | t
gp_segment_id | -1
并发删除测试
delete,单进程基准性能
digoal=# \timing
Timing is on.
digoal=# delete from test where id=1;
DELETE 1
Time: 240.666 ms
digoal=# delete from test where id=2;
DELETE 1
Time: 36.234 ms
digoal=# delete from test where id=3;
DELETE 1
Time: 28.856 ms
delete 并发性能测试
$vi test.sql
\setrandom id 1 50000000
delete from test where id=:id;
$./pgsql9.5/bin/pgbench -M simple -n -r -f ./test.sql -P 1 -c 10 -j 10 -T 10
progress: 1.0 s, 4.0 tps, lat 601.661 ms stddev 263.427
progress: 2.0 s, 4.0 tps, lat 1511.570 ms stddev 247.955
progress: 3.0 s, 25.0 tps, lat 800.810 ms stddev 691.464
progress: 4.0 s, 35.0 tps, lat 286.888 ms stddev 11.161
progress: 5.0 s, 37.0 tps, lat 264.560 ms stddev 13.016
progress: 6.0 s, 34.0 tps, lat 296.716 ms stddev 11.332
progress: 7.0 s, 32.0 tps, lat 275.880 ms stddev 26.188
progress: 8.0 s, 26.0 tps, lat 412.082 ms stddev 147.997
progress: 9.0 s, 17.0 tps, lat 461.175 ms stddev 171.816
progress: 10.0 s, 34.0 tps, lat 379.339 ms stddev 190.013
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 10
duration: 10 s
number of transactions actually processed: 258
latency average: 392.610 ms
latency stddev: 318.515 ms
tps = 25.113468 (including connections establishing)
tps = 25.147542 (excluding connections establishing)
statement latencies in milliseconds:
0.005252 \setrandom id 1 50000000
392.604167 delete from test where id=:id;
$./pgsql9.5/bin/pgbench -M prepared -n -r -f ./test.sql -P 1 -c 10 -j 10 -T 10
progress: 1.0 s, 26.0 tps, lat 332.305 ms stddev 115.346
progress: 2.0 s, 31.0 tps, lat 315.662 ms stddev 99.036
progress: 3.0 s, 40.0 tps, lat 255.953 ms stddev 13.549
progress: 4.0 s, 43.0 tps, lat 232.854 ms stddev 6.878
progress: 5.0 s, 41.0 tps, lat 243.544 ms stddev 11.044
progress: 6.0 s, 35.0 tps, lat 263.554 ms stddev 23.382
progress: 7.0 s, 29.0 tps, lat 375.702 ms stddev 183.176
progress: 8.0 s, 37.0 tps, lat 259.230 ms stddev 16.053
progress: 9.0 s, 37.0 tps, lat 271.520 ms stddev 13.303
progress: 10.0 s, 26.0 tps, lat 320.553 ms stddev 148.474
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 10
number of threads: 10
duration: 10 s
number of transactions actually processed: 355
latency average: 284.995 ms
latency stddev: 98.065 ms
tps = 34.391873 (including connections establishing)
tps = 34.442359 (excluding connections establishing)
statement latencies in milliseconds:
0.005192 \setrandom id 1 50000000
284.989341 delete from test where id=:id;
delete 并发性能分析
使用1个连接或多个连接,最终的TPS几乎一致。和GP在处理delete SQL的锁有关。
delete也是持有表锁。
并发插入测试
insert,单进程基准性能
digoal=# create table t(id int, info text, crt_time timestamp) distributed by (id);
CREATE TABLE
Time: 156.959 ms
digoal=# insert into t values (1);
INSERT 0 1
Time: 113.875 ms
digoal=# insert into t values (1);
INSERT 0 1
Time: 25.633 ms
digoal=# insert into t values (1);
INSERT 0 1
Time: 21.263 ms
insert 并发性能测试
$vi test.sql
\setrandom id 1 50000000
insert into t values (:id);
$./pgsql9.5/bin/pgbench -M simple -n -r -f ./test.sql -P 1 -c 10 -j 10 -T 10
progress: 1.0 s, 249.0 tps, lat 38.180 ms stddev 50.487
progress: 2.0 s, 366.0 tps, lat 27.815 ms stddev 16.822
progress: 3.0 s, 296.0 tps, lat 33.613 ms stddev 34.803
progress: 4.0 s, 296.0 tps, lat 33.725 ms stddev 10.792
progress: 5.0 s, 274.0 tps, lat 36.624 ms stddev 17.423
progress: 6.0 s, 280.0 tps, lat 35.980 ms stddev 35.385
progress: 7.0 s, 356.0 tps, lat 28.166 ms stddev 7.185
progress: 8.0 s, 333.0 tps, lat 29.745 ms stddev 9.597
progress: 9.0 s, 257.9 tps, lat 32.871 ms stddev 24.897
progress: 10.0 s, 338.1 tps, lat 34.008 ms stddev 31.587
transaction type: Custom query
scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 10
duration: 10 s
number of transactions actually processed: 3056
latency average: 32.815 ms
latency stddev: 26.684 ms
tps = 302.610331 (including connections establishing)
tps = 303.090986 (excluding connections establishing)
statement latencies in milliseconds:
0.004006 \setrandom id 1 50000000
32.810048 insert into t values (:id);
$./pgsql9.5/bin/pgbench -M prepared -n -r -f ./test.sql -P 1 -c 10 -j 10 -T 10
progress: 1.0 s, 236.0 tps, lat 41.008 ms stddev 56.402
progress: 2.0 s, 310.0 tps, lat 32.330 ms stddev 29.544
progress: 3.0 s, 376.0 tps, lat 26.737 ms stddev 4.911
progress: 4.0 s, 348.0 tps, lat 28.565 ms stddev 9.450
progress: 5.0 s, 112.0 tps, lat 89.650 ms stddev 132.283
progress: 6.0 s, 362.0 tps, lat 27.394 ms stddev 6.078
progress: 7.0 s, 368.0 tps, lat 27.373 ms stddev 6.049
progress: 8.0 s, 364.0 tps, lat 27.397 ms stddev 6.315
progress: 9.0 s, 356.0 tps, lat 28.029 ms stddev 9.189
progress: 10.0 s, 322.0 tps, lat 31.181 ms stddev 28.778
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 10
number of threads: 10
duration: 10 s
number of transactions actually processed: 3164
latency average: 31.609 ms
latency stddev: 34.630 ms
tps = 315.497708 (including connections establishing)
tps = 315.990969 (excluding connections establishing)
statement latencies in milliseconds:
0.003991 \setrandom id 1 50000000
31.604701 insert into t values (:id);
小结
1. 查询,使用绑定变量会产生分布式提交,效率很低;所以查询使用simple query效率(响应时间更快)更高,增加并发可以提高tps。
2. 更新,持有表级排他锁,是否绑定变量效率都差不多。根据键值更新不会产生分布式事务,但是如果更新的是一个范围,会自动使用分布式事务提交。因为是表级排他锁,所以增加并发不能提高tps。
3. 删除,持有表级排他锁,是否绑定变量效率都差不多。根据键值删除不会产生分布式事务,但是如果删除的是一个范围,会自动使用分布式事务提交。因为是表级排他锁,所以增加并发不能提高tps。
4. 插入,是否绑定变量效率都差不多,没有表级排他锁,因此增加并发可以提高tps。