Tag: tuning mysql

Database MySQL, 10 trucchi per migliorarne le performance e la stabilità

 sveliamo dei semplici segreti che molti amministratori di database MySQL usano per migliorare le performance delle proprie basi dati.

wordpress_mysql_backup

Il database è la memoria del nostro sito internet o applicativo e ci permette di offrire contenuti dinamici in tempo reale ai nostri utenti.
Ottimizzare il database del proprio sito internet è un’operazione estremamente complessa e condizionata da tante variabili.

Se gestiamo applicazioni o siti che devono supportare numerosi accessi simultanei oppure gestiamo una base di dati che nel tempo ha assunto una elevata complessità (nell’ordine di qualche centinaia di migliaia di righe) è possibile avere un degrado nelle prestazioni, che di solito può essere risolto generalmente ottimizzando il DB.

Quelli che seguiranno sono 10 trucchi che aiutano a migliorare ed ottimizzare le performance di un DB MySQL e/o Percona Server basato su motore InnoDB (praticamente tutti quelli dei maggiori CMS, come WordPress, Moodle, Prestashop, Magento, Joomla, ecc.).

Parametri MySQL da modificare/configurare per un database performante

1.      Configurare il parametro innodb_buffer_pool_size

Il parametro più importante da modificare immediatamente è innodb_buffer_pool_size ed è quello che indica quanta memoria può essere usata per memorizzare in RAM i dati (e quindi evitare accessi al disco continui). Che valore dare dunque al buffer?

Mettiamo che abbiamo un server su cui risiede sia il webserver che Mysql, con 8 giga di ram: dedichiamo a innodb_buffer_pool_size 4GB

Per essere più chiaro e meno ripetitivo faccio una tabella

TIPOLOGIA DI SERVER RAM A DISPOSIZIONE RAM DA DEDICARE
Webserver+mysql 8GB RAM 4GB RAM
Webserver+mysql 16GB RAM 10GB RAM
DBserver dedicato 6GB RAM 5GB RAM
DBserver dedicato 12GB RAM 10GB RAM
DBserver dedicato 24GB RAM 20GB RAM

2.      Configurare il parametro innodb_log_file_size

Questa proprietà definisce la grandezza massima del file di log per tabelle InnoDB. Pur essendo vero che maggiore è la dimensione, migliori sono le prestazioni, è anche vero che bisogna fare i conti con le dimensioni del server. A seconda di tale considerazione si consiglia un tuning di questa variabile tra i 512 e i 4GB.

3.      Configurare il parametro max_connections

Un numero massimo di connessioni disponibili inferiori al numero di connessioni correnti impedirà agli utenti di accedere al database, rendere il vostro sito inaccessibile e / o lenta. Un numero molto elevato di connessioni disponibili per un numero molto basso di connessioni effettive farà server MySQL richiedono più RAM rispetto effettivamente necessario. Il problema di valori troppo alti (oltre i 1000) è il possibile crash del server che si trova a dover gestire contemporaneamente oltre 1000 transazioni attive.

4.      Configurare il parametro innodb_file_per_table

Questa impostazione serve per indicare a MySQL se si vuole conservare i dati e gli indici in un unico tablespace (impostazione su OFF) o in un file IBD separato per ogni tabella (impostazione su ON). Il consiglio è di impostare il parametro su ON (valore di default per MySQL 5.6) soprattutto se si effettuano operazioni di cancellazione e ricostruzione tabelle od operazioni di compressione, che permettono di recuperare spazio. Evitare però di usare l’impostazione su ON qualora si abbia a che fare con un database dal numero di tabelle davvero elevato (oltre 10 mila).

5.      Configurare il parametro innodb_flush_log_at_trx_commit

Se sembra che InnoDB sia più lento di MyISAM è possibile che questo valore sia stato configurato male. Il default (1) significa che ad ogni commit di una transizione (o statement fuori dalla stessa) necessita di fare flush del log su disco, un’operazione piuttosto costosa se molto frequente. Settare il valore a 2 significa fare il flush del log solo tramite cache del sistema operativo. Il valore 0 è più veloce, ma è rischioso perchè si possono perdere dati durante le transizioni nel caso di un crash di MySQL Server. Anche il valore 2 è rischioso se il sistema operativo va in crash.

6.      Configurare il parametro innodb_flush_metod

Sui sistemi Linux e simili (FreeBSD, Solaris…) c’è inoltre il problema di evitare il doppio buffering e per farlo occorre settare la variabile innodb_flush_method a O_DIRECT. Fatelo se vi accorgete che il vostro sistema swappa più del previsto quando il db è sotto stress.

7.      Configurare il parametro innodb_log_buffer_size

Il default per questa variabile è 1MB e per sistemi con poche transazioni ed un traffico medio potrebbe bastare. Non va settato un valore troppo alto perchè è il buffer è flushato ogni secondo ed esagerare significherebbe sprecare memoria. Solitamente sono più che sufficienti 8-16 MB.

8.      Configurare il parametro query_cache_size

Utile per applicazioni con molti dati in lettura. Valori compresi tra i 256 e i 2048 MB sono i migliori, a seconda della potenza della vostra macchina.
Al momento in cui scrivo le considerazioni valgono per MySQL Server 5.5 (io le ho provate su MySQL Server 5.6) e sono comunque suggerimenti da utilizzare con criterio e in base alle possibilità della propria macchina.

9.      Configurare il parametro log_bin

Attivare il logging binario è necessario sia per far funzionare il server come replica di un database server master, sia se sul server (in configurazione singola) si vuole abilitare il sistema di backup automatico. Logga le istruzioni che scrivono dati in formato binario. L’argomento opzionale deve essere il nome del log. Se non è specificato, verrà usato datadir/’log-basename’-bin o ‘datadir’/mysql-bin (il secondo solo se --log-basename non è specificato). Si raccomanda caldamente di usare --log-basename o specificare un nome file per essere sicuri che la replica non si arresti nel caso in cui il nome host del server cambi.

10.  Configurare il parametro skip_name_resolve

MySQL, per i client che si collegano da remoto, mantiene una cache dove memorizza numero IP, nome host ed altre informazioni. Per far ciò il server tenta una risoluzione DNS IP->host name.
Se i DNS interni alla LAN non forniscono un reverse DNS lookup (PTR records) allora è probabile sperimentare un rallentamento nelle connessioni. Infatti MySQL tenterà ogni volta di reinserire nella cache il nome host, processo inevitabilmente destinato a fallire con un timeout.
Per disabilitare il DNS host name lookup è sufficiente far partire MySQL con l’opzione –skip-name-resolve. Basta modificare il file di configurazione my.cnf o my.ini

Miglioriamo le performance di MySQL Server ottimizzando e riparando le tabelle.

MySQL non ha purtroppo  la sana abitudine di ottimizzare le tabelle qualora ve ne sia bisogno.

Per ottimizzazione delle tabelle si intende la riduzione dei byte in eccesso che si vengono a verificare quando un campo di un qualsiasi record viene modificato oppure quando un record viene cancellato.

Ad esempio, se un campo contiene del testo d occupa 100 byte e poi il campo viene modificato ed occuperà 90 byte, c’è il rischio di trovarsi con 10 byte in eccesso.

Stesso discorso quando un record viene cancellato: se la tabella occupa 1000 byte e viene cancellato un record che ne occupa 150, ci troveremo con una tabella di 850 byte col rischio di averne 150 in eccesso.

MySQL mette a disposizione l’istruzione OPTIMIZE TABLE il cui compito è quello, appunto di ottimizzare la tabella e compattare i dati.

Per far ciò basta usare lo strumento Mysqlcheck, in questo modo:

mysqlcheck –u root –p —auto–repair –c –o nomedatabase

Tuning e ottimizzazione server web linux. Quando il gioco si fa duro …

“Quando il gioco si fa duro … i duri cominciano giocare” esclamava il buon John Belushi in Animal House.
Questa regola era validissima nei tempi passati in cui l’informatica era un terreno poco fertile, l’hardware aveva dei prezzi proibitivi e si era disposti a passare intere nottate in bianco per risparmiare 50k di RAM ottimizzando le routine più complesse direttamente in assembly.

Oggi il panorama è cambiato, l’hardware ha costi irrisori se paragonati a quelli dell’epoca e non c’è più tempo, capacità e voglia di ottimizzare : quando qualcosa non funziona bene si passa semplicemente ad hardware più potente. Quando la banda non basta più si compra più banda.

La convinzione ovvero che basti mettere mano al portafogli per risolvere tutti i problemi informatici del momento.

Questo è vero in alcuni casi ma rimane sempre una soluzione errata ed eticamente scorretta quando magari con un’ora di intelligente ottimizzazione si può incrementare le performance di oltre il 400%.
Il concetto è valido in particolar modo per i Webserver che sono spesso il risultato di sottocomponenti distinte (DBMS, Linguaggio lato server, Server web) su cui gira un sito internet che elabora dati e presenta contenuti multimediali al navigante.

Morale della favola : aspettate prima di montare un altro processore e quadruplicare la RAM, provate invece ad ottizzare le singole componenti software per rendere il sito web snello e scattante e in grado di sopravvivvere ad un traffico di centinaia di visite al secondo senza crashare.

Un buon modo di iniziare è sapere su cosa metter mano, ovvero ciò che il mercato offre nell’ambito di server LAMP, cosa installare e cosa ottimizzare per avere i miglior risultati possibili.

Innanzitutto vale sempre il metodo top-down, ovvero risolvere il problema scomponendo il problema in più sottoproblemi, ricordando in questo caso che l’inefficienza di ogni sottocomponente si ripercuoterà in modo incisivo sull’efficienza o meno dell’intero server. L’imperativo è dunque : evitare i colli di bottiglia.

Se ad esempio facciamo un uso massiccio di PHP, molti accessi concorrenti al Database con query complesse, utilizzo di CMS del calibro di Drupal, Joomla o WordPress e ci aspettiamo di riuscire a soddisfare una mole di richieste non indifferente bisogna seguire scrupolosamente i seguenti passi :

Scelta di un Hardware decente : ormai un server di fascia alta ce lo si può permettere per meno di 100 euro al mese. Configurazioni interessanti basate su intel i7 o addirittura Xeon, quad o six core, 12 giga o più in triple channel e dischi raid sata3.
Se si vuol essere performanti bisogna sempre fare una scelta a livello hardware adatta alle esigenze. Dischi SSD in RAID 1 sicuramente aumentano le performance diminuendo la latenza disco.
Se invece siamo nella condizione di avere un “macinino” e il nostro sito diventa sempre più popolare e non abbiamo voglia di migrare tutto su hardware più potente … ottimizziamo.
Qualunque siano i casi ottimizziamo a prescindere.

Web server : è ormai uno standard, lo danno installato di default con la nostra distribuzione Linux e ce lo teniamo come se fosse il miglior webserver del mondo. Apache esatto. Ottimo webserver facilmente ottimizzabile variando i parametri in httpd.conf.
Oppure possiamo optare per il meno conosciuto NGINX. Nginx si sta configurando sempre più chiaramente come una valida alternativa ad Apache: sembra infatti che, benchmark alla mano, Nginx risulti molto più leggero e performante del famoso rivale che spesso e volentieri soffre di memory leak i quali possono causare un consumo di memoria piuttosto “imbarazzante”. Se poi Nginx viene utilizzato in abbinamento con PHP FPM (FastCGI Process Manager), una versione di PHP ottimizzata per siti a traffico elevato, allora la differenza con la classica configurazione Apache+PHP diventa sensibile!

Database : normalmente ci si appoggia a MySQL come DBMS standard, più raramente a PostgreSQL. Database relazionali destinati alla realizzazione di progetti molto ambiziosi grazie a feature come integrità referenziale, stored procedure, stored function, viste, triggers, transazioni ACID.
Qualora il vostro sito non faccia uso di queste funzionalità optate per un DBMS senza queste funzioni ma più veloce e performante come ad esempio Drizzle.
Se siete pigri per passare a Drizzle e l’applicazione web (o sito) non ha una business logic complessa e non avete bisogno dell’integrità referenziale e tutte le altre belle cosucce elencate prima limitatevi a utilizzare tabelle MyISAM di MySQL piuttosto che le più complete ma meno performanti InnoDB.
A livello di progettazione Database vale la pena ricordare che una progettazione ad-hoc è fondamentale per una buona performance dell’intero progetto. Dunque ottimizzare i tipi di dato in uso nella creazione del database, le giuste tabelle, eliminare le ridondanze, fare un buon uso di indici, partizionare le tabelle, ma sopratutto ottimizzare le query SQL. A volte la performance di una query ottimizzata può essere di oltre il 1000 % (mille avete letto bene).
Una meticolosa analisi in ambito di progettazione a partire da uno schema E/R corretto è d’obbligo.
Vale la pena ricordare che un corretto tuning delle variabili d’ambiente in my.cnf (il file di configurazione di MySQL) può portare a vantaggi tangibili (spesso addirittura notevoli) sopratutto all’aumentare delle richieste concorrenti.

Script server side : diamo per scontato che programmiate in PHP e che dunque gli script siano scritti bene, o se magari non l’abbiate scritti voi che comunque siano performanti, o che comunque performanti o non performanti che siano non avete la possibilità o la capacità per modificarli.
E’ bene sapere che esistono degli opcode cacher per PHP come APC o eAccelerator che possono far risparmiare importanti risorse nella fase di fetching del codice php. Sarebbe complesso e decisamente lungo da spiegare nel dettaglio ma vi basti sapere che in media con l’adozione di tali strumenti (gratis oltretutto) le prestazioni aumentano da un 50% fino ad oltre il 400%.

Qualora intendiate dunque ottimizzare le performance del vostro sito web o vogliate consulenza per la realizzazione di siti web ad alto traffico, contattateci. Siamo in grado di metter mano ad ogni aspetto del vostro webserver e aumentare sensibilmente l’efficienza del vostro server benchmark alla mano.