La replicazione nativa: scenari di utilizzo

di Patrizio Tassone – Pubblicato su Linux&C. n° 54.

La replicazione nativa di MySQL

La replicazione nativa di MySQL offre ottime opportunità di scalabilità, come si è potuto intuire nella prima puntata di questo articolo, e non è un caso che portali ad altissimo traffico quali YouTube, Flickr, Wikipedia, Digg, del.icio.us, solo per citare i più noti, utilizzino questa tecnologia per servire migliaia, talvolta milioni di utenti ogni giorno.
In questo articolo cercheremo di indagare più a fondo sugli scenari che si possono avvantaggiare della replicazione, sia in termini di scalabilità che di affidabilità.

Scenari di utilizzo

La replicazione, come si è visto, dispone di un host deputato a raccogliere tutte le connessioni in scrittura da parte dei client, che vengono riversate su un file di log (direttiva log-bin in my.cnf) che poi viene letto dagli altri host slave, riallineando tutte le basi di dati in un paradigma di shared-nothing, che non vede la presenza di costosi dischi condivisi.
L’utilizzo principe di una tecnologia come questa è relativa all’ambito web, in particolare come layer di back-end per la memorizzazione delle informazioni di portali ad alto traffico (contenuti dinamici, utenti, metadati di interesse statistico utili per il datawarehouse con finalità di marketing), dove le operazione di selezione pura (select) sono in un ordine di grandezza superiore rispetto alle scritture effettuate.

lc54_figura1
Lo scenario più frequente, e più semplice, è quello visibile in figura 1, che vede la presenza di un bilanciatore, rigorosamente ridondato, che suddivide il traffico in entrata su più web server che costituiscono, in questo caso, il lato applicativo dell’infrastruttura (nel caso più semplice, Apache + PHP/Perl/Python/Ruby o quant’altro); la batteria di server con Apache, in numero variabile a seconda del numero di accessi previsti, della banda in ingresso, della complessità delle operazioni applicative, sono connessi direttamente al back-end di memorizzazione dei dati: un host, il Master, accetterà le scritture mentre gli altri host verranno interrogati nel caso di letture.

lc54_figura2

lc54_figura2bis

Lo scenario è leggermente più complesso nel caso in cui, tra la batteria dei web server e il back-end, si frapponga un layer di application server (figura 2, scenario tipico in ambiti Java), dove risiede parte della logica applicativa: lato database non vi è molta differenza, se non che l’aggiunta di un layer intermedio offre una scalabilità maggiore e un minor numero di connessioni concorrenti.
Per sfruttare appieno le possibilità offerte da questa infrastruttura, le applicazioni devono avere una minima conoscenza dell’architettura che sarà utilizzata in produzione: in verità, le uniche informazioni di cui devono disporre gli sviluppatori sono relative alla presenza di due tipi di connessioni, una in scrittura (verso il master), una in lettura (bilanciata fra più slave).
Non necessariamente i layer superiori devono conoscere il numero esatto di slave presenti nella infrastruttura, in quanto sistemi di virtualizzazione più o meno complessi possono astrarre questa informazione.
Una prima possibilità, forse la più semplice da implementare, almeno nella forma base, vede utilizzo del DNS che, in round robin, bilancia gli accessi tra gli slave, con un file di zona che conterrà qualcosa di simile alle righe seguenti:


master A 192.168.1.100
slave A 192.168.1.110
slave A 192.168.1.120
slave A 192.168.1.130

I client potranno riferire le connessioni a master.retelocale.lan per il master e slave.retelocale.lan per i diversi slave e, a meno di cache-ing degli IP, potranno bilanciare le letture sui diversi database server.
In alternativa, possono essere utilizzati direttamente appliance di bilanciamento, realizzate con hardware dedicati (Cisco, Alteon i più diffusi) o con sistemi software (Linux LVS, Balance e similari), inserendo tutti gli slave dietro a tale device.
In caso di caduta di un nodo slave “nascosto” dal balancer, la configurazione dell’apparato dovrà provvedere ad escludere l’IP del server caduto; nel caso di uso puro di DNS, sarà necessario implementare un servizio di monitoring e far eseguire aggiornamenti dinamici ai record DNS utilizzando nsupdate.
In alternativa, è possibile specificare direttamente all’interno della logica applicativa gli IP dei server database, anche se questo comporta una certa scomodità nel caso in cui venga modificata dinamicamente l’architettura, in particolare in caso di failure.

Caduta del master

La divisione in master e slave fa sorgere immediatamente una prima osservazione, più che lecita: cosa accade se il master ha una failure grave e smette di funzionare?
La notizia positiva, che in questi frangenti fa scorgere un accenno di sorriso al project manager incaricato della supervisione del portale, è che il servizio continua ad essere erogato, e l’impatto sugli utenti è minimo, almeno per quanto riguarda la fruizione dei contenuti.
Se stiamo parlando di una portale di informazione o di un forum ad altissimo traffico, gli utenti potranno ancora leggere gli articoli e i post, effettuare ricerche, ma non potranno inserire commenti – operazione che comporta una scrittura sul master.
MySQL ovviamente dispone dei meccanismi di recover di un simile evento: per avere la certezza (quasi) assoluta di non perdere alcun record, la soluzione consigliata potrebbe essere quella di mettere sotto HA classico il nodo master, e solo questo, utilizzando storage condiviso, eventualmente remotizzato con iSCSI o AoE, che permette una buona affidabilità offrendo, nel contempo, una riduzione dei costi non indifferente.
In alternativa, si può optare per l’elezione di uno degli slave al ruolo (provvisorio) di master, operazione che vedremo tra un attimo in pratica: l’idea è quella di promuovere uno slave al ruolo di master, eventualmente sospendendolo dal ruolo di slave fino a quando il master non sarà completamente reintegrato (non obbligatorio, a seconda del carico un server MySQL può compiere egregiamente entrambe le operazioni).
In genere, quando questa è la policy che viene decisa in fase di progetto, è preferibile identificare uno slave come “candidato master”, di solito il server dotato di maggiori risorse computazionali e un MTBF superiore.
E’ però necessario informare tutti gli slave che l’host dal quale stavano leggendo gli aggiornamenti è cambiato, e che continuino la lettura da un altro nodo.
Sostituzione del master
La caduta del master impedisce l’aggiornamento della base dei dati, e quando il fermo si prolunga oltremodo può essere conveniente utilizzare uno degli slave e promuoverlo a master per accettare le nuove scritture da parte dei client.
La modifica della configurazione deve essere propagata agli altri host della replicazione: ricordate, infatti, che nel file di configurazione my.cnf degli stessi era indicato l’host cui connettersi e dal quale leggere il log-bin? Se questo viene a mancare tutta la configurazione deve essere aggiornata, comprendendo anche un reset dello slave neo-eletto, per essere sicuri che altre informazioni presenti accidentalmente nel log-bin dello stesso non vadano ad alterare la base dei dati in modo improprio. Normalmente la sostituzione del server è provvisoria e, proprio per questo, la configurazione statica, quella presente nel file di configurazione, non verrà modificata.

lc54_figura3

Lo scenario, a livello di IP, è quello visto la volta scorsa, e riproposta in figura 4: nell’esempio il il .100 cade, il .110 prende il posto del .100 e il .120 si collega al 110 per leggere le query di update.
Per prima cosa sullo slave01 (IP 110) sarà obbligatorio controllare che la direttiva log-bin sia presente nel file di configurazione: per uno server slave tale file di log non è necessario ma ora, diventando master, diventa fondamentale affinché la replicazione diventi possibile.
Nel caso la direttiva log-bin non fosse attiva, dopo l’inserimento della stessa è doveroso un riavvio del server.
Le ulteriori operazioni da compiere, a MySQL ovviamente avviato, sono le seguenti:


mysql-110> reset master;
mysql-110> slave stop;

Con la prima istruzione si effettua un reset del server nella sua veste di master: serve a ripulire i log, nel caso qualche istruzione fosse presente, magari ereditata dalla fase di installazione. Il secondo comando impartito, invece, blocca la replicazione, visto che il polling sull’IP ormai caduto continua senza sosta: questa operazione non è fondamentale, ma lo diventa nel caso in cui si attui l’IP aliasing relativo all’IP del master caduto, per evitare comportamenti anomali.
Sul secondo slave, invece, le operazioni sono diverse: per prima cosa fermiamo la replicazione


mysql-120> slave stop;

Adesso si può effettuare il cambio dei parametri necessari per la nuova connessione: in realtà, poiché gli utenti sono stati già configurati anche sugli slave (all’epoca della creazione facemmo replicare anche l’istruzione GRANT, si veda lo scorso numero), l’unico cambio da effettuare è solo l’IP del master.


mysql-120> CHANGE MASTER TO MASTER_HOST='172.16.2.110';

La sintassi è piuttosto chiara: nel caso in cui venissero cambiati altri parametri, quali username, password, il file di binlog da leggere, oppure il timeout, si dovranno inserire le informazioni da modificare una dopo l’altra nell’istruzione precedente, separate da virgole; dal manuale ufficiale:


mysql> CHANGE MASTER TO
MASTER_HOST='master2.mycompany.com',
MASTER_USER='replication',
MASTER_PASSWORD='bigs3cret',
MASTER_PORT=3306,
MASTER_LOG_FILE='master2-bin.001',
MASTER_LOG_POS=4,
MASTER_CONNECT_RETRY=10;

A questo punto non rimane che far ripartire la replicazione:


mysql-120> slave start;

Interrogando lo stato dello slave, ci possiamo sincerare del fatto che tutto sia in ordine:


mysql-120> SHOW SLAVE STATUS;
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.110
Master_User: slave02
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

Il Master_Host è cambiato, adesso compaiono le coordinate del vecchio slave, ora diventato Master: una prova semplice di creazione di una nuova tabella, o l’inserimento di un record potrà fugare ogni dubbio circa la funzionalità delle modifiche apportate.
Proviamo a riavviare a far ripartire MySQL sull’host 120, che svolge ancora il compito di slave anche se è cambiato il master al quale connettersi, e a lanciare un nuovo SLAVE STATUS: il server starà ancora replicando ma dall’IP che gli è stato passato con il CHANGE MASTER e non da quello presente nella configurazione!
Questo fatto potrebbe non essere piacevole ai più, anche perché sembrerebbe trovare poche spiegazioni logiche: sarà sufficiente controllare il file master.info presente nella directory di MySQL per capire cosa sia successo:


slave02# cat master.info
[...]
172.16.2.110
slave02
pass02
[...]

master.info riporta tutte le informazioni relative alla replica dal master, che hanno la precedenza rispetto a quelli presenti nel file di configurazione: tale file, a differenza di my.cnf, viene influenzato dai comandi di CHANGE MASTER ed è persistente al riavvio del server.
Nel caso in cui si voglia effettuare un reset completo dello slave è opportuno eseguire


mysql-120> SLAVE STOP; RESET SLAVE; SLAVE START;

perché la configurazione venga riletta da my.cnf.
In questo caso si ottiene quanto segue:


mysql-120> show slave status \G
Slave_IO_State: Connecting to master
Master_Host: 192.168.1.100
Master_User: slave02
Master_Port: 3306
Slave_IO_Running: No
Slave_SQL_Running: Yes

E’ tornato tutto nella norma: poiché il master è ancora fermo, la replicazione si è bloccata, come testimonia il No relativo a Slave_IO_Running.

Aggiornamento dei client

Il problema maggiore nel failure del Master non è elevare a tale ruolo un altro dei database server che, come avete visto, è piuttosto semplice. Certo, ci sono altre considerazioni da fare per un servizio veramente a prova di errore, ma sono considerazioni molto specifiche, una per tutte: chi è lo slave più aggiornato? Essendo asincrona, anche se molto veloce, non necessariamente gli slave sono tutti allo stesso livello.
Il problema maggiore, si diceva, è quello di informare del cambiamento gli application server o la batteria di web server: nel caso analizzato inizialmente di una connessione verso un indirizzo risolto dal DNS, tramite opportuni monitor che valutino lo stato dei servizi attivi è possibile aggiornare dinamicamente il record con nsupdate:


# nsupdate -k chiave.key << EOF
update delete master.retelocale.lan A
update add master.retelocale.lan 300 A 172.16.1.1
send
EOF

Ovviamente il tutto andrà eseguito dopo aver concesso le necessarie autorizzazioni ed aver configurato i permessi affinché questa operazioni possa avvenire in tutta sicurezza. In questo modo i client continueranno a collegarsi a master.retelocale.lan, ma l’indirizzo risolto questa volta sarà diverso.
Anche nel caso degli slave si può effettuare una operazione simile a quella appena vista, facendo attenzione affinché il round robin venga mantenuto integro.
Se invece la connessione avviene tramite IP diretto (oppure hostname presenti in /etc/hosts), che magari è inserito all’interno di file di configurazione delle applicazioni difficilmente modificabili, è opportuno utilizzare sistemi di clustering, quali ad esempio heartbeat, affinché l’IP venga migrato dal master caduto verso lo slave neo-eletto a master, in modo che non si perda l’associazione IP_master->Host_master.
Se questo è lo scenario che viene utilizzato, è consigliabile però decidere a priori qual è l’host candidato all’elezione, in modo da configurare il servizio di HA solo su questo, attivando gli opportuni sistemi di monitoraggio (dalla connessione di rete passando per lo switch, a cavi cross, a cavi seriali null-modem, nel caso la vicinanza delle macchine lo permetta, ecc.), affinché la migrazione avvenga senza particolari problemi.

Ritorno del Master

Già, prima o poi quel server dovrà tornare: a seconda di quanto passa dal fermo, se poche ore o qualche giorno, la base di dati presente sul master potrebbe essere poco aggiornata o estremamente disallineata: la linea generale vuole che il vecchio master entri nella replicazione come uno slave, in modo da leggere gli aggiornamenti dal nuovo master.
Nel caso però sia passato molto tempo, i log potrebbero essere stati ruotati e cancellati, e quindi non vi è più alcuna possibilità di aggiornarsi da essi.
E’ consigliabile ripristinare da un check point ben noto, il più recente possibile: allo scopo, normalmente, è deputato l’ultimo backup che, sincronizzato con la rotazione del log, permette di effettuare un reintegro in tutta sicurezza.
Se neppure questa soluzione è fattibile, a meno di complessi script di sync che agiscono sulla base di dati, c’è da effettuare la copia bruta delle informazioni da un nodo verso il vecchio Master.
Quando l’host rientrato sarà allineato alla base dei dati, si potrà prevedere uno switch per ripristinare la situazione iniziale, magari durante un fermo di servizio programmato; l’urgenza di tale operazione è da valutare di volta in volta, in particolare a seconda del tipo di server presenti: se sono tutti equivalenti, non ci dovrebbero essere problemi nell’attesa, mentre diversa è la valutazione nel caso in cui, ad esempio, il vecchio master sia un quadriprocessore e gli slave solo dei semplici mono-processori.

Affidabilità e solo affidabilità

Si parlava di scalabilità, ma sono molti i casi in cui un database MySQL è più che sufficiente, e quello che si vuole ottenere è solo migliorare l’affidabilità per non rischiare di vedere il servizio cadere magari per un guasto hardware.
Un possibile scenario vede la presenza di due server database in modalità attiva/passiva o attiva/attiva, con uno o due IP virtuali utilizzati per accettare le connessioni e che, in caso di caduta di uno dei nodi, vengono migrati secondo necessità.

lc54_figura4

Lo schema di figura 5 è il più semplice: i due nodi hanno un indirizzo privato per la gestione degli stessi (di solito posizionati su una rete di management separata, quando possibile), un indirizzo privato per la replicazione e dispongono di un indirizzo virtuale al quale arrivano le connessioni dal layer applicativo (siano essi Apache o Application Server).
L’IP virtuale viene assegnato, in partenza, a uno soltanto dei due server.
Tra i due nodi è attiva una replicazione che, per la particolarità della configurazione, viene detta circolare: in breve, ogni nodo è, contemporaneamente, master e slave dell’altro e ogni scrittura effettuata sul server .110 viene propagato sul server .120 e viceversa.
La configurazione è analoga a quelle già viste, anche se in modalità “incrociata”:


# Nodo 1 - IP reale 172.16.2.110
master-host = 172.16.2.120
master-user = nodo01
master-password = pass01


# Nodo 2 - IP reale 172.16.2.120
master-host = 172.16.2.110
master-user = nodo02
master-password = pass02

La direttiva log-bin deve essere abilitata su entrambi i server.
Si riavviano i due nodi per far leggere la nuova configurazione: eventualmente ci sarà da impartire reset master e reset slave, a seconda dei dati presenti nei file di log degli stessi. La situazione, alla fine, sarà simile alla seguente:


mysql-110> show slave status \G
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.2.120
Master_User: nodo01
Master_Log_File: mysql-bin.000001


Mysql-120> show slave status \G
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.2.110
Master_User: nodo02
Master_Log_File: mysql-bin.000001

Tutto sta replicando correttamente. Creiamo sul primo nodo un database di test:


mysql-110> create database test_ha;

Nel secondo creiamo una tabella:


mysql-120> use test_ha;
mysql-120> create table tab_ha (
id int auto_increment primary key, testo text);

Tutto sembra funzionare perfettamente. Se proviamo ad inserire alcuni record


mysql-110> insert into tab_ha values (
NULL, "primo record?");
mysql-120> insert into tab_ha values (
NULL, "secondo record?");

vengono entrambi replicate senza problemi. Ma cosa succederebbe se le scritture venissero effettuate contemporaneamente? Un modo veloce per effettuare una simulazione di un accesso concorrente alla base dati, considerando la velocità intrinseca del meccanismo di replicazione, consiste nel bloccare la replica in toto, e farla poi riprendere successivamente:


mysql-110> stop slave;
mysql-120> stop slave;

Inseriamo sia sul primo server che sul secondo un record:


mysql-110> insert into tab_ha values (
NULL, "terzo record?");
mysql-120> insert into tab_ha values (
NULL, "terzo record?");

Gli auto_increment dei due server hanno attribuito lo stesso ID progressivo:


mysql-110> select id, testo, @@server_id
from tab_ha where id=3;
| id | testo | @@server_id |
| 3 | terzo record? | 110 |


mysql-120> select id, testo, @@server_id
from tab_ha where id=3;
| id | testo | @@server_id |
| 3 | terzo record? | 120 |

Facendo ripartire la replicazione, il risultato non può che essere disastroso.


mysql-110> slave start;
mysql-110> show slave status \G
Slave_IO_Running : Yes
Slave_SQL_Running : No
Last_Errno : 1062
Last_Error : Error 'Duplicate entry '3' for
key 1' on query. Default database: 'test_ha'.
Query: 'insert into tab_ha values (NULL, "terzo record?")'

Risultato analogo per l’altro nodo. Ecco quindi che l’HA gestita con due serve attivi può dare sì benefici (due server che bilanciano le connessioni anche in scrittura), ma anche qualche piccolo problema al contorno, risolvibile solo se ben gestito.
La soluzione a questa empasse può avvenire in due modi: il primo, più semplice, è di non permettere di utilizzare i due server attivi contemporaneamente, ma solo uno alla volta (approccio attivo/passivo). Ecco che l’utilizzo di un solo IP virtuale aiuta nella configurazione, in quanto i client possono connettersi solo a quell’IP, e quell’IP risulta assegnato solo su uno dei server gestito da meccanismi di heartbeat.
Nel caso, invece, che si vogliano utilizzare i due server contemporaneamente, è opportuno utilizzare le direttive auto_increment_increment e auto_increment_offset, che permettono di impedire un conflitto di ID: il primo valore indica di quanto aumentare per ogni inserimento il valore del campo id, il secondo indica il valore di partenza dell’auto_increment stesso (che comunque deve essere inferiore o uguale al valore di auto_increment_increment)


mysql-110> show variables like "auto_increment%";
| Variable_name | Value |
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |

E’ ovvio che, in caso di due server, inserire come valore di partenza 1 e 2, rispettivamente per il primo e secondo server, con un incremento di 2 ad ogni insert, si ottiene che il primo server utilizzi sempre i valori ID dispari, e il secondo server tutti gli ID pari, impedendo qualsivoglia conflitto.


mysql-110> set auto_increment_increment=2;
mysql-110> insert into tab_ha values (
NULL, "che id avrà?");
mysql-110> insert into tab_ha values (
NULL, "che id avrà?");
mysql-110> select * from tab_ha;
[...]
| 3 | terzo record? |
| 5 | che id avrà? |
| 7 | che id avrà? |

Questa soluzione, estremamente semplice, impedisce i problemi visti sopra, ma farà storcere il naso a chi utilizza i campi auto_increment come progressivo di inserimento (un ordinamento su id, nell’esempio, non garantirà più l’ordinamento temporale dei record, sarà necessario utilizzare i timestamp, meglio se valorizzati dai trigger di MySQL 5.0) e chi li utilizza come ID seriale per svariati compiti (no, non vanno usati per fatture o ricevute o altri documenti fiscali!), dovrà optare per qualcosa di diverso.
Il caso presentato è estremamente semplice, ma ci sono altri scenari, più complessi da analizzare, che potrebbero portare a situazioni analoghe, e che meritano uno studio approfondito.
Nel caso uno dei due nodi abbia una failure, l’altro continuerà ad essere operativo: al rientro del server caduto, esso si dovrà agganciare alla replicazione e aggiornerà la propria base di dati: è ovvio che, fino a quando il server non si sarà perfettamente allineato, è opportuno evitare che venga interrogato, perché quasi certamente fornirebbe informazioni non corrette e/o aggiornate.
L’utilizzo dell’IP virtuale può risolvere, almeno in parte, molti dei problemi visti sopra, perché inserisce una sorta di “semaforo” alla connessione, rendendola di fatto esclusiva verso un solo server (anche se molto dell’affidabilità dipende dalle tecniche con la quale la si implementa).

Conclusioni

Una soluzione scalabile e affidabile è fondamentale per chi non vuole avere limiti di crescita nel proprio business: a volte si è costretti a sperare di non avere troppo successo perché, altrimenti, le richieste sarebbero così tante da non poter essere servite. MySQL offre tecnologie estremamente semplici da gestire, affidabili e ad un costo più che accettabile, zero!
Nelle prossime puntate, se i lettori saranno interessati, si potrà osservare da vicino il nuovo motore NDB e le nuove soluzioni di bilanciamento e high availability basate su MySQL Cluster “five nine” 99.999%.

Riquadro 1: Commit a due fasi

Nonostante la replicazione sia estremamente veloce, la possibilità che, in caso di caduta del master, qualche record non venga inoltrato agli slave esiste, è poco probabile, ma è reale. La replicazione nativa, come più volte spiegato, è asincrona, e in particolare non implementa in alcun modo la cosiddetta two-phase-commit: se una transazione utilizza questa tecnica, essa dà esito positivo solo quando i due nodi (in genere, almeno due, poi possono essere in numero maggiore) effettuano l’operazione con successo garantendo così che, in caso di caduta del server, almeno l’altro server abbia eseguito quell’operazione con successo.
Quando il valore attribuito alla perdita di un record è estremamente elevato (ad esempio, una transazione bancaria) può essere conveniente utilizzare una soluzione HA più sicura e estremamente più costosa, come uno storage condiviso tra i nodi… oppure utilizzare il MySQL Cluster 99.999%, che implementa la two-phase-commit nativamente.

MySQL Cluster 99.999%: affidabilità e prestazioni

di Patrizio Tassone, pubblicato su Linux&C. n° 61.

MySQL Cluster 99.999%: affidabilità e performance prima di tutto

Quando i dati dell’azienda sono nelle mani di un database, quel database deve essere una roccia. A seconda del tipo di attività in cui quella società si trova ad operare si possono anche tollerare fermi di servizio per manutenzione, per aggiornamento, anche per eventuali failure di qualche tipo; ci sono però ambiti dove niente di tutto questo è neppure ipotizzabile, e dove serve l’affidabilità di una soluzione “five nine”: 99.999% di uptime, che significano qualcosa come poco più di 5 minuti di stop in un anno.
MySQL, parlo del database standard, è già un prodotto estremamente affidabile e, senza scomodare soluzioni iper-ingegnerizzate, con le opportune accortezze, con utilizzo della replicazione e di soluzione di clusterizzazione classiche, si raggiungono (e parlo per esperienza personale) livelli di uptime prossimi al 100%.
Ci sono ambiti, però, dove un approccio del genere può non bastare: sono gli stessi ambiti dove i limiti della replica nativa di MySQL viene amplificata, dove neppure le patch relative al semi-sync di un gigante come Google sono sufficienti a risolvere il problema, dove neppure la clusterizzazione standard – seppur raffinata – può dare una mano.
Serve un database dove tutti i nodi sono contemporaneamente attivi, indistinguibili tra loro, dove contattare l’uno o l’altro non fa differenza, dove non c’è tempo di propagazione delle modifiche come nella replica nativa, dove i nodi sono totalmente interconnessi, dove la caduta di un nodo risulta totalmente trasparente all’applicazione: poco importa se una soluzione di questo genere può avere – come è ovvio che sia – dei limiti, poco importa se, come disse uno dei massimi esperti MySQL scherzando – ma non troppo – “ogni riferimento ad un RDBMS è puramente casuale”.
Serve un database parallelo, e – possibilmente – serve non dover pagare licenze per decine di migliaia di euro a processore.
In definitiva, serve MySQL Cluster “five nine”, disponibile sotto GPL, ma anche sotto licenza proprietaria (proprio come MySQL classico).

Un database “in-memory”

Non troverete nessuna installazione di MySQL Cluster nelle top 10 dei database mondiali: http://link.oltrelinux.com/9932db
Il perché è molto semplice: questa versione di MySQL si appoggia interamente alla memoria dei sistemi che partecipano al cluster, rendendo di fatto impossibile il raggiungimento di dimensioni enormi della base di dati.
Ma questo, a pensarci bene, non è un problema. MySQL Cluster non è la soluzione per tutti i problemi, anzi, la scelta dovrebbe andare prima su altre soluzioni già note e stabili.
E’ bene ricordare – anche se dovrebbe essere scontato – che l’importanza di un database non è data dalla dimensione dello stesso. Pensate per un attimo all’autenticazione degli utenti in una rete VoIP, alla tracciabilità delle stesse telefonate, oppure alla localizzazione di un particolare numero di telefono nel sistema GSM, oppure ancora alla indicizzazione di parole chiave in un motore di ricerca: sono tutti ambiti dove un database raggiungerà a fatica qualche decina di GB, ciononostante un disservizio, ma anche solo un rallentamento nella risposta, potrebbe creare non propri problemi.
Sono tutti ambiti dove non ci sono query particolarmente complesse, dove spesso le interrogazioni non vanno oltre la ricerca a chiave, oppure la selezione dei record tra due valori per un determinato campo, oppure richieste di operazioni come somma o media: tutte operazioni, queste, facilmente parallelizzabili tra i vari nodi, e che permettono una risposta pressoché istantanea.

L’architettura del database

Per comprendere il funzionamento di MySQL Cluster è fondamentale dare uno sguardo alla architettura (figura 1) dello stesso, ai componenti e alle interazioni tra gli stessi.

lc61_figura1
La parola cluster fa intuire da subito che questa soluzione vede la presenza di più nodi: mentre il database classico può risiedere su un solo host, qua di host ne servono, per una prima configurazione, almeno quattro: due nodi storage, un nodo API (o SQL Node) e un nodo di management.
I nodi storage sono deputati esclusivamente della memorizzazione e alla interrogazione delle tuple. Leggono la configurazione dalla console di amministrazione (management console), e devono essere inizializzati alla prima esecuzione.
Il nodo SQL è il gateway che permette di effettuare le interrogazioni verso il sottosistema clusterizzato di memorizzazione delle informazioni: è costituito proprio dal classico MySQL che siamo abituati ad utilizzare, questo per permettere alle applicazioni di non essere modificate per beneficiare della soluzione di clustering.
Il nodo di management permette il controllo di tutto il sistema: provvede a consegnare ad ogni nodo la propria configurazione, permette l’esecuzione dei backup e dei restore, e tiene sotto controllo lo stato del sistema. Inoltre, in caso di failure dei nodi di storage, diventa l’arbitro (arbitrator) che decide quale sezione del cluster deve rimanere attiva (ruolo questo particolarmente critico in caso del cosiddetto split-brain) e quale invece deve effettuare uno shutdown per non compromettere l’integrità del database.
L’hardware che viene consigliato da MySQL AB è di fascia media: un sistema Linux o Unix, con due processori dual core, 16 GB di RAM per macchina (ma anche meno, dipende dalla dimensione del database), dei dischi veloci in raid 0+1 (quindi almeno 4 SCSI o SAS), Gigabit ethernet.
E’ di fondamentale importanza la velocità del network e l’affidabilità dello stesso: poiché i nodi sono totalmente interconnessi e scambiano messaggi di sincronia di continuo, la velocità della comunicazione tra i nodi rischia di diventare un collo di bottiglia. E’ così importante che, nel caso di utilizzo di più di 8 nodi, viene consigliata l’adozione di schede Dolphin, device a bassissima latenza molto utilizzati più nel campo del supercalcolo che nelle sale dati delle aziende.
E’ consigliabile, inoltre, che le schede di rete siano in channel bonding, e che i device ethernet (switch, schede di rete) siano anch’essi ridondati.
La scalabilità di questo genere di architetture è definita “scale-out”, che si contrappone all’altro approccio, lo “scale-up”: per aumentare la potenza di calcolo e lo spazio destinato alla memorizzazione dei dati è sufficiente aggiungere un nuovo server, anziché sostituire il vecchio sistema con uno più potente. Il costo dello scale-out è generalmente inferiore, e di un ordine di grandezza almeno, rispetto allo scale-up.

La memorizzazione distribuita delle informazioni: l’engine NDB

Per disporre dell’alta affidabilità richiesta, è fondamentale evitare qualsiasi single-point-of-failure e, per evitare i s-p-o-f, in un approccio share-nothing (senza cioè alcuno storage condiviso) è necessario duplicare l’informazione su altri nodi.
Per raggiungere un maggiore parallelismo l’informazione deve risiedere su più nodi possibile. Se si vuole massimizzare (potenzialmente almeno) il parallelismo, l’informazione deve risiedere su tutti i nodi.
Il nuovo storage NDBCluster applica queste due semplici regole meravigliosamente: scompone ogni tabella in “fette” (o slice), calcolando un hash particolare sulla chiave primaria, ed ogni slice viene memorizzato su un server diverso. Ogni slice ha un numero di repliche all’interno del cluster che può essere personalizzato, ma non può essere inferiore a 2 (sempre per non creare s-p-o-f). I nodi che dispongono della stessa slice fanno parte dello stesso node-group: fino a quando almeno un host all’interno del node-group è attivo, il cluster è funzionante anche se ha subito diversi failure.
In figura 2 possiamo vedere uno schema dello slicing.
Nello schema sono presenti quattro host fisici, e la tabella viene divisa in quattro slice: ogni slice viene replicato due volte, e per ogni replica esiste una copia “primaria” e una copia “di backup”.
Per la disposizione delle repliche, è evidente che Host 1 e Host 2 formino un node-group, in quanto – insieme – sono gli unici depositari della “fetta” F1 e F3.
Cosa potrebbe succedere in caso di crash dei sistemi o degli host? Qualche esempio chiarirà meglio il concetto:

  • In caso di caduta di Host 1, il database non subirebbe alcun danno, in quanto le informazioni in possesso del server caduto sono replicati anche sul secondo host.
  • In caso di caduta di Host 2, vale già quanto visto per Host 1.
  • In caso di caduta di Host 1 e INSIEME di Host 3, il database non rileverebbe alcun problema, perché i server rimanenti possiedono, globalmente, l’intera informazione F1, F3 (su Host 2) e F2, F4 su Host 4.
  • E’ altresì evidente che la caduta di un intero Node Group, cioè del Host 1 insieme a Host 2, oppure Host 3 insieme a Host 4, determinano lo shutdown del Cluster in quanto l’informazione è irrimediabilmente corrotta e sarà necessario provvedere al ripristino di un backup e al riavvio del sistema.

Come si può osservare dagli esempi fatti, MySQL Cluster va oltre la caduta del singolo server riuscendo, in alcune situazioni, a mantenere il servizio funzionante anche con la caduta di due nodi (che, su quattro sistemi, male non è di certo): tale sicurezza può essere aumentata incrementando il numero di repliche definite da file di configurazione.
Ovviamente, maggiore sarà la sicurezza minore sarà lo spazio a disposizione lasciato per i record (al pari di quanto avviene con i dischi in RAID).

Passiamo all’installazione

Dopo aver presentato a grandi linee le caratteristiche del database parallelo, è ora di iniziare a sperimentare un po’. Per il nostro esempio utilizzeremo una architettura composta da quattro server, un server di management (192.168.100.10), due server storage (192.168.100.20 e .30) e un nodo SQL (192.168.100.40), più che sufficiente per far apprezzare le novità del “five nine”.
L’esempio, puramente didattico, verrà eseguito in ambiente virtuale, e ogni singolo host avrà a disposizione “appena” 256 MB di memoria.
Sui nodi storage e sul nodo di interrogazione andrà installato MySQL, mentre sul nodo di management non sarà necessario provvedere all’installazione completa (noi faremo così per semplicità operativa): per l’esempio ho provveduto ad una installazione da file binari, nessun pacchetto quindi, in modo che sia più semplice seguire i pochi passaggi indipendentemente dalla distribuzione adottata, e nel riquadro 2 a pagina seguente trovate tutti i passaggi da effettuare.

Console di Management

La configurazione di tutti i parametri del cluster è centralizzata all’interno del nodo di management, dove è in esecuzione un apposito servizio: ogni nodo del cluster, sia esso storage o SQL, si connette a tale console e “richiede” la propria configurazione. All’interno della directory supports_files c’è un template di file di configurazione che possiamo utilizzare come prototipo, al pari di quanto avviene per MySQL.


# cp support-files/ndb-config-2-node.ini mysql-cluster.ini

Aprendo il file con un qualsiasi editor, si nota subito come siano presenti alcune sezioni con suffisso “default”, che specificano le impostazioni predefinite per quel particolare parametro: la sezione [ndbd default], quindi, stabilità i parametri generali per le varie sezioni [ndbd] che seguiranno poco più sotto, evitando inutili ripetizioni.
Tutte le tipologie di nodo trovano in questo file INI la sua configurazione: troviamo la sezione ndbd per i nodi che si occuperanno della memorizzazione dei dati, la sezione mysqld per gli host di interrogazione SQL, e nbd_mgmd per lo stesso nodo (ma possono essere più di uno, ridondati) di management.
Tra le voci presenti, analizziamo le più importanti:

  • NoOfReplicas= 2 – indica quante repliche devono essere effettuate per ogni slice. Con due nodi, come nel nostro caso, non può essere che due il valore.
  • DataMemory= 80M – indica lo spazio allocato per la memorizzazione dei dati veri e propri.
  • IndexMemory= 24M – indica lo spazio allocato per la memorizzazione degli indici.
  • DataDir= /var/lib/mysql-cluster – è la directory dove vengono memorizzati i file su disco, i vari check point, e dove eventualmente vengono memorizzati i backup del nodo. Nel nostro caso modificheremo questo parametro con /opt/mysql/data-cluster, non prima di aver creato tale directory e averla assegnata all’utente mysql (per coerenza con l’installazione di MySQL)


# mkdir /opt/mysql/data-cluster
# chown mysql /opt/mysql/data-cluster

E’ interessante notare come ogni singolo nodo disponga di un id, che viene indicato esplicitamente nel file di configurazione. Oltre all’id è possibile indicare l’indirizzo IP (o il nome dell’host) dal quale verrà effettuato il collegamento, per evitare che chiunque possa collegarsi al sistema.
Modifichiamo i parametri HostName con gli indirizzi IP specificati sopra, cancelliamo le occorrenze ulteriori delle sezioni [mysqld] e salviamo lasciando invariati tutti gli altri parametri.
Il file risultate è visibile nel riquadro 3.
Siamo pronti per lanciare in esecuzione – dall’utente mysql – il servizio relativo alla console di management (il numero davanti al prompt fa riferimento all’ultima cifra dell’IP del sistema):


(10)$ ndb_mgmd -f /opt/mysql/mysql-cluster.ini
(10)$ ps aux | grep ndb_mgmd
mysql 5331... ndb_mgmd -f mysql-cluster.ini

Colleghiamoci col client di console e lanciamo il comando show, che ci permette di avere una visione generale dello stato degli host coinvolti:


(10)$ ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> show
Connected to Management Server at: localhost:1186
Cluster Configuration
[ndbd(NDB)] 2 node(s)
id=2 (not connected, accepting connect from 192.168.100.20)
id=3 (not connected, accepting connect from 192.168.100.30)


[ndb_mgmd(MGM)] 1 node(s)
id=1 @192.168.100.10 (Version: 5.0.45)


[mysqld(API)] 1 node(s)
id=4 (not connected, accepting connect from 192.168.100.40)

Solo il nodo di management si è connesso al cluster (192.168.100.10, viene anche evidenziata la versione), e quest’ultimo è in attesa del collegamento degli altri host.

Attivazione dei nodi dati

L’installazione dei due nodi dati sarà identica: MySQL è già installato seguendo le istruzioni del riquadro e, come già visto per la management console, è stata creata la directory /opt/mysql/data-cluster. Vi è da specificare come contattare il server di management: l’indirizzo dello stesso dovrà essere inserito nel file my.cnf, non essendo utilizzati dal cluster meccanismi di auto-discovery di sorta. Aggiungete in fondo al file queste poche righe:


[mysqld]
ndbcluster
ndb-connectstring=192.168.100.10


[mysql_cluster]
ndb-connectstring=192.168.100.10

ndbd è il servizio di storage dei dati e, una volta lanciato, rimane attivo in background. La prima esecuzione deve essere invocata passando l’opzione –initial, in modo che possa preparare adeguatamente la directory indicata.


(20)$ ndbd --initial
(20)$ ls -la /opt/mysql/data-cluster/
drwxr-x--- 2 mysql mysql ... ndb_2_fs
-rw-r--r-- 1 mysql mysql... ndb_2_out.log
-rw-r--r-- 1 mysql mysql... ndb_2.pid

La console di management rileverà subito il nuovo arrivo:


(10)$ ndb_mgm -e show | grep Version
id=2 @192.168.100.20 (Version: 5.0.45, starting, Nodegroup: 0)
id=1 @192.168.100.10 (Version: 5.0.45)

Al nuovo nodo di storage è stato assegnato il Nodegroup 0. Dopo aver eseguito gli stessi comandi sull’altro nodo di storage, l’output della console cambia:


(10)$ ndb_mgm -e show | grep Version
id=2 @192.168.100.20 (Version: 5.0.45, Nodegroup: 0,
Master)
id=3 @192.168.100.30 (Version: 5.0.45, Nodegroup: 0)
id=1 @192.168.100.10 (Version: 5.0.45)

I due nodi deputati alla memorizzazione dei dati sono comparsi entrambi, e si sono auto-organizzati in un unico nodegroup; il primo che ha effettuato il collegamento è promosso a “master”, nodo primario per quel gruppo.
Attivazione del nodo SQL
All’appello manca ancora il server per effettuare le interrogazioni, abbastanza fondamentale visto che è anche l’unico sistema (per il momento almeno) per poter inserire qualcosa nel database e provare che funzioni.
Anche nel my.cnf del nodo SQL devono essere aggiunte le righe come già viste per i nodi storage. Inoltre è fondamentale verificare che l’engine NDB sia attivo, altrimenti addio alle funzionalità di clustering:


(40)$ mysql -e "show engines" | grep ndbcluster
ndbcluster DISABLED Clustered,
fault-tolerant, memory-based tables

Questo dovrebbe essere l’output prima dell’aggiunta delle righe al my.cnf: successivamente al posto di DISABLED troveremo YES. Sulla console intanto, appare anche l’id=4


(10)$ ndb_mgm -e show | grep id=4
id=4 @192.168.100.40 (Version: 5.0.45)

E’ stata dura, ma ce l’abbiamo fatta, e tutti i nodi del cluster sono riusciti a collegarsi alla console. Non rimane da fare altro che qualche prova: creiamo un database, poi una tabella, inseriamo qualche record e, successivamente, spegniamo uno dei due nodi dei dati.


(40)mysql> create database prova_ndb;
(40)mysql> use prova_ndb;
Database changed
(40)mysql> create table tab_ndb (id int auto_increment
primary key, testo char(100)) engine=ndbcluster;
(40)mysql> insert into tab_ndb (testo) values
("primo record con il cluster");
(40)mysql> insert into tab_ndb (testo) values
("secondo record con il cluster");
(40)mysql> select id, testo from tab_ndb;
| id | testo |
| 1 | primo record con il cluster |
| 2 | secondo record con il cluster |

A questo punto, spegniamo il nodo Master: uno shutdown pulito, uno spegnimento brutale, scegliete voi (magari uno snapshot della macchina virtuale, per non rischiare corruzioni di filesystem… in realtà basta un kill di ndbd).
Istantaneamente lo stato della console cambia:


(10)$ ndb_mgm -e show
[ndbd(NDB)] 2 node(s)
id=2 (not connected, accepting connect from 192.168.100.20)
id=3 @192.168.100.30 (Version: 5.0.45, Nodegroup: 0, Master)

Il secondo nodo è diventato Master, mentre il primo è disconnesso. E la query?


(40)mysql> select count(*) from tab_ndb;
| count(*) |
| 2 |

La caduta di uno dei due nodi dati non ha portato alcuna conseguenza e il database risponde senza alcun tipo di problema. Tutto secondo teoria.

Conclusione

MySQL Cluster ha un design veramente ben fatto, ma è necessario analizzare bene quando valga la pena di essere adottato e quando è meglio indirizzarsi verso altre tecnologie meno sperimentali.
Nella prossima puntata, se l’argomento sarà di interesse, potremo continuare ad approfondire la gestione del failover, il backup, e un esempio di migrazione da database MyISAM a NDBStorage.
Cercherò di limitare la dimensione delle macchine virtuali utilizzate in questo articolo, rendendole disponibili alla URL:
http://www.oltrelinux.com/risorse/LC61/mysql.tar.gz

Riquadro 1: Limitazioni di NDB

Non è tutt’oro quello che luccica, e pensare di migrare senza troppi problemi un database MyISAM o InnoDB su storage NDB per approfittare dell’affidabilità che questo offre è una illusione che svanirà presto non appena si passerà dalla lettura delle caratteristiche alla console :-).
E’ richiesta una profonda conoscenza, oltre che dell’ambiente MySQL Cluster, anche della vostra base di dati: già, perché lavorando in memoria ogni byte è sacro, e bisogna prima capire come viene utilizzata dal server, e poi centellinare la RAM laddove veramente serve.
Gli indici hanno un “costo elevato”, e devono essere utilizzati solo se veramente necessari. Tabelle senza chiavi primarie non devono esistere (questo, in generale, indipendentemente dal Cluster, dovrebbe essere una buona norma a prescindere) perché, in caso non sia specificata una primary key, viene auto-generata dal server con un hash che non aggiunge informazioni in più e consuma memoria.
Sono presenti le transazioni, ma solo nella modalità “REPEATABLE READ”, non quindi al livello elevato di InnoDB. Come MyISAM, non supporta le foreign key e, anche se sono permesse nella dichiarazione, sono semplicemente ignorate.
Non esistono i campi VAR: almeno fino alla versione 5.1, i campi variabili sono memorizzati con la massima occupazione di memoria: un varchar(255) sarà quindi un char(255).
Queste le principali limitazioni, ma ve ne sono anche altre che vi invito a consultare sul sito ufficiale di MySQL.

Riquadro 2: Installazione di MySQL da archivio binario (tar.gz)

Una volta effettuato il download da uno dei tanti mirror, ecco la sequenza classica delle operazioni per l’installazione:


# groupadd mysql
# adduser -g mysql mysql
# cd /opt
# tar xvfz /path/to/mysql-5.0.45-linux-i686.tar.gz
# chown -R root:mysql mysql-5.0.45-linux-i686
# ln -s mysql-5.0.45-linux-i686 mysql
# cd mysql
# scripts/mysql_install_db --user=mysql
# chown -R mysql data

Vista l’esiguità di memoria degli host, copiamo il file
my-small.cnf nella basedir di MySQL, insieme allo script mysql.server:


# cp support-files/my-small.cnf my.cnf
# cp support-files/mysql.server .

Modifichiamo il parametro basedir del file mysql.server per rispecchiare la path di installazione:


basedir = /opt/mysql

Aggiungiamo anche /opt/mysql/bin alla path di sistema, in modo che sia più semplice l’utilizzo dei comandi; inoltre effettuiamo un link simbolico per far apparire my.cnf in /etc:


# PATH=$PATH:/opt/mysql/bin
# ln -s /opt/mysql/my.cnf /etc/

Per far partire il server, sarà sufficiente il comando:


# /opt/mysql/mysql.server start

Riquadro 3: Il file mysql-cluster.ini


[ndbd default]
NoOfReplicas= 2
MaxNoOfConcurrentOperations= 10000
DataMemory= 80M
IndexMemory= 24M
TimeBetweenWatchDogCheck= 30000
DataDir= /opt/mysql/data-cluster
MaxNoOfOrderedIndexes= 512


[ndb_mgmd default]
DataDir= /opt/mysql/data-cluster


[ndb_mgmd]
Id=1
HostName= 192.168.100.10


[ndbd]


Id= 2
HostName= 192.168.100.20


[ndbd]
Id= 3
HostName= 192.168.100.30


[mysqld]
Id= 4
Hostname= 192.168.100.40


# choose an unused port number
# in this configuration 63132,
# 63133, and 63134 will be used
[tcp default]
PortNumber= 63132

Le novità più interessanti della versione 5.1 di MySQL

Di Corrado Pandiani, pubblicato su Linux&C. n° 64.

Le novità più interessanti della versione 5.1 di MySQL

Con il rilascio della versione 5.0 MySQL ha introdotto le funzionalità avanzate che da tempo tanti utilizzatori chiedevano: stored procedure, stored function, view e trigger tanto per citare le principali. Possiamo quindi pensare alla 5.0 come alla versione che ha consentito a MySQL di fare un importante salto di qualità, aprendosi la strada verso un utilizzo più maturo anche al di fuori dell’ambito del web dove aveva trovato storicamente grande slancio.
Il rilascio della 5.1 stabile, che dovrebbe essere imminente (nel momento in cui scriviamo è disponibile la 5.1.23rc – Release Candidate, il prossimo passo dovrebbe essere la GA – Generally Available), introduce nuove funzionalità, pensate principalmente per il datawarehousing e che dovrebbe consentire un ulteriore balzo in avanti verso nuovi mercati.
MySQL 5.1 offre come novità di spicco una funzionalità che consente la gestione di tabelle di grandi dimensioni in maniera trasparente ed efficiente: il partitioning. E’ considerata la funzionalità più caratterizzante di questa versione, anche se ci sarebbe da citarne di altrettanto interessanti: l’event scheduler, i log tabellari, la replicazione row-based, l’interfaccia plugin, la replicazione e le tabelle su disco per MySQL Cluster, le funzioni XML.

In cosa consiste il partizionamento

Il partizionamento è una tecnica di progettazione fisica delle tabelle non certo nuova e con la quale molti DBA avranno già familiarità. Il principale motivo per cui ad un certo punto si decide di operare il partizionamento di una tabella, cioè di spezzarla in tabelle più piccole, è di ridurre la quantità di dati letta dalle query SQL al fine di ottenere tempi di risposta migliori.
Il partizionamento viene fatto in base ad una certa logica ben definita. Facciamo un esempio: supponiamo di avere la tabella con tutto l’elenco telefonico della Repubblica Popolare Cinese, parliamo grossomodo di almeno un miliardo di record e, supponendo che ogni record contenga i campi nome, cognome, indirizzo, città, provincia e numero di telefono per un totale di circa 80 byte a riga, il conto è presto fatto, siamo di fronte ad una tabella di 80GB circa.
Apriamo a questo punto una piccola parentesi sulla questione delle dimensioni e su cosa possa essere considerato effettivamente grande e cosa invece no; ci preme ricordare che non esiste un metro universale di giudizio, la valutazione da farsi è sempre relativa alla realtà che stiamo formalizzando ed è fortemente influenzata da fattori hardware e software. Insomma, ciò che a noi può sembrare enorme, calato nella nostra realtà, potrebbe non esserlo in altre: tutto è relativo!
Chiarito ciò, asseriamo che nel nostro caso qualche decina di GB per una sola tabella sono effettivamente tanti. Decidiamo quindi di suddividerla in sottotabelle più piccole per ottimizzare le interrogazioni.
lc64_mysql_schemi_partizionamento Esistono due tecniche per partizionare una tabella: col partizionamento orizzontale si creano tante tabelle identiche a quella di partenza (con nomi ovviamente diversi) e si ridistribuiscono i record originali tra di esse. La cosa fondamentale è decidere la logica di distribuzione dei record, quella che più appropriatamente chiamiamo la funzione di partizionamento. Nel nostro caso potremmo ad esempio utilizzare l’ordinamento alfabetico creando una tabella per ogni iniziale del campo cognome e inserire i record in ognuna di esse in maniera appropriata. Facile intuire come ad esempio la ricerca di un certo cognome risulti ora più efficiente; il server deve aprire e fare la ricerca su una tabella di dimensioni decisamente più contenute rispetto a quella di partenza. Oppure potremmo scegliere di creare tante tabelle quante sono le province cinesi e di distribuirvi all’interno i vari record in base alla residenza anagrafica. In questo secondo caso una query che dovesse elaborare delle statistiche di qualche tipo su una singola provincia o distretto telefonico trarrebbe beneficio da questa nuova struttura. Ad essere usata sarebbe una sola tabella con un numero decisamente inferiore di record.
Col partizionamento verticale si creano più tabelle con lo stesso numero di record della tabella di origine, ma ridistribuendo le colonne. Nel nostro caso potremmo creare una tabella con le sole colonne nome e cognome e un’altra con i rimanenti campi. Ha poco senso, lo ammettiamo, ed in effetti nella realtà questa tecnica è la meno usata anche perché implica di norma la duplicazione dell’intera chiave primaria in ogni sottotabella. Questa tecnica è valida, tuttavia, quando la tabella di partenza è costituita da molte colonne, la maggior parte delle quali usate di rado nelle ricerche. In tal caso separare le poche colonne usate più frequentemente dalle tante usate di rado porta a qualche significativo beneficio in termini di performance.
Un altro ambito di applicazione è quando si separano le colonne a dimensione variabile (varchar, varbinary, text, blob), di norma più lente, da quelle a dimensione fissa (tutti gli altri tipi), più veloci.
Lo scotto da pagare quando spezziamo le tabelle senza un opportuno supporto da parte del motore del database è che dovremo creare una serie di tabelle separate e l’applicazione che fa uso del DB deve essere messa a conoscenza della logica che si è utilizzata, in modo da decidere di volta in volta su quali sottotabelle operare. Si tratta di uno sforzo computazionale in più e soprattutto la manutenibilità del software risulta più difficile nel momento in cui dovessimo decidere di partizionare le tabelle in un modo diverso da quello deciso inizialmente; dovremmo non solo ridistribuire nuovamente i record, ma riscrivere anche parte della applicazione per renderla edotta della nuova logica.
Per chi ha già esperienza di MySQL e sta subito pensando alle tabelle MERGE, diciamo subito che la questione è ben diversa, anche se non è questo il momento di affrontare l’argomento.

Il partitioning di MySQL 5.1

MySQL 5.1 consente di partizionare una tabella automaticamente una tabella, facendo gestire le sotto tabelle direttamente al motore database. Al momento è supportato solo il partizionamento orizzontale.
La definizione del tipo di partizionamento, della funzione di partizione, del numero di partizioni e della loro posizione sul filesystem è definibile semplicemente con il comando CREATE TABLE.
Una volta creata la tabella e definito il partitioning nelle sue parti essenziali, la gestione della ripartizione dei record tra le varie partizioni avviene in maniera totalmente trasparente ed automatica. La logica di ripartizione dei record, ivi incluso lo spostamento degli stessi da una partizione ad un’altra in caso di modifica del valore di certi campi, è gestita dal server.
In pratica quello che in passato doveva necessariamente essere implementato a livello applicativo, viene ora gestito internamente dal DBMS in maniera molto efficiente.
La visione esterna di una tabella partizionata non è dissimile da quella di ogni altra tabella; le applicazioni ed i client che si collegano al DB hanno la sensazione di operare su una sola grande tabella, ignorando ogni dettaglio sottostante in merito al partizionamento, mentre invece il motore del database opera su tabelle più piccole, con i vantaggi di cui abbiamo parlato.
Ci sono poi altri vantaggi di importanza decisamente non secondaria: il partitioning consente ad esempio di superare il limite sulla dimensione dei file di certi filesystem, consentendo di avere tabelle grandi a piacere aumentando il numero delle partizioni. Inoltre, il fatto di poter creare ogni partizione in un punto qualsiasi del filesystem consente di aumentare considerevolmente le perfomance in quelle architetture in cui si possono sfruttare accessi paralleli su dischi diversi.
La gestione dei dati risulta inoltre facilitata; creando in maniera intelligente le partizioni, si possono effettuare operazioni che normalmente sarebbero molto onerose, sfruttano le sole partizioni necessarie senza coinvolgere tutte le altre. Ad esempio, in una ipotetica tabella degli ordini evasi da un negozio, partizionata in base all’anno di registrazione, risulta molto semplice cancellare tutti i record di una certa annata, basta rimuovere la partizione relativa con un apposito comando anziché eseguire un DELETE sull’intera tabella.
La rimozione di una partizione si traduce in pratica nella semplice cancellazione di un file su disco.
Il partitioning si può definire per tabelle MyISAM, InnoDB, Archive e NDB, ma non per gli altri tipi di engine. In particolare c’è da segnalare come il partitioning (in questo caso implicito) sia la tecnica utilizzata da MySQL Cluster per la ripartizione delle tabelle all’interno dei nodi dati, caratteristica che, insieme ad altre, lo rende una soluzione molto performante.
I paradigmi di partizionamento disponibili in MySQL 5.1 sono quattro: by range, by list, by hash, by key.
Prima di procedere con i dettagli per ognuno dei tipi, facciamo qualche premessa. Anzitutto gli esempi che seguiranno faranno sempre riferimento a tabelle di tipo MyISAM, ma si tenga presente che i comandi che utilizzeremo sono validi anche per gli altri engine supportati.

Partitioning by range

Nel partitioning by range le righe vengono assegnate alle varie partizioni in base al fatto che il valore di una certa colonna o della funzione di partizionamento cada all’interno di un certo intervallo.
Vediamo come creare una tabella in tal senso:


mysql> CREATE TABLE impiegato(
-> id INT UNSIGNED NOT NULL,
-> nome VARCHAR(50) NOT NULL,
-> cognome VARCHAR(50) NOT NULL,
-> data_assunzione DATE NOT NULL DEFAULT '1970-01-01',
-> data_licenziamento DATE DEFAULT '9999-12-31',
-> codice_reparto INT UNSIGNED NOT NULL)
-> PARTITION BY RANGE(YEAR(data_assunzione)) (
-> PARTITION p0 VALUES LESS THAN (2000),
-> PARTITION p1 VALUES LESS THAN (2002),
-> PARTITION p2 VALUES LESS THAN (2004),
-> PARTITION p3 VALUES LESS THAN (2006),
-> PARTITION p4 VALUES LESS THAN MAXVALUE
-> );

Prendiamo in esame la parte in grassetto; anzitutto diciamo a MySQL quale deve essere il tipo di partizionamento e quale colonna utilizzare per ripartire i record:


PARTITION BY RANGE(YEAR(data_assunzione))

in base al valore della funzione YEAR(data_assunzione) ogni record sarà posizionato in una delle partizioni che vengono definite in seguito.
Ogni partizione è identificata da un nome univoco arbitrario (p0, p1, ecc.) e, per ognuna di esse, dobbiamo definire il range di valori corrispondente. La definizione dei valori limite dei range devono essere necessariamente inseriti in maniera sequenziale dal minore al maggiore; in caso contrario l’operazione terminerà con un errore.
E’ buona cosa prevedere sempre come ultima partizione (p4 nel nostro caso) quella definita con la clausola VALUES LESS THAN MAXVALUE in modo tale che tutti i record che non dovessero cadere negli intervalli definiti in precedenza finiscano in quest’ultima. Senza l’ultima partizione definita in questo modo ogni record al di fuori dei range presenti genererebbe un errore di inserimento.
Andiamo a curiosare ora sul filesystem nella directory dei dati (in caso di incertezza utilizzate il comando SHOW VARIABLES LIKE ‘datadir’), troveremo i seguenti file:


# cd /usr/local/mysql/data/test
# ls -l impiegati*
-rw-rw---- [...] 8782 [...] impiegati.frm
-rw-rw---- [...] 40 [...] impiegati.par
-rw-rw---- [...] 0 [...] impiegati#P#p0.MYD
-rw-rw---- [...] 1024 [...] impiegati#P#p0.MYI
-rw-rw---- [...] 0 [...] impiegati#P#p1.MYD
-rw-rw---- [...] 1024 [...] impiegati#P#p1.MYI
[...]

Rispetto alle tabelle MyISAM tradizionali, costituite da soli tre file (frm: definizione e charset, MYD: dati, MYI: indici), ora abbiamo un nuovo file .par che definisce le partizioni e una coppia di file dati (MYD) e file indici (MYI) per ognuna delle partizioni definite, quindi in realtà l’operazione di partizionamento non riguarda solo i dati, ma anche gli eventuali indici presenti.
Nel caso in cui avessimo creato la tabella di tipo InnoDB, però, non avremmo trovato sul filesystem tali file. Nel caso di InnoDB, infatti, il partizionamento è gestito internamente al tablespace (lo spazio condiviso, di solito un unico grande file, nel quale sono memorizzate insieme tutte le tabelle InnoDB presenti nel DBMS e tutti i relativi indici).
Proviamo a fare qualche INSERT:


mysql> INSERT INTO impiegati
-> VALUES(1,'Giuseppe','Verdi','1995-05-05',null,5);
mysql> INSERT INTO impiegati


-> VALUES(2,'Mario','Rossi','2007-10-11',null,5);
mysql> INSERT INTO impiegati
-> VALUES(3,'Luigi','Bianchi','2004-10-11',
-> '2008-01-01',4);

Andiamo ora a verificare sul filesystem se i record sono stati inseriti nelle partizioni previste; basta vedere quali siano i file dati che sono aumentati di dimensione.


# ls -l impiegati*
-rw-rw---- [...] 32 [...] impiegati#P#p0.MYD
-rw-rw---- [...] 28 [...] impiegati#P#p3.MYD
-rw-rw---- [...] 36 [...] impiegati#P#p4.MYD

Le righe sopra riportate indicano le partizioni che sono variate. Il primo record è stato inserito in p0 in quanto l’anno 1995 è minore del primo valore di range definito: 2000. Il secondo record è stato inserito in p4 in quanto il valore 2007 è più grande di 2006 o, meglio, è minore di MAXVALUE. Il terzo record, il cui valore è 2004, è stato inserito in p3 essendo minore di 2006.
Facciamo notare infine che la funzione (o la colonna) di partizionamento deve restituire un valore intero.

Partitioning by list

Questo tipo di partizionamento è simile al precedente con la sola differenza che in questo caso anziché definire degli intervalli, vengono definite liste discrete di valori. Vediamo un esempio in cui creiamo le partizioni in base al valore del campo codice_reparto:


mysql> CREATE TABLE impiegati (
-> id INT UNSIGNED NOT NULL,
-> nome VARCHAR(50) NOT NULL,
-> cognome VARCHAR(50) NOT NULL,
-> data_assunzione DATE NOT NULL DEFAULT '1970-01-01',
-> data_licenziamento DATE DEFAULT '9999-12-31',
-> codice_reparto INT UNSIGNED)
-> PARTITION BY LIST(codice_reparto) (
-> PARTITION RNord VALUES IN (2,6,10,11),
-> PARTITION RSud VALUES IN (3,4,9,12),
-> PARTITION REst VALUES IN (1,5),
-> PARTITION ROvest VALUES IN (7,8,13)
-> );

In questo tipo di partizionamento non è possibile creare una partizione in cui inserire eventuali valori non previsti come nel caso del partition by range. Valori non validi restituiscono un errore di inserimento.


mysql> INSERT INTO impiegati
-> VALUES(1,'Giuseppe','Verdi','1995-05-05',null,5);
mysql> INSERT INTO impiegati
-> VALUES(2,'Mario','Rossi','1995-05-05',null,10);
mysql> INSERT INTO impiegati
-> VALUES(3,'Carlo','Bianchi','2005-12-05',null,15);
ERROR 1523 (HY000): Table has no partition
for value 15

Tutte le altre considerazioni sono le stesse viste in precedenza.

Partitioning by hash

Con i partizionamenti by range e by list, dove siamo noi a predeterminare la funzione di partizionamento, a lungo andare possiamo correre il rischio di ottenere partizioni molto diverse: alcune con molti ed altre con pochissimi record. Questa situazione porta ovviamente ad una disomogeneità dei tempi di risposta. Se non vogliamo correre questo rischio o non vogliamo scervellarci troppo a scegliere la funzione di partizionamento più appropriata, possiamo affidarci a MySQL e far
gestire tutto a lui in maniera automatica con il partitioning by hash. Con questo tipo di partizionamento il server distribuisce i record in maniera omogenea e consente di ottenere partizioni più o meno della stessa dimensione. Bisogna solo decidere quale sia la colonna (o anche in questo caso la funzione) in base alla quale partizionare e stabilire il numero di partizioni desiderate. Ad esempio:


mysql> CREATE TABLE part_hash
-> (col1 INT, col2 DATE, col3 CHAR(10))
-> PARTITION BY HASH ( YEAR(col2) )
-> PARTITIONS 5;

Valgono anche in questo caso le considerazioni precedenti ad eccezione del fatto che in questo caso il nome delle partizioni è gestito in automatico da MySQL. Resta valido il fatto che la funzione di partizionamento deve restituire un valore intero.

Partitioning by key

Il partitioning by key è simile a quello by hash ad eccezione del fatto che, mentre prima era necessario definire una funzione di partizionamento, in questo caso è MySQL a scegliere la funzione di hashing da utilizzare.
Possiamo specificare una colonna di partizionamento da utilizzare, altrimenti verrà utilizzata di default la PRIMARY KEY della tabella, oppure una colonna definita come UNIQUE.
Nel seguente esempio viene utilizzata la colonna col1 associata alla chiave primaria:


mysql> CREATE TABLE p_key1
-> (col1 INT, col2 INT, PRIMARY KEY(col1))
-> PARTITION BY KEY()
-> PARTITIONS 3;

mentre nel seguente viene utilizzata la colonna col2 associata alla UNIQUE KEY:


mysql> CREATE TABLE p_key2
-> (col1 INT, col2 INT, UNIQUE KEY(col2))
-> PARTITION BY KEY()
-> PARTITIONS 3;

Specificando invece una o più colonne bisogna tenere presente che devono essere sempre in parte comprese nella chiave primaria o devono coincidere con la chiave primaria stessa.
Vediamo un esempio in cui questa regola è violata ed otteniamo un errore:


mysql> CREATE TABLE p_err
-> (id INT, col1 INT, PRIMARY KEY(id))
-> PARTITION BY KEY(col1)
-> PARTITIONS 5;
ERROR 1503 (HY000): A PRIMARY KEY must include
all columns in the table's partitioning function

Questa è una importante differenza rispetto alle altre tipologie di partizionamento dove invece si può tranquillamente specificare colonne non necessariamente facenti parte della chiave primaria. Un’altra differenza è che in questo caso le colonne specificate possono essere di qualsiasi tipo, mentre negli altri casi devono essere necessariamente di tipo intero.


mysql> CREATE TABLE reddito (
-> codice_fiscale CHAR(16) PRIMARY KEY,
-> reddito INT)
-> PARTITION BY KEY(codice_fiscale)
-> PARTITIONS 10;
Query OK, 0 rows affected (0.07 sec)

Gestione delle partizioni

Una volta creata una tabella partizionata, questa non dovrà rimanere così per sempre, è possibile variarne in ogni momento il numero e la tipologia delle partizioni a seconda delle proprie esigenze.
La modifica del partizionamento implica normalmente lo spostamento di buona parte dei record (a volte tutti) per soddisfare alle nuove condizioni; questa operazione è tanto più onerosa quanto più è grande la tabella e tanto più questi spostamenti sono numerosi. L’operazione di modifica delle partizioni deve generalmente essere di tipo straordinario, deve essere ben ponderata e soprattutto dettata da esigenze reali.
Vediamo alcuni dei comandi più utili.
Aggiunta di una partizione nel caso di un by range:


mysql> ALTER TABLE impiegato
-> ADD PARTITION (PARTITION p5
-> VALUES LESS THAN (2008));

attenzione, però: si può aggiungere una partizione solo se è l’ultima della lista, non posso cioè aggiungere un nuovo intervallo che cada tra due già esistenti.
Aggiunta di una partizione nel caso di un by list:


mysql> ALTER TABLE impiegato
-> ADD PARTITION (PARTITION nuovoReparto
-> VALUES IN (14,15,16));

in questo caso l’istruzione non genera errori se nessuno dei valori indicati è presente in partizioni già esistenti.
La rimozione di una intera partizione è analoga per entrambi i casi, basta indicarne il nome:


mysql> ALTER TABLE impiegato DROP PARTITION p5;

la rimozione di una partizione implica anche la rimozione di tutti i record in essa contenuti.
Se vogliamo riorganizzare invece in modo diverso le partizioni presenti (ridistribuendo i record e senza perdere alcun dato) possiamo utilizzare il comando REORGANIZE.
Supponiamo di avere la seguente tabella così definita:


mysql> SHOW CREATE TABLE utenti\G
Table: utenti
Create Table: CREATE TABLE `utenti` (
`id` int(11) default NULL,
`nome` varchar(30) default NULL,
`cognome` varchar(30) default NULL,
`data_registrazione` date default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
PARTITION BY RANGE ( YEAR(data_registrazione) ) (
PARTITION p0 VALUES LESS THAN (2006) ENGINE = MyISAM,
PARTITION p1 VALUES LESS THAN (2007) ENGINE = MyISAM,
PARTITION p2 VALUES LESS THAN (2008) ENGINE = MyISAM,
PARTITION p3 VALUES LESS THAN (2009) ENGINE = MyISAM
)

possiamo ad esempio modificare la partizione p0, spezzandola in due:


mysql> ALTER TABLE utenti REORGANIZE PARTITION
-> p0 INTO ( PARTITION p01 VALUES LESS THAN (2005),
-> PARTITION p02 VALUES LESS THAN (2006));

Possiamo anche fare il merge di partizioni adiacenti:


mysql> ALTER TABLE utenti
-> REORGANIZE PARTITION p01,p02 INTO (
-> PARTITION p0 VALUES LESS THAN (2006));

tornando in questo modo alla tabella come era in origine oppure possiamo anche reimpostare tutte le partizioni, passando ad esempio dalle 4 originarie a 2 solamente:


mysql> ALTER TABLE utenti
-> REORGANIZE PARTITION p0,p1,p2,p3 INTO (
-> PARTITION m0 VALUES LESS THAN (2007),
-> PARTITION m1 VALUES LESS THAN (2009));

Per il partitioning by list, il comando REORGANIZE funziona allo stesso modo.
Per quanto riguarda i partizionamenti by hash e by key, le cose sono un po’ diverse ed inoltre non è possibile fare il DROP di una partizione come per gli altri casi. Possiamo, però, diminuire ed aumentare a piacere il numero delle partizioni e, come sempre, MySQL si occuperà automaticamente di ridistribuire opportunamente tutti i record.
Supponiamo di avere una tabella con 10 partizioni:


mysql> CREATE TABLE utenti (
-> id INT,
-> nome VARCHAR(30),
-> cognome VARCHAR(30),
-> data_registrazione DATE )
-> PARTITION BY HASH( MONTH(data_registrazione) )
-> PARTITIONS 10;

per ridurre ad esempio il numero della partizioni da 10 a 6 dobbiamo usare comando COALESCE indicando il numero di partizioni da eliminare:


mysql> ALTER TABLE utenti COALESCE PARTITION 4;

Ovviamente il numero indicato non può essere uguale o superiore al numero di partizioni presenti se non vogliamo ottenere un errore.
Infine, per aggiungere altre partizioni, utilizziamo:


mysql> ALTER TABLE utenti ADD PARTITION PARTITIONS 4;

Performance

Proviamo finalmente a fare qualche query e cerchiamo di capire cosa succede quando leggiamo i record da una tabella partizionata. Utilizzeremo il comando EXPLAIN (lo abbiamo spiegato in dettaglio nel numero 59 di Linux&C) che ci consente di sapere dal server un po’ di informazioni su come viene eseguita una query: quali indici saranno utilizzati, la stima di quante righe verrano lette, in che modo verrano risolti i join e altro ancora. Per lo scopo della nostra trattazione utilizzeremo una variante introdotta in MySQL 5.1 per avere qualche informazione in più in merito alle partizioni: EXPLAIN PARTITIONS. In questo modo il server ci dirà, oltre al resto, anche quali partizioni saranno utilizzate.
Quando si parla di performance è anche il caso di fare qualche misurazione, per dimostrare che i vantaggi promessi dalla teoria sono anche reali. Per fare, però, delle misurazioni attendibili ci serve una tabella di grandi dimensioni, non di svariati GB ma grande a sufficienza per apprezzare il tempo di esecuzione di una query. Nel riquadro 2 potete seguire passo passo l’esempio, riproducibile, in cui grazie ad una stored procedure costruiamo dapprima una tabella MyISAM non partizionata di 160MB di dati casuali. Assicuratevi di avere il doppio di tale spazio a disposizione sul vostro disco, dato che poi costruiremo l’analoga tabella partizionata in diversi modi e vedremo come variano i tempi di esecuzione di una stessa query: è abbastanza evidente che, usato nel modo corretto, il partitioning porta effettivamente a dei significativi vantaggi in termini di tempo di risposta.

Conclusioni

Il partitioning in MySQL 5.1, come abbiamo già avuto modo di dire, è probabilmente la più importante delle novità e consente di ottenere tempi di risposta, nell’ambito di tabelle di grande dimensione, di molto inferiori rispetto alle soluzioni possibili con le vecchie versioni.
Bisogna, però, stare attenti, un uso non corretto del partitioning non porta a nessun vantaggio, anzi, potrebbe anche peggiorare la situazione. Quindi, prima di iniziare ad utilizzare questa nuova ed interessante opportunità è necessario fare qualche prova (a partire magari dagli spunti offerti da questo articolo) e approfondire qualche dettaglio un po’ più spinoso facendo riferimento alla documentazione ufficiale.

Riquadro 1: Le altre novità di MySQL 5.1

Event scheduler: consente la creazione di eventi da eseguire ad intervalli regolari iniziando e finendo in determinati momenti. Ad un evento è associato del codice da eseguire, scritto con lo stesso linguaggio usato per descrivere stored procedure e trigger. In pratica, ciò che si può fare appoggiandosi al cron di sistema, è stato portato direttamente all’interno del DBMS.
Replicazione row-based: la replicazione finora funzionava inviando da una macchina master ad altre macchine slave le query di modifica dei dati. Ora è possibile replicare inviando dal master non solo gli statement SQL, ma anche i dati veri e propri in un formato binario. Questa nuova funzionalità risolve ad esempio certi problemi sorti in passato con la replicazione delle stored function. C’è anche la possibilità di gestire la replicazione in modo misto: invio agli slave sia le query SQL sia i valori dei record, il server sceglie il modo più opportuno a seconda del caso.
Plugin API: possibilità di caricare e disabilitare componenti aggiuntivi a runtime, senza riavviare il server. Al momento è una della novità ancora poco sviluppate, ma consente già di caricare un plugin apposito per il parsing full-text. Consentirà anche di abilitare e disabilitare a runtime storage engine di terze parti o autoprodotti, questa sarà sicuramente la cosa più interessante.
Log tabellari: c’è la possibilità di far scrivere alcuni dei file di log del server (lo slow_log e il general_log) in tabelle apposite del DBMS. In questo modo la consultazione di tali log diviene pià facile e flessibile visto che lo si può fare direttamente con interrogazioni SQL. Il monitoring del DB e il debugging delle applicazione risultano agevolati.
MySQL Cluster disk data: nelle vecchie versioni il Cluster teneva tutti i dati esclusivamente in memoria; questa caratteristica poteva divenire rapidamente limitante per il consumo eccessivo di RAM. La nuova versione invece consente di scrivere i dati delle tabelle anche su disco facendo così risparmiare la memoria. Il funzionamento non è dei più intuitivi al momento, ma è un bel passo avanti.
MySQL Cluster Replication: è possibile replicare un intero cluster su un altro cluster oppure su un altro DB anche se non è presente il cluster.
Funzioni XML: ci sono due nuove funzioni per leggere e manipolare abbastanza facilmente i campi nei quali sia stato inserito del codice XML. Le funzioni ExtractValue() e UpdateXML() sono state implementate con il pieno supporto di Xpath.

Riquadro 2: Le performance del partitioning

Scriviamo una stored procedure per creare due tabelle contenenti gli stessi dati casuali, una partizionata ed l’altra non partizionata.


mysql> DELIMITER //
mysql> CREATE PROCEDURE popola_tabella (IN numero_record INT)
-> BEGIN
-> DECLARE contatore INT DEFAULT 0;
-> DROP TABLE IF EXISTS utente_nopart;
-> DROP TABLE IF EXISTS utente_part;
-> CREATE TABLE utente_nopart(
-> id INT UNSIGNED,
-> nome VARCHAR(32) NOT NULL,
-> cognome VARCHAR(32) NOT NULL,
-> data_registrazione DATE NOT NULL);
-> CREATE TABLE utente_part(
-> id INT UNSIGNED,
-> nome VARCHAR(32) NOT NULL,
-> cognome VARCHAR(32) NOT NULL,
-> data_registrazione DATE NOT NULL)
-> PARTITION BY RANGE ( YEAR(data_registrazione) ) (
-> PARTITION p0 VALUES LESS THAN (2000),
-> PARTITION p1 VALUES LESS THAN (2001),
-> PARTITION p2 VALUES LESS THAN (2002),
-> PARTITION p3 VALUES LESS THAN (2003),
-> PARTITION p4 VALUES LESS THAN (2004),
-> PARTITION p5 VALUES LESS THAN (2005),
-> PARTITION p6 VALUES LESS THAN (2006),
-> PARTITION p7 VALUES LESS THAN (2007),
-> PARTITION p8 VALUES LESS THAN (MAXVALUE)
-> );
-> REPEAT
-> SET contatore=contatore+1;
-> INSERT INTO utente_nopart

-> VALUES(contatore, MD5(contatore), MD5(contatore),
MAKEDATE(ROUND(9*RAND()+1999),ROUND(365*RAND()+1)));
-> UNTIL contatore=numero_record
-> END REPEAT;
-> INSERT INTO utente_part SELECT * FROM utente_nopart;
-> END//
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;

Lanciamo la stored procedure e creiamo le due tabelle con due milioni di record ciascuna (il tempo di esecuzione dipende dalla potenza del vostro computer, ma non sorprendetevi di aspettare qualche minuto).


mysql> CALL popola_tabella(2000000);
Query OK, 2000000 rows affected, 2 warnings (2 min 32.72 sec)

Eseguiamo una semplice query di conteggio sulla tabella non partizionata, ad esempio contiamo gli utenti registrati al nostro servizio dall’inizio del 2007 ad oggi:
mysql> SELECT COUNT(*) FROM utente_nopart
WHERE data_registrazione BETWEEN ‘2007-01-01’ AND CURDATE();
| COUNT(*) |
| 252035 |

eseguiamo la stessa query su quella partizionata:


mysql> SELECT COUNT(*) FROM utente_part
WHERE data_registrazione BETWEEN '2007-01-01' AND CURDATE();
| COUNT(*) |
| 252035 |

il tempo di esecuzione si è ridotto dell’80%, da 1.73 a 0.30 secondi, non male. Analizziamo le due query con EXPLAIN ed EXPLAIN PARTITIONS per vedere il perché. Iniziamo con quella \sualla tabella non partizionata:


mysql> EXPLAIN SELECT COUNT(*) FROM utente_nopart
WHERE data_registrazione BETWEEN '2007-01-01' AND CURDATE()\G
id: 1
select_type: SIMPLE
table: utente_nopart
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 2000000
Extra: Using where

Come reso evidente da type: ALL viene fatto un full table scan, cioè viene letta l’intera tabella e vengono infatti analizzati due milioni di righe.
Analizziamo ora la query per la tabella partizionata:


mysql> EXPLAIN PARTITIONS SELECT COUNT(*) FROM utente_part
WHERE data_registrazione BETWEEN '2006-01-01' AND CURDATE()\G
id: 1
select_type: SIMPLE
[...]
table: utente_part
partitions: p8
type: ALL
rows: 333323

qui si vede invece che non vengono lette tutte le righe della tabella, ma solo quelle della partizione p8 per un totale di soli 333.323 record. Da questo motivo dipende la miglior performance. Eseguiamo ora la query seguente che non ha una condizione espressa sulla colonna usata per il partizionamento e vediamo in entrambe le tabelle il risultato:


mysql> SELECT COUNT(*) FROM utente_nopart
WHERE cognome BETWEEN 'a' AND 'b';
| COUNT(*) |
| 124431 |
1 row in set (1.35 sec)


mysql> SELECT COUNT(*) FROM utente_part
WHERE cognome BETWEEN 'a' AND 'b';
| COUNT(*) |
| 124431 |
1 row in set (1.41 sec)

I tempi sono simili, anzi, nel caso della tabella partizionata addirittura un po’ più alto.
Anche EXPLAIN PARTITIONS ci rende evidente che:


mysql> EXPLAIN PARTITIONS SELECT COUNT(*) FROM utente_part
WHERE cognome BETWEEN 'a' AND 'b'\G
id: 1
select_type: SIMPLE
table: utente_part
partitions: p0,p1,p2,p3,p4,p5,p6,p7,p8
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 2000000
Extra: Using where

sono lette tutte le partizioni, l’equivalente del full table scan.

Un tutorial per ottimizzare le query SQL su MySQL

Di Corrado Pandiani, pubblicato su Linux&C. n° 59.

Ottimizzare MySQL: come rendere più veloci le query SQL

L’obiettivo minimo che gli sviluppatori che utilizzano MySQL per le proprie applicazioni raggiungono con una certa dimestichezza è quello di saper scrivere query SQL per ottenere i risultati desiderati. In molti, però, si fermano a questo livello, rinunciando a sondare gli strani misteri che sono patrimonio di un buon database administrator: capita quindi che, al crescere della dimensione dei dati e della complessità delle interrogazioni da implementare, ci si trovi di fronte a problemi apparentemente irrisolvibili o ad applicazioni con tempi di risposta inaccettabili.
Non sempre la determinazione del collo di bottiglia è cosa immediata: spesso è decisamente più facile pensare all’acquisto di hardware più potente, o alla ricerca del parametro di configurazione che, se ben impostato, potrebbe consentire la risoluzione di ogni problema.
Il più delle volte le soluzioni da mettere in pratica non sono queste, o meglio, non lo sono in prima istanza: quello che va fatto è una analisi dello schema del database e delle query eseguite dal server.
Riscrivere qualche interrogazione, ottimizzandola, e sistemare qualche tabella è spesso – non sempre – il solo intervento necessario, e in pochi minuti si può addirittura sbloccare una situazione che sembrava ormai cronica.
MySQL è notoriamente un DBMS molto performante, ma non ci si può fidare alla cieca di questa (pur vera) certezza: una progettazione errata e, soprattutto, query scritte male (spesso conseguenza di un database non normalizzato), sono in grado di affossare le performance di qualsiasi server, è solo questione di tempo, dati e memoria.
MySQL mette a disposizione uno strumento prezioso di analisi, poco conosciuto dai non addetti ai lavori, la funzione EXPLAIN, che sarà l’argomento di questo articolo: ne analizzeremo il funzionamento e, con qualche esempio, vedremo quale sia la metodologia di analisi per ottimizzare il funzionamento delle query.
Questa conoscenza consentirà di restituire un po’ di cicli macchina al vostro server “impallato” e, molto probabilmente, di scongiurare nell’immediato l’acquisto di nuovo hardware, cosa che il vostro capo o il vostro cliente apprezzerà più di ogni altro aspetto della questione.

Ripassiamo gli indici

Gli indici saranno i principali protagonisti della nostra trattazione.
Un indice associato ad una tabella non è troppo diverso da quello che trovate all’inizio di un libro: senza di esso, rintracciare un paragrafo o un capitolo diventa assai difficile e c’è da scorrere tutte le pagine presenti. Se poi c’è da trovare tutte le occorrenze di una certa parola, la situazione diventa ancora più tragica in assenza di un indice analitico.
MySQL utilizza la stessa tecnica vista per il libro: se una tabella non ha indici associati, l’unico modo per ritrovare i record di interesse è eseguire l’operazione che prende il nome di “full table scan”, una lettura riga per riga di tutte le informazioni presenti.
Pensate cosa significhi in termine di I/O effettuare una lettura completa di svariati gigabyte solo per trovare qualche riga che soddisfi una ricerca.
Gli indici sono strutture che “puntano” alle informazioni presenti nella tabella, come una sorta di segnalibri: sono composti da una sequenza di nodi contenenti i riferimenti ai record e vengono utilizzati per velocizzare le ricerche all’interno della tabella stessa, laddove questo sia possibile; sono costruiti sui valori di una o più colonne, e creano corrispondenze tra certi valori delle colonne e le righe dove tali valori si trovano.
Nella metafora vista in precedenza, un nodo dell’indice conterrà il titolo di un capitolo o del paragrafo, e verrà tenuto il riferimento del numero di pagina ove tale informazione si trova.
In MySQL è possibile creare diversi tipi di indice:

  • PRIMARY KEY: è l’indice associato alla chiave primaria, non può contenere valori NULL né valori duplicati. Usato spesso in associazione con la direttiva auto_increment in caso di valori numerici unici.
  • UNIQUE: non ci possono essere valori duplicati, sono consentiti i valori NULL.
  • INDEX: ci possono essere valori duplicati e valori NULL.
  • FULLTEXT: indice specializzato per la ricerca di parole nei testi.

Oltre ad essere strutture generalmente molto più piccole rispetto alla tabella cui fanno riferimento, gli indici hanno un trattamento di riguardo da parte di MySQL: i nodi, comprensivi di valore e puntatore, vengono conservati in un buffer di memoria denominato key_buffer (qua e nel seguito si fa riferimento all’engine MyISAM), che contiene i nodi utilizzati più di recente e, compatibilmente con la memoria disponibile, può anche contenere tutti gli indici del database.
La sua dimensione è data dalla variabile key_buffer_size, impostabile a piacere nella sezione [mysqld] del file my.cnf, il file di configurazione di MySQL che si trova in /etc (o in /etc/mysql a seconda della distribuzione).
Il key_buffer è preferibile che abbia il valore più elevato possibile, più è grande maggiore è la probabilità che il nodo dell’indice richiesto sia già in memoria, compatibilmente però con la RAM a disposizione, cercando di evitare il pericolo di swap del sistema operativo che farebbe degradare le performance.
Le query SQL con condizioni di ricerca espresse su campi indicizzati possono trarre grandi vantaggi in termini di tempo di esecuzione: ci sarebbe ancora da dire molto sulla gestione degli indici da parte del database, ma per i nostri scopi può bastare.
Identificare le query da ottimizzare
Prima di usare EXPLAIN dobbiamo selezionare le query che devono essere oggetto della nostra analisi ma, fortunatamente, non tutte avranno bisogno di essere ottimizzate e potranno tranquillamente essere considerate trascurabili.
Per questo scopo ci serve il contenuto dello slow-log, un file di testo nel quale MySQL scrive tutte le query che vengono eseguite in un tempo superiore al valore impostato nel parametro long_query_time (che di default vale 10 secondi).
Associate ad ogni query vengono inoltre riportate informazioni di fondamentale importanza: data e ora, tempo di esecuzione espresso in secondi, numero di righe, tempo di lock, utente richiedente.
Non essendo lo slow-log attivo per default, dobbiamo abilitarlo inserendo il parametro log-slow-queries[=file_name] nella sezione [mysqld] del file my.cnf: in assenza della parte file_name, che ne può specificare un path e un nome alternativo, il file verrà creato nella directory dei dati e si chiamerà host_name-slow.log. Se non sappiamo quale sia la directory dei dati possiamo scoprirlo con il comando:


mysql> SHOW VARIABLES LIKE 'datadir';

Impostati i parametri in my.cnf sarà necessario il riavvio del server. Il database dovrà lavorare per un po’ di tempo affinché il file di log venga popolato. Per disporre di un contenuto significativo che dia una visione realistica del funzionamento del server occorre che lo slow-log copra diverse ore o, meglio ancora, diversi giorni: per una corretta selezione delle query candidate all’ottimizzazione è bene valutare il funzionamento del database su un arco temporale che copra tutte le variazioni possibili di carico e annoveri il funzionamento di tutte le applicazioni che ne fanno uso, in quanto uno slow-log parziale può portare ad analisi non complete e fuorvianti.
Dall’analisi delle query presente possiamo costruire una lista di interrogazioni da analizzare partendo dalle più lente e più frequenti, facendo molta attenzione però agli eventuali falsi positivi, le query cioè che sono presenti nel log pur non essendo scritte male o lente per loro natura.
Attenzione anche a tutti gli elementi esterni al database possono influenzare le perfomance e che possono rendere “lente” query che, in situazioni normali, non lo sarebbero state: un caso tipico è quello di un applicativo esterno che ha fatto un uso anomalo delle risorse di sistema (tempo macchina o disco) sottraendole agli altri processi, tra cui anche MySQL.
Altro caso assai frequente riguarda le query eseguite durante il backup del database: nel tempo necessario ad eseguire la copia dei dati è probabile che si generino delle slow query conseguente all’intenso I/O.
Altre query da escludere sono quelle poco frequenti, quelle eseguite per errore e quelle eseguite da un amministratore per onerose operazioni di modifica alle tabelle o complesse ricerche. E’ comunque necessario fare uso di un po’ di buon senso: se è vero che in termini generali tali query possono essere trascurate, è altrettanto vero che se una certa query eseguita una volta ogni giorno impiega un’ora non può essere tralasciata, soprattutto se in tale lasso di tempo tiene bloccate risorse (tabelle) per i thread concorrenti.
Se sul server è attiva l’opzione log_queries_not_using_indexes lo slow-log conterrà anche tutte le query che non hanno fatto uso di alcun indice, indipendentemente dalla velocità di esecuzione: questo può essere utile per individuare molte query eseguite in un tempo relativamente basso ma che comunque sarebbero migliorabili.
Se lo slow-log è di piccole dimensioni può essere controllato manualmente, ma nella maggior parte dei casi è sicuramente più comodo l’utilizzo di un programma presente in tutte le installazioni di MySQL, mysqldumpslow, che esegue l’analisi dello slow-log, producendo in output un documento di sintesi in cui le query simili sono raggruppate in base al tempo medio di esecuzione e la frequenza, ordinate a partire dalla più lenta. I parametri numerici e testuali presenti nelle query sono rispettivamente sostituiti con i simboli generici N ed S.
I file di log non sono consultabili da tutti gli utenti e, a seconda delle distribuzioni e della tipologia di installazione, è probabile che ad essi abbia accesso solo l’utente di root oppure l’utente con cui MySQL è in esecuzione (e il gruppo relativo), e questo comporta l’impossibilità dell’analisi in diverse situazioni: il caso più tipico è chi utilizza un database su una piattaforma in hosting, senza avere accesso fisicamente alla shell e alle impostazioni del server.
In tali circostanze o si riesce a replicare localmente l’intera installazione effettuando una analisi in ambiente di pre-produzione (che dovrebbe essere sempre presente, non solo per i test prestazionali), o si è costretti ad eseguire sull’applicazione la misura delle performance, magari scrivendo il tempo impiegato in un file di log che può essere consultato offline.

La funzione EXPLAIN

EXPLAIN viene utilizzata per consultare il “query optimizer”, la sezione del server che effettua le valutazioni su come rendere più veloce l’interrogazione che viene richiesta al database: è questa sezione che decide se un indice dovrà essere utilizzato, l’ordine di verifica delle condizioni ecc.
In pratica, interrogando il query optimizer si chiede a MySQL come intende sviluppare la query.
Le informazioni che fornisce EXPLAIN sono utili per molti aspetti:

  • forniscono indizi circa l’opportunità di aggiunta di alcuni indici alle tabelle;
  • se una tabella ha già degli indici utilizzati, l’output del comando aiuta a capire come vengono utilizzati dal motore;
  • se gli indici esistono ma non vengono utilizzati dal query optimizer, aiuta a scrivere meglio il codice SQL affinché la query venga eseguita beneficiando della presenza dell’indice.

L’uso di EXPLAIN è di estrema importanza per studiare i join che, se male utilizzati, hanno lo svantaggio di poter incrementare a dismisura il carico di lavoro richiesto al server: infatti, se una query che interroga una tabella di 1000 righe è scritta male, nel caso peggiore il server dovrà leggere al massimo 1000 righe; se invece viene effettuato un join di due tabelle di 1000 righe ciascuna, il caso peggiore comporta l’esame di tutte le possibili combinazioni del prodotto cartesiano tra le due tabelle, ossia 1.000.000 di righe!
EXPLAIN può aiutare a riscrivere meglio le query o modificare le tabelle in modo tale che il server risolva il join nel modo meno oneroso possibile, riducendo il numero di combinazioni da esaminare.
EXPLAIN produce una riga di output per ogni tabella referenziata dalla query. L’ordine di visualizzazione delle righe è importante: indica la sequenza che MySQL utilizzerà per considerare le tabelle nella risoluzione dei vincoli di join, indipendentemente da come sono scritte nella parte FROM.
EXPLAIN consente di analizzare qualsiasi query SELECT, ma può essere utilizzato indirettamente anche per UPDATE e DELETE scrivendo una generica SELECT * con le stesse condizioni di FROM e WHERE: solitamente le condizioni usate nelle query di modifica dei dati sono meno complesse di quelle usate in quelle di selezione, il che rende questa operazione non molto frequente.
Primo esempio: utilizzo corretto degli indici
Per il nostro primo esempio utilizziamo il database Sakila di una ipotetica videoteca, liberamente scaricabile da
http://downloads.mysql.com/docs/sakila-db.tar.gz
Delle 22 tabelle presenti ne useremo per i nostri esempi solo 3.

  • film: elenco dei film a catalogo comprensivi di titolo, genere, durata a altre informazioni
  • actor: elenco di attori (nomi di fantasia)
  • film_actor: tabella che di collegamento tra gli attori e i film in cui hanno recitato

Le tabelle sono già ottimizzate a dovere, ma per capire il funzionamento è necessario crearne di nuove non ottimizzate, senza indici:


mysql> CREATE TABLE lista_attori SELECT * FROM actor;
mysql> CREATE TABLE lista_film SELECT * FROM film;
mysql> CREATE TABLE lista_film_attori
SELECT * FROM film_actor;

L’esecuzione di CREATE TABLE … SELECT … consente infatti di copiare tutti i dati di una tabella in una nuova ricreando gli stessi campi ma senza indici.
Eseguiamo una query per trovare i nomi degli attori e i titoli dei film in cui hanno recitato la cui durata è maggiore di tre ore.


mysql> SELECT first_name, last_name, title
-> FROM lista_film, lista_attori, lista_film_attori
-> WHERE lista_film.film_id=lista_film_attori.film_id
-> AND lista_attori.actor_id=lista_film_attori.actor_id
-> AND length > 180;

La stessa query si può scrivere con la sintassi JOIN estesa, in maniera del tutto equivalente anche in termini di performance:


SELECT first_name, last_name, title
FROM
lista_film
INNER JOIN lista_film_attori ON
lista_film.film_id=lista_film_attori.film_id
INNER JOIN lista_attori ON
lista_attori.actor_id=lista_film_attori.actor_id
WHERE
length > 180;

Osserviamo il risultato che restituisce:


| first_name | last_name | title |
| PENELOPE | GUINESS | KING EVOLUTION |
| ED | CHASE | YOUNG LANGUAGE |
...
| MATTHEW | CARREY | WORST BANGER |
200 rows in set (29.02 sec)

lc59_ottimizzazione1

Notate come il tempo di esecuzione sia particolarmente alto. La velocità di esecuzione dipende anche dalla potenza di calcolo del computer, quindi potreste ottenere un risultato un po’ diverso, ma probabilmente dello stesso ordine di grandezza. Vediamo allora come migliorare la situazione.
Proviamo a fare un EXPLAIN della query appena eseguita, il cui output è presente in figura 2 nel primo passaggio:


mysql> EXPLAIN SELECT first_name, last_name, title
-> FROM lista_film, lista_attori, lista_film_attori
-> WERE lista_film.film_id=lista_film_attori.film_id
-> AND lista_attori.actor_id=lista_film_attori.actor_id
-> AND length > 180;

Notiamo anzitutto che i campi type hanno il valore ALL per tutte le tabelle. Ciò significa che per ognuna viene fatto un full scan per ogni combinazione di righe dalle tabelle precedenti. Per conoscere il numero di combinazioni totali è sufficiente moltiplicare tra loro i valori riportati in rows.


mysql> select 200*1000*5462 as prodotto_cartesiano;
| prodotto_cartesiano |
| 1092400000 |

MySQL deve costruirsi oltre 1 miliardo di possibili combinazioni, controllarle una per una per individuare alla fine le 200 righe del risultato che cerchiamo: ecco spiegata la ragione della estrema lentezza della query.
Effettuiamo una prima ottimizzazione creando due indici per consentire di risolvere più facilmente la prima condizioni di join presente nella query (notate come la forma estesa semplifichi la lettura alla ricerca delle necessità di ottimizzazione):


mysql> ALTER TABLE lista_film ADD INDEX(film_id);
Query OK, 1000 rows affected (0.07 sec)
Records: 1000 Duplicates: 0 Warnings: 0


mysql> ALTER TABLE lista_film_attori ADD INDEX(film_id);
Query OK, 5462 rows affected (0.01 sec)
Records: 5462 Duplicates: 0 Warnings: 0

Eseguiamo nuovamente EXPLAIN, il cui output è presente sempre in figura 2 ma nel secondo passaggio.
Notiamo che la situazione è considerevolmente migliorata per la tabella lista_film_attori per la quale è possibile utilizzare l’indice costruito su film_id come ci dice il campo key. Per ogni combinazione di righe dalle tabelle precedenti la tabella in questione mette in gioco 5 righe (rows) e risolve il join con lista_film usando la colonna film_id (ref).


mysql> select 200*1000*5 as prodotto_cartesiano;
| prodotto_cartesiano |
| 1000000 |

Abbiamo ridotto il numero delle combinazioni di tre ordini di grandezza, da oltre un miliardo a un milione. La query eseguita dopo questa prima ottimizzazione impiega oltre un decimo del tempo. Proseguiamo con l’ottimizzazione creando gli indici per agevolare l’altra condizione di join.


mysql> ALTER TABLE lista_attori ADD INDEX(actor_id);
Query OK, 200 rows affected (0.00 sec)
Records: 200 Duplicates: 0 Warnings: 0


mysql> ALTER TABLE lista_film_attori ADD INDEX(actor_id);
Query OK, 5462 rows affected (0.02 sec)
Records: 5462 Duplicates: 0 Warnings: 0

Nel terzo passaggio vediamo come è ulteriormente variato l’output di EXPLAIN.
Siamo ora in una situazione già accettabile: le combinazioni sono scese a 5000 (1000*5*1) con due tabelle su tre in grado di sfruttare un indice.
Notiamo anche che l’ordine delle tabelle è variato e quanto mostrato da EXPLAIN è l’esatta sequenza con cui “query optimizer” aprirà le tabelle e risolverà i join. L’ordine scritto nel FROM non ha alcuna rilevanza: la scelta della sequenza più opportuna è operata in base a quella che è in grado di fornire il risultato migliore.
In questo caso vi è la necessità di effettuare il full scan di lista_film, e viene eseguito come prima operazione per evitare di doverlo ripetere più volte se fosse posticipato nella esecuzione della query, ragion per cui lista_film è la prima tabella considerata.
La necessità di effettuare il full scan è data dalla presenza della condizione sul campo length (length > 180) che non è un campo indicizzato. Proviamo allora a definire un indice anche per esso e vediamo cosa succede.


mysql> ALTER TABLE lista_film ADD INDEX(length);
Query OK, 1000 rows affected (0.02 sec)
Records: 1000 Duplicates: 0 Warnings: 0

L’output della nuova EXPLAIN è indicato come quarto passaggio. Ora anche la prima tabella può sfruttare l’indice e il valore range del campo ref indica che solo un certo intervallo dell’indice viene letto, non tutto.
Calcoliamo la complessità finale della query:


mysql> select 46*5*1 as prodotto_cartesiano;
| prodotto_cartesiano |
| 230 |

Ora eseguiamola nuovamente dopo le ottimizzazioni:


mysql> SELECT first_name, last_name, title
-> FROM lista_film, lista_attori, lista_film_attori
-> WHERE lista_film.film_id=lista_film_attori.film_id
-> AND lista_attori.actor_id=lista_film_attori.actor_id
-> AND length > 180;
| first_name | last_name | title |
| PENELOPE | GUINESS | KING EVOLUTION |
| ED | CHASE | YOUNG LANGUAGE |
...
| MATTHEW | CARREY | WORST BANGER |
200 rows in set (00.01 sec)

Il tempo di esecuzione si è decisamente ridotto!

Un nuovo esempio: riscrivere una query

Supponiamo che siano state inserite delle durate errate nel campo length della tabella film, e il valore corretto sarebbe il 10% in più rispetto ai valori presenti nel database.
Prima di modificare il campo, però, cerchiamo quali sarebbero i film la cui durata sarà superiore alle 3 ore (180 minuti).
Creiamo un indice sul campo length e analizziamo una semplice query:


mysql> ALTER TABLE film ADD INDEX(length);
Query OK, 1000 rows affected (0.14 sec)
Records: 1000 Duplicates: 0 Warnings: 0


mysql> EXPLAIN SELECT title FROM film
WHERE 1.1*length > 180\G
********************* 1. row *********************
id: 1
select_type: SIMPLE
table: film
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 927
Extra: Using where

C’è da chiedersi perché non venga utilizzato l’indice definito sulla colonna length. La spiegazione è oltremodo semplice: quando una condizione è espressa in termini di una funzione o di una operazione algebrica applicata ad un campo indicizzato, MySQL non può sfruttare l’indice in quanto non conosce a priori il valore assunto dall’espressione o dalla funzione. Il valore deve quindi essere calcolato riga per riga, obbligando un full scan della tabella, come EXPLAIN correttamente indica.


mysql> EXPLAIN SELECT title FROM film
WHERE length > 180/1.1\G
********************* 1. row *********************
id: 1
select_type: SIMPLE
table: film
type: range
possible_keys: length,length_2
key: length
key_len: 3
ref: NULL
rows: 155
Extra: Using where

Provando a scrivere la query con una condizione espressa in un modo diverso ma equivalente, si verifica come l’indice venga correttamente utilizzato.
Forzare l’ordine delle tabelle
Nella maggior parte dei casi le scelte effettuare dal query optimizer per quanto riguarda l’ordine di lettura delle tabelle sono quelle ottimali, ma può però capitare che non lo siano e si voglia costringere MySQL ad operare una scelta diversa: è quindi sufficiente aggiungere alla prima SELECT il modificatore STRAIGHT_JOIN, che farà in modo che l’ordine considerato sarà quello specificato in FROM.
Fortunatamente non capiterà spesso di andare contro le scelte di MySQL, ma come esercizio si può provare a forzare l’ordine delle tabelle per verificare come cambino le performance di una query.

Aggiornare le statistiche

Abbiamo citato il fatto che il valore di rows sia una stima calcolata in base alle statistiche sulla distribuzione degli indici e non il numero reale delle righe selezionate. Non è scopo di questo articolo spiegare gli internal di questa valutazione, ma ciò chi ci preme sottolineare è che i valori utilizzati per le ottimizzazioni devono essere il più aggiornati possibile: è quindi consigliabile eseguire di tanto in tanto il comando


mysql> ANALYZE TABLE nome_tabella;

che ha il compito specifico di effettuare un refresh delle informazioni necessarie.
Non esiste una regola per decidere la frequenza dell’esecuzione, molto dipende dalla quantità di modifiche dei campi indicizzati. Per sicurezza è utile eseguirlo su tutte le tabelle con cadenza giornaliera o settimanale a seconda dei casi (basta affidarsi a cron) e comunque eseguirlo sistematicamente tutte le volte che si fanno modifiche di massa di valori associati ad indice, o dopo aver cancellato grosse parti di una tabella. Anche il comando che segue può servire allo scopo:


mysql> OPTIMIZE TABLE nome_tabella;

Anche OPTIMIZE TABLE aggiorna le statistiche di distribuzione degli indici, ma in più esegue altre operazioni (ad esempio compattare le tabelle per recuperare lo spazio vuoto) e potrebbe risultare più lento.

Conclusioni

Vi abbiamo mostrato come identificare le query più lente sfruttando lo slow-log: in seguito l’uso di EXPLAIN ha mostrato come si possano analizzare tali query al fine di creare gli indici necessari per ridurre il numero di combinazioni di righe e, nel caso in cui tali indici non venissero utilizzati, modificare la struttura della query per permettere a MySQL una migliore ottimizzazione della interrogazione.
Tutto ciò a vantaggio dei tempi di risposta e del fatto che avrete un DBMS in grado di poter assolvere a nuovi compiti.
A volte purtroppo capita anche il caso in cui, nonostante tutte le possibili ottimizzazioni, il sistema risulti ancora troppo lento. Solo in tal caso non resta altro da fare che potenziare l’hardware o pensare a soluzioni basate sulla replicazione o MySQL Cluster a seconda dei casi, ma qua il discorso diventa sicuramente più complesso e sarà oggetto di ulteriori articoli nei prossimi numeri.

Riquadro 1: I campi di una EXPLAIN SELECT

id: valore numerico progressivo che indica la SELECT a cui si fa riferimento; se non sono presenti query annidate o UNION varrà sempre 1.
select_type: categorizza il tipo di SELECT; se non si hanno query annidate o UNION varrà sempre SIMPLE.
table: è il nome della tabella cui le informazioni della riga fanno riferimento.
type: indica il tipo di join. Il suo valore è una misura dell’efficienza con cui MySQL risolve una condizione di join (vedi riquadro 2 per tutti i possibili valori).
possible_keys: indica quali sono gli indici della tabella che MySQL può considerare per identificare la righe che soddisfano la query. Può essere una lista di uno o più valori, o può valere NULL se non ci sono indici o se nessuno degli indici può essere usato.
key: indica il nome dell’indice che “query optimizer” ha deciso di utilizzare. Se il valore è NULL vuol dire che nessun indice è stato usato; questo capita ovviamente se non vi sono indici oppure se optimizer ritiene che uno scan della tabella sia più veloce (tipico di quando il numero delle righe selezionate è relativamente grande rispetto al numero totale di righe della tabella)
key_len: indica quanti byte dell’indice sono usati. E’ un’informazione utile solo nei casi in cui vi sia un indice multiplo, costruito cioè con l’unione di più colonne. Per gli scopi dell’articolo non ci interessa approfondirlo ulteriormente.
ref: indica quali colonne della tabella precedente sono usate per risolvere la condizione di join con la tabella attuale. Vale NULL nel caso della prima tabella.
rows: è un valore numerico che rappresenta il numero di righe messe in gioco dalla tabella. Attenzione però, si tratta solo di una stima calcolata da optimizer in base alle statistiche interne sugli indici, non è il numero delle righe realmente selezionate. Per fortuna ciò che conta non è il valore puntuale ma l’ordine di grandezza di tale numero, che nella quasi totalità dei casi è attendibile. Il valore “vero” lo si può ottenere solo eseguendo la query, cosa che EXPLAIN non fa.
Extra: fornisce informazioni addizionali sul join (vedi riquadro 3 per tutti i possibili valori).

Riquadro 2: I valori del campo Type presente nella EXPLAIN

system: la tabella ha esattamente una riga.
const: la tabella ha una sola riga che soddisfa alle condizioni di join. E’ simile a system ma con la differenza che la tabella può avere molte altre righe.
eq_ref: è letta una sola riga dalla tabella corrente per ogni combinazione di righe dalle tabelle precedenti. Tipico dei join in cui MySQL può utilizzare una primary key per identificare le righe di una tabella.
ref: sono lette diverse righe per ogni combinazione di righe dalle tabelle precedenti. Simile a eq_ref, ma può capitare quando viene usato un indice multiplo o quando non viene utilizzato un indice per intero (solo la parte sinistra).
ref_or_null: simile a ref, ma in più MySQL deve cercare righe contenenti NULL.
range: l’indice è usato per selezionare le righe che cadono in un certo intervallo di valori. Tipico di quando vengono usate condizioni di disuguaglianza, ad esempio id<10.
index: MySQL esegue un full scan dell’indice. Non è un caso piacevole ma sempre meglio di un full scan della tabella. Un indice è più veloce da leggere dei dati reali in quanto è ordinato, solitamente più piccolo dei dati e spesso si trova per buona parte in memoria.
ALL: MySQL esegue un full scan della tabella da disco. E’ la situazione peggiore, soprattutto se si riferisce ad una tabella che non sia la prima: deve essere eseguito il full scan per ogni combinazione di righe dalle tabelle precedenti e le performance peggiorano esponenzialmente. Assolutamente da evitare, specialmente quando la dimensione delle tabelle non è trascurabile.

Riquadro 3: I valori del campo Extra presente nella EXPLAIN

Ecco i valori possibili del campo Extra: quelli con un + identificano valori “buoni”, quelli con – valori “cattivi”.

  • +Using index: MySQL può ottimizzare la query leggendo i valori dall’indice senza dover leggere i corrispondenti dati dalla tabella fisica. Questa ottimizzazione è possibile quando ad esempio vengono selezionate solo colonne che sono indicizzate.
  • +Where used: MySQL usa le condizioni espresse in WHERE per identificare le righe che soddisfano la query. Senza tali condizioni si sarebbero ottenute tutte le righe della tabella.
  • +Distinct: MySQL legge una singola riga dalla tabella per ogni combinazione di righe dalle tabelle precedenti.
  • +Not exists: MySQL riesce ad ottimizzare una condizione di LEFT JOIN non considerando alcune righe.
  • -Using filesort: Le righe che soddisfano la query devono essere ordinate. E’ un passo ulteriore di computazione.
  • -Using temporary: Deve essere creata una tabella temporanea per poter processare la query.
  • -Range checked for each record: MySQL non può determinare in anticipo quale indice utilizzare. Per ogni combinazione di righe dalle tabelle precedenti controlla gli indici della tabella corrente per cercare il migliore da utilizzare. Non è una buona situazione, ma meglio che non usare gli indici del tutto.

Supporto completo (outsourcing) dei database MySQL e relativi server

Il servizio di outsourcing di database di Stardata è un impegno completo nella cura, amministrazione, monitoraggio ed assistenza di database MySQL.
Stardata ha una lunga e consolidata esperienza nel trattamento dati, con diversi clienti di classe Enterprise e può liberare le aziende dall’onerosa incombenza di gestire i database MySQL.
Il nostro servizio di outsourcing dei database è una soluzione sicura per i problemi di gestione e manutenzione dei database.
Il servizio di Stardata si applica alla cura di database singoli e replicazioni complesse, instaurando sistemi di monitoraggio e controllo per garantire una piena efficienza dei database continua nel tempo.
Abbiamo soluzioni personalizzate per ogni tipo di azienda. Contattateci!

Analisi delle prestazioni e tuning del database

Comprende una analisi dell’installazione e delle ottimizzazioni del sistema operativo e del server. Seguono una analisi della struttura dati, con normalizzazione (quando occorra) ed una analisi delle query più pesanti o più frequenti per migliorarne le prestazioni.
La durata dipende dalla complessità della struttura dati e può variare da uno a cinque giorni: oltre questo termine, vengono rilasciate le linee guida ed il cliente potrà – se vorrà – continuare l’analisi in maniera indipendente oppure continuare con il supporto di StarData s.r.l.

Un esempio di questa proposta:
La società XYZ gestisce un portale web ad alto traffico: si lamenta del fatto che le prestazioni del DB, all’aumentare del numero di record, sono peggiorate in maniera considerevole.
Anziché migrare su hardware più potente – che non serve risolve nel breve/medio periodo nel tempo, ma solo nel brevissimo, viene commissionato uno studio che analizza la configurazione del server, configurazione del database e dei parametri ad esso associati (spazio per la cache, memoria riservata ecc.), analisi delle query lente, ecc. suggerendo le tecniche migliori per poter incrementare la velocità di accesso ai dati.

PHP 5.3 su Red Hat Enterprise Linux (RHEL) 4

Un cliente che utilizza ancora RHEL 4 ci ha chiesto di predisporre il server web per ospitare, oltre all’applicazione attuale, anche una nuova applicazione basata su Drupal 7.x e quindi PHP 5.3.
La necessità di supportare contemporaneamente PHP 4.3 (utilizzato dalla web application attuale) e 5.3 (per Drupal) ci ha imposto delle notevoli restrizioni: non era possibile installare i pacchetti dai repository legacy esistenti perché avrebbero sovrascritto le precedenti versioni, quindi le possibilità erano due: ricompilare Apache e PHP, oppure estrarre il contenuto dei pacchetti esistenti e creare un’installazione parallela che sfruttasse quanto più possibile il sistema già installato, divergendo solo ove necessario. Abbiamo optato per questa seconda soluzione.
Non disponendo il cliente di un ambiente di test, la prima mossa è stata creare una virtual machine CentOS 4 i386 su cui effettuare tutte le prove del caso. Abbiamo installato un sistema minimale ed aggiunto i pacchetti php, php-gd e php-mysql che sapevamo presenti in staging e produzione.
A questo punto abbiamo scaricato sulla nostra VM il repo file di un repository che disponeva dei pacchetti PHP 5.3 per RHEL 4 e l’abbiamo utilizzato per ottenere la lista degli rpm da scaricare:

# wget http://rpms.famillecollet.com/enterprise/remi.repo
# mv remi.repo /etc/yum.repos.d/
# wget rpms.famillecollet.com/RPM-GPG-KEY-remi
# rpm --import RPM-GPG-KEY-remi
# yum --enablerepo=remi install php php-gd php-mysql php-pdo
[...]

Abbiamo preso nota dei pacchetti che venivano installati dal repository e li abbiamo scaricati in una directory per estrarne i contenuti:

# mkdir php5-rpms
# cd php5-rpms
# wget http://repo.famillecollet.com/enterprise/4 [...]
# mkdir contents
# cd contents
# for file in ../*.rpm ; do rpm2cpio $file | cpio -id ; done
[...]
# ls
etc/  lib/  usr/

Disponevamo dei binari e dei file di configurazione base necessari, che altro ci serviva?

  • Uno script di init per la seconda istanza di Apache e relativo file di configurazione in /etc/sysconfig/
  • Una ServerRoot separata per la seconda istanza di Apache in cui mettere tutti i file di configurazione
  • Un php.ini e una directory php.d separate per la configurazione di PHP
  • Una directory separata per le estensioni PHP in modo che non sovrascrivessero le vecchie

Partiamo impostando un riferimento alla directory in cui abbiamo estratto gli RPM, poi facciamo delle copie dei file attuali ed aggiorniamo la ServerRoot di Apache con i file estratti e ci assicuriamo che la directory dei log sia diversa da quella dell’istanza di default:

# FILES_REPO=/root/php5-rpms/contents
# cp /etc/init.d/httpd /etc/init.d/httpd-php53
# cp /etc/sysconfig/httpd /etc/sysconfig/httpd-php53
# cp -a /etc/httpd /etc/httpd-php53
# cp -a ${FILES_REPO}/etc/httpd/* /etc/httpd-php53/
# mkdir -p /var/log/httpd-php53
# chmod 700 /var/log/httpd-php53
# cd /etc/httpd-php53
# rm logs
# ln -s ../../var/log/httpd-php53 logs

Poi è la volta di PHP. Copiamo il modulo Apache, i relativi file di configurazione (in una nuova directory creata ad-hoc) ed estensioni (anch’esse in una nuova directory) e qualche binario che non dovrebbe sovrascrivere quanto abbiamo già installato:

# cp ${FILES_REPO}/usr/lib/httpd/modules/libphp5.so /usr/lib/httpd/modules/
# mkdir -p /etc/php5
# cp -a ${FILES_REPO}/etc/php.ini ${FILES_REPO}/etc/php.d /etc/php5/
# cp -a ${FILES_REPO}/usr/lib/php /usr/lib/php5
# cp -i ${FILES_REPO}/usr/bin/phar* /usr/bin/
# cp -i ${FILES_REPO}/usr/bin/php-cgi ${FILES_REPO}/usr/bin/phpize /usr/bin/

Manca poco, dobbiamo aggiungere un paio di librerie necessarie ai moduli.

# cp ${FILES_REPO}/usr/lib/libedit.so.0.0.27 /usr/lib/
# cd /usr/lib
# ln -s libedit.so.0.0.27 libedit.so.0

# cp -i ${FILES_REPO}/usr/lib/mysql/libmysqlclient.so.18.0.0 /usr/lib/mysql/
# cd /usr/lib/mysql
# ln -s libmysqlclient.so.18.0.0 libmysqlclient.so.18

# ldconfig

Gli ingredienti ci sono tutti, dobbiamo solo farli “parlare” tra loro. La prima cosa che modificheremo è lo script di init (riporto solo le parti in cui è stato modificato rispetto al default):

if [ -f /etc/sysconfig/httpd ]; then
        . /etc/sysconfig/httpd-php53
fi
[...]
prog=httpd-php53
pidfile=${PIDFILE-/var/run/httpd-php53.pid}
lockfile=${LOCKFILE-/var/lock/subsys/httpd-php53}
[...]
start() {
        echo -n $"Starting $prog: "
        check13 || exit 1
        LANG=$HTTPD_LANG PHP_INI_SCAN_DIR=/etc/php5/php.d daemon $httpd $OPTIONS
[...]

Come vedete le righe modificate sono veramente poche. Abbiamo cambiato il nome del file di configurazione da includere, fatto in modo di creare nomi univoci per il pidfile ed il lockfile e poi abbiamo modificato la linea di comando per includere una variabile che specifica a PHP dove reperire i file .ini relativi alle estensioni.

Nel file di configurazione /etc/sysconfig/httpd-php53 abbiamo cambiato solo le opzioni in modo da specificare una ServerRoot diversa:

OPTIONS="-d /etc/httpd-php53"

A questo punto siamo passati a configurare Apache, prima il file /etc/httpd-php53/conf/httpd.conf cambiando solo ServerRoot e PidFile in modo coerente con quanto avevamo fatto nello script di init e nel sysconfig:

ServerRoot "/etc/httpd-php53"
[...]
PidFile run/httpd-php53.pid

Poi siamo passati a fare un ritocco alla configurazione di PHP in /etc/httpd-php53/conf.d/php.conf, aggiungendo la direttiva PHPINIDir che specifica dove reperire il php.ini:

LoadModule php5_module modules/libphp5.so
PHPINIDir /etc/php5/

Il passo successivo riguarda proprio il php.ini (quello nuovo che abbiamo copiato in /etc/php5/php.ini), in cui dovremo cambiare la direttiva extension_dir:

extension_dir = "/usr/lib/php5/modules/"

A questo punto creiamo uno script php di test e riavviamo il servizio per verificare che tutto sia a posto:

# echo '<?php phpinfo() ?>' > /var/www/html/stardata-test-php53.php
# service httpd-php53 restart
# curl http://localhost/stardata-test-php53.php
[...]

Nell’output dovreste vedere che la versione di PHP è la 5.3 e le estensioni vengono caricate da /usr/lib/php5

Soluzione per errori 503 “Service temporarily unavailable” su Apache

Un nostro cliente aveva un problema con Apache: al riavvio della loro web application (che gira su un application server separato), Apache sembrava “addormentarsi” per circa un minuto, restituendo una pagina d’errore 503 “Service temporarily unavailable”.
Controllando i log, abbiamo trovato la causa del problema:

[Thu Oct 11 09:35:18 2012] [error] (111)Connection refused: proxy: HTTP: attempt to connect to 192.168.0.10:48880 (192.168.0.10) failed
[Thu Oct 11 09:35:18 2012] [error] ap_proxy_connect_backend disabling worker for (192.168.0.10)
[Thu Oct 11 09:35:19 2012] [error] proxy: HTTP: disabled connection for (192.168.0.10)
[...]

Il modulo proxy di Apache, vedendo che il servizio era down, bloccava tutte le richieste per un minuto. Per risolvere è bastato aggiungere l’opzione retry=0 alla direttiva ProxyPass nel file di configurazione:

ProxyPass / http://192.168.0.10:48880/ retry=0 timeout=5
ProxyPassReverse / http://192.168.0.10:48880/

Per maggiori informazioni si faccia riferimento alla documentazione di Apache.

Backup incrementali su un server RSYNC centralizzato

Una delle soluzioni più storicamente affidabili per effettuare backup incrementali in una Intranet è costituita dall’utilizzo di server RSYNC, sia su piattaforma Linux che Windows. In questa script d’esempio si presuppone di avere un server RSYNC già funzionante, protetto da password di accesso, verso il quale effettuare un backup incrementale settimanale.

Per prima cosa si definirà la directory di cui effettuare il backup, ad esempio la home dell’utente:

SRC_DIR=/home/$USER

Successivamente si specificherà il file che conterrà eventuali file o directory da escludere (anche mediante wildcard), il nome del server centrale di backup e quindi la relativa password di accesso, in questo modo:

EXCLUDES=/path/to/exclude_file
BACKUP_SERVER=server_name
export RSYNC_PASSWORD=server_password

A questo punto si sceglieranno i nomi delle directory di destinazione (è da notare come il comando date in combinazione con il flag A restituirà il nome del giorno della settimana per esteso) e le opzioni di backup come illustrato di seguito:

DST_DIR=`date +%A`
OPTS="--force --ignore-errors --delete-excluded --exclude-from=$EXCLUDES --delete --backup --backup-dir=/$DST_DIR -a"

Prima di inviare il comando di backup vero e proprio, un’ulteriore serie di comandi verrà utilizzata per ripulire i backup incrementali relativi al giorno corrente della settimana precedente (se esistente) in questo modo:

mkdir $HOME/empty_dir
rsync --delete -a $HOME/empty_dir/ $BACKUP_SERVER::$USER/$DST_DIR/
rmdir $HOME/empty_dir

In pratica verrà creata localmente (e quindi alla fine rimossa) una directory vuota sulla base della quale il contenuto del giorno corrente verrà aggiornato (di conseguenza, essendo la directory vuota ed utilizzando il flag –delete, ciò significa che a tutti gli effetti la directory di destinazione verrà ripulita).

Finalmente siamo pronti ad avviare il backup con il comando rsync come specificato di seguito:

rsync $OPTS $SRC_DIR $BACKUP_SERVER::$USER/current

Al termine dell’esecuzione (e della settimana) si avrà, nel server centralizzato di backup, rispettivamente per ogni utente, una directory denominata current/ che conterrà il backup completo, mentre una directory per ogni giorno della settimana conterrà il relativo backup incrementale.

Creazione di archivi TAR cifrati

Sicuramente ognuno di noi avrà utilizzato per i propri backup e restore il comando tar(1) nelle sue più diverse forme, ad esempio con o senza compressione (flag –z). Ma quanti di voi erano a conoscenza di come fosse semplice generare degli archivi cifrati?

Per ottenere ciò ci viene in aiuto il pacchetto OpenSSL, ovvero il toolkit crittografico che implementa i protocolli Secure Socket Layer (SSL, versioni 2 e 3) e Transport Layer Security (TLS, versione 1). In particolare, openssl(1) è il relativo comando che implementa le diverse funzioni crittografiche della libreria crypto di OpenSSL dalla shell.
Detto ciò, è quindi possibile creare un archivio TAR cifrato (relativamente alla directory DIR) in questo modo:

# tar -zcvf - DIR |openssl des3 -salt -k password |dd of=DIR.3des

dove, in particolare, viene previsto l’utilizzo dello standard di cifratura Triple-DES e la scrittura del file cifrato avviene mediante dd(1). Analogamente, sarà possibile estrarre il nostro archivio segreto mediante la pipeline illustrata di seguito:

# dd if=DIR.3des |openssl des3 -d -k password |tar -zxvf -

Ovviamente sempre a condizione di ricordarsi la password ;-).

Sincronizzare il sistema con HTPDate

Illustriamo in quest’esempio un modo alternativo a NTP (Network Time Protocol) per sincronizzare il nostro sistema.
Questa diversa modalità di operare potrebbe esserci utile perché ci potremmo trovare, tipicamente in presenza di reti Corporate o comunque rigidamente filtrate, a dover sincronizzare Linux Box che consentono il traffico da e verso la nostra organizzazione solo mediante la porta 80, ovvero HTTP, o comunque non attraverso la porta 123, tipica del protocollo NTP.

A tal scopo, è possibile utilizzare il comando HTPDate(8), il quale può essere utilizzato sia in modalità interattiva sia in modalità di servizio (daemon mode). Ma come lavora?

HTPDate sfrutta una connessione Telnet sulla porta 80 per recuperare una pagina web da una lista di Web Server specificati in input, i quali tipicamente restituiscono, a fronte di una richiesta standard GET / HTTP/1.1, oltre alla pagina desiderata, diverse informazioni addizionali quali ad esempio:

Date: Tue, 24 Jun 2008 13:43:48 GMT

Compreso il funzionamento di base del programma, utilizzarlo risulta davvero abbastanza semplice. In particolare, mediante il comando:

# htpdate -d www.google.com yahoo.com www.kernel.org
www.google.com 24 Jun 2008 11:38:36 GMT (0.055) => 1645
www.google.com 24 Jun 2008 11:38:37 GMT (0.055) => 1645
www.yahoo.com 24 Jun 2008 11:38:37 GMT (0.118) => 1645
www.yahoo.com 24 Jun 2008 11:38:38 GMT (0.117) => 1645
www.kernel.org 24 Jun 2008 11:38:39 GMT (0.190) => 1645
www.kernel.org 24 Jun 2008 11:38:40 GMT (0.189) => 1645
#: 3, mean: 1645, average: 1645.000
Timezone: GMT+1 (CET,CEST)
Offset 1645.000 seconds

vengono effettuare due richieste per ognuno dei tre Server Web specificati, ognuno dei quali ci evidenzia un offset di 1645 secondi, ovvero poco più di 27 minuti di differenza tra l’orario definito sulla nostra Linux Box e quello restituito rispettivamente dai tre Server Web.

A questo punto, constatando che il server più veloce nella risposta (e quindi il più accurato) è stato quello di Google (0.055 il suo tempo di risposta), sarà possibile sincronizzare la nostra Linux Box mediante il comando:

# htpdate -s -l www.google.com

dove il flag -l specifica l’utilizzo del Syslog come standard output.

Il file di log /var/log/messages riporterà quindi la serie delle operazioni eseguite da HTPDate, in particolare l’interrogazione del Server Web www.google.com, il calcolo della media delle risposte (in questo caso le due query risultano identiche), il recupero del Timezone corrente ed infine l’effettiva sincronizzazione del tempo.

Jun 24 13:21:55 seti htpdate: www.google.com
24 Jun 2008 11:49:20 GMT (0.076) => 1645
[...]
Jun 24 13:21:56 seti htpdate: Setting 1645.000 seconds
Jun 24 13:21:56 seti htpdate: Set: Tue Jun 24 13:49:21 2008

Il nostro sistema è quindi aggiornato!

La seconda modalità operativa di HTPDate consente di lanciare il comando in daemon mode, in modo tale da avere il nostro sistema costantemente sincronizzato ad intervalli regolari. Ad esempio mediante il comando:

# htpdate -D www.google.com www.yahoo.com www.kernel.org

verrà mantenuta la sincronizzazione con la lista dei Server Web indicati, nell’ordine specificato da HTPDate. Altre interessanti opzioni, come l’intervallo di polling per la sincronizzazione, la possibilità di specificare utente e gruppo per l’esecuzione del comando in modalità daemon mode (ad esempio nobody) piuttosto che l’opportunità di scegliere una porta arbitraria per l’esecuzione del programma (ad esempio la porta 8080 se fossimo in presenza di reti il cui accesso web sia consentito solo tramite Proxy Server definiti su quella porta) sono nella pagina man.

Riallocazione di un settore illeggibile o danneggiato in un disco con partizioni LVM

Nelle più recenti distribuzioni Linux è presente smartd(8), una potente utility per il monitoraggio dei dischi ATA, IDE e SCSI-3.

SMART (Self-Monitoring, Analysis and Reporting Technology) è una tecnologia sviluppata per monitorare l’affidabilità degli hard disk, eseguire dei test di funzionamento e quindi tentare di prevedere eventuali failure.

Talvolta ci sarà capitato di rilevare errori riportati dal kernel, dal demone smartd o tramite sistemi di altra natura, del tipo:

Apr 17 16:20:31 seti kernel: hde: dma_intr: status=0x51 { DriveReady SeekComplete Error }
Apr 17 16:20:31 seti kernel: hde: dma_intr: error=0x40 { UncorrectableError }, LBAsect=6305975, sector=6305901

Questo genere di errori sono tipicamente corrispondenti ad errori presenti in lettura o scrittura sul nostro hard disk.
Inoltre tale errori, in ambiente SMART, corrispondono a messaggi di log del tipo:

Apr 18 09:05:03 seti smartd[2253]: Device: /dev/hde, 1 Currently unreadable (pending) sectors

che inequivocabilmente ci informano circa l’impossibilità di accedere in lettura o in scrittura ad un settore (danneggiato) del disco.

Arrivati però a questo punto, cosa possiamo fare?

Nel nostro esempio, uno dei casi più complessi, il settore danneggiato si trova in una partizione LVM; sarà quindi nostro obiettivo cercare di individuare il relativo blocco fisico danneggiato e quindi riparare, se possibile, l’errore.
In generale è bene ricordare che il settore è la minima unità gestibile contenente dati mentre il blocco è un multiplo della dimensione del settore del disco stesso; allo stesso tempo il contenuto di un file può essere distribuito su uno o più blocchi di dati.

Cominciamo con un semplice test eseguito sul disco sul quale sono presenti gli errori sopraesposti:

# smartctl -t long /dev/hde

In questo caso attraverso l’utility smartctl(8) sarà eseguito un test approfondito, dalla durata di circa mezz’ora, sul device /dev/hde.

Al termine dello stesso, sarà possibile recuperare i risultati del test appena terminato, in questo modo:

# smartctl -l selftest /dev/hde
[...]
=== START OF READ SMART DATA SECTION ===
SMART Self-test log structure revision number 1
Num  Test_Description  Status                   Remaining  LBA_of_first_error
# 1  Extended offline  Completed: read failure  40%        6305975

Come si può notare dall’output restituito, il test appena eseguito si è interrotto al 40% avendo individuato un errore nell’indirizzamento di blocco logico (Logical Block Addressing – LBA) corrispondente al settore numero 6305975.
Andiamo a controllare come è partizionato il nostro disco fisso:

# fdisk -lu /dev/hde
Disk /dev/hde: 61.4 GB, 61492838400 bytes
255 heads, 63 sectors/track, 7476 cylinders, total 120103200 sectors
Units = sectors of 1 * 512 = 512 bytes
Device      Boot    Start         End      Blocks   Id  System
/dev/hde1   *          63      208844      104391   83  Linux
/dev/hde2          208845   120101939    59946547+  8e  Linux LVM

La prima cosa che facilmente si nota è che l’indirizzo logico del blocco si riferisce ad un settore presente nella partizione Linux LVM /dev/hde2.

A questo punto però si deve fare attenzione: l’LBA dell’errore restituito in precedenza, 6305975, costituisce il valore assoluto di indirizzamento logico della memoria di massa complessiva.

A noi invece, visto che si sta andando a lavorare all’interno di una singola partizione, occorrerà determinare l’offset corrispondente: di fatto sottrarremo dal valore dell’indirizzo riportato in precedenza il valore dell’indirizzo dal quale inizia la partizione LVM interessata.

Quindi, nel nostro esempio, l’offset cercato sarà:

6305975 – 208845 = 6097130			(FS block offset)

Inoltre, ricordando che ogni partizione LVM verrà poi organizzata in Physical Extent (PE) della stessa grandezza, ed essendo in presenza di allocazione lineare, il secondo passo consisterà nel determinare la dimensione (qui di seguito espressa in KB), di ogni PE tramite il comando:

# part=/dev/hde2; pvdisplay -c $part | awk -F: '{print $8}'
32768

Considerando che il physical block size del sistema corrisponde a 512 bytes (cioè metà KB), si avrà che la dimensione di ogni PE sarà uguale a:

32768 * 2 = 65536 				(PE physical size)

Quindi, per determinare in quale PE si trovi il blocco logico rovinato, sarà sufficiente dividere l’FS block offset precedentemente calcolato per la dimensione di ogni PE appena ottenuta:

6097130 / 65536 = 93.034 -> 93 		(#PE)

Sappiamo perciò che il blocco logico in questione risiede sul 93-esimo PE.
Di fatto, attraverso l’output del comando:

# lvdisplay --maps |egrep 'Physical|LV Name|Type'
LV Name                	/dev/VolGroup00/LogVol00
   Type                	linear
   Physical volume	/dev/hde2
   Physical extents	0 to 1800
LV Name                	/dev/VolGroup00/LogVol01
   Type                	linear
   Physical volume    	/dev/hde2
   Physical extents    	1801 to 1828

possiamo conseguentemente determinare che il PE considerato appartiene al primo Logical Volume (LV) chiamato /dev/VolGroup00/LogVol00 del Volume Group (VG) /dev/VolGroup00.

Visto che ora si andrà ad intervenire scendendo dal livello di partizione LVM a livello di LV, ci occorrerà anche sapere l’offset iniziale di quest’ultimo, ottenuto sommando il blocco logico di partenza del VG e tutti i blocchi logici relativi ai PE degli LV precedenti. Tramite il comando:

# grep pe_start $(grep -l $part /etc/lvm/backup/*)
pe_start = 384

sarà quindi prima possibile determinare il blocco logico di partenza del VG; poi, osservando che il nostro LV è di fatto il primo LV del VG, si determina che il primo PE di /dev/VolGroup00/LogVol00 è uguale a 0 (mentre se ad esempio avessimo dovuto lavorare sul secondo LV sarebbe stato uguale a 1801). Quindi risulterà:

384 + (0 * 65536) = 384				(LV offset)

Finalmente sarà possibile determinare il blocco fisico danneggiato del file-system secondo la formula:

((FS block offset) - (LV offset)) / ((PE physical size) / (physical block size))

ovvero:

((6097130 - 384) / (65536 / 512)) = (6096746 / 128) = 47630.828 -> 47630

Nel calcolo è da notare come il valore 128 corrisponda al numero di blocchi fisici presenti in ogni PE.

Siamo finalmente arrivati!

Testiamo ora che effettivamente il blocco fisico #47630 sia danneggiato attraverso il comando dd(1) e l’opzione ‘skip’ che consente di leggere l’input saltando i primi 47630 blocchi fisici (ovvero tentando di leggere esattamente il blocco fisico danneggiato):

# dd if=/dev/VolGroup00/LogVol00 of=/tmp/block-47630 bs=65536 count=1 skip=47630
dd: reading `/dev/VolGroup00/LogVol00': Input/output error
0+0 records in
0+0 records out
0 bytes (0 B) copied, 18.6989 seconds, 0.0 kB/s

Non ci resta altro da fare che formattare il blocco fisico danneggiato (‘scrivendo zeri’ per una lunghezza pari alla dimensione del PE physical size) attraverso il comando dd(1) e l’opzione ‘seek’ che consente di scrivere in output saltando i primi 47630 blocchi fisici (ovvero accedendo esattamente al blocco fisico danneggiato):

dd if=/dev/zero of=/dev/VolGroup00/LogVol00 count=1 bs=65536 seek=47630

ed il gioco (si fa per dire) è fatto!

Possiamo infine, come verifica, dopo aver ripetuto il testo iniziale attraverso il comando smartctl -t long /dev/hde interrogare il relativo log ottenendo il seguente risultato:

# smartctl -l selftest /dev/hde
[...]
=== START OF READ SMART DATA SECTION ===
SMART Self-test log structure revision number 1
Num  Test_Description  Status                   Remaining  LBA_of_first_error
# 1  Extended offline  Completed without error  00%        -
# 2  Extended offline  Completed: read failure  40%        6305975

che ci mostra come quest’ultimo test (Num #1) sia stato portato a termine senza errori, e che quindi l’errore del nostro hard disk sia stato efficacemente corretto.

Quando “ENUM” fa i capricci

Il tipo “ENUM” consente di definire una lista di valori da attribuire a un campo, una soluzione molto comoda quando i valori sono pochi e vogliamo evitare la creazione di una tabella di supporto. Vediamo però un esempio in cui le cose non vanno come dovrebbero.


mysql> CREATE TABLE test_enum (ID INT, strano ENUM('0','1'));
Query OK, 0 rows affected (0.38 sec)

Abbiamo creato una tabella con un campo ENUM che accetta i valori ‘0’ e ‘1’. Fin qui, tutto bene.


mysql> INSERT INTO test_enum VALUES (1,1);
Query OK, 1 row affected (0.28 sec)

Il primo inserimento dovrebbe fare quel che ci aspettiamo, cioè mettere ‘1’ nel campo ID e ‘1’ nel campo strano. Controlliamo:


mysql> SELECT * FROM test_enum;
+------+--------+
| ID | strano |
+------+--------+
| 1 | 0 |
+------+--------+
1 row in set (0.00 sec)

Uhm, no. Questo non è quel che volevamo. Come mai il campo strano ha uno zero mentre noi abbiamo inserito 1? Il motivo è che i valori di enum devono essere valori testuali e quindi andrebbero messi fra virgolette.

Va bene, ma cosa succede se invece mettiamo un numero? Succede che MySQL cerca di venirci incontro, interpretando il numero come “il valore che sta alla posizione indicata da quel numero”. Nel nostro caso, si trattava di 1, che identifica la prima posizione, che nel nostro caso era ‘0’. Ecco spiegato l’arcano. Questo meccanismo si rivela comodo quando i valori hanno una grafia dubbia, e quindi per evitare errori possiamo citarli per posizione invece che per nome:


nano ENUM('Dotto', 'Gongolo', 'Eolo', 'Brontolo', 'Mammolo', 'Pisolo', 'Cucciolo');

Ora l’istruzione


INSERT INTO nani set nano = 1

farà sì che il campo nano contenga ‘Dotto’, cioè il primo valore della serie.

Un tranello simile ci attende quando vogliamo utilizzare il valore di default di un campo. Sapete che ogni campo di una tabella MySQL può avere un valore di default, da usare quando si omette quella colonna in un inserimento.

Guardate la creazione di questa tabella, in cui mettiamo un valore di default per un campo ENUM.


mysql> CREATE TABLE test_enum2 (ID int, strano enum('0','1') NOT NULL DEFAULT 1 );
Query OK, 0 rows affected (0.01 sec)

Ora, se inseriamo un record che non contiene un valore per il campo “strano”, ci potremmo aspettare che tale campo assuma il valore ‘1’.


mysql> INSERT INTO test_enum2 (ID) VALUES (10);
Query OK, 1 row affected (0.00 sec)

Invece, anche questa volta, ci troviamo il valore ‘0’.


mysql> SELECT * FROM test_enum2;
+------+--------+
| ID | strano |
+------+--------+
| 10 | 0 |
+------+--------+
1 row in set (0.00 sec)

La spiegazione è la stessa. Anche per la definizione del valore di default, indicando un numero senza virgolette si intende la posizione nella lista, non il valore stesso.