PostgreSQL 参数调整(性能优化)
昨天分别在外网和无外网环境下安装PostgreSQL,有外网环境下安装的相当顺利。但是在无外网环境下就是两个不同的概念了,可谓十有八折。感兴趣的同学可以搭建一下。
PostgreSQL安装完成后第一件事便是做相关测试,然后调整参数。
/*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
了解到系统情况后便可做相关合理的调整,以达到性能优化的目的。
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(预写日志)记录写入缓冲区,然后将这些缓冲区刷新到磁盘。由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的值:
postgres=# show checkpoint_timeout; checkpoint_timeout -------------------- 5min (1 row) postgres=# show checkpoint_completion_target; checkpoint_completion_target ------------------------------ 0.5 (1 row)
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,提高性能
注意:
并非所有参数都适用于所有应用程序类型。某些应用程序通过调整参数可以提高性能,有些则不会。必须针对应用程序及操作系统的特定需求来调整数据库参数。
下面介绍几个我认为重要的:
PostgreSQL配置参数修改
1.修改配置文件
在配置文件C:\PostgreSQL\data\pg96\postgresql.conf 中直接修改,修改前记得备份一下原文件,因为你不知道意外和明天不知道哪个会先来。修改完成之后,记得重启数据库哦。
2.命令行的修改方式
ALTER SYSTEM SET configuration_parameter { TO | = } { value | 'value' | DEFAULT }
例如:我们现在要修改 maintenance_work_mem
--查看所有数据库参数的值 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;
数据库参数优化总结
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 , 垃圾回收不妥协