ݺߣ

ݺߣShare a Scribd company logo

Tomáš Vondra: Paralelizace dotazu a partitioning v PostgreSQL

•
•
Develcz
Develcz

Paralelizace dotazů byla představena v PostgreSQL 9.6. Partitioning se poprvé objevil v PostgreSQL 10. PostgreSQL 11 přináší mnoho vylepšení iniciální implementace, odstraňuje různá omezení a umožňuje použití vlastností pro daleko širší škálu případů. V přednášce se nejdříve podíváme na to jaké dotazy dnes PostgreSQL umí paralelizovat, krátkou demonstraci výkonu, a přehled omezení dotazů ktere (zatím) paralelizovat nelze. V druhé části se budeme věnovat partitioningu - ukážeme si jak se partitioning v PostgreSQL řešil dříve, a jak to řeší PostgreSQL 10+.

1 of 23
Download to read offline
Paralelizace dotazů a partitioning v PostgreSQL 11 Tomáš Vondra <tomas.vondra@2ndquadrant.com> devel.cz / 16. června 2018 / Praha
Paralelizace schopnost využít více procesorů pro vykonávání dotazu PostgreSQL 9.6+ 2
Paralelizace ● do PostgreSQL 9.5 (včetně) ○ žádná paralelizace na úrovni dotazů :-/ ○ jeden dotaz = jeden proces = jeden CPU ○ není problém pro OLTP (malé dotazy, saturace počtem spojení) ○ problém pro DWH/DSS/BI/… (velké dotazy, málo současných dotazů) ● PostgreSQL 9.6 ○ základní infrastruktura pro paralelní dotazy ○ omezená podpora pro operace (sekvenční sken + pár dalších operací) ● PostgreSQL 10 ○ zásadní vylepšení (víc operací, užitečnější pro daleko širší škálu dotazů) ● PostgreSQL 11 ○ další vylepšení / optimalizace 3
tabulka Partitioning Parallelism worker 1 worker 2 gather 4
PostgreSQL 9.6 ● vypnuté by default ○ max_parallel_workers_per_gather = 0 ○ force_parallel_mode = off ○ parallel_setup_cost = 1000 ○ parallel_tuple_cost = 0.1 ○ min_parallel_relation_size = 8MB ● operace podporující paralelní exekuci ○ Sequential Scan (driving table) ○ Hash Join ○ Nested Loop ○ Aggregate ○ Gather (nová operace) 5
PostgreSQL 10 ● další operace podporující paralelní exekuci ○ Index Scan (driving table) ○ Bitmap Heap Scan ○ Merge Joins ○ Gather Merge (nová operace) ● zapnuté by default ○ max_parallel_workers_per_gather = 2 ○ min_parallel_table_scan_size = 8MB ○ min_parallel_index_scan_size = 512kB 6
PostgreSQL 11 ● vylepšení operací podporujících paralelní exekuci ○ Hash Join - umí worker procesy umí sdílet hash tabulku ○ CREATE INDEX ○ CREATE TABLE … AS … ○ CREATE MATERIALIZED VIEW … ○ některé dotazy s UNION 7
Co se (zatím) neparalelizuje ... ● dotazy které zapisují do databáze (INSERT, DELETE, …) ○ paralelní “worker” procesy sdílí zámky, takže nutná opatrnost ○ PostgreSQL 11 umí CREATE TABLE … AS SELECT … ● DDL (CREATE/ALTER/DROP/…) ○ ale PostgreSQL 11 už má první vlaštovky (CREATE INDEX) ● dotazy které mohou usnout (CURSOR) ○ výjimky pro PL/pgSQL a další procedurální jazyky (používají kurzory interně) 8
TPC-H 100GB / Q1 select l_returnflag, l_linestatus, sum(l_quantity) as sum_qty, sum(l_extendedprice) as sum_base_price, sum(l_extendedprice * (1 - l_discount)) ..., ... from lineitem where l_shipdate <= date '1992-01-01' - interval ':1' day group by l_returnflag, l_linestatus order by l_returnflag, l_linestatus LIMIT 1; 9
TPC-H 100GB / Q1 10
Deklarativní partitioning schopnost rozdělit tabulku na části, deklarací pravidel PostgreSQL 10+ 11
Cíle partitioningu ● jednodušší správa dat (odstranění partition vs. DELETE) ○ DELETE FROM tabulka WHERE datum < ‘2017-01-01 00:00:00’ ○ ALTER TABLE … DETACH PARTITION … ● optimalizace dotazů (přeskočení partition, ...) ○ např. data uložená po dnech / měsících ○ vyhledávání dat pro konkrétní den / měsíc může přeskočit většinu partitions ○ další partition-aware optimalizace (agregace, joiny, paralelismus, …) ● horizontální škálování (umístění partition na jiný stroj) ○ varianta shardingu - kombinace s FDW (Foreign Data Wrappers) ○ FDW umožňuje využití non-PostgreSQL databází 12
Částečně možné i dříve ... CREATE TABLE tabulka (datum TIMESTAMP, x INT); CREATE TABLE tabulka_201801 ( CHECK (datum >= '2018-01-01' AND datum < '2018-02-01') ) INHERITS (tabulka); EXPLAIN SELECT * FROM tabulka; QUERY PLAN ------------------------------------------------------------------------- Append (cost=0.00..40.61 rows=2041 width=12) -> Seq Scan on tabulka (cost=0.00..0.00 rows=1 width=12) -> Seq Scan on tabulka_201801 (cost=0.00..30.40 rows=2040 width=12) (3 rows) 13
Částečně možné i dříve ... EXPLAIN SELECT * FROM tabulka WHERE datum = '2018-02-15 03:53:22'; QUERY PLAN ---------------------------------------------------------------------------- -- Append (cost=0.00..0.01 rows=1 width=12) -> Seq Scan on tabulka (cost=0.00..0.00 rows=1 width=12) Filter: (datum = '2018-02-15 03:53:22'::timestamp without time zone) (3 rows) 14
Částečně možné i dříve ... CREATE OR REPLACE FUNCTION table_insert_trigger() RETURNS TRIGGER AS $$ BEGIN IF ( NEW.datum >= DATE '2018-01-01' AND NEW.datum < DATE '2018-02-01' ) THEN INSERT INTO table_200801 VALUES (NEW.*); ELSE RAISE EXCEPTION 'date out of range'; END IF; RETURN NULL; END; $$ LANGUAGE plpgsql; CREATE TRIGGER insert_trigger BEFORE INSERT ON measurement FOR EACH ROW EXECUTE PROCEDURE table_insert_trigger(); 15
Nevýhody ● spousta boilerplate kódu v triggerech ○ většinou se to opakuje pořád dokola ● nízký výkon triggerů ○ triggery se spouští row-by-row, což má overhead ● neškáluje na velký počet partitions ○ rozumný limit je někde okolo 100 partitions ● v podstatě nemožnost "pokročilých" optimalizací ○ pravidla jsou skovaná někde hluboko v triggerech ● některé vlastnosti nejde rozumně implementovat ○ přesun dat mezi partitions, … Plánovač nerozumí pravidlům - libovolně složitá, zahrabaná v triggerech, ... 16
PostgreSQL 10 ● syntaxe CREATE TABLE t (...) PARTITION BY { RANGE | LIST | HASH } CREATE TABLE p (...) PARTITION OF t FOR VALUES ... ALTER TABLE t { ATTACH | DETACH } PARTITION ... ● pevně dané varianty partitioningu ○ RANGE ○ LIST ○ HASH (PostgreSQL 11) ● automatické routování řádek do příslušné partition (místo triggerů) 17
Deklarativní partitioning CREATE TABLE tabulka (datum TIMESTAMP, x INT) PARTITION BY RANGE (datum); CREATE TABLE tabulka_201801 PARTITION OF tabulka FOR VALUES FROM ('2018-01-01') TO ('2018-02-01'); 18
Deklarativní partitioning EXPLAIN SELECT * FROM tabulka; QUERY PLAN ------------------------------------------------------------------------- Append (cost=0.00..40.61 rows=2041 width=12) -> Seq Scan on tabulka_201801 (cost=0.00..30.40 rows=2040 width=12) (2 rows) EXPLAIN SELECT * FROM tabulka WHERE datum = '2018-02-15 03:53:22'; QUERY PLAN ------------------------------------------- Result (cost=0.00..0.00 rows=0 width=12) One-Time Filter: false (2 rows) 19
PostgreSQL 11 ● PARTITION BY HASH ● DEFAULT PARTITION ● UPDATE může přesouvat data mezi partitions ● lepší partition elimination (během plánování i exekuce) ● podpora PRIMARY/FOREIGN KEY, indexů a triggerů (s omezeními) ● partition-wise joins (equality joins + shodný partitioning) ● partition-wise aggregation (vypnuto by default) ● routování řádek do foreign partitions (postgres_fdw) ● ... 20
TPC-H 100GB / Q1 select l_returnflag, l_linestatus, sum(l_quantity) as sum_qty, sum(l_extendedprice) as sum_base_price, sum(l_extendedprice * (1 - l_discount)) ..., ... from lineitem where l_shipdate <= date '$1' - interval ':1' day group by l_returnflag, l_linestatus order by l_returnflag, l_linestatus LIMIT 1; 21
TPC-H 100GB / Q1 22
TPC-H 100GB / Q1 23

More Related Content

Tomáš Vondra: Paralelizace dotazu a partitioning v PostgreSQL

  • 1. Paralelizace dotazů a partitioning v PostgreSQL 11 Tomáš Vondra <tomas.vondra@2ndquadrant.com> devel.cz / 16. června 2018 / Praha
  • 2. Paralelizace schopnost využít více procesorů pro vykonávání dotazu PostgreSQL 9.6+ 2
  • 3. Paralelizace ● do PostgreSQL 9.5 (včetně) ○ žádná paralelizace na úrovni dotazů :-/ ○ jeden dotaz = jeden proces = jeden CPU ○ není problém pro OLTP (malé dotazy, saturace počtem spojení) ○ problém pro DWH/DSS/BI/… (velké dotazy, málo současných dotazů) ● PostgreSQL 9.6 ○ základní infrastruktura pro paralelní dotazy ○ omezená podpora pro operace (sekvenční sken + pár dalších operací) ● PostgreSQL 10 ○ zásadní vylepšení (víc operací, užitečnější pro daleko širší škálu dotazů) ● PostgreSQL 11 ○ další vylepšení / optimalizace 3
  • 5. PostgreSQL 9.6 ● vypnuté by default ○ max_parallel_workers_per_gather = 0 ○ force_parallel_mode = off ○ parallel_setup_cost = 1000 ○ parallel_tuple_cost = 0.1 ○ min_parallel_relation_size = 8MB ● operace podporující paralelní exekuci ○ Sequential Scan (driving table) ○ Hash Join ○ Nested Loop ○ Aggregate ○ Gather (nová operace) 5
  • 6. PostgreSQL 10 ● další operace podporující paralelní exekuci ○ Index Scan (driving table) ○ Bitmap Heap Scan ○ Merge Joins ○ Gather Merge (nová operace) ● zapnuté by default ○ max_parallel_workers_per_gather = 2 ○ min_parallel_table_scan_size = 8MB ○ min_parallel_index_scan_size = 512kB 6
  • 7. PostgreSQL 11 ● vylepšení operací podporujících paralelní exekuci ○ Hash Join - umí worker procesy umí sdílet hash tabulku ○ CREATE INDEX ○ CREATE TABLE … AS … ○ CREATE MATERIALIZED VIEW … ○ některé dotazy s UNION 7
  • 8. Co se (zatím) neparalelizuje ... ● dotazy které zapisují do databáze (INSERT, DELETE, …) ○ paralelní “worker” procesy sdílí zámky, takže nutná opatrnost ○ PostgreSQL 11 umí CREATE TABLE … AS SELECT … ● DDL (CREATE/ALTER/DROP/…) ○ ale PostgreSQL 11 už má první vlaštovky (CREATE INDEX) ● dotazy které mohou usnout (CURSOR) ○ výjimky pro PL/pgSQL a další procedurální jazyky (používají kurzory interně) 8
  • 9. TPC-H 100GB / Q1 select l_returnflag, l_linestatus, sum(l_quantity) as sum_qty, sum(l_extendedprice) as sum_base_price, sum(l_extendedprice * (1 - l_discount)) ..., ... from lineitem where l_shipdate <= date '1992-01-01' - interval ':1' day group by l_returnflag, l_linestatus order by l_returnflag, l_linestatus LIMIT 1; 9
  • 10. TPC-H 100GB / Q1 10
  • 11. Deklarativní partitioning schopnost rozdělit tabulku na části, deklarací pravidel PostgreSQL 10+ 11
  • 12. Cíle partitioningu ● jednodušší správa dat (odstranění partition vs. DELETE) ○ DELETE FROM tabulka WHERE datum < ‘2017-01-01 00:00:00’ ○ ALTER TABLE … DETACH PARTITION … ● optimalizace dotazů (přeskočení partition, ...) ○ např. data uložená po dnech / měsících ○ vyhledávání dat pro konkrétní den / měsíc může přeskočit většinu partitions ○ další partition-aware optimalizace (agregace, joiny, paralelismus, …) ● horizontální škálování (umístění partition na jiný stroj) ○ varianta shardingu - kombinace s FDW (Foreign Data Wrappers) ○ FDW umožňuje využití non-PostgreSQL databází 12
  • 13. Částečně možné i dříve ... CREATE TABLE tabulka (datum TIMESTAMP, x INT); CREATE TABLE tabulka_201801 ( CHECK (datum >= '2018-01-01' AND datum < '2018-02-01') ) INHERITS (tabulka); EXPLAIN SELECT * FROM tabulka; QUERY PLAN ------------------------------------------------------------------------- Append (cost=0.00..40.61 rows=2041 width=12) -> Seq Scan on tabulka (cost=0.00..0.00 rows=1 width=12) -> Seq Scan on tabulka_201801 (cost=0.00..30.40 rows=2040 width=12) (3 rows) 13
  • 14. Částečně možné i dříve ... EXPLAIN SELECT * FROM tabulka WHERE datum = '2018-02-15 03:53:22'; QUERY PLAN ---------------------------------------------------------------------------- -- Append (cost=0.00..0.01 rows=1 width=12) -> Seq Scan on tabulka (cost=0.00..0.00 rows=1 width=12) Filter: (datum = '2018-02-15 03:53:22'::timestamp without time zone) (3 rows) 14
  • 15. Částečně možné i dříve ... CREATE OR REPLACE FUNCTION table_insert_trigger() RETURNS TRIGGER AS $$ BEGIN IF ( NEW.datum >= DATE '2018-01-01' AND NEW.datum < DATE '2018-02-01' ) THEN INSERT INTO table_200801 VALUES (NEW.*); ELSE RAISE EXCEPTION 'date out of range'; END IF; RETURN NULL; END; $$ LANGUAGE plpgsql; CREATE TRIGGER insert_trigger BEFORE INSERT ON measurement FOR EACH ROW EXECUTE PROCEDURE table_insert_trigger(); 15
  • 16. Nevýhody ● spousta boilerplate kódu v triggerech ○ většinou se to opakuje pořád dokola ● nízký výkon triggerů ○ triggery se spouští row-by-row, což má overhead ● neškáluje na velký počet partitions ○ rozumný limit je někde okolo 100 partitions ● v podstatě nemožnost "pokročilých" optimalizací ○ pravidla jsou skovaná někde hluboko v triggerech ● některé vlastnosti nejde rozumně implementovat ○ přesun dat mezi partitions, … Plánovač nerozumí pravidlům - libovolně složitá, zahrabaná v triggerech, ... 16
  • 17. PostgreSQL 10 ● syntaxe CREATE TABLE t (...) PARTITION BY { RANGE | LIST | HASH } CREATE TABLE p (...) PARTITION OF t FOR VALUES ... ALTER TABLE t { ATTACH | DETACH } PARTITION ... ● pevně dané varianty partitioningu ○ RANGE ○ LIST ○ HASH (PostgreSQL 11) ● automatické routování řádek do příslušné partition (místo triggerů) 17
  • 18. Deklarativní partitioning CREATE TABLE tabulka (datum TIMESTAMP, x INT) PARTITION BY RANGE (datum); CREATE TABLE tabulka_201801 PARTITION OF tabulka FOR VALUES FROM ('2018-01-01') TO ('2018-02-01'); 18
  • 19. Deklarativní partitioning EXPLAIN SELECT * FROM tabulka; QUERY PLAN ------------------------------------------------------------------------- Append (cost=0.00..40.61 rows=2041 width=12) -> Seq Scan on tabulka_201801 (cost=0.00..30.40 rows=2040 width=12) (2 rows) EXPLAIN SELECT * FROM tabulka WHERE datum = '2018-02-15 03:53:22'; QUERY PLAN ------------------------------------------- Result (cost=0.00..0.00 rows=0 width=12) One-Time Filter: false (2 rows) 19
  • 20. PostgreSQL 11 ● PARTITION BY HASH ● DEFAULT PARTITION ● UPDATE může přesouvat data mezi partitions ● lepší partition elimination (během plánování i exekuce) ● podpora PRIMARY/FOREIGN KEY, indexů a triggerů (s omezeními) ● partition-wise joins (equality joins + shodný partitioning) ● partition-wise aggregation (vypnuto by default) ● routování řádek do foreign partitions (postgres_fdw) ● ... 20
  • 21. TPC-H 100GB / Q1 select l_returnflag, l_linestatus, sum(l_quantity) as sum_qty, sum(l_extendedprice) as sum_base_price, sum(l_extendedprice * (1 - l_discount)) ..., ... from lineitem where l_shipdate <= date '$1' - interval ':1' day group by l_returnflag, l_linestatus order by l_returnflag, l_linestatus LIMIT 1; 21
  • 22. TPC-H 100GB / Q1 22
  • 23. TPC-H 100GB / Q1 23