mysql-web

Tuning di MySQL: come ottimizzare le performance

Indice dei contenuti

MySql Tuning

MySQL è il database relazionale più utilizzato su scala globale per contenere la base di dati dei siti web attivi su Internet.

 
Possiamo affermare che è lo standard adottato da tutti i CMS più noti come: WordPress, Prestashop e Joomla. Molto spesso, per ottimizzare la velocità di un sito, si tiene conto solamente degli elementi che compongono il layer più alto come, ad esempio, eventuali plugin e più generalmente tutti gli elementi contenuti nella pagina ma anche le query SQL, che vengono processate per rendere il sito dinamico, impattano in maniera significativa sulle performance dell’applicazione.

Vediamo di seguito alcuni consigli che consentono la configurazione del server database secondo le vostre esigenze.

Tali personalizzazioni potranno essere applicate su tutti i nostri prodotti cloud, a partire dal Cloud Hosting fino all’Easy Cloud e quindi al Cloud Server. Iniziamo l’argomento discutendo della variabile key_buffer: tra le più importanti ai fini prestazionali del servizio. Si tratta di un’area di memoria destinata a memorizzare gli indici delle tabelle di tipo MyISAM. Quando non è sufficientemente capiente, MySQL deve accedere al disco per leggere gli indici, con conseguenti rallentamenti.

Per verificarlo, basterà eseguire il comando:

show global status like '%key_read%';

ed analizzare l’output relativo al campo Key_reads.

Questo è un vero e proprio “contatore” che enumera le volte che il servizio accede al file system per leggere i dati anzichè prelevarli dalla memoria (Key_read_requests).

E’ opportuno mantenere la percentuale di indici letti in RAM intorno al 90-100% e per calcolare questo valore, basterà utilizzare questa semplice formula:

(1 - Key_reads/Key_read_requests) * 100

Nel caso in cui non sia sufficiente, è necessario aumentare il key_buffer.

Un altro aspetto importante per velocizzare il tempo di esecuzione delle query, è quello di evitare che determinate operazioni, come ad esempio quelle processate con l’ordinamento oppure le join, scrivono tabelle temporanee su disco anzichè in memoria.

In questo caso occorre modificare il valore delle direttive tmp_table_size e max_heap_table_size dopo avere analizzato con il comando

SHOW GLOBAL STATUS LIKE 'Created_tmp%';

quante tabelle vengono memorizzate su file system (Created_tmp_disk_tables) in confronto a quelle totali generate (Created_tmp_tables).

Se il rapporto è superiore al 30%, è opportuno aumentare i valori di tmp_table_size e max_heap_table_size.

Il terzo parametro che andremo a discutere è table_open_cache, ovvero la cache delle tabelle aperte da MySQL in un determinato istante (in base ai thread attivi in quel momento) che può essere salvato in memoria.

Per calcolarlo si deve verificare il numero totale di tabelle con il comando

SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE';

Controllare quanti thread ci sono attivi in quel momento con

show global status like 'Threads_connected';

e configurare il valore di table_open_cache in questo modo

table_open_cache = total_tables * threads_connected * 0.50

La costante 0.50 è stata inserita per dimezzare il valore totale rendendo il risultato più vicino possibile a un caso reale di utilizzo di MySQL, in quanto difficilmente tutti i thread accederanno a tutte le tabelle contemporaneamente.

Strettamente correlato a table_open_cache c’è il numero massimo di file che può gestire in maniera concorrente MySQL, quindi la variabile open_files_limit, che può essere impostata nel modo seguente:

open_files_limit = table_open_cache * 2

Vediamo quindi come settare la dimensione della cache destinata ai thread non utilizzati in quel determinato momento da una connessione ma che sono disponibili a processare nuove query.

E’ particolarmente importante nel caso in cui il servizio processa una notevole quantità di connessioni di breve durata.

thread_cache_size va aumentato finchè l’hit rate non sarà il più vicino possibile al 100%

Si dovrà verificare il numero di connessioni effettuate e quindi il numero di thread creati dall’avvio del servizio.

Questi sono i comandi:

SHOW STATUS LIKE 'threads_created';
SHOW STATUS LIKE 'connections';

Dopodichè l’efficienza potrà essere calcolata applicando questa formula:

100 - ((Threads_created / Connections) * 100)

CONDIVIDI SUI SOCIAL

Lascia un commento

Il tuo indirizzo email non sarà pubblicato. I campi obbligatori sono contrassegnati *

− 1 = 6