Calcolatore di tuning della memoria

Dove finisce la memoria di un database, e il parametro che si moltiplica in silenzio

I database gestiti arrivano con parametri di memoria ricavati dalla dimensione dell'istanza, e quasi tutti sono valori predefiniti sensati che conviene lasciare stare. Uno no, perché non si comporta come gli altri: work_mem viene concesso per ogni nodo di ordinamento o hash, quindi il suo costo reale si moltiplica per il numero di connessioni e per il numero di quei nodi in ciascuna query. Questo strumento ricava i parametri standard dalla memoria dell'istanza e poi mostra quel prodotto in modo esplicito.

Come funziona

• Ricava shared_buffers, effective_cache_size e maintenance_work_mem dalla memoria dell'istanza, con i rapporti che usa RDS stesso.
• Mostra gli equivalenti MySQL — innodb_buffer_pool_size e i buffer per connessione — quando cambi motore.
• Moltiplica work_mem per il numero di connessioni e per gli ordinamenti per query, cioè la cifra che deve davvero starci.
• Avvisa quando l'allocazione di base più quel caso peggiore supera la memoria dell'istanza.

PostgreSQL, come RDS imposta i valori predefiniti
  shared_buffers        = {DBInstanceClassMemory/32768}  -> 25% della RAM
  effective_cache_size  ~ 75% della RAM (suggerimento al planner, non un'allocazione)
  maintenance_work_mem  ~ 5%, con tetto attorno a 2 GiB

MySQL
  innodb_buffer_pool_size = {DBInstanceClassMemory*3/4} -> 75% della RAM

quello che si moltiplica
  caso peggiore = work_mem x connessioni x ordinamenti_per_query

Esempio pratico

Un'istanza PostgreSQL da 32 GiB con i valori predefiniti, e cosa succede quando si alza un'impostazione.

1. shared_buffers = 25% di 32 GiB = 8 GiB
2. effective_cache_size = 75% = 24 GiB, ma non riserva nulla — dice solo al planner cosa il sistema sta probabilmente mettendo in cache
3. maintenance_work_mem = 5% = 1,6 GiB, sotto il tetto di 2 GiB
4. work_mem = 16 MiB, con 200 connessioni e 2 nodi di ordinamento per query
5. caso peggiore = 16 MiB x 200 x 2 = 6,25 GiB
6. 8 GiB + 6,25 GiB = 14,25 GiB su 32 — comodo
7. ora alza work_mem a 64 MiB per sistemare un report lento, e cresci a 1.000 connessioni con 4 ordinamenti
8. caso peggiore = 64 MiB x 1.000 x 4 = 250 GiB su un'istanza da 32 GiB

Gli ultimi due passi sono la via consueta verso un'uccisione per esaurimento di memoria. Nulla sembrava avventato: qualcuno ha quadruplicato work_mem per accelerare una query, e il traffico è cresciuto. Il resto l'ha fatto la moltiplicazione, e accade sotto carico e non al momento della modifica: per questo raramente viene colta in revisione.

Come leggere il risultato

• effective_cache_size non alloca nulla. È la stima che dai al planner di quanta memoria il sistema operativo dedica probabilmente alla cache dei tuoi dati, e cambia quali piani sembrano economici. Impostarlo alto non consuma memoria; impostarlo male produce piani cattivi.
• shared_buffers al 25% è il punto di partenza convenzionale e il valore predefinito di RDS. Alzarlo non è gratis: PostgreSQL si appoggia alla page cache del sistema come secondo livello, quindi la memoria spostata in shared_buffers è memoria tolta a quella cache. Valori molto grandi possono rendere tutto più lento.
• MySQL ha la forma opposta. innodb_buffer_pool_size vale 75% per impostazione predefinita perché InnoDB non si appoggia alla cache del sistema come fa PostgreSQL: vuole i dati nel proprio pool. Non trasferire qui l'intuizione di PostgreSQL.
• Alza work_mem per la sessione e non per il server quando serve a un solo report. SET work_mem in quella connessione dà il piano che vuoi senza concedere la stessa dotazione a tutte le altre connessioni dell'istanza.
• Una query con più nodi di ordinamento o hash può prendere work_mem più volte, all'interno di una sola connessione. I worker paralleli lo moltiplicano ancora. Il cursore degli ordinamenti per query esiste perché supporne uno è il modo più comune per cui questa stima esce troppo bassa.

Domande frequenti

Il caso peggiore è realistico o solo aritmetica?

È un tetto, non una previsione: ogni connessione dovrebbe eseguire contemporaneamente una query avida di memoria. Ma è il tetto che conta, perché i database non cedono con grazia quando lo raggiungono: vengono uccisi dall'OOM killer, e i picchi di traffico sono esattamente il momento in cui tutte le connessioni lavorano insieme.

Il mio database gestito non mi lascia cambiare shared_buffers. Perché?

Su RDS e Azure si imposta tramite un gruppo di parametri come formula su DBInstanceClassMemory, non come numero fisso, e cambiarlo richiede un riavvio perché il segmento di memoria condivisa viene allocato all'avvio. Cloud SQL blocca del tutto alcuni flag. Il valore predefinito di solito va bene; il parametro che merita attenzione è work_mem.

Come faccio a sapere quanta work_mem serve davvero a una query?

Esegui EXPLAIN (ANALYZE, BUFFERS) e cerca merge esterni o hash su disco. Se un ordinamento è finito su disco, il piano lo dice e riporta la dimensione. Quella cifra — poco più dello spill — è ciò che la query vuole, e ti dice se conviene alzare l'impostazione globalmente o se è la query a dover essere sistemata.

E i buffer per connessione di MySQL?

Si moltiplicano allo stesso modo. sort_buffer_size, join_buffer_size e read_rnd_buffer_size vengono allocati per connessione quando servono, oltre al buffer pool. Vale la stessa disciplina: tienili modesti e alzali per la sessione che ne ha davvero bisogno.

Fonti

• PostgreSQL — parametri di consumo delle risorse
• Amazon RDS — buone pratiche
• MySQL — configurazione del buffer pool InnoDB