• 售前

  • 售后

热门帖子
入门百科

postgresql无序uuid性能测试及对数据库的影响

[复制链接]
123456790 显示全部楼层 发表于 2021-8-14 15:10:22 |阅读模式 打印 上一主题 下一主题
无序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.创建表
  1. --无序的uuid
  2. pgbenchdb=# create table test_uuid_v4(id char(32) primary key);
  3. CREATE TABLE
  4. --有序的uuid
  5. pgbenchdb=# create table test_time_nextval(id char(32) primary key);
  6. CREATE TABLE
  7. --递增序列
  8. pgbenchdb=# create table test_seq_bigint(id int8 primary key);
  9. CREATE TABLE
  10. --创建序列
  11. create sequence test_seq start with 1 ;
复制代码
2.测试脚本
  1. --测试无序uuid脚本
  2. vi pgbench_uuid_v4.sql
  3. insert into test_uuid_v4 (id) values (replace(uuid_generate_v4()::text,'-',''));
  4. --测试有序uuid脚本
  5. vi pgbench_time_nextval.sql
  6. insert into test_time_nextval (id) values (replace(uuid_time_nextval()::text,'-',''));
  7. --测试序列脚本
  8. vi pgbench_seq_bigint.sql
  9. insert into test_seq_bigint (id) values (nextval('test_seq'::regclass));
复制代码
无序uuid,无数据环境
  1. 磁盘使用情况
  2. avg-cpu:  %user   %nice %system %iowait  %steal   %idle
  3.            0.76    0.00    0.38    4.67    0.00   94.19
  4. Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
  5. 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
  6. 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
  7. 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
  8. 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
  9. 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
  10. tps:
  11. [thunisoft@localhost thunisoft]$ pgbench -M prepared -r -n -j 8 -c 8 -T 60 -f /opt/thunisoft/pgbench_uuid_v4.sql -U sa pgbenchdb
  12. transaction type: /opt/thunisoft/pgbench_uuid_v4.sql
  13. scaling factor: 1
  14. query mode: prepared
  15. number of clients: 8
  16. number of threads: 8
  17. duration: 60 s
  18. number of transactions actually processed: 53494
  19. latency average = 8.974 ms
  20. tps = 891.495404 (including connections establishing)
  21. tps = 891.588967 (excluding connections establishing)
  22. script statistics:
  23. - statement latencies in milliseconds:
  24.          9.006  insert into test_uuid_v4 (id) values (replace(uuid_generate_v4()::text,'-',''));
复制代码
无数据环境下,tps
  1. 类别     |  第一次  | 第二次  | 第三次 | 平均值(tps) |%util |await
  2. ---------------+---------+---------+---------+---------+-------+-------
  3. 无序uuid                  | 919          | 907     |  891  |   906     | 99.2% | 10.66   
  4. 有序uuid              | 985          | 882     |  932  |   933     | 98.7% | 4.4
  5. 序列                  | 1311     | 1277    |  1280 |  1289     | 97.5% | 3.4
复制代码
向表内里初始化100w数据
  1. pgbenchdb=# insert into test_uuid_v4 (id) select  replace(uuid_generate_v4()::text,'-','') from generate_series(1,1000000);
  2. INSERT 0 1000000
  3. Time: 43389.817 ms (00:43.390)
  4. pgbenchdb=# insert into test_time_nextval (id) select replace(uuid_time_nextval()::text,'-','') from generate_series(1,1000000);
  5. INSERT 0 1000000
  6. Time: 30585.134 ms (00:30.585)
  7. pgbenchdb=#  insert into test_seq_bigint select generate_series (1,1000000);
  8. INSERT 0 1000000
  9. Time: 9818.639 ms (00:09.819)
  10. 无序uuid插入100w需要43s,有序需要30s,序列需要10s。
复制代码
插入一百万数据后的tps
  1. 类别     |  第一次  | 第二次  | 第三次 | 平均值(tps) |%util |await
  2. ---------------+---------+---------+---------+---------+-------+-------
  3. 无序uuid                  | 355          | 440     |  302  |   365     | 98.8% | 13   
  4. 有序uuid              | 948          | 964     |  870  |   927     | 97.2% | 4.0
  5. 序列                  | 1159     | 1234    |  1115 |  1169     | 96.6% | 3.5
复制代码
插入一千万数据后的tps
  1. 类别     |  第一次  | 第二次  | 第三次 | 平均值(tps) |%util |await
  2. ---------------+---------+---------+---------+---------+-------+-------
  3. 无序uuid                  | 260          | 292     |  227  |   260     | 99.2% | 16.8   
  4. 有序uuid              | 817          | 960     |  883  |   870     | 97.7% | 3.9
  5. 序列                  | 1305     | 1261    |  1270 |  1278     | 96.8% | 3.0
复制代码
插入五千万数据后
  1. 向表中插入5kw数据,并且添加主键
  2. pgbenchdb=# insert into test_time_nextval (id) select replace(uuid_time_nextval()::text,'-','') from generate_series(1,50000000);
  3. INSERT 0 50000000
  4. Time: 453985.318 ms (07:33.985)
  5. pgbenchdb=# insert into test_seq_bigint select generate_series (1,50000000);
  6. INSERT 0 50000000
  7. Time: 352206.160 ms (05:52.206)
  8. pgbenchdb=# insert into test_uuid_v4 (id) select  replace(uuid_generate_v4()::text,'-','') from generate_series(1,50000000);
  9. INSERT 0 50000000
  10. Time: 1159689.338 ms (00:19:19.689)
  11. 在无主键情况下,插入五千万数据,有序uuid耗时7分钟,序列耗时6分钟,而无序uuid耗时接近20分钟。
  12. pgbenchdb=# alter table test_uuid_v4 add primary key ("id");
  13. ALTER TABLE
  14. Time: 845199.296 ms (14:05.199)
  15. pgbenchdb=# alter table test_time_nextval add primary key ("id");
  16. ALTER TABLE
  17. Time: 932151.103 ms (15:32.151)
  18. pgbenchdb=# alter table test_seq_bigint add primary key ("id");
  19. ALTER TABLE
  20. Time: 148138.871 ms (02:28.139)
  21. pgbenchdb=# select pg_size_pretty(pg_total_relation_size('test_uuid_v4'));
  22. pg_size_pretty
  23. ----------------
  24. 6072 MB
  25. (1 row)
  26. Time: 0.861 ms
  27. pgbenchdb=#  select pg_size_pretty(pg_total_relation_size('test_time_nextval'));
  28. pg_size_pretty
  29. ----------------
  30. 6072 MB
  31. (1 row)
  32. Time: 0.942 ms
  33. pgbenchdb=#  select pg_size_pretty(pg_total_relation_size('test_seq_bigint'));
  34. pg_size_pretty
  35. ----------------
  36. 2800 MB
  37. (1 row)
  38. Time: 0.699 ms
复制代码
插入5kw后
  1. 类别     |  第一次  | 第二次  | 第三次 | 平均值(tps) |%util |await
  2. ---------------+---------+---------+---------+---------+-------+-------
  3. 无序uuid                  | 162          | 163     |  163  |   163     | 99.6% | 18.4   
  4. 有序uuid              | 738          | 933     |  979  |   883     | 97.7% | 3.9
  5. 序列                  | 1132     | 1264    |  1265 |  1220     | 96.8% | 3.5
复制代码
插入1亿条数据后
  1.   类别     |  第一次  | 第二次  | 第三次 | 平均值(tps) |%util |await
  2. ---------------+---------+---------+---------+---------+-------+-------
  3. 无序uuid                  | 121          | 131     |  143  |   131     | 99.6% | 28.2   
  4. 有序uuid              | 819          | 795     |  888  |   834     | 99.2% | 28.7
  5. 序列                  | 1193     | 1115    |  1109 |  1139     | 96.8% | 11.3
复制代码
普通btree索引

上面测了无序uuid,1kw环境下,有主键的tps是260,无主键的tps是1234。尝试测试普通的索引,和唯一索引tps
  1. --创建普通索引
  2. pgbenchdb=# create index i_test_uuid_v4_id on test_uuid_v4(id);
  3. CREATE INDEX
  4. Time: 316367.010 ms (05:16.367)
  5. --创建普通索引后
  6. [thunisoft@localhost thunisoft]$ pgbench -M prepared -r -n -j 8 -c 8 -T 60 -f /opt/thunisoft/pgbench_uuid_v4.sql -U sa pgbenchdb
  7. transaction type: /opt/thunisoft/pgbench_uuid_v4.sql
  8. scaling factor: 1
  9. query mode: prepared
  10. number of clients: 8
  11. number of threads: 8
  12. duration: 60 s
  13. number of transactions actually processed: 13308
  14. latency average = 36.080 ms
  15. tps = 221.727391 (including connections establishing)
  16. tps = 221.749660 (excluding connections establishing)
  17. script statistics:
  18. - statement latencies in milliseconds:
  19.         38.512  insert into test_uuid_v4 (id) values (replace(uuid_generate_v4()::text,'-',''));
  20. --创建唯一索引
  21. pgbenchdb=# drop index i_test_uuid_v4_id;
  22. DROP INDEX
  23. Time: 267.451 ms
  24. pgbenchdb=# create unique index i_test_uuid_v4_id on test_uuid_v4(id);
  25. CREATE INDEX
  26. Time: 153372.622 ms (02:33.373)
  27. [thunisoft@localhost thunisoft]$ pgbench -M prepared -r -n -j 8 -c 8 -T 60 -f /opt/thunisoft/pgbench_uuid_v4.sql -U sa pgbenchdb
  28. ^[[3~transaction type: /opt/thunisoft/pgbench_uuid_v4.sql
  29. scaling factor: 1
  30. query mode: prepared
  31. number of clients: 8
  32. number of threads: 8
  33. duration: 60 s
  34. number of transactions actually processed: 13847
  35. latency average = 34.693 ms
  36. tps = 230.593988 (including connections establishing)
  37. tps = 230.620469 (excluding connections establishing)
  38. script statistics:
  39. - statement latencies in milliseconds:
  40.         36.410  insert into test_uuid_v4 (id) values (replace(uuid_generate_v4()::text,'-',''));
复制代码
无论是普通btree索引和唯一索引,都会影响插入的效率。
删除全部的主键索引
  1. --删除所有主键
  2. alter table test_uuid_v4 drop constraint "test_uuid_v4_pkey";
  3. alter table test_time_nextval drop constraint "test_time_nextval_pkey" ;
  4. alter table test_seq_bigint drop constraint "test_seq_bigint_pkey";
  5. 1,--无序uuid:测试pgbench_uuid_v4.sql
  6. [thunisoft@localhost thunisoft]$ pgbench -M prepared -r -n -j 8 -c 8 -T 60 -f /opt/thunisoft/pgbench_uuid_v4.sql -U sa pgbenchdb
  7. transaction type: /opt/thunisoft/pgbench_uuid_v4.sql
  8. scaling factor: 1
  9. query mode: prepared
  10. number of clients: 8
  11. number of threads: 8
  12. duration: 60 s
  13. number of transactions actually processed: 74109
  14. latency average = 6.479 ms
  15. tps = 1234.842229 (including connections establishing)
  16. tps = 1235.042674 (excluding connections establishing)
  17. script statistics:
  18. - statement latencies in milliseconds:
  19.          6.112  insert into test_uuid_v4 (id) values (replace(uuid_generate_v4()::text,'-',''));
  20. 2、--有序uuid,测试pgbench_time_nextval.sql
  21. [thunisoft@localhost thunisoft]$ pgbench -M prepared -r -n -j 8 -c 8 -T 60 -f /opt/thunisoft/pgbench_time_nextval.sql -U sa pgbenchdb
  22. transaction type: /opt/thunisoft/pgbench_time_nextval.sql
  23. scaling factor: 1
  24. query mode: prepared
  25. number of clients: 8
  26. number of threads: 8
  27. duration: 60 s
  28. number of transactions actually processed: 74027
  29. latency average = 6.486 ms
  30. tps = 1233.364360 (including connections establishing)
  31. tps = 1233.482292 (excluding connections establishing)
  32. script statistics:
  33. - statement latencies in milliseconds:
  34.          6.186  insert into test_time_nextval (id) values (replace(uuid_time_nextval()::text,'-',''));
  35. 3、--序列,测试pgbench_seq_bigint.sql
  36. [thunisoft@localhost thunisoft]$ pgbench -M prepared -r -n -j 8 -c 8 -T 60 -f /opt/thunisoft/pgbench_seq_bigint.sql -U sa pgbenchdb
  37. transaction type: /opt/thunisoft/pgbench_seq_bigint.sql
  38. scaling factor: 1
  39. query mode: prepared
  40. number of clients: 8
  41. number of threads: 8
  42. duration: 60 s
  43. number of transactions actually processed: 76312
  44. latency average = 6.290 ms
  45. tps = 1271.832907 (including connections establishing)
  46. tps = 1272.124397 (excluding connections establishing)
  47. script statistics:
  48. - statement latencies in milliseconds:
  49.          5.916  insert into test_seq_bigint (id) values (nextval('test_seq'::regclass));
复制代码
删除主键约束后,三种环境下tps非常接近,都达到了1200+。
Btree索引,插入操作的均匀tps对比
  1. 类别/平均tps    |  无数据  | 一千万  | 五千万 | 一亿                 |
  2. ---------------+---------+---------+---------+---------+
  3. 无序uuid                  | 960          | 260     |  163  |   131     |
  4. 有序uuid              | 933          | 870     |  883  |   834     |
  5. 序列                  | 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后循环。
  1. 使用uuid_time_nextval生成的有序uuid
  2. pgbenchdb=# select id from test_time_nextval;
  3.                 id               
  4. ----------------------------------
  5. a18b7dd0ca92b0b5c1844a402f9c6999
  6. a18b540b8bbe0ddb2b6d0189b2e393c6
  7. a18b83eb7320b0a90e625185421e065e
  8. a18bade4ff15e05dab81ecd3f4c2dee4
  9. a18b79e41c3bc8d2d4ba4b70447e6b29
  10. a18bdad18d9e0d2fa1d9d675bc7129f0
  11. a18b13723ec7be9a2f1a3aec5345a88b
  12. a18bd9d866047aec69a064d30e9493d2
  13. a18bd76e8c787c7464479502f381e6d7
  14. a18ba5c0c966f81cfdbeff866618da8d
  15. ......
复制代码
有序uuid前四位有序,背面的随机天生。
结语

1.关于有序的uuid,前4位是有序的,背面都是随机天生的。
2.在该环境中发现,无序uuid随着数据量的不停增大,tps下滑比较锋利。
3.由于btree索引的存在,无序的uuid会导致大量的离散io。导致磁盘使用率高。进而影响插入效率。随着表数据量的增大更加明显。
4.该测试是在普通的磁盘上面测试,并未在ssd上面测试。
5.假如要使用有序uuid,有多种实现方式,还必要思量分布式环境下生玉成局有序uuid。
以上就是postgresql无序uuid性能测试的详细内容,更多关于postgresql无序uuid性能测试的资料请关注草根技能分享其它相关文章!

帖子地址: 

回复

使用道具 举报

分享
推广
火星云矿 | 预约S19Pro,享500抵1000!
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

草根技术分享(草根吧)是全球知名中文IT技术交流平台,创建于2021年,包含原创博客、精品问答、职业培训、技术社区、资源下载等产品服务,提供原创、优质、完整内容的专业IT技术开发社区。
  • 官方手机版

  • 微信公众号

  • 商务合作