pgDU Sydney 15th November 2019 Hari Kiran PostgreSQL Consultant, - - PowerPoint PPT Presentation

pgdu sydney 15th november 2019
SMART_READER_LITE
LIVE PREVIEW

pgDU Sydney 15th November 2019 Hari Kiran PostgreSQL Consultant, - - PowerPoint PPT Presentation

pgDU Sydney 15th November 2019 Hari Kiran PostgreSQL Consultant, 2ndQuadrant https://www.2ndQuadrant.com pgDU Sydney THP & Defrag Enabled by default Disable! Disable! Disable! Consolidate Fragmented huge pages Defrags huge


slide-1
SLIDE 1

https://www.2ndQuadrant.com

pgDU Sydney

Hari Kiran PostgreSQL Consultant, 2ndQuadrant

15th November 2019

slide-2
SLIDE 2

https://www.2ndQuadrant.com

pgDU Sydney

THP & Defrag

  • Enabled by default

Disable! Disable! Disable!

  • Consolidate Fragmented huge pages
  • Defrags huge pages
  • Application Wait
slide-3
SLIDE 3

https://www.2ndQuadrant.com

pgDU Sydney

IO Scheduler

noop or none or deadline

What makes sense?

  • When set to noop, host takes care of proper schedule
  • I/O requests into a FIFO (First in, First Out) queue

Note: Use pgbench to test TPS to measure the impact of this I/O scheduler.

slide-4
SLIDE 4

https://www.2ndQuadrant.com

pgDU Sydney

PG Background processes

Can’t love them all… Can’t leave them all

  • Checkpointer
  • Background Writer
  • AutoVacuum
slide-5
SLIDE 5

https://www.2ndQuadrant.com

pgDU Sydney

Checkpointer

  • WALs & Recovery
  • Not quite often; not too lazy
  • Get away from defaults
slide-6
SLIDE 6

https://www.2ndQuadrant.com

pgDU Sydney

Background Writer

  • maxwritten_clean → bgwriter_lru_maxpages.
  • Look at buffers_clean and buffers_backend
  • buffers_clean > buffers_backend.

○ increase bgwriter_lru_multiplier ○ decrease bgwriter_delay Signs of an insufficient shared buffers and hot part of your data don't fit into shared buffers and forced to travel between RAM and disks.

  • buffers_backend_fsync should always show 0
slide-7
SLIDE 7

https://www.2ndQuadrant.com

pgDU Sydney

AutoVacuum

  • Don’t disable autovacuum. Seriously!
  • Busy databases & large ones - decrease the scale factor, more frequent

clean-up (pg_stat_user_tables)

  • Got good storage & multiple cores - increase the throttling parameters
  • Increasing autovacuum_max_workers alone will not really help in most

cases.

  • Per table parameters using ALTER TABLE - makes the system more

complex and more difficult to inspect.

slide-8
SLIDE 8

https://www.2ndQuadrant.com

pgDU Sydney

Engineering Decisions

slide-9
SLIDE 9

https://www.2ndQuadrant.com

pgDU Sydney

Connection Management - PgBouncer

Application -> PgBouncer PgBouncer -> pool PgBouncer -> PostgreSQL server connection Disconnection -> PgBouncer pool

slide-10
SLIDE 10

https://www.2ndQuadrant.com

pgDU Sydney

pg_dump… Dump faster

pg_dump Sections

  • -section=pre-data

– Most DDL statements

  • -section=data

– Table Data, Large Objects, Sequence Values

  • -section=post-data

– Indexes, Triggers, Rules – All Constraints except CHECK constraints Tables are locked - cannot run DDL during dump – Can specify --lock-wait-timeout=Nms Note: Autovacuum won’t start until after dump

slide-11
SLIDE 11

https://www.2ndQuadrant.com

pgDU Sydney

Table Partitioning

Storage groups are Database Tablespaces

slide-12
SLIDE 12

https://www.2ndQuadrant.com

pgDU Sydney

Partition Pruning

Planner-time partition pruning

  • A binary search quickly identifies matching LIST

and RANGE partitions

  • A hashing function finds the matching partitions

for HASH partitioned tables

Execution-time partition pruning

  • Phase-one: pruning is performed during executor initialization

(not shown in EXPLAIN plans)

  • Phase-two: remove partitions using parameters that are only known

when the executor is actually running

slide-13
SLIDE 13

https://www.2ndQuadrant.com

pgDU Sydney

High Availability

  • hot_standby_feedback
  • max_standby_streaming_delay
  • max_standby_archive_delay
slide-14
SLIDE 14

https://www.2ndQuadrant.com

pgDU Sydney

High Availability

slide-15
SLIDE 15

https://www.2ndQuadrant.com

pgDU Sydney

Data Segregation

  • Hard Separation via Different Databases in Same Cluster
  • Soft Separation in the Same Database
  • Views
  • Row Level Security
  • Schemas
slide-16
SLIDE 16

https://www.2ndQuadrant.com

pgDU Sydney

Pass by our booth!

slide-17
SLIDE 17

https://www.2ndQuadrant.com

pgDU Sydney

Thank you! Questions?

Hari Kiran hari.kiran@2ndquadrant.com

slide-18
SLIDE 18

https://www.2ndQuadrant.com

pgDU Sydney

2ndQuadrant PostgreSQL Solutions

Website https://www.2ndquadrant.com/ Blog https://blog.2ndquadrant.com/ Email info@2ndQuadrant.com