PG copy&insert性能对比

最新推荐文章于 2024-04-15 11:01:55 发布
最新推荐文章于 2024-04-15 11:01:55 发布
阅读量2.9k 收藏 3
点赞数
分类专栏： PG学习笔记 文章标签： postgresql insert copy 性能 测试
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/jacicson1987/article/details/83383761
版权

目录

 

测试环境

表结构

CASE1

结果

CASE 2

结果

CASE 3

结果

TPS修正

CASE 4

结果

结论

测试环境

Intel(R) Core(TM) i7-4790 CPU @ 3.60GHz

32G memory

SSD harddisk.

postgresql.conf

 
max_connections = 256
 
shared_buffers = 3GB                    # min 128kB
 
work_mem = 16MB                         # min 64kB
maintenance_work_mem = 256MB            # min 1MB
 
wal_buffers = 4MB 
checkpoint_timeout = 10min              # range 30s-1d
max_wal_size = 2GB
 
checkpoint_completion_target = 0.9

表结构

zj_testdb=# \d+ t1
                                    Table "public.t1"
 Column |  Type  | Collation | Nullable | Default | Storage | Stats target | Description 
--------+--------+-----------+----------+---------+---------+--------------+-------------
 c1     | bigint |           |          |         | plain   |              | 
 c2     | bigint |           |          |         | plain   |              | 
 c3     | bigint |           |          |         | plain   |              | 
 c4     | bigint |           |          |         | plain   |              | 
 c5     | bigint |           |          |         | plain   |              | 
 c6     | bigint |           |          |         | plain   |              | 
 c7     | bigint |           |          |         | plain   |              | 
 c8     | bigint |           |          |         | plain   |              | 
 c9     | bigint |           |          |         | plain   |              | 
 c10    | bigint |           |          |         | plain   |              | 
 c11    | bigint |           |          |         | plain   |              | 
 c12    | bigint |           |          |         | plain   |              | 
 c13    | bigint |           |          |         | plain   |              | 
 c14    | bigint |           |          |         | plain   |              | 
 c15    | bigint |           |          |         | plain   |              | 
 c16    | bigint |           |          |         | plain   |              | 
 c17    | bigint |           |          |         | plain   |              | 
 c18    | bigint |           |          |         | plain   |              | 
 c19    | bigint |           |          |         | plain   |              | 
 c20    | bigint |           |          |         | plain   |              | 
 c21    | bigint |           |          |         | plain   |              | 
 c22    | bigint |           |          |         | plain   |              | 
 c23    | bigint |           |          |         | plain   |              | 
 c24    | bigint |           |          |         | plain   |              | 
 c25    | bigint |           |          |         | plain   |              | 
 c26    | bigint |           |          |         | plain   |              | 
 c27    | bigint |           |          |         | plain   |              | 
 c28    | bigint |           |          |         | plain   |              | 
 c29    | bigint |           |          |         | plain   |              | 
 c30    | bigint |           |          |         | plain   |              | 
 c31    | bigint |           |          |         | plain   |              | 
 c32    | bigint |           |          |         | plain   |              | 
 c33    | bigint |           |          |         | plain   |              | 
 c34    | bigint |           |          |         | plain   |              | 
 c35    | bigint |           |          |         | plain   |              | 
 c36    | bigint |           |          |         | plain   |              | 
 c37    | bigint |           |          |         | plain   |              | 
 c38    | bigint |           |          |         | plain   |              | 
 c39    | bigint |           |          |         | plain   |              | 
 c40    | bigint |           |          |         | plain   |              | 
 c41    | bigint |           |          |         | plain   |              | 
 c42    | bigint |           |          |         | plain   |              | 
 c43    | bigint |           |          |         | plain   |              | 
 c44    | bigint |           |          |         | plain   |              | 
 c45    | bigint |           |          |         | plain   |              | 
 c46    | bigint |           |          |         | plain   |              | 
 c47    | bigint |           |          |         | plain   |              | 
 c48    | bigint |           |          |         | plain   |              | 
 c49    | bigint |           |          |         | plain   |              | 
 c50    | bigint |           |          |         | plain   |              | 
 c51    | bigint |           |          |         | plain   |              | 
 c52    | bigint |           |          |         | plain   |              | 
 c53    | bigint |           |          |         | plain   |              | 
 c54    | bigint |           |          |         | plain   |              | 
 c55    | bigint |           |          |         | plain   |              | 
 c56    | bigint |           |          |         | plain   |              | 
 c57    | bigint |           |          |         | plain   |              | 
 c58    | bigint |           |          |         | plain   |              | 
 c59    | bigint |           |          |         | plain   |              | 
 c60    | bigint |           |          |         | plain   |              | 
 c61    | bigint |           |          |         | plain   |              | 
 c62    | bigint |           |          |         | plain   |              | 
 c63    | bigint |           |          |         | plain   |              | 
 c64    | bigint |           |          |         | plain   |              | 
 c65    | bigint |           |          |         | plain   |              | 
 c66    | bigint |           |          |         | plain   |              | 
 c67    | bigint |           |          |         | plain   |              | 
 c68    | bigint |           |          |         | plain   |              | 
 c69    | bigint |           |          |         | plain   |              | 
 c70    | bigint |           |          |         | plain   |              | 
 c71    | bigint |           |          |         | plain   |              | 
 c72    | bigint |           |          |         | plain   |              | 
 c73    | bigint |           |          |         | plain   |              | 
 c74    | bigint |           |          |         | plain   |              | 
 c75    | bigint |           |          |         | plain   |              | 
 c76    | bigint |           |          |         | plain   |              | 
 c77    | bigint |           |          |         | plain   |              | 
 c78    | bigint |           |          |         | plain   |              | 
 c79    | bigint |           |          |         | plain   |              | 
 c80    | bigint |           |          |         | plain   |              | 
 c81    | bigint |           |          |         | plain   |              | 
 c82    | bigint |           |          |         | plain   |              | 
 c83    | bigint |           |          |         | plain   |              | 
 c84    | bigint |           |          |         | plain   |              | 
 c85    | bigint |           |          |         | plain   |              | 
 c86    | bigint |           |          |         | plain   |              | 
 c87    | bigint |           |          |         | plain   |              | 
 c88    | bigint |           |          |         | plain   |              | 
 c89    | bigint |           |          |         | plain   |              | 
 c90    | bigint |           |          |         | plain   |              | 
 c91    | bigint |           |          |         | plain   |              | 
 c92    | bigint |           |          |         | plain   |              | 
 c93    | bigint |           |          |         | plain   |              | 
 c94    | bigint |           |          |         | plain   |              | 
 c95    | bigint |           |          |         | plain   |              | 
 c96    | bigint |           |          |         | plain   |              | 
 c97    | bigint |           |          |         | plain   |              | 
 c98    | bigint |           |          |         | plain   |              | 
 c99    | bigint |           |          |         | plain   |              | 
 c100   | bigint |           |          |         | plain   |              | 
Tablespace: "zj_tablespace"

CASE1

batchsize=100, 8 threads

test_insert.sql

[root@server4 zjcktest]# cat test_insert.sql 
insert into t1 select generate_series(1,100),1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1

copy csv

copy (select generate_series(1,100),1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1) to '/data/zjcktest/csv';

copy.sql

[root@server4 zjcktest]# cat copy.sql 
copy t1 from '/data/zjcktest/csv' ;

pgbench命令执行insert和copy

INSERT:
pgbench -M prepared -n -r -P 1 -f /data/zjcktest/test_insert.sql -h 127.0.0.1 -p 5432 -U postgres zj_testdb -c 8 -j 8 -T 120

COPY:
pgbench -M prepared -n -r -P 1 -f /data/zjcktest/copy.sql -h 127.0.0.1 -p 5432 -U postgres zj_testdb -c 8 -j 8 -T 120

结果

两者速率相差不大，copy略快5%

8 threads batchsize=100行数事务数TPS 
batch 100 insert select120221001202211000.835
batch 100 copy127711001277111057.615
    
batch 100 insert select129455001294551077.917
batch 100 copy135522001355221120.751
    
batch 100 insert select131649001316491092.143
batch 100 copy132996001329961107.717

 

CASE 2

1 thread, batchsize=100

修改pgbench执行命令

INSERT:
pgbench -M prepared -n -r -P 1 -f /data/zjcktest/test_insert.sql -h 127.0.0.1 -p 5432 -U postgres zj_testdb -c 1 -j 1 -T 120

COPY:
pgbench -M prepared -n -r -P 1 -f /data/zjcktest/copy.sql -h 127.0.0.1 -p 5432 -U postgres zj_testdb -c 1 -j 1 -T 120

结果

比8线程慢25%。 copy和insert很接近，insert比copy略好3%。

1 thread, batchsize=100行数事务数TPS 
batch 100 insert select900310090031750.254
batch 100 copy890370089037741.454
    
batch 100 insert select897130089713747.577
batch 100 copy879870087987733.224
    
batch 100 insert select883990088399736.622
batch 100 copy877630087763731.356
    
batch 100 insert select904290090429753.572
batch 100 copy882740088274735.607

 

CASE 3

1 thread, batchsize=1000

修改test_insert.sql和生成新的copy文件

copy (select generate_series(1,1000),1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1) to '/data/zjcktest/csv';  


[root@server4 zjcktest]# cat test_insert.sql 
insert into t1 select generate_series(1,1000),1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1

结果

仍旧insert稍快 4%。 

1 thread, batchsize=1000行数事务数TPS 
batch 100 insert select111790001117993
batch 100 copy107940001079489.948

 

TPS修正

考虑到copy需要读取文件，按照金士顿 SSD 读取500MB/s来计算

总读取字节读取速率cost time修正后TPS
21792330425242880004.16 s93.18 

去掉读文件的时间，总的TPS和insert几乎一致。

 

CASE 4

1 thread, batchsize = 1

修改test_insert.sql 和 重新生成copy文件csv

cat test_insert.sql 
insert into t1 values (5,1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1)

zj_testdb=# copy (select 5,1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1) to '/data/zjcktest/csv';

结果

insert要比copy快 15%. 

由于单行频繁打开关闭文件句柄导致copy性能下降。

1 thread, batchsize=1行数事务数TPS 
insert 8356328356326963.591
copy6969416969415807.831

 

结论

在test_insert.sql中，使用generate_series 而不是显示一行一行insert语句的情况下，insert 整体性能和copy基本一致，单线程情况下insert性能更好。

对比test_insert.sql里有100行insert语句的测试，这里少了绝大部分的SQL语句的解析，性能与copy一样甚至超过。

 

 

小湿哥
关注
  • 0
    点赞
  • 踩
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
insert、to_sql、copy_from 3种写入PG数据库代码效率对比
书生的日常
10-22 3790
昨天写入PG的时候直接用insert命令拼接字符串被数据库的同事批了，o(╥﹏╥)o 这里我测试了3种写入PG数据库的方式，数据源为1个含有13642条数据的csv，并进行了一个对比： 1.insert命令写入 具体的命令代码见我的这篇文章：https://blog.csdn.net/weixin_44731100/article/details/88534304 可见，写入13642...
pg copy命令导数
lt89102476的博客
12-14 1439
[size=small]copy 在vim里面,用%s/|//g把所有的|符号替换掉，以，隔开字段 [root@localhost tmp]# cat a.txt SanFrancisco,41,55,0,1994-11-29 xinzhou,32,32,0.11,2014-08-26 meiguo,25,65,0.58,2015-06-09 califonia,25,65,0.58...
使用 COPY 加速 PostgreSQL 批量插入
weixin_43230594的博客
11-22 1049
Speed up PostgreSQL bulk inserts with COPY
PostgreSQL数据库如何快速插入大量数据_postgresql 大批量插入(1)
最新发布
2301_77121488的博客
04-15 1679
在一些应用场景中，需要向PostgreSQL数据库中快速装入大量的数据，比如数据库迁移，SQL日志分析等。在Abase上快速插入数据有几种方案，每种方案的效率怎么样？如何调优能加快的数据装载？SQL日志分析是一个采集JDBC日志、分析SQL、发送分析结果工具。在分析阶段，需要解析大量的JDBC日志，并把解析后的结构化结果装入数据库供后续处理。(img-iECZ6T9C-1713150098109)]–无主键，没有用到C_BH 查询，增加insert速度先去掉。，来测试不同方案的数据装入效率。
insert与copy的插入比较
theArcticOcean
01-20 1365
举例代码： 将一段数据复制到vector的首部 int data[5] = {0,1,2,3,4}; vectorint> vInt; vInt.push_back(10); vInt.insert(vInt.begin(),data,data+5); vInt.clear(); vInt.push_back(10); copy(data, data+5, inserter(vInt,v
pg的copy说明
ArvinZhangjia的博客
01-07 1193
copy 可以在表和文件之间交换数据 COPY table_name [ ( column_name [, ...] ) ] FROM { 'filename' | PROGRAM 'command' | STDIN } [ [ WITH ] ( option [, ...] ) ] COPY { table_name [ ( column_name [, ...] ...
PG copy注意事项
岳麓丹枫
12-28 706
结论：copy 形式的sql 字段值与字段值之间应该使用 tab 间隔，而不是空格间隔 实例 [root@localhost map3d8500_Uniview]# /home/postgres/pgsql/bin/psql -Upostgres psql (11.1) Type "help" for help. postgres=# \d lxm.lxm ...
Postgre数据库Insert 、Query性能优化详解
09-10
在测试中，无论使用哪种插入方式（INSERT、COPY或COPY+UNLOGGED），查询性能基本保持一致，平均耗时500毫秒，并且随着记录数的增加，查询效率并未下降，这表明良好的索引设计对查询性能至关重要。 总的来说，...
clj-pgcopy:从Clojure将数据快速导入Postgres
05-22
clj-pgcopy 使用二进制格式的PostgreSQLCOPY实现，将数据快速导入到PostgreSQL。 因为有时jdbc/insert! 和朋友不够快。 该库使用基于类型的分派来确定正确的postgres二进制格式。 有关更多信息，请参见部分。 ...
PostgreSQL 数据库性能提升的几个方面
09-11
5. **使用COPY命令批量插入数据**：COPY命令比单个INSERT语句更快，因为它减少了与服务器的交互次数。在需要向表中插入大量数据时，COPY是更高效的选择。 6. **临时删除索引**：在大规模数据导入或备份恢复期间，...
PgBulkInsert：Java库，可使用二进制COPY协议高效地批量插入PostgreSQL
02-05
PgBulkInsert是一个Java库，用于使用Binary COPY协议批量插入PostgreSQL。 它提供了PostgreSQL 的包装器： 是PostgreSQL特有的功能，它允许高效地批量导入表或从表导出数据。 与使用INSERT和SELECT相比，这是将...
第八节 pg_dump.docx
09-26
4. **特殊输出模式**：`--column-inserts`和`--inserts`选项使数据以INSERT命令而非COPY命令的形式导出，确保数据在导入时不会错位。前者还允许指定列列表，避免因字段顺序改变导致的问题。 5. **恢复过程**：使用...
PG数据库COPY命令详解
Tiano__的博客
01-10 1938
在 PostgreSQL 中，COPY 命令是一个用于导入或导出数据的快速、高效的工具。它可以将数据从数据文件中读取到表中，或将表中的数据写出到数据文件中。文件中读取数据，并将数据插入到名为。表中读取数据，并将数据写出到。
php使用pg中copy命令,postgresql中COPY的用法
weixin_36051633的博客
03-13 1850
一.测试创建表：[postgres@cacti ~]$ cat test.sqlCREATE TABLE weather ( city varchar(80), temp_lo int,temp_hi int, prcp real,date date);二.导入test.sql文件生成表：testdb01=> \i test.sql***(Single step mode: verify c...
oracle数据库 append,oracle中insert，copy，insert append执行对比
weixin_36098968的博客
04-06 222
oracle中insert，copy，insert append执行对比还是由于迁移数据库，测试了insert，copy，insert append的执行速度。环境：oracle9i向oracle11g迁移，linux系统。表已建立，只允许插入数据，而不能使用create .. select 或 copy .. create。1. 建立dblink，供insert，insert append使用。...
PG COPY导入导出数据
岳麓丹枫
12-05 1210
导出 实例1 /home/postgres/pgsql/bin/psql -U postgres -c "copy(select datname from pg_database where datname not in ('postgres','template0','template1','zabbix') order by datname) to '/home/postgres/pgsql/...
php使用pg中copy命令,PGSQL COPY命令导入/导出数据
weixin_39719078的博客
03-13 1916
PGSQL中可使用COPY命令来导入/导出数据，这里以CSV文件为例。导出数据：SQL Shell执行：COPY table_name (col1,col2,col3) TO 'path_name.csv' CSV HEADER;如果表格使用了分表，需使用select获取数据：COPY (select col1,col2,col3 from table_name) TO 'path_name.cs...
copy优化
HighGO
01-22 618
对于大数据量的copy导入, 性能优化的方法可以考虑以下几个方面. 1. auto analyze 调整(尽量减少或者导入阶段关闭后续再打开) 2. table blocksize(如果业务形态是以大批量导入数据为主, 可以调整到最大) 3. unlogged table, (使用unlogged table可以减少wal flush带来的负担, 注意删除fork init文件, 避免db cras
pgadmin 导出insert
07-28
您可以使用pgAdmin中的导出功能来导出INSERT语句。请按照以下步骤操作： 1. 打开pgAdmin并连接到您的数据库。 2. 在对象资源树中，展开您的数据库以显示表和其他对象。 3. 选择要导出的表。 4. 在工具栏上，点击“导出”按钮（一个向下箭头的图标）或右键单击表并选择“导出”选项。 5. 在导出对话框中，选择导出类型为“SQL”。 6. 选择要导出的目标文件路径和名称。 7. 在“选项”选项卡中，确保选择了“插入数据”选项。 8. 点击“导出”按钮以开始导出过程。 完成上述步骤后，pgAdmin将生成一个包含INSERT语句的SQL文件，其中包含选定表的数据。您可以使用这个文件来在另一个数据库中重新插入数据。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值