Upgrade to Pro
— share decks privately, control downloads, hide ads and more …
Speaker Deck
Features
Speaker Deck
PRO
Sign in
Sign up for free
Search
Search
How to Scale Postgres - Automation, Tuning & Sh...
Search
Lukas Fittl
June 23, 2020
Technology
0
640
How to Scale Postgres - Automation, Tuning & Sharding
Talk at Postgres Vision 2020
Lukas Fittl
June 23, 2020
Tweet
Share
More Decks by Lukas Fittl
See All by Lukas Fittl
What's Missing for Postgres Monitoring
lfittl
0
230
A Map For Monitoring PostgreSQL
lfittl
2
370
Monitoring Postgres at Scale
lfittl
1
410
Monitoring PostgreSQL at Scale
lfittl
4
250
Postgres Performance for App Developers
lfittl
2
280
GraphQL ❤ PostgreSQL -- P.S. aka BeatQL
lfittl
1
580
Hacking PostgreSQL to Gain SQL Parsing Superpowers
lfittl
1
560
PostgreSQL at a Web Startup
lfittl
3
590
Advanced pg_stat_statements: Filtering, Regression Testing & more
lfittl
4
760
Other Decks in Technology
See All in Technology
会社紹介資料 / Sansan Company Profile
sansan33
PRO
6
360k
ローカル環境でAIを動かそう!
falken
PRO
1
170
Introduction to Sansan for Engineers / エンジニア向け会社紹介
sansan33
PRO
5
38k
Digitization部 紹介資料
sansan33
PRO
1
3.8k
セキュリティSaaS企業が実践するCursor運用ルールと知見 / How a Security SaaS Company Runs Cursor: Rules & Insights
tetsuzawa
0
400
プラットフォームとしての Datadog / Datadog as Platforms
aoto
PRO
1
340
面接を通過するためにやってて良かったこと3選
sansantech
PRO
0
130
Redmineの意外と知らない便利機能 (Redmine 6.0対応版)
vividtone
0
1.1k
NW運用の工夫と発明
recuraki
1
790
ソフトウェアは捨てやすく作ろう/Let's make software easy to discard
sanogemaru
10
5.8k
TechBull Membersの開発進捗どうですか!?
rvirus0817
0
200
Cloud Run を解剖して コンテナ監視を考える / Breaking Down Cloud Run to Rethink Container Monitoring
aoto
PRO
0
110
Featured
See All Featured
The Art of Programming - Codeland 2020
erikaheidi
54
13k
Sharpening the Axe: The Primacy of Toolmaking
bcantrill
42
2.3k
Java REST API Framework Comparison - PWX 2021
mraible
31
8.6k
Why Our Code Smells
bkeepers
PRO
336
57k
Easily Structure & Communicate Ideas using Wireframe
afnizarnur
194
16k
Optimising Largest Contentful Paint
csswizardry
37
3.3k
Writing Fast Ruby
sferik
628
61k
How to train your dragon (web standard)
notwaldorf
92
6k
The World Runs on Bad Software
bkeepers
PRO
68
11k
Scaling GitHub
holman
459
140k
GitHub's CSS Performance
jonrohan
1031
460k
Creating an realtime collaboration tool: Agile Flush - .NET Oxford
marcduiker
30
2.1k
Transcript
@LukasFittl How to Scale Postgres: Automation, Tuning & Sharding
@LukasFittl
Scaling Postgres
Scaling Postgres
Automation Handling 100s of database servers
Consistency is key
Infrastructure as Code
Postgres Infrastructure as Code
Demo: Managing Configuration using Terraform
Cloud PaaS Synchronized Configuration Terraform
Cloud PaaS Synchronized Configuration Terraform Access Control (Roles, pg_hba.conf) Terraform
Cloud PaaS Synchronized Configuration Terraform Parameter Groups Access Control (Roles,
pg_hba.conf) Roles: Terraform HBA: Built-in Automatic Failover (for HA & Planned Updates) Built-in
Cloud PaaS Synchronized Configuration Terraform Parameter Groups Access Control (Roles,
pg_hba.conf) Roles: Terraform HBA: Built-in Automatic Failover (for HA & Planned Updates) Built-in Read Replicas Built-in
Cloud PaaS Synchronized Configuration Terraform Parameter Groups Access Control (Roles,
pg_hba.conf) Roles: Terraform HBA: Built-in Automatic Failover (for HA & Planned Updates) Built-in Read Replicas Built-in Backups Built-in
Cloud PaaS Synchronized Configuration Terraform Parameter Groups Access Control (Roles,
pg_hba.conf) Roles: Terraform HBA: Built-in Automatic Failover (for HA & Planned Updates) Built-in Read Replicas Built-in Backups Built-in Connection Pooling Manual Setup
Cloud PaaS Self-Managed VM Synchronized Configuration Terraform Parameter Groups ?
Access Control (Roles, pg_hba.conf) Roles: Terraform HBA: Built-in ? Automatic Failover (for HA & Planned Updates) Built-in ? Read Replicas Built-in ? Backups Built-in ? Connection Pooling Manual Setup ?
Cloud PaaS Self-Managed VM Synchronized Configuration Terraform Parameter Groups ?
Access Control (Roles, pg_hba.conf) Roles: Terraform HBA: Built-in ? Automatic Failover (for HA & Planned Updates) Built-in pg_auto_failover Read Replicas Built-in ? Backups Built-in ? Connection Pooling Manual Setup ?
pg_auto_failover: Simple, automated failover
pg_auto_failover
Demo: Postgres HA using pg_auto_failover
Tuning Making The Most Of Your Database Server
work_mem tuning
Out Of Memory vs Operations Spill To Disk
Temporary Files Written pg_stat_statements.temp_blks_written pg_stat_database.temp_bytes
Temporary Files Written (Per Query) log_temp_files = 0 Jan 20
09:18:58pm PST 28847 LOG: temporary file: path "base/pgsql_ pgsql_tmp28847.9", size 50658332 Jan 20 09:18:58pm PST 28847 STATEMENT: WITH servers AS ( SELECT …
When Sorts Spill To Disk, Increase work_mem However, be aware
of OOMs!
When you get a lot of Out of Memory Errors
Reduce work_mem!
VACUUM
autovacuum => SELECT pid, query FROM pg_stat_activity WHERE query LIKE
'autovacuum: %'; 10469 | autovacuum: VACUUM ANALYZE public.schema_columns 12848 | autovacuum: VACUUM public.replication_follower_stats 28626 | autovacuum: VACUUM public.schema_index_stats | (to prevent wraparound) (3 rows) pg_stat_activity
autovacuum pg_stat_progress_vacuum relid: OID of the table phase: current VACUUM
phase heap_blks_total: Heap Blocks Total heap_blks_scanned: Heap Blocks Scanned heap_blks_vacuumed: Heap Blocks Vacuumed …
Reduce autovacuum_vacuum_cost_delay To Increase VACUUM Speed 80 MB/s 8 MB/s
(20ms) (2ms) PG 12+ Older PG Default OS / Disk Reads
Use Table Partitioning For Append-Only + Delete Workloads (e.g. Timeseries)
Checkpoints
Data Directory WAL WAL WAL Buffer Cache Checkpointer WAL Checkpoints
Are Important For I/O Tuning
16688 LOG: checkpoint starting: xlog xlog = WAL exceeded max_wal_size,
checkpoint has to happen quickly time = checkpoint_timeout reached, checkpoint impact spread over time
Checkpoint Statistics pg_stat_bgwriter checkpoints_timed: # of scheduled checkpoints checkpoints_req: #
of requested checkpoints 1. Time Between Checkpoints 2. % of Timed Checkpoints
Increase max_wal_size / Reduce checkpoint_timeout To Have More Timed Checkpoints
(but be careful with recovery times)
Tune checkpoint_completion_target To Control I/O Impact of Timed Checkpoints (Often
0.9 is a good value, but depends on I/O Subsystem & Workload)
Demo: Postgres 13 WAL Monitoring
Sharding Scaling Beyond The Limits of a Single Server
Citus: Extension for Sharding Postgres
Select from table Coordinator Table metadata Select from table_1001 Select
from table_1003 Select from table_1002 Select from table_1004 Data node N Data node 2 Data node 1 Table_1001 Table_1003 Table_1002 Table_1004 Each node PostgreSQL with Citus installed 1 shard = 1 PostgreSQL table Sharding data across multiple nodes
Demo: Hyperscale (Citus) on Kubernetes with Azure Arc
Thank you!
[email protected]
@LukasFittl