postgresql优化与维护

1、1. 硬件数据库最重要的就是I/O了。所以一切从I/O 开始。RAID: 这个基本不用说，数据库放 RAID10上面，只读的备份数据库可以放 RAID0，反正挂了没关系。谨记：数据库是 Random Read注意的是硬盘外圈要比内圈快，所以跟linux建立 swap 的原理一样，尽力把数据库的东西放在硬盘的外圈 （前端）直接在 fdisk的时候就做好。 同时有个省钱方法，因为硬盘的外围速度远远快于内圈的速度，所以选择一个1 T 的 SATA硬盘，然后只用外圈的100G，其他的不用，这样的话，速度要比单买一个100G的 SAS还快。RAID卡的选择：RAID卡一定要带电池的才可以 （BBU）有电

2、源的才能做到东西写进 CACHE，RAID就返回硬盘写成功（不用等）1. Areca2. 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 (P8

3、00 Batterybacked RAID control)CPU： 64 位Cache：越大越好（现在个人电脑都3M的 cache 了）CORE：越多 越好 （postgresql 毕竟是跑 cpu 的）建议最少 4 个 core RAM： 最少 4G。通常根据具体需求，用 16-64G 的 RAM2. OS （系统）可用系统：1. Debian Stable2. CentOS3. Ubuntu LTS4. Red Hat5. SUSE Enterprise如果准备付费（服务），那么就是Canonical, Novell跟Redhat这三家选择而已如果准备不买任何服务，可以用Debian,

4、CentOS, Ubuntu LTS这里还是觉得系统用 Red Hat ( 不付费就 CentOS）毕竟人家是企业级的老大哥，错不了。* 现在 CentOS也可以买到服务了。不可用系统：例如 fedora (redhat QA) ubuntu (non-LTS)Scheduler:Grub 增加： elevator=deadlineredhat的图标可以看出， deadline是数据库的最佳选择文件系统（Filesystem）这里的选择是： ext2 ，ext3 跟 ext4 。 为什么只考虑这几个呢？因为数据库还是稳定第一，内核开发人员所做的文件系统，理论上说出问题的情况会少点。WAL： 放

5、 ext2 因为 WAL本身自己有 Journal 了，不需要用 ext3 （ ext2 快很多）data ： ext3Block Size ：postgres 自己是 8k 的 block size 。所以 文件系统也用 8k 的 block size 。这样才能最佳的提高系统的效能。ext4 ：出来时间还不够长，不考虑。分区 （Partitioning）Postgres跟系统 OS 应该在不同分区系统（ OS）：系统应该放独立的RAID1数据库 （Postgres Data ）：数据库应该放独立的 RAID10上。 如果 RAID是带电池的， mount 的时候给 data=writeba

6、ck 的选项独立的数据库分区， 就不许要记录文件时间了 （都是放数据的） 所以 mount 的时候要给 noatime 的 选项，这样可以节约更新时间(timestamp) 的 I/O 了。WAL日志（xlogs ）： 独立的 RAID1上 （ EXT2 系统）日志是 Sequentialwrite ，所以普通的硬盘（ SATA）速度就足够了，没有必要浪费SAS在 log 上Postgresql日志（logs ）：直接丢给 syslog 就可以。最好在中设定单独的文件名 . 这里 例如用 local2 来做 postgresqllocal2.* -/var/log/postgres/记得 lo

7、g 要给 Async，这样才不会等卡在 log 的 I/O 上，同时记得设定 logrotate 以及创建路径（ path ）ext2 VS ext3性能测试：HP DL5854 Dual Core 8222 processors64GB RAM(2) MSA70 direct attached storage arrays.25 spindles in each array (RAID 10) HP P800 Controller6 Disk in RAID 10 on embedded controller xlog with ext3: avg = KB/secxlog with ext

8、2: avg = KB/sec3. Postgres内存 （Memory Usage)Shared Buffer CacheWorking MemoryMaintenance MemoryShared BuffersPostgres 启动时要到的固定内存。 每个 allocation 是 8k。 Postgres 不直接做硬盘读写，而是把硬盘中的东西放入 Shared Buffers ，然后更改 Shared Buffers ，在 flush 到硬盘去。通常 Shared Buffers设定为内存（ available memory）的 25%-40%左右。在系统（ OS）中，记得设置的值（

9、/etc/决定了进程可调用的最大共享内存数量。简单的计算方法是=postgres shared_buffers + 32 MB要保留足够的空间（不然会out of memory ）postgresql除了 shared buffer还会用到一些其他的内存， 例如 max_connections,max_locks_pre_transactionWorking Memory这个是 postgres 运行作业中（ task ）需要的内存，例如内存内的hashed（ aggregates ， hash joins ）sort （order by, distinct 等等）合理的设定，可以保证 pos

10、tgres 在做这些东西的时候可以完全在内存内完成，而不需要把数据吐回到硬盘上去作 swap。但是设定太大的话，会造 成 postgres 使用的内存大于实际机器的内存，这个时候就会去硬盘swap 了。（效能下降）working memory 是 per connection and per sort的设定。所以设定一定要非常小心。举例来说，如果设定working memory 为 32MB，那么以下例子：select * from lines, lineitemswhere =and lineid=6order by baz;这里就可能用到 64MB的内存。hashjoin between

11、lines and lineitems (32MB)order by baz (32MB)要注意自己有多少query 是用到了如果同时有 100 个链接，那么就是order by或者 join100 connection X 64MB = 6400MB (6G)内存通常来说， working mem 不要给太大， 2-4MB足够在 postgres 之后的版本， working mem 可以在 query 中设定Query:begin;set work_mem to128MB ;select * from foo order by bar;insert into foo values ( re

12、set work_mem; commit;bar );Function:create function return_foo() returns setof text as$ select * from foo order by bar; $SET work_mem to 128MBLANGUAGE sql postgres 官方不建议（但是支持）在 文件中更改 work_mem然后 HUP（数据库应该没有任何中断）利用 explain analyze可以检查是否有足够的work_memsort (cost=. rows=1 width=0) (actual time=. rows=10000

13、00 loops=1)Sort Key: (generate_series(1, 1000000)Sort Method: external merge Disk:13696kb-> Result (cost=.rows=1 width=0)(actualtime=.rows=1000000 loops=1)Total runtime: ms(5 rows)以上的 query 分析显示，这里需要从硬盘走set work_mem 到 16MB才能确保性能。13MB的东西。所以这个query应给Maintenance Memory（维护内存）maintenance_work_mem 决定系统

14、作维护时可以调用的内存大小。这个也是同样可以在query 中随时设定。这个内存只有在 VACUUM, CREATE INDEX以及 REINDEX 等等系统维护指令的时候才会用到。系统维护是，调用硬盘 swap 会大大降低系统效能。通常maintenance_work_mem超过 1G的时候并没有 什么实际的效能增加 （如果内存够， 设定在 1G足以）Background Writer (bgwriter)功能：负责定时写 shared buffer cache中的 dirty shared buffers好处：a.减少系统 flush shared buffersb.在 checkpoint

15、中，不会看到I/O到硬盘（已经被bgwriter的突然性暴增，因为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自己写

16、的数据会造成一定的性能下降。如果想确定所有的数据都由bgwriter来写，可以设定这里的值为-1bgwriter_lru_multiplier:采 用计算的方式来决定多少数据应该被bgwriter来写。这里保持内置的就可以。计算 bgwriter的 I/O:1000 / bgwriter_delay * bgwriter_lru_maxpages * 8192 =(8192 是 postgres的 8k block ）实际I/O例如：1000/200 * 100 * 8192 = 4096000 = 4000 kbbgwrater可以用 pg_stat_bgwriter来监测。如果想要观察状况

17、，记得首先清理旧的stat信息。bgwrater的运行bgwriter 如果设定的太大（做太多事情）那么就会影响到前台的效能 （server ）但是如果由系统（ server ）来做 buffer flush 同样会影响效能。所以这里的最好设定就是通过观察 pg_stat_bgwriter 来找到一个最佳的平衡点。WAL （write ahead log)postgres 中的所有写动作都是首先写入 WAL，然后才执行的。 这样可以确保数据的准确跟 完整。当中途数据库崩溃的时候， postgres 可以通过 WAL恢复到崩溃前的状况而不会出现数据错误等等问题。WAL会在两种情况下被回写硬盘。1

18、. commit。 当 commit 数据的时候， WAL会被强制写回硬盘（ flush ）并且所有这个 commit 之前的东西如果在 WAL中，也会一同被 flush 。2. WAL writer 进程自己会定时回写。FSYNC vs ASYNCpostgres 的 default 是做 fsync ，也就是说 postgres 会等待数据被写入硬盘，才会给 query 返回成功的信号。如果设定 sync=no 关闭 fsync 的 话， postgres 不会等待 WAL会写硬盘，就直接返回 query 成功。通常这个会带来 15-25%的性能提升。但是缺点就是，如果系统崩溃（断电， p

19、ostgres 挂掉）的时候，你将有可能丢失最后那个 transcation. 不过这个并不会造成你系统的数据结构问题。（ no data corrupt ）如果说在系统出问 题的时候丢失 1-2 笔数据是可以接受的，那么 25%的性能提升是很可观的。WAL设定：fsync可以选择 on 或者 offwal_sync_method：linux中是使用 fdatasync 。其他的。不知道，应该是看系统的文件参数了full_page_writes:开启的时候，在 checkpoint 之后的第一次对 page 的更改， postgres 会将每 个 disk page 写入 WAL。这样可以防止

20、系统当机（断电）的时候， page 刚好只有被写一半。打开这个选项可以保证 page image 的完整性。关 闭的时候会有一定的性能增加。 尤其使用带电池的 RAID卡的时候，危险更低。这个选项属于底风险换取性能的选项，可以关闭wal_buffers:WAL的储存大小。 default 是 64 kb。 实验证明， 设定这个值在 256 kb 到 1 MB 之间会提升效能 。wal_writer_delayWAL检查 WAL数据（回写）的间隔时间。值是毫秒（milliseconds)Checkpoints确保数据回写硬盘。 dirty data page会被 flushed回硬盘。check

21、point 由以下 3 中条件激发 （bgwriter 如果设定，会帮忙在后台写入，所以就不会有 checkpoint 时候的短期高 I/O 出现）1. 到达设定的 WAL segments2. 到达设定的 timeout3.用户下达 checkpoint指令如果 checkpoint 运行频率高于 checkpint_warning 值。postgres 会在日志（log ）中记录出来，通过观察 log ，可以来决定 checkpoint_segments 的设定。增加 cehckpoint_segments 或者 checkpoint_timeout 可以有一 定的效能提升。而唯一的坏处就

22、是如果系统挂了， 在重启的时需要多一点时间来回复 （系统启动回复期间数据库是不能用的） 鉴于 postgres 很少挂掉，这个 其实可以设定的很长（ 1 天都可以）设定：checkpoint_segments最多的wal log数量，到达后会激发checkpoint，通常设定在30 就好checkpoint_timeout一般设置15-20分钟，常的可以设定1 天也没关系checkpoint_completion_target 这个保持不动就好。内建是，意思就是每个 checkpoint 预计在下个 checkpoint 完成前的一半时间内完成 （听起来有点绕嘴，呵呵）checkpoint_w

23、arning如果checkpint速度快于这个时间，在log中记录。内建是30秒理论中的完美设定，就是你的backend 从来不用回写硬盘。background 来写入的。这个就要靠调整bgwriter, checkpoints东西都是由跟 wal到一个最佳平衡状态。当然这个是理想中的完美，想真的做到。继续想吧。呵呵4. 维护 保持 postgres 的笑容维护数据库是必须的。基本维护vacuumdelete 数据的 时候，数据库只是记录这笔数据是不要的并不是真的删除数据。所以这个时候就要 vacuum了， vacuum会把标记为不要的数据清除掉。这里要注 意的是， vacuum不会清理 in

24、dex 。当数据更改超过 75%的时候，需要重新建立 index 。postgres index 可以用 cluster 重建速度快很多。在 postgres 中， vacuum=cluster ，没有任何区别了（保留 cluster 只是为了兼容旧版指令）Full Vacuum这个会做 exclusive lock 。vacuum跟 full vacuum的区别是 vacuum会把标志为不要的空间标志成可以再次使用（回收）而 full vacuum 则会把这个空间删除（返还给系统OS）所以 vacuum之后你的 postgres 在硬盘上看到的占用空间不会减少，但是第一没必要，第二fullv

25、acuum会减小硬盘占用空间。 不建议使用exclusive lock不好玩。fullvacuum，ANALYZEAnalyze 会更新统计信息（ statistics ）所有的 query 的最佳方案，以及 sql prepared statement 都是靠这统计信息而决定的。所以当数据库中的一定量数据变动后（例如超过 10%），要作 analyze ，严格的说，这个是应该常做的东 西，属于数据库正常维护的一部分。 另外一个很重要的就是，如果是 upload 数据（ restore 那种）做完之后要记得作 analyze （ restore 自动不给你作的）当 建立新的 table 的时候

26、，或者给 table 增加 index ，或者对 table 作 reindex ，或者 restore 数据进数据库，需要手动跑 analyze 才可以。 analyze 直接影响default_statistics_target数据。Autovacuum根 据 postgres 的官方资料，autovacuum 在之后才变得比较真的实用 （推出的）因为在之前， autovacuum 一次只能同 时做一个数据库中的一个 table 。 之后的版本，可以作多数据库多 table 。设定log_autovacuum_min_duration:-1 为关闭。 0 是 log 全部。 >0 就

27、是说超过这个时间的就30，那么所有超过 30ms的都会被日志记录。log下来。例如设定为autovacuum_max_workers:同 时启用的 autovacuum 进程。通常不要设定太高，3 个就可以。autovacuum_naptime:检查数据库的时间， default是 1 分钟，不用改动autovacuum_vacuum_threshold:最低 n 行记录才会引发 autovacuum 。也就是数据改变说低于这个值， autovacuum 不会运行。 default 是 50autovacuum_analyze_threshold:运 行 analyze 的最低值，跟上面的一样

28、autovacuum_vacuum_scale_factor:table中的百分比的计算方式（超过一定百分比作vacuum)内建是 20%autovacuum_analyze_scale_factor：同上，不过是 analyze 的设定autovacuum_freeze_max_age:最大 XID 出发 autovacuumautovacuum_vacuum_cost_delay:延 迟。如果系统负荷其他东西，可以让 vacuum慢点，保证其他东西的运行 . 这里是通过延迟来限制autovacuum_vacuum_cost_limit:同 上，也是作限制的，这里是通过cost 限制 lim

29、itClusterCluster类似于 vacuumfull。建议使用 cluster而不是 vacuumfull。cluster跟 vacuum full 一样会重写 table ，移除所有的 dead row 。同样也是要做exclusive lock。TruncateTurncat会删除一个 table中的所有数据，并且不会造成任何的dead row（ delete 则会造成 dead row ）同样的， turncate 也可以用来重建 table begin;lock foo in access exclusive mode;create table bar as select *

30、from foo;turncate foo;insert into foo (select * from bar);commit;这样就重新清理了foo 这个 table 了。REINDEX重 新建立 index5. 其他planner:statistics 直接决定 planner 的结 果。使用 planner ，那么要记得确保 statistics 的准确（ analyze ）default_statistics_target:设定 analyze 分析的值。这个可以在 query 中随时设定更改 set default_statistics_target to 100;analyze

31、verbose mytable;INFO: analyzing“”INFO: “mytable ”: scanned 30000 of 1448084 pages, containing 1355449 live rows and 0 dead rows; 30000 rows in sample, estimated total rowsANALYZEset default_statistics_target to 300;analyze verbose mytable;INFO: analyzing“”INFO: “mytable ”: scanned 90000 of 1448084 p

32、ages, containing 4066431 live rows and 137 dead rows; 90000 rows in sample, estimated total rowsANALYZESet statisticsper column 给不同的 column 设定不同的statisticsalter table foo alter column bar set statistics 120查找何时需要增加statistics跑 个 query 作 expain analyze这个就会看到例如：-> Seq Scan on bar (cost= rows=52 widt

33、h=2 (actual time=. rows=3600 loops=1)这里的 rows 应该跟真正的 rows 数量差不多才是正确的。seq_page_costplanner 作 sequential scan 时候的 cost 。 default 是 1，如果内存， cache， shared buffer 设定正确。那么这个 default 的值太低了，可以增加random_page_costplannerbuffer作 random page fetch的值。 default设定正确，那么这个值太高了，可以降低是 如果内存，cache， sharedseq_page_cost 跟 r

34、andom_page_cost 的值可以设定成一样 的。然后测试效能，可以适当降低 random_page_cost 的值cpu_operator_costdefault是，测试为，通常设定在比较好set cpu_operator_cost to ;explain analyze select.cpu_tuple_costdefault是 测试为，通常设定在比较好set cpu_tuple_cost to ;explain analyze selecteffective_cache应 该跟尽可能的给到系统free 能接受的大小（越大越好）total used free shared buffer cachedmem: xxxx yyyyy zzz aaaa bbbb cccc设定的计算方法为：effective_cache=cached X 50% + shared这里的 50%可以根据服务器的繁忙程度在 40%-70%之间调整。监测方法：explain analyze ;set effective_cache_size=新的值 ;explain analyze ;reset effective_cache_size;尝试出一个最适合的值，就可以改文件设定成固定了。Natural vs Primary Ke