postgresql無序uuid效能測試

月圖靈發表於2021-06-10

SQLUI

無序uuid對資料庫的影響

由於最近在做超大表的效能測試，在該過程中發現了無序uuid做主鍵對錶插入效能有一定影響。結合實際情況發現當表的資料量越大，對錶插入效能的影響也就越大。

測試環境

PostgreSQL建立插入指令碼，測試各種情況的tps。

資料庫版本：PostgreSQL 10.4 (ArteryBase 5.0.0, Thunisoft)

作業系統配置：CentOS Linux release 7 ，32GB記憶體，8 cpu

測試引數：pgbench -M prepared -r -n -j 8 -c 8 -T 60 -f /opt/thunisoft/pgbench_uuid_v4.sql -U sa pgbenchdb

空表，1000w資料，5000w資料，一億資料的各種主鍵測試。

測試無序的uuid，有序的uuid，序列，有普通btree，有唯一索引和沒有主鍵的情況

測試

1.建立表

--無序的uuid
pgbenchdb=# create table test_uuid_v4(id char(32) primary key);
CREATE TABLE
--有序的uuid
pgbenchdb=# create table test_time_nextval(id char(32) primary key);
CREATE TABLE
--遞增序列
pgbenchdb=# create table test_seq_bigint(id int8 primary key);
CREATE TABLE
--建立序列
 create sequence test_seq start with 1 ;

2.測試指令碼

--測試無序uuid指令碼
vi pgbench_uuid_v4.sql
insert into test_uuid_v4 (id) values (replace(uuid_generate_v4()::text,'-',''));
--測試有序uuid指令碼
vi pgbench_time_nextval.sql
insert into test_time_nextval (id) values (replace(uuid_time_nextval()::text,'-',''));
--測試序列指令碼
vi pgbench_seq_bigint.sql
insert into test_seq_bigint (id) values (nextval('test_seq'::regclass));

無序uuid,無資料情況

磁碟使用情況
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0.76    0.00    0.38    4.67    0.00   94.19

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sdb               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00    0.00    0.00   0.00   0.00
sda               0.00     0.00    0.00   96.00     0.00  2048.00    42.67     1.02   10.67    0.00   10.67  10.33  99.20
dm-0              0.00     0.00    0.00   96.00     0.00  2048.00    42.67     1.02   10.66    0.00   10.66  10.32  99.10
dm-1              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00    0.00    0.00   0.00   0.00
dm-2              0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00    0.00    0.00   0.00   0.00

tps：
[thunisoft@localhost thunisoft]$ pgbench -M prepared -r -n -j 8 -c 8 -T 60 -f /opt/thunisoft/pgbench_uuid_v4.sql -U sa pgbenchdb 
transaction type: /opt/thunisoft/pgbench_uuid_v4.sql
scaling factor: 1
query mode: prepared
number of clients: 8
number of threads: 8
duration: 60 s
number of transactions actually processed: 53494
latency average = 8.974 ms
tps = 891.495404 (including connections establishing)
tps = 891.588967 (excluding connections establishing)
script statistics:
 - statement latencies in milliseconds:
         9.006  insert into test_uuid_v4 (id) values (replace(uuid_generate_v4()::text,'-',''));

無資料情況下，tps

       類別     |  第一次  | 第二次  | 第三次 | 平均值(tps) |%util |await
---------------+---------+---------+---------+---------+-------+-------
 無序uuid		  | 919  	| 907     |  891  |   906     | 99.2% | 10.66   
 有序uuid    	  | 985  	| 882     |  932  |   933     | 98.7% | 4.4
 序列    	      | 1311     | 1277    |  1280 |  1289     | 97.5% | 3.4

向表裡面初始化100w資料

pgbenchdb=# insert into test_uuid_v4 (id) select  replace(uuid_generate_v4()::text,'-','') from generate_series(1,1000000);
INSERT 0 1000000
Time: 43389.817 ms (00:43.390)
pgbenchdb=# insert into test_time_nextval (id) select replace(uuid_time_nextval()::text,'-','') from generate_series(1,1000000);
INSERT 0 1000000
Time: 30585.134 ms (00:30.585)
pgbenchdb=#  insert into test_seq_bigint select generate_series (1,1000000);
INSERT 0 1000000
Time: 9818.639 ms (00:09.819)
無序uuid插入100w需要43s，有序需要30s，序列需要10s。

插入一百萬資料後的tps

       類別     |  第一次  | 第二次  | 第三次 | 平均值(tps) |%util |await
---------------+---------+---------+---------+---------+-------+-------
 無序uuid		  | 355  	| 440     |  302  |   365     | 98.8% | 13   
 有序uuid    	  | 948  	| 964     |  870  |   927     | 97.2% | 4.0
 序列    	      | 1159     | 1234    |  1115 |  1169     | 96.6% | 3.5

插入一千萬資料後的tps

       類別     |  第一次  | 第二次  | 第三次 | 平均值(tps) |%util |await
---------------+---------+---------+---------+---------+-------+-------
 無序uuid		  | 260  	| 292     |  227  |   260     | 99.2% | 16.8   
 有序uuid    	  | 817  	| 960     |  883  |   870     | 97.7% | 3.9
 序列       	   | 1305     | 1261    |  1270 |  1278     | 96.8% | 3.0

插入五千萬資料後

向表中插入5kw資料，並且新增主鍵
pgbenchdb=# insert into test_time_nextval (id) select replace(uuid_time_nextval()::text,'-','') from generate_series(1,50000000);
INSERT 0 50000000
Time: 453985.318 ms (07:33.985)
pgbenchdb=# insert into test_seq_bigint select generate_series (1,50000000);
INSERT 0 50000000
Time: 352206.160 ms (05:52.206)
pgbenchdb=# insert into test_uuid_v4 (id) select  replace(uuid_generate_v4()::text,'-','') from generate_series(1,50000000);
INSERT 0 50000000
Time: 1159689.338 ms (00:19:19.689)

在無主鍵情況下，插入五千萬資料，有序uuid耗時7分鐘，序列耗時6分鐘，而無序uuid耗時接近20分鐘。

pgbenchdb=# alter table test_uuid_v4 add primary key ("id");
ALTER TABLE
Time: 845199.296 ms (14:05.199)
pgbenchdb=# alter table test_time_nextval add primary key ("id");
ALTER TABLE
Time: 932151.103 ms (15:32.151)
pgbenchdb=# alter table test_seq_bigint add primary key ("id");
ALTER TABLE
Time: 148138.871 ms (02:28.139)

pgbenchdb=# select pg_size_pretty(pg_total_relation_size('test_uuid_v4'));
 pg_size_pretty 
----------------
 6072 MB
(1 row)

Time: 0.861 ms
pgbenchdb=#  select pg_size_pretty(pg_total_relation_size('test_time_nextval'));
 pg_size_pretty 
----------------
 6072 MB
(1 row)

Time: 0.942 ms
pgbenchdb=#  select pg_size_pretty(pg_total_relation_size('test_seq_bigint'));
 pg_size_pretty 
----------------
 2800 MB
(1 row)

Time: 0.699 ms

插入5kw後

       類別     |  第一次  | 第二次  | 第三次 | 平均值(tps) |%util |await
---------------+---------+---------+---------+---------+-------+-------
 無序uuid		  | 162  	| 163     |  163  |   163     | 99.6% | 18.4   
 有序uuid    	  | 738  	| 933     |  979  |   883     | 97.7% | 3.9
 序列         	 | 1132     | 1264    |  1265 |  1220     | 96.8% | 3.5

插入1億條資料後

       類別     |  第一次  | 第二次  | 第三次 | 平均值(tps) |%util |await
---------------+---------+---------+---------+---------+-------+-------
 無序uuid		  | 121  	| 131     |  143  |   131     | 99.6% | 28.2   
 有序uuid    	  | 819  	| 795     |  888  |   834     | 99.2% | 28.7
 序列      	    | 1193     | 1115    |  1109 |  1139     | 96.8% | 11.3

普通btree索引

上面測了無序uuid，1kw情況下，有主鍵的tps是260，無主鍵的tps是1234。嘗試測試普通的索引，和唯一索引tps

--建立普通索引
pgbenchdb=# create index i_test_uuid_v4_id on test_uuid_v4(id);
CREATE INDEX
Time: 316367.010 ms (05:16.367)
--建立普通索引後
[thunisoft@localhost thunisoft]$ pgbench -M prepared -r -n -j 8 -c 8 -T 60 -f /opt/thunisoft/pgbench_uuid_v4.sql -U sa pgbenchdb 
transaction type: /opt/thunisoft/pgbench_uuid_v4.sql
scaling factor: 1
query mode: prepared
number of clients: 8
number of threads: 8
duration: 60 s
number of transactions actually processed: 13308
latency average = 36.080 ms
tps = 221.727391 (including connections establishing)
tps = 221.749660 (excluding connections establishing)
script statistics:
 - statement latencies in milliseconds:
        38.512  insert into test_uuid_v4 (id) values (replace(uuid_generate_v4()::text,'-',''));
--建立唯一索引
pgbenchdb=# drop index i_test_uuid_v4_id;
DROP INDEX
Time: 267.451 ms
pgbenchdb=# create unique index i_test_uuid_v4_id on test_uuid_v4(id);
CREATE INDEX
Time: 153372.622 ms (02:33.373)
[thunisoft@localhost thunisoft]$ pgbench -M prepared -r -n -j 8 -c 8 -T 60 -f /opt/thunisoft/pgbench_uuid_v4.sql -U sa pgbenchdb 
^[[3~transaction type: /opt/thunisoft/pgbench_uuid_v4.sql
scaling factor: 1
query mode: prepared
number of clients: 8
number of threads: 8
duration: 60 s
number of transactions actually processed: 13847
latency average = 34.693 ms
tps = 230.593988 (including connections establishing)
tps = 230.620469 (excluding connections establishing)
script statistics:
 - statement latencies in milliseconds:
        36.410  insert into test_uuid_v4 (id) values (replace(uuid_generate_v4()::text,'-',''));

無論是普通btree索引和唯一索引，都會影響插入的效率。

刪除所有的主鍵索引

--刪除所有主鍵
alter table test_uuid_v4 drop constraint "test_uuid_v4_pkey";
alter table test_time_nextval drop constraint "test_time_nextval_pkey" ;
alter table test_seq_bigint drop constraint "test_seq_bigint_pkey";

1,--無序uuid：測試pgbench_uuid_v4.sql
[thunisoft@localhost thunisoft]$ pgbench -M prepared -r -n -j 8 -c 8 -T 60 -f /opt/thunisoft/pgbench_uuid_v4.sql -U sa pgbenchdb 
transaction type: /opt/thunisoft/pgbench_uuid_v4.sql
scaling factor: 1
query mode: prepared
number of clients: 8
number of threads: 8
duration: 60 s
number of transactions actually processed: 74109
latency average = 6.479 ms
tps = 1234.842229 (including connections establishing)
tps = 1235.042674 (excluding connections establishing)
script statistics:
 - statement latencies in milliseconds:
         6.112  insert into test_uuid_v4 (id) values (replace(uuid_generate_v4()::text,'-',''));

2、--有序uuid，測試pgbench_time_nextval.sql
[thunisoft@localhost thunisoft]$ pgbench -M prepared -r -n -j 8 -c 8 -T 60 -f /opt/thunisoft/pgbench_time_nextval.sql -U sa pgbenchdb 
transaction type: /opt/thunisoft/pgbench_time_nextval.sql
scaling factor: 1
query mode: prepared
number of clients: 8
number of threads: 8
duration: 60 s
number of transactions actually processed: 74027
latency average = 6.486 ms
tps = 1233.364360 (including connections establishing)
tps = 1233.482292 (excluding connections establishing)
script statistics:
 - statement latencies in milliseconds:
         6.186  insert into test_time_nextval (id) values (replace(uuid_time_nextval()::text,'-',''));
3、--序列，測試pgbench_seq_bigint.sql
[thunisoft@localhost thunisoft]$ pgbench -M prepared -r -n -j 8 -c 8 -T 60 -f /opt/thunisoft/pgbench_seq_bigint.sql -U sa pgbenchdb 
transaction type: /opt/thunisoft/pgbench_seq_bigint.sql
scaling factor: 1
query mode: prepared
number of clients: 8
number of threads: 8
duration: 60 s
number of transactions actually processed: 76312
latency average = 6.290 ms
tps = 1271.832907 (including connections establishing)
tps = 1272.124397 (excluding connections establishing)
script statistics:
 - statement latencies in milliseconds:
         5.916  insert into test_seq_bigint (id) values (nextval('test_seq'::regclass));

刪除主鍵約束後，三種情況下tps非常接近，都達到了1200+。

Btree索引，插入操作的平均tps對比

 類別/平均tps    |  無資料  | 一千萬  | 五千萬 | 一億 		|
---------------+---------+---------+---------+---------+
 無序uuid		  | 960  	| 260     |  163  |   131     |
 有序uuid    	  | 933  	| 870     |  883  |   834     |
 序列        	  | 1289     | 1278    |  1220 |  1139     |

根據測試資料可以看出無序的uuid在資料到達1kw後插入資料的tps下降的非常厲害，而有序的uuid和遞增序列下降的比較少。到一億資料的tps有序uuid是無序的6倍，序列是無序uuid的9倍。

建立單獨的表空間用來儲存索引資訊

如果有多快磁碟那麼可以將索引和資料分開儲存，以此來加快寫入的速度。

建立單獨的索引空間：

create tablespace indx_test owner sa location '/home/tablespace/index_test';

指定索引儲存目錄：

create index i_test_uuid_v4_id on test_uuid_v4 using btree(id) tablespace indx_test;

關於有序uuid

測試使用的sequential-uuids外掛，生成的有序uuid。

有序uuid的結構為(block ID; random data)，實際上就是把資料拆成兩部分，一部分自增，一部分隨機。

sequential-uuids

sequential-uuids-git

提供了兩種演算法：

1.uuid_sequence_nextval(sequence regclass, block_size int default 65536, block_count int default 65536)

字首為自增序列，如果塊ID使用2位元組儲存，一個索引BLOCK裡面可以儲存256條記錄（假設8K的BLOCK，一條記錄包括uuid VALUE（16位元組）以及ctid（6位元組），所以一個索引頁約儲存363條記錄（8000 /（16 + 6）））

2.uuid_time_nextval(interval_length int default 60, interval_count int default 65536) RETURNS uuid

預設每60秒內的資料的字首是一樣的，字首遞增1，到65535後迴圈。

使用uuid_time_nextval生成的有序uuid
pgbenchdb=# select id from test_time_nextval;
                id                
----------------------------------
 a18b7dd0ca92b0b5c1844a402f9c6999
 a18b540b8bbe0ddb2b6d0189b2e393c6
 a18b83eb7320b0a90e625185421e065e
 a18bade4ff15e05dab81ecd3f4c2dee4
 a18b79e41c3bc8d2d4ba4b70447e6b29
 a18bdad18d9e0d2fa1d9d675bc7129f0
 a18b13723ec7be9a2f1a3aec5345a88b
 a18bd9d866047aec69a064d30e9493d2
 a18bd76e8c787c7464479502f381e6d7
 a18ba5c0c966f81cfdbeff866618da8d
......

有序uuid前四位有序，後面的隨機生成。

結語

1.關於有序的uuid，前4位是有序的，後面都是隨機生成的。

2.在該環境中發現，無序uuid隨著資料量的不斷增大，tps下滑比較厲害。

3.由於btree索引的存在，無序的uuid會導致大量的離散io。導致磁碟使用率高。進而影響插入效率。隨著表資料量的增大更加明顯。

4.該測試是在普通的磁碟上面測試，並未在ssd上面測試。

5.如果要使用有序uuid，有多種實現方式，還需要考慮分散式情況下生成全域性有序uuid。

postgresql:pgbench基準效能測試
2020-12-08
SQL
PostgreSQL TPROC-C基準測試：PostgreSQL 12與PostgreSQL 13效能對比
2020-12-08
SQL
PostgreSQL新增UUID功能。
2011-02-22
SQLUI
【LocustPlus序】漫談服務端效能測試
2016-11-02
服務端
無線效能優化：FPS測試
2016-01-28
優化
MongoDB資料庫順序讀效能評估測試
2016-08-11
MongoDB資料庫
【效能測試】使用ab做Http效能測試
2018-01-08
HTTP
效能測試
2024-09-19
效能測試：分散式測試
2017-06-03
分散式
Jmeter介面測試+效能測試
2024-04-16
JMeter
PostgreSQL vs. Oracle 測試
2015-01-05
SQLOracle
無人值守自動化效能測試平臺
2017-07-03
微服務測試之效能測試
2019-04-22
微服務
效能測試之測試指標
2021-10-18
指標
【PG流複製】Postgresql流複製部署過程及效能測試
2019-01-23
SQL
測試開發之效能篇-效能測試設計
2021-11-03
Jmeter效能測試：高併發分散式效能測試
2024-03-07
JMeter分散式
【效能測試策略】系統調優由易到難的順序
2022-05-19
效能測試——效能測試-常見效能指標-總體概況
2024-04-18
指標
Redis 效能測試
2020-08-13
Redis
效能測試流程
2019-01-07
xhprofphp效能測試
2017-11-12
PHP
效能測試工具
2015-04-16
Kafka效能測試
2024-05-06
Kafka
【效能測試】效能測試各知識第1篇：效能測試大綱【附程式碼文件】
2024-03-10
（一）效能測試（壓力測試、負載測試）
2020-12-15
負載
PostgreSQL中如何高效使用UUID主鍵？
2024-04-23
SQLUI
【PG效能測試】pgbench效能測試工具簡單使用
2019-01-22
新潮測試平臺之效能測試
2020-02-12
基於statement複製下NULL、RAND、UUID的測試
2014-02-19
NullUI
效能測試有哪些指標需要測試？
2022-05-06
指標
介面測試和效能測試的區別
2020-11-27
效能測試之測試分析與調優
2021-11-12
效能測試總結(二)---測試流程篇
2016-05-15
效能測試的流程
2020-06-25
手遊前端效能測試
2020-07-28
前端
淺談效能測試
2019-03-02
WebGPU效能測試分析
2021-07-06
WebGPU