Come ottimizzare il database di WordPress: uso degli indici

Come ottimizzare il database di WordPress: uso degli indici

L’obiettivo che cercheremo di raggiungere con le tecniche descritte in questo nuovo articolo è quello di ridurre i tempi di caricamento delle pagine WordPress, per tutte o solo su alcune specifiche: questo comporterà dei compromessi (come accettare che il database occupi più spazio) di cui non parlerò nello specifico, per non dilungarmi in aspetti molto tecnici ed annoiare i più. Per farlo, come prima cosa andremo ad individuare le tabelle critiche di WordPress.

Le tabelle MySQL di WordPress da ottimizzare

Di seguito riporto una lista delle principali tabelle di WordPress, almeno quelle base e senza considerare quelle create o usate dai plugin che installate. Tenete conto, quindi, che il vostro problema di rallentamento del sito potrebbe a volte essere dovuto a tabelle non qui listate. Non ho elencato tutte le tabelle (lo schema completo è questo) ma mi sono dedicato a quelle che vengono più spesso e probabilmente popolate da siti web molto anziani, con molti dati e con molti webmaster che ci abbiano messo mano senza reinstallare il database stesso.

Web Hosting
  • wp_options – Tutte le impostazioni specifiche di WordPress passano da qui, per cui questa tabella occuperà molto spazio e, come vedremo, per una precisa scelta progettuale di WP (ad oggi controversa e dibattuta) si tratta di una tabella senza indici che viene scansionata più volte riga per riga, per cui fa perdere potenzialmente molto tempo in fase di caricamento;
  • wp_postmeta – Include i meta dati dei post, quindi i loro attributi; anche qui, potenzialmente occupa molto spazio;
  • wp_posts – Memorizza post, pagine e tipi personalizzati; se avete 1000 pagine e 200 articoli salvati, avrete almeno 1200 righe occupate in questa tabella (almeno perchè anche gli allegati del sito contano come righe);
  • wp_usermeta – Salva le meta informazioni sugli utenti
  • wp_users – Salva le informazioni sulle anagrafiche degli utenti del vostro sito.

Quando diventa necessario effettuare delle operazioni di ottimizzazione del database?

Quando ottimizzare le tabelle di WordPress

Non sempre, e non si tratta di un’operazione per principianti o smanettoni: in genere è sconsigliato mettere mano agli aspetti che vedremo senza un background informatico solido, e bisogna avere la consapevolezza che modificare certi aspetti rischia di degradarne altri. Solo l’esperienza, in questi casi, può dare una mano a capire quale sia la scelta migliore (eventualmente non fare nulla).

La situazione tipica in cui le ottimizzazioni descritte in questo articolo sono necessarie riguardano due casi:

  1. siti web in WordPress che possiedano molti contenuti (pagine + articoli) o su cui siano state fatte molte modifiche in termini di personalizzazioni (child theme, plugin personalizzati);
  2. siti web in WordPress che funzionino via mu-plugins scritti da voi, o necessitino di molti plugin.

Ottimizzazione del codice

Si tratta di due casistiche, come è facile intuire se avete un minimo di esperienza, tutt’altro che isolate: in questi casi il database potrebbe essere facilmente sottoposto ad un carico di lavoro extra, per cui quello che è possibile fare è agire sul database stesso in termini strutturali, cercando al tempo stesso di limitare il numero di accessi a MySQL a quelli davvero importanti. Non sempre questa seconda operazione è agevole da effettuare, per cui esiste la possibilità di ricorrere ad hook personalizzati per disabilitare certe caratteristiche, modifiche pezzi di codice per farli eseguire più velocemente, eliminare hook poco utili oppure per eseguire o riscrivere le query (WP_Query, ad esempio) in modo più efficente.

Alcuni hook che possono potenzialmente dare fastidio e sovraccaricare il database sono quelli di init, quelli di tipo single_template, e tutti quelli che vadano a modificare le query standard (pre_get_posts, query_posts, wp_the_query, …).

WordPress e le ottimizzazioni automatiche: un aspetto sopravvalutato

L’ottimizzazione del database può passare per una procedura automatizzata che può effettuare WordPress di default, che è stata descritta qui (o al limite installando WP-Optimize) ma che possiede i caratteri della genericità: raramente si riesce, purtroppo, a migliorare seriamente le prestazioni di un sito web sulla base di queste azioni automatiche.

Si può anche pensare di ottimizzare le singole tabelle, ripararle ed agire mediante azioni simili con PHPMyAdmin o via console, ma questo comporta dei miglioramenti molto relativi e, da quello che ho visto in questi anni, è decisamente raro che basti fare un’azione del genere per ridurre i tempi di caricamento della pagina (azioni che, comunque, andrebbero eseguite periodicamente sul db).

Molto meglio, quindi, dedicarsi ad attività specializzate come quelle che vedremo ora.

WordPress e indici MySQL

Gli indici sono un’infrastruttura molto utilizzata in MySQL per garantire massima velocità in fase di lettura dei dati (anche in scrittura, con qualche accortezza: ma generalmente l’efficenza del sito in prima istanza è legata alle operazioni di lettura). Senza complicarci la vita a considerare i vari tipi di indice, diciamo che funzionano più o meno come l’indice di un libro e che in generale, ad ogni singolo caricamento della pagina, WordPress cerca più volte in vari “libri” diversi le informazioni che i client richiedono: motivo per cui un indice può darci una grossa mano a velocizzare le cose. Motivo per cui, quindi, bisognerebbe sempre accertarsi che esistano gli indici giusti all’interno del nostro database di WordPress, per quanto la maggioranza di essi siano già stati impostati e, soprattutto, l’uso sconsiderato di indici MySQL può portare facilmente ad un degrado delle prestazioni (lo ricordo sempre, e ribadisco che queste ottimizzazioni non sono per tutti).

La prossima cosa da fare, a questo punto, è quella di individuare le query che vengono eseguite nella pagina web che rallenta.

Visualizzare le query con Query Monitor

Per monitorare le query che vengono eseguire in WordPress è possibile ricorrere ad uno strumento come Query Monitor, un piccolo plugin per lo sviluppo nonchè uno strumento di analisi molto potete, in grado di mostrarvi sia i tempi di caricamento delle singole pagine che il numero di query eseguite che, appunto, il tipo di query eseguite e molto altro. Dopo averlo installato, basterà cliccate sul pannellino in alto dopo aver caricato la pagina critica per visualizzare le query più impegnative per il database (attenzione, in questa prima fase è opportuno effettuare le modifiche in locale e soprattutto farsi una copia completa e integra del database a parte, da ripristinare in caso di necessità). Quando lo installate rimanete loggati come amministratori, ed andate ad aprire la pagina che carica molto lentamente: vedrete tutte le informazioni automaticamente, e spuntando un’apposita opzione interna avrete la possibilità di settare un cookie per vedere le stesse informazioni anche da utenti anonimi.

Per intenderci, in questa schermata 2.27s è il tempo di caricamento della pagina, 35.74MB rappresentano la memoria RAM utilizzata per il caricamento della pagina, 0.3737s sono i secondi impiegati dal database per completare le proprie operazioni e 82Q è il conto del numero di query che vengono eseguite dalla pagina. Ovviamente valori puramente ideali da raggiungere sarebbero:

  • tempi di caricamento inferiori ad 1 secondo per pagina (pochi millisecondi sarebbe il top);
  • RAM consumata intorno ai 40 MB;
  • tempo di utilizzo del database più basso possibile (è pari almeno a quello necessario per caricare la pagina, ovviamente).

Nel dettaglio, Query Monitor è in grado di mostrarci moltissimo, comprese le funzioni che richiamano le query critiche nella pagina corrente: se possiamo intervenire sulle funzioni, cercheremo di velocizzarle, diversamente andremo a valutare di inserire degli indici dove necessario. In particolare, in questa sede vedremo di listare le query lente o slow query.

Dall’immagine è possibile vedere che ci sono 5 query lente, che il tempo di caricamento della pagina è di 3,80 secondi, che vengono eseguite 219 query, che ben 10 sono duplicate e che il template di riferimento è page-home.php. Ci sono anche altri dati ma qui, per brevità e visto che l’argomento è già corposo di suo, li ignorerò.

Cliccando sulla prima riga, otterremo una tabella che elenca le query critiche: nel mio caso le prime query critiche erano

  1. SELECT meta_value FROM wp_postmeta JOIN wp_posts (…)
  2. SELECT wp_posts.ID FROM wp_posts (…)
  3. SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts
  4. SELECT post_id, meta_key, meta_value
    FROM wp_postmetaWHERE post_id IN (…)

Per quello che sono riuscito a fare in questi giorni sul sito che ho curato, è abbastanza complesso riuscire a velocizzare query che prevedano l’operatore IN, così come lo è farlo in presenza di SQL_CALC_FOUND_ROWS, per cui non lo tratteremo. Relativamente più agevole è agire sui casi 1 e 2, ovvero una query di JOIN che coinvolge due tabelle e ed una semplice query di lettura su wp_posts.

Per visualizzare informazioni importanti sulla query in oggetto basta premettere alla query stessa, dopo aver copiata, l’operatore EXPLAIN: come risultato ci darà dei dati strutturati che interessano, in particolare, le tabelle e le colonne coinvolte (campo table e key).

Esempio:

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra 	
1 	SIMPLE 	wp_postmeta 	ref 	post_id,meta_key 	meta_key 	576 	const 	152 	Using where
1 	SIMPLE 	wp_posts 	eq_ref 	PRIMARY,type_status_date 	PRIMARY 	8 	trovalost_test.wp_postmeta.post_id 	1 	Using where

Come aggiungere un indice MySQL

Ammettendo di voler ottimizzare la colonna meta_key sulla tabella wp_postmeta come suggerito (post_id possiede già una chiave primaria), è possibile aggiungere un indice scrivendo così:

CREATE INDEX trovalost_1 ON wp_postmeta (meta_key)

Una volta eseguito il comando, prendiamo nota del nome dell’indice (trovalost_1) in modo da poterlo rimuovere, se necessario, in seguito; attendiamo che MySQL finisca il proprio lavoro, e riceveremo una notifica del genere:

MySQL ha restituito un insieme vuoto (i.e. zero righe). (La query ha impiegato 2.1081 secondi.)

A questo punto proviamo a ricaricare la pagina, ricordandoci che partivamo da 3,80s come tempo di caricamento globale e 2,07s come tempo di impiego del database. L’ottimizzazione sembra che abbia portato i suoi frutti (tempo di uso del database dimezzato) ma, probabilmente per altri motivi, il tempo di caricamento della pagina non è cambiato. Si tratta comunque di un miglioramento importante, anche perchè si rifletterà variamente sui tempi di caricamento delle altre pagine.

Ecco quello che mostra il nostro tool di ottimizzazione.

Come rimuovere un indice MySQL

Per rimuovere l’indice (attenzione che non è detto che ricopiando passivamente quello che scrivo otteniate risultati: bisogna sempre ragionare sulle query del vostro sito) scrivete semplicemente così:

DROP INDEX trovalost_1 ON wp_postmeta

e tutto tornerà come prima.

Giusto per fare un’altra ottimizzazione frequente, possiamo provare ad aggiungere un indice sulla wp_options, vista l’altissima frequenza di query del tipo SELECT * from wp_options WHERE autoload=’yes’:

CREATE INDEX trovalost_2 ON wp_options (autoload)

Nel mio caso questo sembra aver portato ad un altro piccolo miglioramento, questa volta anche in termini di tempi di caricamento della pagina.

Mi fermo volutamente qui perchè quello che conta è aver fatto passare l’idea corretta: considerate che potete effettuare ottimizzazioni anche su query di JOIN, facendo l’EXPLAIN della query ed andando a vedere le colonne coinvolte. Non sempre ciò che suggerisce EXPLAIN è da seguire in modo passivo, ma è sempre opportuno fare test con più indici e conservare quelli dalle migliori prestazioni.

Ti piace questo articolo?

2 voti

Su Trovalost.it puntiamo sulla qualità dei contenuti da quando siamo nati: la tua sincera valutazione può aiutarci a migliorare ogni giorno.

Come ottimizzare il database di WordPress: uso degli indici

Votato 10 / 10, da 2 utenti