Vediamo come poter ottimizzare, verificare ed impostare la configurazione di MySQL sul vostro server, così da migliorare le prestazioni dei siti web ospitati che sono la causa del maggior consumo di risorse. Questa operazione è delicata, quindi suggerisco di esguire sempre dei backup delle situazioni precedenti o farsi assistere da un’amico preparato….
skip-name-resolve – Per impostazione predefinita, MariaDB è configurato per eseguire una ricerca DNS inversa del client che si connette al server. Questo viene fatto per consentire di impostare gli account utente con il nome host della macchina client. Tuttavia, la quantità di tempo necessaria per eseguire la ricerca inversa si somma e rallenta le prestazioni.
innodb_buffer_pool_size – Questa è una delle opzioni più importanti da impostare in MariaDB. Più avanti vedremo la regola per come calcolare un buon valore per questa impostazione se non si desidera utilizzare MySQLTuner. Essenzialmente questa opzione imposta quanta RAM può essere utilizzata da MariaDB per bufferizzare i dati della tabella dal motore di archiviazione InnoDB. La maggior parte dei software utilizza il motore di archiviazione InnoDB per il supporto ACID e chiave esterna .
innodb_log_file_size – Controlla la dimensione di ciascun file di registro di ripristino InnoDB. Non è consigliabile impostare questo valore predefinito in quanto si scaricherà troppo sul disco, causando un IO elevato. Invece, si consiglia di impostarlo al 25% di innodb_buffer_pool_size, ma tieni presente che l’aumento delle dimensioni rallenterà i tempi di arresto, avvio e ripristino.
max_heap_table_size / tmp_memory_table_size – La quantità massima di memoria che MariaDB consentirà per le tabelle MEMORY e le tabelle temporanee. Maggiore è la memoria consentita, maggiore è la tabella temporanea che è possibile creare che è effettivamente archiviata in memoria. Le tabelle temporanee vengono utilizzate da GROUP BY, UNION e sottoquery e l’aumento di questo valore può migliorare le prestazioni in caso di query complesse. Si consiglia di avere sia tmp_memory_table_size che max_heap_table_size impostati sullo stesso valore, poiché il limite inferiore viene utilizzato per le tabelle temporanee.
query_cache_size – Quanta memoria viene utilizzata per memorizzare nella cache i risultati delle query. Devi stare attento quando lo imposti; se lo imposti troppo alto può influire negativamente sulle prestazioni. Lo script MySQLTuner ti farà sapere quanto è efficace la cache delle query, che può essere un buon indicatore dell’efficacia delle modifiche apportate.
Abilita le tabelle InnoDB
MySQL e MariaDB utilizzano InnoDB come motore di archiviazione predefinito. InnoDB fornisce un approccio più flessibile e ogni informazione del database viene conservata in un .ibd file di dati. Ogni file .ibd rappresenta un proprio tablespace. In questo modo le operazioni del database come TRUNCATE possono essere completate più velocemente e puoi anche recuperare lo spazio inutilizzato durante l’eliminazione o il troncamento di una tabella del database.
Un altro vantaggio di questa configurazione è il fatto che è possibile conservare alcune delle tabelle del database in un dispositivo di archiviazione separato. Ciò può migliorare notevolmente il carico di I/O sui dischi.
Innodb_file_per_table è abilitato per impostazione predefinita dalle versione MySQL 5.6 e versioni successive. Puoi verificare se anche nel tuo caso sia così direttamente nel file /etc/my.cnf.
innodb_file_per_table=1
Aumentare le dimensioni del pool di buffer InnoDB
Il valore innodb_buffer_pool_size dovrebbe essere leggermente superiore alla dimensione totale utilizzata dalle tabelle InnoDB, ma tieni presente che valori più alti significano un maggiore utilizzo della memoria di sistema. Se si è limitati dalla memoria di sistema, è possibile utilizzare un valore inferiore poiché il pool di buffer funziona mantenendo i blocchi utilizzati di frequente in memoria per ridurre l’IO del disco. Tuttavia, non vuoi che sia troppo alto rispetto al punto in cui il tuo sistema sta scambiando la memoria su disco, annullando i vantaggi.
È possibile eseguire il seguente comando per elencare l’utilizzo della memoria da parte del motore di archiviazione per ottenere la dimensione delle tabelle:
mysql -e "SELECTengine, count(*) tables, concat(round(sum(table_rows)/1000000,2),'M') righe, concat(round(sum(data_length)/(1024*1024*1024),2 ),'G') data, concat(round(sum(index_length)/(1024*1024*1024),2),'G') idx, concat(round(sum(data_length+index_length)/(1024*1024* 1024),2),'G') total_size FROM information_schema.TABLES GROUPBYengineORDERBYsum(data_length+index_length) DESCLIMIT10"
Il risultato dell’esecuzione del comando precedente sarà simile a questo output.
+--------------------+--------+--------+--------+-------+------------+
| engine | tables | rows | data | idx | total_size |
+--------------------+--------+--------+--------+-------+------------+
| InnoDB | 15545 | 98.66M | 11.57G | 5.08G | 16.65G |
| MyISAM | 802 | 1.95M | 0.95G | 0.26G | 1.21G |
| MEMORY | 778 | 0.00M | 0.00G | 0.01G | 0.01G |
| CSV | 2 | 0.00M | 0.00G | 0.00G | 0.00G |
| PERFORMANCE_SCHEMA | 52 | 0.07M | 0.00G | 0.00G | 0.00G |
+--------------------+--------+--------+--------+-------+------------+
Nell’esempio precedente, InnoDB utilizza 16,65 GB di spazio di archiviazione. Si consiglia di impostare il pool di buffer su ¼ al di sopra della dimensione, che si traduce in un’impostazione di circa 23 GB.
NOTE
https://www.nexcess.net/help/mariadb-performance-tuning/
Sul sito trovi la continuazione dell'articolo per applicare il test di Benchmark MariaDB è sysbench
Il motore InnoDB ha un pool di buffer utilizzato per la memorizzazione nella cache dei dati ed indici in memoria. Questo ovviamente aiuterà le tue query MySQL/MariaDB ad essere eseguite molto più velocemente. La scelta della dimensione corretta qui richiede alcune decisioni molto importanti e una buona conoscenza del consumo di memoria del sistema.
Regola : dovresti regolare la dimensione del pool di buffer InnoDb = 80% RAM
Per esempio se il tuo server ha 1Gb di ram, il Buffer pool dovrebbe essere circa 80% del totale, quindi 800Mb lasciando i rimanenti 200Mb al sistema.
Oppure se per esempio se il tuo server ha 32Gb di ram, il Buffer pool dovrebbe essere circa 80% del totale, quindi 26Gb lasciando i rimanenti 6Gb al sistema.
A numeri inferiori, la nostra regola dell’80% sembra abbastanza ragionevole. Tuttavia, quando entriamo in server di grandi dimensioni, inizia a sembrare meno sensato. Affinché la regola sia valida, deve significare che il consumo di memoria del carico di lavoro aumenta in proporzione alla dimensione necessaria del pool di buffer, ma di solito non è così in tutti i casi.
innodb_buffer_pool_size 800M
Abilita registro query lente MySQL
La registrazione di query che richiedono molto tempo per essere eseguite semplifica la risoluzione dei problemi del database. Il registro delle query lente può essere abilitato aggiungendo le seguenti righe nel file di configurazione MySQL/MariaDB.
slow-query-log=1
slow-query-log-file= /var/lib/mysql/mysql-slow-query.log
long-query-time=1
Dove la prima variabile abilita il registro delle query lente
La seconda variabile definisce la directory del file di registro
La terza variabile definisce il tempo necessario per completare una query MySQL
Riavvia il servizio MySQL/MariaDB.
systemctl restart mysql
systemctl restart mariadb
Quindi monitora il registro
tail -f /var/lib/mysql/mysql-slow-query.log
Evita di usare Swappiness in MySQL
Il kernel Linux sposta parte della memoria in una partizione speciale del disco chiamata spazio di “scambio” quando il sistema esaurisce la memoria fisica. In questa condizione, il sistema scrive informazioni sul disco anziché liberare memoria. Poiché la memoria di sistema è più veloce dell’archiviazione su disco, si consiglia di disabilitare lo swappiness. Swappiness può essere disabilitato utilizzando il seguente comando.
[mysqld]
sysctl -w vm.swappiness=0
Sulla maggior parte dei server CentOS che hanno lo scambio abilitato, questo valore è in genere 60. Ciò significa che il server tenterà di utilizzare lo scambio quando raggiunge il 60% della RAM totale utilizzata. Per i server con una grande quantità di RAM, ciò potrebbe causare un utilizzo non necessario dell’I/O del disco.
Per modificare temporaneamente il valore, eseguire il comando seguente, modificando il valore numerico (un valore inferiore significa meno “scambio” e meno probabilità di scambio):
Non impostare il valore su 0 a meno che non si intenda disabilitare completamente lo scambio e si disponga di memoria sufficiente per ospitare tutte le funzioni . La disabilitazione dello scambio su un server che non dispone di memoria sufficiente può interrompere i processi per recuperare la memoria con conseguente possibile perdita e danneggiamento dei dati.
Puoi verificare il valore utilizzato per l’area di scambio sul server con il seguente comando: free -h
Disabilita la ricerca inversa DNS per MySQL
Quando viene ricevuta una nuova connessione, MySQL/MariaDB eseguirà una ricerca DNS per risolvere l’indirizzo IP dell’utente. Ciò potrebbe causare un ritardo quando la configurazione DNS non è valida o c’è un problema con il server DNS. Per disabilitare la ricerca DNS, aggiungi la seguente riga nel file di configurazione di MySQL e riavvia il servizio MySQL.
[mysqld]
skip-name-resolve
Controllare le connessioni inattive
Le risorse vengono consumate da connessioni inattive, quindi è necessario terminarle o aggiornarle, se possibile. Queste connessioni rimangono nello stato di “sospensione” e possono rimanere per un lungo periodo di tempo. Controllare le connessioni inattive utilizzando il seguente comando.
# mysqladmin processlist -u root -p | grep “Sleep”
La query elencherà i processi che si trovano nello stato di sospensione. Generalmente in PHP, l’evento può verificarsi quando si utilizza mysql_pconnect. Questo apre la connessione MySQL, esegue le query, rimuove le autenticazioni e lascia la connessione aperta. Utilizzando la direttiva wait_timeout , le connessioni inattive possono essere interrotte. Il valore predefinito per wait_timeout è 28800 secondi che può essere ridotto a un intervallo di tempo minimo come 60 secondi.
Imposta capacità massima della tabella di memoria
Max_heap_table_size è la variabile utilizzata in MySQL per configurare la capacità massima della tabella di memoria. La dimensione della capacità massima della tabella di memoria deve essere uguale alla capacità della tabella temporanea per evitare scritture su disco. Si consiglia di impostare questo valore sul server su 64 MB per GB di memoria. Aggiungi la seguente riga nel file di configurazione di MySQL e riavvia il servizio.
[mysqld]
max_heap_table_size=64M
Imposta tabella temporane
Tmp_table_size è lo spazio massimo utilizzato per la tabella di memoria incorporata. Se la dimensione della tabella supera il limite specificato, verrà convertita in una tabella MyISAM su disco. In MySQL/MariaDB, puoi aggiungere le seguenti variabili nel file di configurazione per impostare la dimensione della tabella temporanea. Si consiglia di impostare questo valore sul server 64M per ogni GB di memoria.
[mysqld]
tmp_table_size=64M
Imposta MySQL Max Connections
La direttiva max_connections indica al tuo server quante connessioni simultanee sono consentite. La connessione viene aperta solo per il tempo in cui viene eseguita la query MySQL, dopodiché viene chiusa e una nuova connessione può prendere il suo posto.
L’impostazione predefinita per max_connections è 100. Puoi vedere qual è l’impostazione corrente eseguendo il seguente comando SQL dal comando:
show variables like "max_connections";
Questo restituirà un set di risultati come questo
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 100 |
+-----------------+-------+
Tieni presente che troppe connessioni possono causare un elevato utilizzo della RAM e bloccare il tuo server MySQL. Di solito, i siti Web di piccole dimensioni richiedono tra 100 e 200 connessioni mentre quelli più grandi potrebbero richiedere 500-800 o anche di più. Il valore che applichi qui dipende fortemente dal tuo particolare utilizzo di MySQL/MariaDB.
Puoi modificare dinamicamente il valore di max_connections, senza dover riavviare il servizio MySQL eseguendo il comando SQL:
set global max_connections = 300;
Oppure puoi impostare my.cnf in questo modo
max_connections = 200
Regola per la connessione massima: è possibile calcolare la connessione massima utilizzando la formula:
RAM disponibile = Buffer globali + (Buffer thread x max_connections)
max_connections = (RAM disponibile – Buffer globali) / Buffer thread
Per ottenere l’elenco dei buffer e i relativi valori:
SHOW VARIABLES LIKE '%buffer%';
Ecco un elenco dei buffer e se sono globali o thread:
Buffer globali : key_buffer_size, innodb_buffer_pool_size, innodb_log_buffer_size, innodb_additional_mem_pool_size, net_buffer_size, query_cache_size
Buffer di thread : sort_buffer_size, myisam_sort_buffer_size, read_buffer_size, join_buffer_size, read_rnd_buffer_size, thread_stack
Configura MySQL query_cache_size
Da MySQL 5.6.8, query_cache_type è impostato su OFF per impostazione predefinita. Quindi, se non l’hai attivato esplicitamente sulla vecchia versione, potrebbe non funzionare più!
Se hai molte query ripetitive e i tuoi dati non cambiano spesso, dovresti usare una query cache . Le persone spesso non comprendono il concetto alla base query_cache_size e impostano questo valore su gigabyte, il che può effettivamente causare un degrado delle prestazioni.
La ragione di ciò è il fatto che i thread devono bloccare la cache durante gli aggiornamenti. Di solito un valore di 200-300 MB dovrebbe essere più che sufficiente. Se il tuo sito web è relativamente piccolo, puoi provare a dare il valore di 64Mb e ad aumentare nel tempo.
L’aumento del valore della dimensione della cache delle query a GB non è consigliato in quanto potrebbe ridurre le prestazioni del database.
Controlla lo stato corrente di query_cache
mysql -e "show variables like 'query_cache_%'"
+------------------------------+-----------+
| Variable_name | Value |
+------------------------------+-----------+
| query_cache_limit | 2097152 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 268435456 |
| query_cache_strip_comments | ON |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+------------------------------+-----------+
Dovrai aggiungere le seguenti impostazioni nel file di configurazione di MySQL:
query_cache_type = 1
query_cache_limit = 256K
query_cache_min_res_unit = 2k
query_cache_size = 80M
MySQL Tuner
MySQL Tuner è un ottimo script Perl progettato per MySQL e MariaDB per aiutarti a impostare le configurazioni di base che la maggior parte delle persone consiglia di regolare per le prestazioni. Per ottenere i migliori risultati in termini di prestazioni per il tuo database, ti consigliamo di installare lo schema di sistema (sysschema) per il tuo sistema e di abilitare lo schema delle prestazioni in modo che MariaDB possa raccogliere informazioni sulle query eseguite dal tuo sistema. Il file readme include una buona quantità di informazioni sulla configurazione e l’utilizzo di questo strumento.à.
MySQLTuner è uno script scritto in Perl che consente di rivedere rapidamente un’installazione di MySQL e apportare modifiche per aumentare le prestazioni e la stabilità. Le variabili di configurazione correnti e i dati di stato vengono recuperati e presentati in un breve formato insieme ad alcuni suggerimenti sulle prestazioni di base.
MySQLTuner supporta ~300 indicatori per MySQL / MariaDB / Percona Server nell’ultima versione.
Procediamo nello scaricare lo script MySQL Tuner direttamente sul vostro server ed eseguiamolo nel seguente modo. Creiamo anche una directory dove contenerlo.
cd /
mkdir mysqltuner
wget http://mysqltuner.pl/ -O mysqltuner.pl
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/basic_passwords.txt -O basic_passwords.txt
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/vulnerabilities.csv -O vulnerabilities.csv
Per impostazione predefinita, su MariaDB, lo schema delle prestazioni è disabilitato per impostazione predefinita. prendere in considerazione l’attivazione dello schema delle prestazioni nel file di configurazione my.cnf:
nano /etc/my.cnf
[mysqld]
performance_schema = on
Gli aggiornamenti delle statistiche dei metadati possono avere un forte impatto sulle prestazioni dei server di database e di MySQLTuner. Assicurati che innodb_stats_on_metadata sia disabilitato.
set global innodb_stats_on_metadata = 0;
Utilizzo: utilizzo minimo a livello locale
perl mysqltuner.pl --host 127.0.0.1
Naturalmente, puoi aggiungere il bit di esecuzione ( chmod +x mysqltuner.pl
) in modo da poterlo eseguire senza chiamare direttamente perl.
chmod +x mysqltuner.pl
Utilizzo: abilita le informazioni di output massime su MySQL/MariaDb senza eseguire il debug
perl mysqltuner.pl --verbose perl mysqltuner.pl --buffers --dbstat --idxstat --sysstat --pfstat --tbstat
Utilizzo: abilita il controllo delle vulnerabilità CVE per la tua versione di MariaDB o MySQL
perl mysqltuner.pl --cvefile=vulnerabilities.csv
Utilizzo: scrivere il risultato in un file con le informazioni visualizzate
perl mysqltuner.pl --outputfile /tmp/result_mysqltuner.txt
Utilizzo: aggiorna MySQLTuner e i file di dati (password e cve) se necessario
perl mysqltuner.pl --checkversion --updateversion
Se vengono apportate delle modifiche al file my.cnf in funzione dei suggerimenti di MySQL Tuner, in primo luogo fate un backup del file e alla fine delle stesse modifiche riavviate il servizio MySQL
/scripts/restartsrv_mysql
Di seguito una prova di ottimizzazione di un server VPS
STANDARD Apache Configuration
SSL Cipher Suite [?]
ECDHE-ECDSA-CHACHA20-POLY1305:ECDHE-RSA-CHACHA20-POLY1305:ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-RSA-AES256-GCM-SHA384:DHE-RSA-AES128-GCM-SHA256:DHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-AES128-SHA256:ECDHE-RSA-AES128-SHA256:ECDHE-ECDSA-AES128-SHA:ECDHE-RSA-AES256-SHA384:ECDHE-RSA-AES128-SHA:ECDHE-ECDSA-AES256-SHA384:ECDHE-ECDSA-AES256-SHA:ECDHE-RSA-AES256-SHA:DHE-RSA-AES128-SHA256:DHE-RSA-AES128-SHA:DHE-RSA-AES256-SHA256:DHE-RSA-AES256-SHA:ECDHE-ECDSA-DES-CBC3-SHA:ECDHE-RSA-DES-CBC3-SHA:EDH-RSA-DES-CBC3-SHA:AES128-GCM-SHA256:AES256-GCM-SHA384:AES128-SHA256:AES256-SHA256:AES128-SHA:AES256-SHA:DES-CBC3-SHA:!DSS default
SSL/TLS Protocols [?] – All -SSLv2 -SSLv3 default
LogLevel [?] – System Default: warn
Trace Enable [?] – System Default: Off
PCI Recommendation: Off
Server Signature [?] – Off
System Default: Off
PCI Recommendation: Off
Server Tokens [?] – Minimal
System Default: ProductOnly
PCI Recommendation: ProductOnly
File ETag [?] – None
System Default: None
PCI Recommendation: None
Directory “/” Options [?]
x ExecCGI default
x FollowSymLinks default
Includes
x IncludesNOEXEC default
x Indexes default
MultiViews
x SymLinksIfOwnerMatch default
Start Servers [?] – 5 – NEW 8
5 default
Minimum Spare Servers [?] – 5 – NEW 8
5 default
Maximum Spare Servers [?] – 10 – NEW 16
10 default
Server Limit (Maximum: 20,000) [?] – 256
256 default
Max Request Workers [?] – 150
150 default
Max Connections Per Child [?] – 10000
10000 default
Keep-Alive [?] – On
On default
Off
Keep-Alive Timeout [?] – 5
5 default
Max Keep-Alive Requests [?] – 100 – NEW 120
Unlimited
100 default
Timeout [?] – 300 – NEW 60
300 default
[/private]