Il node di chiamata postgres funziona con le tabelle temporanee causando “perdita di memoria”

Ho un programma node.js che chiama una funzione Postgres (istanza micro di Amazon RDS), get_jobs all’interno di una transazione, 18 volte al secondo utilizzando il pacchetto node-postgres di brianc.

Il codice nodo è solo una versione avanzata dell’esempio del pool di client di base di brianc , più o meno come …

 var pg = require('pg'); var conString = "postgres://username:[email protected]/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 

Quindi Postgres sta ottenendo:

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

… ripetuto ogni 55ms.

get_jobs è scritto con tabelle temporanee, qualcosa del genere

 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; 

Ho usato il modello di tabella temporanea perché so che i jobs saranno sempre un piccolo estratto di righe da really_big_table_1 , nella speranza che questo really_big_table_1 migliore di una singola query con più join e più condizioni. (L’ho usato con grande efficacia con SQL Server e non mi fido di alcun ottimizzatore di query ora, ma per favore dimmi se questo è l’approccio sbagliato per Postgres!)

La query viene eseguita in 8 ms su piccole tabelle (come misurato dal nodo), tempo sufficiente per completare un “poll” di lavoro prima dell’avvio del successivo.

Problema: dopo circa 3 ore di polling a questa velocità, il server Postgres esaurisce la memoria e si blocca.

Quello che ho provato già …

  • Se riscrivo la funzione senza tabelle temporanee, Postgres non esaurisce la memoria, ma io uso molto il pattern della tabella temporanea, quindi questa non è una soluzione.

  • Se interrompo il programma del nodo (che uccide le 10 connessioni che usa per eseguire le interrogazioni) la memoria si libera. Il semplice fatto che il nodo attenda un minuto tra le sessioni di polling non ha lo stesso effetto, quindi ci sono ovviamente risorse che il backend Postgres associato alla connessione in pool sta mantenendo.

  • Se VACUUM un VACUUM mentre il polling è attivo, non ha alcun effetto sul consumo di memoria e il server continua a morire.

  • La riduzione della frequenza di polling modifica solo la quantità di tempo prima che il server muoia.

  • Aggiungere DISCARD ALL; dopo ogni COMMIT; non ha alcun effetto

  • Chiamare esplicitamente i DROP TABLE jobs; DROP TABLE jobs_extra; DROP TABLE jobs; DROP TABLE jobs_extra; dopo RETURN query () invece di ON COMMIT DROP s su CREATE TABLE s. Il server si blocca ancora.

  • Per suggerimento di CFrei, aggiunto pg.defaults.poolSize = 0 al codice nodo nel tentativo di disabilitare il pooling. Il server si è ancora arrestato in modo anomalo, ma ha impiegato molto più tempo e lo scambio è andato molto più alto (secondo picco) rispetto a tutti i test precedenti che sembravano il primo picco sotto. Ho scoperto più tardi che pg.defaults.poolSize = 0 non può disabilitare il pool come previsto .

Scambia l'utilizzo della memoria sul server Postgres

  • Sulla base di questo : “Autovacuum non può accedere alle tabelle temporanee, pertanto è necessario eseguire le operazioni appropriate di vuoto e analisi tramite i comandi SQL di sessione.”, Ho provato a eseguire un VACUUM dal server del nodo (come alcuni tentativi di rendere VACUUM un comando “in sessione”). Non sono riuscito a far funzionare questo test. Ho molti oggetti nel mio database e VACUUM , che funziona su tutti gli oggetti, impiegava troppo tempo per eseguire ogni iterazione del lavoro. Limitare VACUUM solo alle tabelle temporanee era imansible: (a) non è ansible eseguire VACUUM in una transazione e (b) al di fuori della transazione le tabelle temporanee non esistono. : P EDIT: Più tardi sul forum IRC di Postgres, un utente utile ha spiegato che VACUUM non è rilevante per le tabelle temporanee, ma può essere utile per ripulire le righe create e cancellate da pg_attributes che le TABELLE TEMP causano. In ogni caso, VACUUMing “in sessione” non era la risposta.

  • DROP TABLE ... IF EXISTS prima di CREATE TABLE , invece di ON COMMIT DROP . Il server muore ancora.

  • CREATE TEMP TABLE (...) e insert into ... (select...) invece di CREATE TEMP TABLE ... AS , invece di ON COMMIT DROP . Il server muore.

Quindi ON COMMIT DROP non rilascia tutte le risorse associate? Cos’altro potrebbe contenere la memoria? Come lo rilascio?

L’ho usato con grande efficacia con SQL Server e non mi fido di alcun ottimizzatore di query ora

Quindi non li usano. È ancora ansible eseguire le query direttamente, come mostrato di seguito.

ma per favore dimmi se questo è l’approccio sbagliato per Postgres!

Non è un approccio completamente sbagliato, è solo molto imbarazzante, poiché stai cercando di creare qualcosa che sia stato implementato da altri per un uso molto più facile. Di conseguenza, stai facendo molti errori che possono portare a molti problemi, tra cui perdite di memoria.

Confronta con la semplicità dell’esatto stesso esempio che usa pg-promise :

 var pgp = require('pg-promise')(); var conString = "postgres://username:[email protected]/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 

Diventa ancora più semplice quando si utilizza la syntax ES6:

 var pgp = require('pg-promise')(); var conString = "postgres://username:[email protected]/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 

L’unica cosa che non ho compreso nel tuo esempio: l’uso di una transazione per eseguire una singola SELECT . Questo non è ciò che le transazioni sono generalmente per, in quanto non stai cambiando alcun dato. Presumo che stavi cercando di ridurre un vero pezzo di codice che hai modificato anche alcuni dati.

Nel caso in cui non sia necessaria una transazione, il codice può essere ulteriormente ridotto a:

 var pgp = require('pg-promise')(); var conString = "postgres://username:[email protected]/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 

AGGIORNARE

Sarebbe un approccio pericoloso, tuttavia, non controllare la fine della richiesta precedente, che potrebbe anche creare problemi di memoria / connessione.

Un approccio sicuro dovrebbe essere:

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

Usa CTE per creare set di risultati parziali invece di tabelle temporanee.

 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; 

Il pianificatore valuterà ogni blocco in sequenza come volevo ottenere con le tabelle temporanee.

So che questo non risolve direttamente il problema della perdita di memoria (sono abbastanza sicuro che c'è qualcosa di sbagliato nell'implementazione di Postgres, almeno nel modo in cui si manifestano nella configurazione RDS).

Tuttavia, la query funziona, la query è pianificata nel modo in cui intendevo e l'utilizzo della memoria è stabile ora dopo 3 giorni di esecuzione del lavoro e il mio server non si blocca.

Non ho cambiato il codice del nodo.