PostgreSQL 硬件性能调整 已翻译 100%

oschina 投递于 2014/11/27 07:12 (共 18 段, 翻译完成于 11-29)
阅读 6418
收藏 105
5
加载中

POSTGRESQL 是一个对象关系型数据库,由来自全球一组网络开发者开发。它是一个可代替如Oracle、Informix商业数据库的开源版本。

POSTGRESQL 最初由加州大学伯克利分校开发。1996年,一个小组开始在互联网上开发该数据库。他们使用email分享想法,用文件服务器分享代码。POSTGRESQL现在在功能方面、性能方面以及可靠性上可与商业数据库比肩。它支持事务、视图、存储过程和参考完整性约束。它也支持大量的编程接口,包括ODBC、Java(JDBC)、TCL/TK、PHP、Perl以及Python。得益于互联网开发者人才库,POSTGRESQL 还有广阔的增长空间。

开源中国七里香
开源中国七里香
翻译于 2014/11/27 09:06
1

性能概念

数据库性能优化有两个方面。一方面是提高数据库对电脑CPU,内存和硬盘的使用。另一方面是最优化传递到数据库的查询。这篇文章讨论的是在硬件方面优化数据库性能。通过使用例如:CREATE INDEX,VACUUM,VACUUM FULL,ANALYZE,CLUSTER和EXPLAIN这些数据库SQL命令,插叙查询的最优化已经完成了。这些在我写的《PostgreSQL:Introduction and Concepts》(http://momjian.us/main/writings/pgsql/aw_pgsql_book/)这本书中已经讨论过了。

为了理解硬件性能的问题,就必须理解在电脑的内部发生了什么。简单的说,一台电脑可以被视为一个被存储器包围的中央处理单元(CPU)。在和CPU同一小片上的是不同的寄存器,它们保存了中间运算结果和各种指针以及计数器。包围这些的是CPU cache,其中有最新的访问信息。越过CPU cache是大量的随机存取存储器(RAM),它保存了正在运行的程序以及数据。在RAM的外围就是硬盘了,它保存了更加多的信息。硬盘是唯一可以永久存储信息的区域。,所以电脑关机后,所有被保存下来的信息都在这里。归纳起来,这些是包围CPU的存储区域:

\includegraphics[height=0.25\textheight]{caches}

存储区域 容量

CPU寄存器    几字节

CPU高速缓存 几千字节

RAM           几兆字节

硬盘           几千兆字节

你可以看到储存大小随着离CPU距离的增加而增加。理论上,大容量的永久存储可以被安置在CPU的旁边,但是这将变的很慢而且很昂贵。实际当中,最常用的信息被放在CPU的旁边,而不怎么用的信息就放得离CPU远远的。在CPU需要的时候再拿给CPU。

MagicBLS
MagicBLS
翻译于 2014/11/27 13:02
1

缩短数据与 CPU 的距离

数据在各种存储区域的转移是自动执行的。编译器决定哪些数据存在寄存器里头。CPU 决定哪些数据存在缓存里面。 操作系统负责内存和硬盘之间的数据交换。 

数据库管理员对 CPU 的寄存器和缓存无能为力。要提高数据库的性能,只能通过增加内存中的有用数据量, 从而减少磁盘访问来获得。

看似简单, 其实不然, 内存中的数据包含很多东西: 

  • 正在执行中的程序

  • 程序的数据和堆栈

  • POSTGRESQL 共享缓存

  • 内核磁盘缓存

  • 内核

理想的性能调整, 既要增加内存中的数据库数据占有量,又不能对系统造成负面影响。 

BreakingBad
BreakingBad
翻译于 2014/11/27 09:32
1

POSTGRESQL 共享缓存

\includegraphics[height=0.35\textheight]{buffer_stack}

POSTGRESQL 没有直接访问磁盘,而是访问 POSTGRESQL 的缓存。然后再由 POSTGRESQL 的后台程序读写这些数据块, 最后写到磁盘上。 

后台首先在表中,查找缓存是否已经存在这些数据。 有, 就继续处理。没有, 则由操作系统从内核磁盘缓存, 或者直接从磁盘加载这些数据。无论哪一种,代价都很高。 

POSTGRESQL 默认分配 1000 个缓存。每个缓存有 8k 字节。增加缓存的数量,能增加后台访问缓存的频率,减少代价较高的系统请求。缓存的数量,可以通过 postmaster 命令行的参数, 或者配置文件 postgresql.conf 中的 shared_buffers 的值来设置。 

BreakingBad
BreakingBad
翻译于 2014/11/27 10:08
1

多大才算太大?

\includegraphics[width=0.8\paperwidth]{memory}

你可能在想, “那我把所有的内存都分配给 POSTGRESQL 的缓冲区好了”。 如果你这么做, 那系统内核以及其他程序就没有内存可用了。理想的 POSTGRESQL 共享缓冲区大小,是在没有对系统产生不利影响的情况下, 越大越好。 

要理解什么是不利影响,首先要明白 UNIX 是如何管理内存的。要是内存容量足够大,能容下所有的程序和数据。 那我们也就用不着管理内存了。问题是, 内存的容量有限,所以, 需要内核将内存中的数据分页, 存入磁盘,这就是传说的的数据交换。原理是, 将当前用不上的数据移到磁盘中。这个操作叫做交换区页面移入(swap pageout)。页面移入交换区不难,只要在程序非活跃期执行就可以。问题在于, 页面重新从交换区移出来的时候。 也就是, 移到交换区的旧页面, 又重新移回内存。这个操叫交换区移出( swap pagein)。说它是个问题, 是因为, 当页面移入内存的时候, 程序需要终止执行, 直到移入操作完成。 

系统的页面移入活跃情况, 可以通过像 vmstatand sar 这种系统分析工具来查看, 是否有足够的内存, 维持系统的正常运作。不要把交换区页面移出,跟常规的页面移出搞混了。常规的页面移出, 将页面数据从文件系统中读出来,当作是系统操作的一部分。如果你看不出, 是否有交换区页面移出操作。但是交换区页面移入的操作非常活跃, 这也说明,有大量的页面移出的操作正在进行。

BreakingBad
BreakingBad
翻译于 2014/11/27 11:38
1

高速缓存cache)容量的影响

或许你会想为什么高速缓存的大小如此重要。首先,试想一下PostgreSQL共享缓存大到可以放下整张表。重复连续扫描这张表就不需要硬盘的参与,因为数据已经在cache里了。现在假设cache比表小一个单元。一次连续的扫描将会把所有单元载入cache直到最后一个单元。当需要最后一个单元时,最初的单元被移除。当另一次连续扫描开始的时候,最初的单元已经不再cache里了,为了载入它,最开始的单元会被移除,也就是第一次扫描时的第二个单元会被移除。这将持续进行到单元结束。这个例子很极端,但是你可以看到减少一个单元就将会把cache的效率从100%变为0%。这表明找到合适的cache容量会戏剧性的改变性能。

MagicBLS
MagicBLS
翻译于 2014/11/27 13:19
1

合适容量的共享缓存

理论上,POSTGERSQL共享缓存将是:

  • 它应该足够大来应付通常的表访问操作。

  •  它应该足够小来避免 swap pagein 的发生。

记住数据库管理器运行时分配所有的共享存储。这一区域即使在没有访问数据库的请求时也保持一样大小。一些操作系统pageout未指定的共享存储,而另一些LOCK共享存储到RAM中。LOCK贡献存储更好一点。P OSTGERSQL的管理员指导手册里有关于不同操作系统核心配置的信息, http://developer.postgresql.org/docs/postgres/kernel-resources.html

MagicBLS
MagicBLS
翻译于 2014/11/27 13:41
1

内存排序队列大小

另一个优化参数是排序队列的内存总量。当对大表或一个记录集排序时,PostgerSQL会将他们分块排序,将中间结果存储在临时文件里。这些文件将在所有队列处理完毕后合并使用。增加每一队列的大小就会产生更少的临时文件同时加快处理速度。然后,如果队列过大,部分内存队列就会在处理过程中pageout到虚拟内存从而导致pagein的发生。因此,用小点的队列产生更多的临时文件会快很多,但又一次,当太多内存被分配时,虚拟内存pagein产生。记住这个参数是后端使用在处理批次上的,而不是 ORDER BY, CREATE INDEX合并。多少同时的排序就会用多少倍这么多的内存。

这个值可以通过数据库管理器命令行标志改变或通过改变在postgresql.conf中sort_mem的值来改变。

MagicBLS
MagicBLS
翻译于 2014/11/27 15:29
2

缓存规模和排序规模

缓存规模和排序规模都会影响内存的使用。你不可能增加一个的规模, 而不影响另外一个。记住,缓存的规模是在 postmaster 启动的时候, 就设好的。 而排序的规模择由排序的数量决定。一般情况下,缓存规模要大过排序的规模。不过, 某些用到 ORDER BY, CREATE INDEX 或数据合并的查询, 可以通过加大排序规模来提速。 

此外, 许多操作系统对共享内存的分配有限制。修改这一限制, 就意味着, 要重新编译或者配置内核。也就是说, 你要对操作系统这方面相当熟练才行。更多信息, 参考 POSTGRESQL 管理员操作手册,http://developer.postgresql.org/docs/postgres/kernel-resources.html.

BreakingBad
BreakingBad
翻译于 2014/11/27 15:48
1

在调整的开始,使用15%的RAM作为缓存大小,如果有几个大的事物就用2-4%的内存做排序大小,如果你有很多小事物的话就使用更小的内存。你可以尝试提高它来看看性能是否提升,swapping交换是否发生。如果共享缓存过大,你就花费太多时间来维护大量的缓存,而且它会浪费掉本可以被其他进程使用的RAM,无法作为额外的内核磁盘的缓存。

有价值的服务器参数是effective_cache_size。这个参数被优化器用来估计内核磁盘缓存的大小。在使用统一缓存的内核里,这个值应该设为内核未使用RAM的平均值,因为这样内核就可以使用未使用的RAM来缓存最近访问的磁盘页。在有固定磁盘缓存的内核里,这个值应该设为内核缓存的大小,一般为RAM的10%。

Ley
Ley
翻译于 2014/11/28 08:37
1
本文中的所有译文仅用于学习和交流目的,转载请务必注明文章译者、出处、和本文链接。
我们的翻译工作遵照 CC 协议,如果我们的工作有侵犯到您的权益,请及时联系我们。
加载中

评论(7)

w
wzf
看了前面一点,有的地方翻译的确有问题啊。
存储器那个表的表头“Storage Area”和“ Measured in ”被翻译成了“存储区域”和“容量”。
大詹
在没达到一定量之前,简单暴力的分层处理就是合适的。每层干好自己的事就可以了。
大漠真人
大漠真人
这翻译,我去了,标题应该是硬件性能调优吧
sunday12345
sunday12345
shared_buffer, wal 这算技术分析;

这篇文章算,很老的「基本面分析」
中山野鬼
中山野鬼
哈,有些道理我赞同。有些走了极端。。。既然考虑多种磁盘文件系统,你还需要考虑磁头问题?系统如果这么设计,就不靠谱了。或许做极端,专用硬件支撑的数据库,需要考虑这些, 但绝对不是支持多操作系统的通用数据库该考虑的问题。
温习江湖
温习江湖
啥也不说了

CPU寄存器 几字节
CPU高速缓存 几千字节
RAM 几兆字节
硬盘 几千兆字节
白豆腐徐长卿
白豆腐徐长卿
这么旧的文章。
返回顶部
顶部