PostgreSQL Database Maintenance Tips

wd@qunar

About Me & PG in Qunar

• 2010 年加⼊入 Qunar，⺫⽬目前负责公司的 CPC/CPM ⼲⼴广告服 务、度假⽆无线部门和 PGDBA 团队。

• Qunar ⺫⽬目前⼤大概有上百台 PG 服务器，涉及到的公司业 务⼤大概⼀一年流⽔水⼏几百个亿，业务线有机票、酒店、门 票、度假、团购、呼叫中⼼心等。

表索引膨胀

• 多版本控制 • 检查参考 SQL: https://wiki.postgresql.org/wiki/

Show_database_bloat

表索引膨胀

• 使⽤用 Autovacuum

• http://www.postgresql.org/docs/current/static/routinevacuuming.html#AUTOVACUUM

• 基于 database 和 table 级别的参数配置

• https://devcenter.heroku.com/articles/heroku-postgres-database-tuning

=# ALTER TABLE users SET (autovacuum_vacuum_threshold = 50); ALTER TABLE =# ALTER TABLE users SET (autovacuum_vacuum_scale_factor = 0.2); ALTER TABLE =# ALTER DATABASE dd5ir2j6frrtr0 SET vacuum_cost_limit = 300; ALTER DATABASE

表索引膨胀

• Vacuum full && Vacuum && reindex

• create index concurrently • primary key = not null, default bind to a seq, with uniq index

db 资源的消耗

• Slow query vs Freq. query • pg_stat_statements

• what-logging-has-least-overhead • 使⽤用 rsyslog

• 多⾏行问题

db 资源的消耗

-> 使⽤用 pgbench 情况 no logging: 95.812276 tps pg_stat_statements: 94.818323 tps full logging to local file in $PGDATA: 96.345291 tps full logging to file on another disk: 96.651113 tps full logging to syslog (logging to another disk): 95.866050 tps full logging to remote syslog: 95.187785 tps

->⼤大 sql 情况 no logging: 86.519867 tps pg_stat_statements: 86.679150 tps full logging to local file in $PGDATA: 80.970747 tps full logging to file on another disk: 77.005846 tps full logging to syslog (logging to another disk): 63.636626 tps full logging to remote syslog: 76.662515 tps

索引使⽤用情况

• Duplicate indexes • Useless indexes • Index bloat • Seq scan count

https://wiki.postgresql.org/wiki/Index_Maintenance

索引使⽤用情况

数据来⾃自于 pg_stat_all_tables

使⽤用 pgbouncer 代替程序连接池

• 连接池 vs 短链接

使⽤用 pgbouncer 代替程序连接池

• 问题被解决了?

• pgb 的监控

⼏几个 SQL Tips

• psql copy | some command vs 先 copy 到本地⽂文 件，再做处理

• 使⽤用主键分⻚页 • update t1 set a = 1 where a <> 1 • select count(*) from t1 vs xxxx

Q&A?