PostgreSQL性能调优笔记一系统篇

原文地址:http://zhangwensheng.cn/blog/post/vincent/postgresql_performance_turning_os
关于PostgreSQL的性能调优,有很多细致的工作要做。作为DBA可以从操作系统、PostgreSQL的数据库系统部署、全局配置参数、索引等等方面去做调整,而开发人员可以从应用架构及SQL语句等细节着手进行调优工作。这里主要整理了PostgreSQL日常的巡检、整体调优中遇到的知识点及调优手段和方法。
实验均在CentOS6.x和PostgreSQL9.3中进行,其他Linux发行版或Unix-Like操作系统和其他版本的PostgreSQL可能有所不同。

一、知识准备

数据库是与操作系统结合非常紧密的系统应用,操作系统的很多参数会直接作用在我们的线上数据库,因此对一个合格的DBA来说,了解操作系统和了解数据库原理几乎一样基础和重要。

1、了解Linux系统的I/O调度算法(I/O Schedulers)

在Linux系统中有4种I/O调度算法
* cfq绝对公平算法
* deadline:绝对保障,适合在虚拟机所在宿主机器
* anticipatory:适合大量顺序读的情景
* noop:适合SSD或虚拟机
可以通过以下命令查看IO调度算法:

cat /sys/block/sda/queue/scheduler 
noop anticipatory deadline [cfq]

输出结果中用中括号扩起来的为当前的IO调度算法,例如当前的算法为cfq。
改变当前电梯调度算法只需要将算法名称echo到下面的目标文件中即可。

echo deadline > /sys/block/sda/queue/scheduler

2、常用性能统计分析工具

Linux的最常用的性能统计分析工具有:top,free,vmstat,iostat,mpstat,sar,pidstat。除了top和free外,其他工具均位于sysstat包中
在CentOS中安装sysstat包:yum install sysstat

vmstat
1f549bea48e57d829580d349e79698cc
r:当前CPU队列中有几个进程在等待,持续为1说明有CPU进程一直在等待,超过核心数说明压力过大;
b:当前有多少个进程进入不可中断式进程(processes in uninterruptible sleep);
swpd:已经使用的交换分区的大小;
free:当前的空闲内存;
buff:已经使用的buffer的大小,特指buffer cache(存在用来描述文件元数据的cache);
cache:已经使用的pagecache的大小,特指文件page的cache;
si/so:从磁盘交换到swap分区和从swap分区交换到磁盘的大小;
bi/bo:从磁盘读出和写入到磁盘的大小,单位blocks/s;
in:每秒被中断的进程数;
cs:每秒多少个CPU进程在进进出出;
可以通过以下命令强制清除已经在使用的buffer和cache,下文的实际案例中会详细记录。

echo 3 > /proc/sys/vm/drop_caches

iostat

iostat -x /dev/sdaiostat -x /dev/sda

2aed1f0f44b00a8c1976416a3a0ae180
其中-x参数指获取更多信息;
rrqm/s,wrqm/s:每秒读写请求的合并数量(OS会尽量读取和写入临近扇区);
r/s,w/s:每秒读写请求次数;
rsec/s,wsec/s:每秒读写请求的字节数;
avgrq-sz:每秒请求的排队大小;The average size (in sectors) of the requests that were issued to the device.
avgqu-sz:每秒请求的队列长度;
await:从服务发起到返回信息共花费的平均的服务时间;
svctm:该值将取消,不必关注;Warning! Do not trust this field any more. This field will be removed in a future sysstat version.
%util:磁盘的利用率;

mpstat
返回CPU的详细性能信息,如果只需要观察某一个CPU,加上参数-P n,n为要观察的core的索引。
d52dcb424f26de9dab1b96ff7b73abaa
%usr:用户花费的时间比例;
%nice:
%sys:系统花费的时间比例;
%iowait:IO等待;
%irq:硬中断花费的CPU时间;
%soft:软中断花费的CPU时间;
%steal,%guest:这两个参数与虚拟机相关;
%idle:空闲比率;

sar

sar是性能统计非常重要并有用的工具。sar每隔一段时间进行一次统计,它的配置可以在/etc/cron.d/sysstat中,默认为10分钟:

*/10 * * * * root /usr/lib64/sa/sa1 1 1

根据自己的需求,加快或放慢统计信息的收集频率,在测试过程中可以将每10分钟修改为每1分钟用来提高统计的实时性;
sar的结果是基于历史的,即从开机到最后一次收集数据时的统计信息,在输出时默认使用AM/PM显示时间,可以在执行sar前强制使用LANG=C使用24小时时间表示法显示时间;
-r参数:汇总内存的使用情况
-q参数:汇总CPU状况
b565711eee1298480d960b4075ba0f53
runq-sz:运行队列平均长度;
plist-sz:进程列数
ldavg-1,ldavg-5,ldavg-15:每分、每5分钟、每15分钟的平均负载;
-b参数:汇总IO状况
5f46210bd121fbfc5c42182b6050a850
tps,rtps,wtps:TPS数
bread/s,bwrtn/s:每秒读写block的大小,需要注意:这里每个block的大小为512字节,man sar有它的详细说明;
Total amount of data read from the devices in blocks per second. Blocks are equivalent to sectors with 2.4 kernels and newer and therefore have a size of 512 bytes. With older kernels, a
block is of indeterminate size.

sar命令也可以使用-P 0,1,2….n查看单个CPU的历史数据;
-n DEV:网络状况的负载
-n EDEV:错误的网络包
sar的历史数据保存在/var/log/sa/目录,可以设置sar历史数据的保留天数,查看默认保存几天的历史数据和修改保存天数可以查看或编辑/etc/sysconfig/sysstat中的HISTORY值;当设置的保存天数超过28天,则会在/var/log/sa/下建立月份目录;
只查看某一天的数据指定一下具体的日期对应的历史数据文件即可,例如查看13号的数据:

sar –q /var/log/sa/sa13

468945c1e04fcf991dd1f7a99eaf227c

sar保存的历史数据,可以借助gunplot工具绘制性能指标图形,配置gunplot很麻烦,通常使用zabbix等工具方便很多。
单位换算的小技巧:2的10次方为K,20次方为MB,30次方为GB。

nmon

补充一个很强大的工具:nmon,像一个图形界面的top一样,可以动态的、漂亮的显示当前的IO,CPU,MEMORY,NETWORK的实时性能。并且可以将历史数据通过OFFICE宏输出为图表,具体使用略。有兴趣参考下面两个链接:
http://nmon.sourceforge.net/pmwiki.php?n=Main.HomePage

https://www.ibm.com/developerworks/community/wikis/homelang=en#!/wiki/Power%20Systems/page/nmon_analyser

iotop

像top工具一样,但它是用来观察IO状况的,可以方便的观察当前系统的IO是否存在瓶颈以及IO出现瓶颈时观察是哪些进程造成的。通常还会配合pidstat命令一起来排查问题。
除了iotop这样专业的工具定位IO问题,还可以直接利用进程状态来找到相关的进程。
我们知道进程有如下几种状态:
- D uninterruptible sleep (usually IO)
- R running or runnable (on run queue)
- S interruptible sleep (waiting for an event to complete)
- T stopped, either by a job control signal or because it is being traced.
- W paging (not valid since the 2.6.xx kernel)
- X dead (should never be seen)
- Z defunct (“zombie”) process, terminated but not reaped by its parent.
其中状态为 D 的一般就是由于 wait IO 而造成所谓的”非中断睡眠“,我们可以从这点入手然后一步步的定位问题:

for x in `seq 10`; do ps -eo state,pid,cmd | grep "^D"; echo "----"; sleep 5; done;
while true; do date; ps auxf | awk '{if($8=="D") print $0;}'; sleep 1; done

还可以适用pidstat -d 1 来查看进程的读写情况,用来诊断IO问题。

二、资源限制

为什么要进行资源限制?例如mongodb,例如其他进程,不想让他们使用太多的系统资源,这时就需要对资源进行限制。

使用PAM模块进行资源限制

使用PAM模块对用户可用资源进行限制。/etc/pam.d/system-auth配置文件中已经配置了当用户登录成功后对用户使用pam_limits.so模块进行限制。
085a133416729049002bf69dad2913d4
PAM的主配置文件:/etc/security/limits.conf,子配置文件位于/etc/security/limits.d/目录,系统首先读/etc/security/limits.conf,如果/etc/security/limits.d/目录下还有配置文件的话,会将主配置文件和子配置文件合并后一起分析,/etc/security/limits.d/里的配置节会覆盖/etc/security/limits.conf中已存在的配置。
默认的CentOS 6.4以后,子配置文件/etc/security/limits.d/90-nproc.conf对所有用户一共可开启的进程数限制为1024个,如果将该限制注释或删除,可以看到用户的max processes和超级用户的一样了,是一个不固定的数字,例如773566。如果不对用户进行最大进程数的限制,系统允许的用户最大进程数就由内核计算,计算的依据为:

default_nproc = max_threads / 2
= (mempages * PAGE_SIZE) / ( 2 * 8 *THREAD_SIZE )
= total_memory/128K;

验证一下:

[root@localhost ~]# cat /proc/meminfo |grep MemTotal
MemTotal:       99038104 kB

用MemTotal除以128,得到773735,与默认值非常接近,有一些内存被用作例如内核启动等,实际的可用内存会稍微少一点。

作为专有服务器,例如数据库服务器,应该根据硬件状况进行调整。

[root@cos64d ~]# cat /etc/security/limits.d/90-nproc.conf 
# Default limit for number of user's processes to prevent
# accidental fork bombs.
# See rhbz #432903 for reasoning.
*          soft    nproc     1024
root       soft    nproc     unlimited

对CPU进行限制

一个进程最多可占用多长时间的CPU(单位:分钟),通过top命令的TIME列进行观察。
使用ulimit –a查看当前系统的限制,默认的cpu time是无限制的。
cd07da4839739edf197185a12c60e57d
CPU限制举例:
编辑主配置文件如下(注意一下下面的配置文件中限制CPU占用时间的单位为分钟,而用ulimit命令输出的时间单位为秒):

[root@cos64d ~]# vim /etc/security/limits.conf
postgres             soft    cpu        1
postgres             hard    cpu        2

修改完成可以从下图中对比postgres用户与root用户的limits区别:
65ee87e08f4f0ede5bafadb429085ad7
上图中的红色标注,上面为root用户的,下面为postgres用户的。此时postgres用户的cpu time为60秒。使用dd命令进行测试,通过top命令观察,当postgres用户的dd进程的运行60秒之后,系统会自动KILL掉该进程,并且给进程返回CPU time limit exceeded的消息。
2895bcc93f03ca0be54548b72131921a

[root@cos64d ~]# su - postgres 
[postgres@cos64d ~]$ dd if=/dev/zero of=/dev/null 
CPU time limit exceeded (core dumped)

在/etc/security/limits.conf配置文件中的domain一列,可以使用用户名,UID等。UID:表示大于该UID的所有用户,@**标识一个用户组。

最大进程数的限制

通过ulimit –a | grep processes可以查看操作系统当前最大进程数的限制。CentOS对所有用户(包括超级用户)默认为1024,在子配置文件中进行修改,可以使用一个简单的bomb程序进行测试:

bomb(){ bomb | bomb& };bomb

执行之后,可以看到进程数达到系统的最大限制时,会报Resource temporarily unavailable的错误出来了。
4282dc64f127181915d711bc5e6a16aa

三、System V IPC资源限制

系统信号(量)配置对PostgreSQL的影响

在PostgreSQL中shared_buffers的值超过系统内核参数设置时会有以下错误信息:
FATAL: could not map anonymous shared memory: Cannot allocate memory
HINT: This error usually means that PostgreSQL’s request for a shared memory segment exceeded available memory or swap space. To reduce the request size (currently 4418682880 bytes), reduce PostgreSQL’s shared memory usage, perhaps by reducing shared_buffers or max_connections.
当设置了较大的max_connections值时,启动数据库可能会失败,报出以下的错误
FATAL: could not create semaphores: No space left on device
DETAIL: Failed system call was semget(5432127, 17, 03600).
HINT: This error does not mean that you have run out of disk space. It occurs when either the system limit for the maximum number of semaphore sets (SEMMNI), or the system wide maximum number of semaphores (SEMMNS), would be exceeded. You need to raise the respective kernel parameter. Alternatively, reduce PostgreSQL’s consumption of semaphores by reducing its max_connections parameter.
The PostgreSQL documentation contains more information about configuring your system for PostgreSQL.
这个错误并不是指磁盘空间不足,而是内核的System V信号灯(semphore)的限制小于PostgreSQL意欲创建的连接数的数量。
PostgreSQL为每个进程使用一个信号灯,每个进程以16个信号灯为一个信号灯集(a semphore set)。每个信号灯集还包含第17个信号灯,它(第17个信号灯)里面存储一个”magic number”,用以检测与所使用的其他应用程序的信号集冲突。

IPC
名词解释IPC:InterProcess Communication(IPC,进程间通信)

信号量参数

SEMMNI

内核参数SEMMNI 决定系统同一时间存在的信号集的数量限制。SEMMNI一般应该是系统预期代理进程数上限乘以逻辑分区数目的两倍,再加上客户端连接数。因此这个参数至少应该为:

内核参数SEMMNI 决定系统同一时间存在的信号集的数量限制。SEMMNI一般应该是系统预期代理进程数上限乘以逻辑分区数目的两倍,再加上客户端连接数。因此这个参数至少应该为:

ceil((max_connections + autovacuum_max_workers + 4) / 16)

SEMMNS

系统里的最大信号灯数目是由SEMMNS设置的, 因此这个值应该至少和max_connections加autovacuum_max_workers设置一样大,并且每16个连接,也就是一个信号灯集中还要另外加一个”magic number”信号灯。因此这个参数至少应该为:

ceil((max_connections + autovacuum_max_workers + 4) / 16) * 17

降低允许的最大连接数目是一个临时绕开启动失败的方法,这个启动失败通常被来自函数semget的错误响应” No space left on device “搞得很让人迷惑。
例如下面这台PostgreSQL的服务器:
CPU: Intel(R) Xeon(R) CPU E7- 4820 @ 2.00GHz(4颗物理CPU*8核*2=64线程)
内存:96GB
使用ipcs -la查看系统所有关于IPC的限制,用ipcrm命令可以手动清理信号量。

[root@localhost ~]# ipcs -la
------ Shared Memory Limits --------
max number of segments = 4096
= SHMSEG
max seg size (kbytes) = 67108864
= cat /proc/sys/kernel/shmmax = SHMMAX(bytes)/1024 = 64GB
max total shared memory (kbytes) = 17179869184
= cat /proc/sys/kernel/shmall = SHMALL / 4k = 16GB
min seg size (bytes) = 1
= SHMMIN
------ Semaphore Limits --------
max number of arrays = 128 (SEMMNI)
max semaphores per array = 250 (SEMMSL)
max semaphores system wide = 32000 (SEMMNS)
max ops per semop call = 32 (SEMOPM)
semaphore max value = 32767 (SEMVMX)
------ Messages: Limits --------
max queues system wide = 32768
max size of message (bytes) = 65536
default max size of queue (bytes) = 65536

也可以cat /proc/sys/kernel/sem查看信号量的配置,从左到右输出的值顺序分别是
SEMMSL(max semaphores per array)
SEMMNS(max semaphores system wide)
SEMOPM(max ops per semop call)
SEMMNI(max number of arrays)。至少ceil((max_connections+autovacuum_max_workers)/16) =

[root@localhost ~]# cat /proc/sys/kernel/sem 
250 32000   32  128
SEMMSL, SEMMNS, SEMOPM, SEMMNI

只查看共享内存的限制,可以通过ipcs –lm查看

[root@localhost ~]# ipcs -lm
------ Shared Memory Limits --------
max number of segments = 4096
max seg size (kbytes) = 67108864
max total shared memory (kbytes) = 17179869184
min seg size (bytes) = 1

通过以上信号量的配置,可以计算出在PG中可以允许的最大连接数:
SEMMNI = ceil((max_connections + autovacuum_max_workers + 4) / 16) = ceil((max_connections + 3 + 4) / 16)
得出max_connections = 2041,去掉零头实际测试一下,设置max_connections的值为2000,可以看到PostgreSQL启动成功,但是超过2000时则会报出could not create semaphores: No space left on device的错误。
因此,如果我们计划将max_connections的值设置为5000,那么SEMMNI应该至少设置为320,SEMMNS应该设置为80000。

[root@localhost ~]# sysctl -w kernel.sem="250 80000 32 320"
kernel.sem = 250 80000 32 320

使用sysctl –w命令只能临时生效,重启后又会恢复到原来的值,只有将kernel.sem的值写入sysctl.conf文件才会永久生效:

[root@localhost ~]# echo “kernel.sem=250 80000 32 320″ >> /etc/sysctl.conf

也可以直接按照GREENPLUM的参数进行设置:

[root@localhost ~]# sysctl -w kernel.sem="250 512000 100 2048"

关于SEMMOP的设置:
The SEMOPM Parameter
This parameter defines the maximum number of semaphore operations that can be performed per semop(2) system call (semaphore call). The semop(2) function provides the ability to do operations for multiple semaphores with one semop(2) system call. Since a semaphore set can have the maximum number of SEMMSL semaphores per semaphore set, it is often recommended to set SEMOPM equal to SEMMSL.
Oracle recommends to set SEMOPM to a minimum value of 100 for 9i R2 and 10g R1/R2 databases on x86 and x86-64 platforms.
下面这张图辅助理解信号量、信号集之间的关系:
01f560ee112be4291815bcc6ea22d3f5

SHMALL
系统物理内存:96GB
Linux kernel分配:2GB
可以分配给SHMALL的内存为94GB,SHMALL的单位为内存页数,需要计算94GB是多少内存页数,通常内存页的大小为4k,为了安全起见,通过getconf PAGE_SIZE获取当前的页大小

[root@localhost ~]# getconf PAGE_SIZE
4096

计算SHMALL = 94*2^30/4096 = 24641536

SHMMAX
如果系统只有一个数据库实例,通过SHMALL*PAGESIZE计算
如果系统有三个数据库实例,一个分配24GB,一个分配48GB,第三个分配94-48-24=22GB,那么SHMMAX应该分配最少48GB*PAGESIZE

7487376296269e88dcb158a1d1b27141

ceil:返回大于或等于表达式的最小整数

使用cgroup进行资源限制

ps -eo pid,cgroup,cmd | grep -i postgres

用上面的命令查看是否有cgroup对系统资源的限制。

三、系统常用的调优方法

对于现在较新版本的Linux操作系统,可调的点比较少。关注一下几个方向:

Kernel
如果有条件,Kernel尽量用比较新的,例如

[root@zhangwensheng.cn ~]$ uname -r
3.14.29-3.centos6.x86_64

可以经常关注kernel看一下releasenotes,如果有bugfix或较好的性能更新,有条件尽量升级。

I/O优化
除了根据不同应用场景,配置磁盘的I/O调度方式之外,还可以通过以下手段小幅提高I/O能力。

blockdev –getra /dev/sda

默认为256,在当前较新的硬件机器中,可以设置到16384

blockdev –setra 16384 /dev/sda

或echo 16384 /sys/block/sda/queue/read_ahead_kb

大页

[root@localhost ~]# cat /sys/kernel/mm/transparent_hugepage/enabled 
[always] madvise never

有时候我们并不想使用大页,由PG来管理内存更好,所以将它关闭:

echo never > /sys/kernel/mm/transparent_hugepage/enabled

启用大页

echo always > /sys/kernel/mm/transparent_hugepage/enabled

swap

cat /proc/sys/vm/swappiness 
60
[root@localhost ~]# echo '0' > /proc/sys/vm/swappiness 
[root@localhost ~]# cat /proc/sys/vm/swappiness 
0
[root@localhost localhost ~]# vi /etc/rc.d/rc.local 
[root@localhost localhost ~]# tail -n 1 /etc/rc.d/rc.local 
echo '0' > /proc/sys/vm/swappiness

释放swap,要注意一定需要有足够的磁盘

swapoff -a 
swapon -a

网络
使用以下命令查看各状态的连接

netstat -n | awk '/^tcp/ {++S[$NF]} END {for(a in S) print a, S[a]}'

查看sock状态

cat /proc/net/sockstat
sockets: used 294
TCP: inuse 35 orphan 0 tw 0 alloc 45 mem 1
UDP: inuse 13 mem 2
UDPLITE: inuse 0
RAW: inuse 4
FRAG: inuse 0 memory 0

sockets: used:已使用的所有协议套接字总量
TCP: inuse:正在使用(正在侦听)的TCP套接字数量。其值≤ netstat –lnt | grep ^tcp | wc –l
TCP: orphan:无主(不属于任何进程)的TCP连接数(无用、待销毁的TCP socket数)
TCP: tw:等待关闭的TCP连接数。其值等于netstat –ant | grep TIME_WAIT | wc –l
TCP:alloc(allocated):已分配(已建立、已申请到sk_buff)的TCP套接字数量。其值等于netstat –ant | grep ^tcp | wc –l
TCP:mem:套接字缓冲区使用量(单位不详。用scp实测,速度在4803.9kB/s时:其值=11,netstat –ant 中相应的22端口的Recv-Q=0,Send-Q≈400)
UDP:inuse:正在使用的UDP套接字数量
RAW:
FRAG:使用的IP段数量

参考文档

Shared Memory Under Linux
http://fscked.org/writings/SHM/
ulimit限制之nproc问题
http://blog.yufeng.info/archives/2568
System V IPC
http://docs.oracle.com/cd/E19455-01/806-4750/6jdqdfltg/index.html
RHEL 6 Configuring SHMMAX and SHMALL Settings
http://seriousbirder.com/blogs/rhel-6-configuring-shmmax-and-shmall-settings/
Tuning and Optimizing Red Hat Enterprise Linux for Oracle 9i and 10g Databases
https://access.redhat.com/documentation/en-US/Red_Hat_Enterprise_Linux/5/html/Tuning_and_Optimizing_Red_Hat_Enterprise_Linux_for_Oracle_9i_and_10g_Databases/sect-Oracle_9i_and_10g_Tuning_Guide-Setting_Semaphores-The_SEMOPM_Parameter.html
内核参数SEMMSL SEMMNS SEMOPM SEMMNI参数的设置
http://www.dbafree.net/?p=92
Configuring SHMMAX and SHMALL for Oracle in Linux
http://dbasolutions.wikispaces.com/SHMMAX+and+SHMALL
Understanding Linux Load Average – Part 2
https://prutser.wordpress.com/2012/04/23/understanding-linux-load-average-part-1/
https://prutser.wordpress.com/2012/05/05/understanding-linux-load-average-part-2/
https://prutser.wordpress.com/2012/05/28/understanding-linux-load-average-part-3/
Linux 系统监控、诊断工具之 IO wait
https://linux.cn/article-4452-1.html

还没有评论,快来抢沙发!

发表评论

  • 😉
  • 😐
  • 😡
  • 😈
  • 🙂
  • 😯
  • 🙁
  • 🙄
  • 😛
  • 😳
  • 😮
  • emoji-mrgree
  • 😆
  • 💡
  • 😀
  • 👿
  • 😥
  • 😎
  • ➡
  • 😕
  • ❓
  • ❗
  • 71 queries in 0.486 seconds