Ajust de les variables del sistema MySQL per obtenir un alt rendiment

Per a la majoria de desenvolupadors d’aplicacions, la base de dades és un altar de déus dimonis que queda millor que no s’aprova. Però no ha de ser així!


En fer coses iguals, el nivell de confort que té un desenvolupador amb la base de dades subjacent defineix el seu nivell d’antiguitat. Poca base de dades i poca experiència de codificació = desenvolupador menor; poca base de dades i bona experiència de codificació = desenvolupador de nivell mitjà; bona base de dades i bona experiència de codificació = desenvolupador sènior.

És una dura realitat que fins i tot es presenta amb 6-8 anys sota la seva lluita per cinturó per explicar les complexitats de l’optimitzador de consultes i prefereixen mirar cap al cel quan se li pregunta afinació de bases de dades.

Per què?

Sorprenentment, la raó no és la mandra (tot i que en alguna part ho és).

La qüestió és que les bases de dades són una força pròpia per lluitar. Fins i tot tradicionalment, quan només hi havia els tipus relacionals de bases de dades per tractar-les, dominar-les era un camí de miracle i de carrera propi; avui en dia, tenim tants tipus de bases de dades que és impossible que una sola ànima mortal ho domini tot.

Dit això, hi ha una bona possibilitat de que encara sigueu satisfets amb les bases de dades relacionals o que formeu part d’un equip que tingui un producte funcionant satisfactòriament en una base de dades relacional durant molt de temps. I en nou casos de cada deu, esteu a MySQL (o a MariaDB). Per a aquests casos, la immersió una mica més profunda sota el capó proporciona avantatges massius per augmentar el rendiment de les aplicacions i val la pena aprendre..

Curiós? Anem a capbussar-nos!

No és curiós? Bé, bussegeu de totes maneres, perquè la vostra carrera en depèn. ��

Optimitzeu la memòria cau de consulta del MySQL

Gairebé tota l’optimització en el camp dels ordinadors es redueix a la memòria cau. Per un extrem, la CPU manté diversos nivells de memòria cau per accelerar els seus càlculs i, per l’altra, les aplicacions web fan un ús agressiu de solucions de memòria cau com Redis per donar resultats als usuaris precomputats al servidor en lloc de colpejar la base de dades cada vegada..

Però bé, fins i tot la pobra base de dades MySQL té la seva pròpia memòria cau! És a dir, cada vegada que consultis alguna cosa i les dades siguin obsoletes, MySQL servirà aquests resultats en memòria cau en lloc de tornar a executar la consulta, fent que l’aplicació sigui ridículament més ràpida.

Podeu comprovar si teniu la memòria cau de consulta disponible (nota, disponible, no habilitada) a la base de dades executant aquesta consulta a la consola de bases de dades:

MariaDB [(cap)]> MOSTRA VARIABLES COM A ‘have_query_cache’;
+——————+——-+
| Nom variable | Valor |
+——————+——-+
| have_query_cache | SÍ |
+——————+——-+

Per tant, podeu veure que estic executant MariaDB i que tinc la memòria cau disponible per activar-la. És molt improbable que l’hagis desactivat si utilitzeu una instal·lació estàndard de MySQL.

Ara anem a veure si realment heu activat la memòria cau de consulta:

MariaDB [(cap)]> MOSTRA VARIABLES COM A “query_cache_type”;
+——————+——-+
| Nom variable | Valor |
+——————+——-+
| query_cache_type | ON |
+——————+——-+

Sí, sí. Però en cas que no ho sigui, podeu activar-ho dient:

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

Curiosament, aquesta variable també accepta un tercer valor que denota “sota demanda”, el que significa que MySQL només emmagatzemarà en memòria cau les consultes que li expliquem, però no hi entrarem aquí.

Amb això, heu accedit a la memòria cau i heu fet el primer pas cap a una configuració més robusta de MySQL. Dic el primer pas perquè, si bé l’activa és una millora important, cal sintonitzar la memòria cau per adaptar-nos a la configuració. Aprenem, doncs, a fer-ho.

L’altra variable d’interès aquí és query_cache_size, la funció és explicativa:

MariaDB [(cap)]> MOSTRA VARIABLES COM A “query_cache_size”;
+——————+———-+
| Nom variable | Valor |
+——————+———-+
| query_cache_size | 16777216 |
+——————+———-+

Per tant, tinc una memòria cau de consulta d’uns 16 MB de mida. Tingueu en compte que, encara que la memòria cau de consulta està activada, però aquesta mida és zero, la memòria cau està desactivada efectivament. És per això que no és suficient comprovar una única variable. Ara, haureu d’establir una mida de memòria cau de consulta, però quant hauria de ser? En primer lloc, tingueu en compte que la funció de memòria cau de consulta necessitarà per si mateixa 4 KB per emmagatzemar els seus metadats, de manera que qualsevol cosa que seleccioneu hauria d’estar per sobre d’aquest.

Suposem que heu definit la mida de la memòria cau de la consulta a 500 KB:

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

Això ho fa bastant? Bé, no, perquè la forma en què el motor de consulta acabarà funcionant depèn d’un parell de coses més:

  • En primer lloc, la variable query_cache_size ha de ser prou gran com per contenir el resultat de les vostres consultes. Si és massa petit, no es guardarà res a la memòria cau.
  • En segon lloc, si query_cache_size està establert en un nombre massa elevat, hi haurà dos tipus de problemes: 1) El motor haurà de fer un treball addicional per emmagatzemar i localitzar els resultats de la consulta en aquesta àrea de memòria massiva. 2) Si la majoria de les consultes donen lloc a mides molt menors, la memòria cau es fragmentarà i els avantatges d’utilitzar una memòria cau es perdran..

Com saps que la memòria cau es fragmenta? Comproveu el nombre total de blocs de la memòria cau així:

MariaDB [(cap)]> mostrar l’estat com “Qcache_total_blocks”;
+———————+——-+
| Nom variable | Valor |
+———————+——-+
| Qcache_total_blocks | 33 |
+———————+——-+

Si el nombre és molt alt, la memòria cau es fragmenta i cal que es redueixi.

Per tant, per evitar aquests problemes, assegureu-vos que la mida de query_cache_size està triada amb prudència. Si us sentiu frustrats que no us he deixat amb un número concret aquí, em temo que és així com passen les coses un cop passades al desenvolupament passat i a l’enginyeria. Heu de mirar l’aplicació que esteu executant i veure quines són les mides de consulta dels resultats de consulta importants i, a continuació, definir aquest número. I fins i tot llavors potser acabareu equivocant-vos. ��

Roscada, grups de fils, espera i temps mort

Probablement sigui la part més interessant del funcionament de MySQL i d’aconseguir-ho bé significa fer que l’aplicació sigui diverses vegades més ràpida!

Roscada

MySQL és un servidor multi-threaded. Això vol dir que cada vegada que hi hagi una nova connexió amb el servidor MySQL, obre un nou fil amb les dades de connexió i li passa una nansa al client (per si us pregunteu què és un fil, vegeu això). Aleshores, el client envia totes les consultes sobre aquest fil i rep resultats. Això ens porta a plantejar-nos una pregunta natural: quants fils poden girar MySQL? La resposta rau a la següent secció.

Piscina de fils

Cap programa d’un sistema informàtic pot obrir tants fils com es vulgui. El motiu és doble: 1) La memòria de cost dels fils (RAM) i el sistema operatiu no us permetrà passar per tots i consumir-ne tot. 2) Gestionar, per exemple, un milió de fils, és una tasca massiva per si sol, i si el servidor MySQL pogués crear molts fils, moriria intentant fer front a la despesa general..

Per evitar aquests problemes, MySQL arriba amb un pool de fils: un nombre fix de fils que formen part d’un grup al principi. Les noves sol·licituds de connexió provoquen que MySQL agafi un d’aquests fils i retorni les dades de la connexió, i si es fan servir tots els fils, les connexions noves es rebutgen. Anem a veure la mida que té l’agrupació de fils:

ariaDB [(cap)]> mostra variables com “thread_pool_size”;
+——————+——-+
| Nom variable | Valor |
+——————+——-+
| fil_pool_size | 4 |
+——————+——-+

Per tant, la meva màquina permet un màxim de quatre connexions al mateix temps. És interessant notar que el número 4 prové del fet que tinc un processador de quatre nuclis, cosa que vol dir que el meu ordinador només pot executar quatre tasques paral·leles alhora (parlo de tasques realment paral·leles, no de concurrents). L’ideal és que aquest sigui el límit que hauria d’executar el valor de thread_pool_size, però en augmentar les màquines de més gran possibilitat és puntual. Si no voleu que totes les connexions noves s’esperen i està bé d’aconseguir algun rendiment (de nou, aquest és un àmbit que es pot considerar millor segons el rendiment del vostre aplicació sota càrrega), una bona idea pot augmentar fins a 8..

Tanmateix, establir-la més enllà de 16 és una idea terrible a menys que tinguis una màquina de 32 nuclis, ja que el rendiment es redueix significativament. El forat de conill de les piscines de fil a MySQL aprofundeix, però si us interessa, aquí una discussió més detallada.

L’espera i el temps de espera

Un cop creat un fil i connectat a un client, es perd una pèrdua de recursos si el client no va enviar cap consulta durant els propers segons (o minuts). Com a resultat, MySQL finalitza una connexió després d’un període d’inactivitat. Això està controlat per la variable wait_timeout:

MariaDB [(cap)]> mostra variables com ara “wait%”;
+—————+——-+
| Nom variable | Valor |
+—————+——-+
| wait_timeout | 28800 |
+—————+——-+

El valor resultant és en segons. Doncs sí, per defecte MySQL està configurat per esperar vuit o més hores abans que obtingui el cable. Això pot ser bo si teniu consultes de llarga durada i realment voleu esperar-les (però fins i tot, vuit hores és absurd!), Però terrible en la majoria dels casos. Quan s’executa una consulta, aquest valor s’estableix en 0 (que significa per sempre), però generalment, s’hauria d’establir a un valor molt baix (5 segons, per exemple, o potser encara menys) per alliberar la connexió d’altres processos..

Sintonització de taules temporals

Comencem amb les taules temporals de MySQL.

Suposem que tenim un MySQL que s’assembla estructuralment a aquest aspecte: TAULA UNIÓ (TAULA B INNER JOIN C). És a dir, ens interessa unir les taules B i C i, a continuació, realitzar una unió del resultat amb la taula A. Ara, MySQL primer procediria a unir-se a les taules B i C, però abans que pugui realitzar una unió, necessita per emmagatzemar aquestes dades en algun lloc. Aquí és on entren les taules temporals: MySQL les utilitza per emmagatzemar temporalment dades en etapes intermèdies en consultes complexes i, un cop finalitzada la consulta, aquesta taula temporal es descarta..

Ara la pregunta és: per què ens hem de preocupar de tot això?

Simplement perquè la taula temporal, només un resultat de consulta, són dades que MySQL utilitza en càlcul, i la velocitat d’accés (entre altres limitacions) determinarà la velocitat d’execució de la consulta. Per exemple, emmagatzemar la taula temporal a la RAM serà diverses vegades més ràpid que guardar-la al disc.

Hi ha dues variables que controlen aquest comportament:

MariaDB [(cap)]> mostra variables com “MariaDB [(cap)]> mostra variables com “tmp_table_size”;
+—————-+———-+

| Nom variable | Valor |

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

| tmp_table_size | 16777216 |

+—————-+———-+
‘;
+———————+———-+
| Nom variable | Valor |
+———————+———-+
| max_heap_table_size | 16777216 |
+———————+———-+

MariaDB [(cap)]> mostra variables com “tmp_table_size”;
+—————-+———-+
| Nom variable | Valor |
+—————-+———-+
| tmp_table_size | 16777216 |
+—————-+———-+

El primer, max_heap_table_size, ens explica la quantitat de memòria RAM que pot utilitzar una taula MySQL (aquí “heap” es refereix aquí a l’estructura de dades que s’utilitza en l’assignació i la gestió de RAM – llegeix més aquí), mentre que la segona, tmp_table_size, mostra quina és la mida màxima de la taula temporal. En el meu cas, ambdues tenen una configuració de 16 MB, tot i que el que estic intentant fer que augmentar només tmp_table_size no funcionarà de forma general, MySQL encara estaria limitat per max_table_heap_size.

Ara arriba el punt: si les taules temporals que es creen són superiors al límit permès per aquestes variables, MySQL es veuria obligat a escriure-les al disc dur, amb un rendiment extremadament deficient. La nostra feina ara és senzilla: fem el possible per endevinar la mida de dades més exacta de les taules temporals i ajustar aquestes variables fins a aquest límit. Tanmateix, vull tenir precaució contra l’absurd: establir aquest límit a 16 GB (suposant que teniu molta memòria RAM) quan la majoria de les vostres taules temporals tenen una mida inferior a 24 MB és una bogeria: simplement esteu malgastant memòria RAM que podria ” Heu estat utilitzades per altres consultes o parts del sistema (memòria cau, per exemple).

Conclusió

No és possible cobrir totes les variables del sistema d’un article, ni tan sols totes les importants d’un article quan la mateixa documentació MySQL té diversos milers de paraules. Si bé tractem algunes variables universals aquí, us recomanaria que examinés les variables del sistema per al motor que utilitzeu (InnoDB o MyISAM).

El resultat més desitjat per escriure aquest article és que traguis tres coses:

  1. MySQL és un programari típic que funciona dins dels límits establerts pel sistema operatiu. No és un programa misteriós que sap el que Déu sap i què és impossible de domar. A més, per sort, no és tan difícil entendre com es configura i es controla les variables del sistema.
  2.  No hi ha cap configuració única que faci que la vostra instal·lació de MySQL es faci zoom. No teniu més remei que mirar els vostres sistemes en funcionament (recordeu que l’optimització es produeix després que l’aplicació estigui en producció, no abans), feu les millors conjectures i mesures i conviu amb la realitat que mai serà perfecte..
  3. L’ajustar les variables no és l’única manera d’optimitzar MySQL: les consultes d’escriptura eficients és un altre gran problema, però és una cosa que abordaré en un altre article. Però el tema és que, fins i tot si heu fet una anàlisi divina i ajusteu els paràmetres al millor, encara és possible que us acabi tot.

Quina és la vostra variable preferida del sistema per afinar? ��

Tags:

  • Base de dades

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