Ottimizzazione delle variabili di sistema MySQL per prestazioni elevate

Per la maggior parte degli sviluppatori di applicazioni, il database è un altare di divinità demoniache da lasciare inavvicinabili. Ma non deve essere così!


A parità di altre condizioni, il livello di comfort di uno sviluppatore con il database sottostante definisce il livello di anzianità. Piccolo database e poca esperienza di programmazione = sviluppatore junior; piccolo database e buona esperienza di programmazione = sviluppatore di medio livello; buon database e buona esperienza di programmazione = sviluppatore senior.

È una dura realtà che persino gli sviluppatori con 6-8 anni sotto la cintura lottano per spiegare le complessità dell’ottimizzatore delle query e preferiscono guardare verso il cielo quando viene chiesto di ottimizzazione del database.

Perché?

Sorprendentemente, la ragione non è la pigrizia (anche se in parte lo è).

Il punto è che i database sono una forza propria con cui confrontarsi. Anche tradizionalmente, quando c’erano solo i tipi di database relazionali da affrontare, padroneggiarli era un miracolo e un percorso di carriera a sé stante; al giorno d’oggi, abbiamo così tanti tipi di database che è impossibile aspettarsi che un’unica anima mortale padroneggi tutto.

Detto questo, ci sono buone probabilità che tu sia ancora soddisfatto dei database relazionali o faccia parte di un team che ha un prodotto in esecuzione su un database relazionale in modo soddisfacente per molto, molto tempo. E in nove casi su dieci, sei su MySQL (o MariaDB). In questi casi, immergersi un po ‘più in profondità sotto il cofano offre enormi vantaggi nel migliorare le prestazioni dell’applicazione e vale la pena imparare.

Curioso? Immergiamoci!

Non curioso? Bene, tuffati comunque, perché la tua carriera dipende da questo! ��

Ottimizza la cache delle query MySQL

Quasi tutta l’ottimizzazione nel campo dei computer si riduce alla memorizzazione nella cache. Da un lato, la CPU mantiene diversi livelli di cache per accelerare i suoi calcoli e, dall’altro, le app Web fanno un uso aggressivo di soluzioni di cache come Redis per server risultati precalcolati per gli utenti piuttosto che colpire il database ogni volta.

Ma ehi, anche il povero database MySQL ha una propria cache di query! Cioè, ogni volta che esegui una query su qualcosa e i dati sono ancora obsoleti, MySQL fornirà questi risultati memorizzati nella cache anziché eseguire nuovamente la query, rendendo l’app incredibilmente più veloce.

È possibile verificare se la cache delle query è disponibile (nota, disponibile, non abilitata) nel database eseguendo questa query nella console del database:

MariaDB [(nessuno)]> MOSTRA VARIABILI COME ‘have_query_cache’;
+——————+——-+
| Nome variabile | Valore |
+——————+——-+
| have_query_cache | SÌ |
+——————+——-+

Quindi, puoi vedere che sto eseguendo MariaDB e che ho la cache delle query disponibile per essere attivata. È estremamente improbabile che tu lo spegni se utilizzi un’installazione MySQL standard.

Ora vediamo se la cache delle query è effettivamente attivata:

MariaDB [(nessuno)]> MOSTRA VARIABILI COME ‘query_cache_type’;
+——————+——-+
| Nome variabile | Valore |
+——————+——-+
| query_cache_type | ON |
+——————+——-+

Sì, certamente. Ma in caso contrario, puoi accenderlo dicendo:

MariaDB [(nessuno)]> SET GLOBAL query_cache_type = ON;

È interessante notare che questa variabile accetta anche un terzo valore che indica “on-demand”, il che significa che MySQL memorizzerà nella cache solo le query a cui le diciamo, ma non ne parleremo qui.

Con questo, hai la cache di query attiva e hai fatto il primo passo verso una configurazione MySQL più solida! Dico il primo passo perché durante l’attivazione è un grande miglioramento, è necessario ottimizzare la cache delle query per adattarsi alla nostra configurazione. Quindi impariamo a farlo.

L’altra variabile di interesse qui è query_cache_size, la cui funzione è autoesplicativa:

MariaDB [(nessuno)]> MOSTRA VARIABILI COME ‘query_cache_size’;
+——————+———-+
| Nome variabile | Valore |
+——————+———-+
| query_cache_size | 16777216 |
+——————+———-+

Quindi, ho una cache di query di circa 16 MB di dimensione. Si noti che anche se la memorizzazione nella cache delle query è attivata, ma questa dimensione è zero, la memorizzazione nella cache è effettivamente disattivata. Ecco perché il controllo di una sola variabile non è sufficiente. Ora, dovresti impostare una dimensione della cache della query, ma quanto dovrebbe essere? Prima di tutto, tieni presente che la funzione di memorizzazione nella cache delle query avrà bisogno di 4 KB per archiviare i suoi metadati, quindi qualsiasi cosa tu scelga dovrebbe essere superiore a quella.

Supponiamo che tu abbia impostato la dimensione della cache della query su 500 KB:

MariaDB [(nessuno)]> SET GLOBAL query_cache_size = 500000;

Lo sta facendo abbastanza? Bene, perché il modo in cui il motore di query finirà per funzionare dipende da un paio di cose in più:

  • Prima di tutto, la variabile query_cache_size deve essere abbastanza grande da contenere il risultato delle tue query. Se è troppo piccolo, nulla verrà memorizzato nella cache.
  • In secondo luogo, se query_cache_size è impostato su un numero troppo alto, ci saranno due tipi di problemi: 1) Il motore dovrà fare un lavoro extra per archiviare e localizzare i risultati delle query in questa enorme area di memoria. 2) Se la maggior parte delle query si traduce in dimensioni molto più ridotte, la cache verrà frammentata e i vantaggi dell’utilizzo di una cache andranno persi.

Come fai a sapere se la cache viene frammentata? Controlla il numero totale di blocchi nella cache in questo modo:

MariaDB [(nessuno)]> mostra lo stato come ‘Qcache_total_blocks’;
+———————+——-+
| Nome variabile | Valore |
+———————+——-+
| Qcache_total_blocks | 33 |
+———————+——-+

Se il numero è molto elevato, la cache è frammentata e deve essere svuotata.

Quindi, per evitare questi problemi, assicurati che la dimensione di query_cache_size sia scelta saggiamente. Se ti senti frustrato di non averti lasciato un numero concreto qui, temo che le cose stiano così una volta che passi dallo sviluppo e passi all’ingegneria. È necessario esaminare l’app in esecuzione e vedere quali sono le dimensioni della query per i risultati della query importanti, quindi impostare questo numero. E anche allora potresti finire per fare un errore. ��

Threading, pool di thread, attesa e timeout

Questa è probabilmente la parte più interessante di come funziona MySQL e farlo correttamente significa rendere la tua app più volte più veloce!

Threading

MySQL è un server multi-thread. Ciò significa che, ogni volta che c’è una nuova connessione al server MySQL, apre un nuovo thread con i dati di connessione e passa un handle al client (nel caso in cui ti stia chiedendo che cos’è un thread, vedi Questo). Il client invia quindi tutte le query su questo thread e riceve i risultati. Questo ci porta a porre una domanda naturale: quanti thread può far girare MySQL? La risposta si trova nella sezione successiva.

Pool di thread

Nessun programma in un sistema informatico può aprire tutti i thread che vuole. Il motivo è duplice: 1) I thread costano la memoria (RAM) e il sistema operativo non ti consente di impazzire e divorare tutto. 2) Gestire, per esempio, un milione di thread è un compito enorme da solo, e se il server MySQL potesse creare tanti thread, morirebbe nel tentativo di gestire l’overhead.

Per evitare questi problemi, MySQL viene fornito con un pool di thread, un numero fisso di thread che fanno parte di un pool all’inizio. Le nuove richieste di connessione fanno sì che MySQL raccolga uno di questi thread e restituisca i dati di connessione e, se tutti i thread vengono esauriti, le nuove connessioni vengono naturalmente rifiutate. Vediamo quanto è grande il pool di thread:

ariaDB [(nessuno)]> mostra variabili come ‘thread_pool_size’;
+——————+——-+
| Nome variabile | Valore |
+——————+——-+
| thread_pool_size | 4 |
+——————+——-+

Quindi, la mia macchina consente un massimo di quattro connessioni contemporaneamente. È interessante notare che il numero 4 deriva dal fatto che ho un processore a quattro core, il che significa che il mio computer può eseguire solo 4 attività parallele alla volta (sto parlando di attività veramente parallele qui, non simultanee). Idealmente, questo è il limite che dovrebbe guidare il valore di thread_pool_size, ma su macchine più robuste aumentarlo ne trarrà vantaggio. Se non vuoi aspettare che tutte le nuove connessioni aspettino e sei a posto per fare un salto di performance (di nuovo, questa è un’area che puoi giudicare meglio in base alle prestazioni della tua app sotto carico), aumentarla fino a 8 potrebbe essere una buona idea.

Tuttavia, impostarlo oltre 16 è una pessima idea a meno che tu non abbia una macchina a 32 core, poiché le prestazioni si riducono in modo significativo. La tana del coniglio dei pool di thread in MySQL è profonda, ma se sei interessato, ecco una discussione più dettagliata.

Attesa e timeout

Una volta che un thread è stato creato e collegato a un client, sarebbe uno spreco di risorse se il client non inviasse query per i prossimi secondi (o minuti). Di conseguenza, MySQL termina una connessione dopo un periodo di inattività. Questo è controllato dalla variabile wait_timeout:

MariaDB [(nessuno)]> mostra variabili come ‘wait%’;
+—————+——-+
| Nome variabile | Valore |
+—————+——-+
| wait_timeout | 28800 |
+—————+——-+

Il valore risultante è in secondi. Quindi sì, per impostazione predefinita MySQL è impostato per attendere oltre 8 ore prima di tagliare il cavo! Questo può essere utile se hai domande a lungo termine e vuoi davvero aspettarle (ma anche allora, otto ore sono assurde!) Ma nella maggior parte dei casi è terribile. Quando viene eseguita una query, questo valore è impostato su 0 (che significa per sempre), ma in genere dovrebbe essere impostato su un valore molto basso (5 secondi, ad esempio, o forse anche meno) per liberare la connessione per altri processi.

Ottimizzazione delle tabelle temporanee

Cominciamo con quelle che sono le tabelle temporanee in MySQL.

Supponiamo di avere un MySQL che assomigli strutturalmente a questo: TABELLA A UNIONE (TABELLA B INTERNO JOIN C). Cioè, siamo interessati a unire le tabelle B e C e quindi eseguire un’unione del risultato con la tabella A. Ora, MySQL procederà prima a unirsi alle tabelle B e C, ma prima di poter eseguire un’unione, è necessario per archiviare questi dati da qualche parte. È qui che arrivano le tabelle temporanee: MySQL le utilizza per archiviare temporaneamente i dati in fasi intermedie in query complesse e una volta terminata la query, questa tabella temporanea viene scartata.

Ora la domanda è: perché dovremmo preoccuparci di tutto questo?

Semplicemente perché la tabella temporanea, solo un risultato della query, sono i dati utilizzati da MySQL nel calcolo e la velocità del suo accesso (tra le altre limitazioni) determinerà la velocità di esecuzione della query. Ad esempio, l’archiviazione della tabella temporanea nella RAM sarà più volte più rapida rispetto all’archiviazione sul disco.

Esistono due variabili che controllano questo comportamento:

MariaDB [(nessuno)]> mostra variabili come ‘MariaDB [(none)]> mostra variabili come ‘tmp_table_size’;
+—————-+———-+

| Nome variabile | Valore |

+—————-+———-+

| tmp_table_size | 16777216 |

+—————-+———-+
‘;
+———————+———-+
| Nome variabile | Valore |
+———————+———-+
| max_heap_table_size | 16777216 |
+———————+———-+

MariaDB [(nessuno)]> mostra variabili come ‘tmp_table_size’;
+—————-+———-+
| Nome variabile | Valore |
+—————-+———-+
| tmp_table_size | 16777216 |
+—————-+———-+

Il primo, max_heap_table_size, ci dice quanta RAM può essere utilizzata da una tabella MySQL (“heap” qui si riferisce alla struttura di dati utilizzata nell’allocazione e nella gestione della RAM – leggi di più Qui), mentre il secondo, tmp_table_size, mostra qual è la dimensione massima della tabella temporanea. Nel mio caso, entrambi sono impostati su 16 MB, anche se il punto che sto provando a far sì che aumentare solo tmp_table_size non funzionerà nel complesso, MySQL sarebbe comunque limitato da max_table_heap_size.

Ora arriva il punto: se le tabelle temporanee in fase di creazione sono superiori al limite consentito da queste variabili, MySQL sarebbe costretto a scriverle sul disco rigido, con prestazioni estremamente scadenti. Il nostro lavoro ora è semplice: facciamo del nostro meglio per indovinare la dimensione dei dati più accurata per le tabelle temporanee e modificare queste variabili a quel limite. Tuttavia, vorrei essere prudente contro l’assurdità: impostare questo limite su 16 GB (supponendo che tu abbia così tanta RAM) quando la maggior parte dei tuoi tavoli temporanei ha dimensioni inferiori a 24 MB è follia – stai semplicemente sprecando RAM che potrebbe ” sono stati utilizzati da altre query o parti del sistema (cache, ad esempio).

Conclusione

Non è possibile coprire tutte le variabili di sistema in un articolo, o anche tutte quelle importanti in un articolo quando la stessa documentazione di MySQL comprende diverse migliaia di parole. Mentre qui abbiamo trattato alcune variabili universali, ti incoraggio a esaminare le variabili di sistema per il motore che stai utilizzando (InnoDB o MyISAM).

Il mio risultato più desiderabile per aver scritto questo articolo è che tu porti via tre cose:

  1. MySQL è un tipico software che funziona entro i limiti stabiliti dal sistema operativo. Non è un programma misterioso che fa sapere a Dio ed è impossibile da domare. Inoltre, per fortuna, non è così difficile capire come è impostato e viene controllato dalle sue variabili di sistema.
  2.  Non esiste una singola impostazione che renda zoomare l’installazione di MySQL. Non hai altra scelta che guardare all’interno dei tuoi sistemi in esecuzione (ricorda, l’ottimizzazione arriva dopo che l’app è in produzione, non prima), fai le ipotesi e le misurazioni migliori e vivi con la realtà che non sarà mai perfetto.
  3. L’ottimizzazione delle variabili non è l’unico modo per ottimizzare MySQL: le query di scrittura efficienti sono un altro grosso problema, ma sono qualcosa che affronterò in un altro articolo. Ma il punto è che, anche se hai fatto un’analisi divina e messo a punto questi parametri al meglio, è ancora possibile per te portare tutto a una brusca frenata.

Qual è la tua variabile di sistema preferita per l’ottimizzazione? ��

TAGS:

  • Banca dati

Jeffrey Wilson Administrator
Sorry! The Author has not filled his profile.
follow me
    Like this post? Please share to your friends:
    Adblock
    detector
    map