Postgres in production.2014

Objectives • 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 © 2014 EDB All rights reserved. 2

Agenda • 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 © 2014 EDB All rights reserved. 3

Postgres Plus Advanced Server Postgres Plus Management Performance © 2014 EnterpriseDB Corporation. All rights reserved. 5 Cloud Database High Availability REMOTE DBA 24x7 SUPPORT PROFESSIONAL SERVICES TRAINING EDB Serves All Your Postgres Needs PostgreSQL Security

POSTGRES innovation Services & training © 2014 EnterpriseDB Corporation. All rights reserved. 6 ENTERPRISE reliability 24/7 support 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

Why We Analyze Support Tickets • 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 © 2014 EnterpriseDB Corporation. All rights reserved. 8

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 © 2014 EnterpriseDB Corporation. All rights reserved. 9

Tickets and Platforms 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 © 2014 EnterpriseDB Corporation. All rights reserved. 10

What Do Customers Call About? 100% 90% 80% 70% 60% 50% 40% 30% 20% 10% 0% POC Dev Deployment Maintenance © 2014 EnterpriseDB Corporation. All rights reserved. 12 Tuning Product Awareness How to Enhancements Corruption Bug # Tickets Phase Category POC Dev Deployment Maintenance Grand Total Bug 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 Phase Category POC Dev Deployment Maintenance Grand Total Connectors 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% © 2014 EnterpriseDB Corporation. All rights reserved. 13

Select Top questions (not in rank order) 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? © 2014 EnterpriseDB Corporation. All rights reserved. 14

Best Practices and Lessons from Support • 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 © 2014 EnterpriseDB Corporation. All rights reserved. 15

Architectural Health Check • 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 © 2014 EnterpriseDB Corporation. All rights reserved. 17

100% 90% 80% 70% 60% 50% 40% 30% 20% 10% 0% Top Database Parameter Adjustments – Ordered by Frequency effec ve_cache_size Top Database Parameter Adjustments - Ordered by Frequency wal_buffers maintenance_work_mem shared_buffers bgwriter_lru_maxpages work_mem random_page_cost checkpoint_segments bgwriter_lru_mul plier cpu_tuple_cost checkpoint_comple on_target checkpoint_ meout © 2014 EnterpriseDB Corporation. All rights reserved. 18 Red bars: experts expected top parameters, including vacuum_cost_limit, log_line_prefix, log_min_duration_statement, log_checkpoints checkpoint_warning autovacuum_vacuum_cost_delay autovacuum_vacuum_threshold prefix waitsstatement factor line_lock_scale_log_log_dura vacuum_on_log_autovacuum_min_autovacuum_nap me log_autovacuum_min_dura on

Backup and Standby Snapshot 7% Backup Approach © 2014 EnterpriseDB Corporation. All rights reserved. 20 Standby Approach hot 47% None 18% cold 12% warm 23% Physical Only 36% Logical Only 29% FS Physical and Logical 14% None 14% 43% 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 Missing Indexes Bloated Tables © 2014 EnterpriseDB Corporation. All rights reserved. 21 Unused Indexes 39% Bloated Indexes 30% 13% 9% Missing PK 9% Bloat management (43%) is a key production problem specific to Postgres

Observations from the AHCs • 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%) © 2014 EnterpriseDB Corporation. All rights reserved. 22

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 © 2014 EnterpriseDB Corporation. All rights reserved. 23 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 Potential Pain Points • Expected data volume growth and increased analysis – scalability concerns © 2014 EnterpriseDB Corporation. All rights reserved. 24 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 Potential Pain Points • Concern for increased workloads and scalability © 2014 EnterpriseDB Corporation. All rights reserved. 25 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 • 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 © 2014 EnterpriseDB Corporation. All rights reserved. 27

Backup and Recovery Strategies • 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 © 2014 EnterpriseDB Corporation. All rights reserved. 28

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 © 2014 EnterpriseDB Corporation. All rights reserved. 29

Architectural Health Check • 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 http://enterprisedb.com/services/packaged-services/health-check © 2014 EnterpriseDB Corporation. All rights reserved. 30

Postgres in production.2014

More Related Content

Similar to Postgres in production.2014

More from EDB

Recently uploaded

Postgres in production.2014