PostgreSQL性能优化综合

【软件环境】

CentOS 5 x64

Postgresql 9.1.3

plproxy2.3

pgbouncer1.42

【测试模型】

设计一个包含INSERT,UPDATE,SELECT语句的业务模型用于本优化案例.

业务逻辑 :

【测试表】

create table user_info

(userid int,

engname textcnname textoccupation textbirthday datesignname textemail textqq numericcrt_time timestamp without time zonemod_time timestamp without time zone

);

create table user_session

logintime timestamp(0) without time zonelogin_count bigint defaultlogouttime timestamponline_interval interval interval '0'

create table user_login_rec

login_time timestamp without time zoneip inet

create table user_logout_rec

logout_time timestamp without time zone);

【初始化数据】

insert into user_info useridengnamecnnameoccupationbirthdaysignnameemailqqcrt_timemod_time)

select generate_series120000000),

'digoal.zhou''德哥''DBA''1970-01-01'

E'公益是一辈子的事,I\'m Digoal.Zhou,Just do it!''digoal@126.com'276732431clock_timestamp(),0)">NULL;

user_session );

set work_mem='2048MB';

maintenance_work_mem;

alter table user_info add constraint pk_user_info primary key );

alter table user_session add constraint pk_user_session primary key 【业务函数】

-- 模拟用户登录的函数

create or replace function f_user_login

i_userid OUT o_userid OUT o_engname textOUT o_cnname textOUT o_occupation textOUT o_birthday dateOUT o_signname textOUT o_email textOUT o_qq numeric

as $BODY$

declare

begin

select useridqq

into o_userido_engnameo_cnnameo_occupationo_birthdayo_signnameo_emailo_qq

from user_info where userid=i_userid user_login_rec login_timeip values nowinet_client_addr());

update user_session logintimelogin_count+1returnend$BODY$

language plpgsql;

模拟用户退出的函数

f_user_logout

OUT o_result int

user_logout_rec logout_time logouttimeonline_interval+(()-logintimeo_result :=exception

when others then

;

【搭建测试模型】

1. 搭建环境,安装Postgresql9.1.3数据库软件并初始化数据库(略).

2. 调整数据库postgresql.conf参数.打开日志,sql 统计,跟踪,以及性能参数,便于优化过程中取证.

监听IPv4的所有IP.

listen_addresses '0.0.0.0'

最大允许1000个连接.

max_connections 1000

为超级用户保留3个可用连接.

superuser_reserved_connections 3

默认的unix socket文件放在/tmp修改为$PGDATA以确保安全.

unix_socket_directory '/pgdata/digoal/1921/data02/pg_root'

默认的访问权限是0777修改为0700更安全.

unix_socket_permissions 0700

Linux下面默认是2小时. tcp的keepalives包发送间隔以及重试次数,0)">如果客户端没有响应,0)">将主动释放对应的SOCKETtcp_keepalives_idle 60

tcp_keepalives_interval 10

_keepalives_count 6

大的shared_buffers需要大的checkpoint_segmentsSystem V共享内存资源.

这个值不需要设的太大,0)">因为Postgresql还依赖操作系统的cache来提高读性能,0)">另外,0)">写操作频繁的数据库这个设太大反而会增加checkpoint压力.

shared_buffers 512MB

这个值越大,0)"> VACUUM CREATE INDEX的操作越快,0)">当然大到一定程度瓶颈就不在内存了,0)">可能是cpu例如创建索引.

这个值是一个操作的内存使用上限,0)">而不是一次性分配出去的.并且需要注意如果开启了autovacuum最大可能有autovacuum_max_workers*maintenance_work_mem的内存被系统消耗掉.

maintenance_work_mem 一般设置为比系统限制的略少,0)">ulimit -a : stack size kbytess10240

max_stack_depth 8MB

手动执行vacuum操作时,0)">默认是没有停顿执行到底的,0)">为了防止VACUUM操作消耗太多数据库服务器硬件资源,0)">这个值是指在消耗多少资源后停顿多少时间,以便其他的操作可以使用更多的硬件资源.

vacuum_cost_delay 10ms

#vacuum_cost_page_hit = 1 # 0-10000 credits

#vacuum_cost_page_miss = 10 # 0-10000 credits

#vacuum_cost_page_dirty = 20 # 0-10000 credits

#vacuum_cost_limit = 200 # 1-10000 credits

默认bgwriter进程执行一次后会停顿200ms再被唤醒执行下一次操作,0)">当数据库的写操作很频繁的时候,0)">可能太长,0)">导致其他进程需要花费过多的时间来进行的操作.

bgwriter_delay 10ms

如果需要做数据库WAL日志备份的话至少需要设置成archive级别,0)">如果需要做hot_standby那么需要设置成由于这个值修改需要重启数据库,0)">所以先设置成比较好.当然意味着记录得更详细,0)">如果没有打算做设置得越低性能越好.

wal_level hot_standby

wal buffers默认是-根据的设置自动调整*%.最大限制是XLOGsegment_sizewal_buffers 16384kB

多少个xlog file产生后开始操作,0)">允许shared_buffer中的被频繁访问的脏数据存储得更久.一定程度上可以提高数据库性能.但是太大的话会导致在数据库发生的时候需要处理更多的脏数据带来长时间的IO开销.太小的话会导致产生更多的文件(因为full page writesonCHECKPOINT后的第一次块的改变要写全块,0)"> checkpoint越频繁,0)">越多的数据更新要写全块导致产生更多).

checkpoint_segments 64

这个和的效果是一样的,0)">只是触发的条件是时间条件.

checkpoint_timeout 5min

归档参数的修改也需要重启数据库,0)">所以就先打开吧.

archive_mode on

这个是归档调用的命令,0)">我这里用date代替,0)">所以归档的时候调用的是输出时间而不是拷贝wal文件.

archive_command '/bin/date'

如果要做hot standby这个必须大于并且修改之后要重启数据库所以先设置为32.

_wal_senders 32

这是个standby 数据库参数,0)">为了方便角色切换,0)">我一般是所有的数据库都把他设置为on 的.

hot_standby 这个参数是说数据库中随机的PAGE访问的开销占seq_page_cost的多少倍 seq_page_cost默认是1.其他的开销都是的倍数.这些都用于基于成本的执行计划选择.

random_page_cost 2.0

和上一个参数一样,0)">用于基于成本的执行计划选择.不是说会用多少它只是个度量值.表示系统有多少内存可以作为操作系统的.越大的话,0)">数据库越倾向使用index这种适合random访问的执行计划.

effective_cache_size 12000MB

下面是日志输出的配置.

log_destination 'csvlog'

logging_collector on

log_directory '/var/applog/pg_log/digoal/1921'

log_truncate_on_rotation log_rotation_age 1d

log_rotation_size 10MB

这个参数调整的是记录执行时间超过秒的sql到日志中,0)">一般用于跟踪哪些执行时间长.

log_min_duration_statement 1000ms

记录每一次到日志中.

log_checkpoints 记录锁等待超过秒的操作,0)">一般用于排查业务逻辑上的问题.

log_lock_waits deadlock_timeout 1s

记录DDL语句,0)">一般用于跟踪数据库中的危险操作.

log_statement 'ddl'

这个原本是1024表示跟踪的在的地方截断,0)">超过将无法显示全2048会消耗更多的内存(基本可以忽略),0)">不过可以显示更长的

track_activity_query_size 2048

就是打开的,0)"> log_autovacuum_min_duration 记录所有的操作.

autovacuum log_autovacuum_min_duration 0

这个模块用于记录数据库中的最近的条以及这些的统计信息,0)">如执行了多少次,0)">总共耗时是多少.一般用于发现业务上最频繁调用的是什么,0)">有针对性的进行优化.

shared_preload_libraries 'pg_stat_statements'

custom_variable_classes pg_stat_statementsmax 1000

pg_stat_statementstrack all

其他参数值默认.

这些参数的详细解释如有疑问请参考官方文档.

3. 新建数据库用户digoal,库digoal. 并使用前面的测试模型新建表以及函数,初始化数据.

下面的测试过程中只测登陆部分,未测试退出部分,因为登陆过程已经包含了INSERT,SELECT. 基本上可以反映整个调优过程了.

【调优阶段1】

使用pgbench进行压力测试,发现瓶颈并合理优化.

1. pgbench用到的登陆脚本

cat login.sql


   
   
    
    \setrandom userid 20000000
   
   
   
   
    
    selectqq from user_info =:;
   
   
   
   
    
    (:());
   
   
   
   
    
    ;

2.pgbench用到的退出脚本

cat logout.sql

insert
  3. 压力测试

pgbench M simple r c 8f /homepostgrestestloginsql j n T 180h 172.163.33p 1921U digoal digoal

4. 压力测试结果

transaction type Custom query

scaling factor1

query mode simple

number of clients8

number of threadsduration s

number of transactions actually processed62675

tps348.084647including connections establishing)

tps 348.100337excluding connections establishingstatement latencies in milliseconds:

0.004577 \setrandom userid 20000000

12.963789 5.540750 insert 4.457834 update user_session 5. 瓶颈分析与优化

压力测试中查看数据库服务器的iostat -x

avgcpuuser nice system iowait steal idle

0.69 0.000.2524.1174.95

Device rrqms wrqms rs ws rsecs wsecs avgrqsz avgqusz await svctm util

ccissc0d0 6.001.5060.0040.000.016.671.00

ccissc0d0p1 c0d0p2 0.00

c0d0p3 c0d1 c0d2 638.5010.00217.50160.006444.0029.03152.58707.894.40100.10

c0d3 c0d4 c0d5 dm-0 866.506932.008.09446.26510.491.142340.00

操作系统的平均IO请求等待700多毫秒,Postgresql数据文件所处的块设备使用率100%. 存在严重的IO性能瓶颈.

使用pgfincore降低读的物理IO请求.

pgfincore的相关文章可参考如下,

《use posix_fadvise pre-cache frequency data》

http://blog.163.com/digoal@126/blog/static/163877040201062944945126/

《a powerful upgrade from pgfincore 1.0》

http://blog.163.com/digoal@126/blog/static/1638770402011630102117658/

《TOAST table with pgfincore》

http://blog.163.com/digoal@126/blog/static/16387704020120524144140/

pgfincore所起的作用类似EnterpriseDB的InfiniteCache或者熟悉Oracle的朋友可能更易于接受的KEEP BUFFER POOL.

载入os cache

digoal=> reltoastrelid pg_class relname'user_info'reltoastrelid

---------------

16424

row)

relname oid=16424 relname

----------------

pg_toast_16421

\c digoal postgres

seYou are now connected to database "digoal" user "postgres".

=# select * from pgfadvise_willneed('pg_toast.pg_toast_16421');

relpath | os_page_size | rel_os_pages | os_pages_free

----------------------------------------------+--------------+--------------+---------------

pg_tblspc/16385/PG_9.1_201105231/16386/16424 | 4096 | 0 | 243865

(1 row)

# select * from pgfadvise_willneed('digoal.user_info');

relpath | os_page_size | rel_os_pages | os_pages_free

------------------------------------------------+--------------+--------------+---------------

pg_tblspc/16385/PG_9.1_201105231/16386/16421 | 4096 | 262144 | 243834

pg_tblspc/16385/PG_9.1_201105231/16386/16421.1 | 4096 | 262144 | 243834

pg_tblspc/16385/PG_9.1_201105231/16386/16421.2 | 4096 | 244944 | 243834

(3 rows)

# select * from pgfadvise_willneed('digoal.user_session');

pg_tblspc/16385/PG_9.1_201105231/16386/16431 | 4096 | 262144 | 243834

pg_tblspc/16385/PG_9.1_201105231/16386/16431.1 | 4096 | 33640 | 243834

(2 rows)

# select reltoastrelid from pg_class where relname='user_session';

reltoastrelid

---------------

# select * from pgfadvise_willneed('digoal.pk_user_session');

pg_tblspc/16385/PG_9.1_201105231/16386/16438 | 4096 | 109680 | 243865

# select * from pgfadvise_willneed('digoal.pk_user_info');

pg_tblspc/16385/PG_9.1_201105231/16386/16436 | 4096 | 109680 | 235567

(1 row)

PostgreSQL性能优化综合

猜你在找的Postgre SQL相关文章