Scaling PostgreSQL with Hot Standby - 2ndQuadrant

arizonahoopleData Management

Nov 28, 2012 (4 years and 9 months ago)

436 views

Greg Smith - ©
2ndQuadrant Limited 2010
Scaling PostgreSQL with Hot
Standby
PostgreSQL West 2010
Greg Smith - ©
2ndQuadrant Limited 2010
Hot Standby

New in PostgreSQL 9.0

Allows connections to server while archive
recovery is in progress

Connections will not be terminated if/when we
move from recovery to normal running at
switchover/failover time

hot_standby = on (not the default)

Works with either streaming or
file-based replication
Greg Smith - ©
2ndQuadrant Limited 2010
User's Overview

Connections work normally – no special options

Transactions will be read only

Cannot write new data to the database

Cannot write new WAL data

Cannot allocate a TransactionId

Cannot hold any table lock higher than AccessShare

No row level locking

No temporary tables

No two-phase commit (prepared transactions)

No access to sequences (i.e. no nextval())
Greg Smith - ©
2ndQuadrant Limited 2010
What works?!?

SELECTs, Cursors including sort files

MVCC works normally

Recovery changes made by a single “writer”

Don't normally block “readers”

LOAD – so many plugins work normally

SET, RESET

Serializable transactions, savepoints etc
Greg Smith - ©
2ndQuadrant Limited 2010
Use Cases

High availability with active monitoring

Offload long running reports

Materialize views

Load balance small read-only queries
Greg Smith - ©
2ndQuadrant Limited 2010
Prioritizations

Keep the standby current for failover (good)

Long running reports on the standby (fast)

Avoid adding overhead to the master (cheap)

Good, fast, cheap: pick two
Greg Smith - ©
2ndQuadrant Limited 2010
Query Conflicts

Primary: Drop Database X

Standby: Query on database X

Cannot do both

Action on primary has already happened, so
whatever occurs, WAL recovery must always win
Greg Smith - ©
2ndQuadrant Limited 2010
Causes of Query Conflicts

Cleanup records (“Snapshot too old” error)

Access Exclusive Locks (LOCK, DDL)

Drop Database

Drop Tablespace

Very, very long queries (wraparound issue)

Buffer cleanup (VACUUM, HOT)

Others can normally be avoided; this cannot.
Greg Smith - ©
2ndQuadrant Limited 2010
Standard query visibility

MVCC keeps multiple row versions

Active query transactions are noted

Row cleanup avoids touching active data
Greg Smith - ©
2ndQuadrant Limited 2010
Hot Standby Query Visibility

Queries executing on standby are independent

Master does not know what is running

DROP TABLE will not respect open locks

VACUUM cleanup will prune active rows
Greg Smith - ©
2ndQuadrant Limited 2010
vacuum_defer_cleanup_age

Set in master postgresql.conf

Units are transactions

Monitor txid_current() to estimate times

Also delays HOT cleanup

Will cause cleanup bloat on the master
Greg Smith - ©
2ndQuadrant Limited 2010
max_standby_*_delay

Set in standby postgresql.conf

max_standby_archive_delay

Changes from a shipped archive file

max_standby_streaming_delay

Changes from Streaming Replication

Defaults are both 30 seconds

Similar to statement_timeout

Changes eventually must be applied

Maximum amount of time to wait for them
Greg Smith - ©
2ndQuadrant Limited 2010
max_standby_*_delay

0 always cancels queries immediately

Small values prioritize quick failover

-1 means wait forever for queries to finish

Large values or -1 for reporting server

Keep master and standby clocks synchronized
Greg Smith - ©
2ndQuadrant Limited 2010
Query Conflict Avoidance

Connect to primary via dblink

Hold open a snapshot while query executes

SELECT sleep(60);

Similar to how a running query impacts MVCC
Greg Smith - ©
2ndQuadrant Limited 2010
Optimizing for long queries

Avoid cleanup - vacuum_defer_cleanup_age

Will bloat the master

Set a large value for max_standby_*_delay

Will cause standby to fall behind

Hold an open snapshot

Bloats the master only when running
Greg Smith - ©
2ndQuadrant Limited 2010
Optimizing for fast failover

Low value for max_standby_*_delay
Greg Smith - ©
2ndQuadrant Limited 2010
Optimizing for load balancing

Low max_standby_*_delay for fresh results

Application should expect cancellation
Greg Smith - ©
2ndQuadrant Limited 2010
Routing reads and writes

Writes must go to master

Reads execute against master or any standby

Application may know

Application servers may support this concept

JDBC

Django

Database proxy servers can do this routing

pgpool-II 3.0 “tastes” queries

Hard to solve in all cases

Where do functions (stored procedures) go?
Greg Smith - ©
2ndQuadrant Limited 2010

Hot”
Read-Only
Node
Architecture: Read Scaling

Many read copies with slight lag

Each is also a potential failover node

Not suitable for long reports

Hot”
Read-Only
Node
pgpool-II
router
Writes
Reads
Primary
Node
Greg Smith - ©
2ndQuadrant Limited 2010
Failover
Node
Architecture: Reporting Server

Rolling Reporting Server(s)

Live servers runs queries

Other servers provide failover
capability for Primary
Primary
Node

Hot”
Read-Only
Reporting
Node
Archive
Greg Smith - ©
2ndQuadrant Limited 2010
Architecture: Relay Server

Archive data streamed to a standby

Ship the result to a second layer standby

pg_streamrecv

https://github.com/mhagander
Primary
Node
Hot Standby
Hot Standby
Greg Smith - ©
2ndQuadrant Limited 2010
Work in progress

Automatic snapshot export to master

pg-blender: projects.2ndQuadrant.com

Easier provisioning/failover

repmgr: projects.2ndQuadrant.com

Improved monitoring

Synchronous replication

Expected for PostgreSQL 9.1

Master and standby no longer decoupled
Greg Smith - ©
2ndQuadrant Limited 2010
For more information
www.2ndQuadrant.com/books