Ottimizzare MySQL/MariaDB: come migliorare le prestazioni sui server Linux

La prestazione di un database spesso determina in modo significativo la velocità e la stabilità delle applicazioni web. MariaDB e MySQL offrono numerose possibilità di ottimizzazione: dalla configurazione agli indici, fino al caching e alla replica. Con misure mirate, le risorse del database possono essere utilizzate in modo più efficiente e i tempi di risposta delle query possono essere notevolmente ridotti.

Perché si dovrebbe ottimizzare MariaDB e MySQL?

L’ottimizzazione di MariaDB e MySQL è utile perché i database non ottimizzati possono rapidamente raggiungere i loro limiti. Una scarsa performance può causare lunghi tempi di caricamento, timeout o persino interruzioni del sistema.

Soprattutto con grandi quantità di dati o molti accessi simultanei, ogni query inefficiente grava su CPU e RAM. Anche la memorizzazione di dati ridondanti o la mancanza di indici influiscono negativamente sulla velocità. Un database ottimizzato riduce il carico di sistema, migliora la scalabilità e garantisce che le applicazioni funzionino in modo stabile anche sotto carico elevato.

Come si può ottimizzare MySQL/MariaDB?

Ci sono molte possibilità per ottimizzare un database MariaDB o MySQL su un server Linux. Queste includono la modifica della configurazione, l’ottimizzazione degli indici, il miglioramento delle query, la regolazione di InnoDB e l’utilizzo di caching o replica. Di seguito vengono presentate le principali misure.

Opzione 1: identificare le query lente

Una parte del processo per ottimizzare MySQL/MariaDB è il controllo di query lente o inefficaci. Una query MySQL/MariaDB mal strutturata può rallentare l’intero database.

MySQL/MariaDB può essere configurato per registrare ogni query che impiega più del numero di secondi specificato. In questo modo, è possibile monitorare tutte le query lente e correggerle all’occorrenza.

Per abilitare la registrazione delle query lente, accedi a MySQL/MariaDB:

mysql -u root -p

Inserisci il seguente comando per abilitare la registrazione:

SET GLOBAL slow_query_log = 'ON';

Il valore predefinito di soglia è 10 secondi. Usa il seguente comando per abilitare la registrazione di ogni query che impiega più di 1 secondo:

SET GLOBAL long_query_time = 1;

Le query che impiegano più di 1 secondo vengono registrate in /var/lib/mysql/hostname-slow.log.

Anche strumenti di monitoraggio come mysqltuner o performance_schema possono essere utilizzati e forniscono indicazioni preziose per individuare quali query possono essere ottimizzate.

Opzione 2: adattare la configurazione di InnoDB

La configurazione di MariaDB/MySQL è uno dei metodi più efficaci per migliorare in modo sostenibile le prestazioni di un database. Molte installazioni standard utilizzano valori generici, progettati per ambienti di test più piccoli, ma non per sistemi produttivi con molte query. Adattando specificamente i parametri di InnoDB, il database può utilizzare le risorse in modo più efficiente.

Tra i parametri più importanti rientrano:

• innodb_flush_log_at_trx_commit: questa impostazione offre un notevole compromesso tra prestazioni e affidabilità. Per impostazione predefinita, il valore è impostato su 1, il che significa che ogni transazione viene scritta immediatamente su disco. Ciò garantisce la massima sicurezza, ma può ridurre notevolmente le prestazioni in caso di un carico transazionale elevato. Un valore di 2 riduce significativamente le operazioni di I/O, mentre esiste un rischio minimo di perdita di dati in caso di crash.
• innodb_log_file_size: la dimensione dei file di log di InnoDB influisce direttamente sulla velocità delle operazioni di scrittura. File di log più grandi significano che più transazioni possono essere accumulate in memoria prima di essere scritte su disco.
• innodb_file_per_table: con questa opzione, per ogni tabella InnoDB viene creato un proprio file tablespace. Ciò comporta diversi vantaggi: facilita la gestione di tabelle grandi, riduce la frammentazione all’interno del tablespace condiviso e può migliorare le prestazioni durante i backup.
• innodb_buffer_pool_size: questo valore dovrebbe tipicamente rappresentare il 50–80% della RAM disponibile, per mantenere quanti più dati e indici possibile in memoria.
• innodb_flush_method: questa opzione stabilisce come InnoDB scrive dati e log su disco. Imposta questo valore su O_DIRECT per evitare un doppio buffering dei dati.

Per modificare le impostazioni di InnoDB, apri il file my.cnf per la modifica. Una configurazione 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 essenziali per migliorare le prestazioni delle query di MySQL o MariaDB. Invece di cercare ogni riga di una tabella, il database può utilizzare l’indice per saltare direttamente alle voci rilevanti.

Tuttavia, bisogna considerare che troppi o errati indici possono essere controproducenti. Ogni indice aggiuntivo richiede spazio di archiviazione. Pertanto, è importante indicizzare solo le colonne che vengono effettivamente utilizzate frequentemente.

Un semplice esempio: supponiamo di avere una tabella users e di cercare spesso la colonna email. In tal caso, un indice può accelerare notevolmente 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 notevolmente più velocemente, poiché il database non deve cercare in ogni riga della tabella ma accede direttamente alle voci corrispondenti.

Inoltre, gli indici combinati possono essere utili quando più colonne vengono frequentemente utilizzate insieme nelle query.

Regolarmente dovrebbero essere rimossi anche gli indici non più necessari o raramente utilizzati, per risparmiare memoria e migliorare le prestazioni di scrittura. Nell’esempio viene eliminato l’indice idx_old_column:

DROP INDEX idx_old_column ON users;

Opzione 4: ottimizzare le query

Le query SQL complesse o inefficaci possono gravare notevolmente sul database e peggiorare le prestazioni, specialmente con tabelle di grandi dimensioni. Per ottimizzare MySQL, dovresti prima verificare 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 utilizzati, quante righe devono essere lette e in quale ordine vengono elaborate le tabelle. Così puoi capire se la query è efficiente o se sono necessarie ulteriori ottimizzazioni, ad esempio l’aggiunta di indici o la modifica dei join.

Evita le query della forma SELECT *, poiché caricano molte colonne non necessarie. Invece, dovresti interrogare solo le colonne realmente necessarie. Questo riduce la quantità di dati trasferiti e migliora la velocità della query. Con join complessi, conviene formulare le condizioni 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 distribuzione del carico su più server, e il caching per ridurre il numero di accessi diretti al database, possono aiutare a ottimizzare MySQL/MariaDB.

Nella replica viene generalmente utilizzato il cosiddetto principio master-slave: il server master gestisce tutte le operazioni di scrittura, mentre uno o più server slave replicano i dati e gestiscono le query di lettura. In questo modo, il database può gestire carichi elevati in modo più efficiente senza sovraccaricare il server master. La configurazione e l’implementazione della replica richiedono un iniziale sforzo, ma apportano un miglioramento delle prestazioni, specialmente in applicazioni fortemente utilizzate.

Inoltre, il caching può migliorare notevolmente i tempi di risposta. MySQL/MariaDB offre il query cache, che memorizza temporaneamente i risultati delle query eseguite frequentemente. In questo modo, le stesse query non devono essere eseguite nuovamente. Con le seguenti impostazioni, puoi definire la dimensione del cache e attivare il query cache:

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

Per le applicazioni moderne, è inoltre utile utilizzare soluzioni di caching esterne come Redis, che possono accedere ai dati frequentemente richiesti in maniera ancora più rapida.

Opzione 6: partizionamento delle tabelle

Con tabelle molto grandi la lavorazione delle query può richiedere più tempo, poiché il database deve esaminare ogni riga. Con il partizionamento (ottimizzazione MySQL) è possibile suddividere le tabelle in parti più piccole e logicamente separate, ad esempio in base alla data, all’intervallo di ID o altri criteri. Ogni partizione viene gestita internamente come una tabella separata, consentendo esecuzioni di query più rapide per quelle che coinvolgono solo determinate partizioni.

Un esempio di partizionamento 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 memorizzati nella partizione p2023 e tutti gli ordini del 2024 in p2024.

Opzione 7: utilizzare il connection pooling

Ogni nuova connessione a MySQL/MariaDB richiede tempo e risorse. Se la tua applicazione stabilisce e termina una connessione a ogni richiesta, si genera un carico inutile sul server. Il cosiddetto connection pooling può essere utilizzato per ottimizzare MySQL e risolve questo problema mantenendo aperto un certo numero di connessioni al database. Le applicazioni accedono ripetutamente a queste connessioni 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 connessione completa per ogni richiesta, il pool utilizza le connessioni già esistenti. Questo porta a tempi di risposta più rapidi e alleggerisce allo stesso tempo il server del database.