我正在测试Postgres插入性能。我有一个表,其中有一列,其数据类型为数字。还有一个索引。我用这个查询填充了数据库:
insert into aNumber (id) values (564),(43536),(34560) ...
我非常快地插入了400万行,使用上面的查询一次插入10,000行。在数据库达到600万行之后,性能急剧下降到每15分钟100万行。有什么技巧可以提高插入性能吗?我需要在这个项目上的最佳插入性能。
在内存为5gb的机器上使用Windows 7 Pro。
我正在测试Postgres插入性能。我有一个表,其中有一列,其数据类型为数字。还有一个索引。我用这个查询填充了数据库:
insert into aNumber (id) values (564),(43536),(34560) ...
我非常快地插入了400万行,使用上面的查询一次插入10,000行。在数据库达到600万行之后,性能急剧下降到每15分钟100万行。有什么技巧可以提高插入性能吗?我需要在这个项目上的最佳插入性能。
在内存为5gb的机器上使用Windows 7 Pro。
当前回答
我也遇到了这个插入性能问题。我的解决方案是衍生一些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()
对于我的项目,加载速度要快得多。这段代码片段只是给出了它的工作原理。读者应该能够轻松地修改它。
其他回答
我也遇到了这个插入性能问题。我的解决方案是衍生一些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()
对于我的项目,加载速度要快得多。这段代码片段只是给出了它的工作原理。读者应该能够轻松地修改它。
为了获得最佳的插入性能,如果可以选择禁用索引。除此之外,更好的硬件(磁盘、内存)也很有帮助
如果你碰巧插入带有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分钟。
我今天花了大约6个小时在同一个问题上。插入以“常规”速度(每100K小于3秒)进行,直到5MI(总共30MI)行,然后性能急剧下降(一直下降到每100K 1分钟)。
我不会列出所有不起作用的事情,直接切入正题。
我在目标表上放置了一个主键(这是一个GUID),我的30MI或行愉快地以每100K不到3秒的恒定速度流到目的地。
除了Craig Ringer的文章和depesz的博客文章外,如果您想通过在事务中使用预处理语句插入来加快通过ODBC (psqlodbc)接口的插入速度,还需要做一些额外的事情来使其快速工作:
Set the level-of-rollback-on-errors to "Transaction" by specifying Protocol=-1 in the connection string. By default psqlodbc uses "Statement" level, which creates a SAVEPOINT for each statement rather than an entire transaction, making inserts slower. Use server-side prepared statements by specifying UseServerSidePrepare=1 in the connection string. Without this option the client sends the entire insert statement along with each row being inserted. Disable auto-commit on each statement using SQLSetConnectAttr(conn, SQL_ATTR_AUTOCOMMIT, reinterpret_cast<SQLPOINTER>(SQL_AUTOCOMMIT_OFF), 0); Once all rows have been inserted, commit the transaction using SQLEndTran(SQL_HANDLE_DBC, conn, SQL_COMMIT);. There is no need to explicitly open a transaction.
不幸的是,psqlodbc通过发出一系列未准备好的插入语句来“实现”SQLBulkOperations,因此为了实现最快的插入,需要手动编写上述步骤。