性能优化和体系化运维 ——唐成

内容简要:

一、操作系统优化

二、数据库配置优化

三、日常操作

四、运维方案

 

 

一、操作系统优化

操作系统优化主要从共享内存、参数设置、使用大页、信号量和Limit参数等方面阐述。

 

(一)共享内存

当用户使用PostgreSQL 9.3或GreenPlum5及以前的版本,为防止数据库使用Swap,需先设置下面两个参数:

Ø  kernel.shmmax = 16724692992

Ø  kernel.shmall = 4083177

从PostgreSQL 9.3开始,数据库不再大量使用Sysv类型的共享内存,主要使用MMAP类型的共享内存,因此上面两个参数设置一个较小的值,数据库也可以启动。

 

(二)参数设置

1.   vm.swappiness=0

在数据库中需尽量避免使用Swap,因此将该参数值设置为0。

2.   Overcommit参数

通常情况下设置vm.overcommit_memory=2,即不让系统超申请。通常情况下该值为0,表示申请的内存可以超过物理机内存。当大家都开始使用的时候,则会发生OOM,将一些进程给Cut掉,这在数据库中是比较危险的情况,因此建议将vm.overcommit_memory设为2。

除此之外,需要设置vm.overcommit_ratio= 90,需要根据实际情况设置。

当设置完这两个值后,可以申请的内存不超过:

swap的大小 + 物理内存* vm.overcommit_ratio

例如:一个256G内存的机器,16G Swap,应该把vm.overcommit_ratio= 93,这样256*95%+16=254G,内存申请不可超过254G,如果超过的话则申请失败。

 

(三)大页

1.为什么要使用大页

使用大页是因为页表问题的存在,使用小页会存在页表占用过多内存的问题。

假设一台256G的机器,我们分配了共享内存为128G。如果是小页,大小为4K,则有33554432页表项,每项至少占用4字节,则页表大小32M*4=128M,如果有1024个连接,则页表占用128M*1024=128G内存,占据机器总内存的一半。

如果使用2M大小的大页表,则:则有128G/2M=65536项,65536*4=256K,1024个连接:1024*256k=256M内存,内存占用率大幅降低。

通常在 Linux操作系统里面,建议使用大页。

 

2.大页配置

大页参数设置:vm.nr_hugepages;

这个参数设置的值为多少,则有对应数量的2M大页。大页的大小需要与数据库的Shared_buffer相一致,如果比Shared_buffer大很多则会浪费资源。

大页不会被Swap,默认Lock,即类似Oracle的lock_sga,且分配大页内存后,及时不使用大页,也不可做其他用途。

 

(四)信号量

PostgreSQL数据库是多进程数据库,进程和进程之间访问同一个共享内存时,需要各种各样的“锁”机制,通常信号量指的就是进程之间的“锁”。需要设置kernel.sem=20 13000 20 650,参数的4个数据对应:SEMMSL、SEMMNS、SEMOPM、SEMMNI。

SEMMSL:信号集的最大信号量数,PostgreSQL要求大于17,取整数20, Oracle要求是250。

SEMMNS整个系统范围内的最大信号量数,所以SEMMNS = SEMMSL *SEMMNI。

SEMOPMSemop函数在一次调用中所能操作一个信号量集中最大的信号量数,所以能常与SEMMSL相同。

SEMMNI信号量集的最大数目,PostgreSQL数据库中要求是数据库进程数/16,假设允许10000个连接,即需要至少625,取一个整数650。这个进程数不只是用户服务进程,还需要包括一些管理的服务进程,如Autovacuum的Work进程。

 

(五)Limit参数

1./etc/security/limits.conf(软/硬限制一样):

Ø  soft nofile 65536

Ø  hard nofile 65536(打开文件的值)

Ø  soft nproc 131072

Ø  hard nproc 131072(进程数)

Ø  soft memlock -1

Ø  hard memlock -1 (内存)

 

2./etc/security/limits.d/20-nproc.conf

Ø  soft nproc 131072

当设置了“/etc/security/limits.d/20-nproc.conf”时,参数有时候不一定生效,因为在不同的机器中可能还有个Limits.d,下面有个配置文件优先级比limits.d/20-nproc.conf高,有些机器不一定是20,可能是其他的值。此时要将值设高一些,然后检查底下limits.d下面这篇文件中是否设置,如果没有则要把这个值设高,如果设低的话,limits.d.conf里设高也没有用。

 

 

二、数据库配置优化

数据库主要包含以下几个参数:

Shared_buffer

1)小内存(32G)的机器上配置4GB~8GB即可;

2)小内存的机器(>32G),配置8GB即可。

通常Shared_buffer配置4GB~8GB即可。PostgreSQL是使用这个文件缓存做的,如果Shared_buffer设大,缓存有两份。

 

Work_mem

1)通常保持默认的4MB即可;

2)如果机器内存很多,可以设置为64MB,通常不要太大,防止发生OOM。

 

Maintenance_work_mem:

可以在Session级别设置,当手工建索引或Vacuum慢时,可以把这个参数在Session级别调大。

 

Wal_buffers

通常保持默认值-1即可,-1表示会自动根据shared_buffer的大小而自动设置一个合适的大小,最大不要超过WAL文件的大小,如16MB。

 

Max_connections

可以设置的大一些,如5000,因为修改这个参数需要重启机器。

 

时间上还有很多的其他参数,如一些超时参数,防止长时间发呆的连接,防止长时间发呆的事务等,具体详情可关注PostgreSQL中文社区的培训认证考试PCA、PCP、PCM。

 

 

三、日常操作

数据库日常操作及对应语句:

查看数据库版本

select version();

查看数据库的启动时间

select pg_postmaster_start_time();

查看最后load配置文件的时间

select pg_conf_load_time();

显示数据库时区

show timezone;

查看有哪些数据库

psql –l

查看当前用户

select user; select current_user, session_user;

current_user, session_user指不带括号的函数。

查看当前连接的数据库名称

select current_catalog, current_database ();

查看当前客户端的IP及端口

select inet_client_addr(), inet_client_port();

查看当前数据库服务器的IP及端口

select inet_server_addr(), inet_server_port();

查询当前session的后台服务进程的pid

select pg_backend_pid();

查看参数配置

1)show shared_buffers

2)select current_setting('shared_buffers');

查看当前正在写的WAL文件

1)9.X: select pg_xlogfile_name(pg_current_xlog_location());

2)>=10版本: select pg_walfile_name(pg_current_wal_lsn ());

查看当前WAL的buffer还有多少字节没有刷到磁盘中

9.X:selectpg_xlog_location_diff(pg_current_xlog_insert_location(), pg_current_xlog_location());

>=10版本: : select pg_wal_lsn_diff(pg_current_wal_insert_lsn (), pg_current_wal_lsn ());

查看数据库实例是否正在做基础备份

select pg_is_in_backup(), pg_backup_start_time() ;

当前数据库实例是Hot Standby状态还是正常数据库状态

1)select pg_is_in_recovery();

如果是备库显示true,否则是主库。

2)pg_controldata |grep state

指控制文件,在生产情况“in production”情况下是主库,在恢复状态下是备库。

查看数据库的大小

select pg_database_size('osdba');

查看表的大小

1)select pg_size_pretty(pg_relation_size('ipdb2')) ;

2)select pg_size_pretty(pg_total_relation_size('ipdb2')) ;

查看某个表上索引的大小

1)select pg_size_pretty(pg_indexes_size('ipdb2'));

2)“ipdb2”指表名。

查看表空间的大小

1)select pg_size_pretty(pg_tablespace_size('pg_global'));

2)select pg_size_pretty(pg_tablespace_size('pg_default'));

查看表对应的数据文件

select pg_relation_filepath('test01');

让配置生效

1)pg_ctl reload

2)select pg_reload_conf();

切换Log日志文件

select pg_rotate_logfile();

切换WAL日志文件

1)9.x: select pg_switch_xlog();

2)>=10版本: select pg_switch_wal();

手工产生checkpoint

checkpoint;

查询正在运行的SQL(也能看到等待事件)

select * from pg_stat_activity;

取消一个长时间运行的查询SQL(非DML)

select pg_cancel_backend(pid);

终止一个进行运行的SQL(包括DML)

select pg_terminate_backend(pid);

杀掉除自己之外的连接(危险)

Select usename,datname, client_addr, pg_terminate_backend(pid) from pg_stat_activity where pid<> pg_backend_pid();

查看备库

select * from pg_stat_replication;

暂停备库的wal日志应用

select pg_xlog_replay_pause();

继续备库的wal日志应用

pg_xlog_replay_resume();

检查备库的wal日志应用是否暂停了

pg_is_xlog_replay_paused();

 

 

四、运维方案

(一)制定运维整体方案

制定完善运维整体方案,包括运维环境监控、日常数据库管理、数据库备份与恢复、性能监控、性能调优。

运维环境监控:包括 CPU是否过高、IO是否过忙、网络监控(网络流量是否过大)、磁盘空间监控、数据库年龄监控(如果数据年龄超了,数据库会停止工作)、表和物化视图上索引的数量、数据库级的统计信息。

日常数据库管理:包括实例状态检查、PG监听是否正常、WAL日志检查(是否出现爆增还爆减)、表空间检查、日志检查(是否报错)、备份有效性检查的方法。

数据库备份与恢复:包括备份策略设定、物理备份、逻辑备份(库表小做逻辑备份)、备份脚本、恢复脚本或恢复操作过程、如何防止误删除(是否架建延持备库)。

性能监控:包括检查等待事件、磁盘IO监控、TOP 10 SQL、数据库的每秒查询的行、插入的行、删除的行、更新的行。

性能调优:包括OS层面优化、PG参数优化、SQL优化、IO优化、架构优化:如读写分离、分库分表。

上述工作都需要提前做好,以保证后续正常运维。

 

(二)运维的工作

日常运维工作包括:

• 表、索引、物化视图、数据库、表空间的大小,表空间剩余可用空间;

• 数据库年龄、表的年龄;

• 表,物化视图的索引数量;

• 索引扫描次数;

• 表、物化视图、索引膨胀字节数,膨胀比例;

• Deadtuple;

• 序列剩余次数;

• HA,备份,归档,备库延迟状态;

• 错误日志统计;

• 事件触发器、触发器的情况;

• Unlogged table的情况,如果是9.X版本之前,了解Hash Index情况;

• 锁等待;

• 活跃度,Active, Idle, Idle in transaction状态会话数,剩余可用连接数;

• 带事务号的长事务,2PC事务;

• 网卡利用率,CPU利用率,IO利用率,内存利用率;

• 慢SQL及当时的Analyze执行计划;

• TOP SQL;

• 数据库级别统计信息:回滚数,提交数,命中率,死锁次数,IO TIME,Tuple DML次数。

 

上一篇:ECS 8080端口连接拒绝问题排查


下一篇:ECS控制台云资源分组管理新姿势---全局标签