PHP e MySql: ottimizzare le query lente (slow query) con FULLTEXT

Puo’ capitare di trovarsi di fronte a gestire database con tabelle di diverse decine di MegaByte (MB) e trovarsi con dei sistemi piuttosto lenti nel restituire i risultati delle query di ricerca di MySql. Nell’articolo andremo a vedere cosa migliorare per rendere piu’ ottimizzate le query di ricerca cosi’ da bilanciare il carico di lavoro del nostro server dedicato o VPS che sia.In particolare ci rendiamo conto che il nostro sistema e’ appesantito quando dalla nostra splendida shell di linux il comodo htop ci restituisce un carico piuttosto elevato del mysqld. Analogamente dal pulsante processi di PHPMyadmin o semplicemente dalla shell di mysql vediamo che molte query sono in sleeping ed il carico diventa piuttosto consistente. Cosa fare in queste situazioni ?
Ecco alcuni consigli utili:
1. Tracciare le query lente;
2. Verificare che non ci siano errori nell’error log del nostro sito dovuti a difetti di programmazione.
3. Evitare connessioni persistenti al Database;
4. Ridurre il timeout;
5. Incrementare il numero di connessioni massime al mysql
6. Incrementare il numero di MaxClients;
7. Ottimizzare il Database e le query di ricerca lente;

Il tracciamento delle query lente (slow query) e’ fondamentale per capire cosa sbagliamo. Per attivare il tracciamento basta editare il file /etc/my.cnf aggiungendo, nelle versioni piu’ recenti, queste righe:
long_query_time = 0
slow_query_log = 1
slow_query_log_file=/var/log/mysql/log-slow-queries.log

Dopo aver editato il file con vi ed aver salvato le modifiche bastera’ creare la cartella /var/log/mysql/ (mkdir /var/log/mysql/) ed il file dove andremo a loggare le query (touch /var/log/mysql/log-slow-queries.log). Una volta assegnati i giusti owner e group (chown mysql:mysql /var/log/mysql/log-slow-queries.log) sara’ possibile riavviare mysqld (/etc/init.d/mysqld restart) e mettersi a leggere le innumerevoli (spero per voi poche!) query di ricerche lente (tail /var/log/mysql/log-slow-queries.log -f).
Se ci rendiamo conto che sono piuttosto numerose possiamo bloccare l’esecuzione del tail con CTRL+c ed incrementare il long_query_time = 0 magari portandolo a long_query_time = 0.3 cosi’ da ridurre il numero di query a video e lavorare inizialmente con quelle piu’ lente.

Un’analisi dell’error log e’ fondamentale per verificare che non ci siano difetti di programmazione non gestite (Es.: mysql_query($nomeQuery); andrebbe gestito con un $result 0 @mysql_query($nomeQuery); if ($result) …) o messaggi di warning che in produzioni, solitamente, non sono impostati a video per evitare che l’utilizzatore veda a monitor quei piccoli errori che non impediscono la fruizione del sito o del prodotto che gli rendiamo disponibile).
Con la “nuova” versione di PHP 5.3.x molte funzioni sono diventate deprecate (ereg, session_register etc. etc.) e non saranno piu’ presenti dalla versione 6 quindi e’ fondamentale esaminare l’error log per rendersi conto di dove occorre intervenire. Il comodo  tail -f fa sempre al caso nostro per esaminare il sito mentre lo navighiamo cosi’ da rendersi facilmente conto degli errori bloccanti e dei warning.

Una delle cose che rendono quasi inservibile un database sono le connessioni persistenti che vanno assolutamente evitate se non strettamente necessarie quindi al posto del classico mysql_pconnect usare mysql_connect e, possibilmente disconnettersi. E’ anche possibile agire sul mysql per ridurre il tempo di timeout ed il numero massimo di connessioni editando ad esempio il file /etc/my.cnf ed inserendo:
max_connections = 500
wait_timeout = 25

Dopo aver salvato le modifiche occorre riavviare il mysqld (service mysqld restart).

Se tutto cio’ non bastasse e’ possibile incrementare il numero di MaxClients agendo direttamente sul file /etc/httpd/conf/httpd.conf (variabile da versione a versione di Linux ovviamente) ed incrementando il numero di MaxClients nei moduli prefork MPM e worker MPM facendoli magari arrivare a 256 nel preforker e 300 nel worker se la nostra macchina e’ una macchina prestante.

Qualora tutto cio’ non fosse sufficiente e’ necessario studiarsi bene le query piu’ lente. Noteremo spesso che le query piu’ lente in tabelle di grandi dimensioni possono risultare quelle dove viene usate il “comodo” ma lento comando LIKE. I casi tipici sono per le ricerche all’interno dei siti in campi CHAR, VARCHAR, TEXT, ad esempio:
$query = “SELECT * FROM tabellona WHERE comune LIKE ‘%$comune%’”;

Molto probabilmente il campo “comune” e’ un campo VARCHAR ma la query risulta molto lenta! Cosa possiamo fare in questi casi ? Rivolgerci alla comoda ricerca full-text. Tale tipo di ricerca si basa sugli indici. E’ quindi necessario modificare la tabella aggiungendo gli indici fulltext necessari. Nel nostro caso:
ALTER TABLE tabellona ADD FULLTEXT(comune);

Adesso occorrera’ cercare la query di ricerca incriminata e modificarla in questo modo:
$query = “SELECT * FROM tabellona WHERE MATCH(comune) AGAINST(‘$comune’)”;

Se andiamo a tracciare la stessa query vedremo che “magicamente” il tempo di esecuzione si e’ ridotto notevolmente ed il nostro server riesce a sopportare un carico di lavoro piu’ ampio.

Avete altri suggerimenti ? I commenti sono adatti allo scopo…