当前位置: 首页 > 面试题库 >

COPY如何工作,为什么它比INSERT这么快?

党祖鹤
2023-03-14
问题内容

今天,我整天都在改善Python脚本的性能,该脚本将数据推送到Postgres数据库中。我以前是这样插入记录的:

query = "INSERT INTO my_table (a,b,c ... ) VALUES (%s, %s, %s ...)";
for d in data:
    cursor.execute(query, d)

然后,我重新编写了脚本,以使它创建的内存文件比PostgresCOPY命令所使用的内存文件大,这使我可以将数据从文件复制到表中:

f = StringIO(my_tsv_string)
cursor.copy_expert("COPY my_table FROM STDIN WITH CSV DELIMITER AS E'\t' ENCODING 'utf-8' QUOTE E'\b' NULL ''", f)

COPY方法 惊人地更快

METHOD      | TIME (secs)   | # RECORDS
=======================================
COPY_FROM   | 92.998    | 48339
INSERT      | 1011.931  | 48377

但是我找不到关于为什么的任何信息?它与多行的工作方式有何不同INSERT,从而使其变得更快?

也请参阅此基准测试:

# original
0.008857011795043945: query_builder_insert
0.0029380321502685547: copy_from_insert

#  10 records
0.00867605209350586: query_builder_insert
0.003248929977416992: copy_from_insert

# 10k records
0.041108131408691406: query_builder_insert
0.010066032409667969: copy_from_insert

# 1M records
3.464181900024414: query_builder_insert
0.47070908546447754: copy_from_insert

# 10M records
38.96936798095703: query_builder_insert
5.955034017562866: copy_from_insert

问题答案:

这里有许多因素在起作用:

  • 网络延迟和往返延迟
  • PostgreSQL中按语句的开销
  • 上下文切换和调度程序延迟
  • COMMIT 成本,如果对于每个插入一次执行一次提交的人(不是)
  • COPY批量加载的特定优化

网络延迟

如果服务器是远程服务器,则您可能正在“支付”每个语句的固定时间“价格”,例如50毫秒(1/20秒)。对于某些云托管数据库,甚至更多。由于下一个插入操作要等到最后一个操作成功完成后才能开始,所以这意味着您的
最大 插入速率是每秒1000 /来回延迟毫秒数行。延迟为50毫秒(“
ping时间”),即20行/秒。即使在本地服务器上,此延迟也不为零。WherasCOPY只是填充TCP发送和接收窗口,并且以DB可以写入它们并网络可以传输它们的速度来传输流。它不受延迟的影响很大,并且可能每秒在同一网络链接上插入数千行。

PostgreSQL中的每个陈述式成本

在PostgreSQL中解析,计划和执行语句也有成本。它必须要加锁,打开关系文件,查找索引等。COPY在开始时尝试一次完成所有这些操作,然后只专注于尽可能快地加载行。

任务/上下文切换成本

由于操作系统必须在您的应用程序准备和发送时在等待行的postgres之间切换,然后在postgres处理该行的同时等待postgres的响应的应用程序之间切换,因此还要支付更多的时间成本。每次从一个切换到另一个时,都会浪费一点时间。当进程进入和离开等待状态时,可能会浪费更多时间来挂起和恢复各种低级内核状态。

错过了COPY优化

最重要的是,COPY有一些优化可以用于某些负载。例如,如果没有生成的键并且任何默认值都是常量,它可以预先计算它们并完全绕过执行程序,从而将数据快速加载到较低级别的表中,从而完全跳过了PostgreSQL的部分正常工作。如果您CREATE TABLETRUNCATE与您处于同一事务中COPY,则可以绕过多客户端数据库中所需的常规事务簿记,从而做更多的技巧来加快加载速度。

尽管如此,PostgreSQLCOPY仍然可以做更多的事情来加快速度,这是它尚不知道如何做的事情。如果您要更改表的一定比例,它可能会自动跳过索引更新,然后重建索引。它可以批量进行索引更新。还有更多。

提交费用

最后要考虑的一件事是提交成本。对于您来说,这可能不是问题,因为psycopg2默认情况下会打开一个事务,直到您告知后才提交。除非您告诉它使用自动提交。但是对于许多数据库驱动程序,自动提交是默认设置。在这种情况下,您将每次执行一次提交INSERT。这意味着一个磁盘刷新,服务器确保将内存中的所有数据写出到磁盘上,并告诉磁盘将自己的缓存写出到持久性存储中。这可能需要很
长时间 时间,并且因硬件而异。我的基于SSD的NVMe BTRFS笔记本电脑只能执行200 fsyncs
/秒,而不是每秒300,000次非同步写入。因此它只会加载200行/秒!某些服务器只能执行50 fsync
/秒。有些可以做到20,000。因此,如果必须定期提交,则尝试分批加载和提交,进行多行插入等。由于最后COPY只提交一次,因此提交成本可以忽略不计。但这也意味着COPY无法从数据中途的错误中恢复;它会取消整个批量加载。



 类似资料:
  • 为了减少内核或跨进程内存泄漏(Spectre攻击),Linux内核1将使用一个新选项编译,引入到,以便通过所谓的retpoline执行间接调用。 这似乎是一个新发明的术语,因为谷歌搜索只是最近才使用(通常都是在2018年)。 1但是,它不是Linux特有的--类似或相同的构造似乎被用作其他操作系统缓解策略的一部分。

  • 我创建了一个简单的GUI,其中包括一个JTable。这张桌子可能被保存了 此时,保存函数按预期工作,当在保存目录中查找时,我可以看到表对象存储在文件中。 但是,当我尝试从文件加载表时,GUI从不显示加载的表。调用actionlistener函数,因为我有一个系统。输出“数据已加载”,但表格从不显示更新的数据。 我已尝试调用repaint(),但无效。如果有人能让我知道我可能做错了什么,我将不胜感激

  • 问题内容: 我使用INNER JOIN进行查询,结果为1200万行。我喜欢把它放在桌子上。我做了一些测试,当我使用AS SELECT子句创建表时,比先创建表并在运行SELECT之后运行INSERT更快。我不明白为什么。有人可以为我解释吗?ks 问题答案: 如果您使用“选择创建表”(CTAS) 您会自动对数据进行 直接路径插入 。如果你做一个 您执行 常规插入操作 。如果要执行直接路径插入,则必须使

  • 问题内容: 从2010年的计算机语言基准游戏中可以看出: Go平均比C慢10倍 Go比Java慢3倍! 考虑到Go编译器会生成要执行的本机代码,这怎么可能? Go的编译器不成熟?还是Go语言存在一些内在问题? 编辑: 大多数答案否认Go语言的内在缓慢,声称问题出在不成熟的编译器中。 因此,我进行了一些自己的测试来计算斐波那契数:迭代算法在Go(freebsd,6g)中以与C(带有O3选项)一样的速

  • 这是我的线程: 它得到一个上限和下限以及一个在所有线程上共享的结果。 这是我的测试类: