¿Qué hacer cuando hay una aplicación de código cerrado que no accede a la base de datos de la manera más óptima? ¿Cómo ajustar las consultas sin cambiar la aplicación, y posiblemente la base de datos en sí?
Si no ha hecho tales preguntas, es un DBA muy exitoso y riguroso.
Bueno, si me lo piden, déjenme compartir el sufrimiento y la experiencia.
Necesita almacenar más datos o configurar una tarea
Puede desplazarse con seguridad por esta sección si el historial del problema no es interesante.
Inicialmente, teníamos un sistema propietario que analizaba sus datos de un formato cerrado en la base de datos PostgreSQL, desde donde leíamos, analizábamos y procesábamos estos datos.
Además, las herramientas de este sistema también usaron esta base para ciertas operaciones, por lo que abandonarla y crear una copia con su estructura parecía una idea inútil.
De forma predeterminada, el sistema elimina automáticamente los registros anteriores a una semana, por lo que no hubo problemas de rendimiento en el stand.
Sin embargo, necesitamos almacenar datos mucho más tiempo, siempre que haya suficiente espacio en el disco del servidor. Bueno, es muy recomendable no perder el acceso a estos datos y seguir utilizando las herramientas integradas del sistema, incluso para datos antiguos.
Por lo tanto, la decisión obvia fue realizar particiones y disparadores en las operaciones INSERT. El enfoque es bastante simple y efectivo. Los datos se insertan en las particiones necesarias, la eliminación de registros antiguos está deshabilitada, todo parece estar bien.
Hasta que pasaron un par de años y los datos no se acumularon bien.
Aquí, "de repente" resultó que las solicitudes realizadas por las herramientas del sistema utilizado no limitan la selección por fecha (o más bien, no lo limitan al campo según el cual se realiza la partición). Es decir Si estamos buscando algo, la búsqueda se realiza en todas las particiones. Las operaciones de ACTUALIZACIÓN también comenzaron a disminuir: en condiciones solo se usaba un ID-shnik.
Como resultado, la solicitud se ejecuta durante mucho tiempo, retira todas las demás solicitudes, la carga está creciendo rápidamente.
Por supuesto, lo primero que viene a la mente es contactar al desarrollador.
Sin embargo, en la mayoría de los casos, ya no está en la zona de acceso, o solicitará el costo de otro sistema para completar en varias líneas.
Por lo tanto, surgió la idea de que probablemente ya exista algún tipo de proxy que pueda ayudarnos.
Necesitamos un proxy
La búsqueda rápida en Google no encontró una respuesta clara a la pregunta de cómo reescribir una consulta entrante al lado de PostgreSQL o algún software de terceros.
Por lo tanto (bueno, solo por diversión también, por supuesto), se escribió un software bastante simple que acepta conexiones de clientes y las envía por proxy en PostgreSQL. Al mismo tiempo, se leen las consultas SQL entrantes y, si es necesario, se reemplazan.
Compartir un enlace a githubSi bien no hice ningún paquete binario, mis manos no llegaron. Pero el montaje es bastante simple. Todo está escrito en C ++ / Qt, porque Llevo mucho tiempo escribiendo sobre esto ...
La configuración es bastante simple:
Especifique qué interfaz y puerto escuchar:
listen_address=0.0.0.0 listen_port=5433
Obligamos al software negligente a conectarse a la dirección especificada en lugar de conectarse directamente al servidor PostgreSQL.
Anotamos dónde reenviar las conexiones (en este ejemplo, el proxy se encuentra en la misma máquina que el servidor PostgreSQL):
dst_address=127.0.0.1 dst_port=5432
Establecemos una expresión regular para capturar la solicitud deseada:
query = SELECT \* FROM tablename WHERE (.+)
Decimos que necesitamos reescribirlo:
action = rewrite
Decimos cómo reescribir:
rewrite = SELECT * FROM tablename WHERE (col3 >= '$(now-1M)') AND $(1)
En este ejemplo, agregamos un filtro a las condiciones de consulta por la columna con la fecha, lo que indica que solo estamos interesados en los registros del último mes.
Se podría escribir así:
rewrite = SELECT * FROM tablename WHERE (col3 >= now() - interval '1 month') AND $(1)
Pero la solicitud no será óptima debido a la presencia de la función now (); la búsqueda se seguirá realizando en todas las particiones. Para buscar solo en lo necesario, debe especificar un valor constante. Por lo tanto, nuestro proxy sustituye la marca de tiempo con un turno de un mes en lugar de la construcción $ (ahora-1M).
Resultado (del registro):
ORIGINAL query: SELECT * FROM tablename WHERE id=1; MODIFIED query (rule 1): SELECT * FROM tablename WHERE (col3 >= '2018-11-12 11:25:23.0+00') AND id=1;
Por lo tanto, es posible reemplazar, en principio, cualquier solicitud. Las respuestas del servidor no cambian y se transmiten al cliente tal cual. De esta manera, el retraso de transmisión se minimiza. Además, la aplicación generalmente espera una respuesta de cierto formato, por lo que no es deseable cambiar el conjunto de columnas en la solicitud y la respuesta.
También es posible imprimir fácilmente todas las solicitudes de interés en el registro:
query = .+ action = log
El repositorio tiene una configuración con ejemplos y una descripción más detallada.
Por cierto, es fácil determinar qué tan bien el desarrollador escribe correctamente para trabajar con la base de datos. Por ejemplo, si ve una solicitud tan frecuentemente ejecutada, es hora de que alguien fume manuales.
INSERT INTO tablename (col1, col2, col3) VALUES('value1', 1, '2018-12-31')
Debería ser así:
INSERT INTO tablename (col1, col2, col3) VALUES($1::varchar, $2::integer, $3::date)
Desafortunadamente, hasta ahora nuestro proxy no puede escribir de esta manera: / pero esto no es difícil de hacer. Quizás en el futuro sea posible reescribir la primera solicitud a la segunda.
Sí, el punto importante es que SSL aún no es compatible, por lo que todas las conexiones de los clientes a los servidores proxy se realizarán sin cifrado.
Estaré encantado de comentar y comentar.
Si hay un interés activo de los usuarios, quizás desarrolle más el proyecto.
Puede agregar trabajo con otras bases de datos.