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 autorepair c o nomedatabase

Ottimizzazione WordPress. Come ottimizzare e velocizzare un blog WordPress con una consulenza sistemistica dedicata. Linux Server Hosting per WordPress.

speed-up-wordpress-siteAbbiamo scritto parecchio in questi ultimi due anni che ci ha visto fornire consulenze ad-hoc (a clienti importanti) sull’importanza di avere un blog WordPress veloce e performante e su come raggiungere lo scopo.
Abbiamo scritto dell’importanza di un’installazione Server adeguata ed ottimizzata, dell’importanza di sostituire software come Apache, MySQL, con i più performanti NGINX e Percona Server.
Abbiamo scritto dell’importanza di scegliere un partner adeguato alle esigenze di hosting, relativamente al traffico generato, ai picchi di banda, in rapporto al budget disponibile.
Abbiamo scritto di come sia intelligente utilizzare CDN come Incapsula per migliorare la velocità nella distribuzione di contenuti statici (come le immagini), di come risparmare banda e di come risparmiare connessioni in ingresso che possano gravare in modo negativo sul server e sulle prestazioni.
Abbiamo scritto dell’importanza dei metodi di caching, sia a livello DB (MySQL o Percona Server che sia), sia a livello di CMS, sfruttando ottimi plugin come W3 Total Cache in accoppiata con software di sistema come Memcache o Redis.
Abbiamo scritto sulla possibilità di cachare le pagine statiche tramite un reverse proxy come Varnish.
Altri hanno scritto per noi di come sia importante un caricamento immediato delle pagine web, e di come un solo secondo di ritardo provochi :

  • 11% in meno di pagine visitate;
  • 16% di riduzione nella customer satisfaction;
  • 7% in meno di conversione della visita in acquisto;
  • Il cliente online tipico attende al massimo 2 secondi.

fino ad arrivare all’abbandono del sito dopo aver atteso 5 secondi.

http://www.businesscommunity.it/m/_Marzo2013/fare/Il_successo_delle_vendite_online_si_misura_in_secondi.php

Tutto questo per dire che un Blog WordPress, può e deve usufruire di tutte queste tecnologie disponibili per due scopi essenziali : essere rapido e scattante, reggere migliaia di utenti al secondo.
Il costo è alla portata di tutti, sul lungo termine si arriva spesso al risparmio, in alcuni casi addirittura DECIMANDO il costo di affitto infrastruttura.

Nel frattempo, si sono rivolti a noi per velocizzare il loro sito, clienti come :

e molti altri blog famosi basati su WordPress, coperti da clausola di riservatezza.

Va necessariamente detto, che di norma uno sviluppatore non ha conoscenze specifiche nell’implementare una corretta configurazione e dimensionamento dell’hardware e del software che saranno alla base dell’ottimizzazione del sito in WordPress, per cui se volete realmente fare la differenza, chiedeteci pure una consulenza sistemistica gratuita.

Grazie a competenze specifiche di networking e di sistemistica su Gnu/Linux, nonchè una conoscenza approfondita dei principali fornitori di connettività italiani ed europei, analizzeremo le problematiche attuali, e svilupperemo una strategia ad-hoc da proporvi al fine di garantirvi i migliori risultati al costo più basso, proponendo al contempo un’assistenza continuativa nel tempo grazie a piani di assistenza managed su server VPS, Cloud, o Server Dedicati.

Il vostro blog wordpress vi ringrazierà, ed anche i vostri utenti.

Anche Mammeacrobate.com ci sceglie per l’hosting ottimizzato del blog WordPress.

mammeacrobate

E’ in produzione dallo scorso Sabato mattina, il frutto del lavoro avvenuto nella nottata del Venerdì (per minimizzare il disservizio negli orari con pochi utenti online), che ci ha visto chiamati in causa come consulenti per un portale orientato al tema maternità, che ha numeri considerevoli : 12 mila FAN Facebook, e ben 18 autori.

Dopo aver lamentato problemi di velocità nella navigazione, abbiamo deciso di adottare l’ormai consueta “ricetta” al fine di ottimizzare la navigazione del blog WordPress e velocizzare il caricamento dei contenuti.

Dopo aver ottimizzato i valori di sistema del sistema operativo, abbiamo sostituito la vecchia architettura basata su Apache, PHP 5.3, MySQL 5.1 con la più performante configurazione basata su NGINX con PHP 5.6 in PHP-FPM con Zend OpCache abilitato e Percona Server 5.6

Un tuning ad-hoc del caching di Percona Server al fine di minimizzare l’accesso al DB per le query in sola lettura di tipo SELECT, e l’installazione e configurazione di un plugin per il caching in RAM (tramite memcache), come W3TC ci ha permesso di minimizzare i tempi destinati alla creazione delle pagine dinamiche PHP.

Una successiva implementazione di una pseudo CND su dominio di terzo livello img.mammeacrobate.com, tramite Incapsula, ci ha permesso di assegnare il compito di distribuzione di contenuti statici (tutti gli upload, ed in particolar modo le immagini) ai server della CDN Incapsula, guadagnando in velocità potendo minimizzare le connessioni in ingresso verso il server di Mammeacrobate.com e dunque risparmiare picchi di banda e traffico.

Un’altro sito che si unisce all’ormai folta schiera di blog wordpress ad alto traffico trattati con successo negli ultimi 2 anni.

Se cercate un hosting WordPress ottimizzato per il vostro blog, contattateci pure, sapremo sicuramente proporvi una soluzione ai vostri problemi.

Anche Martina Gold sceglie DREAMSNET.IT per l’ottimizzazione WordPress del suo nuovo sito.

Martina_Gold_03-MediumE’ a causa del rifacimento del nuovo sito Web basato sul CMS WordPress, che anche Martina Gold, nota attrice emergente del mondo dell’hard ci ha chiesto consulenza tramite il proprio staff tecnico, per la risoluzione di problematiche bloccanti dovute al sovraccarico del server su cui era hostato il nuovo sito web.

Al momento della richiesta, il sito risultava praticamente non navigabile a causa di un carico del server su Aruba che arrivava ad oltre 200 volte la soglia massima, con un load average di oltre il 270%.

Dopo un’accurata analisi preliminare abbiamo deciso di intervenire ed accettare la sfida, mettendo in atto tutta una serie di accorgimenti al fine di normalizzare i picchi di carico problematici e rendere l’esperienza di navigazione, fluida, veloce, piacevole.

Tra gli obiettivi preposti nell’ottimizzazione, quello di contenere i costi, rimanendo in linea con i costi di infrastruttura hardware (server) attuali.

Abbiamo pertanto scelto di migrare l’attuale configurazione basata su Server Dedicato Basic 2.2 di Aruba con le seguenti caratteristiche :

Server Basic 2.2
Hardware Assemblato
1x Processore Intel Atom Dual (2x core 1.60 GHz) o superiore
4x GB RAM
1x Hard Disk 500GB SATA 3,5″

25 € / Mese + IVA

su un server VPS Cloud 3 di OVH con le seguenti caratteristiche offerte allo stesso costo :

Cloud VPS 3
6 vCores
8 GB RAM
100 GB Hard Disk RAID10

29.99 € / Mese + IVA

garantendo con questa soluzione una business continuity eccellente derivata da una tecnologia di virtualizzazione Cloud basata su Hypervisor VMWare ESXi.

Un numero maggiore di core e circa il doppio di memoria RAM, ci ha permesso di mettere a puntino un tuning meticoloso dei vari servizi che siamo andati ad installare.

Una sostituzione del vecchio Apache e PHP 5.3, con un performante NGINX con PHP in Fast Process Manager (PHP-FPM) 5.6 che integra Zend Opcache.
Una sostituzione del vecchio MySQL 5.1 con il perfomante Percona Server 5.6 (replace drop in di MySQL – www.percona.com) ed un tuning ad hoc per il caching delle query in RAM.
Una configurazione adeguata ai valori del kernel e ai limiti del sistema.

L’implementazione di un sistema di Caching ad hoc dei contenuti di WordPress, tramite l’installazione di W3 Total Cache, un famoso plugin adibito a questo scopo.
L’installazione di una CDN come Incapsula che ha permesso di velocizzare la navigazione ed il caricamento dei contenuti tramite il delivery dei contenuti funzionando in reverse proxy.

Ad ora, non ci sono problemi di sorta, e l’intero sistema viaggia normalmente con un carico medio di 0.7, fino a rari picchi massimi di 3.

Un significativo aumento della qualità, che si traduce in una reale possibilità di crescita del business dovuto ad una navigazione ed user experience eccellente e all’eliminazione dei fastidiosi blocchi del server che rendeva impossibile la messa in produzione del nuovo sito WordPress

Un nuovo blog ad alto traffico che si va a sommare all’elenco dei numerosi casi di successo conseguiti nell’ottimizzazione di server e blog wordpress.

Hai un sito lento e ti chiedi cosa si possa fare per migliorare ? Contattaci.

Come migliorare le perfomance di un blog WordPress, grazie ad una consulenza sistemistica e ad uno stack Percona Server, PHP-FPM, NGINX, Redis, Memcache e Varnish.

In relazione all’articolo precedente sull’ottimizzazione performance di un server adibito all’hosting di un blog WordPress ad alto traffico come curiosone.tv (potete leggerlo qui), è stato deciso di fare un video dimostrativo al fine fare una comparazione prima / dopo, l’ottimizzazione, utilizzando i dati provenienti da htop, netstat, ed utilizzando il tool Apache Benchmark per effettuare uno stress testing su componenti software (plugin), alla base di un carico a livello di CPU ingestibile, dando un’esperienza di navigazione lenta e snervante, nonchè frequenti crash, e un backend letteralmente ingestibile se non in tempi biblici.

Il tutto è stato realizzato con software open source e “ricette sperimentate sul campo già altre volte”, tra i quali Percona Server, PHP-FPM, Zend Opcache, NGINX, REDIS, Memcache, Varnish, e l’ausilio di una CDN come Incapsula (Già più volte menzionata in questo blog).

La dimostrazione pratica di quanto un’assistenza sistemistica managed a livello server, possa fare la vera differenza in termini di qualità del servizio offerto, garantendo un notevole risparmio sui costi dell’infrastruttura, e garantendo un velocissimo ritorno dell’investimento iniziale, grazie alla qualità della navigazione che l’utente dispone e dei relativi modelli di business basati su interazioni con il visitatore come Advertising e campagne Pay per click di circuiti come Google Adsense.

Investire una cifra modesta per fare l’analisi della situazione iniziare ed effettuare un tuning ad-hoc è il primo passo da seguire, consultandosi con sistemisti Linux di comprovata esperienza.

Gestire un rapporto continuativo di supervisione e gestione server managed è il passo successivo per continuare a garantirsi i benefici ottenuti con una configurazione ad-hoc che deve comunque essere monitorata e supervisionata.

 

Oltreuomo.com ci sceglie come fornitori hosting ad alte prestazioni. Un tuning da oltre 2000 utenti al secondo.

oltreuomoIl famoso blog satirico oltreuomo.com ha deciso di affidarci l’hosting e la parte sistemistica per gestire il picco di utenti che inizia a diventare molto importante e ingestibile con una struttura condivisa come Godaddy.
Abbiamo pertanto illustrato il nostro modus operandi e illustrato i benefici di un tuning ad-hoc e relative ottimizzazioni ai servizi erogati.

Abbiamo installato il sistema operativo, fatto un tuning delle variabili di sistema, settato i vari limiti a livello di sistema operativo.

Abbiamo poi installato e configurato MySQL, PHP-FPM, Zend Opcache, Memcached, Redis.io, NGINX.

Abbiamo separato la parte dei contenuti statici (upload) in un vhost secondario di terzo livello : img.oltreuomo.com.

Abbiamo messo avanti a ognuno dei due host una CDN per limitare le connessioni in ingresso e il consumo di banda.

  • Incapsula per servire img.oltreuomo.com
  • Cloudflare per servire www.oltreuomo.com

A livello WordPress, abbiamo installato il plugin w3 Total cache e configurato in modo di cachare oggetti e query direttamente a livello memcached.
Ulteriormente abbiamo sostituito la index con uno script per funzionare come cacher su Redis per abbassare notevolmente il tempo di load delle pagine da mezzo secondo circa a 0,006 secondi o anche meno con oltre 2000 visitatori connessi.

redis

Il risultato ottenuto è quello di avere un sito WordPress velocissimo, ottimizzato per gestire un alto numero di visitatori simultanei.

Lo screenshot di Google Analytics unito al report di htop vale più di mille parole:

oltreuomo_tuning_wordpress

Qualora vogliate ottimizzare spingere WordPress, Magento o altri siti ai massimi livelli, contattateci pure per una consulenza sistemistica dedicata.