PostgresSQL-内存分配-白红宇

PostgresSQL-内存分配

阅读量：5759 次

发布时间：2019-06-18

本文共 4701 字，大约阅读时间需要 15 分钟。

postgresql的内存分配主要由shared_buffers、temp_buffers、work_mem、maintenance_work_mem参数控制。

shared_buffers又可以叫做共享缓冲区，postgresql对数据操作时都要先将数据从磁盘读取到内存中，然后进行更新，最后再将数据写回磁盘。shared_buffers的功能就是用于存放从磁盘读取的数据。根据文档参数的设置范围一般在25%~40%之间。windows与linux对内存的管理方式不同，在linux中需要注意共享段大小的设置(kernel.shmmax)。

temp_buffers称之为临时缓冲区，用于数据库会话访问临时表数据，系统默认值为8M。可以在单独的session中对该参数进行设置，尤其是需要访问比较大的临时表时，将会有显著的性能提升。

work_mem可以称之为工作内存或者操作内存。其负责内部的sort和hash操作，合适的work_mem大小能够保证这些操作在内存中进行。定义太小的话，sort或者hash操作将需要与硬盘进行swap，这样会极大的降低系统的性能；太大的话致使在能够在内存中完成的操作数量减少，其他的部分需要与磁盘进行swap操作，增加IO降低性能。系统提供的默认值是1M，在实际的生产环境中，要对系统监控数据进行分析，作出最好的选择。

大致有两种方式：估计方法与计算方法。第一种是可以根据业务量的大小和类型，一般语句运行时间，来粗略的估计一下。第二种方式是通过对数据库的监控，数据采集，然后计算其大小。总之合适的大小对系统的性能至关重要。

在实际的维护中可以通过explain analyze分析语句的work_mem大小是否合适。在语句中设置work_mem参数的大小可以充分利用内存，提高语句的执行效率。

对于work_mem内存分配时还要考虑数据库的并发情况，max_connections决定了系统的最大的并发连接数。不论如何调整work_mem都要考虑max_connections*work_mem+shared_buffers+temp_buffers+maintenance_work_mem+操作系统所需内存不能够超过整个的RAM大小，这是非常重要的。

work_mem参数对系统的性能是如此的重要，让其实时的适应数据库的运行状况显的不太可能，但是可以通过对数据库运行周期的监控，总结相应的数据，然后定制一个专用的脚本，专门用来修改work_mem的大小，使其阶段性的更加适应系统的状况，不失为一种好的方法。

maintenance_work_mem称之为维护工作内存，主要是针对数据库的维护操作或者语句。尽量的将这些操作在内存中进行。主要针对VACUUM，CREATE INDEX，REINDEX等操作。在对整个数据库进行VACUUM或者较大的index进行重建时，适当的调整该参数非常必要。

postresql文档提示在启用了autoacuum功能的情况下，该参数不能配置的过大。

监听IPv4的所有IP.

listen_addresses = '0.0.0.0'

最大允许1000个连接.

max_connections = 1000

为超级用户保留3个可用连接.

superuser_reserved_connections = 3

默认的unix socket文件放在/tmp, 修改为$PGDATA, 以确保安全.

unix_socket_directory = '/pgdata/digoal/1921/data02/pg_root'

默认的访问权限是0777, 修改为0700更安全.

unix_socket_permissions = 0700

Linux下面默认是2小时.tcp的keepalives包发送间隔以及重试次数, 如果客户端没有响应, 将主动释放对应的SOCKET.

tcp_keepalives_idle = 60

tcp_keepalives_interval = 10

tcp_keepalives_count = 6

大的shared_buffers需要大的checkpoint_segments,同时需要申请更多的System V共享内存资源.

这个值不需要设的太大, 因为PostgreSQL还依赖操作系统的cache来提高读性能, 另外, 写操作频繁的数据库这个设太大反而会增加checkpoint压力.

shared_buffers = 512MB

这个值越大, VACUUM, CREATE INDEX的操作越快, 当然大到一定程度瓶颈就不在内存了, 可能是CPU例如创建索引.

这个值是一个操作的内存使用上限, 而不是一次性分配出去的. 并且需要注意如果开启了autovacuum, 最大可能有autovacuum_max_workers*maintenance_work_mem的内存被系统消耗掉.

maintenance_work_mem = 512MB

一般设置为比系统限制的略少,ulimit -a : stack size (kbytes, -s) 10240

max_stack_depth = 8MB

手动执行vacuum操作时, 默认是没有停顿执行到底的, 为了防止VACUUM操作消耗太多数据库服务器硬件资源, 这个值是指vacuum在消耗多少资源后停顿多少时间,以便其他的操作可以使用更多的硬件资源.

vacuum_cost_delay = 10ms

#vacuum_cost_page_hit = 1 # 0-10000 credits

#vacuum_cost_page_miss = 10 # 0-10000 credits

#vacuum_cost_page_dirty = 20 # 0-10000 credits

#vacuum_cost_limit = 200 # 1-10000 credits

默认bgwriter进程执行一次后会停顿200ms再被唤醒执行下一次操作, 当数据库的写操作很频繁的时候, 200ms可能太长, 导致其他进程需要花费过多的时间来进行bgwriter的操作.

bgwriter_delay = 10ms

如果需要做数据库WAL日志备份的话至少需要设置成archive级别, 如果需要做hot_standby那么需要设置成hot_standby, 由于这个值修改需要重启数据库, 所以先设置成hot_standby比较好. 当然hot_standby意味着WAL记录得更详细, 如果没有打算做hot_standby设置得越低性能越好.

wal_level = hot_standby

wal buffers默认是-1 根据shared_buffers的设置自动调整shared_buffers*3% .最大限制是XLOG的segment_size.

wal_buffers = 16384kB

多少个xlog file产生后开始checkpoint操作, 这个值越大, 允许shared_buffer中的被频繁访问的脏数据存储得更久. 一定程度上可以提高数据库性能. 但是太大的话会导致在数据库发生checkpoint的时候需要处理更多的脏数据带来长时间的IO开销. 太小的话会导致产生更多的WAL文件(因为full page writes=on,CHECKPOINT后的第一次块的改变要写全块, checkpoint越频繁, 越多的数据更新要写全块导致产生更多WAL).