配置PostgreSQL以匹配服务器配置

Question

目前，我们正在单台专用机器上运行web应用程序和数据库服务器。这是硬件配置- http://www.hetzner.de/en/hosting/produkte_rootserver/ex10。大约50 is的内存是免费的。PostgresSQL只需要600 7Gb，而7Gb服务器进程则需要7GB。平均CPU负载为25%。

软件是Ubuntu 12.04和Postgres 9.1。数据库大小为15 is。

随着负载的增加，我们的应用程序响应时间在过去几个月中从230 As增加到450 As。应用程序占用40%的份额，而DB占用60%的响应时间。

我们缓存了很多东西，但是想知道我们是否可以通过调整Postgres的配置来获得一些东西。经过一些研究，我们发现默认的PostgreSQL配置(https://gist.github.com/darkofabijan/9453c793ceec1ac6274d)是非常保守的，我们肯定应该调整我们的配置。

运行pg调优后，我们得到了以下建议值。

maintenance_work_mem = 1GB 
checkpoint_completion_target = 0.7 
effective_cache_size = 44GB 
work_mem = 288MB 
wal_buffers = 4MB 
checkpoint_segments = 8 
shared_buffers = 15GB 
max_connections = 200 

当我们开始使用推荐值运行我们的PostgreSQL服务器时，当应用程序响应时间跳转到2000ms+而数据库响应时间增加时，我们得到了某种规律的峰值。运行了几个小时后，我们恢复到原来的Ubuntu12.04/PostgreSQL9.1配置。

显然，我们在运行DB方面没有太多经验。我们将非常感谢两项有关建议的具体建议，即建议的价值和指向良好资源的指针。

编辑1：

            name            |         current_setting          |        source
----------------------------+----------------------------------+----------------------
 application_name           | psql                             | client
 client_encoding            | UTF8                             | client
 DateStyle                  | ISO, MDY                         | configuration file
 default_text_search_config | pg_catalog.english               | configuration file
 external_pid_file          | /var/run/postgresql/9.1-main.pid | configuration file
 lc_messages                | en_US.UTF-8                      | configuration file
 lc_monetary                | en_US.UTF-8                      | configuration file
 lc_numeric                 | en_US.UTF-8                      | configuration file
 lc_time                    | en_US.UTF-8                      | configuration file
 log_line_prefix            | %t                               | configuration file
 log_timezone               | localtime                        | environment variable
 max_connections            | 100                              | configuration file
 max_stack_depth            | 2MB                              | environment variable
 port                       | 5432                             | configuration file
 shared_buffers             | 24MB                             | configuration file
 ssl                        | on                               | configuration file
 TimeZone                   | localtime                        | environment variable
 unix_socket_directory      | /var/run/postgresql              | configuration file

Answer 1

这是一个相当广泛的话题。我建议你拿起一本格雷格·史密斯的优秀著作"PostgreSQL 9.0高性能“，然后仔细阅读。http://www.packtpub.com/postgresql-90-high-performance/book

能否显示以下查询的输出：

SELECT name, current_setting(name), source
FROM pg_settings
WHERE source NOT IN ('default', 'override');

要测试的东西：

wal_buffers = 16MB
checkpoint_segments = 32 #(or higher)
shared_buffers = 6GB
random_page_cost = 2.0 # not the first choice of things to tune

您的日志记录设置是什么？是否存在明显的缓慢查询？如果是这样的话，您可以通过獾运行它们，使您对要针对的查询有一个很好的了解。

以下是一些您可以测试的设置，考虑到您当前的设置以及您提到的有关系统的内容。

log_line_prefix = '%t [%p] (user=%u) (db=%d) (rhost=%h) [vxid:%v txid:%x] [%i] '
shared_buffers = '6GB' # requires cluster restart
checkpoint_completion_target = 0.8
checkpoint_segments = 32
effective_io_concurrency = <lesser of cpus and disks/channels>
log_destination = stderr    # This and the log_directory are what I use,
log_directory = pg_log      # it is up to you whether or not you want to change them
logging_collector = 'on'
log_filename = 'postgresql-%Y-%m-%d.log'
log_min_duration_statement = '2s' # Set this as necessary, set lower per role
random_page_cost = 2.5 # If faster disks, drop to 2.0
track_activity_query_size = 4096
work_mem = '50MB'   # Adjust this as necessary.
                    # Be aware this is a per-action setting,
                    # eg. aggregation, sorts, etc can each consume up to
                    # this amount of RAM

您需要重新启动数据库，以使其中一些设置生效。