如何在PostgreSQL中加速插入性能

我正在测试Postgres插入性能。我有一个表，其中有一列，其数据类型为数字。还有一个索引。我用这个查询填充了数据库:

insert into aNumber (id) values (564),(43536),(34560) ...

我非常快地插入了400万行，使用上面的查询一次插入10,000行。在数据库达到600万行之后，性能急剧下降到每15分钟100万行。有什么技巧可以提高插入性能吗?我需要在这个项目上的最佳插入性能。

在内存为5gb的机器上使用Windows 7 Pro。

268546 次浏览
小开

为了获得最佳的插入性能，如果可以选择禁用索引。除此之外，更好的硬件(磁盘、内存)也很有帮助

小开
最佳答案

参见PostgreSQL手册中的填充数据库，该主题的Depesz一如既往的优秀文章和这个SO问题。

(请注意，这个答案是关于批量加载数据到现有的DB或创建一个新的DB。如果你对pg_restore或__ABC1执行__ABC2输出的DB恢复性能感兴趣，其中大部分不适用，因为__ABC2和pg_restore已经在完成模式+数据恢复后创建触发器和索引之类的事情)。

有很多事情要做。理想的解决方案是导入到一个没有索引的UNLOGGED表中，然后将其更改为logged并添加索引。不幸的是，在PostgreSQL 9.4中不支持将表从UNLOGGED更改为logged。9.5添加ALTER TABLE ... SET LOGGED来允许你这样做。

如果可以让数据库脱机进行批量导入，请使用pg_bulkload。

否则:

  • 禁用表上的任何触发器

  • 导入前删除索引，导入后重新创建索引。(多在一次传递中构建索引所花费的时间比逐步向其添加相同的数据所花费的时间更少，并且所得到的索引更紧凑)。

  • 如果在单个事务中执行导入操作，则删除外键约束、执行导入并在提交前重新创建约束是安全的。如果导入被分割到多个事务，则不要这样做，因为可能会引入无效数据。

  • 如果可能，使用COPY代替INSERTs

  • 如果你不能使用COPY，考虑使用多值的INSERTs。你好像已经开始这么做了。不要试图在一个VALUES中列出太多个值;这些值必须多次适合内存，所以每条语句保持几百个。

  • 将您的插入批处理到显式事务中，每个事务执行数十万或数百万个插入。AFAIK没有实际的限制，但是批处理可以让您通过在输入数据中标记每个批处理的开始来从错误中恢复。再说一遍，你似乎已经在这么做了。

  • 使用synchronous_commit=off和一个巨大的commit_delay来减少fsync()的开销。但是，如果您将工作批处理到大的事务中，这不会有太大帮助。

  • INSERT或COPY从多个连接并行。有多少取决于硬件的磁盘子系统;根据经验，如果使用直接连接的存储，则每个物理硬盘驱动器需要一个连接。

  • 设置一个较高的max_wal_size值(旧版本中的checkpoint_segments)并启用log_checkpoints。查看PostgreSQL日志，确保它没有抱怨检查点出现得太频繁。

  • 当且仅当你不介意在导入过程中系统崩溃导致整个PostgreSQL集群(你的数据库和同一集群上的任何其他数据库)发生灾难性损坏时，你可以停止Pg，设置fsync=off，启动Pg，进行导入，然后(至关重要的)停止Pg并再次设置fsync=on。看到细胞膜的配置。如果你设置了fsync=off，你也可以设置full_page_writes=off;同样，只要记得在导入后将其重新打开，以防止数据库损坏和数据丢失。参见Pg手册中的非持久的设置。

你还应该考虑优化你的系统:

  • 尽可能使用质量好 ssd盘进行存储。具有可靠的、电源保护的回写缓存的优秀ssd使提交速度快得令人难以置信。当你遵循上面的建议(减少磁盘刷新/ __abc0的数量)时，它们就不那么有益了，但仍然可以提供很大的帮助。不要使用没有适当的掉电保护的廉价ssd，除非你不在乎保存你的数据。

  • 如果您正在使用RAID 5或RAID 6作为直接连接的存储，请立即停止。备份您的数据，将RAID组重构为RAID 10，然后重试。RAID 5/6在大容量写性能方面是无望的——尽管具有大缓存的良好RAID控制器会有所帮助。

  • 如果你可以选择使用硬件RAID控制器和一个大的电池支持的回写缓存，这可以真正提高具有大量提交的工作负载的写性能。如果你正在使用带有commit_delay的异步提交，或者你在批量加载期间处理较少的大事务，那么它就没有多大帮助。

  • 如果可能，将WAL (pg_wal，或旧版本中的pg_xlog)存储在单独的磁盘/磁盘阵列上。在同一个磁盘上使用单独的文件系统没有什么意义。人们经常选择将RAID1对用于WAL。同样，这对具有高提交率的系统影响更大，如果使用未记录日志的表作为数据加载目标，则影响很小。

你可能也对优化PostgreSQL进行快速测试感兴趣。

小开

使用COPY table TO ... WITH BINARY根据文档"比文本和CSV格式略快."只有当您有数百万行要插入，并且您对二进制数据感到满意时才这样做。

这是一个Python中的示例食谱，使用psycopg2和二进制输入。

小开

除了Craig Ringer的文章和depesz的博客文章外，如果你想通过在事务中使用预处理语句插入来加快通过ODBC (psqlodbc)接口的插入速度，你还需要做一些额外的事情来让它快速工作:

  1. 通过在连接字符串中指定Protocol=-1，将错误回滚级别设置为“Transaction”。默认情况下，psqlodbc使用“语句”级别，这将为每个语句而不是整个事务创建一个SAVEPOINT，从而使插入变慢。
  2. 通过在连接字符串中指定UseServerSidePrepare=1来使用服务器端准备语句。如果没有这个选项，客户端会将整个插入语句连同被插入的每一行一起发送。
  3. 在每条语句上使用SQLSetConnectAttr(conn, SQL_ATTR_AUTOCOMMIT, reinterpret_cast<SQLPOINTER>(SQL_AUTOCOMMIT_OFF), 0);禁用自动提交
  4. 插入所有行之后，使用SQLEndTran(SQL_HANDLE_DBC, conn, SQL_COMMIT);提交事务。不需要显式地打开事务。

不幸的是，psqlodbc通过发出一系列未准备好的插入语句来“实现”SQLBulkOperations，因此为了实现最快的插入，需要手动编写上述步骤。

小开

我也遇到了这个插入性能问题。我的解决方案是衍生一些go例程来完成插入工作。同时，SetMaxOpenConns应该被赋予一个合适的数字，否则会有太多的打开连接错误被警告。

db, _ := sql.open()
db.SetMaxOpenConns(SOME CONFIG INTEGER NUMBER)
var wg sync.WaitGroup
for _, query := range queries {
wg.Add(1)
go func(msg string) {
defer wg.Done()
_, err := db.Exec(msg)
if err != nil {
fmt.Println(err)
}
}(query)
}
wg.Wait()

对于我的项目，加载速度要快得多。这段代码片段只是给出了它的工作原理。读者应该能够轻松地修改它。

小开

我今天花了大约6个小时在同一个问题上。插入以“常规”速度(每100K小于3秒)进行，直到5MI(总共30MI)行，然后性能急剧下降(一直下降到每100K 1分钟)。

我不会列出所有不起作用的事情，直接切入正题。

我在目标表(这是一个GUID)上丢失主键，我的30MI或行愉快地以每100K不到3秒的恒定速度流到目的地。

小开

如果你碰巧插入带有uuid的列(这不是完全你的情况)并添加到@Dennis 回答(我还不能评论)，建议使用gen_random_uuid()(需要PG 9.4和pgcrypto模块)比uuid_generate_v4()快(很多)

=# explain analyze select uuid_generate_v4(),* from generate_series(1,10000);
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Function Scan on generate_series  (cost=0.00..12.50 rows=1000 width=4) (actual time=11.674..10304.959 rows=10000 loops=1)
Planning time: 0.157 ms
Execution time: 13353.098 ms
(3 filas)

vs


=# explain analyze select gen_random_uuid(),* from generate_series(1,10000);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Function Scan on generate_series  (cost=0.00..12.50 rows=1000 width=4) (actual time=252.274..418.137 rows=10000 loops=1)
Planning time: 0.064 ms
Execution time: 503.818 ms
(3 filas)

而且，这是建议的官方方式

请注意

如果您只需要随机生成的uuid(版本4)，可以考虑使用pgcrypto模块中的gen_random_uuid()函数。

这将3.7M行的插入时间从大约2小时降低到大约10分钟。


提交答案