月付网站空间提供商,网站标题改了,做企业网站 需要用服务器吗,安卓app下载现在很多企业都将数据库逐渐由Mysql转向了更加强大而且开源的PostgreSQL数据库。在数据迁移过程中#xff0c;PostgreSQL数据库导入大量数据时候非常缓慢#xff0c;本文我们就来说说PostgreSQL数据库批量导入数据时的优化方法和策略。概述考虑PostgreSQL数据库批量导入数据时…现在很多企业都将数据库逐渐由Mysql转向了更加强大而且开源的PostgreSQL数据库。在数据迁移过程中PostgreSQL数据库导入大量数据时候非常缓慢本文我们就来说说PostgreSQL数据库批量导入数据时的优化方法和策略。概述考虑PostgreSQL数据库批量导入数据时性能缓慢的原因无非有几个因素索引触发器外键GUID主键还有可能是预写日志(WAL)。我们就从这几个影响因素着手优化。当然有可能本文说的这些技巧都不能有效问题遇到这样的问题时候就需要我们具体问题具体分析并针对性的解决。关闭日志记录对于PostgreSQL 9.5及更高版本可以先将目标表更改为UNLOGGED然后在加载数据后将其更改回LOGGEDALTER TABLE SET UNLOGGEDALTER TABLE LOGGEDUNLOGGED模式可以确保PostgreSQL不会在变量导入数据时将表写操作记录到预写日志(WAL)从而极大的优化导入过程。但是由于未记录操作因此如果在加载过程中发生崩溃或服务器关机等故障则无法恢复数据。PostgreSQL重新启动后将自动截断任何未记录的表。另外未记录的表不会复制到备用服务器。在这种情况下必须在加载之前删除现有的复制并在加载之后重新创建。根据主节点中的数据量和备用数据库的数量重建复制的时间可能会很长对于高可用性要求来说这是不可接受的。建议采用以下方法将数据批量插入未记录的表中在将表和数据更改为未记录模式之前对其进行备份数据加载完成后重新创建对备用服务器的任何复制对可以轻松重新填充的表使用UNLOGGED的批量插入(例如大型查找表或维度表)。删除索引数据库索引可能在批量数据插入期间导致严重的延迟。因为添加数据过程对应的索引条目需要实时更新。建议在开始批量插入之前尽可能删除目标表中的索引并在导入完成后重新创建索引。同样在大型表上创建索引可能很耗时但是比在加载过程中更新索引要快。DROP INDEX , … CREATE INDEX ON (column1, …,column n)创建索引之前临时提高maintenance_work_mem配置参数可能会有帮助。增加的工作内存可以帮助更快地创建索引。为了安全起见的另一种选择是使用现有数据和索引在同一数据库中复制目标表。然后测试有索引和删除索两种情况下批量导入数据的性能对比然后根据测试结果选择更好的方法。删除外键和索引一样外键约束也会影响大批量导入的性能。因为导入过程中必须检查插入的每个行数据的每个外键是否存在相应的主键。当批量导入时必须为每一行触发该触发器检查外键从而增加了开销。除非受到业务规则的限制否则建议先从目标表中删除所有外键在单个事务中加载数据然后在提交事务后重新创建外键。ALTER TABLE DROP CONSTRAINT BEGIN TRANSACTIONCOMMITALTER TABLE ADD CONSTRAINT FOREIGN KEY ()REFERENCES ()...同样增加maintenance_work_mem配置参数也能提高重新创建外键约束的性能。暂停触发器INSERT或DELETE触发器(如果导入过程还涉及从目标表中删除记录)可能会导致批量数据导入延迟。这是因为每个触发器将具有需要检查的逻辑并且需要在每行被插入或删除后立即完成操作。建议在批量导入数据之前禁用目标表中的所有触发器并在导入完成后再启用它们。禁用所有触发器也会强制执行外键约束检查的系统触发器。ALTER TABLE DISABLE TRIGGER ALLALTER TABLE ENABLE TRIGGER ALL使用多值INSERT对于成批数据加载运行数千个或数十万个INSERT语句可能是个糟糕的选择。因为查询优化器必须解析和准备每个单独的INSERT命令然后进行所有约束检查作为单独的事务运行并记录日志。而使用多值单个INSERT语句可以节省这些不必要的开支。INSERT INTO (, , …, )VALUES(, , …, ),(, , …, ),(, , …, ),(, , …, ),...多值INSERT性能受现有索引的影响。建议在运行命令之前先删除索引然后再创建索引。另一个需要注意的地方是PostgreSQL可用于运行多值INSERT的内存量。运行多值INSERT时RAM中必须容纳大量输入值并且除非有足够的可用内存否则该过程可能会失败。建议将设置effective_cache_size参数到50%并将shared_buffer设为机器的总内存的参数设为25%。为了安全起见将导入划分为多条的多值INSERT每个语句的值不要超过1000行。使用COPY命令建议使用PostgreSQL COPY命令从一个或多个文件导入数据。COPY针对批量数据导入会进行额外的优化比运行大量INSERT语句甚至多值INSERTS的都要快。COPY [( column1, … , )]FROM WITH (, , … , )使用COPY的还有很多的优势它支持文本和二进制文件导入本质上是事务性的它允许指定输入文件的结构它可以使用WHERE子句有条件地导入数据。运行ANALYZ这与提高批量数据导入性能无关但是强烈建议在批量导入之后立即在目标表上运行ANALYZE命令。大量的新导入的行将大大改变数据表中列中的数据分布并且会使表的统计信息都过时。当用查询优化器使用过时的统计信息时查询性能可能会非常慢。运行ANALYZE命令将确保更新统计信息。总结对于数据库应用程序来说可能并非每天都会进行批量数据导入但是在运行时会对查询性能产生影响。这就是为什么有必要尽可能缩短导入时间。DBA可以最大程度地减少意外的事情之一就是在具有类似服务器规格和PostgreSQL配置的开发或准线上环境中进行性能测试并进行优化。每种数据加载方案都是不同的最好尝试每种方法并找到最好最快的方法。
