Greenplum RT高的原因分析 和 优化方法

10 minute read

背景

本文测试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。

Flag Counter

digoal’s 大量PostgreSQL文章入口