对于典型的Rails应用程序,我将从SQLite切换到PostgreSQL。
问题是,运行规格变得缓慢与PG。
在SQLite上,它花费了~34秒,而在PG上,它是~76秒,这是慢2倍以上...
所以现在我想应用一些技巧使规范的性能与SQLite媲美。没有任何代码修改(理想情况下,只需要设置连接选项,这可能是不可能的)。
我头上有几件显而易见的事情是:
正如您可能已经理解的,我不关心可靠性和其余部分(DB只是一个抛弃的东西)。
我需要从控球中得到最大的收获尽可能快...
最佳答案最理想的描述戏法为了做到这一点,设置和这些技巧的缺点。
最新情况:fsync = off
+full_page_writes = off
仅将时间缩短到~65秒(~-16秒)。开局不错,但远未达到34的目标。
更新2:我尝试使用RAM磁盘但性能增益在误差范围内。所以似乎不值得。
更新3:*我发现了最大的瓶颈,现在我的规范运行速度与SQLite一样快。
问题是数据库清理导致了截断显然SQLite在那里太快了。
要“修复”它,我打开一个交易在每次测试之前,最后再把它滚回去。
700次测试的一些数字。
SQLite的速度提高了2倍。4倍的速度增长为PG。
发布于 2017-12-27 09:03:06
首先,始终使用PostgreSQL的最新版本。性能改进总是会出现,所以如果要调优旧版本,可能是在浪费时间。例如,PostgreSQL 9.2显著提高了TRUNCATE
当然还增加了索引扫描。即使是较小的版本也应始终遵循;请参见版本策略...
如果您失去了表空间,整个数据库可能会损坏,如果不进行大量工作,就很难使用。与仅仅使用UNLOGGED
表和有很多RAM的缓存无论如何。
如果你真的想要一个基于ramdisk的系统,initdb
在ramdisk上创建了一个全新的集群initdb
在ramdisk上运行一个新的PostgreSQL实例,这样就有了一个完全一次性的PostgreSQL实例。
在测试时,可以将服务器配置为非耐用但操作更快...
这是唯一可接受的用途之一。fsync=off
设置为PostgreSQL。这个设置很大程度上告诉PostgreSQL不要费心于有序的写操作或任何其他讨厌的数据--完整性保护和崩溃安全之类的东西,允许它在失去电源或出现操作系统崩溃时将数据完全销毁。
不用说,你永远不应该fsync=off
在生产中,除非您使用pg作为临时数据库,否则您可以从其他地方重新生成数据。如果并且只有当你要关闭fsync的时候也可以关闭full_page_writes
离开,因为它不再有任何好处。当心fsync=off
and full_page_writes
在聚类所以他们会影响全PostgreSQL实例中的数据库。
对于生产用途,您可以使用synchronous_commit=off
并设置一个commit_delay
,因为你会得到与fsync=off
没有巨大的数据破坏风险。如果启用异步提交,您确实有一个丢失最近数据的小窗口--但仅此而已。
如果您可以选择稍微更改DDL,也可以使用UNLOGGED
PG 9.1+中的表可以完全避免WAL日志记录,并以服务器崩溃时删除表为代价获得真正的速度提升。没有使所有表未记录的配置选项,必须在CREATE TABLE
除了适合于测试之外,如果数据库中包含大量生成的或不重要的数据,而这些数据包含了您需要的安全内容,这是非常方便的。
检查你的日志,看看你是否收到太多检查点的警告。如果你是,你应该增加你的检查点[医]段段.你也可能想调整你的检查点[医]完井[医]目标平滑地写出来。
调音shared_buffers
来适应你的工作量。这与操作系统有关,取决于您的机器上还发生了什么,并且需要一些尝试和错误。缺省值非常保守。如果增加,则可能需要增加操作系统的最大共享内存限制shared_buffers
在PostgreSQL 9.2及更低版本上,9.3及以上版本更改了它们如何使用共享内存来避免这种情况。
如果您使用的只是几个做大量工作的连接,请增加work_mem
要给他们更多的内存,以供玩,等等。请注意过高的a。work_mem
设置可能会导致内存不足问题,因为它是按排序进行的,而不是每个连接,因此一个查询可以有许多嵌套的排序。只有你真的不得不增加work_mem
如果您可以看到排序溢出到磁盘EXPLAIN
或使用log_temp_files
设置(推荐),但更高的价值也可能让PG选择更明智的计划。
正如另一张海报所言,如果可能的话,将xlog和主要表/索引放在单独的HDD上是明智的。单独的分区是毫无意义的,你真的需要单独的驱动器。如果您正在运行fsync=off
如果你用UNLOGGED
桌子。
最后,调整您的查询。确保你的random_page_cost
and seq_page_cost
反映系统的性能,确保effective_cache_size
正确使用EXPLAIN (BUFFERS, ANALYZE)
若要检查单个查询计划,请将auto_explain
模块继续报告所有慢速查询。通过创建适当的索引或调整成本参数,您通常可以显着地提高查询性能。
AFAIK没有办法将整个数据库或集群设置为UNLOGGED
能这样做会很有趣的。考虑询问PostgreSQL邮件列表。
您也可以在操作系统级别进行一些优化。您可能需要做的主要事情是说服操作系统不要将写到磁盘的内容大量刷新,因为您并不关心它们何时/是否到达磁盘。
在Linux中,您可以使用虚拟内存子系统氏dirty_*
设置,比如dirty_writeback_centisecs
...
调优写回设置过于松弛的唯一问题是,其他程序的刷新可能也会导致所有PostgreSQL的累积缓冲区也被刷新,从而导致在所有写操作被阻塞时出现大阻塞。您可以通过在不同的文件系统上运行PostgreSQL来缓解这种情况,但有些刷新可能是设备级或整个主机级而不是文件系统级,因此不能依赖这种方式。
这个调优实际上需要使用设置来查看什么对您的工作负载最有效。
在更新的内核上,您可能希望确保vm.zone_reclaim_mode
设置为零,因为它可能会导致NUMA系统(目前大多数系统)的严重性能问题,原因是与PostgreSQL管理方式的交互。shared_buffers
...
这些东西确实需要修改代码;它们可能不适合您。有些东西你可能可以申请。
如果没有将工作分批到更大的事务中,请启动。很多小交易都是昂贵的,所以只要有可能和可行,你就应该批量处理。如果您正在使用异步提交,那么这一点就不太重要了,但仍然强烈建议您这样做。
尽可能使用临时表。它们不会生成Wal流量,因此它们用于插入和更新的速度要快得多。有时,需要将一堆数据放入临时表,然后根据需要对其进行操作,然后执行INSERT INTO ... SELECT ...
把它复制到最后一张表。注意,临时表是每个会话的;如果会话结束或失去连接,则临时表就会消失,其他连接也看不到会话临时表的内容。
如果您使用的是PostgreSQL 9.1或更高版本,则可以使用UNLOGGED
表,用于您可以丢失的数据,如会话状态。它们可以在不同的会话中看到,并在连接之间保存。如果服务器不干净地关闭它们,那么它们就会被截断,因此不能用于无法重新创建的任何东西,但是它们很适合缓存、物化视图、状态表等。
一般来说,不要DELETE FROM blah;
.使用TRUNCATE TABLE blah;
相反,当您将所有行转储到一个表中时,速度要快得多。在一个表中截断多个表TRUNCATE
如果可以的话打电话。如果你做了很多TRUNCATES
一遍又一遍地提到小桌子;参见:PostgreSQL截断速度
如果你没有外键索引,DELETE
涉及这些外键引用的主键的速度将非常慢。如果您期望创建这样的索引,请确保DELETE
来自引用的表。不需要索引TRUNCATE
...
不要创建不需要的索引。每个索引都有维护成本。尽量使用一组最少的索引,让位图索引扫描将它们组合起来,而不是维护太多庞大、昂贵的多列索引。在需要索引的地方,尝试先填充表,然后在末尾创建索引。
拥有足够的RAM来保存整个数据库是一个巨大的胜利,如果你能管理它。
如果你没有足够的内存,你就能得到更快的存储效果。即使是廉价的SSD,也会使纺丝锈菌产生巨大的变化。但是,不要相信廉价的SSD生产,它们通常不安全,可能会吃掉您的数据。
格雷格·史密斯的书PostgreSQL 9.0高性能尽管引用了一个稍旧的版本,但仍然具有相关性。这应该是一个有用的参考。
加入PostgreSQL通用邮件列表并遵循它。
发布于 2017-12-27 09:43:45
https://stackoverflow.com/questions/-100005213
复制相似问题