数据库配置优化

数据库配置优化

内存配置优化

PG与内存有关的配置参数：

*shared_buffers：共享缓存区大小，推荐内存的1/4，不超过总内存的1/2

*work_mem：进程单独分配的内存（排序、hash）

*maintence_work_mem：进程单独分配的内存（维护操作）

 

从PostgreSQL9.3开始，共享内存已从System V方式改成了Posix方式和mmap方式，因此在PostgreSQL9.3之后不再需要配置Linux的"shmmax"和"shmall"参数了，而在此之前的版本中需要配置这两个参数：

*shmmax：表示单个共享内存段（shared memory segment）可以创建的最大值

*shmall：表示整个系统内可以为共享内存配置的页面数。如果一台机器上运行有多个数据库实例，需要把此值配置成大于各个数据库实例所需要的共享内存之和

shmmax单位是字节，而shmall的单位是页面

 

一台2GB的机器，可以配置的共享内存为512MB，配置如下

kernel. shmmax = 536870912

kernel.shmall = 131072

把上述配置放到/etc/sysctl.conf文件中，然后运行sysctl -p让其生效即可

 

除设置Sys V共享内存的参数外，还需要设置SysV信号量的相关参数，SysV信号量用于对共享内存访问时的锁管理。Linux下需要配置的信号量参数主要有以下几个：

*SEMMSL：内核参数，控制每个信号集合的最大信号数

*SEMMNS：内核参数，控制系统范围内能使用的最大信号量数

*SEMOPM：semop()函数每次调用所能操作的一个信号量集中的最大信号量

*SEMMNI：内核参数，控制整个系统中信号量集的最大数量

 

“信号量集”：每个“信号量集”都是由很多个信号量组成的，操作系统可以对一个信号量集做一个原子操作，所以系统中信号量的最大数目=每个信号量集的大小*信号量集的个数,即:

SEMMNS = SEMMSL* SEMMNI

而SEMOPM是指对某个“信号量集”进行一个原子操作时，可以操作的信号量数，所以其最大不应超过“信号量集”中“信号量”的数目，通常设置为相等,即:

SEMOPM = SEMMSL

 

对PostgreSQL数据库来说，对这几个参数有如下要求：

SEMMNI >= ceil((max_connections + autovacuum_max_workers +4)/16)

SEMMSL>=17

从前面可以知道:

SEMOPM <= SEMMSL

SEMMNS=SEMMSL* SEMMNI = ceil((max_connections + autovacuum_max_workers +4) /16)*17

 

假设一个数据库有如下配置

max_connections=1000

autovacuum_max_workers =3

则这几个参数的配置值为:

SEMMNI= ceil((max_connections + autovacuum_max_workers +4)/ 16)=63

SEMMSL要求大于17，取默认值250即可以满足要求

SEMOPM与SEMMSL相同，即250

SEMMNS= SEMMNI*SEMMSL=63*250-15750

那么,此数据库在/etc/sysctl.conf中的配置如下:

SEMMSL SEMMNS SEMOPM SEMMNI

kernel.sem=250 15750 250 63

有时, Linux的默认配置值也比上面的大,这时保持默认值也是可以的

 

关于双缓存的优化

PG数据库中对数据块设计了专门的共享缓冲区，由于PG的数据文件都在文件系统中，操作系统的文件系统也有缓存，会导致有两个副本，内存利用率不高，这就是“double caching”问题

 

减少这个问题影响的方法：

1、设置较小的shared buffer，大多数内存给文件系统缓存使用

2、设置较大的shared buffer，只留较少的内存给文件系统缓存使用

第2种方法需要防止Linux下页表过大的问题。

页表就是操作系统把逻辑地址映射成物理地址时，需要把映射关系也存到一个内存中，这部分内存就是页表。在Linux操作系统中，即使是同一块共享内存，在每个进程中的逻辑地址也是不相同的，因此不同进程中的映射表项也是不相同的。在64位的机器上，每个4KB页需要占用大约8字节的内存，一台48GB内存的机器，如果分配了24GB的共享内存，则每个进程的页表大小为：（24G/4k） ×8=48MB，如果服务器连接上500个进程，页表的大小将是500×48=24GB。马上就会把机器上的所有内存吃光了，会产生很大的问题。当然不是每个新连接一上来，进程的页表就会马上分配48MB，当进程需要建立逻辑地址与物理地址之间的关系时才会分配，所以进程占用的页表空间是缓慢增加的，但最终还是可能会占用很大的页表内存

 

可以使用下面的命令检查页表的大小：

[root@sk ~]#cat /proc/meminfo |grep PageTables

PageTables: 4492 kB

如果发现页表的大小不是几十MB，而是达到了1GB以上，则说明数据库存在此问题。对于基本共享内存的多进程架构程序都会存在这个问题。Oracle数据库在Linux下也存在这个问题，需要使用大页来解决。PostgreSQL9.4版本开始支持大页，打开大页的方法是设置参数huge_pages，如下:

huge_pages = try

把huge_pages设置为"try"，表示让PostgreSQL尝试使用大页，如果操作系统没有配置大页或配置的大页小于PostgreSQL需要的大页内存，那么PostgreSQL在分配大页失败后，会使用普通内存。如果把“huge_pages”设置为“on”，分配大页失败后，PG启动也会失败

操作系统中大页的设置项在/etc/sysctl.conf中：

vm.nr_hugepages=10240

vacuum中的优化

PostgreSQL数据库需要定期做vacuuming：

*标记多版本中不再需要的旧版本行所占用的空间为可用，以重复使用这部分磁盘空间

*更新统计数据，保证执行计划的正确性

*事务ID为32位递增的一个整数，当增加到最大值后，会从起始值开始，这就要保证旧的已提交事务的数据仍然可见，需要把这些行上的事务ID更新为一个永远可见的事务ID （Frozen XID）

 

目前有以下两种VACUUM：

*标准的VACUUM

*VACUUM FULL

 

VACUUM标准方式通常可以与SELECT语句和DML语句（如INSERT、UPDATE、DELETE命令）并行执行，但是若在清理该表，不能使用如ALTER TABLE这样的DDL语句来修改表定义。VACUUM FULL需要有在表上的一个排斥锁才能工作，不能与其他使用该表的语句并行执行，因此一般情况下管理员应尽量使用标准的VACUUM。不过VACUUM FULL可以回收更多的磁盘空间，当然它的运行速度也要慢得多

 

对于一般的数据库，默认配置中autovacuum后台进程会自动运行（这是因为默认配置参数autovacuum为on），会自动周期地做vacuum，所以不再需要手工vacuum

 

执行VACUUM时会产生一些负载，这会影响到一些正常的数据库访问，因此PG数据库提供了一种机制和多个配置参数来减少对正常访问的影响。这种机制实施的方式为：在执行VACUUM和ANALYZE命令时，PostgreSOL统计这些操作产生的代价值，并把这个代价值累积到一个计数器中，当此计数器的值超过设定的阀值时，则休眠，然后把计数器置0并继续执行，当计数器的值再次超过指定的阀值时，则再次休眠，如此不断重复这个过程。手工执行vacuum的代价阀值是由参数vacuum_cost_limit指定的，默认为200，每次休眠的时间是由参数vacuum_cost_delay指定的，单位是毫秒，默认为0，即不休眠。计算代价值的方法如下。

vacuum_cost_page_hit：vacuum访问的数据块在共享内存中的代价值，默认为1

vacuum_cost_page_miss：vacuum访问的数据块不在共享内存中的代价值，默认为10

vacuum_cost_page_dirty：vacuum改变一个非脏数据块为脏数据块的代价值，默认为20

想减少执行vacuum命令对现有系统的影响，可以把vacuum_cost_delay设置为一个合适的值

 

对于自动autovacuum，也有一组与上面类似的参数来实现相同的功能：

autovacuum_vacuum_cost_delay

autovacuum_vacuum_cost_limit

autovacuum_vacuum_cost_page_hit

autovacuum_vacuum_cost page_miss

autovacuum_vacuum_cost_page_dirty

默认情况下，autovacuum_vacuum_cost_delay = 20ms，这个默认值对更新比较频繁的数据库来说太高了，建议设置为1~4ms。

还有一个参数autovacuum_max_workers可以指定启动autovacuum的work进程是多少，默认值为3

当发现来不及autovacuum时，可以把此参数值调得大一些

另外，可以合并更新，以减少更新的量

 

对于更新频繁的表，应该设置更小的fillfactor，这样可以更多地利用HOT（heap only tuple），同时索引上的更新也会少很多，自然也就减少了vacuum的代价

 

调整autovacuum进程进行vacuum操作的阀值条件参数，也可以提高性能：

autovacuum_vacuum_threshold：当表上发生变化的行数至少达到此参数值时，才可能让autovacuum对其进行VACUUM，这里说“可能”是因为还由另一个参数autovacuum_vacuum_scale_factor同时控制vacuum的执行条件。默认值为50，也可以在表上单独设置此参数，让不同的表有不同的配置

autovacuum_vacuum_scale_factor：触发VACUUM的第二个阀值条件

 

autovacuum进程在表上触发vacuum的条件如下:

表上发生变化的行数>= autovacuum_vacuum_scale_factor*表上记录数+autovacuum_vacuum_threshold

 

控制调整autovacuum进程进行ANALYZE操作的阀值条件：

autovacuum_analyze_threshold：当表上发生变化的行数达到此参数值时，才可能让autovacuum对其进行ANALYZE，这里说“可能”也是因为还由另一个参数autovacuum_analyze_scale_factor同时控制vacuum的执行条件。默认值为50。也可以在表上单独设置此参数，让不同的表有不同的配置

 

控制表中事务的最大寿命：

autovacuum_freeze_max_age：默认值为2亿

当表中事务的最大寿命达到此参数值时，就会在表上强制执行VACUUM操作。这主要为防止事务ID回滚后无法正确判断事务的新旧，从而导致数据丢失。需要注意的是，即使autovacuum被禁止，系统也会强制调用autovacuum进程在表上执行VACUUM

 

预写式日志写优化

预写式日志（WAL）是对数据文件进行修改（通过表或索引的数据文件）时，先把这些操作记录到日志中，数据文件修改后的脏页不必马上刷新到磁盘中，如果出现崩溃，可以重做记录在日志中的操作从而恢复数据库

 

检查点（checkpoint）是事务序列中的点，在产生检查点时，所有脏数据页都会刷新到磁盘中并且会向日志文件写入一条特殊的检查点记录，确保在该点之前的所有信息都已经写到数据文件中去了（改变以前刷新的WAL的缩写文件）。在发生崩溃后，恢复过程会查找最后的检查点记录，然后重做这个检查点之后的日志，以便把数据库恢复到正常情况下。检查点完成之后，检查点之前的日志不再需要了，可以循环使用或删除，当然这些日志还可以用在Standby数据库上

 

以下两个参数控制检查点发生的频率：

checkpoint_segments

checkpoint_timeout

每写完checkpoint_segments个WAL日志文件或每过checkpoint_timeout秒就创建一个检查点，不管哪个条件满足，都会生成一个检查点。默认情况下checkpoint_segment为3，checkpoint_timeout为300秒（5分钟）。当然也可以用SQL命令CHECKPOINT强制创建一个检查点

产生检查点时，需要把当前所有数据块的脏页刷新到磁盘中，因此它的开销比较高。让检查点产生得慢一些，可能会提高性能。可以设置checkpoint_warning，对检查点参数进行简单检查。如果检查点发生的间隔接近checkpoint_warning秒，就会在服务器日志中输出一条消息，这样，通过监控这条日志，就可以在检查点发生得太频繁时通知用户减少该频率

 

为了避免检查点产生太多的I/O，导致系统性能出现大的抖动，可以让PostgreSQL在平时也尽快平均地把脏页刷新到磁盘中，而不必等到产生检查点时，才发现需要写太多的脏页。这个机制是由参数checkpoint_completion_target来控制的，此参数的默认值为0.5，即让PostgreSQL在两个检查点间隔时间的0.5倍时间内完成所有脏页的刷新。看起来把该值设置得越接近1.0，性能的抖动才会越平稳，但实际上不要设置为1.0，设置为0.9就足够了，因为设置为1.0极有可能导致不能按时完成检查点

 

还有一个名为wal_buffers的参数，用于指定WAL缓存的大小，在较老的PostgreSQL版本中，此值默认为64KB，但在实际使用中，这个值通常有些小，因此在较新的PostgreSQL版本中，此默认值已被改为4MB

 

PostgreSQL也提供了组提交（group commit）功能，这个功能默认是关闭的。它由以下两个参数设置：

commit_delay：默认此值为0，而非零的延迟允许多个事务共用一个fsync()系统调用提交，如果系统负载足够高，那么在给出的间隔里，其他事务可能已经准备好提交了，这样多个事务就可以共用一个fsync()调用，从而提高性能。但是如果没有其他事务准备提交，那么这个间隔就增加了事务的延迟时间。因此，只有在其他处于活跃状态的事务数超过参数commit_siblings设置的值时，这个延迟才会真的发生，才会让多个事务共用一个fsync()系统调用

commit_siblings：默认值为5，表示只有存在5个活跃事务时，才会有组提交

 

一些参数可以根据实际情况进行调整：

*wal_level：决定多少信息写入WAL中。默认值是minimal，只写入数据库崩溃或突然关机后恢复时所需要的信息。archive添加WAL归档需要的记录。hot_standby添加备库只读查询时需要的信息。服务器启动时才能设置这个参数

*synchronous_commit：声明一个事务是否需要等到操作被写到WAL日志后再返回。当设置成off时，会提高性能，但已成功提交并返回给应用程序的事务在主机或数据库崩溃时可能丢失，这些事务可能还没有刷新到WAL日志中。将这个参数设置为off不会有数据库不一致性的风险，在数据库发生故障时仅可能丢失一些最近已提交的事务。另外，此参数是可以以session级别来设置的，当明确知道某个事务不是很重要时，可以在session级别把此参数设置为off，这样不影响其他事务

*full_page_writes：默认为on，PostgreSQL服务器在检查点之后对页面第一次写入时，将整个页面写到WAL里。这主要是防止在操作系统崩溃过程中，只有部分页面写入磁盘，避免同一个页面中新旧数据被混合，以致在后面的恢复期间，由于WAL里存储的行变化信息不完整，无法完全恢复该页。把完整的页面影像保存下来，就可以保证正确恢复页面。虽然设置为off可以提高一点性能，但一般不建议设置为off

*wal_writer_delay：声明WAL写进程的周期。在每个周期中，将WAL刷到磁盘后，wal_休眠 wal_writer_delay毫秒后会再次重复执行。默认是200毫秒