PostgreSQL系统配置优化

砂拉豆

原文：http://blog.csdn.net/xto/article/details/5403782
PG的配置文件是数据库目录下的postgresql.conf文件，8.0以后的版本可支持K，M，G这样的参数，只要修改相应参数后重新启动PG服务就OK了。
shared_buffers：这是最重要的参数，postgresql通过shared_buffers和内核和磁盘打交道，因此应该尽量大，让更多的数据缓存在shared_buffers中。通常设置为实际RAM的10％是合理的，比如50000(400M)。将所有的内存都给shared_buffers 将导致没有内存来运行程序。 unix 管理内存使用 swap, 当没有内存可用的时候，内核会将长时间不用的内存挪到 swap 去，这个叫：swap pageout，当你再次使用它的时候内核又会将其挪回来，这个叫：swap pagein，这个操作有很大的不好，因为它会导致你的程序挂起直到操作完成。
正确的 Shared Buffer Cache 大小：
(1) 它应该足够大来应付通常的表访问操作。
(2) 它应该足够小来避免 swap pagein 的发生。
work_mem： 在pgsql 8.0之前叫做sort_mem。postgresql在执行排序操作时，会根据work_mem的大小决定是否将一个大的结果集拆分为几个小的和 work_mem查不多大小的临时文件。显然拆分的结果是降低了排序的速度。因此增加work_mem有助于提高排序的速度。通常设置为实际RAM的2% -4%，根据需要排序结果集的大小而定，比如81920(80M)。
cache size 和 sort size 都影响内存的使用。记住， cache size 是在postmaster 启动的时候就申请好的，sort size 的改变是依赖于执行多少个排序。通常，cache size 比 sort size 更有效果。 一开始调整, 如果你只有一些大的session而有更多的小session: 10% of RAM for cache size 2-4% of RAM for sort size 还有一个很有价值的参数： effective_cache_size。 优化器用这个参数来预估内核的硬盘 buffer cache. 当内核有 unified buffer cache (统一缓冲), 这个值将是没被使用的内存的平均值，因为这种内核会使用所有没有使用的内存来作最近硬盘访问的缓存的；当内核用 fixed-sized disk buffer cache 时，这个参数应该设成一样的，通常是内存的 10%.
    effective_cache_size：是postgresql能够使用的最大缓存，这个数字对于独立的pgsql服务器而言应该足够大，比如4G的内存，可以设置为3.5G(437500)
    maintence_work_mem：这里定义的内存只是在CREATE INDEX, VACUUM等时用到，因此用到的频率不高，但是往往这些指令消耗比较多的资源，因此应该尽快让这些指令快速执行完毕：给maintence_work_mem大的内存，比如512M(524288)
max_connections： 通常，max_connections的目的是防止max_connections * work_mem超出了实际内存大小。比如，如果将work_mem设置为实际内存的2%大小，则在极端情况下，如果有50个查询都有排序要求，而且都使用2％的内存，则会导致swap的产生，系统性能就会大大降低。当然，如果有4G的内存，同时出现50个如此大的查询的几率应该是很小的。不过，要清楚 max_connections和work_mem的关系。
maintenance_work_mem (integer)：声明在维护性操作中使用的最大的内存数，比如 VACUUM， CREATE INDEX，和 ALTER TABLE ADD FOREIGN KEY 等。 数值是用千字节计的，缺省是 16384 千字节（16 MB）。因为在一个数据库会话里，任意时刻只有一个这样的操作可以执行，并且一个数据库安装通常不会有太多这样的工作并发执行，把这个数值设置得比 work_mem 更大是安全的。 更大的设置可以改进清理和恢复数据库转储的速度。
max_stack_depth (integer)：声明服务器的执行堆栈的最大安全深度。为此设置一个参数的原因是内核强制的实际堆栈尺寸（就是 ulimit -s 或者局部等效物的设置），小于一个安全的一兆字节左右的范围。 需要这么一个安全的界限是因为在服务器里，并非所有过程都检查了堆栈深度，儿只是在可能递规的过程，比如表达式计算这样的过程里面进行检查。 把这个参数设置得大于实际的内核限制讲意味着一个正在跑的递归函数可能会导致一个独立服务器进程的崩溃。缺省设置是 2048 KB （两兆），这个值相对比较小，不容易导致崩溃。 但是，这个值可能太小了，以至于无法执行复杂的函数。
自由空间映射max_fsm_pages (integer)：设置在共享的自由空间映射表里自由空间会跟踪的最大数目的磁盘页面数。 每个页面槽位需要消耗六个字节的共享内存。这个设置必须大于 16 * max_fsm_relations。 缺省是 20000。这个选项只能在服务器启动的时候设置。
max_fsm_relations (integer)：设置自由空间将在共享地自由空间映射里跟踪的最大数目的关系（表和索引）。每个槽位大概要使用五十字节左右。缺省是 1000。这个选项只能在服务器启动的时候设置。
内核资源使用max_files_per_process (integer)：设置每个服务器进程允许同时打开的最大的文件数目。缺省是 1000。 如果内核强制一个合理的每进程限制， 那么你不用操心这个设置。但是在一些平台上（特别指出的是，大多数BSD 系统）， sysconf 返回一个系统真正可以支持的数目大的多的数值。如果你发现有 "Too many open files" 这样的失败现象，那么就尝试缩小这个设置。这个选项只能在服务器启动时设置。
preload_libraries (string)：这个变量声明一个或者多个在服务器启动的时候预先装载的共享库。可以选择在装载每个库的时候调用一个无参数的初始化函数。 要声明这个函数，可以在库名字后面加一个冒号，然后增加一个初始化函数名字。 比如 '$libdir/mylib:mylib_init' 会预先装载 mylib 并且执行 mylib_init。 如果装载了多过一个库，用逗号分隔它们。如果没有找到声明的库或者没有找到初始化函数，那么服务器将启动失败。可以用这个方法预先装载PostgreSQL 的过程语言库， 通常是使用 '$libdir/plXXX:plXXX_init' 语法，这里的 XXX 是 pgsql，perl，tcl，或者 python。
通过预先装载一个共享库（以及在需要的时候初始化它）， 我们就可以避免第一次使用这个库的那些启动时间。不过，启动每个服务器进程的时间可能会增加，即使进程从来没有使用过这些库也这样。因此我们只是建议对那些将被大多数会话使用的库才使用这个选项。
基于开销的清理延迟：在 VACUUM 和 ANALYZE 命令执行过程中， 系统维护一个内部的指针，这个指针跟踪所执行的各种 I/O 操作的近似开销。 如果积累的开销达到了一个限制（通过 vacuum_cost_limit 声明），那么执行这个操作的进程将睡眠一会儿（用 vacuum_cost_delay 声明）。 然后它会重置指针然后继续执行。
这个特性的目的时允许管理员减少这些命令在并发活动的数据库上的 I/O 影响。 有些情况下，像 VACUUM 和ANALYZE 这样的维护命令并不需要迅速完成；但是，通常都不希望这些命令会严重干扰系统执行其它数据库操作的响应能力。 基于开销的清理延迟为管理员提供了一个实现这个目的的手段。
缺省的时候，这个特性是关闭的。要想打开它，把 vacuum_cost_delay 变量设置为一个非零值。
vacuum_cost_delay (integer)：以毫秒计的时间长度，如果超过了开销限制，那么进程将睡眠一会儿。缺省值是 0，它关闭基于开销的清理延迟特性。正数值打开基于开销的清理。 不过，要注意在许多系统上，sleep 延迟的有效分辨率是 10 毫秒； 把 vacuum_cost_delay 设置为一个不是 10 的整数倍的数值与将它设置为下一个 10 的整数倍作用相同。
vacuum_cost_page_hit (integer)：清理一个在共享缓存里找到的缓冲区的开销。它代表锁住缓冲池，查找共享的散列表以及扫描页面的内容的开销。缺省值是 1。
vacuum_cost_page_miss (integer)：清理一个要从磁盘上读取的缓冲区的估计开销。这个行为代表锁住缓冲池，查找共享散列表，从磁盘读取需要的数据块以及扫描它的内容的开销。 缺省值是 10。
    vacuum_cost_page_dirty (integer)：如果清理修改一个原先是干净的块的预计开销。它需要一个把脏的磁盘块再次冲刷到磁盘上的额外开销。缺省值是 20。
vacuum_cost_limit (integer)：导致清理进程休眠的积累开销。缺省是 200。
注意: 有些操作会持有关键的锁，并且应该尽快结束。 在这样的操作过程中，基于开销的清理延迟不会发生作用。为了避免在这种情况下的长延时，实际的延迟是这样计算的： vacuum_cost_delay * accumulated_balance / vacuum_cost_limit 与 vacuum_cost_delay * 4 之间的最大值。
后端写进程
从 PostgreSQL 8.0 开始，就有一个独立的服务器进程，叫做后端写进程， 它唯一的功能就是发出写"脏"共享缓冲区的命令。 这么做的目的是让持有用户查询的服务器进程应该很少或者几乎不等待写动作的发生，因为后端写进程会做这件事情。这样的安排同样也减少了检查点造成的性能下降。 后端写进程将持续的把脏页面刷新到磁盘上，所以再检查点到来的时候，只有几个页面需要刷新到磁盘上。但是这样还是增加了 I/O 的总净负荷，因为以前的检查点间隔里，一个重复弄脏的页面可能只会冲刷一次， 而同一个间隔里，后端写进程可能会写好几次。在大多数情况下，连续的低负荷要比周期性的尖峰负荷好，但是在本节讨论的参数可以用于为本地需要调节其行为。
bgwriter_delay (integer)：声明后端写进程活跃回合之间的延迟。在每个回合里，写进程都会为一些脏的缓冲区发出写操作（可以用下面的参数控制）。选取的缓冲区总是那些在当前的脏缓冲区里当前最少使用的。 然后它就休眠bgwriter_delay 毫秒，然后重复动作。缺省值是 200。 请注意在许多系统上，休眠延时的有效分辨率是 10 毫秒；因此，设置 bgwriter_delay 为一个不是 10 的倍数的数值与把它设置为下一个 10 的倍数是一样的效果。 这个选项只能在服务器启动的时候或者 postgresql.conf 文件里设置。
bgwriter_percent (integer)：在每个回合里，当前的脏缓冲区中不超过这个百分比的量将被写到磁盘上 （把小数圆整为下一个整数缓冲区的数值）。 这个选项只能在服务器启动的时候或者 postgresql.conf 文件里设置。
bgwriter_maxpages (integer)：在每个回合里，不超过这个数值的脏缓冲区写入。缺省值是 100。 这个选项只能在服务器启动的时候或者 postgresql.conf 文件里设置。
小的 bgwriter_percent 和 bgwriter_maxpages 减少后端写进程导致的额外 I/O 负荷， 但是会导致在检查点的时候的更多工作。要降低检查点时的峰值负荷，增加这些值。要想完全关闭后台写进程，可以把 bgwriter_percent 和/或bgwriter_maxpages 设置为零。 
有三个主要方面可以提升PostgreSQL的潜能。
查询方式的变化 
这主要涉及修改查询方式以获取更好的性能: 
创建索引，包括表达式和部分索引； 
使用COPY语句代替多个Insert语句； 
将多个SQL语句组成一个事务以减少提交事务的开销； 
从一个索引中提取多条记录时使用CLUSTER； 
从一个查询结果中取出部分记录时使用LIMIT； 
使用预编译式查询（Prepared Query)； 
使用ANALYZE以保持精确的优化统计； 
定期使用 VACUUM 或 pg_autovacuum 
进行大量数据更改时先删除索引（然后重建索引）