PostgreSQL 性能优化

你的名字 2022-08-09 13:40 202阅读 0赞

每次看postgresql的设定，好像每次都忘记。过一段时间又要看。今天终于决定，开始blog吧。  
postgresql 优化与维护  
1. 硬件  
  
数据库最重要的就是 I/O了。所以一切从I/O开始。  
RAID: 这个基本不用说，数据库放RAID10上面，只读的备份数据库可以放RAID0，反正挂了没关系。谨记：数据库是Random Read  
  
RAID卡的选择：  
RAID卡一定要带电池的才可以（BBU）有电源的才能做到东西写进 CACHE，RAID就返回硬盘写成功（不用等）  
1. Areca  
2. LSI (真正的LSI，re-brand不要）  
3. HP P400 以上系列  
  
硬盘选择：  
  
首选是SAS: 15K RPM 每个SAS大约能提供25MB/s的Random Write。也就是说在RAID10的设定下，如果需要50MB/s的Random Write就需要４个硬盘  
  
节俭选择是： SATA 可以多用几个硬盘（SAS一倍数量）达到在RAID10中接近SAS的速度。就算SATA买SAS一倍的数量，价格仍然比SAS便宜。  
也可以买 产品： 例如 Compaq的 MSA 70 (P800 Battery backed RAID control)  
  
CPU：64位  
Cache：越大越好 （现在个人电脑都3M的cache了）  
CORE：越多 越好 （postgresql毕竟是跑cpu的）建议最少4个core  
RAM： 最少4G。通常根据具体需求，用16-64G的RAM  
2. OS （系统）  
  
可用系统：  
1. Debian Stable  
2. CentOS  
3. Ubuntu LTS  
4. Red Hat  
5. SUSE Enterprise  
  
如果准备付费（服务），那么就是 Canonical, Novell 跟 Redhat这三家选择而已  
  
如果准备不买任何服务，可以用Debian, CentOS, Ubuntu LTS  
  
这里还是觉得系统用Red Hat (不付费就CentOS）毕竟人家是企业级的老大哥，错不了。  
\* 现在CentOS也可以买到服务了。  
  
不可用系统： 例如 fedora (redhat QA) ubuntu (non-LTS)  
Scheduler:  
  
Grub 增加： elevator=deadline  
  
redhat 的图标可以看出，deadline是数据库的最佳选择  
文件系统 （Filesystem）  
  
这里的选择是：ext2，ext3 跟 ext4。 为什么只考虑这几个呢？因为数据库还是稳定第一，内核开发人员所做的文件系统，理论上说出问题的情况会少点。  
  
WAL： 放ext2 因为WAL本身自己有Journal了，不需要用ext3 （ext2快很多）  
data： ext3  
  
Block Size： postgres自己是8k的block size。所以文件系统也用8k的 block size。这样才能最佳的提高系统的效能。  
  
ext4：出来时间还 不够长，不考虑。  
分区 （Partitioning）  
  
Postgres 跟系统 OS 应该在不同分区  
  
系统（OS）：系统应该放独立的RAID1  
  
数据库 （Postgres Data）：数据库应该放独立的RAID10上。 如果RAID是带电池的，mount 的时候给 data=writeback的选项  
独立的数据库分区，就不许要记录文件时间了（都是放数据的）所以mount的时候要给noatime的 选项，这样可以节约更新时间(timestamp)的I/O了。  
  
WAL日志（xlogs）： 独立的RAID1上 （EXT2 系统）日志是 Sequential write，所以普通的硬盘（SATA）速度就足够了，没有必要浪费SAS在log上  
  
Postgresql 日志（logs）：直接丢给syslog就可以。最好在syslog.conf中设定单独的文件名. 这里 例如用local2来做postgresql  
local2.\* -/var/log/postgres/postgres.log  
记得log要给Async，这样才不会等卡在log的I/O上， 同时记得设定logrotate以及创建路径（path）  
  
ext2 VS ext3 性能测试：  
HP DL585  
4 Dual Core 8222 processors  
64GB RAM  
(2) MSA70 direct attached storage arrays.  
25 spindles in each array (RAID 10)  
HP P800 Controller  
6 Disk in RAID 10 on embedded controller  
  
xlog with ext3: avg = 87418.44 KB/sec  
xlog with ext2: avg = 115375.34 KB/sec  
3. Postgres 内存 （Memory Usage)  
  
Shared Buffer Cache  
Working Memory  
Maintenance Memory  
Shared Buffers  
  
Postgres 启动时要到的固定内存。每个allocation是8k。 Postgres不直接做硬盘读写，而是把硬盘中的东西放入Shared Buffers，然后更改Shared Buffers，在flush 到硬盘去。  
通常 Shared Buffers设定为内存（available memory）的25%-40%左右。  
在系统（OS）中，记得设置 kernel.shmmax的值（/etc/sysctl.conf)  
kernel.shmmax决定了进程可调用的最大共享内存数量。简单的计 算方法是  
kernel.shmmax=postgres shared\_buffers + 32 MB  
要保留足够的空间（不然会out of memory）postgresql除了shared buffer还会用到一些其他的内存，例如max\_connections, max\_locks\_pre\_transaction  
Working Memory  
  
这个是postgres运行作业中 （task）需要的内存，例如内存内的hashed （aggregates， hash joins）sort （order by, distinct 等等）合理的设定，可以保证postgres在做这些东西的时候可以完全在内存内完成，而不需要把数据吐回到硬盘上去作swap。但是设定太大的话，会造 成postgres使用的内存大于实际机器的内存，这个时候就会去硬盘swap了。（效能下降）  
  
working memory是per connection and per sort的设定。所以设定一定要非常小心。举例来说，如果设定working memory为32MB，那么以下例子：  
select \* from lines, lineitems  
where lines.lineid = lineitems.lineid  
and lineid=6  
order by baz;  
这里就可 能用到64MB的内存。  
hashjoin between lines and lineitems (32MB)  
order by baz (32MB)  
要注意自己有多少query是用到了order by或者join  
如果同时有100个链接，那么就是 100 connection X 64MB = 6400MB (6G) 内存  
通常来说，working mem不要给太大，2-4MB足够  
  
在postgres 8.3之后的版本，working mem可以在query中设定  
Query:  
begin;  
set work\_mem to ‘128MB’;  
select \* from foo order by bar;  
insert into foo values (‘bar’);  
reset work\_mem;  
commit;  
Function:  
create function return\_foo() returns setof text as  
$ select \* from foo order by bar; $  
SET work\_mem to ‘128MB’  
LANGUAGE ’sql’  
  
postgres官方不建议（但是支持）在 postgresql.conf文件中更改work\_mem然后HUP （数据库应该没有任何中断）  
  
利用 explain analyze可以检查是否有足够的work\_mem  
sort (cost=0.02..0.03 rows=1 width=0) (actual time=2270.744..22588.341 rows=1000000 loops=1)  
Sort Key: (generate\_series(1, 1000000))  
Sort Method: external merge Disk:13696kb  
\-> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.006..144.720 rows=1000000 loops=1)  
Total runtime: 3009.218 ms  
(5 rows)  
以上的 query分析显示，这里需要从硬盘走13MB的东西。所以这个query应给set work\_mem到16MB才能确保性能。  
Maintenance Memory （维护内存）  
  
maintenance\_work\_mem 决定系统作维护时可以调用的内存大小。  
这个也是同样可以在query中随时设定。  
这个内存只有在VACUUM, CREATE INDEX 以及 REINDEX 等等系统维护指令的时候才会用到。系统维护是，调用硬盘swap会大大降低系统效能。通常maintenance\_work\_mem超过1G的时候并没有 什么实际的效能增加（如果内存够， 设定在1G足以）  
Background Writer (bgwriter)  
  
功能：  
负责定时写 shared buffer cache 中的 dirty shared buffers  
好处：  
a. 减少系统flush shared buffers到硬盘（已经被bgwriter做了）  
b. 在checkpoint中，不会看到I/O的突然性暴增，因为dirty buffers在背景中已经被flush进硬盘  
坏处：  
因为一直定时在背后flush disk，会看到平均硬盘I/O怎加（好过checkpoint时I/O暴增）  
  
设定：  
  
bgwriter\_delay：  
sleep between rounds。 default 200（根据机器，数据而调整）  
  
bgwriter\_lru\_maxpages:  
决 定每次bgwriter写多少数据。如果实际数据大于这里的设定，那么剩余数据将会被postgres的进程（server process）来完成。server porcess自己写的数据会造成一定的性能下降。如果想确定所有的数据都由bgwriter来写，可以设定这里的值为-1  
  
bgwriter\_lru\_multiplier:  
采 用计算的方式来决定多少数据应该被bgwriter来写。这里保持内置的2.0就可以。  
  
计算bgwriter的I/O:  
1000 / bgwriter\_delay \* bgwriter\_lru\_maxpages \* 8192 = 实际I/O  
(8192是 postgres的8k block）  
例如：  
1000/200 \* 100 \* 8192 = 4096000 = 4000 kb  
  
bgwrater 可以用 pg\_stat\_bgwriter 来监测。如果想要观察bgwrater 的运行状况，记得首先清理旧的stat信息。  
  
bgwriter如果设定的太大（做太多事情）那么就会影响到前台的效能 （server）但是如果由系统（server）来做buffer flush同样会影响效能。所以这里的最好设定就是通过观察 pg\_stat\_bgwriter 来找到一个最佳的平衡点。  
WAL （write ahead log)  
  
postgres中的所有写动作都是首先写入WAL，然后才执行的。这样可以确保数据的准确跟 完整。当中途数据库崩溃的时候，postgres可以通过WAL恢复到崩溃前的状况而不会出现数据错误等等问题。  
WAL 会在两种情况下被回写硬盘。  
1. commit。 当commit数据的时候，WAL会被强制写回硬盘（flush）并且所有这个commit之前的东西如果在WAL中，也会一同被flush。  
2. WAL writer进程自己会定时回写。  
  
FSYNC vs ASYNC  
postgres 的 default 是做 fsync，也就是说postgres会等待数据被写入硬盘，才会给query返回成功的信号。如果设定sync=no关闭fsync的 话，postgres不会等待WAL会写硬盘，就直接返回query成功。通常这个会带来15-25%的性能提升。  
但是缺点就是，如果系统崩溃 （断电，postgres挂掉）的时候，你将有可能丢失最后那个transcation. 不过这个并不会造成你系统的数据结构问题。（no data corrupt）如果说在系统出问 题的时候丢失1-2笔数据是可以接受的，那么25%的性能提升是很可观的。  
  
WAL设定：  
fsync 可以选择on或者off  
wal\_sync\_method：  
linux中是使用fdatasync。其他的。。。不知道，应该是看系统的文 件参数了  
full\_page\_writes:  
开启的时候，在checkpoint之后的第一次对page的更改，postgres会将每 个disk page写入WAL。这样可以防止系统当机（断电）的时候，page刚好只有被写一半。打开这个选项可以保证page image的完整性。  
关 闭的时候会有一定的性能增加。尤其使用带电池的 RAID卡的时候，危险更低。这个选项属于底风险换取性能的选项，可以关闭  
  
wal\_buffers:  
WAL 的储存大小。default 是 64 kb。 实验证明， 设定这个值在 256 kb 到 1 MB 之间会提升效能。  
  
wal\_writer\_delay  
WAL 检查WAL数据（回写）的间隔时间。值是毫秒（milliseconds)  
Checkpoints  
  
确保数据回写硬盘。dirty data page会被 flushed回硬盘。  
checkpoint 由以下3中条件激发 （bgwriter如果设定，会帮忙在后台写入，所以就不会有checkpoint时候的短期高I/O出现）  
1. 到达设定的WAL segments  
2. 到达设定的timeout  
3. 用户下达checkpoint指令  
如果 checkpoint运行频率高于checkpint\_warning值。postgres会在日志（log）中记录出来，通过观察log，可以来决定 checkpoint\_segments的设定。  
增加cehckpoint\_segments或者checkpoint\_timeout可以有一 定的效能提升。而唯一的坏处就是如果系统挂了，在重启的时需要多一点时间来回复（系统启动回复期间数据库是不能用的）鉴于postgres很少挂掉，这个 其实可以设定的很长（1天都可以）  
  
设定：  
checkpoint\_segments 最多的wal log数量，到达后会激发checkpoint，通常设定在30就好  
checkpoint\_timeout 一般设置15-20分钟，常的可以设定1天也没关系  
checkpoint\_completion\_target 这个保持不动就好。内建是0.5，意思就是每个checkpoint预计在下个checkpoint完成前的一半时间内完成（听起来有点绕嘴，呵呵）  
checkpoint\_warning 如果checkpint速度快于这个时间，在log中记录。内建是30秒  
  
理论中的完美设定，就是你的backend从来不用回写硬盘。 东西都是由background来写入的。这个就要靠调整bgwriter, checkpoints跟wal到一个最佳平衡状态。当然这个是理想中的完美，想真的做到。。。继续想吧。呵呵  
4. 维护 – 保持postgres的笑容  
  
维护数据库是必 须的。基本维护  
vacuum  
  
delete数据的 时候，数据库只是记录这笔数据是‘不要的‘并不是真的删除数据。所以这个时候就要vacuum了，vacuum会把标记为‘不要‘的数据清除掉。这里要注 意的是，vacuum不会清理index。当数据更改超过75%的时候，需要重新建立index。postgres 8.4 index可以用cluster重建速度快很多。在postgres 9.x中，vacuum=cluster，没有任何区别了（保留cluster只是为了兼容旧版指令）  
  
Full Vacuum  
这个会做exclusive lock。vacuum跟full vacuum的区别是vacuum会把标志为‘不要‘的空间标志成可以再次使用（回收）而 full vacuum则会把这个空间删除（返还给系统OS）所以vacuum之后你的postgres在硬盘上看到的占用空间不会减少，但是full vacuum会减小硬盘占用空间。不建议使用full vacuum，第一没必要，第二exclusive lock不好玩。  
ANALYZE  
  
Analyze 会更新统计信息（statistics）所有的query的最佳方案，以及sql prepared statement都是靠这统计信息而决定的。所以当数据库中的一定量数据变动后（例如超过10%），要作analyze，严格的说，这个是应该常做的东 西，属于数据库正常维护的一部分。另外一个很重要的就是，如果是 upload数据（restore那种）做完之后要记得作analyze（restore自动不给你作的）  
当 建立新的table的时候，或者给table增加index，或者对table作reindex，或者restore数据进数据库，需要手动跑 analyze才可以。analyze直接影响default\_statistics\_target数据。  
Autovacuum  
  
根 据postgres的官方资料，autovacuum在8.3之后才变得比较真的实用（8.1推出的）因为在8.3之前，autovacuum一次只能同 时做一个数据库中的一个table。 8.3之后的版本，可以作多数据库多table。  
  
设定  
  
log\_autovacuum\_min\_duration:  
\-1 为关闭。0是log全部。>0就是说超过这个时间的就log下来。例如设定为30，那么所有超过30ms的都会被日志记录。  
autovacuum\_max\_workers:  
同 时启用的autovacuum进程。通常不要设定太高，3个就可以。  
autovacuum\_naptime:  
检查数据库的时 间，default是1分钟，不用改动  
autovacuum\_vacuum\_threshold:  
最低n行记录才会引发 autovacuum。也就是数据改变说低于这个值，autovacuum不会运行。default是50  
autovacuum\_analyze\_threshold:  
运 行analyze的最低值，跟上面的一样  
autovacuum\_vacuum\_scale\_factor:  
table中的百分比的计算方 式（超过一定百分比作vacuum)内建是20% (0.2)  
autovacuum\_analyze\_scale\_factor：  
同上， 不过是analyze的设定  
autovacuum\_freeze\_max\_age:  
最大XID出发autovacuum  
autovacuum\_vacuum\_cost\_delay:  
延 迟。。如果系统负荷其他东西，可以让vacuum慢点，保证其他东西的运行.这里是通过延迟来限制  
autovacuum\_vacuum\_cost\_limit:  
同 上，也是作限制的，这里是通过cost限制limit  
Cluster  
  
Cluster 类似于vacuum full。建议使用cluster而不是vacuum full。cluster跟vacuum full一样会重写table，移除所有的dead row。同样也是要做exclusive lock。  
Truncate  
  
Turncat 会删除一个table中的所有数据， 并且不会造成任何的dead row（delete则会造成dead row）同样的，turncate也可以用来重建table  
  
begin;  
lock foo in access exclusive mode;  
create table bar as select \* from foo;  
turncate foo;  
insert into foo (select \* from bar);  
commit;  
  
这样就重新清理了 foo这个table了。  
  
REINDEX  
  
重 新建立index  
5. 其他  
planner:  
  
statistics直接决定planner的结 果。使用planner，那么要记得确保statistics的准确（analyze）  
default\_statistics\_target:  
  
设定analyze分析的值。这个可以在 query中随时设定更改  
set default\_statistics\_target to 100;  
analyze verbose mytable;  
INFO: analyzing “aweber\_shoggoth.mytable”  
INFO: “mytable”: scanned 30000 of 1448084 pages, containing 1355449 live rows and 0 dead rows; 30000 rows in sample, 65426800 estimated total rows  
ANALYZE  
  
set default\_statistics\_target to 300;  
analyze verbose mytable;  
INFO: analyzing “aweber\_shoggoth.mytable”  
INFO: “mytable”: scanned 90000 of 1448084 pages, containing 4066431 live rows and 137 dead rows; 90000 rows in sample, 65428152 estimated total rows  
ANALYZE  
Set statistics per column 给不同的column设定不同的 statistics  
  
alter table foo alter column bar set statistics 120  
查找何时需要增加statistics  
  
跑 个query作expain analyze  
这个就会看到例如：  
\-> Seq Scan on bar (cost=0.00-52.00 rows=52 width=2 (actual time=0.007..1.894 rows=3600 loops=1)  
这里的rows应该跟真正的rows数量差不多才 是正确的。  
seq\_page\_cost  
  
planner 作sequential scan时候的cost。default是1，如果内存，cache，shared buffer设定正确。那么这个default的值太低了，可以增加  
random\_page\_cost  
  
planner 作random page fetch的值。default是4.0 如果内存，cache，shared buffer设定正确，那么这个值太高了，可以降低  
  
seq\_page\_cost跟random\_page\_cost的值可以设定成一样 的。然后测试效能，可以适当降低random\_page\_cost的值  
cpu\_operator\_cost  
  
default 是0.0025，测试为，通常设定在0.5比较好  
set cpu\_operator\_cost to 0.5;  
explain analyze select ….  
cpu\_tuple\_cost  
  
default 是0.01 测试为，通常设定在0.5比较好  
set cpu\_tuple\_cost to 0.5;  
explain analyze select …  
effective\_cache  
  
应 该跟尽可能的给到系统free能接受的大小（越大越好）  
total used free shared buffer cached  
mem: xxxx yyyyy zzz aaaa bbbb cccc  
设定的计算方法为：  
effective\_cache=cached X 50% + shared  
这里的50%可以根据服务器的繁忙程度 在40%-70%之间调整。  
监测方法：  
explain analyze ;

set effective\_cache\_size=新的值;

http://blog.sina.com.cn/s/blog\_681cd80d0100md5o.html