Backup MySQL lento e server down quando passa Google

Print Friendly, PDF & Email

Una delle casistiche poco conosciute all’utente finale di un sito Web è quello inerente a problemi di disponibilità del sito che avvengono quando il server esegue il backup del database MySQL.

Sebbene le operazioni di manutenzione e di backup siano schedulate in orari notturni dove ci si aspetta poca affluenza traffico e un minor carico del server, cosa succede quando proprio in quel momento passano i crawler di Google per scansionare i nuovi contenuti ed indicizzarli ?

Se il backup è progettato correttamente, con le giuste tecnologie e su un dataset di dimensioni adeguate, semplicemente andrà tutto bene e Google avrà modo di scansionare il nostro sito, i nuovi contenuti per poi successivamente indicizzarli, altrimenti verrà raggiunto da una serie di errori di tipo 500 e una volta speso il crawling budget destinato alle attività di crawling sul tuo sito se ne andrà senza recuperare i nuovi contenuti e anzi rilevando numerosi errori di tipo 500 che nel medio / lungo termine potrebbe compromettere anche il posizionamento del tuo sito dato l’error ratio molto elevata.

Ma perchè avviene questo ?

Per capire le motivazioni per cui avvengono questi problemi che non sono frequenti ma nemmeno troppo rari, dobbiamo capire come normalmente un hosting provider si pre-occupa di svolgere le attività di backup a livello server e più nello specifico quando andrà a backuppare il database del nostro sito. Cosa succede ad esempio se mentre sta copiando arriva una richiesta al nostro sito che tramite PHP si occupa di eseguire più query SQL al database ?

In un contesto di perfetta idiozia andremo a sporcare il backup generando una situazione inconsistente dato che magari in quel millisecondo avremmo aggiornato un nuovo record nell’ipotetica tabella anagrafica senza scrivere il relativo record nella tabella codice fiscale.

In un contesto standard il sistemista o il pannello di controllo andrà a eseguire mysqldump con i parametri di locking che bloccherà le tabelle sia in lettura che scrittura, garantirà un dump integro e coerente ma andrà inevitabilmente a generare problemi di locking, ovvero avremmo delle risorse bloccate e dunque la query non andrà a buon fine entro il tempo stabilito dal webserver innescando nella migliore delle ipotesi un timeout, nella peggiore “appendendo” il crawler per diverse decine di secondi e generare timeout lato crawler.

Evitare queste situazioni è possibile utilizzando i giusti accorgimenti e sapendo valutare lo scenario in base alla tecnologia, alla dimensione del dataset, al tipo di tabelle ed essenzialmente al budget del cliente per poter pianificare con serenità la migliore soluzione per le sue tasche.

Backup MySQL tramite mysqldump

È probabilmente il metodo di backup più semplice e più comune nonchè anche il più errato, in parte perché fa parte dei programmi client MySQL, quindi sarà installato di default. Effettua backup logici e può eseguire il backup di tutti i database, di un database e di tutte le tabelle o di una o più tabelle dello stesso database. Il dump viene stampato sull’uscita standard, in modo da poter inviare l’output ad altri programmi per l’interoperabilità. Ad esempio, è possibile collegare l’uscita a gzip, dato che mysqldump non comprime l’uscita. L’aspetto negativo maggiore è che è dolorosamente lento su grandi database, non solo perché esegue backup logici, ma anche perché sia il backup che il processo di ripristino sono a thread singolo. Il dump contiene istruzioni SQL per creare le tabelle, popolarle con i dati o entrambi.

Come abbiamo accennato sopra, questo è il modo più errato che si possa utilizzare per fare un backup di un database MySQL, Percona Server o MariaDB, ovvero utilizzare l’utility di sistema mysqldump. Se ancora non ve ne foste accorti, siamo nell’anno 2019 e non più nel 2005 e forse è ora che voi e il vostro hosting provider si dia una bella svegliata prendendo atto di quanti problemi crea questa utility sopratutto su grossi database di svariati gigabyte.

Se non lo fa, cambiate pure fornitore se il vostro progetto è estremamente ambizioso e importante per voi.

Mysqldump può estrarre e copiare il contenuto delle tabelle riga per riga, oppure può estrarlo interamente dalla tabella e immetterlo in un buffer di memoria prima di copiarlo. Usare il buffering può essere un problema quando si copiano grandi tabelle.

Essenzialmente lo scopo di mysqldump è quello di generare un file .sql in cui ci sono le istruzioni per ricreare il database chiamato come dicevamo prima dump.

Come dicevamo prima per garantire un backup di database consistente si usano parametri per il lock delle tabelle :

  • Per le tabelle InnoDB si usa --single-transaction come opzione.
  • Per le tabelle MyISAM si usa invece l’opzione --lock-tables

Se hai un database di pochi megabyte o una decina di megabyte puoi “tranquillamente” utilizzare questo approccio che richiede qualche secondo o poco più per produrre un dump .sql, ma se ci troviamo di fronte ad un database di svariati gigabyte ? Potresti impiegare decine di minuti o addirittura ore.

In quel frangente il database sarà in uno stato di locking e nessuno potrà scrivere o leggere. Nemmeno Google restituendovi questo tipo di errore.

502 bad gateway nginx

Percona XtraBackup. Come fare un backup MySQL da professionisti.

Mentre il metodo prima elencato esegue backup logici, con una lentezza estenuante, un approccio simile diventa inutilizzabile man mano che un database inizia a crescere di qualche gigabyte.  Xtrabackup è il software di backup fisico MySQL più diffuso. Può creare un backup caldo (il che significa che non è necessario fermare il server del database), è molto più veloce e supporta backup completamente non bloccanti, a condizione che tutte le tabelle siano InnoDB o XtraDB. Può creare file di backup locali o un flusso di output standard, quindi è molto versatile. Ad esempio, utilizzando strumenti come gof3r è possibile eseguire lo streaming dei backup su Amazon S3 e caricare il backup al volo (senza conservarlo localmente). La dimensione dei backup è grande quasi quanto l’intero database, ma xtrabackup supporta i backup compressi utilizzando qpress, riducendo notevolmente la dimensione finale. Un’altra grande caratteristica è che può crittografare i backup o lo streaming con AES128, AES192 e AES256.

Oltre ad avere molti altri vantaggi in primis un restore dell’intero DB praticamente immediato e molte altre feature che andrebbero ben oltre il nostro scopo focalizzato su problematiche sistemistiche che sfociano in problematiche di tipo SEO, il vantaggio principale è quello di non bloccare col locking il database e dunque continuare a rispondere ad eventuale query di visitatori notturni, spider e crawler.

Detto in maniera semplice, mentre mysqldump deve bloccare le tabelle, effettuare il backup e poi sbloccarle, Percona XtraBackup utilizza una funzione nativa di MySQL e software DBMS derivati come MariaDB o Percona Server chiamati binary log dove vengono salvate tutte le istruzioni in scrittura e modifiche che giungono al DB.

Percona XtraBackup insomma si occupa di copiare i dati in maniera grezza, continuare a loggare le istruzioni di modifica in questi binary log per poi leggere questi file di log e andare ad applicare le istruzioni contenute al loro interno (ovvero le modifiche del database) alla copia del database del backup generando così una “fotografia” pienamente integra e conforme alla situazione iniziale.

Performance MySQLdump VS Percona XtraBackup

Se vogliamo toccare con mano dei numeri reali ovvero misurare per decidere, possiamo vedere brevemente un benchmark tra i due tool per comprendere di cosa stiamo parlando e perchè mysqldump deve essere considerato come un giocattolino inadeguato per sistemi in produzione, andato a creare backup mysql lenti.

Come possiamo vedere pur lavorando sullo stesso dataset di ben 73GB un backup realizzato con mysqlsump è ben 50 volte più lento rispetto ad uno realizzato con Percona XtraBackup.

Va inoltre doverosamente detto che Percona XtraBackup è assolutamente free senza alcuna limitazione e attualmente la migliore soluzione di backup esistente per sistemi basati su MySQL e derivati. Talmente valido che rimane addirittura migliore di MySQL Enterprise Backup realizzato dalla casa madre di MySQL stessa e offerto alla modica cifra (tono ovviamente sarcastico) di 5000 dollari all’anno per server.

Non esiste insomma alcun motivo per non utilizzare Percona XtraBackup se non solo superficialità ed ignoranza conclamata.

Usi pannelli come cPanel o Plesk ? Attenzione, anche loro usano mysqldump

Purtroppo il dilettantismo e il pressapochismo non riguarda solo sistemisti della domenica e improvvisati tuttofare ma anche aziende di un certo spessore che producono pannelli di controllo commerciali come cPanel o Plesk.

Leggendo infatti sul loro sito possiamo leggere in data gennaio 2019 inerentemente all’utilizzo di Percona :

Oltretutto anche rispetto ad altre tipologie di backup ecco un semplice specchietto riassuntivo che mostra perchè dovreste utilizzare questo modo di lavorare invece di improvvisarvi con utility antiquate e altamente non performanti.

Percona XtraBackup è l’unica soluzione in grado di soddisfare tutti i requisiti, utilizzabile in maniera assolutamente proficua anche insieme ad una replica MASTER / SLAVE ma questo è un altro discorso.

Conclusione

Se sei abituato a trovare backup dei singoli database e i vari .sql nel tuo spazio di backup giornaliero o settimanale, inizia a preoccuparti. Se noti che il backup MySQL è molto lento, poniti le giuste domande. Se vedi errori dello spider di Google, inizia a preoccuparti, se vieni a conoscenza che il tuo fornitore di hosting usa mysqldump, inizia a preoccuparti, se il tuo progetto web ti da da vivere e sei certo che non usa Percona XtraBackup, cambia subito fornitore. Non ci piace screditare nessuno, ma un dilettante allo sbaraglio è pericolo per se stesso e per il tuo business.

 

 

 

17279

Vuoi ricevere i migliori consigli ?

Ogni settimana nuovi consigli e novità !