PostgreSQL性能调优
PostgreSQL性能调优
调整硬件配置
在大容量的数据库中，适当的硬件配置也是提高性能的一个途径。
1.存储器
目前的电脑内存是大增了，8GB也是主流了，内存增大肯定会比内存小的时候性能要高。但比如数据库是几百GB的时候，怎么也不可能只通过内存就可以解决了。数据量大的时候，高速的存储介质也是非常重要的，主要用途不同对存储介质的要求也不一样。
1)OLAP（联机分析处理）
行扫描的查询为主体的场合，大量的数据通过IO流来交换，这个时候IO控制器的性能就比较重要了。增加硬盘的场合RAID5构成有效果。
2)OLTP（联机事务处理）
随机访问为主体的场合，要求seek速度要比较快。SSD(SolidStateDrive)或者RAID1+0构成较好。而RAID5插入，更新处理比较慢。Aischool单校方案应属于OLTP，但是由于成本的原因方案中存储做的RAID5。
2.内存容量
数据只保存在内存的时候速度是很快的。如果一个查询的时候大部分数据都能保存在内存上，只有一小部分没能够保存在内存上，这个时候性能差别是相当大的。内存操作和硬盘操作之间的速度差距一般在倍以上。因此我们要求查询等处理的数据尽可能能在内存上解决，或者尽量减少硬盘操作。现在的内存也是容量越来越大了，目前8GB也不是稀罕的事情了，并且价格也不贵。因此大型数据库的时候大容量的内存配置是必须的。Aischool单校方案由于应用和数据库安装在同一机器上，内存配置在16G或以上。
3.CPU速度
电脑的一个关键性的指标就是CPU的处理速度。因此在DBMS中CPU速度快的电脑也是必要的。PostgreSQL的反应速度和CPU的处理速度一般是成正比的。CPU性能好的话，数据库的总体性能也会提高。Aischool单校方案为2颗物理CPU，每颗4核。
数据库参数调整
PostgreSQL有很多可以设置的系统参数。其中对性能影响较大的几个参数如下。
1.连接数
max_connections：最大连接数。默认是个。在大系统中个是比较少的，一般可能都比多，但是如果过大的话，内存使用过大（(+*max_locks_per_transaction)*max_connections），导致系统性能反而不高。应用程序设置合适的最小缓冲池，减少和数据库的连接开销，但是各应用程序的最小缓冲池之和要小于max_connections-superuser_reserved_connections
superuser_reserved_connections：预留给超级用户的连接数。
Aischool单校方案max_connections=0、superuser_reserved_connections=10
2.内存相关参数
shared_buffers：设置数据库服务器内存共享内存缓冲区的使用量。数据库专用服务器一般设置为物理内存的20%-40%左右。wal_buffers：WAL共享数据存储器使用的内存量。这个参数要求足够大，如果太小的话，log关联的磁盘操作过频繁，一般繁忙的系统设置为xlog文件段的大小16MB。work_mem：默认是1MB，如果发现数据经常使用临时文件排序或groupby等，可以考虑设置为一个比较大的值。按需使用，每个排序或mergeJOIN用到的哈希表，DISTINCT，都需要消耗work_mem，如
果一个执行计划中有多个此类操作则最大需要使用多个work_mem。postgres官方不建议（但是支持）在postgresql.conf文件中更改work_mem。利用explainanalyze可以检查是否有足够的work_mem，例如：在执行计划中出现了SortMethod:externalmergeDisk:kb，这说明需要从硬盘走13MB的数据，这时我们应该在会话级设置参数work_mem（SETwork_mem=14MB;）有足够的值。effective_cache_size：设置用于一个查询的有效规模的计划的假设磁盘缓存大小，参数是告诉数据库，OS的缓存大小。越大，数据库使用索引的积极性就越高。因为数据很可能在OS的缓存里，乱序读取的效率也不差。这个值理论上等于OS可以使用的缓存大小。
maintenance_work_mem：它决定数据库的维护操作使用的内存空间的大小。数据库的维护操作包括VACUUM、CREATEINDEX和ALTERTABLEADDFOREIGNKEY等操作。按需使用maintenance_work_mem设置的内存,当有并发的创建索引和autovacuum等操作时可能造成内存消耗过度，这时需要设置参数vacuum_cost_delay（VACUUM操作比较消耗IO，设置延时是指VACUUM操作消
耗的成本大于vacuum_cost_limit后延迟10毫秒再继续执行）。
3.其他参数
checkpoint_segments：多少个xlogrotate后触发checkpoint,checkpointsegments一般设置为大于shared_buffer的SIZE。如shared_buffer=MB，wal文件单个16MB，则checkpoint_segments=/16。
random_page_cost：默认4.0，调小后更倾向使用索引，而非全表扫描。
synchronous_
