Voorwoord of hoe het idee van segmenteren ontstond
Het verhaal begint hier:
Lyrische uitweiding:
Precies ‘op dat moment’, want
Hoe kun je anders de klant blij maken en tegelijkertijd je eigen vaardigheden verbeteren?
Om alles zoveel mogelijk te vereenvoudigen, dan zijn er maar twee manieren om iets in de prestaties van de database radicaal te verbeteren:
1) Uitgebreid pad - we vergroten de middelen, veranderen de configuratie;
2) Intensief pad - zoekopdrachtoptimalisatie
Omdat, ik herhaal, het op dat moment niet langer duidelijk was wat er nog meer moest veranderen in het verzoek om te versnellen, werd het pad gekozen: tafelontwerpwijzigingen.
De belangrijkste vraag rijst dus: wat en hoe gaan we veranderen?
Begincondities
Ten eerste is er deze ERD (voorwaardelijk vereenvoudigd weergegeven):
Основные особенности:
- veel-op-veel-relaties
- de tabel heeft al een potentiële partitiesleutel
Origineel verzoek:
SELECT
p."PARAMETER_ID" as parameter_id,
pc."PC_NAME" AS pc_name,
pc."CUSTOMER_PARTNUMBER" AS customer_partnumber,
w."LASERMARK" AS lasermark,
w."LOTID" AS lotid,
w."REPORTED_VALUE" AS reported_value,
w."LOWER_SPEC_LIMIT" AS lower_spec_limit,
w."UPPER_SPEC_LIMIT" AS upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SHIPMENT_NAME" AS shipment_name,
s."SHIPMENT_DATE" AS shipment_date,
extract(year from s."SHIPMENT_DATE") AS year,
extract(month from s."SHIPMENT_DATE") as month,
s."REPORT_NAME" AS report_name,
p."SPARAM_NAME" AS SPARAM_name,
p."CUSTOMERPARAM_NAME" AS customerparam_name
FROM data w INNER JOIN shipment s ON s."SHIPMENT_ID" = w."SHIPMENT_ID"
INNER JOIN parameters p ON p."PARAMETER_ID" = w."PARAMETER_ID"
INNER JOIN shipment_pc sp ON s."SHIPMENT_ID" = sp."SHIPMENT_ID"
INNER JOIN pc pc ON pc."PC_ID" = sp."PC_ID"
INNER JOIN ( SELECT w2."LASERMARK" , MAX(s2."SHIPMENT_DATE") AS "SHIPMENT_DATE"
FROM shipment s2 INNER JOIN data w2 ON s2."SHIPMENT_ID" = w2."SHIPMENT_ID"
GROUP BY w2."LASERMARK"
) md ON md."SHIPMENT_DATE" = s."SHIPMENT_DATE" AND md."LASERMARK" = w."LASERMARK"
WHERE
s."SHIPMENT_DATE" >= '2018-07-01' AND s."SHIPMENT_DATE" <= '2018-09-30' ;
Resultaten van uitvoering op de testdatabase:
Kosten : 502 997.55
Uitvoeringstijd: 505 seconden.
Wat zien we? Een regulier verzoek, gebaseerd op een tijdsdeel.
Laten we de eenvoudigste logische veronderstelling maken: als er een voorbeeld van een tijdsdeel bestaat, zal dat ons dan helpen? Dat klopt: verdeling.
Wat te sectie?
Op het eerste gezicht ligt de keuze voor de hand: declaratieve partitie van de tabel "verzending" met behulp van de sleutel "SHIPMENT_DATE" (te ver vooruit springen - uiteindelijk bleek het een beetje mis te gaan in de productie).
Hoe verdelen?
Deze vraag is ook niet zo moeilijk. Gelukkig is er in PostgreSQL 10 nu een menselijk partitiemechanisme.
Dus:
- Bewaar een dump van de brontabel - pg_dump bron_tabel
- Verwijder de originele tabel - zet tabel source_table neer
- Maak een bovenliggende tabel met bereikpartitionering - maak tabel source_table
- Secties maken - maak tabel source_table, maak index
- Importeer de dump die in stap 1 is gemaakt - pg_herstellen
Scripts voor partitionering
Voor eenvoud en gemak zijn de stappen 2,3,4 gecombineerd in één script.
Dus:
Sla een dump van de brontabel op
pg_dump postgres --file=/dump/shipment.dmp --format=c --table=shipment --verbose > /dump/shipment.log 2>&1
Verwijder de brontabel + Maak een bovenliggende tabel met bereikpartitionering + Maak partities
--create_partition_shipment.sql
do language plpgsql $$
declare
rec_shipment_date RECORD ;
partition_name varchar;
index_name varchar;
current_year varchar ;
current_month varchar ;
begin_year varchar ;
begin_month varchar ;
next_year varchar ;
next_month varchar ;
first_flag boolean ;
i integer ;
begin
RAISE NOTICE 'CREATE TEMPORARY TABLE FOR SHIPMENT_DATE';
CREATE TEMP TABLE tmp_shipment_date as select distinct "SHIPMENT_DATE" from shipment order by "SHIPMENT_DATE" ;
RAISE NOTICE 'DROP TABLE shipment';
drop table shipment cascade ;
CREATE TABLE public.shipment
(
"SHIPMENT_ID" integer NOT NULL DEFAULT nextval('shipment_shipment_id_seq'::regclass),
"SHIPMENT_NAME" character varying(30) COLLATE pg_catalog."default",
"SHIPMENT_DATE" timestamp without time zone,
"REPORT_NAME" character varying(40) COLLATE pg_catalog."default"
)
PARTITION BY RANGE ("SHIPMENT_DATE")
WITH (
OIDS = FALSE
)
TABLESPACE pg_default;
RAISE NOTICE 'CREATE PARTITIONS FOR TABLE shipment';
current_year:='0';
current_month:='0';
begin_year := '0' ;
begin_month := '0' ;
next_year := '0' ;
next_month := '0' ;
FOR rec_shipment_date IN SELECT * FROM tmp_shipment_date LOOP
RAISE NOTICE 'SHIPMENT_DATE=%',rec_shipment_date."SHIPMENT_DATE";
current_year := date_part('year' ,rec_shipment_date."SHIPMENT_DATE");
current_month := date_part('month' ,rec_shipment_date."SHIPMENT_DATE") ;
IF to_number(current_month,'99') < 10 THEN
current_month := '0'||current_month ;
END IF ;
--Init borders
IF begin_year = '0' THEN
first_flag := true ; --first time flag
begin_year := current_year ;
begin_month := current_month ;
IF current_month = '12' THEN
next_year := date_part('year' ,rec_shipment_date."SHIPMENT_DATE" + interval '1 year') ;
ELSE
next_year := current_year ;
END IF;
next_month := date_part('month' ,rec_shipment_date."SHIPMENT_DATE" + interval '1 month') ;
END IF;
-- Check current date into borders NOT for First time
IF to_date( current_year||'.'||current_month, 'YYYY.MM') >= to_date( begin_year||'.'||begin_month, 'YYYY.MM') AND
to_date( current_year||'.'||current_month, 'YYYY.MM') < to_date( next_year||'.'||next_month, 'YYYY.MM') AND
NOT first_flag
THEN
CONTINUE ;
ELSE
--NEW borders only for second and after time
begin_year := current_year ;
begin_month := current_month ;
IF current_month = '12' THEN
next_year := date_part('year' ,rec_shipment_date."SHIPMENT_DATE" + interval '1 year') ;
ELSE
next_year := current_year ;
END IF;
next_month := date_part('month' ,rec_shipment_date."SHIPMENT_DATE" + interval '1 month') ;
END IF;
partition_name := 'shipment_shipment_date_'||begin_year||'-'||begin_month||'-01-'|| next_year||'-'||next_month||'-01' ;
EXECUTE format('CREATE TABLE ' || quote_ident(partition_name) || ' PARTITION OF shipment FOR VALUES FROM ( %L ) TO ( %L ) ' , current_year||'-'||current_month||'-01' , next_year||'-'||next_month||'-01' ) ;
index_name := partition_name||'_shipment_id_idx';
RAISE NOTICE 'INDEX NAME =%',index_name;
EXECUTE format('CREATE INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree ("SHIPMENT_ID") TABLESPACE pg_default ' ) ;
--Drop first time flag
first_flag := false ;
END LOOP;
end
$$;
Importeren van de dump
pg_restore -d postgres --data-only --format=c --table=shipment --verbose shipment.dmp > /tmp/data_dump/shipment_restore.log 2>&1
De partitieresultaten controleren
Wat hebben we als resultaat? De volledige tekst van het uitvoeringsplan is groot en saai, dus het is heel goed mogelijk om je te beperken tot de definitieve cijfers.
Het was
Kosten: 502 997.55
Uitvoertijd: 505 seconden.
Is geworden
Kosten: 77 872.36
Uitvoertijd: 79 seconden.
Een behoorlijk goed resultaat. Lagere kosten en uitvoeringstijd. Het gebruik van partities geeft dus het verwachte effect en in het algemeen geen verrassingen.
Maak de klant blij
De testresultaten zijn ter beoordeling aan de klant voorgelegd. En nadat ze het hadden doorgenomen, kregen ze een enigszins onverwacht oordeel: “Geweldig, verdeel de ‘data’-tabel.”
Ja, maar we hebben een heel andere “verzending”-tabel onderzocht; de “data”-tabel bevat niet het veld “SHIPMENT_DATE”.
Geen probleem, toevoegen, wijzigen. Het belangrijkste is dat de klant tevreden is met het resultaat, de implementatiedetails zijn niet bijzonder belangrijk.
Partitioneren van de hoofdtabel “data”
Over het algemeen deden zich geen bijzondere problemen voor. Hoewel het partitie-algoritme natuurlijk enigszins is veranderd.
De kolom “SHIPMENT_DATA” toevoegen aan de tabel “data”.
psql -h хост -U база -d юзер
=> ALTER TABLE data ADD COLUMN "SHIPMENT_DATE" timestamp without time zone ;
Vul de waarden van de kolom “SHIPMENT_DATA” in de tabel “data” in met de waarden van de gelijknamige kolom uit de tabel “verzending”
-----------------------------
--update_data.sql
--updating for altered table "data" to values of "shipment_data" from the table "shipment"
--version 1.0
do language plpgsql $$
declare
rec_shipment_data RECORD ;
shipment_date timestamp without time zone ;
row_count integer ;
total_rows integer ;
begin
select count(*) into total_rows from shipment ;
RAISE NOTICE 'Total %',total_rows;
row_count:= 0 ;
FOR rec_shipment_data IN SELECT * FROM shipment LOOP
update data set "SHIPMENT_DATE" = rec_shipment_data."SHIPMENT_DATE" where "SHIPMENT_ID" = rec_shipment_data."SHIPMENT_ID";
row_count:= row_count +1 ;
RAISE NOTICE 'row count = % , from %',row_count,total_rows;
END LOOP;
end
$$;
Sla een dump op van de “data” -tabel
pg_dump postgres --file=/dump/data.dmp --format=c --table=data --verbose > /dump/data.log 2>&1</source
Maak de gepartitioneerde tabel “data” opnieuw
--create_partition_data.sql
--create partitions for the table "wafer data" by range column "shipment_data" with one month duration
--version 1.0
do language plpgsql $$
declare
rec_shipment_date RECORD ;
partition_name varchar;
index_name varchar;
current_year varchar ;
current_month varchar ;
begin_year varchar ;
begin_month varchar ;
next_year varchar ;
next_month varchar ;
first_flag boolean ;
i integer ;
begin
RAISE NOTICE 'CREATE TEMPORARY TABLE FOR SHIPMENT_DATE';
CREATE TEMP TABLE tmp_shipment_date as select distinct "SHIPMENT_DATE" from shipment order by "SHIPMENT_DATE" ;
RAISE NOTICE 'DROP TABLE data';
drop table data cascade ;
RAISE NOTICE 'CREATE PARTITIONED TABLE data';
CREATE TABLE public.data
(
"RUN_ID" integer,
"LASERMARK" character varying(20) COLLATE pg_catalog."default" NOT NULL,
"LOTID" character varying(80) COLLATE pg_catalog."default",
"SHIPMENT_ID" integer NOT NULL,
"PARAMETER_ID" integer NOT NULL,
"INTERNAL_VALUE" character varying(75) COLLATE pg_catalog."default",
"REPORTED_VALUE" character varying(75) COLLATE pg_catalog."default",
"LOWER_SPEC_LIMIT" numeric,
"UPPER_SPEC_LIMIT" numeric ,
"SHIPMENT_DATE" timestamp without time zone
)
PARTITION BY RANGE ("SHIPMENT_DATE")
WITH (
OIDS = FALSE
)
TABLESPACE pg_default ;
RAISE NOTICE 'CREATE PARTITIONS FOR TABLE data';
current_year:='0';
current_month:='0';
begin_year := '0' ;
begin_month := '0' ;
next_year := '0' ;
next_month := '0' ;
i := 1;
FOR rec_shipment_date IN SELECT * FROM tmp_shipment_date LOOP
RAISE NOTICE 'SHIPMENT_DATE=%',rec_shipment_date."SHIPMENT_DATE";
current_year := date_part('year' ,rec_shipment_date."SHIPMENT_DATE");
current_month := date_part('month' ,rec_shipment_date."SHIPMENT_DATE") ;
--Init borders
IF begin_year = '0' THEN
RAISE NOTICE '***Init borders';
first_flag := true ; --first time flag
begin_year := current_year ;
begin_month := current_month ;
IF current_month = '12' THEN
next_year := date_part('year' ,rec_shipment_date."SHIPMENT_DATE" + interval '1 year') ;
ELSE
next_year := current_year ;
END IF;
next_month := date_part('month' ,rec_shipment_date."SHIPMENT_DATE" + interval '1 month') ;
END IF;
-- RAISE NOTICE 'current_year=% , current_month=% ',current_year,current_month;
-- RAISE NOTICE 'begin_year=% , begin_month=% ',begin_year,begin_month;
-- RAISE NOTICE 'next_year=% , next_month=% ',next_year,next_month;
-- Check current date into borders NOT for First time
RAISE NOTICE 'Current data = %',to_char( to_date( current_year||'.'||current_month, 'YYYY.MM'), 'YYYY.MM');
RAISE NOTICE 'Begin data = %',to_char( to_date( begin_year||'.'||begin_month, 'YYYY.MM'), 'YYYY.MM');
RAISE NOTICE 'Next data = %',to_char( to_date( next_year||'.'||next_month, 'YYYY.MM'), 'YYYY.MM');
IF to_date( current_year||'.'||current_month, 'YYYY.MM') >= to_date( begin_year||'.'||begin_month, 'YYYY.MM') AND
to_date( current_year||'.'||current_month, 'YYYY.MM') < to_date( next_year||'.'||next_month, 'YYYY.MM') AND
NOT first_flag
THEN
RAISE NOTICE '***CONTINUE';
CONTINUE ;
ELSE
--NEW borders only for second and after time
RAISE NOTICE '***NEW BORDERS';
begin_year := current_year ;
begin_month := current_month ;
IF current_month = '12' THEN
next_year := date_part('year' ,rec_shipment_date."SHIPMENT_DATE" + interval '1 year') ;
ELSE
next_year := current_year ;
END IF;
next_month := date_part('month' ,rec_shipment_date."SHIPMENT_DATE" + interval '1 month') ;
END IF;
IF to_number(current_month,'99') < 10 THEN
current_month := '0'||current_month ;
END IF ;
IF to_number(begin_month,'99') < 10 THEN
begin_month := '0'||begin_month ;
END IF ;
IF to_number(next_month,'99') < 10 THEN
next_month := '0'||next_month ;
END IF ;
RAISE NOTICE 'current_year=% , current_month=% ',current_year,current_month;
RAISE NOTICE 'begin_year=% , begin_month=% ',begin_year,begin_month;
RAISE NOTICE 'next_year=% , next_month=% ',next_year,next_month;
partition_name := 'data_'||begin_year||begin_month||'01_'||next_year||next_month||'01' ;
RAISE NOTICE 'PARTITION NUMBER % , TABLE NAME =%',i , partition_name;
EXECUTE format('CREATE TABLE ' || quote_ident(partition_name) || ' PARTITION OF data FOR VALUES FROM ( %L ) TO ( %L ) ' , begin_year||'-'||begin_month||'-01' , next_year||'-'||next_month||'-01' ) ;
index_name := partition_name||'_shipment_id_parameter_id_idx';
RAISE NOTICE 'INDEX NAME =%',index_name;
EXECUTE format('CREATE INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree ("SHIPMENT_ID", "PARAMETER_ID") TABLESPACE pg_default ' ) ;
index_name := partition_name||'_lasermark_idx';
RAISE NOTICE 'INDEX NAME =%',index_name;
EXECUTE format('CREATE INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree ("LASERMARK" COLLATE pg_catalog."default") TABLESPACE pg_default ' ) ;
index_name := partition_name||'_shipment_id_idx';
RAISE NOTICE 'INDEX NAME =%',index_name;
EXECUTE format('CREATE INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree ("SHIPMENT_ID") TABLESPACE pg_default ' ) ;
index_name := partition_name||'_parameter_id_idx';
RAISE NOTICE 'INDEX NAME =%',index_name;
EXECUTE format('CREATE INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree ("PARAMETER_ID") TABLESPACE pg_default ' ) ;
index_name := partition_name||'_shipment_date_idx';
RAISE NOTICE 'INDEX NAME =%',index_name;
EXECUTE format('CREATE INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree ("SHIPMENT_DATE") TABLESPACE pg_default ' ) ;
--Drop first time flag
first_flag := false ;
END LOOP;
end
$$;
Laad de dump die in stap 3 is gemaakt.
pg_restore -h хост -юзер -d база --data-only --format=c --table=data --verbose data.dmp > data_restore.log 2>&1
Maak een aparte sectie voor oude gegevens
---------------------------------------------------
--create_partition_for_old_dates.sql
--create partitions for keeping old dates
--version 1.0
do language plpgsql $$
declare
rec_shipment_date RECORD ;
partition_name varchar;
index_name varchar;
begin
SELECT min("SHIPMENT_DATE") AS min_date INTO rec_shipment_date from data ;
RAISE NOTICE 'Old date is %',rec_shipment_date.min_date ;
partition_name := 'data_old_dates' ;
RAISE NOTICE 'PARTITION NAME IS %',partition_name;
EXECUTE format('CREATE TABLE ' || quote_ident(partition_name) || ' PARTITION OF data FOR VALUES FROM ( %L ) TO ( %L ) ' , '1900-01-01' ,
to_char( rec_shipment_date.min_date,'YYYY')||'-'||to_char(rec_shipment_date.min_date,'MM')||'-01' ) ;
index_name := partition_name||'_shipment_id_parameter_id_idx';
EXECUTE format('CREATE INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree ("SHIPMENT_ID", "PARAMETER_ID") TABLESPACE pg_default ' ) ;
index_name := partition_name||'_lasermark_idx';
EXECUTE format('CREATE INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree ("LASERMARK" COLLATE pg_catalog."default") TABLESPACE pg_default ' ) ;
index_name := partition_name||'_shipment_id_idx';
EXECUTE format('CREATE INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree ("SHIPMENT_ID") TABLESPACE pg_default ' ) ;
index_name := partition_name||'_parameter_id_idx';
EXECUTE format('CREATE INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree ("PARAMETER_ID") TABLESPACE pg_default ' ) ;
index_name := partition_name||'_shipment_date_idx';
EXECUTE format('CREATE INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree ("SHIPMENT_DATE") TABLESPACE pg_default ' ) ;
end
$$;
Eind resultaat:
Het was
Kosten: 502 997.55
Uitvoeringstijd: 505 seconden.
Is geworden
Kosten: 68 533.70
Uitvoertijd: 69 seconden
Waardig, heel waardig. En gezien het feit dat we er gaandeweg in zijn geslaagd het partitiemechanisme in PostgreSQL 10 min of meer onder de knie te krijgen - een uitstekend resultaat.
Lyrische retraite
Is het mogelijk om het nog beter te doen? JA, DAT KAN!Om dit te doen, moet u MATERIALIZED VIEW gebruiken.
MAAK EEN GEMATERIALISEERDE WEERGAVE LASERMARK_VIEW
CREATE MATERIALIZED VIEW LASERMARK_VIEW
AS
SELECT w."LASERMARK" , MAX(s."SHIPMENT_DATE") AS "SHIPMENT_DATE"
FROM shipment s INNER JOIN data w ON s."SHIPMENT_ID" = w."SHIPMENT_ID"
GROUP BY w."LASERMARK" ;
CREATE INDEX lasermark_vw_shipment_date_ind on lasermark_view USING btree ("SHIPMENT_DATE") TABLESPACE pg_default;
analyze lasermark_view ;
We herschrijven het verzoek opnieuw:
Query's uitvoeren met de gematerialiseerde weergave
SELECT
p."PARAMETER_ID" as parameter_id,
pc."PC_NAME" AS pc_name,
pc."CUSTOMER_PARTNUMBER" AS customer_partnumber,
w."LASERMARK" AS lasermark,
w."LOTID" AS lotid,
w."REPORTED_VALUE" AS reported_value,
w."LOWER_SPEC_LIMIT" AS lower_spec_limit,
w."UPPER_SPEC_LIMIT" AS upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SHIPMENT_NAME" AS shipment_name,
s."SHIPMENT_DATE" AS shipment_date,
extract(year from s."SHIPMENT_DATE") AS year,
extract(month from s."SHIPMENT_DATE") as month,
s."REPORT_NAME" AS report_name,
p."STC_NAME" AS STC_name,
p."CUSTOMERPARAM_NAME" AS customerparam_name
FROM data w INNER JOIN shipment s ON s."SHIPMENT_ID" = w."SHIPMENT_ID"
INNER JOIN parameters p ON p."PARAMETER_ID" = w."PARAMETER_ID"
INNER JOIN shipment_pc sp ON s."SHIPMENT_ID" = sp."SHIPMENT_ID"
INNER JOIN pc pc ON pc."PC_ID" = sp."PC_ID"
INNER JOIN LASERMARK_VIEW md ON md."SHIPMENT_DATE" = s."SHIPMENT_DATE" AND md."LASERMARK" = w."LASERMARK"
WHERE
s."SHIPMENT_DATE" >= '2018-07-01' AND s."SHIPMENT_DATE" <= '2018-09-30';
En we krijgen nog een resultaat:
Het was
Kosten: 502 997.55
Uitvoeringstijd: 505 seconden
Is geworden
Kosten: 42 481.16
Uitvoertijd: 43 seconden.
Hoewel zo’n veelbelovend resultaat uiteraard bedrieglijk is; ideeën moeten worden opgefrist. Dus de totale tijd voor het ontvangen van gegevens zal niet veel helpen. Maar als experiment is het best interessant.
Eigenlijk, zo bleek, nogmaals bedankt
nawoord
De klant is dus tevreden. EN noodzakelijk profiteren van de situatie.
овая адача: Wat kun je bedenken om te verdiepen en uit te breiden?
En dan herinner ik me: jongens, we hebben geen monitoring van onze PostgreSQL-databases.
Eerlijk gezegd is er nog steeds enige monitoring in de vorm van Cloud Watch op AWS. Maar wat is het nut van deze monitoring voor de DBA? Over het algemeen vrijwel geen.
Als je de kans hebt om iets nuttigs en interessants voor jezelf te doen, kun je deze kans niet niet benutten...
VOOR
Zo komen we bij het meest interessante deel:
3 december 2018.
Het besluit nemen om onderzoek te doen naar de beschikbare mogelijkheden voor het monitoren van de prestaties van PostgreSQL-query's.
Maar dat is een heel ander verhaal.
Wordt vervolgd…
Bron: www.habr.com