En los procesadores modernos hay muchos núcleos. Durante años, las aplicaciones han enviado consultas a bases de datos en paralelo. Si se trata de una consulta de informes a múltiples filas en una tabla, se ejecuta más rápido cuando utiliza varios núcleos de CPU, y en PostgreSQL esto es posible a partir de la versión 9.6.
Se necesitó 3 años para implementar la función de consultas paralelas, y fue necesario reescribir el código en diferentes etapas de la ejecución de las consultas. PostgreSQL 9.6 introdujo la infraestructura para futuras mejoras en el código. En versiones posteriores, otros tipos de consultas también se ejecutan en paralelo.
Limitaciones
- No active la ejecución paralela si todos los núcleos ya están ocupados, de lo contrario, otras consultas se ralentizarán.
- Lo más importante es que el procesamiento paralelo con altos valores de WORK_MEM utiliza mucha memoria: cada conexión hash o cada ordenación consume memoria en la cantidad de work_mem.
- Las consultas OLTP con baja latencia no pueden acelerarse mediante la ejecución paralela. Y si una consulta devuelve una sola fila, el procesamiento paralelo solo la ralentizará.
- A los desarrolladores les gusta usar el benchmark TPC-H. Quizás tenga consultas similares para una ejecución paralela óptima.
- Solo las consultas SELECT sin bloqueo de predicado se ejecutan en paralelo.
- A veces, una correcta indexación es mejor que el escaneo secuencial de una tabla en modo paralelo.
- Las pausas en las consultas y los cursores no son compatibles.
- Las funciones de ventana y las funciones agregadas de conjuntos ordenados no son paralelas.
- No obtienes ninguna ventaja en la carga de trabajo de entrada/salida.
- No existen algoritmos de ordenación paralelos. Pero las consultas con ordenaciones pueden ejecutarse en paralelo en ciertos aspectos.
- Reemplace CTE (WITH …) por un SELECT anidado para habilitar el procesamiento paralelo.
- Las envolturas de datos de terceros aún no admiten el procesamiento paralelo (¡y podrían hacerlo!).
- FULL OUTER JOIN no es compatible.
- max_rows desactiva el procesamiento paralelo.
- Si hay una función en la consulta que no está marcada como PARALLEL SAFE, será de un solo hilo.
- El nivel de aislamiento de la transacción SERIALIZABLE desactiva el procesamiento paralelo.
Entorno de prueba
Los desarrolladores de PostgreSQL intentaron reducir el tiempo de respuesta de las consultas del benchmark TPC-H. Descargue el benchmark y . Este uso no oficial del benchmark TPC-H no es para comparar bases de datos o hardware.
- Descargue TPC-H_Tools_v2.17.3.zip (o una versión más nueva) .
- Renombre makefile.suite a Makefile y modifique según se describe aquí: . Compile el código con el comando make.
- Genere los datos:
./dbgen -s 10crea una base de datos de 23 GB. Esto es suficiente para observar la diferencia en el rendimiento de consultas paralelas y no paralelas. - Convierta los archivos
tblencsv con forysed. - Clone el repositorio
pg_tpchy copie los archivoscsvenpg_tpch/dss/data. - Cree consultas con el comando
qgen. - Cargue los datos en la base con el comando
./tpch.sh.
Escaneo secuencial paralelo
Puede ser más rápido no debido a la lectura paralela, sino porque los datos están distribuidos entre muchos núcleos de la CPU. En los sistemas operativos modernos, los archivos de datos de PostgreSQL se almacenan en caché de manera eficiente. Con la lectura anticipada, se puede obtener del almacenamiento un bloque mayor de lo que solicita el demonio PG. Por lo tanto, el rendimiento de la consulta no está limitado por la entrada y salida del disco. Consume ciclos de CPU para:
- leer filas una por una de las páginas de la tabla;
- comparar los valores de las filas y las condiciones
WHERE.
Realicemos una consulta simple select:
tpch=# explain analyze select l_quantity as sum_qty from lineitem where l_shipdate <= date '1998-12-01' - interval '105' day;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Seq Scan on lineitem (cost=0.00..1964772.00 rows=58856235 width=5) (actual time=0.014..16951.669 rows=58839715 loops=1)
Filter: (l_shipdate <= '1998-08-18 00:00:00'::timestamp without time zone)
Rows Removed by Filter: 1146337
Planning Time: 0.203 ms
Execution Time: 19035.100 msEl escaneo secuencial devuelve demasiadas filas sin agregación, por lo que la consulta se ejecuta con un solo núcleo de CPU.
Si agregamos SUM(), se puede ver que dos procesos de trabajo ayudan a acelerar la consulta:
explain analyze select sum(l_quantity) as sum_qty from lineitem where l_shipdate Gather (cost=1589701.91..1589702.12 rows=2 width=32) (actual time=8553.241..8555.067 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=1588701.91..1588701.92 rows=1 width=32) (actual time=8547.546..8547.546 rows=1 loops=3)
-> Parallel Seq Scan on lineitem (cost=0.00..1527393.33 rows=24523431 width=5) (actual time=0.038..5998.417 rows=19613238 loops=3)
Filter: (l_shipdate <= '1998-08-18 00:00:00'::timestamp without time zone)
Rows Removed by Filter: 382112
Planning Time: 0.241 ms
Execution Time: 8555.131 msAgregación paralela
El nodo 'Parallel Seq Scan' produce filas para la agregación parcial. El nodo 'Partial Aggregate' recorta estas filas utilizando SUM(). Al final, el contador SUM de cada proceso de trabajo se recopila en el nodo 'Gather'.
El resultado final se calcula con el nodo «Finalize Aggregate». Si tiene sus propias funciones de agregación, no olvide marcarlas como «parallel safe».
Número de procesos de trabajo
El número de procesos de trabajo se puede aumentar sin reiniciar el servidor:
explain analyze select sum(l_quantity) as sum_qty from lineitem where l_shipdate Gather (cost=1589701.91..1589702.12 rows=2 width=32) (actual time=8553.241..8555.067 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=1588701.91..1588701.92 rows=1 width=32) (actual time=8547.546..8547.546 rows=1 loops=3)
-> Parallel Seq Scan on lineitem (cost=0.00..1527393.33 rows=24523431 width=5) (actual time=0.038..5998.417 rows=19613238 loops=3)
Filter: (l_shipdate <= '1998-08-18 00:00:00'::timestamp without time zone)
Rows Removed by Filter: 382112
Planning Time: 0.241 ms
Execution Time: 8555.131 ms¿Qué está ocurriendo aquí? El número de procesos de trabajo se ha duplicado, y la solicitud se volvió solo 1,6599 veces más rápida. Los cálculos son interesantes. Teníamos 2 procesos de trabajo y 1 líder. Después del cambio, ahora son 4+1.
Nuestra aceleración máxima por procesamiento paralelo: 5/3 = 1,66(6) veces.
¿Cómo funciona?
Procesos
La ejecución de una solicitud siempre comienza con el proceso líder. El líder realiza toda la parte no paralela y parte del procesamiento paralelo. Otros procesos que realizan las mismas solicitudes se llaman procesos de trabajo. El procesamiento paralelo utiliza la infraestructura (a partir de la versión 9.4). Dado que otras partes de PostgreSQL utilizan procesos, no hilos, una solicitud con 3 procesos de trabajo podría ser 4 veces más rápida que el procesamiento tradicional.
Interacción
Los procesos de trabajo se comunican con el líder a través de una cola de mensajes (basada en memoria compartida). Cada proceso tiene 2 colas: una para errores y otra para tuplas.
¿Cuántos procesos de trabajo se necesitan?
El límite mínimo es establecido por el parámetro . Luego, el ejecutor de la consulta toma procesos de trabajo del conjunto, limitado por el parámetro . El último límite es , es decir, el número total de procesos en segundo plano.
Si no se puede asignar un proceso de trabajo, el procesamiento será de un solo proceso.
El planificador de consultas puede reducir los procesos de trabajo dependiendo del tamaño de la tabla o índice. Para esto, hay parámetros y .
set min_parallel_table_scan_size='8MB'
8MB tabla => 1 trabajador
24MB tabla => 2 trabajadores
72MB tabla => 3 trabajadores
x => log(x / min_parallel_table_scan_size) / log(3) + 1 trabajadorCada vez que la tabla es 3 veces más grande que min_parallel_(index|table)_scan_size, PostgreSQL agrega un proceso de trabajo. El número de procesos de trabajo no se basa en costos. La dependencia cíclica complica las implementaciones complejas. En su lugar, el planificador usa reglas simples.
En la práctica, estas reglas no siempre son adecuadas para producción, por lo que se puede cambiar el número de procesos de trabajo para una tabla específica: ALTER TABLE … SET (parallel_workers = N).
¿Por qué no se utiliza el procesamiento paralelo?
Además de una larga lista de restricciones, hay más comprobaciones de costos:
— para evitar el procesamiento paralelo de consultas cortas. Este parámetro estima el tiempo para la preparación de la memoria, el inicio del proceso y el intercambio inicial de datos.
: la comunicación del líder con los trabajadores puede prolongarse proporcionalmente al número de tuplas de los procesos de trabajo. Este parámetro calcula los costos del intercambio de datos.
Conexiones de bucles anidados — Nested Loop Join
PostgreSQL 9.6+ puede realizar bucles anidados en paralelo: es una operación sencilla.
explain (costs off) select c_custkey, count(o_orderkey)
from customer left outer join orders on
c_custkey = o_custkey and o_comment not like '%specialposits%'
group by c_custkey;
QUERY PLAN
--------------------------------------------------------------------------------------
Finalize GroupAggregate
Group Key: customer.c_custkey
-> Gather Merge
Workers Planned: 4
-> Partial GroupAggregate
Group Key: customer.c_custkey
-> Nested Loop Left Join
-> Parallel Index Only Scan using customer_pkey on customer
-> Index Scan using idx_orders_custkey on orders
Index Cond: (customer.c_custkey = o_custkey)
Filter: ((o_comment)::text !~~ '%specialposits%'::text)La recopilación ocurre en la última etapa, por lo que el Nested Loop Left Join es una operación paralela. El Parallel Index Only Scan apareció solo en la versión 10. Funciona de manera similar al escaneo secuencial paralelo. La condición c_custkey = o_custkey lee un pedido por cada fila del cliente. Por lo tanto, no es paralelo.
Unión hash — Hash Join
Cada proceso de trabajo crea su tabla hash hasta PostgreSQL 11. Y si hay más de cuatro procesos, el rendimiento no aumentará. En la nueva versión, la tabla hash es compartida. Cada proceso de trabajo puede usar WORK_MEM para crear una tabla hash.
seleccionar
l_shipmode,
suma(caso
cuando o_orderpriority = '1-URGENTE'
o o_orderpriority = '2-ALTO'
entonces 1
else 0
fin) como high_line_count,
suma(caso
cuando o_orderpriority '1-URGENTE'
y o_orderpriority '2-ALTO'
entonces 1
else 0
fin) como low_line_count
de
órdenes,
línea
donde
o_orderkey = l_orderkey
y l_shipmode en ('CORREO', 'AÉREO')
y l_commitdate < l_receiptdate
y l_shipdate = fecha '1996-01-01'
y l_receiptdate < fecha '1996-01-01' + intervalo '1' año
grupo por
l_shipmode
ordenar por
l_shipmode
LIMITE 1;
PLAN DE CONSULTA
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Límite (costo=1964755.66..1964961.44 filas=1 ancho=27) (tiempo real=7579.592..7922.997 filas=1 bucles=1)
-> Finalizar GroupAggregate (costo=1964755.66..1966196.11 filas=7 ancho=27) (tiempo real=7579.590..7579.591 filas=1 bucles=1)
Clave de Grupo: línea.l_shipmode
-> Reunir Junta (costo=1964755.66..1966195.83 filas=28 ancho=27) (tiempo real=7559.593..7922.319 filas=6 bucles=1)
Trabajadores Planeados: 4
Trabajadores Lanzados: 4
-> Agrupar Parcial (costo=1963755.61..1965192.44 filas=7 ancho=27) (tiempo real=7548.103..7564.592 filas=2 bucles=5)
Clave de Grupo: línea.l_shipmode
-> Ordenar (costo=1963755.61..1963935.20 filas=71838 ancho=27) (tiempo real=7530.280..7539.688 filas=62519 bucles=5)
Clave de Orden: línea.l_shipmode
Método de Orden: fusión externa Disco: 2304kB
Trabajador 0: Método de Orden: fusión externa Disco: 2064kB
Trabajador 1: Método de Orden: fusión externa Disco: 2384kB
Trabajador 2: Método de Orden: fusión externa Disco: 2264kB
Trabajador 3: Método de Orden: fusión externa Disco: 2336kB
-> Unión Hash Paralela (costo=382571.01..1957960.99 filas=71838 ancho=27) (tiempo real=7036.917..7499.692 filas=62519 bucles=5)
Condición Hash: (línea.l_orderkey = órdenes.o_orderkey)
-> Escaneo Secuencial Paralelo en línea (costo=0.00..1552386.40 filas=71838 ancho=19) (tiempo real=0.583..4901.063 filas=62519 bucles=5)
Filtro: ((l_shipmode = CUALQUIERA ('{CORREO,AÉREO}'::bpchar[])) Y (l_commitdate < l_receiptdate) Y (l_shipdate = '1996-01-01'::fecha) Y (l_receiptdate < '1997-01-01 00:00:00'::marca de tiempo sin zona horaria))
Filas Eliminadas por Filtro: 11934691
-> Hash Paralelo (costo=313722.45..313722.45 filas=3750045 ancho=20) (tiempo real=2011.518..2011.518 filas=3000000 bucles=5)
Cubos: 65536 Lotes: 256 Uso de Memoria: 3840kB
-> Escaneo Secuencial Paralelo en órdenes (costo=0.00..313722.45 filas=3750045 ancho=20) (tiempo real=0.029..995.948 filas=3000000 bucles=5)
Tiempo de Planificación: 0.977 ms
Tiempo de Ejecución: 7923.770 msLa consulta 12 de TPC-H muestra claramente la unión hash paralela. Cada proceso de trabajo participa en la creación de una tabla hash común.
Unión de fusión — Merge Join
La unión de fusión no es paralela por su naturaleza. No te preocupes si esta es la última etapa de la consulta, aún puede ejecutarse en paralelo.
-- Consulta 2 de TPC-H
explicar (costos apagados) seleccionar s_acctbal, s_name, n_name, p_partkey, p_mfgr, s_address, s_phone, s_comment
desde parte, proveedor, partsupp, nación, región
donde
p_partkey = ps_partkey
y s_suppkey = ps_suppkey
y p_size = 36
y p_type como '%BRASS'
y s_nationkey = n_nationkey
y n_regionkey = r_regionkey
y r_name = 'AMÉRICA'
y ps_supplycost = (
seleccionar
min(ps_supplycost)
desde partsupp, proveedor, nación, región
donde
p_partkey = ps_partkey
y s_suppkey = ps_suppkey
y s_nationkey = n_nationkey
y n_regionkey = r_regionkey
y r_name = 'AMÉRICA'
)
ordenar por s_acctbal desc, n_name, s_name, p_partkey
LIMITE 100;
PLAN DE CONSULTA
----------------------------------------------------------------------------------------------------------
Límite
-> Ordenar
Clave de Orden: supplier.s_acctbal DESC, nation.n_name, supplier.s_name, part.p_partkey
-> Unión de Fusiones
Condición de fusión: (part.p_partkey = partsupp.ps_partkey)
Filtro de unión: (partsupp.ps_supplycost = (Subplan 1))
-> Reunir fusión
Trabajadores planificados: 4
-> Escaneo de índice paralelo utilizando <strong>part_pkey</strong> en parte
Filtro: (((p_type)::text ~~ '%BRASS'::text) Y (p_size = 36))
-> Materializar
-> Ordenar
Clave de Orden: partsupp.ps_partkey
-> Bucle anidado
-> Bucle anidado
Filtro de unión: (nation.n_regionkey = region.r_regionkey)
-> Escaneo secuencial en región
Filtro: (r_name = 'AMÉRICA'::bpchar)
-> Unión hash
Condición hash: (supplier.s_nationkey = nation.n_nationkey)
-> Escaneo secuencial en proveedor
-> Hash
-> Escaneo secuencial en nación
-> Escaneo de índice utilizando idx_partsupp_suppkey en partsupp
Condición de índice: (ps_suppkey = supplier.s_suppkey)
Subplan 1
-> Agregar
-> Bucle anidado
Filtro de unión: (nation_1.n_regionkey = region_1.r_regionkey)
-> Escaneo secuencial en región region_1
Filtro: (r_name = 'AMÉRICA'::bpchar)
-> Bucle anidado
-> Bucle anidado
-> Escaneo de índice utilizando idx_partsupp_partkey en partsupp partsupp_1
Condición de índice: (part.p_partkey = ps_partkey)
-> Escaneo de índice utilizando supplier_pkey en proveedor supplier_1
Condición de índice: (s_suppkey = partsupp_1.ps_suppkey)
-> Escaneo de índice utilizando nation_pkey en nación nación_1
Condición de índice: (n_nationkey = supplier_1.s_nationkey)El nodo 'Merge Join' se encuentra por encima de 'Gather Merge'. Por lo tanto, la fusión no utiliza procesamiento paralelo. Sin embargo, el nodo 'Parallel Index Scan' aún ayuda con el segmento part_pkey.
Unión por secciones
En PostgreSQL 11 desactivada por defecto: tiene un costo de planificación muy alto. Las tablas con particionamiento similar se pueden unir sección por sección. Así, Postgres utilizará tablas hash más pequeñas. Cada unión de secciones puede ser paralela.
tpch=# set enable_partitionwise_join=t;
tpch=# explain (costs off) select * from prt1 t1, prt2 t2
where t1.a = t2.b and t1.b = 0 and t2.b between 0 and 10000;
PLAN DE CONSULTA
---------------------------------------------------
Append
-> Hash Join
Condición Hash: (t2.b = t1.a)
-> Secuencia de Escaneo en prt2_p1 t2
Filtro: ((b >= 0) AND (b <= 10000))
-> Hash
-> Secuencia de Escaneo en prt1_p1 t1
Filtro: (b = 0)
-> Hash Join
Condición Hash: (t2_1.b = t1_1.a)
-> Secuencia de Escaneo en prt2_p2 t2_1
Filtro: ((b >= 0) AND (b <= 10000))
-> Hash
-> Secuencia de Escaneo en prt1_p2 t1_1
Filtro: (b = 0)
tpch=# set parallel_setup_cost = 1;
tpch=# set parallel_tuple_cost = 0.01;
tpch=# explain (costs off) select * from prt1 t1, prt2 t2
where t1.a = t2.b and t1.b = 0 and t2.b between 0 and 10000;
PLAN DE CONSULTA
-----------------------------------------------------------
Gather
Trabajadores Planificados: 4
-> Append Paralelo
-> Join Hash Paralelo
Condición Hash: (t2_1.b = t1_1.a)
-> Secuencia de Escaneo Paralelo en prt2_p2 t2_1
Filtro: ((b >= 0) AND (b <= 10000))
-> Hash
-> Secuencia de Escaneo Paralelo en prt1_p2 t1_1
Filtro: (b = 0)
-> Join Hash Paralelo
Condición Hash: (t2.b = t1.a)
-> Secuencia de Escaneo Paralelo en prt2_p1 t2
Filtro: ((b >= 0) AND (b <= 10000))
-> Hash
-> Secuencia de Escaneo Paralelo en prt1_p1 t1
Filtro: (b = 0)Lo principal es que la unión por secciones puede ser paralela solo si estas secciones son lo suficientemente grandes.
Adición paralela — Parallel Append
puede ser utilizada en lugar de diferentes bloques en diferentes procesos de trabajo. Esto suele suceder con consultas UNION ALL. La desventaja es menos paralelismo, ya que cada proceso de trabajo maneja solo 1 consulta.
Aquí se están ejecutando 2 procesos de trabajo, aunque se han habilitado 4.
tpch=# explain (costs off) select sum(l_quantity) as sum_qty from lineitem where l_shipdate <= date '1998-12-01' - interval '105' day union all select sum(l_quantity) as sum_qty from lineitem where l_shipdate Parallel Append
-> Aggregate
-> Seq Scan on lineitem
Filter: (l_shipdate Aggregate
-> Seq Scan on lineitem lineitem_1
Filter: (l_shipdate <= '1998-08-18 00:00:00'::timestamp without time zone)Las variables más importantes
- WORK_MEM limita la cantidad de memoria para cada proceso, no solo para las consultas: work_mem procesos conexiones = mucha memoria.
- — cuántos procesos de trabajo utilizará el programa ejecutante para el procesamiento paralelo del plan.
- — ajusta el número total de procesos de trabajo según el número de núcleos de CPU en el servidor.
- — lo mismo, pero para procesos de trabajo paralelos.
Resultados
A partir de la versión 9.6, el procesamiento paralelo puede mejorar significativamente el rendimiento de consultas complejas que escanean muchas filas o índices. En PostgreSQL 10, el procesamiento paralelo está habilitado por defecto. Recuerde desactivarlo en servidores con alta carga de trabajo OLTP. Los escaneos secuenciales o escaneos de índices consumen muchos recursos. Si no está ejecutando informes sobre todo el conjunto de datos, las consultas se pueden hacer más eficientes simplemente agregando índices faltantes o utilizando la partición adecuada.
Enlaces
Fuente: habr.com
