{"id":36704,"date":"2019-10-31T22:13:15","date_gmt":"2019-10-31T19:13:15","guid":{"rendered":"https:\/\/prohoster.info\/blog\/optimizatsiya-zaprosov-bazy-dannyh-na-primere-b2b-servisa-dlya-stroitelej\/"},"modified":"2019-10-31T22:13:15","modified_gmt":"2019-10-31T19:13:15","slug":"optimizatsiya-zaprosov-bazy-dannyh-na-primere-b2b-servisa-dlya-stroitelej","status":"publish","type":"post","link":"https:\/\/prohoster.info\/es\/blog\/administrirovanie\/optimizatsiya-zaprosov-bazy-dannyh-na-primere-b2b-servisa-dlya-stroitelej","title":{"rendered":"Optimizaci\u00f3n de consultas de bases de datos en un servicio B2B para constructores","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

\u00bfC\u00f3mo multiplicar por diez la cantidad de consultas a la base de datos sin trasladarse a un servidor m\u00e1s potente y manteniendo la operatividad del sistema? Te contar\u00e9 c\u00f3mo luchamos contra la ca\u00edda del rendimiento de nuestra base de datos, c\u00f3mo optimizamos las consultas SQL para atender al mayor n\u00famero posible de usuarios y sin aumentar los costos de recursos computacionales.
\n<\/a><\/noindex>
\nEstoy desarrollando un servicio para la gesti\u00f3n de procesos empresariales en empresas de construcci\u00f3n. Con nosotros colaboran alrededor de 3 mil compa\u00f1\u00edas. M\u00e1s de 10 mil personas trabajan cada d\u00eda con nuestro sistema de 4 a 10 horas. Resuelve diversas tareas de planificaci\u00f3n, notificaci\u00f3n, alertas, validaci\u00f3n\u2026 Utilizamos PostgreSQL 9.6. En la base de datos tenemos alrededor de 300 tablas y cada d\u00eda recibe hasta 200 millones de consultas (10 mil diferentes). En promedio, tenemos entre 3 y 4 mil consultas por segundo, y en los momentos m\u00e1s \u00e1lgidos, m\u00e1s de 10 mil consultas por segundo. La mayor parte de las consultas son OLAP. Las adiciones, modificaciones y eliminaciones son considerablemente menos, es decir, la carga de OLTP es relativamente peque\u00f1a. Todas estas cifras las he presentado para que puedas evaluar la magnitud de nuestro proyecto y entender cu\u00e1n \u00fatil puede ser nuestra experiencia para ti.<\/p>\n

Cuadro primero. L\u00edrico<\/h3>\n

\nCuando comenzamos el desarrollo, no pensamos mucho en cu\u00e1nta carga recaer\u00eda sobre la base de datos y qu\u00e9 har\u00edamos si el servidor no pudiera soportarlo. Al dise\u00f1ar la base de datos, seguimos recomendaciones generales y tratamos de no dispararnos en el pie, pero m\u00e1s all\u00e1 de consejos generales como \u201cno uses el patr\u00f3n Entity Attribute Values<\/a><\/noindex> no profundizamos. Dise\u00f1amos bas\u00e1ndonos en principios de normalizaci\u00f3n, evitando la redundancia de datos y no nos preocupamos por acelerar ciertas consultas. Una vez que llegaron los primeros usuarios, nos enfrentamos al problema del rendimiento. Como suele suceder, est\u00e1bamos completamente despreparados para esto. Los primeros problemas resultaron ser simples. Generalmente, se resolv\u00edan a\u00f1adiendo un nuevo \u00edndice. Pero lleg\u00f3 un momento en que las soluciones simples dejaron de funcionar. Al darnos cuenta de que nos faltaba experiencia y que nos costaba cada vez m\u00e1s comprender la causa de los problemas, contratamos a especialistas que nos ayudaron a configurar correctamente el servidor, conectar el monitoreo y nos mostraron d\u00f3nde mirar para obtener. estad\u00edsticas<\/a><\/noindex>.<\/p>\n

Cuadro segundo. Estad\u00edstico<\/h3>\n

\nTenemos alrededor de 10,000 consultas diferentes que se ejecutan en nuestra base de datos diariamente. De estas 10,000, hay monstruos que se ejecutan 2-3 millones de veces con un tiempo promedio de ejecuci\u00f3n de 0.1-0.3 ms, y hay consultas con un tiempo promedio de ejecuci\u00f3n de 30 segundos, que se llaman 100 veces al d\u00eda.<\/p>\n

Optimizar las 10,000 consultas no era posible, as\u00ed que decidimos averiguar hacia d\u00f3nde dirigir nuestros esfuerzos para mejorar el rendimiento de la base de datos de manera efectiva. Despu\u00e9s de varias iteraciones, comenzamos a clasificar las consultas por tipo.<\/p>\n

Consultas TOP<\/h4>\n

\nEstas son las consultas m\u00e1s pesadas, que consumen m\u00e1s tiempo (tiempo total). Son consultas que o se llaman con mucha frecuencia o que tardan mucho en ejecutarse (las consultas lentas y frecuentes ya fueron optimizadas en las primeras iteraciones para mejorar la velocidad). En total, el servidor dedica m\u00e1s tiempo a ejecutarlas. Adem\u00e1s, es importante diferenciar las consultas top por el tiempo total de ejecuci\u00f3n y por el tiempo de I\/O. Los m\u00e9todos de optimizaci\u00f3n para estas consultas son un poco diferentes.<\/p>\n

La pr\u00e1ctica com\u00fan de todas las empresas es trabajar con las consultas TOP. Son pocas, y la optimizaci\u00f3n de una sola consulta puede liberar entre el 5% y el 10% de los recursos. Sin embargo, a medida que el proyecto 'madura', la optimizaci\u00f3n de las consultas TOP se convierte en una tarea cada vez m\u00e1s no trivial. Todos los m\u00e9todos simples ya se han probado, y el \u2018m\u00e1s pesado\u2019 de los pedidos consume \u2018solo\u2019 entre el 3% y el 5% de los recursos. Si las consultas TOP en conjunto ocupan menos del 30%-40% del tiempo, lo m\u00e1s probable es que ya haya hecho esfuerzos para que trabajen r\u00e1pido y ha llegado el momento de pasar a la optimizaci\u00f3n de las consultas del siguiente grupo.
\nQueda por responder cu\u00e1ntas consultas superiores incluir en este grupo. Generalmente tomo no menos de 10, pero no m\u00e1s de 20. Intento que el tiempo de la primera y la \u00faltima consulta en el grupo TOP no difiera m\u00e1s de 10 veces. Es decir, si el tiempo de ejecuci\u00f3n de las consultas cae dr\u00e1sticamente del primer lugar al d\u00e9cimo, elijo TOP-10; si la ca\u00edda es m\u00e1s gradual, aumento el tama\u00f1o del grupo a 15 o 20.
\n\"Optimizaci\u00f3n
\n<\/p>\n

Medianos (medium)<\/h4>\n

\nEstas son todas las consultas que vienen justo despu\u00e9s de las CONSULTAS TOP, excepto las \u00faltimas 5-10%. Generalmente, la oportunidad de aumentar significativamente el rendimiento del servidor radica en la optimizaci\u00f3n de estas consultas. Pueden representar hasta el 80%. Pero incluso si su cuota supera el 50%, es hora de mirarlas m\u00e1s de cerca.<\/p>\n

Cola (tail)<\/h4>\n

\nComo se mencion\u00f3, estas consultas se llevan a cabo al final y ocupan del 5 al 10% del tiempo. Se pueden olvidar, a menos que utilices herramientas autom\u00e1ticas de an\u00e1lisis de consultas, ya que su optimizaci\u00f3n tambi\u00e9n puede ser econ\u00f3mica.<\/p>\n

\u00bfC\u00f3mo evaluar cada grupo?<\/p>\n

Utilizo una consulta SQL que ayuda a hacer esta evaluaci\u00f3n para PostgreSQL (estoy seguro de que se puede escribir una consulta similar para muchos otros SGBD).<\/p>\n

Consulta SQL para evaluar el tama\u00f1o de los grupos TOP-MEDIUM-TAIL<\/b><\/p>\n

SELECT sum(time_top) AS sum_top, sum(time_medium) AS sum_medium, sum(time_tail) AS sum_tail\nFROM\n(\n  SELECT CASE WHEN rn  20 AND rn  800 THEN tt_percent ELSE 0 END AS time_tail\n  FROM (\n    SELECT total_time \/ (SELECT sum(total_time) FROM pg_stat_statements) * 100 AS tt_percent, query,\n    ROW_NUMBER () OVER (ORDER BY total_time DESC) AS rn\n    FROM pg_stat_statements\n    ORDER BY total_time DESC\n  ) AS t\n)\nAS ts\n<\/code><\/pre>\n
El resultado de la consulta son tres columnas, cada una de las cuales contiene el porcentaje de tiempo dedicado al procesamiento de las consultas de ese grupo. Dentro de la consulta hay dos n\u00fameros (en mi caso 20 y 800) que separan las consultas de un grupo de las de otro.<\/p>\n
As\u00ed es como se relacionan aproximadamente las proporciones de consultas en el momento del inicio de la optimizaci\u00f3n y ahora.<\/p>\n
\"Optimizaci\u00f3n
\n
\nEn el diagrama se puede ver que la proporci\u00f3n de consultas TOP ha disminuido dr\u00e1sticamente, mientras que han aumentado las de \u2018media\u2019.
\nAl principio, en las consultas TOP hab\u00eda errores evidentes. Con el tiempo, los problemas iniciales desaparecieron, la proporci\u00f3n de consultas TOP se redujo, y se requer\u00edan cada vez m\u00e1s esfuerzos para acelerar las consultas pesadas. <\/p>\n
Para obtener el texto de las consultas, utilizamos la siguiente consulta<\/b><\/p>\n
SELECT * FROM (\n  SELECT ROW_NUMBER () OVER (ORDER BY total_time DESC) AS rn, total_time \/ (SELECT sum(total_time) FROM pg_stat_statements) * 100 AS tt_percent, query\n  FROM pg_stat_statements\n  ORDER BY total_time DESC\n) AS T\nWHERE\nrn  20 AND rn  800  -- TAIL\n<\/code><\/pre>\n
Aqu\u00ed est\u00e1 la lista de las t\u00e9cnicas m\u00e1s utilizadas que nos ayudaron a acelerar las consultas TOP:<\/p>\n