Tuning applicativo su AWS RDS: query PostgreSQL sotto i 10 ms e -50% costi cloud per l'IoT nel Manufacturing

Il cliente è un player internazionale di primo piano nel settore Manufacturing, specializzato nello sviluppo e produzione di soluzioni avanzate per il condizionamento, la refrigerazione e il riscaldamento. L'azienda si affida a un'imponente infrastruttura cloud su AWS per gestire, in tempo reale, i flussi di dati provenienti da migliaia di dispositivi IoT distribuiti a livello globale, con il proprio database core ospitato su Amazon RDS per PostgreSQL.
Con la crescita del traffico applicativo, alcune classi di query generate dinamicamente dall'applicativo hanno iniziato a mostrare tempi di risposta altalenanti e picchi di carico.

Sul cloud, tempi di risposta più lunghi e un maggior consumo di CPU e RAM si traducono direttamente in una spesa più alta per garantire agli utenti le stesse performance. Il tuning non si limita a riscrivere le query o ad aggiungere indici: richiede anche una conoscenza diretta del motore di database, in questo caso PostgreSQL (ma ragionamenti analoghi valgono per Oracle).
Le query generate dall'applicativo, infatti, erano soggette a:

• Ri-pianificazione continua: ogni esecuzione di una query, pur avendo la stessa struttura e differendo solo per i parametri, veniva analizzata e ripianificata da zero da PostgreSQL, con un costo computazionale ricorrente e non necessario.
• Tempi di risposta instabili: la durata media delle query si attestava intorno ai 43 ms, con picchi marcati concentrati in specifiche finestre della giornata, in corrispondenza dei momenti di traffico IoT più intenso.

Anziché intervenire sulla logica delle query, il team Miriade ha agito sul punto in cui l'applicativo invia le istruzioni SQL al database, introducendo uno statement PREPARE esplicito immediatamente prima dell'esecuzione delle query parametriche.
In pratica, la query viene "preparata" una sola volta per sessione (PREPARE … AS …) e la modalità di caching del piano viene forzata sul piano generico (plan_cache_mode = force_generic_plan). Da quel momento, ogni successiva EXECUTE della stessa query, anche con parametri diversi, riutilizza il piano di esecuzione già calcolato invece di essere analizzata e ripianificata da zero.

Le misurazioni confermano l'effetto della modifica: su una query di lettura dati dispositivo (get_variable_details), la prima esecuzione dopo la PREPARE ha richiesto circa 25 ms, mentre tutte le esecuzioni successive, con parametri differenti, sono scese a circa 1,1–1,4 ms.
Esteso all'intero carico applicativo, l'intervento ha fatto crollare la durata media delle query da 43 ms a meno di 10 ms [moltiplicatore da verificare], e le dashboard di monitoring, che prima mostravano picchi di carico marcati e ricorrenti, hanno registrato un carico praticamente azzerato nelle stesse finestre orarie.

Il margine di risorse recuperato ha permesso di ridimensionare l'istanza di produzione da un db.m5.8xlarge [prezzo €/ora e €/mese da verificare su fattura] a un db.m5.4xlarge [idem], dimezzando il costo di calcolo.

Per garantire un passaggio di versione indolore e ingegnerizzare test di carico validi anche per il futuro, Miriade ha sfruttato i seguenti tool e metodologie.

• Amazon RDS per PostgreSQL - Aggiornamento critico (dalla versione 10 alla 14) del database relazionale managed alla base dell'intera infrastruttura globale del cliente.
• Query plan caching applicativo (PREPARE / plan_cache_mode) - Tecnica di tuning lato applicativo che elimina la ri-pianificazione ripetuta delle query, senza richiedere modifiche alla logica SQL né nuovi indici.

Leggi anche: per un cliente del settore HVAC Miriade ha gestito anche l'upgrade di PostgreSQL dalla versione 10 alla 14 su AWS RDS, migrando 10 TB con meno di 30 minuti di downtime. Upgrade Zero-Impact su AWS RDS. Ottimizzazione PostgreSQL per IoT nel Manufacturing