Ricerca nel sito web

15 utili suggerimenti per la regolazione e l'ottimizzazione delle prestazioni di MySQL/MariaDB

MySQL è un potente sistema di gestione di database relazionali open source o in breve RDBMS. È stato rilasciato nel 1995 (20 anni). Utilizza Structured Query Language che è probabilmente la scelta più popolare per la gestione dei contenuti all'interno di un database. L'ultima versione di MySQL è 5.6.25 ed è stata rilasciata il 29 maggio 2015.

Un fatto interessante su MySQL è il fatto che il nome deriva dalla figlia di Michael Widenius (il creatore di MySQL), My. Anche se ci sono molti fatti interessanti su MySQL, questo articolo ha lo scopo di mostrarti alcune pratiche utili per aiutarti a gestire il tuo server MySQL.

Nell'aprile 2009 il progetto MySQL è stato acquistato da Oracle. Di conseguenza è stato creato un fork della comunità MySQL chiamato MariaDB. Il motivo principale per cui è stato creato il fork è stato quello di mantenere il progetto libero sotto la General Public License.

Oggi MySQL e MariaDB sono uno degli RDBMS più (se non il più) utilizzati per applicazioni web come WordPress, Joomla, Magento e altri.

Questo articolo ti mostrerà alcuni suggerimenti di base ma utili su come ottimizzare le prestazioni di MySQL/MariaDB. Tieni presente che questo articolo presuppone che tu abbia già installato MySQL o MariaDB. Se ti stai ancora chiedendo come installarli sul tuo sistema, puoi seguire le nostre guide approfondite qui:

  1. Installazione di LAMP su RHEL/CentOS 7
  2. Installazione di LAMP su Fedora 22
  3. Configurazione di LAMP su Ubuntu 15.04
  4. Installazione di MariaDB su Debian 8
  5. Installa MariaDB su Gentoo Linux
  6. Installa MariaDB su Arch Linux

Importante: prima di iniziare, non accettare ciecamente questi suggerimenti. Ogni configurazione MySQL è unica e richiede una riflessione aggiuntiva prima di apportare qualsiasi modifica.

Cose che devi sapere:

  1. Il file di configurazione di MySQL/MariaDB si trova in /etc/my.cnf. Ogni volta che modifichi questo file dovrai riavviare il servizio MySQL affinché le nuove modifiche possano avere effetto.
  2. Per scrivere questo articolo è stata utilizzata come modello la versione 5.6 di MySQL.

1. Abilita file per tabella InnoDB

Innanzitutto è importante spiegare che InnoDB è un motore di archiviazione. MySQL e MariaDB utilizzano InnoDB come motore di archiviazione predefinito. In passato MySQL conservava le tabelle e gli indici del database in uno spazio tabelle di sistema. Questo approccio è stato pensato per i server il cui unico scopo è l'elaborazione del database e il loro disco di archiviazione non viene utilizzato per altri scopi.

InnoDB fornisce un approccio più flessibile e le informazioni di ogni database vengono conservate in un file di dati .ibd. 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 spazio inutilizzato quando elimini o tronchi una tabella del database.

Un altro vantaggio di questa configurazione è il fatto che è possibile conservare alcune tabelle del database in un dispositivo di archiviazione separato. Ciò può migliorare notevolmente il carico di I/O sui tuoi dischi.

innodb_file_per_table è abilitato per impostazione predefinita in MySQL 5.6 e versioni successive. Puoi vederlo nel file /etc/my.cnf. La direttiva si presenta così:

innodb_file_per_table=1

2. Memorizza i dati del database MySQL su una partizione separata

Nota: questa configurazione funziona solo con MySQL, ma non con MariaDB.

A volte le operazioni di lettura/scrittura del sistema operativo possono rallentare le prestazioni del server MySQL, soprattutto se si trova sullo stesso disco rigido. Consiglierei invece di utilizzare un disco rigido separato (preferibilmente SSD) per il servizio MySQL.

Per completare l'operazione dovrai collegare la nuova unità al tuo computer/server. Ai fini di questo articolo, presumo che l'unità si trovi in /dev/sdb.

Il passo successivo è preparare la nuova unità:

fdisk /dev/sdb

Ora premi "n" per creare una nuova partizione. Quindi premi "p" per rendere primaria la nuova partizione. Successivamente, imposta il numero della partizione da 1-4. Successivamente selezionerai la dimensione della partizione. Premi Invio qui. Nel passaggio successivo dovrai configurare la dimensione della partizione.

Se desideri utilizzare l'intero disco, premi nuovamente Invio. Altrimenti puoi impostare manualmente la dimensione della nuova partizione. Quando sei pronto, premi "w" per scrivere le modifiche. Ora dovremo creare un filesystem per la nostra nuova partizione. Questo può essere fatto facilmente con:

mkfs.ext4 /dev/sdb1

Ora monteremo la nostra nuova partizione in una cartella. Ho chiamato la mia cartella "ssd" e ho creato nella directory principale:

mkdir /ssd/

Siamo pronti per montare la nuova partizione che abbiamo appena creato nella nuova cartella:

mount /dev/sdb1  /ssd/

Puoi eseguire il montaggio all'avvio aggiungendo la seguente riga nel file /etc/fstab.

/dev/sdb1 /ssd ext3 defaults 0 0

Ora sei pronto per spostare MySQL sul nuovo disco. Per prima cosa interrompi il servizio MySQL con:

service mysqld stop

Ti consiglierei di fermare anche Apache/nginx per impedire qualsiasi tentativo di scrivere nei database:

service httpd stop
service nginx stop

Ora copia l'intera directory MySQL nella nuova unità:

cp /var/lib/mysql /ssd/ -Rp

L'operazione potrebbe richiedere del tempo a seconda del sito dei database MySQL. Una volta completato questo processo, rinominare la directory MySQL:

mv /var/lib/mysql /var/lib/mysql-backup

Successivamente creeremo un collegamento simbolico.

ln -s /ssd/mysql /var/lib/mysql

Ora sei pronto per avviare MySQL e il servizio web:

service mysqld start
service httpd start
service nginx start

A questo punto sarà possibile accedere ai database MySQL dalla nuova unità.