PostgreSQL 参数调整(性能优化)

一时失言乱红尘 2023-03-06 05:22 38阅读 0赞

昨天分别在外网和无外网环境下安装PostgreSQL，有外网环境下安装的相当顺利。但是在无外网环境下就是两个不同的概念了，可谓十有八折。感兴趣的同学可以搭建一下。

PostgreSQL安装完成后第一件事便是做相关测试，然后调整参数。

![复制代码][aHR0cHM6Ly9jb21tb24uY25ibG9ncy5jb20vaW1hZ2VzL2NvcHljb2RlLmdpZg]

/*CPU
    查看CPU型号*/
    cat /proc/cpuinfo | grep name | cut -f2 -d: | uniq -c
    
    /*查看物理CPU个数*/
    cat /proc/cpuinfo | grep "physical id" | sort -u | wc -l  
    
    /*查看逻辑CPU个数*/
    cat /proc/cpuinfo | grep "processor" | wc -l  
    
    /*查看CPU内核数*/
    cat /proc/cpuinfo | grep "cpu cores" | uniq  
    
    /*查看单个物理CPU封装的逻辑CPU数量*/
    cat /proc/cpuinfo | grep "siblings" | uniq  
    
    /*计算是否开启超线程
    ##逻辑CPU > 物理CPU x CPU核数 #开启超线程
    ##逻辑CPU = 物理CPU x CPU核数 #没有开启超线程或不支持超线程*/
    
    /*查看是否超线程,如果cpu cores数量和siblings数量一致，则没有启用超线程，否则超线程被启用。*/
    cat /proc/cpuinfo | grep -e "cpu cores"  -e "siblings" | sort | uniq
    
    
    /*内存
    TOP
    /*命令经常用来监控linux的系统状况，比如cpu、内存的使用等。*/
    /*查看某个用户内存使用情况,如:postgres*/
    top -u postgres
    /*
    内容解释：
    
    　　PID：进程的ID
    　　USER：进程所有者
    　　PR：进程的优先级别，越小越优先被执行
    　　NInice：值
    　　VIRT：进程占用的虚拟内存
    　　RES：进程占用的物理内存
    　　SHR：进程使用的共享内存
    　　S：进程的状态。S表示休眠，R表示正在运行，Z表示僵死状态，N表示该进程优先值为负数
    　　%CPU：进程占用CPU的使用率
    　　%MEM：进程使用的物理内存和总内存的百分比
    　　TIME+：该进程启动后占用的总的CPU时间，即占用CPU使用时间的累加值。
    　　COMMAND：进程启动命令名称
    
    常用的命令：
    
    　　P：按%CPU使用率排行
    　　T：按MITE+排行
    　　M：按%MEM排行
    */
    
    /*查看进程相关信息占用的内存情况，(进程号可以通过ps查看)如下所示：*/
    pmap -d 14596
    
    ps -e -o 'pid,comm,args,pcpu,rsz,vsz,stime,user,uid' 
    ps -e -o 'pid,comm,args,pcpu,rsz,vsz,stime,user,uid' | grep postgres |  sort -nrk5
    /*其中rsz为实际内存，上例实现按内存排序，由大到小*/
    
    /*看内存占用*/
    free -m
    
    /*看硬盘占用率*/
    df -h
    /*查看IO情况*/
    iostat -x 1 10
    /*
    如果 iostat 没有，要 yum install sysstat安装这个包，第一眼看下图红色圈圈的那个如果%util接近100%,表明I/O请求太多,I/O系统已经满负荷，磁盘可能存在瓶颈,一般%util大于70%,I/O压力就比较大，读取速度有较多的wait，然后再看其他的参数，
    内容解释:
    rrqm/s:每秒进行merge的读操作数目。即delta(rmerge)/s 
    wrqm/s:每秒进行merge的写操作数目。即delta(wmerge)/s 
    r/s:每秒完成的读I/O设备次数。即delta(rio)/s 
    w/s:每秒完成的写I/0设备次数。即delta(wio)/s 
    rsec/s:每秒读扇区数。即delta(rsect)/s 
    wsec/s:每秒写扇区数。即delta(wsect)/s 
    rKB/s:每秒读K字节数。是rsec/s的一半，因为每扇区大小为512字节 
    
    wKB/s:每秒写K字节数。是wsec/s的一半 
    avgrq-sz:平均每次设备I/O操作的数据大小(扇区)。即delta(rsect+wsect)/delta(rio+wio) 
    avgqu-sz:平均I/O队列长度。即delta(aveq)/s/1000(因为aveq的单位为毫秒) 
    await:平均每次设备I/O操作的等待时间(毫秒)。即delta(ruse+wuse)/delta(rio+wio) 
    svctm:平均每次设备I/O操作的服务时间(毫秒)。即delta(use)/delta(rio+wio) 
    %util:一秒中有百分之多少的时间用于I/O操作,或者说一秒中有多少时间I/O队列是非空的
    
    */
    /*找到对应进程*/
    ll /proc/进程号/exe

![复制代码][aHR0cHM6Ly9jb21tb24uY25ibG9ncy5jb20vaW1hZ2VzL2NvcHljb2RlLmdpZg]

了解到系统情况后便可做相关合理的调整，以达到性能优化的目的。

**1.shared\_buffers**  
PostgreSQL既使用自身的缓冲区，也使用内核缓冲IO。这意味着数据会在内存中存储两次，首先是存入PostgreSQL缓冲区，然后是内核缓冲区。这被称为双重缓冲区处理。对大多数操作系统来说，这个参数是最有效的用于调优的参数。此参数的作用是设置PostgreSQL中用于缓存的专用内存量。  
shared\_buffers的默认值设置得非常低，因为某些机器和操作系统不支持使用更高的值。但在大多数现代设备中，通常需要增大此参数的值才能获得最佳性能。  
建议的设置值为机器总内存大小的25％，但是也可以根据实际情况尝试设置更低和更高的值。实际值取决于机器的具体配置和工作的数据量大小。举个例子，如果工作数据集可以很容易地放入内存中，那么可以增加shared\_buffers的值来包含整个数据库，以便整个工作数据集可以保留在缓存中。  
在生产环境中，将shared\_buffers设置为较大的值通常可以提供非常好的性能，但应当时刻注意找到平衡点。  
查看当前shared\_buffers的值：

postgres=# show shared_buffers;
     shared_buffers 
    ----------------
     128MB
    (1 row)

**2.wal\_buffers**

PostgreSQL将其WAL（[预写日志][Link 1]）记录写入缓冲区，然后将这些缓冲区刷新到磁盘。由wal\_buffers定义的缓冲区的默认大小为16MB，但如果有大量并发连接的话，则设置为一个较高的值可以提供更好的性能。  
查看当前wal\_buffers的值：

postgres=# show wal_buffers;
     wal_buffers 
    -------------
     4MB
    (1 row)

**3.effective\_cache\_size**

effective\_cache\_size提供可用于磁盘高速缓存的内存量的估计值。它只是一个建议值，而不是确切分配的内存或缓存大小。它不会实际分配内存，而是会告知优化器内核中可用的缓存量。在一个索引的代价估计中，更高的数值会使得索引扫描更可能被使用，更低的数值会使得顺序扫描更可能被使用。在设置这个参数时，还应该考虑PostgreSQL的共享缓冲区以及将被用于PostgreSQL数据文件的内核磁盘缓冲区。默认值是4GB。  
查看当前effective\_cache\_size的值：

postgres=# show effective_cache_size;
     effective_cache_size 
    ----------------------
     4GB
    (1 row)

**4.work\_mem**

此配置用于复合排序。内存中的排序比溢出到磁盘的排序快得多，设置非常高的值可能会导致部署环境出现内存瓶颈，因为此参数是按用户排序操作。如果有多个用户尝试执行排序操作，则系统将为所有用户分配大小为work\_mem \*总排序操作数的空间。全局设置此参数可能会导致内存使用率过高，因此强烈建议在会话级别修改此参数值。默认值为4MB。  
查看当前work\_mem的值：

postgres=# show work_mem;
     work_mem 
    ----------
     4MB
    (1 row)

**5.maintenance\_work\_mem**  
maintenance\_work\_mem是用于维护任务的内存设置。默认值为64MB。设置较大的值对于VACUUM，RESTORE，CREATE INDEX，ADD FOREIGN KEY和ALTER TABLE等操作的性能提升效果显著。  
查看当前maintenance\_work\_mem的值：

postgres=# show maintenance_work_mem;
     maintenance_work_mem 
    ----------------------
     64MB
    (1 row)

**6.synchronous\_commit**

此参数的作用为在向客户端返回成功状态之前，强制提交等待WAL被写入磁盘。这是性能和可靠性之间的权衡。如果应用程序被设计为性能比可靠性更重要，那么关闭synchronous\_commit。这意味着成功状态与保证写入磁盘之间会存在时间差。在服务器崩溃的情况下，即使客户端在提交时收到成功消息，数据也可能丢失。  
查看当前synchronous\_commit的设置值：

postgres=# show synchronous_commit;
     synchronous_commit 
    --------------------
     on
    (1 row)

**7.checkpoint\_timeout和checkpoint\_completion\_target**  
PostgreSQL将更改写入WAL。检查点进程将数据刷新到数据文件中。发生CHECKPOINT时完成此操作。这是一项开销很大的操作，整个过程涉及大量的磁盘读/写操作。用户可以在需要时随时发出CHECKPOINT指令，或者通过PostgreSQL的参数checkpoint\_timeout和checkpoint\_completion\_target来自动完成。  
checkpoint\_timeout参数用于设置WAL检查点之间的时间。将此设置得太低会减少崩溃恢复时间，因为更多数据会写入磁盘，但由于每个检查点都会占用系统资源，因此也会损害性能。此参数只能在postgresql.conf文件中或在服务器命令行上设置。  
checkpoint\_completion\_target指定检查点完成的目标，作为检查点之间总时间的一部分。默认值是 0.5。 这个参数只能在postgresql.conf文件中或在服务器命令行上设置。高频率的检查点可能会影响性能。  
查看当前checkpoint\_timeout和checkpoint\_completion\_target的值：

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 8 9 10 11 </td> 
 <td> <code>postgres=# show checkpoint_timeout;</code> <code>&nbsp;</code><code>checkpoint_timeout</code> <code>--------------------</code> <code>&nbsp;</code><code>5min</code> <code>(1 row)</code> &nbsp; <code>postgres=# show checkpoint_completion_target;</code> <code>&nbsp;</code><code>checkpoint_completion_target</code> <code>------------------------------</code> <code>&nbsp;</code><code>0.5</code> <code>(1 row)</code> </td> 
 </tr> 
 </tbody> 
</table>

**8.max\_connections**

允许客户端连接的最大数目

**9.fsync**

强制把数据同步更新到磁盘,如果系统的IO压力很大，把改参数改为off

在fsync打开的情况下，优化后性能能够提升30%左右。因为有部分优化选项在默认的SQL测试语句中没有体现出它的优势，如果到实际测试中，提升应该不止30%。  
测试的过程中，主要的瓶颈就在系统的IO，如果需要减少IO的负荷，最直接的方法就是把fsync关闭，但是这样就会在掉电的情况下，可能会丢失部分数据。

**10.commit\_delay**

事务提交后，日志写到wal log上到wal\_buffer写入到磁盘的时间间隔。需要配合commit\_sibling。能够一次写入多个事务，减少IO，提高性能

**11.commit\_siblings**

设置触发commit\_delay的并发事务数，根据并发事务多少来配置。减少IO，提高性能

注意：  
并非所有参数都适用于所有应用程序类型。某些应用程序通过调整参数可以提高性能，有些则不会。必须针对应用程序及操作系统的特定需求来调整数据库参数。

** 下面介绍几个我认为重要的：**

1、增加maintenance\_work\_mem参数大小

增加这个参数可以提升CREATE INDEX和ALTER TABLE ADD FOREIGN KEY的执行效率。

2、增加checkpoint\_segments参数的大小

增加这个参数可以提升大量数据导入时候的速度。

3、设置archive\_mode无效

这个参数设置为无效的时候，能够提升以下的操作的速度

・CREATE TABLE AS SELECT

・CREATE INDEX

・ALTER TABLE SET TABLESPACE

・CLUSTER等。

4、autovacuum相关参数 ([autovacuum介绍文章][autovacuum])

**autovacuum**：默认为on，表示是否开起autovacuum。默认开起。特别的，当需要冻结xid时，尽管此值为off，PG也会进行vacuum。

**autovacuum\_naptime**：下一次vacuum的时间，默认1min。 这个naptime会被vacuum launcher分配到每个DB上。autovacuum\_naptime/num of db。

log\_autovacuum\_min\_duration：记录autovacuum动作到日志文件，当vacuum动作超过此值时。 “-1”表示不记录。“0”表示每次都记录。

**autovacuum\_max\_workers**：最大同时运行的worker数量，不包含launcher本身。

**autovacuum\_work\_mem**    ：每个worker可使用的最大内存数。

**autovacuum\_vacuum\_threshold**    ：默认50。与autovacuum\_vacuum\_scale\_factor配合使用， autovacuum\_vacuum\_scale\_factor默认值为20%。当update,delete的tuples数量超过autovacuum\_vacuum\_scale\_factor\*table\_size+autovacuum\_vacuum\_threshold时，进行vacuum。如果要使vacuum工作勤奋点，则将此值改小。

**autovacuum\_analyze\_threshold**        ：默认50。与autovacuum\_analyze\_scale\_factor配合使用。

**autovacuum\_analyze\_scale\_factor**    **：**默认10%。当update,insert,delete的tuples数量超过autovacuum\_analyze\_scale\_factor\*table\_size+autovacuum\_analyze\_threshold时，进行analyze。

**autovacuum\_freeze\_max\_age**：200 million。离下一次进行xid冻结的最大事务数。

**autovacuum\_multixact\_freeze\_max\_age**：400 million。离下一次进行xid冻结的最大事务数。

**autovacuum\_vacuum\_cost\_delay**    ：如果为-1，取vacuum\_cost\_delay值。

**autovacuum\_vacuum\_cost\_limit**       ：如果为-1，到vacuum\_cost\_limit的值，这个值是所有worker的累加值。

### PostgreSQL配置参数修改 ###

1.修改配置文件

在配置文件C:\\PostgreSQL\\data\\pg96\\postgresql.conf 中直接修改，修改前记得备份一下原文件，因为你不知道意外和明天不知道哪个会先来。修改完成之后，记得重启数据库哦。

2.命令行的修改方式

ALTER SYSTEM SET configuration_parameter { TO | = } { value | 'value' | DEFAULT }

例如：我们现在要修改 maintenance\_work\_mem

![复制代码][aHR0cHM6Ly9jb21tb24uY25ibG9ncy5jb20vaW1hZ2VzL2NvcHljb2RlLmdpZg]

--查看所有数据库参数的值
    show all;
    
    
    show maintenance_work_mem;
    --注意这里的设置不会改变postgresql.conf,只会改变postgresql.conf
    ALTER SYSTEM SET maintenance_work_mem= 1048576;
    
    --重启数据库
    show maintenance_work_mem; 
    
    --取消postgresql.auto.conf的参数设置
    ALTER SYSTEM SET maintenance_work_mem= default;

![复制代码][aHR0cHM6Ly9jb21tb24uY25ibG9ncy5jb20vaW1hZ2VzL2NvcHljb2RlLmdpZg]

### **数据库参数优化总结** ###

![复制代码][aHR0cHM6Ly9jb21tb24uY25ibG9ncy5jb20vaW1hZ2VzL2NvcHljb2RlLmdpZg]

max_connections = 300       # (change requires restart)
    unix_socket_directories = '.'   # comma-separated list of directories
    shared_buffers = 194GB       # 尽量用数据库管理内存，减少双重缓存，提高使用效率
    huge_pages = on           # on, off, or try  ，使用大页
    work_mem = 256MB # min 64kB  ， 减少外部文件排序的可能，提高效率
    maintenance_work_mem = 2GB  # min 1MB  ， 加速建立索引
    autovacuum_work_mem = 2GB   # min 1MB, or -1 to use maintenance_work_mem  ， 加速垃圾回收
    dynamic_shared_memory_type = mmap      # the default is the first option
    vacuum_cost_delay = 0      # 0-100 milliseconds   ， 垃圾回收不妥协，极限压力下，减少膨胀可能性
    bgwriter_delay = 10ms       # 10-10000ms between rounds    ， 刷shared buffer脏页的进程调度间隔，尽量高频调度，减少用户进程申请不到内存而需要主动刷脏页的可能（导致RT升高）。
    bgwriter_lru_maxpages = 1000   # 0-1000 max buffers written/round ,  一次最多刷多少脏页
    bgwriter_lru_multiplier = 10.0          # 0-10.0 multipler on buffers scanned/round  一次扫描多少个块，上次刷出脏页数量的倍数
    effective_io_concurrency = 2           # 1-1000; 0 disables prefetching ， 执行节点为bitmap heap scan时，预读的块数。从而
    wal_level = minimal         # minimal, archive, hot_standby, or logical ， 如果现实环境，建议开启归档。
    synchronous_commit = off    # synchronization level;    ， 异步提交
    wal_sync_method = open_sync    # the default is the first option  ， 因为没有standby，所以写xlog选择一个支持O_DIRECT的fsync方法。
    full_page_writes = off      # recover from partial page writes  ， 生产中，如果有增量备份和归档，可以关闭，提高性能。
    wal_buffers = 1GB           # min 32kB, -1 sets based on shared_buffers  ，wal buffer大小，如果大量写wal buffer等待，则可以加大。
    wal_writer_delay = 10ms         # 1-10000 milliseconds  wal buffer调度间隔，和bg writer delay类似。
    commit_delay = 20           # range 0-100000, in microseconds  ，分组提交的等待时间
    commit_siblings = 9        # range 1-1000  , 有多少个事务同时进入提交阶段时，就触发分组提交。
    checkpoint_timeout = 55min  # range 30s-1h  时间控制的检查点间隔。
    max_wal_size = 320GB    #   2个检查点之间最多允许产生多少个XLOG文件
    checkpoint_completion_target = 0.99     # checkpoint target duration, 0.0 - 1.0  ，平滑调度间隔，假设上一个检查点到现在这个检查点之间产生了100个XLOG，则这次检查点需要在产生100*checkpoint_completion_target个XLOG文件的过程中完成。PG会根据这些值来调度平滑检查点。
    random_page_cost = 1.0     # same scale as above  , 离散扫描的成本因子，本例使用的SSD IO能力足够好
    effective_cache_size = 240GB  # 可用的OS CACHE
    log_destination = 'csvlog'  # Valid values are combinations of
    logging_collector = on          # Enable capturing of stderr and csvlog
    log_truncate_on_rotation = on           # If on, an existing log file with the
    update_process_title = off
    track_activities = off
    autovacuum = on    # Enable autovacuum subprocess?  'on'
    autovacuum_max_workers = 4 # max number of autovacuum subprocesses    ，允许同时有多少个垃圾回收工作进程。
    autovacuum_naptime = 6s  # time between autovacuum runs   ， 自动垃圾回收探测进程的唤醒间隔
    autovacuum_vacuum_cost_delay = 0    # default vacuum cost delay for  ， 垃圾回收不妥协

![复制代码][aHR0cHM6Ly9jb21tb24uY25ibG9ncy5jb20vaW1hZ2VzL2NvcHljb2RlLmdpZg]

[aHR0cHM6Ly9jb21tb24uY25ibG9ncy5jb20vaW1hZ2VzL2NvcHljb2RlLmdpZg]: /images/20230208/94b2cc520e204c27bc315362f6cc3cc3.png
[Link 1]: https://www.cnblogs.com/VicLiu/p/11854782.html
[autovacuum]: https://www.cnblogs.com/VicLiu/p/12011410.html