草庐IT

python - COPY 是如何工作的，为什么它比 INSERT 快得多？

coder 2023-05-27 原文

今天，我花了一整天的时间来改进将数据推送到 Postgres 数据库的 Python 脚本的性能。我以前是这样插入记录的:

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

然后我重新编写了我的脚本，以便它创建一个内存文件，而不是用于 Postgres 的 COPY命令，它允许我将数据从文件复制到我的表:
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使它变得如此之快？

见 this benchmark还有:
# 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 行/秒。即使在本地服务器上，这种延迟也是非零的。关于 COPY只是填充 TCP 发送和接收窗口，并以 DB 可以写入它们并且网络可以传输它们的速度流式传输行。它不受延迟的影响很大，并且可能每秒在同一网络链接上插入数千行。

    PostgreSQL 中的每条语句成本

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

    任务/上下文切换成本

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

    错过了 COPY 优化

    最重要的是，COPY有一些优化可以用于某些类型的负载。例如，如果没有生成的键和任何默认值是常量，它可以预先计算它们并完全绕过执行器，在较低级别将数据快速加载到表中，完全跳过部分 PostgreSQL 的正常工作。如果您 CREATE TABLE或 TRUNCATE您在同一笔交易中 COPY ，它可以通过绕过多客户端数据库中所需的正常事务簿记来做更多的技巧来加快加载速度。

    尽管如此，PostgreSQL 的 COPY仍然可以做更多的事情来加快速度，它还不知道如何做。如果您更改的表超过一定比例，它可以自动跳过索引更新然后重建索引。它可以批量进行索引更新。还有很多。

    promise 成本

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

    关于python - COPY 是如何工作的，为什么它比 INSERT 快得多？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/46715354/

    pythonINSERTcodebrCOPYpostgresqlsql-insertpostgresql-copy

    有关python - COPY 是如何工作的，为什么它比 INSERT 快得多？的更多相关文章

    1. ruby - 如何使用 Nokogiri 的 xpath 和 at_xpath 方法 - 2

      我正在学习如何使用Nokogiri，根据这段代码我遇到了一些问题:require'rubygems'require'mechanize'post_agent=WWW::Mechanize.newpost_page=post_agent.get('http://www.vbulletin.org/forum/showthread.php?t=230708')puts"\nabsolutepathwithtbodygivesnil"putspost_page.parser.xpath('/html/body/div/div/div/div/div/table/tbody/tr/td/div

    2. ruby - 如何从 ruby​​ 中的字符串运行任意对象方法？ - 2

      总的来说，我对ruby​​还比较陌生，我正在为我正在创建的对象编写一些rspec测试用例。许多测试用例都非常基础，我只是想确保正确填充和返回值。我想知道是否有办法使用循环结构来执行此操作。不必为我要测试的每个方法都设置一个assertEquals。例如:describeitem,"TestingtheItem"doit"willhaveanullvaluetostart"doitem=Item.new#HereIcoulddotheitem.name.shouldbe_nil#thenIcoulddoitem.category.shouldbe_nilendend但我想要一些方法来使用

    3. ruby - 为什么我可以在 Ruby 中使用 Object#send 访问私有(private)/ protected 方法？ - 2

      类classAprivatedeffooputs:fooendpublicdefbarputs:barendprivatedefzimputs:zimendprotecteddefdibputs:dibendendA的实例a=A.new测试a.foorescueputs:faila.barrescueputs:faila.zimrescueputs:faila.dibrescueputs:faila.gazrescueputs:fail测试输出failbarfailfailfail.发送测试[:foo,:bar,:zim,:dib,:gaz].each{|m|a.send(m)resc

    4. python - 如何使用 Ruby 或 Python 创建一系列高音调和低音调的蜂鸣声？ - 2

      关闭。这个问题是opinion-based.它目前不接受答案。想要改进这个问题？更新问题，以便editingthispost可以用事实和引用来回答它.关闭4年前。Improvethisquestion我想在固定时间创建一系列低音和高音调的哔哔声。例如:在150毫秒时发出高音调的蜂鸣声在151毫秒时发出低音调的蜂鸣声200毫秒时发出低音调的蜂鸣声250毫秒的高音调蜂鸣声有没有办法在Ruby或Python中做到这一点？我真的不在乎输出编码是什么(.wav、.mp3、.ogg等等)，但我确实想创建一个输出文件。

    5. ruby-on-rails - 由于 "wkhtmltopdf"，PDFKIT 显然无法正常工作 - 2

      我在从html页面生成PDF时遇到问题。我正在使用PDFkit。在安装它的过程中，我注意到我需要wkhtmltopdf。所以我也安装了它。我做了PDFkit的文档所说的一切......现在我在尝试加载PDF时遇到了这个错误。这里是错误:commandfailed:"/usr/local/bin/wkhtmltopdf""--margin-right""0.75in""--page-size""Letter""--margin-top""0.75in""--margin-bottom""0.75in""--encoding""UTF-8""--margin-left""0.75in""-

    6. ruby-on-rails - 如何验证 update_all 是否实际在 Rails 中更新 - 2

      给定这段代码defcreate@upgrades=User.update_all(["role=?","upgraded"],:id=>params[:upgrade])redirect_toadmin_upgrades_path,:notice=>"Successfullyupgradeduser."end我如何在该操作中实际验证它们是否已保存或未重定向到适当的页面和消息？ 最佳答案 在Rails3中，update_all不返回任何有意义的信息，除了已更新的记录数(这可能取决于您的DBMS是否返回该信息)。http://ar.ru

    7. ruby-on-rails - Rails - 子类化模型的设计模式是什么？ - 2

      我有一个模型:classItem项目有一个属性“商店”基于存储的值，我希望Item对象对特定方法具有不同的行为。Rails中是否有针对此的通用设计模式？如果方法中没有大的if-else语句，这是如何干净利落地完成的？ 最佳答案 通常通过Single-TableInheritance. 关于ruby-on-rails-Rails-子类化模型的设计模式是什么？，我们在StackOverflow上找到一个类似的问题： https://stackoverflow.co

    8. ruby-on-rails - 'compass watch' 是如何工作的/它是如何与 rails 一起使用的 - 2

      我在我的项目目录中完成了compasscreate.和compassinitrails。几个问题:我已将我的.sass文件放在public/stylesheets中。这是放置它们的正确位置吗？当我运行compasswatch时，它不会自动编译这些.sass文件。我必须手动指定文件:compasswatchpublic/stylesheets/myfile.sass等。如何让它自动运行？文件ie.css、print.css和screen.css已放在stylesheets/compiled。如何在编译后不让它们重新出现的情况下删除它们？我自己编译的.sass文件编译成compiled/t

    9. ruby - 如何将脚本文件的末尾读取为数据文件(Perl 或任何其他语言) - 2

      我正在寻找执行以下操作的正确语法(在Perl、Shell或Ruby中):#variabletoaccessthedatalinesappendedasafileEND_OF_SCRIPT_MARKERrawdatastartshereanditcontinues. 最佳答案 Perl用__DATA__做这个:#!/usr/bin/perlusestrict;usewarnings;while(){print;}__DATA__Texttoprintgoeshere 关于ruby-如何将脚

    10. ruby - 什么是填充的 Base64 编码字符串以及如何在 ruby​​ 中生成它们？ - 2

      我正在使用的第三方API的文档状态:"[O]urAPIonlyacceptspaddedBase64encodedstrings."什么是“填充的Base64编码字符串”以及如何在Ruby中生成它们。下面的代码是我第一次尝试创建转换为Base64的JSON格式数据。xa=Base64.encode64(a.to_json) 最佳答案 他们说的padding其实就是Base64本身的一部分。它是末尾的“=”和“==”。Base64将3个字节的数据包编码为4个编码字符。所以如果你的输入数据有长度n和n%3=1=>"=="末尾用于填充n%

    随机推荐