Ot­ti­miz­za­re MySQL/MariaDB: come mi­glio­ra­re le pre­sta­zio­ni sui server Linux

La pre­sta­zio­ne di un database spesso determina in modo si­gni­fi­ca­ti­vo la velocità e la stabilità delle ap­pli­ca­zio­ni web. MariaDB e MySQL offrono numerose pos­si­bi­li­tà di ot­ti­miz­za­zio­ne: dalla con­fi­gu­ra­zio­ne agli indici, fino al caching e alla replica. Con misure mirate, le risorse del database possono essere uti­liz­za­te in modo più ef­fi­cien­te e i tempi di risposta delle query possono essere no­te­vol­men­te ridotti.

Perché si dovrebbe ot­ti­miz­za­re MariaDB e MySQL?

L’ot­ti­miz­za­zio­ne di MariaDB e MySQL è utile perché i database non ot­ti­miz­za­ti possono ra­pi­da­men­te rag­giun­ge­re i loro limiti. Una scarsa per­for­man­ce può causare lunghi tempi di ca­ri­ca­men­to, timeout o persino in­ter­ru­zio­ni del sistema.

So­prat­tut­to con grandi quantità di dati o molti accessi si­mul­ta­nei, ogni query inef­fi­cien­te grava su CPU e RAM. Anche la me­mo­riz­za­zio­ne di dati ri­don­dan­ti o la mancanza di indici in­flui­sco­no ne­ga­ti­va­men­te sulla velocità. Un database ot­ti­miz­za­to riduce il carico di sistema, migliora la sca­la­bi­li­tà e ga­ran­ti­sce che le ap­pli­ca­zio­ni fun­zio­ni­no in modo stabile anche sotto carico elevato.

Come si può ot­ti­miz­za­re MySQL/MariaDB?

Ci sono molte pos­si­bi­li­tà per ot­ti­miz­za­re un database MariaDB o MySQL su un server Linux. Queste includono la modifica della con­fi­gu­ra­zio­ne, l’ot­ti­miz­za­zio­ne degli indici, il mi­glio­ra­men­to delle query, la re­go­la­zio­ne di InnoDB e l’utilizzo di caching o replica. Di seguito vengono pre­sen­ta­te le prin­ci­pa­li misure.

Opzione 1: iden­ti­fi­ca­re le query lente

Una parte del processo per ot­ti­miz­za­re MySQL/MariaDB è il controllo di query lente o inef­fi­ca­ci. Una query MySQL/MariaDB mal strut­tu­ra­ta può ral­len­ta­re l’intero database.

MySQL/MariaDB può essere con­fi­gu­ra­to per re­gi­stra­re ogni query che impiega più del numero di secondi spe­ci­fi­ca­to. In questo modo, è possibile mo­ni­to­ra­re tutte le query lente e cor­reg­ger­le all’oc­cor­ren­za.

Per abilitare la re­gi­stra­zio­ne delle query lente, accedi a MySQL/MariaDB:

mysql -u root -p

Inserisci il seguente comando per abilitare la re­gi­stra­zio­ne:

SET GLOBAL slow_query_log = 'ON';

Il valore pre­de­fi­ni­to di soglia è 10 secondi. Usa il seguente comando per abilitare la re­gi­stra­zio­ne di ogni query che impiega più di 1 secondo:

SET GLOBAL long_query_time = 1;

Le query che impiegano più di 1 secondo vengono re­gi­stra­te in /var/lib/mysql/hostname-slow.log.

Anche strumenti di mo­ni­to­rag­gio come mysqltuner o performance_schema possono essere uti­liz­za­ti e for­ni­sco­no in­di­ca­zio­ni preziose per in­di­vi­dua­re quali query possono essere ot­ti­miz­za­te.

Opzione 2: adattare la con­fi­gu­ra­zio­ne di InnoDB

La con­fi­gu­ra­zio­ne di MariaDB/MySQL è uno dei metodi più efficaci per mi­glio­ra­re in modo so­ste­ni­bi­le le pre­sta­zio­ni di un database. Molte in­stal­la­zio­ni standard uti­liz­za­no valori generici, pro­get­ta­ti per ambienti di test più piccoli, ma non per sistemi pro­dut­ti­vi con molte query. Adattando spe­ci­fi­ca­men­te i parametri di InnoDB, il database può uti­liz­za­re le risorse in modo più ef­fi­cien­te.

Tra i parametri più im­por­tan­ti rientrano:

• innodb_flush_log_at_trx_commit: questa im­po­sta­zio­ne offre un notevole com­pro­mes­so tra pre­sta­zio­ni e af­fi­da­bi­li­tà. Per im­po­sta­zio­ne pre­de­fi­ni­ta, il valore è impostato su 1, il che significa che ogni tran­sa­zio­ne viene scritta im­me­dia­ta­men­te su disco. Ciò ga­ran­ti­sce la massima sicurezza, ma può ridurre no­te­vol­men­te le pre­sta­zio­ni in caso di un carico tran­sa­zio­na­le elevato. Un valore di 2 riduce si­gni­fi­ca­ti­va­men­te le ope­ra­zio­ni di I/O, mentre esiste un rischio minimo di perdita di dati in caso di crash.
• innodb_log_file_size: la di­men­sio­ne dei file di log di InnoDB influisce di­ret­ta­men­te sulla velocità delle ope­ra­zio­ni di scrittura. File di log più grandi si­gni­fi­ca­no che più tran­sa­zio­ni possono essere ac­cu­mu­la­te in memoria prima di essere scritte su disco.
• innodb_file_per_table: con questa opzione, per ogni tabella InnoDB viene creato un proprio file ta­ble­spa­ce. Ciò comporta diversi vantaggi: facilita la gestione di tabelle grandi, riduce la fram­men­ta­zio­ne all’interno del ta­ble­spa­ce condiviso e può mi­glio­ra­re le pre­sta­zio­ni durante i backup.
• innodb_buffer_pool_size: questo valore dovrebbe ti­pi­ca­men­te rap­pre­sen­ta­re il 50–80% della RAM di­spo­ni­bi­le, per mantenere quanti più dati e indici possibile in memoria.
• innodb_flush_method: questa opzione sta­bi­li­sce come InnoDB scrive dati e log su disco. Imposta questo valore su O_DIRECT per evitare un doppio buffering dei dati.

Per mo­di­fi­ca­re le im­po­sta­zio­ni di InnoDB, apri il file my.cnf per la modifica. Una con­fi­gu­ra­zio­ne di esempio in my.cnf potrebbe apparire così:

[mysqld]
innodb_flush_log_at_trx_commit = 2
innodb_log_file_size = 512M
innodb_file_per_table = 1
innodb_buffer_pool_size = 1G
innodb_flush_method = O_DIRECT

Riavvia MariaDB/MySQL affinché le modifiche abbiano effetto.

Opzione 3: adattare gli indici

Gli indici sono es­sen­zia­li per mi­glio­ra­re le pre­sta­zio­ni delle query di MySQL o MariaDB. Invece di cercare ogni riga di una tabella, il database può uti­liz­za­re l’indice per saltare di­ret­ta­men­te alle voci rilevanti.

Tuttavia, bisogna con­si­de­ra­re che troppi o errati indici possono essere con­tro­pro­du­cen­ti. Ogni indice ag­giun­ti­vo richiede spazio di ar­chi­via­zio­ne. Pertanto, è im­por­tan­te in­di­ciz­za­re solo le colonne che vengono ef­fet­ti­va­men­te uti­liz­za­te fre­quen­te­men­te.

Un semplice esempio: sup­po­nia­mo di avere una tabella users e di cercare spesso la colonna email. In tal caso, un indice può ac­ce­le­ra­re no­te­vol­men­te le query:

CREATE INDEX idx_user_email ON users(email);

Con questo indice, le query come:

SELECT * FROM users WHERE email='xyz@example.com';

vengono eseguite no­te­vol­men­te più ve­lo­ce­men­te, poiché il database non deve cercare in ogni riga della tabella ma accede di­ret­ta­men­te alle voci cor­ri­spon­den­ti.

Inoltre, gli indici combinati possono essere utili quando più colonne vengono fre­quen­te­men­te uti­liz­za­te insieme nelle query.

Re­go­lar­men­te do­vreb­be­ro essere rimossi anche gli indici non più necessari o raramente uti­liz­za­ti, per ri­spar­mia­re memoria e mi­glio­ra­re le pre­sta­zio­ni di scrittura. Nell’esempio viene eliminato l’indice idx_old_column:

DROP INDEX idx_old_column ON users;

Opzione 4: ot­ti­miz­za­re le query

Le query SQL complesse o inef­fi­ca­ci possono gravare no­te­vol­men­te sul database e peg­gio­ra­re le pre­sta­zio­ni, spe­cial­men­te con tabelle di grandi di­men­sio­ni. Per ot­ti­miz­za­re MySQL, dovresti prima ve­ri­fi­ca­re come il database esegue la query. Per questo è utile il comando EXPLAIN.

EXPLAIN SELECT id, email FROM users WHERE status='active';

Con EXPLAIN, MySQL/MariaDB mostra quali indici vengono uti­liz­za­ti, quante righe devono essere lette e in quale ordine vengono elaborate le tabelle. Così puoi capire se la query è ef­fi­cien­te o se sono ne­ces­sa­rie ulteriori ot­ti­miz­za­zio­ni, ad esempio l’aggiunta di indici o la modifica dei join.

Evita le query della forma SELECT *, poiché caricano molte colonne non ne­ces­sa­rie. Invece, dovresti in­ter­ro­ga­re solo le colonne realmente ne­ces­sa­rie. Questo riduce la quantità di dati tra­sfe­ri­ti e migliora la velocità della query. Con join complessi, conviene formulare le con­di­zio­ni nella clausola WHERE in modo il più preciso possibile per evitare scansioni inutili dell’intera tabella.

Opzione 5: replica e caching

Anche la replica, cioè la di­stri­bu­zio­ne del carico su più server, e il caching per ridurre il numero di accessi diretti al database, possono aiutare a ot­ti­miz­za­re MySQL/MariaDB.

Nella replica viene ge­ne­ral­men­te uti­liz­za­to il co­sid­det­to principio master-slave: il server master gestisce tutte le ope­ra­zio­ni di scrittura, mentre uno o più server slave replicano i dati e ge­sti­sco­no le query di lettura. In questo modo, il database può gestire carichi elevati in modo più ef­fi­cien­te senza so­vrac­ca­ri­ca­re il server master. La con­fi­gu­ra­zio­ne e l’im­ple­men­ta­zio­ne della replica ri­chie­do­no un iniziale sforzo, ma apportano un mi­glio­ra­men­to delle pre­sta­zio­ni, spe­cial­men­te in ap­pli­ca­zio­ni for­te­men­te uti­liz­za­te.

Inoltre, il caching può mi­glio­ra­re no­te­vol­men­te i tempi di risposta. MySQL/MariaDB offre il query cache, che memorizza tem­po­ra­nea­men­te i risultati delle query eseguite fre­quen­te­men­te. In questo modo, le stesse query non devono essere eseguite nuo­va­men­te. Con le seguenti im­po­sta­zio­ni, puoi definire la di­men­sio­ne del cache e attivare il query cache:

SET GLOBAL query_cache_size = 64*1024*1024;
SET GLOBAL query_cache_type = 1;

Per le ap­pli­ca­zio­ni moderne, è inoltre utile uti­liz­za­re soluzioni di caching esterne come Redis, che possono accedere ai dati fre­quen­te­men­te richiesti in maniera ancora più rapida.

Opzione 6: par­ti­zio­na­men­to delle tabelle

Con tabelle molto grandi la la­vo­ra­zio­ne delle query può ri­chie­de­re più tempo, poiché il database deve esaminare ogni riga. Con il par­ti­zio­na­men­to (ot­ti­miz­za­zio­ne MySQL) è possibile sud­di­vi­de­re le tabelle in parti più piccole e lo­gi­ca­men­te separate, ad esempio in base alla data, all’in­ter­val­lo di ID o altri criteri. Ogni par­ti­zio­ne viene gestita in­ter­na­men­te come una tabella separata, con­sen­ten­do ese­cu­zio­ni di query più rapide per quelle che coin­vol­go­no solo de­ter­mi­na­te par­ti­zio­ni.

Un esempio di par­ti­zio­na­men­to per anno in una tabella degli ordini potrebbe essere il seguente:

CREATE TABLE orders (
id INT,
order_date DATE,
amount DECIMAL(10,2)
)
PARTITION BY RANGE (YEAR(order_date)) (
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION p2024 VALUES LESS THAN (2025)
);

In questo caso, tutti gli ordini del 2023 saranno me­mo­riz­za­ti nella par­ti­zio­ne p2023 e tutti gli ordini del 2024 in p2024.

Opzione 7: uti­liz­za­re il con­nec­tion pooling

Ogni nuova con­nes­sio­ne a MySQL/MariaDB richiede tempo e risorse. Se la tua ap­pli­ca­zio­ne sta­bi­li­sce e termina una con­nes­sio­ne a ogni richiesta, si genera un carico inutile sul server. Il co­sid­det­to con­nec­tion pooling può essere uti­liz­za­to per ot­ti­miz­za­re MySQL e risolve questo problema man­te­nen­do aperto un certo numero di con­nes­sio­ni al database. Le ap­pli­ca­zio­ni accedono ri­pe­tu­ta­men­te a queste con­nes­sio­ni esistenti invece di crearne di nuove.

Un esempio in PHP con mysqli si presenta così:

$mysqli = new mysqli('localhost', 'user', 'password', 'db');
$mysqli->options(MYSQLI_OPT_CONNECT_TIMEOUT, 5);

Invece di aprire una nuova con­nes­sio­ne completa per ogni richiesta, il pool utilizza le con­nes­sio­ni già esistenti. Questo porta a tempi di risposta più rapidi e al­leg­ge­ri­sce allo stesso tempo il server del database.