PostgreSQL DBA快速入门(五) - 配置参数

为什么80%的码农都做不了架构师？>>>   

PostgreSQL数据库配置参数在数据库性能优化和扩展中扮演着重要的角色。

在大部分生产环境中，默认参数配置并不能满足，这就是为什么理解一些重要的服务器参数会对数据库性能起到很大的作用的原因。理解这些参数对于减少服务器停机时间至关重要，这也是确保在合适的时间做出参数调优的前提。

PostgreSQL最主要的调优参数都存在平文件postgresql.conf中，该文件位于数据目录下，通常为$PGDATA/postgresql.conf。

这些配置参数总体分为以下几类：

1. 数据库连接参数
2. 日志参数
3. 内存参数
4. WAL日志参数
5. 查询调优参数

大部分的参数都可以通过单独在postgresql.conf文件中进行手工修改。本篇博客主要介绍连接参数、日志参数和内存参数，其他类型的参数（查询调优相关）会在后续的博客中介绍。

连接参数

有很多参数可以控制用户远程和本地访问数据库，最主要的有以下几个。

listen_addresses

任何允许远程访问的PostgreSQL服务都必须修改listen_addresses参数，以允许监听对外服务IP。默认情况下，仅允许从本地进行访问。最常用的设置方式如下：

listen_addresses = '*'

max_connections

最大连接数参数必须设置为一个固定的值，initdb时，系统默认在postgresql.conf文件中将该值设置为100。因为每个连接都会申请一小片共享内存，所以在共享内存有限的系统中，最大连接数不宜设置的过多。默认最大连接数设置为100，也是基于此原则，在经过很多调研实践后得出的结论。实际上，每个客户端用于排序等操作的非共享内存数量会使共享内存相形见拙，但是我们并不能忽视共享内存的存在。

我们需要警觉，该参数不能设置过大，过大的值会产生负面影响。首当其冲的就是浪费共享内存，因为每一个连接使用的共享内存很少，所以我们往往会忽视它。

Logging Parameters

日志设置是重要且必不可少的，因为日志在错误、警告和其他系统问题定位时非常重要。在系统管理过程中，我们可以根据系统管理法则的要求，手工设置log_destination、log_directory和log_filename等参数来个性化日志文件。当然大多数的环境中，都采用默认设置。在类UNIX系统中，通常将数据库日志配置设置在服务器启动和关闭脚本中，而不是直接在postgresql.conf文件中设置。

log_line_prefix

log_line_prefix默认参数为空，当然这并不是我们想要的。一个合理的初始值建议为：

log_line_prefix='%t:%r:%u@%d:[%p]: '

上述设置会将下面的信息打印在每一行日志中：

%t: 时间戳
%u: 用户名
%r: 远程主机连接
%d: 数据库
%p: 连接进程ID

这些设置的作用也许并不会很快显现出来，尤其是进程ID。一旦想要调查性能问题，这时候你就会庆幸当时在日志中做了这些设置。

log_statement

语句日志时找出性能问题的有效手段。分析log_statement记录的信息以及语句级的关联资源可以东西一些性能问题的根源。

该参数的设置值如下：

none: 不记录任何语句级别的信息。
ddl: 仅记录DDL语句，例如CREATE和DROP。该选项通常被用来记录生产数据库中管理员有意或者无意执行的某些操作。
mod: 记录任何修改数据的语句，该模式下，会记录除简单SELECT语句外的所有语句。如果你的生产操作中大部分是基于SELECT的语句，很少有数据改变，那么启用该参数是个不错的决定。
all: 记录每一条语句。该选项通常不适用于生产环境，因为日志会过载。但是如果您的服务器配置特别高，足够撑起所有的工作负载，那么设置该值也不失为一个很好的选择。

log_min_duration_statement

有时候，你可能早已知晓一些特定的查询的执行周期，此时设置该参数可以让您只记录超过该参数定义周期的查询语句。该值的单位为毫秒，下面例子是1秒的设置方式：

log_min_duration_statement=1000

采用如上设置，系统仅仅对执行时间超过1秒的语句进行记录。此设置有利于发现哪些执行时间较长的SQL语句。

Memory Parameters

数据库组件要求使用内存来缓冲经常访问的数据、事务日志和对数据进行排序。因此内存参数正确调优至关重要，因为它可以帮助我们优化数据库性能。

shared_buffers

该参数设定PostgreSQL共享缓存池的大小。换句话说，该参数决定了PostgreSQL用于缓存数据的内存总量。默认值为128MB，在真实的工作负载中该值线的太小，需要增加。该值需要基于数据集的大小来设置，数据集主要受可用内存的大小以及峰值负载处理量的大小限制。最重要的规则是，在超过1GB内存的系统中，将该值设置为系统内存的四分之一最为合适。该值也可以设置的更大，最大设置为可用内存的40%。如果再超过该值，设置可能并不能明显提升效果，因为PostgreSQL还要依靠操作系统cache。shared_buffers设置为一个更大的值也意味着checkpoint_segments值的增加，这是为了覆盖在一段时间内大量新增或修改数据的处理。该值只能在服务器启动时设置。

temp_buffers

该参数用于每一个数据库会话缓存临时表数据时使用。这些是基础的会话本地缓存，仅用于访问临时表。在PostgreSQL 9.4中，默认值为8MB，在单独的会话中，该设置可以在第一次使用临时表之前就行修改，任何后期的修改都不会在已存在会话中生效。

maintenance_work_mem

该参数规定用于维护操作的最大内存数量，这些操作例如：VACUUM , CREATE INDEX, ALTER TABLE ADD FOREIGN KEY。这些操作不像我们通常所见的会话，他们通常会同时发生，所以将该参数值设置的比work_mem大是个明智的选择。在PostgreSQL 9.4版本中，该值默认为64MB。有一件值得注意的事，当系统运行autovacuum时，autovacuum_max_workers会和该参数定义的值相乘。基于调优和不增加autovacuum执行进程的情况下，最好的设置是maintenance_work_mem值为系统总内存的5%左右。该值通常以大概每1GB的操作系统内存配以50MB的maintenance_work_mem值为主要计算方法。

Ends～

转载于:https://my.oschina.net/javacy/blog/1922357