延迟很重要
典型的客户端/服务器往返的网络延迟,交换机网络范围从 0.01 毫秒(本机)到 ~0.5 毫秒、WiFi 网络 5 毫秒、ADSL 网络 20 毫秒、洲际路由网络 300 毫秒,甚至更高,例如卫星和 WWAN 链接。
一个普通的SELECT可能需要 0.1 毫秒左右,才能在服务器端执行。一个普通的INSERT可能需要 0.5 毫秒。
每次应用程序运行查询时,它都必须等待服务器响应成功/失败,可能还有结果集、查询元数据等。这会导致至少一次网络往返延迟。
当您在处理小型的简单查询时,如果您的数据库与应用程序不在同一主机上,则相对于查询的执行时间,网络延迟可能会很长。
许多应用程序,尤其是 ORM,很容易运行许多非常简单的查询。例如,如果你的 Hibernate 应用程序正在获取一个实体,它会延迟获取@OneToMany关系关联到的 1000 个子项,那么由于这样 n+1 次 select 的问题,如果不是更多的话,它可能会执行 1001 次查询。这意味着它可能要花费 1000 倍于您的网络往返延迟来等待。你可以用left join fetch来避免这种情况,但是,您在连接中传输父实体 1000 次,并且必须对其进行重复数据删除。
同样,如果你从 ORM 填充数据库,你可能正在执行数十万个普通INSERT,并在每一个请求之后等待服务器确认它没问题。
尝试关注查询执行时间并尝试优化它很容易,但是对于普通的INSERT INTO ...VALUES ...,您可以做的并不多。删除一些索引和约束,确保它在一个事务中批处理执行,然后你就差不多完成了。
那摆脱所有网络等待怎么样?即使在局域网上,它们也开始累积数千个查询。
避免延迟的一种方法是使用COPY。要使用 PostgreSQL 的 COPY 特性,您的应用程序或驱动程序必须生成一组类似 CSV 的行,并将它们以连续的顺序流式传输到服务器。或者,可以要求服务器向您的应用程序发送类似 CSV 的流。
无论哪种方式,应用程序都不能将 COPY 与其他查询交错,并且必须将 copy-insert 直接加载到目标表中。一种常见的方法是先COPY进入一个临时表,然后从那里执行INSERT INTO ... SELECT ...,UPDATE ... FROM ....,DELETE FROM ... USING...等操作,以使用复制的数据在单个操作中修改主表。
如果您直接编写自己的 SQL,这很方便,但许多应用程序框架和 ORM 不支持它,而且它只能直接替换简单的INSERT。您的应用程序、框架或客户端驱动程序必须针对COPY所需的特殊表示做好转换,查找任何其自身所需的类型元数据等。
(支持COPY的有名驱动程序包括 libpq、PgJDBC、psycopg2 和 Pg gem,但不一定是构建在它们之上的框架和 ORM。)
PL/pgSQL 函数
PL/pgSQL 函数是一组预先创建并存储在数据库服务器上的 SQL 语句,它具有一个指定的名称。频繁或复杂的操作可通过提前编写 SQL 语句来存储。当将来需要数据库提供相同的服务时,只需再次执行存储的函数即可。
复杂的业务逻辑需要多个 SQL 语句。当客户端和服务器之间有很多操作时,会产生大量的网络传输。如果将这些操作放在存储函数中,客户端和服务器之间的网络传输将减少,从而减少网络负载。
PgJDBC – 批处理模式
PostgreSQL 的 JDBC 驱动程序为此问题提供了解决方案。它依赖于 PostgreSQL 服务器自 8.4 以来的支持,以及JDBC API 的批处理功能,将一批查询发送到服务器,然后只等待一次以确认整个批处理运行正常。
这只是理论上的。实际上,一些实现的挑战限制了这一点,因此批处理最多只能以几百个查询的块完成。如果驱动程序可以提前计算出结果的大小,则驱动程序也只能运行以批处理块形式返回结果行的查询。尽管存在这些限制,但是对于执行批量数据加载到远程数据库实例等任务的应用程序,使用Statement.executeBatch()可以为其提供巨大的性能提升。
因为它是标准 API,所以它可以被跨多个数据库引擎工作的应用程序使用。例如,Hibernate 可以使用JDBC 批处理,尽管默认情况下它不这样做。
遗憾的是,无法自动为现有应用程序启用批处理。应用程序必须使用略有不同的接口,它们先发送一系列查询,然后才询问结果。
性能
像 RDS Postgres 这样的托管数据库服务,会是一个很好的例子,来说明这种功能是非常有用的。特别是,从我们自己的网络访问他们,确实显示了延迟带来的损害有多大。
在 ~320 毫秒网络延迟时:
•500 个 INSERT,无批处理:167 秒
•500 个带批处理的 INSERT:1.2 秒
… 这快了 120 倍以上。
您通常不会跨应用服务器和数据库之间的洲际网络,来运行您的应用,但这有助于突出延迟的影响。即使在本机的 unix 套接字上,也能看到 10000 次插入的性能提高了 50% 以上。
COPY 速度最快
实际客户仍然应该偏向COPY。以下是个人工作电脑上的一些结果:
inserting 1000000 rows batched, unbatched and with COPYbatch insert elapsed:23.715315ssequential insert elapsed: 36.150162sCOPY elapsed:1.743593sDone.
批处理工作即使在本地 unix 套接字连接上,也能提供令人惊讶的巨大性能提升。但COPY将两个单独的插入方法都远远抛在了后面。
尽量使用COPY吧。
本网站的文章部分内容可能来源于网络和网友发布,仅供大家学习与参考,如有侵权,请联系站长进行删除处理,不代表本网站立场,转载者并注明出处:https://www.jmbhsh.com/wanjumoxing/36367.html