原作：Frank Wiles
翻译：Evan (Qingyan) Liu, 刘青焱, hmisty [AT] gmail [DOT] com
原文：http://www.revsys.com/writings/postgresql-performance.html
简介
PostgreSQL是当今最先进和灵活的开源SQL数据库。它的强 大和灵活同时也带来一个问题：有这么多人使用PostgreSQL，它的开发者该如何设定缺省配置才能适合所有人的需要呢？不幸的是，答案显然是：他们无 法给出一个适合所有人的缺省配置。
问题是，每一个数据库不仅仅有着不同的设计，更重要的是它们所满足的需求是不同的。有些系统被用来记录海量的数据，但是大部分数据很少被检索。而另 外一些 系统则有着基本静态的数据，频繁的，甚至是疯狂的被检索。然而，大多数的系统对数据库有着不同级别的读写需要[下划线是存疑的翻译， 下同——译者注]。这些许的复杂性，加上完全独特的表结构、数据、硬件配置，希望你开始意识到，调优将会是一件多么困难的事情。
PostgreSQL的缺省配置是非常稳健的，它仅仅是为了满足用户对于一个"处于平均水平"的数据库安装在一个"处于平均水平"的硬件上所能够达 到的最 好期望。本文的目的是帮助PostgreSQL的不同层次的用户们更好的理解PostgreSQL的性能调优这件事情。
理解进程(process)学习PostgreSQL数据库调优的第一步是理解一次查询(query)的生命周期。下面列出了一次查询的步骤：
- 传输查询串到数据库后端
- 解析(parsing)查询串
- 规划(planning)查询，以优化数据检索(retrieval)
- 从硬件上检索数据
- 传输结果到客户端
第一步是发送查询串(你输入的或者你的程序用到的实际的SQL命令)到数据库后端。这个步骤中没有什么好调优的，然而如果你有一些能够事先准 备好的[原 文为that cannot be prepared in advance，怀疑说反了——译者注]、非常大的查询，把它们编写成存储过程 (storage procedure)预先放到数据库里，这样可以最大程度的减少网络传输的数据量。
一旦SQL查询到了数据库服务器内，它就会被解析成符号(token)。同样可以运用上面提到的存储过程来通过预编译减少这个步骤的消耗。
查询规划是PostgreSQL真正开始干一些活的地方了。这个阶段要检查一下，看看查询是不是已经解析好了——如果你的客户端库版 本支持 这个特性的话。同时，它还会分析你的SQL，以便决定检索数据的最有效的方式。我们是否应该使用索引(index)呢？如果是，应该使用哪一个？也许那两 个表适合于进行哈希连接(hash join)？在这个阶段，数据库要做出上述这样一些决定。如果查询被预先解析好了，那么这个步骤就可以 省略了。
现在PostgreSQL规划出了它认为是最佳的检索数据的方法了。真正的检索工作就要开始了。尽管此时还会有一些有帮助的调优方法，但是这一步骤 的性能 已经更多是受到硬件配置的影响了。
最后一步是把结果传输到客户端。尽管这一步没有什么实际的调优选项，你仍然要注意，你的返回数据是从磁盘上读出，然后通过网线送到客户端的。因而， 尽量减 少返回的行列数，只保留那些真正必要的，常常可以提高性能。
有一些对postmaster程序的设置可以显著影响性能。下面就列出了大部分常用的选项，以及它们对于性能的影响：
- max_connections = <num> —— 这个选项设置了数据库后端的同时最大连接数。使用这个特性可以保证你不用启动很多后端以至于开始进行磁盘交换、严重影响到子进程的性能。根据你的应用的需 要，也许完全拒绝一些连接要比降低所有人的性能要来的好些。
- shared_buffers = <num> —— 设置这个选项是提升数据库服务器性能的最简单的方法。因为对于大部分现代的硬件而言，这个选项的缺省值都显得过于低了。一般的建议是，最好把这个值调高到 总物理内存的25%左右。像对于大部分选项一样，我同样在此指出，你可能需要尝试不同的几个数值(高的和低的)，然后观察对于你的特定系统最佳的数值。大 多数人发现，当这个数值超出1/3总物理内存的时候，性能开始下降。
- effective_cache_size = <num> —— 这个数值告诉PostgreSQL的优化器有多少内存可以被用来缓存数据，以及帮助决定是否应该使用索引。这个数值越大，优化器使用索引的可能性也越大。 因此这个数值应该设置成shared_buffers加上可用操作系统缓存两者的总量。通常这个数值会超过系统内存总量的50%。
- work_mem = <num> —— 这个选项是用来控制在排序操作和哈希表格的时候所用的内存的量的。如果你的应用需要做一大堆的排序，那么你可能会需要增加这个内存用量，但是，请小心。这 不是一个系统范围的参数，而是一个操作粒度的。因此，如果一个复杂的查询，包含了若干排序操作，它就会使用多份大小是work_mem的内存！更不用说多 个后端同时执行类似的操作了。这个查询往往会导致你的数据库服务器频繁进行磁盘交换，如果work_mem选项设置的过大的话。在老版本的 PostgreSQL里，这个选项的名字是sort_mem。
- max_fsm_pages = <num> —— 这个选项帮助控制可用空间映射表(free space map)。当一个东西被从一张表里删除的时候，它并没有被从磁盘立即删除，而是简单的在可用空间映射表里打上了"可用"的标记。这个空间就可 以在之后向表中新插入数据的时候被重新使用。如果删除和插入操作非常频繁的话，就有必要增加这个数值以避免表的过度膨胀。
- fsync = <boolean> —— 这个选项决定了是否所有的WAL页面都要在一个事务提交之前通过fsync()调用同步到磁盘。开启这个选项有助于降低数据丢失的风险，但是却会降低写操 作的性能。如果fsync没有开启，那么将会存在数据损坏且不可恢复的风险。请自行决定是否关闭这个选项。
- commit_delay = <num> 和 commit_siblings = <num> —— 这个选项可以被用于调整一次性提交的事务的数量来提高性能。如果在你的事务提交的时候有commit_siblings个活跃的后端，那么服务器会等待 commit_delay微秒，然后尝试一次性提交多条事务操作。
- random_page_cost = <num> —— random_page_cost控制着PostgreSQL如何看待非串行化(non-sequential)磁盘读取操作。较高的数值会使得它更偏好 于顺序扫描(sequential scan)，而不是索引扫描(index scan)——索引扫描通常意味着你的服务器要有非常快速的磁盘。
注意，上述大部分选项配置都会需要较多的共享内存，因而可能需要增加你的系统允许的共享内存量以使得它们能够生效。
显然，硬件的类型和质量也会显著影响数据库的性能。下面是给你的数据库服务器选购硬件的一些小提示(按照重要性排序)：
- 内存 —— 你有越多的内存，你就有越多的磁盘缓存。这一点是十分影响性能的，考虑一下，内存I/O的速度比磁盘I/O快成千上万倍。
- 磁盘类型 —— 显然，高速Ultra-320 SCSI磁盘是你最好的选择，然而高端的SATA磁盘也非常不错了。SATA的单块磁盘足够便宜，选择SATA就意味着，你在同样的预算下能够购买更多。
- 磁盘配置 —— 最合适的配置是RAID 1+0和尽可能多的磁盘。把事务日志(pg_xlog)放到单独的一块磁盘(或者条带(stripe))上。除非你有超过6块磁盘，那么RAID 5不是一个好方法。最新版本的PostgreSQL还允许你使用表空间(tablespace)选项来把不同的表、数据库和索引放到不同的磁盘上以优化性 能。比如，把你最常用的表放在高速的SCSI磁盘上，而把不常用的放到低速的IDE或者SATA磁盘上。
- CPU —— 越多越好。然而，如果你的数据库不会用到很多复杂函数，你最好把你的钱生下来购买更多内存或者更好的磁盘。
通常，你的系统有越多的内存和磁盘，它的性能就越好。这是因为多出来的内存会减少对磁盘的访问。多出来的磁盘会有助于把读写操作分散到多块磁盘上， 从而增 加吞吐量，减少磁盘驱动头拥堵。
另外一个好主意就是把你的应用程序代码和你的数据库服务器放在不同的硬件上。这不仅为数据库服务器提供了更多独享的硬件，更使得操作系统缓存更多 PostgreSQL的数据，而不会因其他应用程序和系统数据而占用磁盘缓存。
例如，如果你有一个web服务器，一个数据库服务器，你可以用一根双绞线接在一个单独的网口上来专门用来连接这两台服务器，所有的web服务器到数 据库服 务器的流量都走这根单独的链路，这样可以最大程度上减小可能的瓶颈。如果你有多台服务器访问同一个数据库服务器的话，你也可以创建一个完全独立的物理网 络，来跑所有的数据库访问流量。
调优数据库最有用的工具就是SQL命令：EXPLAIN ANALYSE。这个命令允许你描绘(profile)你的应用程序执行的每一个SQL查询，以精确查看PostgreSQL规划器将如何执行这个查询。 让我们看一个简短的例子。下面就是一个简单的表结构和查询。
CREATE TABLE authors (
id int4 PRIMARY KEY,
name varchar
);
CREATE TABLE books (
id int4 PRIMARY KEY,
author_id int4,
title varchar
);
如果我们使用查询：
EXPLAIN ANALYZE SELECT authors.name, books.title
FROM books, authors
WHERE books.author_id=16 and authors.id = books.author_id
ORDER BY books.title; 你将会得到类似下面这样的输出：
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Sort (cost=29.71..29.73 rows=6 width=64) (actual time=0.189..16.233 rows=7 loops=1)
Sort Key: books.title
-> Nested Loop (cost=0.00..29.63 rows=6 width=64) (actual time=0.068..0.129 rows=7 loops=1)
-> Index Scan using authors_pkey on authors (cost=0.00..5.82 rows=1 width=36) (actual time=0.029..0.033 rows=1 loops=1)
Index Cond: (id = 16)
-> Seq Scan on books (cost=0.00..23.75 rows=6 width=36) (actual time=0.026..0.052 rows=7 loops=1)
Filter: (author_id = 16)
Total runtime: 16.386 ms
当你分析这个输出的时候，你需要从底往上看。PostgreSQL做的第一件事情就是对books表进行顺序扫描，查看每一个author_id字 段是否 等于16。然后，它对authors表进行索引扫描，因为id字段的PRIMARY KEY属性会导致建表时同时创建一个索引。最后对结果按照books.title字段进行排序。
你在括号中看到的数值是这一个查询步骤的消耗的估算值和实际值。估算值和实际值越接近，一般而言查询性能也就越好。
现在让我们稍微改变一下结构，给books.author_id字段增加一个索引，避免顺序扫描。用下面这个命令：
CREATE INDEX books_idx1 on books(author_id);如果你重跑查询，你不会看到任何改变。这是因为PostgreSQL还没有重新分析数据，也就不知道新索引对这个查询会有什么好处。通过执行下面的 命令可 以解决这个问题：
ANALYZE books;不过，在这个小测试中，规划器还是会倾向于顺序扫描，因为books表没有几行。如果一个查询会返回一个表中的大部分数据，那么规划器也会选择顺序 扫描， 因为这样的确会更快。你也可以通过设置配置参数enable_seqscan为off，强制PostgreSQL使用索引扫描。这不会消除 所有的顺序扫描，因为一些表可能没有索引，但是这确实会强制规划器只要索引可用就总是使用索引扫描。可能比较好的做法是，在每次启动新连接的时候，首先发 送一个命令SET enable_seqscan = off过去，而不是在全局配置中设置。这样做，你可以通过你的应用程序代码来控 制什么时候这个设置生效。不过，一般情况下，关闭顺序扫描应该仅仅用于调优你的应用程序的时候，而不应该在日常的操作当中使用。
一般而言，优化你的查询的最佳方法是在频繁使用的查询所对应的特定的字段和字段的组合上使用索引。不幸的是，这往往是通过试错来完成的。 你 也应当注意，增加一张表的索引的数量会增加每一次插入和更新操作的写操作的次数。所以，别做蠢事，把每一张表的每一个字段都加上索引。
你可以通过调整为一张表或一个字段收集的统计信息的量，帮助PostgreSQL做你希望它做的事情，使用下面这个命令：
ALTER TABLE <table> ALTER COLUMN <column> SET STATISTICS <number>;这个数值可以是一个0到1000的数字。PostgreSQL根据这个数字决定为那个字段收集什么量级的统计信息。这帮助你通过为所有的表格和字段 生成大 量的统计信息，控制生成的查询规划，而不用使用缓慢的vacumm和analyze操作了。
另外一个调优的有用的工具是打开查询日志。你可以告诉PostgreSQL哪些查询是你感兴趣的，你可以通过log_statement配 置选项来配置将它们记录下来。这一点在有许多用户在你的系统里通过一些工具，例如Crystal Reports或者直接用psql，来执行专有查询的情况下，非常有用。
数据库设计和布局(Layout)有时候数据库设计和布局会影响到性能。例如，如果你有一个雇员数据库，像这样的：
CREATE TABLE employees (
id int4 PRIMARY KEY,
active boolean,
first_name varchar,
middle_name varchar,
last_name varchar,
ssn varchar,
address1 varchar,
address2 varchar,
city varchar,
state varchar(2),
zip varchar,
home_phone varchar,
work_phone varchar,
cell_phone varchar,
fax_phone varchar,
pager_number varchar,
business_email varchar,
personal_email varchar,
salary int4,
vacation_days int2,
sick_days int2,
employee_number int4,
office_addr_1 varchar,
office_addr_2 varchar,
office_city varchar,
office_state varchar(2),
office_zip varchar,
department varchar,
title varchar,
supervisor_id int4
);
这个设计非常容易理解，可是在几个层次上它都不太好。尽管这依赖于你特定的应用，然而在大多数情况下，你都不大会需要同时访问所有的数据。在你的应 用中， 处理HR功能能的部分，可能你只是对他们的name, salary, vacation_days, sick_days等字段感兴趣。然而，如果应用程序要显示一个组织结构图，那么它可能只关心表格的department和supervisor_id部 分。
通过把这张表格拆成几张小表，你可以获得更加有效率的查询，因为PostgreSQL可以更少的读取数据，而不会影响功能。下面是一种更好的结构：
CREATE TABLE employees (
id int4 PRIMARY KEY,
active boolean,
employee_number int4,
first_name varchar,
middle_name varchar,
last_name varchar,
department varchar,
title varchar,
email varchar
);
CREATE TABLE employee_address (
id int4 PRIMARY KEY,
employee_id int4,
personal boolean,
address_1 varchar,
address_2 varchar,
city varchar,
state varchar(2),
zip varchar
);
CREATE TABLE employee_number_type (
id int4 PRIMARY KEY,
type varchar
);
CREATE TABLE employee_number (
id int4 PRIMARY KEY,
employee_id int4,
type_id int4,
number varchar
);
CREATE TABLE employee_hr_info (
id int4 PRIMARY KEY,
employee_id int4,
ssn varchar,
salary int4,
vacation_days int2,
sick_days int2
);
通过这种表结构，一个员工的数据被打散到几个逻辑组里。主表包含了最常用的信息，而其它的表则存储了其余的信息。这种布局的额外好处就是一个员工现 在可以同时关联多个电话号码和地址了。
另外一个有用的技巧就是在那些查询一个值比其它值更频繁的列上创建部分索引(partial index)。拿上面的雇员表为例。你的应用的主要部分可能只显示在职的员工，在active列上对在职(active='t')的部分创 建部分索引可以加速查询，同时还可能帮助到规划器选择在某些情况下使用索引、某些情况下不用。你可以用下面的命令创建部分索引：
CREATE INDEX employee_idx2 ON employee(active) WHERE active='t';比如在一些任务跟踪系统里，有的表有一个employee_id列，当某一行不对应任何一个员工的时候该列的值就为空(null)。在这类系统中， 可能有一类"查看未分配的任务"的功能，创建下面这个部分索引能够让这个功能提速：
CREATE INDEX tickets_idx1 ON tickets(employee_id) WHERE employee_id IS NULL;应 用开发
有很多基于SQL数据库构建应用的方法，但是其中有两种主要的形式，叫做无状态的(stateless)和有状态的 (stateful)。在性能方面，它们各自面临不同的问题。
无状态通常是基于web的应用所用的一种访问形式。你的软件连接到数据库，执行一系列的查询，把结果返回给用户，然后断开连接。下一个操作时，用户 把上述过程重新来过，新的连接、新的查询集合、等等。
有状态的应用通常是非web的用户界面，应用初始化一个数据库连接，然后在应用运行期间一直保持不关闭连接。
在基于web的应用中，用户每一次请求都创建一个新的数据库连接。尽管PostgreSQL创建连接所需的时间很短，通常不是一个十分耗时的操作[如 果在高负荷的热门网站上，这个操作的速度仍然是不可接受的，并且很快数据库性能会急剧下降，你的网站速度将非常慢，用户走光——译者注]，最好使 用一些数据库连接池技术来获得最大的性能。[其实真正的网站都要承受大量用户的访问，这时候往往需要使用大量的cache技术，例如 memcache，来缓解数据库的压力，提高网站的速度，提升用户体验——译者注]
下面是一些数据库连接池技术的简短列表：
- Pgpool是一个非常小的服 务器程序，你可以把它和数据库客户端放到同一台服务器上跑，它可以为本地或远程的数据库建立连接池。你的应用程序则连接到pgpool，而不是直接连接到 postmaster。从应用程序的角度来讲，所有的使用方法和以前完全一样。
- 在mod_perl的环境里你可以使用Apache::DBI模 块来把数据库连接池内嵌到Apache自身之中。
- SQLRelay是另外一个数据库连接管理 器，它某种程度上是数据库无关的(agnostic)。它能够工作在除了PostgreSQL之外的若干种数据库之上。
- 你也可以写一些代码来自己实现连接池，但是我仍然强烈建议你使用一些成熟的解决方案，以减少你调试和除错的工作量。
不过也应当注意到，在一些诡异的情况下，我的确看到使用数据库连接池反而会降低web应用的性能。在一些特定的时候使用连接池的开销甚至比简单的创 建连接要来的大。因而我建议，最好把这两种方法都测试一下，看看对于你的环境哪一种是最好的。
如果创建有状态应用程序，你可以通过DECLARE命 令来使用数据库游标(cursor)。游标使得你得以规划和执行一个查询，但是仅仅在你需要的时候才真正取回数据，比如一次取一行数据。这可以大大增加你 的UI响应速度。
一般应用问题这些问题通常会对有状态和无状态的应用产生同样的影响。一个好的技巧是，在服务器端对于任何一个经常执行的查询进行prepare。这样就可以通过 把查询规划缓存起来以备后用来减少整体查询的时间。
然而，应当注意，如果使用占位符(例如'column_name=?')来预先prepare一个查询，规划器不会总能够选择到一个最佳的规划。例 如，查询中有一个布尔值字段'active'的占位符，当该列为false的时候有一个部分索引，那么规划器是不会使用这个部分索引的，因为规划器不能确 信当查询被真正执行的时候这个字段的值是true还是false。
显然，你也可以使用存储过程来降低查询生命周期中的传输、解析和规划部分的比重。最好描绘(profile)一下你的应用，找到最常使用的查询和数 据操作，然后把它们写成存储过程。
这里有一个简短列表。
- PostgreSQL Homepage——关于 PostgreSQL的一切
- psql-performance mailing list——这个PostgreSQL的邮件列表专注于性能方面的问题的讨论
- General PostgreSQL 7.4 performance tuning tips，来自Varlena.com。这个页面不是最终版本，但是仍然 有一些值得一看的东西。
- Annotated .conf file 一个注释过的配置文件，以及其他一些有用的信息。
- PostgreSQL 8.0 Performance Checklist
- PostgreSQL Tuning Service
- PostgreSQL Support Service
Frank Wiles改用PostgreSQL作为他的首选数据库已经超过8个年头了。此间他从未回头。他在一系列的情境下使用PostgreSQL，然而多数情况 下会结合Apache和mod_perl来构建基于浏览器的应用。他发表了若干篇文章，和一本书，涵盖了从系统管理到应用开发的各种主题。他的联系方式是frank@revsys.com。
没有评论:
发表评论