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

 --无序的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 ;

 --测试无序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));

 磁盘使用情况 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) |%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 

 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) |%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) |%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

 类别 | 第一次 | 第二次 | 第三次 | 平均值(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 

 类别 | 第一次 | 第二次 | 第三次 | 平均值(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

 --创建普通索引 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,'-',''));

 --删除所有主键 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 tes
                
提示：
                    本文由神整理自网络，如有侵权请联系本站删除！
                    

                    本站声明： 

                    1、本站所有资源均来源于互联网，不保证100%完整、不提供任何技术支持； 

                    2、本站所发布的文章以及附件仅限用于学习和研究目的;不得将用于商业或者非法用途；否则由此产生的法律后果，本站概不负责！
                
                

                

                                            
上一篇：如何使用PostgreSQL进行中文全文检索_PostgreSQL_
                                                                
下一篇：postgres之jsonb属性的使用操作_PostgreSQL_