我们有一个相当具体的应用程序,它使用Python8.3作为存储后端(使用PostgreSQL和psycopg2)。在大多数情况下,我们对重要的表执行的操作是插入或更新(很少是删除或选择)。
出于理智的原因,我们已经创建了我们自己的Data Mapper-like层,该层工作得相当好,但它有一个很大的瓶颈,即更新性能。当然,我并不期望更新/替换方案能像“插入到空表”的方案那样快,但如果能更近一点就更好了。
请注意,此系统没有并发更新
我们总是在更新时设置每一行的所有字段,这可以从我在测试中使用单词“替换”的术语中看出。到目前为止,我已经尝试了两种方法来解决更新问题:
replace()
过程:CREATE OR REPLACE函数replace_item(data item[])返回VOID作为$$ BEGIN FOR I IN COALESCE(array_lower(data,1),0) ..COALESCE(array_upper(data,1),-1) LOOP UPDATE item SET a0=datai.a0,a1=datai.a1,a2=datai.a2 WHERE key=datai.key;END LOOP;END;$$ LANGUAGE plpgsql
insert_or_replace
规则,以便除偶尔删除之外的所有内容都成为多行插入创建规则"insert_or_replace“AS ON INSERT TO " item”WHERE EXISTS(SELECT 1 FROM item WHERE key=NEW.key)代替(更新项目集a0=NEW.a0,a1=NEW.a1,a2=NEW.a2 WHERE key=NEW.key);
这两种方法都会大大加快更新速度,尽管后者会稍微减慢插入速度:
Multi-row insert : 50000 items inserted in 1.32 seconds averaging 37807.84 items/s
executemany() update : 50000 items updated in 26.67 seconds averaging 1874.57 items/s
update_andres : 50000 items updated in 3.84 seconds averaging 13028.51 items/s
update_merlin83 (i/d/i) : 50000 items updated in 1.29 seconds averaging 38780.46 items/s
update_merlin83 (i/u) : 50000 items updated in 1.24 seconds averaging 40313.28 items/s
replace_item() procedure : 50000 items replaced in 3.10 seconds averaging 16151.42 items/s
Multi-row insert_or_replace: 50000 items inserted in 2.73 seconds averaging 18296.30 items/s
Multi-row insert_or_replace: 50000 items replaced in 2.02 seconds averaging 24729.94 items/s
关于测试运行的随机注释:
更新所有测试都运行在数据库所在的同一台计算机上;连接到localhost.
CREATE TABLE item ( KEY MACADDR PRIMARY key、a0 VARCHAR、a1 VARCHAR、a2 VARCHAR )
因此,真正的问题是:如何才能将更新/替换操作的速度提高一点?(我认为这些调查结果可能“足够好”,但我不想在没有利用SO人群的情况下放弃:)
另外,如果有人建议使用更优雅的replace_item(),或者有证据表明我的测试完全失败了,我们将非常欢迎。
如果您想尝试重现,可以在here上找到测试脚本。记得先检查一下,though...it WorksForMe,但是...
您需要编辑db.connect()行以适合您的设置。
编辑
多亏了#postgresql @ freenode中的andres,我有了另一个单查询更新的测试;非常类似于多行插入(如上面的update_andres所示)。
UPDATE item
SET a0=i.a0, a1=i.a1, a2=i.a2
FROM (VALUES ('00:00:00:00:00:01', 'v0', 'v1', 'v2'),
('00:00:00:00:00:02', 'v3', 'v4', 'v5'),
...
) AS i(key, a0, a1, a2)
WHERE item.key=i.key::macaddr
编辑
多亏了下面的#postgresql @ freenode和jug/jwp中的merlin83,我有了另一个使用插入到临时/删除/插入方法的测试(上面列出的是"update_merlin83 (i/d/i)“)。
INSERT INTO temp_item (key, a0, a1, a2)
VALUES (
('00:00:00:00:00:01', 'v0', 'v1', 'v2'),
('00:00:00:00:00:02', 'v3', 'v4', 'v5'),
...);
DELETE FROM item
USING temp_item
WHERE item.key=temp_item.key;
INSERT INTO item (key, a0, a1, a2)
SELECT key, a0, a1, a2
FROM temp_item;
我的直觉是,这些测试对现实世界场景中的性能并不是很有代表性,但我认为差异足够大,可以为进一步研究提供最有希望的方法。perftest.py脚本还包含所有更新,供那些想要签出它的人使用。它相当难看,所以不要忘了你的护目镜:)
编辑
andres在#postgresql @ freenode中指出,我应该使用插入到临时/更新的变体(上面列出的是"update_merlin83 (i/u)“)进行测试。
INSERT INTO temp_item (key, a0, a1, a2)
VALUES (
('00:00:00:00:00:01', 'v0', 'v1', 'v2'),
('00:00:00:00:00:02', 'v3', 'v4', 'v5'),
...);
UPDATE item
SET a0=temp_item.a0, a1=temp_item.a1, a2=temp_item.a2
FROM temp_item
WHERE item.key=temp_item.key
编辑
可能是最后的编辑:我修改了我的脚本,以更好地匹配我们的加载场景,似乎即使在将事情放大一点并添加一些随机性时,这些数字仍然有效。如果有人从其他场景中得到了非常不同的数字,我很有兴趣了解它。
https://stackoverflow.com/questions/962361
复制相似问题