di Vitux
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 database MySQL e MariaDB.
Memorizza i dati MySQL nelle partizioni separate
Quando si arriva al punto 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 fallirà, 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 elevato consumo di memoria e un elevato carico della CPU. Per i piccoli siti Web, le connessioni possono essere specificate a 100-200 e quelle più grandi potrebbero aver bisogno di 500-800 e più. Le max_connections possono essere modificate dinamicamente utilizzando la query SQL. In questo esempio, ho impostato il valore su 200.
$ mysql -u root -p
mysql> imposta global max_connections=200;
Produzione :
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.
registro-query-lento=1 file-log-query-lento= /var/lib/mysql/mysql-query-slow.log tempo-interrogazione lungo=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 e monitora il registro
$ systemctl riavvia mysql
$ systemctl riavvia mariadb
$ tail -f /var/lib/mysql/mysql-slow-query.log
Imposta il pacchetto massimo consentito da MySQL
I dati vengono suddivisi nei pacchetti in MySQL. Max_allowed_packet definisce la dimensione massima dei pacchetti che possono essere inviati. L’impostazione di max_allowed_packet su un valore 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 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 GB di memoria.
[mysqld] tmp_table_size=64M
Riavvia il servizio mysql
$ systemctl riavvia mysql
$ systemctl riavvia mariadb
Impostare 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 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
Per applicare le modifiche, riavviare il server del database.
$ systemctl riavvia mysql
$ systemctl riavvia mariadb
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-nome-risolvere
Riavvia il servizio:
$ systemctl riavvia mysql
$ systemctl riavvia mariadb
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.
$ sysctl -w vm.swappiness=0
Produzione :
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ù rapido. 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 all’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 riavvia mysql
$ systemctl riavvia mariadb
Gestione delle dimensioni 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. L’aumento del valore della dimensione della cache delle query a GB non è consigliato in quanto potrebbe ridurre le prestazioni del database. Aggiungere la riga seguente nel file my.cnf.
[mysqld] query_cache_size=64M
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 "Dormi"
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. Aggiungere la riga seguente nel file my.cnf
[mysqld] wait_timeout=60
Ottimizza e ripara il database MySQL
Se il server viene spento in modo imprevisto, è possibile che le tabelle in MySQL/MariaDB possano arrestarsi in modo anomalo. Ci sono altri possibili motivi per ottenere il crash della tabella del database come l’accesso al database mentre il processo di copia è in esecuzione, il file system si blocca improvvisamente. In questa situazione, abbiamo uno strumento speciale chiamato ” mysqlcheck ” che controlla, ripara e ottimizza tutte le tabelle nei database.
Utilizzare il seguente comando per eseguire le attività di riparazione e ottimizzazione.
Per tutti i database:
$ mysqlcheck -u root -p --riparazione automatica --check --optimize --all-databases
Per database specifico:
$ mysqlcheck -u root -p --riparazione automatica --check --optimize nomedb
Sostituisci dbname con il nome del tuo database
- Controlla le prestazioni di MySQL/MariaDB utilizzando gli strumenti di test
È consigliabile 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.
Eseguire il comando seguente per scaricare lo strumento
$ wget https://github.com/major/MySQLTuner-perl/tarball/master
Decomprimere il file
$ tar xvzf maestro
Vai alla directory del progetto ed esegui il seguente script.
$ cd maggiore-MySQLTuner-perl-7aa57fa
$ ./mysqltuner.pl
Produzione:
Conclusione
In questo articolo abbiamo imparato come ottimizzare MySQL/MariaDB utilizzando diverse tecniche. Grazie per aver letto.