MySQL e MariaDB sono i sistemi di gestione di database relazionali (RDMS) più utilizzati quando si tratta di hosting di siti Web e sistemi CMS come Joomla, WordPress, Drupal e Typo 3. In questo articolo spiegherò come velocizzare e ottimizzare il tuo Server di database MySQL e MariaDB.
Memorizza i dati MySQL in partizioni separate
Quando si tratta di ottimizzazione e garanzia, è sempre l'idea migliore archiviare i dati del database in un volume separato. I volumi sono specifici per volumi di archiviazione veloci come SSD, NVMe. Anche se il tuo sistema si guasta, avrai il tuo database al sicuro. Poiché il volume della partizione è costituito da volumi di archiviazione veloci, le prestazioni saranno più veloci.
Imposta il numero massimo di connessioni MySQL
MySQL/MariaDB utilizza un'istruzione max_connections che specifica quante connessioni simultanee sono attualmente consentite nel server. Troppe connessioni comportano un consumo di memoria elevato e un carico elevato della CPU. Per i siti Web di piccole dimensioni, le connessioni possono essere specificate tra 100-200 e quelle più grandi potrebbero richiedere 500-800 e più. Le connessione_max può essere modificato dinamicamente utilizzando la query SQL. In questo esempio, ho impostato il valore su 200.
$ mysql -u root -p
mysql> set global max_connections=200;
Uscita :
Abilita registro query lente MySQL
La registrazione delle query che richiedono molto tempo per l'esecuzione semplifica la risoluzione dei problemi del database. Il registro delle query lente può essere abilitato aggiungendo le seguenti righe nel file di configurazione di 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 log delle query lente
La seconda variabile definisce la directory del file di registro
La terza variabile definisce il tempo per completare una query MySQL
Riavvia il servizio mysql/mariadb e monitora il log
$ systemctl restart mysql
$ systemctl restart mariadb
$ tail -f /var/lib/mysql/mysql-slow-query.log
Imposta il pacchetto massimo consentito da MySQL
I dati sono suddivisi in pacchetti in MySQL. Max_allowed_packet definisce la dimensione massima dei pacchetti che possono essere inviati. L'impostazione di max_allowed_packet troppo basso può causare una query troppo lenta. Si consiglia di impostare il valore del pacchetto sulla dimensione del pacchetto più grande.
Impostazione della capacità della tabella temporanea
Tmp_table_size è lo spazio massimo utilizzato per la tabella della memoria interna. 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 64 M per GB di memoria.
[mysqld] tmp_table_size=64M
Riavvia il servizio MySQL
$ systemctl restart mysql
$ systemctl restart mariadb
Imposta la 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 la stessa della capacità della tabella temporanea per evitare scritture su disco. Si consiglia di impostare questo valore sul server su 64 M per GB di memoria. Aggiungi la seguente riga nel file di configurazione di MySQL e riavvia il servizio.
[mysqld] max_heap_table_size=64M
Per applicare le modifiche, riavvia il server del database.
$ systemctl restart mysql
$ systemctl restart mariadb
Disabilita la ricerca inversa DNS per MySQL
Quando viene ricevuta una nuova connessione, MySQL/MariaDB eseguirà la ricerca DNS per risolvere l'indirizzo IP dell'utente. Ciò potrebbe causare un ritardo quando la configurazione DNS non è valida o si verifica 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
Riavvia il servizio:
$ systemctl restart mysql
$ systemctl restart mariadb
Evita di usare Swappiness in MySQL
Il kernel Linux sposta parte della memoria in una partizione speciale del disco chiamata spazio "swap" quando il sistema esaurisce la memoria fisica. In questa condizione, il sistema scrive le informazioni sul disco invece di liberare memoria. Poiché la memoria di sistema è più veloce della memoria su disco, si consiglia di disabilitare lo swappiness. Swappiness può essere disabilitato utilizzando il comando seguente.
$ sysctl -w vm.swappiness=0
Uscita :
Aumenta la dimensione del pool di buffer InnoDB
MySQL/MariaDB ha un motore InnoDB che dispone di un pool di buffer per memorizzare nella cache e indicizzare i dati nella memoria. Il pool di buffer consente di eseguire le query MySQL/MariaDB in modo relativamente più veloce. La scelta della dimensione corretta del pool di buffer InnoDB richiede una certa conoscenza della memoria di sistema. L'idea migliore è impostare il valore della dimensione del pool di buffer InnoDB sull'80% della RAM.
Esempio.
- Memoria di sistema =4 GB
- Dimensione del pool di buffer =3,2 GB
Aggiungi la seguente riga nel file di configurazione di MySQL e riavvia il servizio
[mysqld] Innodb_buffer_pool_size 3.2G
Riavvia il database:
$ systemctl restart mysql
$ systemctl restart mariadb
Gestire la dimensione della cache delle query
La direttiva Query cache in MySQL/MariaDB viene utilizzata per memorizzare nella cache tutte le query che continuano a ripetersi con gli stessi dati. Si consiglia di impostare il valore su 64 MB e aumentare il tempo per i siti Web di piccole dimensioni. Non è consigliabile aumentare il valore della dimensione della cache delle query a GB in quanto potrebbe ridurre le prestazioni del database. Aggiungi la seguente riga nel file my.cnf.
[mysqld] query_cache_size=64M
Verifica connessioni inattive
Le risorse vengono consumate dalle connessioni inattive in modo che debbano essere terminate o aggiornate, se possibile. Queste connessioni rimangono nello stato "sleep" e possono rimanere per un lungo periodo di tempo. Verificare le connessioni inattive utilizzando il seguente comando.
$ mysqladmin processlist -u root -p | grep “Sleep”
La query elencherà i processi che sono in 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 wait_timeout direttiva, le connessioni inattive possono essere interrotte. Il valore predefinito per wait_timeout è 28800 secondi che possono essere ridotti a un intervallo di tempo minimo come 60 secondi. Aggiungi la seguente riga nel file my.cnf
[mysqld] wait_timeout=60
Ottimizza e ripara il database MySQL
Se il server viene arrestato in modo imprevisto, è possibile che le tabelle in MySQL/MariaDB possano arrestarsi in modo anomalo. Esistono altri possibili motivi per ottenere un arresto anomalo della tabella del database, ad esempio l'accesso al database mentre il processo di copia è in esecuzione, il file system viene improvvisamente arrestato in modo anomalo. In questa situazione, abbiamo uno strumento speciale chiamato "mysqlcheck ” che controlla, ripara e ottimizza tutte le tabelle nei database.
Utilizzare il comando seguente per eseguire le attività di riparazione e ottimizzazione.
Per tutti i database:
$ mysqlcheck -u root -p --auto-repair --check --optimize --all-databases
Per database specifico:
$ mysqlcheck -u root -p --auto-repair --check --optimize dbname
Sostituisci dbname con il nome del tuo database
- Verifica le prestazioni di MySQL/MariaDB utilizzando strumenti di test
È buona norma controllare regolarmente le prestazioni del database MySQL/MariaDB. In questo modo sarà facile ottenere il rapporto sulle prestazioni e il punto di miglioramento. Ci sono molti strumenti disponibili tra cui mysqltuner è il migliore.
Esegui il comando seguente per scaricare lo strumento
$ wget https://github.com/major/MySQLTuner-perl/tarball/master
Decomprimi il file
$ tar xvzf master
Vai alla directory del progetto ed esegui il seguente script.
$ cd major-MySQLTuner-perl-7aa57fa
$ ./mysqltuner.pl
Uscita:
Conclusione
In questo articolo abbiamo imparato come ottimizzare MySQL/MariaDB utilizzando diverse tecniche. Grazie per aver letto.