TRACCIARE ED OTTIMIZZARE LE QUERY LENTE IN PHP E MYSQL

Miglioramento della gestione delle query lente tramite il monitoraggio dei file di log e l’ottimizzazione dei tempi

Può accadere di dover gestire database con tabelle di diverse decine di megabyte (MB) e riscontrare problemi di lentezza nell’esecuzione delle query di ricerca in MySQL. In questo articolo esamineremo le strategie per ottimizzare le query di ricerca al fine di bilanciare il carico di lavoro del nostro server dedicato o VPS. In particolare, quando notiamo che il nostro sistema è appesantito e che il processo mysqld sta consumando notevole risorse, è importante prendere in considerazione alcune azioni correttive. Cosa fare in tali situazioni?

Ecco alcuni preziosi consigli:

1. Tracciare le query lente.
2. Verificare che non ci siano errori nel log degli errori del nostro sito dovuti a difetti di programmazione.
3. Evitare connessioni persistenti al database.
4. Ridurre il timeout.
5. Incrementare il numero massimo di connessioni in MySQL.
6. Incrementare il numero di MaxClients nel server web (ad esempio, Apache).
7. Ottimizzare il database e le query di ricerca lente.

Il tracciamento delle query lente (slow query) è fondamentale per identificare le inefficienze. Per attivare il tracciamento, è sufficiente modificare il file di configurazione MySQL (generalmente /etc/my.cnf) aggiungendo le seguenti righe nelle versioni più recenti:

long_query_time = 0
slow_query_log = 1
slow_query_log_file = /var/log/mysql/log-slow-queries.log

Dopo aver apportato queste modifiche e aver creato la cartella /var/log/mysql/ (utilizzando il comando mkdir /var/log/mysql/) e il file di log (con il comando touch /var/log/mysql/log-slow-queries.log), assegnate i permessi corretti (con il comando chown mysql:mysql /var/log/mysql/log-slow-queries.log). Riavviate quindi il servizio mysqld (utilizzando il comando /etc/init.d/mysqld restart) e iniziate a esaminare le query di ricerca lente (con il comando tail /var/log/mysql/log-slow-queries.log -f).

Se notate che ci sono molte query, potete interrompere l’output del log con CTRL+C e aumentare il valore di long_query_time da 0 a, ad esempio, 0.3 per visualizzare solo le query più lente.

Un’analisi del log degli errori è essenziale per verificare la presenza di errori di programmazione non gestiti (ad esempio, mysql_query($nomeQuery); dovrebbe essere gestito come $result = @mysql_query($nomeQuery); if ($result) ...) o di messaggi di avviso che non vengono visualizzati in produzione per evitare che gli utenti vedano errori minori che non impediscono l’utilizzo del sito o del prodotto.

Con la nuova versione di PHP 5.3.x, molte funzioni sono diventate obsolete (ad esempio, ereg, session_register, ecc.) e non saranno più disponibili dalla versione 6. Pertanto, è fondamentale esaminare il log degli errori per individuare dove è necessario apportare modifiche.

Una delle cose che può rendere quasi inutilizzabile un database sono le connessioni persistenti, che dovrebbero essere evitate a meno che non siano strettamente necessarie. Al posto della connessione persistente MySQL (mysql_pconnect), è preferibile utilizzare mysql_connect e, se possibile, disconnettersi quando non servono più. Inoltre, è possibile ridurre il tempo di timeout e il numero massimo di connessioni modificando il file di configurazione MySQL (solitamente /etc/my.cnf):

max_connections = 500
wait_timeout = 25

Dopo aver apportato queste modifiche, riavviate il servizio mysqld (con il comando service mysqld restart).

Se queste modifiche non fossero sufficienti, è possibile aumentare il numero di MaxClients nel file di configurazione del server web (ad esempio, /etc/httpd/conf/httpd.conf). La quantità ottimale varia a seconda della versione del sistema operativo, ma è possibile aumentare il numero di MaxClients nei moduli prefork MPM e worker MPM, ad esempio, portandoli a 256 nel prefork MPM e 300 nel worker MPM se la macchina è sufficientemente potente.

Se nonostante tutte queste modifiche la situazione non migliora, è necessario esaminare attentamente le query più lente. Spesso, nelle tabelle di grandi dimensioni, le query più lente coinvolgono l’uso del comando LIKE in campi CHAR, VARCHAR o TEXT. In tal caso, è consigliabile utilizzare la ricerca full-text basata sugli indici. Per farlo, è necessario modificare la tabella aggiungendo gli indici full-text necessari. Ad esempio:

ALTER TABLE tabellona ADD FULLTEXT(comune);

Dopo aver creato l’indice full-text, è possibile riscrivere la query di ricerca in questo modo:

$query = "SELECT * FROM tabellona WHERE MATCH(comune) AGAINST('$comune')";

Noterete che questa modifica notevolmente ridurrà il tempo di esecuzione della query e consentirà al vostro server di gestire un carico di lavoro più elevato.