Prefacio o cómo surgió la idea de la partición
El comienzo de la historia aquí: Después de que casi todos los recursos para optimizar la consulta se hubieron agotado en ese momento, surgió la pregunta: ¿y ahora qué? Así fue como nació la idea de la partición.
Un inciso lírico:
Precisamente ‘en ese momento’, porque . Gracias ¡y a Habr!
Entonces, ¿cómo más podemos hacer que el cliente esté satisfecho y al mismo tiempo mejorar nuestras habilidades?
Si simplificamos todo al máximo, las vías radicales para mejorar el rendimiento de la base de datos son solo dos:
1) Camino extensivo: aumentamos recursos, cambiamos la configuración;
2) Camino intensivo: optimización de consultas
Dado que, reitero, en ese momento no estaba claro qué más cambiar en la consulta para acelerarla, se eligió el camino de cambiar el diseño de las tablas.
Así que surge la pregunta principal: ¿qué y cómo vamos a cambiar?
Condiciones iniciales
Primero, tenemos este ERD (mostrado de manera simplificada):
Características principales:
- relaciones de 'muchos a muchos'
- la tabla ya tiene una clave potencial de partición
Consulta original:
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';
Resultados de la ejecución en la base de datos de prueba:
Costo : 502 997.55
Tiempo de ejecución: 505 segundos.
¿Qué vemos? Una consulta normal, en un rango temporal.
Hacemos una suposición lógica simple: si hay una muestra de un corte temporal, ¿nos ayudará? Correcto, la partición.
¿Qué particionar?
A primera vista, la elección es obvia: particionar declarativamente la tabla 'shipment' por la clave 'SHIPMENT_DATE' (adelantándonos mucho, el resultado en producción fue un poco diferente).
¿Cómo particionar?
Esta pregunta tampoco es demasiado complicada. Afortunadamente, en PostgreSQL 10, ahora existe un mecanismo de particionado más amigable.
Así que:
- Guardamos el volcado de la tabla original — pg_dump source_table
- Eliminamos la tabla original — drop table source_table
- Creamos la tabla padre con particionado por rango — create table source_table
- Creamos las secciones — create table source_table, create index
- Importamos el volcado creado en el paso 1 — pg_restore
Scripts para particionado
Para simplificar y facilitar, los pasos 2, 3 y 4 se han combinado en un solo script.
Así que:
Guardamos el volcado de la tabla original
pg_dump postgres --file=\/dump\/shipment.dmp --format=c --table=shipment --verbose > \/dump\/shipment.log 2>&1Eliminamos la tabla original + Creamos la tabla padre con particionado por rango + Creamos las secciones
--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 'CREAR TABLA TEMPORAL PARA SHIPMENT_DATE';
CREATE TEMP TABLE tmp_shipment_date as select distinct "SHIPMENT_DATE" from shipment order by "SHIPMENT_DATE" ;
RAISE NOTICE 'ELIMINAR TABLA 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 'CREAR PARTICIONES PARA LA TABLA 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') = 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 'NOMBRE DEL ÍNDICE =%',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
$$;Importar el volcado
pg_restore -d postgres --data-only --format=c --table=shipment --verbose shipment.dmp > /tmp/data_dump/shipment_restore.log 2>&1Verificamos los resultados de la partición
¿Qué tenemos al final? El texto completo del plan de ejecución es largo y aburrido, por lo que se puede limitar a las cifras finales.
Era
Costo: 502 997.55
Tiempo de ejecución: 505 segundos.
Se volvió
Costo: 77 872.36
Tiempo de ejecución: 79 segundos.
Es un buen resultado. Hemos reducido el costo y el tiempo de ejecución. Así, el uso de la partición da el efecto esperado y, en general, sin sorpresas.
Alegrar al cliente
Los resultados de las pruebas fueron presentados al cliente para su revisión. Y después de su examen, se emitió un veredicto algo inesperado: 'Excelente, seccione la tabla «data».
Sí, pero investigamos una tabla diferente, «shipment», la tabla «data» no tiene el campo «SHIPMENT_DATE».
No hay problema, añadan, cambien. Lo principal es que al cliente le satisfaga el resultado, los detalles de implementación no son tan importantes.
Seccionamos la tabla principal «data»
En general, no surgieron dificultades especiales. Aunque, el algoritmo de partición, por supuesto, ha cambiado un poco.
Añadimos la columna «SHIPMENT_DATA» en la tabla «data»
psql -h host -U database -d user
=> ALTER TABLE data ADD COLUMN "SHIPMENT_DATE" timestamp without time zone ;Rellenamos los valores de la columna «SHIPMENT_DATA» en la tabla «data» con los valores de la columna homónima de la tabla «shipment»
-----------------------------
--update_data.sql
--actualización para la tabla "data" alterada a los valores de "shipment_data" de la tabla "shipment"
--versión 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
$$;Guardamos el volcado de la tabla «data»
pg_dump postgres --file=\/dump\/data.dmp --format=c --table=data --verbose > \/dump\/data.log 2>&1Recreamos la tabla particionada «data»
--create_partition_data.sql
--crear particiones para la tabla "wafer data" por rango de la columna "shipment_data" con una duración de un mes
--versión 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 'CREAR TABLA TEMPORAL PARA SHIPMENT_DATE';
CREATE TEMP TABLE tmp_shipment_date as select distinct "SHIPMENT_DATE" from shipment order by "SHIPMENT_DATE";
RAISE NOTICE 'ELIMINAR TABLA data';
drop table data cascade;
RAISE NOTICE 'CREAR TABLA PARTICIONADA 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 'CREAR PARTICIONES PARA LA TABLA 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");
--Iniciar límites
IF begin_year = '0' THEN
RAISE NOTICE '***Iniciar límites';
first_flag := true; --marcador de primera vez
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;
-- Comprobar fecha actual en límites NO para la primera vez
RAISE NOTICE 'Fecha actual = %',to_char(to_date(current_year||'.'||current_month, 'YYYY.MM'), 'YYYY.MM');
RAISE NOTICE 'Fecha de inicio = %',to_char(to_date(begin_year||'.'||begin_month, 'YYYY.MM'), 'YYYY.MM');
RAISE NOTICE 'Siguiente fecha = %',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 '***CONTINUAR';
CONTINUE;
ELSE
--Nuevos límites solo para la segunda vez y posteriores
RAISE NOTICE '***NUEVOS LÍMITES';
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 'NÚMERO DE PARTICIÓN % , NOMBRE DE LA TABLA =%',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 'NOMBRE DEL ÍNDICE =%',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 'NOMBRE DEL ÍNDICE =%',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 'NOMBRE DEL ÍNDICE =%',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 'NOMBRE DEL ÍNDICE =%',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 'NOMBRE DEL ÍNDICE =%',index_name;
EXECUTE format('CREATE INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree ("SHIPMENT_DATE") TABLESPACE pg_default ');
--Eliminar el marcador de primera vez
first_flag := false;
END LOOP;
end
$$;
Estamos cargando el volcado creado en el paso 3.
pg_restore -h host -u user -d database --data-only --format=c --table=data --verbose data.dmp > data_restore.log 2>&1Creamos una sección separada para los datos antiguos
---------------------------------------------------
--create_partition_for_old_dates.sql
--crear particiones para mantener fechas antiguas
--versión 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 'La fecha antigua es %',rec_shipment_date.min_date ;
partition_name := 'data_old_dates' ;
RAISE NOTICE 'EL NOMBRE DE LA PARTICIÓN ES %',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
$$;Resultados finales:
Era
Costo: 502 997.55
Tiempo de ejecución: 505 segundos.
Se volvió
Costo: 68 533.70
Tiempo de ejecución: 69 segundos
Es bastante bueno, muy bueno. Y considerando que en el camino se logró dominar más o menos el mecanismo de particionamiento en PostgreSQL 10 — es un excelente resultado.
Un desvío lírico
¿Se puede hacer aún mejor? — SÍ, SE PUEDE!Para ello, es necesario utilizar MATERIALIZED VIEW.
CREATE MATERIALIZED VIEW 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 ;
Una vez más, reescribimos la consulta:
Consulta utilizando materialized view
SELECCIONAR
p."PARAMETER_ID" como parameter_id,
pc."PC_NAME" como pc_name,
pc."CUSTOMER_PARTNUMBER" como customer_partnumber,
w."LASERMARK" como lasermark,
w."LOTID" como lotid,
w."REPORTED_VALUE" como reported_value,
w."LOWER_SPEC_LIMIT" como lower_spec_limit,
w."UPPER_SPEC_LIMIT" como upper_spec_limit,
p."TYPE_CALCUL" como type_calcul,
s."SHIPMENT_NAME" como shipment_name,
s."SHIPMENT_DATE" como shipment_date,
extraer(año de s."SHIPMENT_DATE") como año,
extraer(mes de s."SHIPMENT_DATE") como mes,
s."REPORT_NAME" como report_name,
p."STC_NAME" como STC_name,
p."CUSTOMERPARAM_NAME" como customerparam_name
DE datos w INNER JOIN envío s EN s."SHIPMENT_ID" = w."SHIPMENT_ID"
INNER JOIN parámetros p EN p."PARAMETER_ID" = w."PARAMETER_ID"
INNER JOIN shipment_pc sp EN s."SHIPMENT_ID" = sp."SHIPMENT_ID"
INNER JOIN pc pc EN pc."PC_ID" = sp."PC_ID"
INNER JOIN LASERMARK_VIEW md EN md."SHIPMENT_DATE" = s."SHIPMENT_DATE" Y md."LASERMARK" = w."LASERMARK"
DONDE
s."SHIPMENT_DATE" >= '2018-07-01' Y s."SHIPMENT_DATE" <= '2018-09-30';
Y obtenemos otro resultado:
Era
Costo: 502 997.55
Tiempo de ejecución: 505 segundos
Se volvió
Costo: 42 481.16
Tiempo de ejecución: 43 segundos.
Aunque, por supuesto, un resultado tan prometedor es engañoso, ya que es necesario actualizar las presentaciones. Así que el tiempo total para obtener los datos no ayudará mucho. Pero como experimento, es bastante interesante.
En realidad, como resultó, gracias otra vez y a Habr! -
Póscrito
Así que, el cliente está satisfecho. Y necesitan aprovechar la situación.
Nueva tarea: ¿Qué se puede idear para profundizar y ampliar?
Y aquí recuerdo - chicos, en realidad no tenemos monitoreo de nuestras bases de datos PostgreSQL.
Hablando sinceramente, hay cierto monitoreo en forma de Cloud Watch en AWS, pero ¿cuál es la utilidad de ese monitoreo para un DBA? En general, prácticamente ninguna.
Si hay una oportunidad de hacer algo útil e interesante también para uno mismo, no se puede dejar pasar esa oportunidad...
PORQUE
Así fue como llegamos a lo más interesante:
3 de diciembre de 2018.
Decisión de comenzar los trabajos para investigar las posibilidades existentes de monitoreo del rendimiento de las consultas de PostgreSQL.
Pero esa es otra historia por completo.
Continuará...
Fuente: habr.com
