Nodo que llama a la función postgres con tablas temporales que causan “pérdida de memoria”

Tengo un progtwig node.js que llama a la función Postgres (micro instancia de Amazon RDS), get_jobs dentro de una transacción, 18 veces por segundo utilizando el paquete node-postgres de brianc.

El código de nodo es solo una versión mejorada del ejemplo de agrupación de clientes básica de brianc , aproximadamente como …

 var pg = require('pg'); var conString = "postgres://username:password@server/database"; function getJobs(cb) { pg.connect(conString, function(err, client, done) { if (err) return console.error('error fetching client from pool', err); client.query("BEGIN;"); client.query('select * from get_jobs()', [], function(err, result) { client.query("COMMIT;"); done(); //call `done()` to release the client back to the pool if (err) console.error('error running query', err); cb(err, result); }); }); } function poll() { getJobs(function(jobs) { // process the jobs }); setTimeout(poll, 55); } poll(); // start polling 

Así que Postgres está recibiendo:

 2016-04-20 12:04:33 UTC:172.31.9.180(38446):XXX@XXX:[5778]:LOG: statement: BEGIN; 2016-04-20 12:04:33 UTC:172.31.9.180(38446):XXX@XXX:[5778]:LOG: execute : select * from get_jobs(); 2016-04-20 12:04:33 UTC:172.31.9.180(38446):XXX@XXX:[5778]:LOG: statement: COMMIT; 

… repite cada 55ms.

get_jobs está escrito con tablas temporales, algo como esto

 CREATE OR REPLACE FUNCTION get_jobs ( ) RETURNS TABLE ( ... ) AS $BODY$ DECLARE _nowstamp bigint; BEGIN -- take the current unix server time in ms _nowstamp := (select extract(epoch from now()) * 1000)::bigint; -- 1. get the jobs that are due CREATE TEMP TABLE jobs ON COMMIT DROP AS select ... from really_big_table_1 where job_time < _nowstamp; -- 2. get other stuff attached to those jobs CREATE TEMP TABLE jobs_extra ON COMMIT DROP AS select ... from really_big_table_2 r inner join jobs j on r.id = j.some_id ALTER TABLE jobs_extra ADD PRIMARY KEY (id); -- 3. return the final result with a join to a third big table RETURN query ( select je.id, ... from jobs_extra je left join really_big_table_3 r on je.id = r.id group by je.id ); END $BODY$ LANGUAGE plpgsql VOLATILE; 

He usado el patrón de la tabla temporal porque sé que los jobs siempre serán un pequeño extracto de filas de really_big_table_1 , con la esperanza de que esto se really_big_table_1 mejor que una sola consulta con múltiples uniones y múltiples condiciones. (Utilicé esto con gran eficacia con SQL Server y no confío en ningún optimizador de consultas ahora, ¡pero dígame si este es el enfoque incorrecto para Postgres!)

La consulta se ejecuta en 8 ms en tablas pequeñas (según se mide desde el nodo), tiempo suficiente para completar una “encuesta” de trabajo antes de que comience la siguiente.

Problema: después de aproximadamente 3 horas de sondeo a esta velocidad, el servidor de Postgres se queda sin memoria y se bloquea.

Lo que ya probé …

  • Si reescribo la función sin las tablas temporales, Postgres no se queda sin memoria, pero uso mucho el patrón de la tabla temporal, por lo que no es una solución.

  • Si detengo el progtwig del nodo (que destruye las 10 conexiones que usa para ejecutar las consultas), la memoria se libera. El simple hecho de hacer que el nodo espere un minuto entre las sesiones de sondeo no tiene el mismo efecto, por lo que obviamente hay recursos que mantiene el backend de Postgres asociado con la conexión agrupada.

  • Si ejecuto un VACUUM mientras se realiza el sondeo, no tiene efecto en el consumo de memoria y el servidor continúa su camino hacia la muerte.

  • La reducción de la frecuencia de sondeo solo cambia la cantidad de tiempo antes de que el servidor muera.

  • Añadiendo DISCARD ALL; después de cada COMMIT; no tiene efecto.

  • Llamar explícitamente a los DROP TABLE jobs; DROP TABLE jobs_extra; DROP TABLE jobs; DROP TABLE jobs_extra; después de RETURN query () lugar de ON COMMIT DROP s en CREATE TABLE s. El servidor sigue fallando.

  • Según la sugerencia de CFrei, se agregó pg.defaults.poolSize = 0 al código de nodo en un bash de deshabilitar la agrupación. El servidor aún se bloqueó, pero tomó mucho más tiempo y el intercambio fue mucho más alto (segundo pico) que todas las pruebas anteriores, que parecían ser el primer pico a continuación. Más tarde pg.defaults.poolSize = 0 que pg.defaults.poolSize = 0 no puede deshabilitar la agrupación como se esperaba .

Uso de memoria de intercambio en el servidor de Postgres

  • Sobre la base de esto : “No se puede acceder a las tablas temporales mediante autovacuum. Por lo tanto, las operaciones de análisis y vacío apropiadas se deben realizar mediante los comandos SQL de la sesión”. Intenté ejecutar un VACUUM desde el servidor del nodo (como un bash de hacer de VACUUM un “en sesión” comando). Realmente no pude hacer funcionar esta prueba. Tengo muchos objetos en mi base de datos y VACUUM , que opera en todos los objetos, tardaba demasiado en ejecutar cada iteración de trabajo. Restringir VACUUM solo a las tablas temporales era imposible: (a) no puede ejecutar VACUUM en una transacción y (b) fuera de la transacción, las tablas temporales no existen. : P EDIT: Más adelante en el foro de IRC de Postgres, un capítulo útil explicó que VACUUM no es relevante para las tablas temporales, pero puede ser útil para limpiar las filas creadas y eliminadas de pg_attributes que causan las TABLAS DE TEMP. En cualquier caso, VACUUMing “en sesión” no fue la respuesta.

  • DROP TABLE ... IF EXISTS antes de CREATE TABLE , en lugar de ON COMMIT DROP . El servidor todavía muere.

  • CREATE TEMP TABLE (...) e insert into ... (select...) lugar de CREATE TEMP TABLE ... AS , en lugar de ON COMMIT DROP . El servidor muere.

¿Entonces, ON COMMIT DROP no está liberando todos los recursos asociados? ¿Qué más podría estar guardando memoria? ¿Cómo lo libero?

Utilicé esto con gran eficacia con SQL Server y no confío en ningún optimizador de consultas ahora

Entonces no los uses. Todavía puede ejecutar consultas directamente, como se muestra a continuación.

¡Pero por favor dígame si este es el enfoque equivocado para Postgres!

No es un enfoque completamente incorrecto, es simplemente muy incómodo, ya que está intentando crear algo que otros han implementado para un uso mucho más fácil. Como resultado, está cometiendo muchos errores que pueden dar lugar a muchos problemas, incluidas las memory leaks.

Compare con la simplicidad del mismo ejemplo exacto que usa pg-promise :

 var pgp = require('pg-promise')(); var conString = "postgres://username:password@server/database"; var db = pgp(conString); function getJobs() { return db.tx(function (t) { return t.func('get_jobs'); }); } function poll() { getJobs() .then(function (jobs) { // process the jobs }) .catch(function (error) { // error }); setTimeout(poll, 55); } poll(); // start polling 

Se vuelve aún más simple cuando se utiliza la syntax ES6:

 var pgp = require('pg-promise')(); var conString = "postgres://username:password@server/database"; var db = pgp(conString); function poll() { db.tx(t=>t.func('get_jobs')) .then(jobs=> { // process the jobs }) .catch(error=> { // error }); setTimeout(poll, 55); } poll(); // start polling 

Lo único que no entendí bien en su ejemplo: el uso de una transacción para ejecutar un solo SELECT . Esto no es para lo que están generalmente las transacciones, ya que no está cambiando ningún dato. Supongo que estaba intentando reducir un trozo de código real que tenía y que también cambia algunos datos.

En caso de que no necesite una transacción, su código puede reducirse aún más a:

 var pgp = require('pg-promise')(); var conString = "postgres://username:password@server/database"; var db = pgp(conString); function poll() { db.func('get_jobs') .then(jobs=> { // process the jobs }) .catch(error=> { // error }); setTimeout(poll, 55); } poll(); // start polling 

ACTUALIZAR

Sería un enfoque peligroso, sin embargo, no controlar el final de la solicitud anterior, lo que también puede crear problemas de memoria / conexión.

Un enfoque seguro debe ser:

 function poll() { db.tx(t=>t.func('get_jobs')) .then(jobs=> { // process the jobs setTimeout(poll, 55); }) .catch(error=> { // error setTimeout(poll, 55); }); } 

Utilice CTE para crear conjuntos de resultados parciales en lugar de tablas temporales.

 CREATE OR REPLACE FUNCTION get_jobs ( ) RETURNS TABLE ( ... ) AS $BODY$ DECLARE _nowstamp bigint; BEGIN -- take the current unix server time in ms _nowstamp := (select extract(epoch from now()) * 1000)::bigint; RETURN query ( -- 1. get the jobs that are due WITH jobs AS ( select ... from really_big_table_1 where job_time < _nowstamp; -- 2. get other stuff attached to those jobs ), jobs_extra AS ( select ... from really_big_table_2 r inner join jobs j on r.id = j.some_id ) -- 3. return the final result with a join to a third big table select je.id, ... from jobs_extra je left join really_big_table_3 r on je.id = r.id group by je.id ); END $BODY$ LANGUAGE plpgsql VOLATILE; 

El planificador evaluará cada bloque en secuencia como quería lograr con las tablas temporales.

Sé que esto no resuelve directamente el problema de la pérdida de memoria (estoy bastante seguro de que hay algo mal con la implementación de Postgres, al menos la forma en que se manifiestan en la configuración de RDS).

Sin embargo, la consulta funciona, es una consulta planificada de la manera que pretendía y el uso de la memoria es estable ahora después de 3 días de ejecutar el trabajo y mi servidor no falla.

No cambié el código de nodo en absoluto.