Ottimizzazione MySQL: come scegliere il valore di InnoDB Buffer Pool

L’ottimizzazione del database MySQL è tra i tuning più importanti in tema di performance web. Quando ci si trova ad ottimizzare MySQL ci si imbatte irreparabilmente su InnoDB.

InnoDB è senza dubbio l’engine MySQL più performante quando si tratta di gestire query di tipo SELECT. La sua configurazione prevede diversi parametri tra cui innodb_buffer_pool_size.

InnoDB Buffer Pool indica la dimensione di RAM da dedicare per la memorizzazione di indici, cache, strutture dati e tutto ciò che ruota attorno InnoDB.

È uno dei parametri più importanti della configurazione di MySQL ed il suo valore va impostato in funzione del quantitativo di memoria RAM disponibile e dei servizi che operano sul server.

In questa guida esamineremo alcuni approcci, dal più semplice al più analitico, per la scelta del valore da configurare come innodb_buffer_pool_size.

Calcolo InnoDB Buffer Pool: Metodo 1

Il primo metodo consiste nell’impostare il valore di innodb_buffer_pool_size al 70-80% del quantitativo di memoria RAM disponibile.

Sebbene funzioni in buona parte dei sistemi con un quantitativo di RAM medio (tra i 32 e i 64 GB), questo metodo potrebbe risultare non performante per server con quantitativi di RAM alti o molto bassi.

In base a questo metodo, infatti, su un server con 192 GB di RAM il valore da impostare ammonterebbe a 150 GB, tralasciando così 40 GB di RAM. 

Calcolo InnoDB Buffer Pool: Metodo 2

Questo è il metodo descritto nel libro High Performance MySQL ed è un approccio sicuramente più analitco del precedente seppur anch’esso molto semplice da applicare.

Basta applicare la seguente procedura per calcolare un valore di InnoDB buffer pool size più accurato:

1. Calcola il quantitativo totale di RAM disponibile
2. Sottrai il quantitativo di RAM da dedicare al sistema operativo (solitamente un 5% del totale)
3. Sottrai il quantitativo di RAM da dedicare agli altri processi di MySQL
4. Sottrai il quantitativo di RAM da dedicare ad eventuali altri servizi di sistema (Varnish, ecc…)
5. Dividi il risultato per 105%, ovvero un’approssimazione dell’overhead richiesto per la gestione del buffer pool stesso.

Esempio. Avendo a disposizione un server con solo InnoDB, 4 GB di log e 192 GB di RAM, possiamo decidere di dedicare circa 10 GB al sistema operativo, 4 GB ai processi di MySQL e 170 GB ad InnoDB buffer pool, ovvero circa l’88% della RAM.

Naturalmente allocare il 5% di RAM per il sistema operativo può risultare troppo poco per quei server dove i quantitativi di RAM oscillano tra i 2 e i 32 GB.

Per server con un quantitativo di RAM inferiore o uguale a 1 GB si consiglia di lasciare ad InnoDB buffer pool il valore di default, ovvero 128 MB.

Per server con un quantitativo di RAM compreso tra 2 e 32 GB, è possibile dedicare al sistema operativo un quantitativo di RAM dato dalla seguente formula:

256MB + 256 * log2(dimensione RAM in GB)

Lo stesso vale per calcolare il quantitativo di RAM da allocare per gli altri processi interni di MySQL.

Calcolo InnoDB Buffer Pool: Metodo 3

Questo metodo calcola il valore da associare a InnoDB Buffer Pool in funzione del numero di dati memorizzati nelle tabelle InnoDB. Come prima cosa eseguire la seguente query SQL:

SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM (SELECT SUM(data_length+index_length) Total_InnoDB_Bytes FROM information_schema.tables WHERE engine='InnoDB') A;

Questa query ritornerà il valore di RIBPS, acronimo di Recommended InnoDB Buffer Pool Size basato sui dati memorizzati su tabelle InnoDB con un incremento del 60%.

Settare il valore di RIBPS espresso in GB come InnoDB Buffer Pool. Se ad esempio il valore restituito fosse 4, settare in my.cfn innodb_buffer_pool_size=4G e riavviare MySQL.

Dopo circa una settimana di utilizzo del database, eseguire la seguente query:

SELECT (PagesData*PageSize)/POWER(1024,3) DataGB FROM (SELECT variable_value PagesData FROM information_schema.global_status WHERE variable_name='Innodb_buffer_pool_pages_data') A, (SELECT variable_value PageSize FROM information_schema.global_status WHERE variable_name='Innodb_page_size') B;

Questa query restituirà il numero di GB di memoria attualmente utilizzata da InnoDB Data nel buffer pool, molto utile per eseguire diagognistiche e per ottimizzare il valore da configurare man mano che il database viene utilizzato.

Avvertimento importante

A volte InnoDB potrebbe richiedere un ulteriore 10% del valore per innodb_buffer_pool_size. Ecco cosa dice la documentazione MySQL al riguardo:

The larger you set this value, the less disk I/O is needed to access data in tables. On a dedicated database server, you may set this to up to 80% of the machine physical memory size. Be prepared to scale back this value if these other issues occur:

Competition for physical memory might cause paging in the operating system.

InnoDB reserves additional memory for buffers and control structures, so that the total allocated space is approximately 10% greater than the specified size.

The address space must be contiguous, which can be an issue on Windows systems with DLLs that load at specific addresses.

The time to initialize the buffer pool is roughly proportional to its size. On large installations, this initialization time may be significant. For example, on a modern Linux x86_64 server, initialization of a 10GB buffer pool takes approximately 6 seconds. See Section 8.9.1, “The InnoDB Buffer Pool”.

Se si intende seguire in modo rigoroso tale regola basta utilizzare la seguente query SQL che ritorna esattamente il valore di InnoDB Buffer Pool da specificare in my.cfn:

SELECT CONCAT(CEILING(RIBPS/POWER(1024,pw)),SUBSTR(' KMGT',pw+1,1)) Recommended_InnoDB_Buffer_Pool_Size FROM ( SELECT RIBPS,FLOOR(LOG(RIBPS)/LOG(1024)) pw FROM ( SELECT SUM(data_length+index_length)*1.1*growth RIBPS FROM information_schema.tables AAA, (SELECT 1 growth) BBB WHERE ENGINE='InnoDB' ) AA ) A;

Se si desidera aumentare del 25% invece che del 10% basta modificare il valore della subquery (SELECT 1 growth) BBB in (SELECT 1.25 growth) BBB