postgres in production.2014

© 2014 EDB All rights reserved. 1

Postgres in Production – Enterprise Best Practices


• Explore key Postgres usage patterns, to expose− Challenges users face in adoption, development, deployment

and maintenance− Frequently used tuning parameters− Common database maintenance practices− Database back-up and recovery strategies

• Provide empirical data to help you anticipate needs during your database lifecycles

• Share ideas and resources to help you address problems before they occur

Objectives


• EnterpriseDB Overview (quick)

• Postgres Support Experiences Explore what kinds of issues your peers are experiencing with Postgres, and when they’re encountering those issues

• Database Health Checks

Share what we’ve learned in the field about how Postgres databases are deployed, tuned and supported

• Summary and Useful Resources

• Q&A

Agenda

© 2014 EnterpriseDB Corporation. All rights reserved. 4

Brief EDB Overview


Postgres Plus Advanced Server Postgres Plus

Cloud Database

High AvailabilityPerformanceManagement

REMOTEDBA 24x7

SUPPORTPROFESSIONAL

SERVICES

TRAINING

EDB Serves All Your Postgres Needs

PostgreSQL

Security


POSTGRESinnovation

ENTERPRISE reliability

24/7 support

Services & training

Enterprise-class features & tools

Indemnification

Product road-map

Control

Thousands of developers

Fast development

cycles

Low cost

No vendor lock-in

Advanced features

Enabling commercial adoption of Postgres


Postgres Support Experiences


• Adoption of Postgres is growing fast− More and more Postgres users are not not ‘dyed in the wool’ Postgres

users – they come from Oracle, MS SQL, DB2, etc.

• Postgres is moving into complex, mission-critical applications− Question patterns are changing from simple to complex− Expertise level is changing – when the customer calls, it is with a

(more) difficult problem or urgent issue

• How do we make Postgres users even more successful?− Targeted white papers− Targeted training for support team− Pro-active training for users as part of our customer enablement

process− Balanced product strategy of new features, new capabilities and

usability

Why We Analyze Support Tickets


Ticket Volumes & Multi Year Growth

• Q1 2014: ~ 2500 total customers• Analysis in this presentation is based on a representative

sample of tickets raised over a 12 months period


Observations

• Postgres Plus Advanced Server: Strong emphasis on proprietary or commercially supported OSS

• PostgreSQL: Significantly less emphasis on proprietary or commercially supported OSS – even though the customer has commercially supported Postgres Distribution

Tickets and Platforms


When do customers call?


What Do Customers Call About?

POC Dev Deployment Maintenance0%

10%

20%

30%

40%

50%

60%

70%

80%

90%

100%

Tuning

Product Awareness

How to

Enhancements

Corruption

Bug

# Tickets PhaseCategory POC Dev Deployment Maintenance Grand TotalBug 8% 22% 9% 7% 10%Corruption 0% 0% 0% 6% 2%Enhancements 3% 4% 1% 0% 1%How to 41% 33% 41% 29% 35%Product Awareness 46% 39% 44% 39% 41%Tuning 3% 2% 5% 19% 10%Grand Total 100% 100% 100% 100% 100%


Where Do Users Need Help# Tickets PhaseCategory POC Dev Deployment Maintenance Grand TotalConnectors 0% 2% 1% 0% 3%

Bug 0% 0% 0% 0% 1%How to 0% 1% 0% 0% 1%Product Awareness 0% 1% 0% 0% 1%

Database 10% 9% 17% 37% 73%Bug 1% 2% 1% 2% 6%Corruption 0% 0% 0% 2% 2%Enhancements 0% 0% 0% 0% 1%

How to 4% 3% 6% 10% 23%Product Awareness 4% 3% 8% 15% 31%Tuning 0% 0% 1% 7% 9%

Replication 1% 4% 4% 2% 12%Bug 0% 1% 0% 0% 2%How to 0% 0% 3% 1% 4%Product Awareness 1% 2% 1% 0% 4%Tuning 0% 0% 0% 1% 1%

Utilities 1% 3% 6% 3% 12%Bug 0% 1% 1% 0% 2%Enhancements 0% 0% 0% 0% 0%How to 1% 1% 3% 1% 6%Product Awareness 0% 1% 3% 1% 5%

Grand Total 12% 17% 28% 42% 100%


Deployment/Database/Product Awareness & How To − How to set up connection pooling?− How to set up Hot Standby and Streaming Replica?− How to add table spaces?− How to partition?− How to upgrade from major version?

Maintenance/Database/Product Awareness & How To− How to find bloat in tables & indexes?− How to enable auditing selectively without performance impact?− What are the effects of changes to the values of postgresql.conf

file parameters wal_writer_delay, bgwriter_delay?− What is the correct configuration of hba_conf?

Select Top questions (not in rank order)


• The majority of questions arise after PoC and Development

• Deployment and Maintenance are the more challenging phases

• Plan early to understand necessary practices− Backup, recovery, high availability strategies− Configuration parameter tuning to support growth− Vacuum and bloat− Connection pooling

Best Practices and Lessons from Support


Lessons Learned from the AHC


• Systematic top-to-bottom review of the customer’s Postgres production installation

• Identifies issues and improvement opportunities in:− Performance− Scalability and support for business and data growth− Availability – Backup strategies, recovery, replication and

failover− Maintenance and tuning− Upgrades

• The following analysis is based on a set of 20 AHCs executed in 2013

Architectural Health Check


Red bars: experts expected top parameters, including vacuum_cost_limit, log_line_prefix, log_min_duration_statement,log_checkpoints

Top Database Parameter Adjustments – Ordered by Frequency


Kernel Parameters

vm.d

irty_

back

grou

nd_r

atio

vm.d

irty_

ratio

kern

el.s

hmm

ax

swap

pine

ss

kern

el.s

hmal

l

vm.o

verc

omm

it_m

emor

y

kern

el.m

m.tr

ansp

aren

t_hu

gepa

ge.e

nabl

ed

kern

el.m

m.tr

ansp

aren

t_hu

gepa

ge.d

efra

g0

2

4

6

8

10

12

14

16


Backup and Standby

hot47%

warm24%

None18%

cold12%

Standby ApproachPhysical Only36%

Logical Only29%

Physical and Log-

ical14%

FS Snap-shot7%

None14%

Backup Approach43% of customers could not execute PITR (prior to AHC) – some had NO backup in place

30% of customers would have been slow or unable to recover from failure


Maintenance Tasks

Unused Indexes39%

Bloated Indexes30%

Bloated Tables13%

Missing Indexes

9%

Missing PK9% Bloat management

(43%) is a key production

problem specific to

Postgres


• Majority of Postgres production deployments are on Linux based OS

• Storage system is a key deployment consideration – DAS/SAN/NFS

• OS System Adjustments (dirty_background_ratio & dirty_ratio) are also a major adjustment (70% & 45%)

Observations from the AHCs


Stage: post-production

Primary Issue:

• Outage due to replication failure - insufficient log info to determine true root cause

Potential Pain Points

• Concerns have reached limits of scale and meeting anticipated growth demands

Recommendations

• Backup and recovery strategies

• Implement continuous archive recovery in addition to streaming replication

• Revised backup strategy with PITR

• Tune auto-vacuum settings and manually vacuum large tables

• Indexes – create concurrent indexes, REINDEX to reduce bloat

• Parameter tuning – various for kernel, memory settings and WALs

AHC Case Study #1

Use Case: on-line advertising service with hosted chat


Stage: pre-deployment

Primary Issues:

• Performance in returning results and timeouts

• No backup strategy in place


• Expected data volume growth and increased analysis – scalability concerns

Recommendations

• Kernel param updates (8)- kernel.shmmax, kernel.shmall, vm.dirty_ratio, vm.dirty_background_ratio,…

• Database param changes (11) -effective_cache_size, shared_buffers, bgwriter_lru_maxpages, wal_buffers, work_mem, …

• Re-architect specific heavily-used database function

• Detailed backup and availability strategies

AHC Case Study #2

Use Case: data warehouse reporting at customer sites


Stage: post-production

Primary Issues:

• System performance

• Running out of connections

• Slow queries/ locked tables

• Advice on new SW release


• Concern for increased workloads and scalability

Recommendations

• max_connections was actually too high, work_mem too low

• Increase checkpoint parameters− checkpoint_segments,

checkpoint_timeout, checkpoint_completion_ target, checkpoint_warning

• Tune storage-related params (SAN) - DB, memory, file system work_mem, random_page_cost

• Upgrade for performance and security improvements

• As workload increases, increase RAM and re-tune related memory config. parameters

AHC Case Study #3

Use Case: claims mgmt. system and reporting application


Summary and Useful Resources


• Postgres users consistently experience different kinds of challenges throughout the database lifecycle− Postgres product knowledge is a critical need at all lifecycle

stages

• Many DBAs would benefit from better knowledge of tuning Postgres for their storage infrastructures

• Tuning databases early in the lifecycle can save a lot of time, hassle and user friction after they’re deployed

• Many organizations lack appropriate backup and recovery strategies

Summary


• Why do you need backup and recovery? Backup and Recovery Strategies protect you in case of:− Catastrophic Device Failure− Site Failure− Maintenance− Operator Error− Compliance− Data Corruption

• Key takeaways:− Logical backup provides granularity in objects (tables, table

spaces, databases); Physical backups provide granularity in time for PITR.

− Understand requirements before diving into technology solution

Backup and Recovery Strategies


• Postgres provides− Logical backup (pg_dump)− Binary/physical backup (Filesystem Snapshots &

pg_basebackup)− WAL (write ahead logs)− Streaming replication− Delayed replication

• Advanced solutions, such as RHCS, Veritas Clustering , EFM and EDB BART - (now in Beta) augment the basic capabilities

Backup and Recovery Strategies


• Performance, Scalability, Availability

• Three-day in-depth Postgres database review

• Analysis of platform, database architecture and application usage

• Provides tuning and management guidance based on EDB’s expert best practices

Get an Architectural Health Check when you are− Not 100% sure about your backup/recovery strategy

− Unsure your database is optimally tuned for your apps

− Moving Postgres databases to the Cloud

− Upgrading your servers to multi-core, high memory devices

− Experiencing high growth in application usage

− Experiencing deterioration of read and/or write throughput

Architectural Health Check

http://enterprisedb.com/services/packaged-services/health-check




For more information about an Architectural Health Check, please visit www.enterprisedb.com/ahc or email us - [email protected]

http://www.enterprisedb.com/ahc

postgres in production.2014

Technology