Ajuste del rendimiento de PostgreSQL para una ejecución de consultas más rápida
Objetivo
Nuestro objetivo es hacer que la ejecución de una consulta ficticia se ejecute más rápido en la base de datos PostgreSQL utilizando solo las herramientas integradas disponibles. en la base de datos.
Versiones de software y sistema operativo
Sistema operativo: Red Hat Enterprise Linux 7.5
Software: servidor PostgreSQL 9.2
Requisitos
Instalación base del servidor PostgreSQL en funcionamiento. Acceso a la herramienta de línea de comando psql y propiedad de la base de datos de ejemplo.
Convenciones
#: requiere que los comandos de Linux determinados se ejecuten con privilegios de root, ya sea directamente como usuario root o mediante el uso del comando
sudo-
$ – comandos de Linux dados para ser ejecutados como un usuario normal sin privilegios
Introducción
PostgreSQL es una base de datos confiable de código abierto disponible en los repositorios de muchas distribuciones modernas. La facilidad de uso, la capacidad de utilizar extensiones y la estabilidad que proporciona aumentan su popularidad. Si bien brindan la funcionalidad básica, como responder consultas SQL, almacenar datos insertados de manera consistente, manejar transacciones, etc., las soluciones de bases de datos más maduras brindan herramientas y conocimientos sobre cómo ajuste la base de datos, identifique posibles cuellos de botella y sea capaz de resolver los problemas de rendimiento que ocurrirán a medida que crezca el sistema impulsado por la solución dada.
PostgreSQL no es una excepción, y en esto En esta guía usaremos la herramienta integrada explain para hacer que una consulta de ejecución lenta se complete más rápido. Está lejos de ser una base de datos del mundo real, pero uno puede darse cuenta del uso de las herramientas integradas. 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 mucho más antiguas de bases de datos y sistemas operativos.
El problema a resolver
Considere esta tabla simple (los nombres de las columnas se explican por sí solos):
foobardb=# \d+ employees
Table "public.employees"
Column | Type | Modifiers | Storage | Stats target | Description
------------------+---------+-----------------------------------------------------+----------+--------------+-------------
emp_id | numeric | not null default nextval('employees_seq'::regclass) | main | |
first_name | text | not null | extended | |
last_name | text | not null | extended | |
birth_year | numeric | not null | main | |
birth_month | numeric | not null | main | |
birth_dayofmonth | numeric | not null | main | |
Indexes:
"employees_pkey" PRIMARY KEY, btree (emp_id)
Has OIDs: no
Con registros como:
foobardb=# select * from employees limit 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 Nice Company 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 los destinatarios del día (antes del horario laboral, no queremos eliminar 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 from employees where birth_month = 3 and birth_dayofmonth = 20;
emp_id | first_name | last_name
--------+------------+-----------
1 | Emily | James
Todo funciona bien, los usuarios reciben su correo. Muchas otras aplicaciones utilizan la base de datos y la tabla de empleados que contiene, como contabilidad y BI. La empresa Nice crece y con ella también crece la plantilla de empleados. Con el tiempo, la aplicación se ejecuta durante demasiado tiempo y la ejecución se superpone con el inicio de las horas de trabajo, lo que da como resultado un tiempo de respuesta lento de la base de datos 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 no se implementará y, con ello, habrá menos amabilidad en Nice Company.
Para este ejemplo, no utilizaremos ninguna herramienta avanzada para resolver el problema, solo una proporcionada por la instalación básica. Veamos cómo el planificador de la base de datos ejecuta la consulta con explain.
No estamos realizando pruebas en producción; Creamos una base de datos para realizar pruebas, creamos la tabla e insertamos en ella dos empleados mencionados anteriormente. Usamos los mismos valores para la consulta todo el tiempo en este tutorial, por lo tanto, en cualquier ejecución, solo un registro coincidirá con la consulta: Emily James. Luego ejecutamos la consulta con el explicar analizar anterior para ver cómo se ejecuta con un mínimo de datos en la tabla:
foobardb=# explain analyze select emp_id, first_name, last_name from employees where birth_month = 3 and birth_dayofmonth = 20;
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Seq Scan on employees (cost=0.00..15.40 rows=1 width=96) (actual time=0.023..0.025 rows=1 loops=1)
Filter: ((birth_month = 3::numeric) AND (birth_dayofmonth = 20::numeric))
Rows Removed by Filter: 1
Total runtime: 0.076 ms
(4 rows)
Eso es muy rápido. Posiblemente tan rápido como lo fue cuando la empresa implementó HBapp por primera vez. Imitemos el estado de producción actual foobardb cargando tantos empleados (falsos) en la base de datos como tenemos en producción (nota: necesitaremos el mismo tamaño de almacenamiento en la base de datos de prueba que en producción). ).
Simplemente usaremos bash para completar la base de datos de prueba (suponiendo que tengamos 500.000 empleados en producción):
$ for j in {1..500000} ; do echo "insert into employees (first_name, last_name, birth_year, birth_month, birth_dayofmonth) values ('user$j','Test',1900,01,01);"; done | psql -d foobardb
Ahora tenemos 500002 empleados:
foobardb=# select count(*) from employees;
count
--------
500002
(1 row)
Ejecutemos la consulta de explicación nuevamente:
foobardb=# explain analyze select emp_id, first_name, last_name from employees where birth_month = 3 and birth_dayofmonth = 20;
QUERY PLAN
----------------------------------------------------------------------------------------------------------
Seq Scan on employees (cost=0.00..11667.63 rows=1 width=22) (actual time=0.012..150.998 rows=1 loops=1)
Filter: ((birth_month = 3::numeric) AND (birth_dayofmonth = 20::numeric))
Rows Removed by Filter: 500001
Total runtime: 151.059 ms
Todavía tenemos solo una coincidencia, 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 el conjunto. tabla, mientras que solo necesitamos un registro, como lo haría un grep en bash. De hecho, puede ser más lento que grep. Si exportamos la tabla a un archivo csv llamado /tmp/exp500k.csv:
foobardb=# copy employees to '/tmp/exp500k.csv' delimiter ',' CSV HEADER;
COPY 500002
Y buscamos 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
user 0m0.018s
sys 0m0.010s
Esto, aparte del almacenamiento en caché, se considera cada vez más lento a medida que crece la tabla.
La solución es la indexación de causas. Ningún empleado puede tener más de una fecha de nacimiento, que consta exactamente de un birth_year, birth_month y birth_dayofmonth, por lo que estos tres campos proporcionan un valor único. para ese usuario en particular. Y un usuario se identifica 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 table employees add constraint birth_uniq unique (emp_id, birth_year, birth_month, birth_dayofmonth);
NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "birth_uniq" for table "employees"
Entonces obtuvimos un índice para los cuatro campos, veamos cómo se ejecuta nuestra consulta:
foobardb=# explain analyze select emp_id, first_name, last_name from employees where birth_month = 3 and birth_dayofmonth = 20;
QUERY PLAN
------------------------------------------------------------------------------------------------------------
Seq Scan on employees (cost=0.00..11667.19 rows=1 width=22) (actual time=103.131..151.084 rows=1 loops=1)
Filter: ((birth_month = 3::numeric) AND (birth_dayofmonth = 20::numeric))
Rows Removed by Filter: 500001
Total runtime: 151.103 ms
(4 rows)
Es idéntico al último y podemos ver que el plan es el mismo, no se utiliza el índice. Creemos otro índice mediante una restricción única en emp_id, birth_month y birth_dayofmonth únicamente (después de todo, no consultamos birth_year) en HBapp):
foobardb=# alter table employees add constraint birth_uniq_m_dom unique (emp_id, birth_month, birth_dayofmonth);
NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "birth_uniq_m_dom" for table "employees"
Veamos el resultado de nuestra afinación:
foobardb=# explain analyze select emp_id, first_name, last_name from employees where birth_month = 3 and birth_dayofmonth = 20;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------
Seq Scan on employees (cost=0.00..11667.19 rows=1 width=22) (actual time=97.187..139.858 rows=1 loops=1)
Filter: ((birth_month = 3::numeric) AND (birth_dayofmonth = 20::numeric))
Rows Removed by Filter: 500001
Total runtime: 139.879 ms
(4 rows)
Nada. La diferencia anterior proviene del uso de cachés, pero el plan es el mismo. Vayamos más allá. A continuación crearemos otro índice en emp_id y birth_month:
foobardb=# alter table employees add constraint birth_uniq_m unique (emp_id, birth_month);
NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "birth_uniq_m" for table "employees"
Y ejecute la consulta nuevamente:
foobardb=# explain analyze select emp_id, first_name, last_name from employees where birth_month = 3 and birth_dayofmonth = 20;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Index Scan using birth_uniq_m on employees (cost=0.00..11464.19 rows=1 width=22) (actual time=0.089..95.605 rows=1 loops=1)
Index Cond: (birth_month = 3::numeric)
Filter: (birth_dayofmonth = 20::numeric)
Total runtime: 95.630 ms
(4 rows)
¡Éxito! La consulta es un 40% más rápida y podemos ver que el plan cambió: la base de datos ya no escanea toda la tabla, sino que usa el índice en birth_month y emp_id. Creamos todas las mezclas de los cuatro campos, solo queda uno. Vale la pena intentarlo:
foobardb=# alter table employees add constraint birth_uniq_dom unique (emp_id, birth_dayofmonth);
NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "birth_uniq_dom" for table "employees"
El último índice se crea en los campos emp_id y birth_dayofmonth. Y el resultado es:
foobardb=# explain analyze select emp_id, first_name, last_name from employees where birth_month = 3 and birth_dayofmonth = 20;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Index Scan using birth_uniq_dom on employees (cost=0.00..11464.19 rows=1 width=22) (actual time=0.025..72.394 rows=1 loops=1)
Index Cond: (birth_dayofmonth = 20::numeric)
Filter: (birth_month = 3::numeric)
Total runtime: 72.421 ms
(4 rows)
Ahora nuestra consulta es aproximadamente un 49% más rápida, utilizando el último (y solo el último) índice creado. Nuestra tabla y los índices relacionados tienen el siguiente aspecto:
foobardb=# \d+ employees
Table "public.employees"
Column | Type | Modifiers | Storage | Stats target | Description
------------------+---------+-----------------------------------------------------+----------+--------------+-------------
emp_id | numeric | not null default nextval('employees_seq'::regclass) | main | |
first_name | text | not null | extended | |
last_name | text | not null | extended | |
birth_year | numeric | not null | main | |
birth_month | numeric | not null | main | |
birth_dayofmonth | numeric | not null | main | |
Indexes:
"employees_pkey" PRIMARY KEY, btree (emp_id)
"birth_uniq" UNIQUE CONSTRAINT, btree (emp_id, birth_year, birth_month, birth_dayofmonth)
"birth_uniq_dom" UNIQUE CONSTRAINT, btree (emp_id, birth_dayofmonth)
"birth_uniq_m" UNIQUE CONSTRAINT, btree (emp_id, birth_month)
"birth_uniq_m_dom" UNIQUE CONSTRAINT, btree (emp_id, birth_month, birth_dayofmonth)
Has OIDs: no
No necesitamos que se creen los índices intermedios, el plan establece claramente que no los usará, por lo que los descartamos:
foobardb=# alter table employees drop constraint birth_uniq;
ALTER TABLE
foobardb=# alter table employees drop constraint birth_uniq_m;
ALTER TABLE
foobardb=# alter table employees drop constraint birth_uniq_m_dom;
ALTER TABLE
Al final, nuestra tabla gana sólo un índice adicional, que es de bajo costo para casi el doble de velocidad de HBapp:
foobardb=# \d+ employees
Table "public.employees"
Column | Type | Modifiers | Storage | Stats target | Description
------------------+---------+-----------------------------------------------------+----------+--------------+-------------
emp_id | numeric | not null default nextval('employees_seq'::regclass) | main | |
first_name | text | not null | extended | |
last_name | text | not null | extended | |
birth_year | numeric | not null | main | |
birth_month | numeric | not null | main | |
birth_dayofmonth | numeric | not null | main | |
Indexes:
"employees_pkey" PRIMARY KEY, btree (emp_id)
"birth_uniq_dom" UNIQUE CONSTRAINT, btree (emp_id, birth_dayofmonth)
Has OIDs: no
Y podemos introducir nuestro ajuste en producción agregando el índice que hemos considerado más útil:
alter table employees add constraint birth_uniq_dom unique (emp_id, birth_dayofmonth);Conclusión
No hace falta decir que éste es sólo un ejemplo ficticio. Es poco probable que almacene la fecha de nacimiento de su empleado en tres campos separados, mientras que podría usar un campo de tipo de fecha, lo que permite operaciones relacionadas con la fecha de una manera mucho más sencilla que comparar valores de mes y día como números enteros. También tenga en cuenta que las pocas consultas explicativas anteriores no se consideran pruebas excesivas. En un escenario del mundo real, necesita probar el impacto del nuevo objeto de base de datos en cualquier otra aplicación que utilice la base de datos, así como en los componentes de su sistema que interactúan con HBapp.
Por ejemplo, en este caso, si podemos procesar la tabla de 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 que HBapp se ejecuta en secuencia durante (todas las 500 filiales de Nice Company), lo que puede provocar un pico de carga en otro lugar; tal vez los servidores de correo reciban muchos correos electrónicos de "Feliz cumpleaños" para transmitir justo antes de enviar los informes diarios a la gerencia, lo que provocará retrasos. de entrega. También está un poco lejos de la realidad que alguien que ajuste una base de datos cree índices mediante prueba y error ciegos, o al menos, esperemos que así sea en una empresa que emplea a tanta gente.
Sin embargo, tenga en cuenta que obtuvimos un aumento del 50% en el rendimiento de la consulta utilizando únicamente la función explain integrada de PostgreSQL para identificar un índice único 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 claro si no la usamos como debe usarse.