前面介绍了 PostgreSQL基于 Patroni 高可用架构部署及故障切换、 基于 repmgr 高可用架构实践、基于 pgpool 实现读写分离实践、数据库备份与恢复、主从数据目录同步工具 pg_rewind、数据库作业调度工具等相关的知识点,今天我将详细的为大家介绍PostgreSQL 性能优化相关知识,希望大家能够从中收获多多!如有帮助,请点在看、转发支持一波!!!
性能优化
优化简介
PostgreSQL优化一方面是找出系统的瓶颈,提高PostgreSQL数据库整体的性能;
另一方面,需要合理的结构设计和参数调整,以提高用户操作响应的速度;
同时还要尽可能的节省系统资源,以便系统可以提供更大负荷的服务。
PostgreSQL 数据库优化是多方面的,原则是减少系统的瓶颈,减少资源的占用,增加系统的反应速度。例如:
通过优化文件系统,提高磁盘IO的读写速度;
通过优化操作系统调度策略,提高 PostgreSQL 的在高负荷情况下负载能力;
优化表结构、索引、查询语句等使查询响应更快。
首先了解系统情况后便可做相关合理的调整,以达到性能优化的目的。
/*CPU查看CPU型号*/cat/proc/cpuinfo|grepname|cut-f2-d:|uniq-c/*查看物理CPU个数*/cat/proc/cpuinfo|grep"physicalid"|sort-u|wc-l/*查看逻辑CPU个数*/cat/proc/cpuinfo|grep"processor"|wc-l/*查看CPU内核数*/cat/proc/cpuinfo|grep"cpucores"|uniq/*查看单个物理CPU封装的逻辑CPU数量*/cat/proc/cpuinfo|grep"siblings"|uniq/*计算是否开启超线程##逻辑CPU>物理CPUxCPU核数#开启超线程##逻辑CPU=物理CPUxCPU核数#没有开启超线程或不支持超线程*//*查看是否超线程,如果cpu cores数量和siblings数量一致,则没有启用超线程,否则超线程被启用。*/cat/proc/cpuinfo|grep-e"cpucores"-e"siblings"|sort|uniq/*内存TOP/*命令经常用来监控linux的系统状况,比如cpu、内存的使用等。*//*查看某个用户内存使用情况,如:postgres*/top-upostgres/*内容解释: 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-d14596ps-e-o'pid,comm,args,pcpu,rsz,vsz,stime,user,uid'ps-e-o'pid,comm,args,pcpu,rsz,vsz,stime,user,uid'|greppostgres|sort-nrk5/*其中rsz为实际内存,上例实现按内存排序,由大到小*//*看内存占用*/free-m/*看硬盘占用率*/df-h/*查看IO情况*/iostat-x110/*如果iostat没有,要yuminstallsysstat安装这个包,第一眼看下图红色圈圈的那个如果%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
更多关于 PostgreSQL 系列的学习文章,请参阅:PostgreSQL 数据库,本系列持续更新中。
优化查询
分析查询语句EXPLAIN
使用 EXPLAIN 语句来分析一个查询语句,执行如下语句:
EXPLAINANALYZESELECT*FROMfruits;
索引对查询速度的影响
下面是查询语句中不使用索引和使用索引的对比。首先,分析未使用索引时的查询情况,EXPLAIN 语句执行如下:
EXPLAINSELECT*FROMfruitsWHEREf_name='apple';
然后,在fruits表的f_name字段上加上索引。执行添加索引的语句及结果如下:
CREATEINDEXindex_nameONfruits(f_name);
现在,再分析上面的查询语句。执行的 EXPLAIN 语句及结果如下:
EXPLAINANALYZESELECT*FROMfruitsWHEREf_name='apple';
优化子查询
子查询可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作。子查询虽然可以使查询语句很灵活,但执行效率不高。执行子查询时,PostgreSQL需要为内层查询语句的查询结果建立一个临时表。然后外层查询语句从临时表中查询记 录。查询完毕后,再撤销这些临时表。因此,子查询的速度会受到一定的影响。如果查询的数据量比较大,这种影响就会随之增大。
在 PostgreSQL 中可以使用连接(JOIN)查询来替代子查询。连接查询不需要建立临时表,其速度比子查询要快,如果查询中使用到索引的话,性能会更好。连接之所以更有效率,是因为PostgreSQL不需要在内存中创建临时表来完成查询工作。
优化数据库结构
将字段很多的表分解成多个表
增加中间表
增加冗余字段
设计数据库表时尽量遵循范式理论的规约,尽可能少的冗余字段,让数据库设计看起来精致、优雅。但是,合理的加入冗余字段可以提高查询速度。
优化插入记录的速度
删除索引
使用批量插入
删除外键约束
禁止自动提交
使用 COPY 批量导入
分析表的统计信息
PostgreSQL中提供了 ANALYZE 语句收集表内容的统计信息,然后把结果保存在系统表 pg_statistic 里。
使用 ANALYZE 来分析fruits表,执行的语句:
ANALYZEVERBOSEfruits;
更多关于 PostgreSQL 系列的学习文章,请参阅:PostgreSQL 数据库,本系列持续更新中。
优化PostgreSQL服务器
优化服务器硬件
配置较大的内存。足够大的内存,是提高PostgreSQL数据库性能的方法之一。内存的速度比磁盘I/0快得多,可以通过增加系统的缓冲区容量,使数据在内存中停留的时间更 长,以减少磁盘I/0。
配置高速磁盘系统,以减少读盘的等待时间,提高响应速度。
合理分布磁盘I/O,把磁盘I/O分散在多个设备上,以减少资源竞争,提高并行操作能力。
配置多处理器,PostgreSQL是多线程的数据库,多处理器可同时执行多个线程。
PostgreSQL 系统参数
shared_buffers
PostgreSQL既使用自身的缓冲区,也使用内核缓冲IO。这意味着数据会在内存中存储两次,首先是存入PostgreSQL缓冲区,然后是内核缓冲区。这被称为双重缓冲区处理。对大多数操作系统来说,这个参数是最有效的用于调优的参数。此参数的作用是设置PostgreSQL中用于缓存的专用内存量。
shared_buffers 的默认值设置得非常低,因为某些机器和操作系统不支持使用更高的值。但在大多数现代设备中,通常需要增大此参数的值才能获得最佳性能。
建议的设置值为机器总内存大小的25%,但是也可以根据实际情况尝试设置更低和更高的值。实际值取决于机器的具体配置和工作的数据量大小。举个例子,如果工作数据集可以很容易地放入内存中,那么可以增加shared_buffers的值来包含整个数据库,以便整个工作数据集可以保留在缓存中。
在生产环境中,将shared_buffers设置为较大的值通常可以提供非常好的性能,但应当时刻注意找到平衡点。
查看当前shared_buffers的值:
postgres=#showshared_buffers;shared_buffers----------------128MB(1row)
wal_buffers
PostgreSQL将其WAL(预写日志)记录写入缓冲区,然后将这些缓冲区刷新到磁盘。由wal_buffers定义的缓冲区的默认大小为16MB,但如果有大量并发连接的话,则设置为一个较高的值可以提供更好的性能。
查看当前wal_buffers的值:
postgres=#showwal_buffers;wal_buffers-------------4MB(1row)
effective_cache_size
effective_cache_size提供可用于磁盘高速缓存的内存量的估计值。它只是一个建议值,而不是确切分配的内存或缓存大小。它不会实际分配内存,而是会告知优化器内核中可用的缓存量。在一个索引的代价估计中,更高的数值会使得索引扫描更可能被使用,更低的数值会使得顺序扫描更可能被使用。在设置这个参数时,还应该考虑PostgreSQL的共享缓冲区以及将被用于PostgreSQL数据文件的内核磁盘缓冲区。默认值是4GB。
查看当前effective_cache_size的值:
postgres=#showeffective_cache_size;effective_cache_size----------------------4GB(1row)
work_mem
此配置用于复合排序。内存中的排序比溢出到磁盘的排序快得多,设置非常高的值可能会导致部署环境出现内存瓶颈,因为此参数是按用户排序操作。如果有多个用户尝试执行排序操作,则系统将为所有用户分配大小为work_mem *
总排序操作数的空间。全局设置此参数可能会导致内存使用率过高,因此强烈建议在会话级别修改此参数值。默认值为4MB。
查看当前work_mem的值:
postgres=#showwork_mem;work_mem----------4MB(1row)
maintenance_work_mem
maintenance_work_mem
是用于维护任务的内存设置。默认值为64MB。设置较大的值对于VACUUM,RESTORE,CREATE INDEX,ADD FOREIGN KEY和ALTER TABLE等操作的性能提升效果显著。
查看当前maintenance_work_mem的值:
postgres=#showmaintenance_work_mem;maintenance_work_mem----------------------64MB(1row)
synchronous_commit
此参数的作用为在向客户端返回成功状态之前,强制提交等待WAL被写入磁盘。这是性能和可靠性之间的权衡。如果应用程序被设计为性能比可靠性更重要,那么关闭synchronous_commit
。这意味着成功状态与保证写入磁盘之间会存在时间差。在服务器崩溃的情况下,即使客户端在提交时收到成功消息,数据也可能丢失。
查看当前synchronous_commit的设置值:
postgres=#showsynchronous_commit;synchronous_commit--------------------on(1row)
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=#showcheckpoint_timeout;checkpoint_timeout--------------------5min(1row)postgres=#showcheckpoint_completion_target;checkpoint_completion_target------------------------------0.5(1row)
max_connections
允许客户端连接的最大数目
fsync
强制把数据同步更新到磁盘,如果系统的IO压力很大,把改参数改为off
在fsync打开的情况下,优化后性能能够提升30%左右。因为有部分优化选项在默认的SQL测试语句中没有体现出它的优势,如果到实际测试中,提升应该不止30%。
测试的过程中,主要的瓶颈就在系统的IO,如果需要减少IO的负荷,最直接的方法就是把fsync关闭,但是这样就会在掉电的情况下,可能会丢失部分数据。
commit_delay
事务提交后,日志写到wal log上到wal_buffer写入到磁盘的时间间隔。需要配合commit_sibling
。能够一次写入多个事务,减少IO,提高性能
commit_siblings
设置触发commit_delay的并发事务数,根据并发事务多少来配置。减少IO,提高性能
注意:并非所有参数都适用于所有应用程序类型。某些应用程序通过调整参数可以提高性能,有些则不会。必须针对应用程序及操作系统的特定需求来调整数据库参数。
下面介绍几个我认为重要的:
增加maintenance_work_mem参数大小
增加这个参数可以提升CREATE INDEX
和ALTER TABLE ADD FOREIGN KEY
的执行效率。
增加checkpoint_segments参数的大小
增加这个参数可以提升大量数据导入时候的速度。
设置archive_mode无效
这个参数设置为无效的时候,能够提升以下的操作的速度
CREATE TABLE AS SELECT
CREATE INDEX
ALTER TABLE SET TABLESPACE
CLUSTER等。
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 配置参数修改
修改配置文件
在配置文件C:\PostgreSQL\data\pg96\postgresql.conf
中直接修改,修改前记得备份一下原文件,因为你不知道意外和明天不知道哪个会先来。修改完成之后,记得重启数据库哦。
命令行的修改方式
ALTERSYSTEMSETconfiguration_parameter{TO|=}{value|'value'|DEFAULT}
例如:我们现在要修改maintenance_work_mem
。
--查看所有数据库参数的值showall;showmaintenance_work_mem;--注意这里的设置不会改变postgresql.conf,只会改变postgresql.confALTERSYSTEMSETmaintenance_work_mem=1048576;--重启数据库showmaintenance_work_mem;--取消postgresql.auto.conf的参数设置ALTERSYSTEMSETmaintenance_work_mem=default;
数据库参数优化总结
max_connections=300#(changerequiresrestart)unix_socket_directories='.'#comma-separatedlistofdirectoriesshared_buffers=194GB#尽量用数据库管理内存,减少双重缓存,提高使用效率huge_pages=on#on,off,ortry,使用大页work_mem=256MB#min64kB,减少外部文件排序的可能,提高效率maintenance_work_mem=2GB#min1MB,加速建立索引autovacuum_work_mem=2GB#min 1MB, or -1 to use maintenance_work_mem ,加速垃圾回收。dynamic_shared_memory_type=mmap#thedefaultisthefirstoptionvacuum_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#可用的OSCACHElog_destination='csvlog'#Validvaluesarecombinationsoflogging_collector=on#Enablecapturingofstderrandcsvloglog_truncate_on_rotation=on#Ifon,anexistinglogfilewiththeupdate_process_title=offtrack_activities=offautovacuum=on#Enableautovacuumsubprocess?'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,垃圾回收不妥协。
更多关于 PostgreSQL 系列的学习文章,请参阅:PostgreSQL 数据库,本系列持续更新中。