Ajuste de rendimiento de PostgreSQL para una ejecución de consulta más rápida

Objetivo

Nuestro objetivo es hacer que una ejecución de consultas ficticias se ejecute más rápido en la base de datos PostgreSQL utilizando solo las herramientas incorporadas disponibles
en la base de datos.

Sistema operativo y versiones de software

• Sistema operativo: Red Hat Enterprise Linux 7.5
• Software: PostgreSQL Server 9.2

Requisitos

PostgreSQL Server Base Instalar en funcionamiento. Acceso a la herramienta de línea de comandos PSQL y propiedad de la base de datos de ejemplo.

Convenciones

• # - requiere que los comandos de Linux dados se ejecuten con privilegios raíz directamente como un usuario raíz o mediante el uso de sudo dominio
• ps - Dados los comandos de Linux para ser ejecutados como un usuario regular no privilegiado

Introducción

PostgreSQL es una base de datos de código abierto confiable disponible en el repositorio de muchas distribuciones modernas. La facilidad de uso, la capacidad de usar extensiones y la estabilidad que proporciona a su popularidad a su popularidad.
Mientras proporciona la funcionalidad base, como responder a las consultas SQL, almacenar datos insertados de manera consistente, manejar transacciones, etc. La mayoría de las soluciones de bases de datos maduras proporcionan herramientas y conocimientos sobre cómo
Sintonice la base de datos, identifique posibles cuellos de botella y pueda resolver los problemas de rendimiento que se destinan a suceder a medida que crece el sistema alimentado por la solución dada.

PostgreSQL no es una excepción, y en este
Guía Usaremos la herramienta incorporada explicar Para hacer una consulta de carrera lenta, complete más rápido. Está lejos de ser una base de datos del mundo real, pero uno puede tomar la pista sobre el uso de las herramientas incorporadas. Usaremos un servidor PostgreSQL versión 9.2 en Red Hat Linux 7.5, pero las herramientas que se muestran en esta guía también están presentes en versiones de base de datos y del sistema operativo mucho más antiguos.

El problema a resolver

Considere esta tabla simple (los nombres de la columna se explican por sí mismos):

foobardb =# \ d+ tabla de empleados "público.empleados "columna | tipo | modificadores | almacenamiento | estadísticas objetivo | descripción ------------------+---------+------- ----------------------------------------------+--- -------+--------------+--------- regClass) | Main | | First_Name | Text | No Null | Extended | | Last_Name | Text | no nulo | extendido | | birth_year | numérico | no nulo | main | | birth_month | numérico | no nulo | main | | birth_dayofmonth | numérico | no nulo | Main | | Índices: "Empleados_PKEY" Clave primaria, btree (emp_id) tiene OID: no 

Con registros como:

foobardb =# select * del límite de empleados 2; Emp_id | First_Name | Last_Name | Birth_year | Birth_month | Birth_dayofmonth --------+------------+-----------+------------+- -----------+------------------ 1 | Emily | James | 1983 | 3 | 20 2 | John | Smith | 1990 | 8 | 12 

En este ejemplo, somos la buena compañía e implementamos una aplicación llamada HBApp que envía un correo electrónico de "feliz cumpleaños" al empleado en su cumpleaños. La aplicación consulta la base de datos todas las mañanas para encontrar destinatarios para el día (antes de las horas de trabajo, no queremos matar nuestra base de datos de recursos humanos por amabilidad).
La aplicación ejecuta la siguiente consulta para encontrar los destinatarios:

foobardb =# select Emp_id, First_Name, Last_Name de los empleados donde birth_month = 3 y birth_dayofmonth = 20; Emp_id | First_Name | Last_Name --------+------------+----------- 1 | Emily | Jaime 

Todo funciona bien, los usuarios reciben su correo. Muchas otras aplicaciones usan la base de datos, y la tabla de empleados dentro, como Contabilidad y BI. La buena compañía crece y, por lo tanto, crece la mesa de los empleados. Con el tiempo, la aplicación se ejecuta demasiado tiempo, y la ejecución se superpone con el inicio de las horas de trabajo, lo que resulta en el tiempo de respuesta de la base de datos lenta en aplicaciones de misión crítica. Tenemos que hacer algo para que esta consulta se ejecute más rápido, o la aplicación será descendente, y con ella habrá menos amabilidad en la buena compañía.

Para este ejemplo, no utilizaremos ninguna herramienta avanzada para resolver el problema, solo una proporcionada por la instalación base. Veamos cómo el planificador de la base de datos ejecuta la consulta con explicar.

No estamos probando en producción; Creamos una base de datos para probar, creamos la tabla e insertamos a dos empleados mencionados anteriormente. Usamos los mismos valores para la consulta todo el tiempo en este tutorial,
Entonces, en cualquier carrera, solo un récord coincidirá con la consulta: Emily James. Luego ejecutamos la consulta con precedente Explicar analizar Para ver cómo se ejecuta con datos mínimos en la tabla:

foobardb =# explicar analizar select emp_id, first_name, last_name de los empleados donde birth_month = 3 y birth_dayofmonth = 20; Plan de consulta ------------------------------------------------ -------------------------------------------------- --- SEQ Scan en los empleados (costo = 0.00 ... 15.40 filas = 1 ancho = 96) (tiempo real = 0.023 ... 0.025 filas = 1 bucles = 1) Filtro: ((birth_month = 3 :: numérico) y (birth_dayofmonth = 20 :: numérico)) filas eliminadas por filtro: 1 tiempo de ejecución total: 0.076 MS (4 filas) 

Eso es muy rápido. Posiblemente tan rápido como fue cuando la compañía desplegó por primera vez el HBAPP. Mimicemos el estado de la producción actual foobardb Al cargar tantos empleados (falsos) en la base de datos como lo hemos hecho en producción (nota: necesitaremos el mismo tamaño de almacenamiento en la base de datos de prueba que en la producción).

Simplemente usaremos bash para poblar la base de datos de prueba (suponiendo que tengamos 500.000 empleados en producción):

$ para j en 1 ... 500000; hacer eco "inserte en los empleados (First_Name, Last_Name, Birth_Year, Birth_Month, Birth_dayOfMonth) Valores ('Usuario $ J', 'Test', 1900,01,01);"; hecho | PSQL -D FOOBARDB 

Ahora tenemos 500002 empleados:

foobardb =# select Count (*) de los empleados; Conteo -------- 500002 (1 fila) 

Vamos a ejecutar la consulta de explicación nuevamente:

foobardb =# explicar analizar select emp_id, first_name, last_name de los empleados donde birth_month = 3 y birth_dayofmonth = 20; Plan de consulta ------------------------------------------------ -------------------------------------------------- -------- SEQ Scan en los empleados (costo = 0.00 ... 11667.63 filas = 1 ancho = 22) (tiempo real = 0.012 ... 150.998 filas = 1 bucles = 1) Filtro: ((birth_month = 3 :: numérico) y (birth_dayofmonth = 20 :: numérico)) filas eliminadas por filtro: 500001 tiempo de ejecución total: 151.059 MS 

Todavía tenemos solo un partido, pero la consulta es significativamente más lenta. Deberíamos notar el primer nodo del planificador: SEQ Scan que significa escaneo secuencial: la base de datos lee todo
tabla, aunque solo necesitamos un registro, como un grep iría intento. De hecho, puede ser realmente más lento que Grep. Si exportamos la tabla a un archivo CSV llamado /tmp/exp500k.CSV:

 foobardb =# Copiar empleados a '/tmp/exp500k.CSV 'delimitador', 'CSV Header; Copiar 500002 

Y grep la información que necesitamos (buscamos el día 20 del tercer mes, los dos últimos valores en el archivo CSV en cada
línea):

$ TIME GREP ", 3,20" /TMP /EXP500K.CSV 1, Emily, James, 1983,3,20 Real 0m0.067S usuario 0m0.018S SYS 0M0.010S 

Esto es, a un lado, el almacenamiento en caché, se considera más lento y más lento a medida que la mesa crece.

La solución es de indexación de causa. Ningún empleado puede tener más de una fecha de nacimiento, que consiste en exactamente una Año de nacimiento, mes de nacimiento y Birth_dayOfMonth - Entonces, estos tres campos proporcionan un valor único para ese usuario en particular. Y un usuario es identificado por su emp_id (Puede haber más de un empleado en la empresa con el mismo nombre). Si declaramos una restricción en estos cuatro campos, también se creará un índice implícito:

foobardb =# alter tabla empleados Agregar restricción birth_uniq único (emp_id, birth_year, birth_month, birth_dayofmonth); AVISO: ALTER TABLA / ADD UNICE CREATARÁ ÍNDICE INFLÍTO "BAUCH_UNIQ" PARA TABLA "Empleados" 

Así que obtuvimos un índice para los cuatro campos, veamos cómo funciona nuestra consulta:

foobardb =# explicar analizar select emp_id, first_name, last_name de los empleados donde birth_month = 3 y birth_dayofmonth = 20; Plan de consulta ------------------------------------------------ -------------------------------------------------- ---------- SEQ Scan en los empleados (costo = 0.00 ... 11667.19 filas = 1 ancho = 22) (tiempo real = 103.131 ... 151.084 filas = 1 bucles = 1) Filtro: ((birth_month = 3 :: numérico) y (birth_dayofmonth = 20 :: numérico)) filas eliminadas por filtro: 500001 tiempo de ejecución total: 151.103 ms (4 filas) 

Eso es idéntico al último, y podemos ver que el plan es el mismo, el índice no se usa. Creemos otro índice mediante una restricción única en emp_id, mes de nacimiento y Birth_dayOfMonth Solo (después de todo, no consultamos por Año de nacimiento en Hbapp):

foobardb =# alter tabla empleados Agregar restricción birth_uniq_m_dom único (emp_id, birth_month, birth_dayofmonth); AVISO: Alter Tabla / Add Unique creará un índice implícito "Birth_uniq_m_dom" para la tabla "empleados" 

Veamos el resultado de nuestro ajuste:

foobardb =# explicar analizar select emp_id, first_name, last_name de los empleados donde birth_month = 3 y birth_dayofmonth = 20; Plan de consulta ------------------------------------------------ -------------------------------------------------- --------- SEQ Scan en los empleados (costo = 0.00 ... 11667.19 filas = 1 ancho = 22) (tiempo real = 97.187 ... 139.858 filas = 1 bucles = 1) Filtro: ((birth_month = 3 :: numérico) y (birth_dayofmonth = 20 :: numérico)) filas eliminadas por filtro: 500001 tiempo de ejecución total: 139.879 ms (4 filas) 

Nada. La diferencia anterior proviene del uso de cachés, pero el plan es el mismo. Vamos más lejos. A continuación, crearemos otro índice en emp_id y mes de nacimiento:

foobardb =# alter tabla empleados Agregar restricción birth_uniq_m unique (emp_id, birth_month); AVISO: ALTER TABLE / ADD UNICE CREARÁ ÍNDICE IMPLÉMENTO "BAICIA_UNIQ_M" PARA TABLA "Empleados" 

Y vuelva a ejecutar la consulta:

foobardb =# explicar analizar select emp_id, first_name, last_name de los empleados donde birth_month = 3 y birth_dayofmonth = 20; Plan de consulta ------------------------------------------------ -------------------------------------------------- -------------------------.00 ... 11464.19 filas = 1 ancho = 22) (tiempo real = 0.089 ... 95.605 filas = 1 bucles = 1) índice cond: (birth_month = 3 :: numérico) Filtro: (birth_dayofmonth = 20 :: numérico) Tiempo de ejecución total: 95.630 ms (4 filas) 

Éxito! La consulta es un 40% más rápida, y podemos ver que el plan cambió: la base de datos ya no escanea la tabla completa, sino que usa el índice en mes de nacimiento y emp_id. Creamos todas las mezclas de los cuatro campos, solo queda uno. Vale la pena intentarlo:

foobardb =# alter tabla empleados Agregar restricción birth_uniq_dom único (emp_id, birth_dayofmonth); AVISO: ALTER TABLE / ADD UNICE creará un índice implícito "Birth_uniq_dom" para la tabla "empleados" 

El último índice se crea en los campos emp_id y Birth_dayOfMonth. Y el resultado es:

foobardb =# explicar analizar select emp_id, first_name, last_name de los empleados donde birth_month = 3 y birth_dayofmonth = 20; Plan de consulta ------------------------------------------------ -------------------------------------------------- ------------------------------ Escaneo índice usando Birth_uniq_dom en empleados (costo = 0.00 ... 11464.19 filas = 1 ancho = 22) (tiempo real = 0.025 ... 72.394 filas = 1 bucles = 1) índice cond: (birth_dayofmonth = 20 :: numérico) Filtro: (birth_month = 3 :: numérico) Tiempo de ejecución total: 72.421 ms (4 filas) 

Ahora nuestra consulta es aproximadamente un 49% más rápida, utilizando el último (y solo el último) índice creado. Nuestra tabla e índices relacionados miran de la siguiente manera:

foobardb =# \ d+ tabla de empleados "público.empleados "columna | tipo | modificadores | almacenamiento | estadísticas objetivo | descripción ------------------+---------+------- ----------------------------------------------+--- -------+--------------+--------- regClass) | Main | | First_Name | Text | No Null | Extended | | Last_Name | Text | no nulo | extendido | | birth_year | numérico | no nulo | main | | birth_month | numérico | no nulo | main | | birth_dayofmonth | numérico | no nulo | Main | | Índices: "Empleados_pkey" Clave primaria, btree (emp_id) "birth_uniq" restricción única, btree (emp_id, birth_year, birth_month, birth_dayofmonth) "birth_uniq_dom" restricción única, btree (emp_id, birth_dayofonth) "birth_uniq_m" sind Restricción, btree (emp_id, birth_month) "birth_uniq_m_dom" restricción única, btree (emp_id, birth_month, birth_dayofmonth) tiene OID: no 

No necesitamos los índices intermedios creados, el plan claramente establece que no los usará, por lo que los eliminamos:

foobardb =# alter tabla empleados caída restricción birth_uniq; Alter tabla foobardb =# alter tabla empleados caída restricción birth_uniq_m; Alter tabla foobardb =# alter tabla empleados caída restricción birth_uniq_m_dom; Alterar la mesa 

Al final, nuestra tabla gana solo un índice adicional, que es de bajo costo para la doble velocidad de HBApp:

foobardb =# \ d+ tabla de empleados "público.empleados "columna | tipo | modificadores | almacenamiento | estadísticas objetivo | descripción ------------------+---------+------- ----------------------------------------------+--- -------+--------------+--------- regClass) | Main | | First_Name | Text | No Null | Extended | | Last_Name | Text | no nulo | extendido | | birth_year | numérico | no nulo | main | | birth_month | numérico | no nulo | main | | birth_dayofmonth | numérico | no nulo | Main | | Índices: "Empleados_pkey" Clave primaria, btree (emp_id) "birth_uniq_dom" restricción única, btree (emp_id, birth_dayofmonth) tiene oids: no 

Y podemos presentar nuestro ajuste a la producción agregando el índice que hemos visto que es más útil:

ALTER TABLA Los empleados Agregar restricción Birth_Uniq_dom único (emp_id, bucle_dayofmonth);

Conclusión

No hace falta decir que este es solo un ejemplo ficticio. Es poco probable que almacene la fecha de nacimiento de su empleado en tres campos separados mientras podría usar un campo de tipo de fecha, lo que permite operaciones relacionadas con la fecha de manera mucho más fácil que comparar valores de mes y día como enteros. También tenga en cuenta que las pocas consultas explicadas anteriores no se ajustan como pruebas excesivas. En un escenario del mundo real, debe probar el impacto del objeto de la nueva base de datos en cualquier otra aplicación que use la base de datos, así como los componentes de su sistema que interactúan con HBAPP.

Por ejemplo, en este caso, si podemos procesar la tabla para los destinatarios en el 50% del tiempo de respuesta original, prácticamente podemos producir el 200% de los correos electrónicos en el otro extremo de la aplicación (digamos, el HBAPP se ejecuta en secuencia para todas las 500 compañías subsidiarias de Nice Company), que pueden resultar en una carga máxima en otro lugar; tal vez los servidores de correo reciban muchos correos electrónicos de "feliz cumpleaños" para transmitir justo antes de que puedan enviar los informes diarios a la administración, lo que resulta en retrasos de entrega. También está un poco lejos de la realidad que alguien que ajusta una base de datos creará índices con prueba y error ciegos, o al menos, esperemos que esto sea así en una empresa que emplea que muchas personas.

Sin embargo, tenga en cuenta que obtuvimos un aumento de rendimiento del 50% en la consulta solo utilizando el PostgreSQL incorporado explicar característica para identificar un solo índice que podría ser útil en la situación dada. También demostramos que cualquier base de datos relacional no es mejor que una búsqueda de texto clara si no la usamos, ya que están destinadas a ser utilizadas.

Tutoriales de Linux relacionados:

• Cosas para instalar en Ubuntu 20.04
• Ubuntu 20.04 Instalación de PostgreSQL
• Ubuntu 22.04 Instalación de PostgreSQL
• Una introducción a la automatización, herramientas y técnicas de Linux
• Optimización de rendimiento de Linux: herramientas y técnicas
• Cosas que hacer después de instalar Ubuntu 20.04 fossa focal Linux
• Descarga de Linux
• Archivos de configuración de Linux: los 30 principales más importantes
• Cómo persistir los datos a PostgreSQL en Java
• Cosas para instalar en Ubuntu 22.04