MySQL Optimize

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.

… contenuto visibile ai soli Amici