Vir die meeste toepassingsontwikkelaars is die databasis ‘n altaar van demoongode wat die beste onaangeraak gelaat word. Maar dit hoef nie so te wees nie!


Anders as wat gelyk is, bepaal die gemak wat ‘n ontwikkelaar met die onderliggende databasis het, hul senioriteitsvlak. Min databasis en min koderingservaring = junior ontwikkelaar; min databasis en goeie koderingservaring = ontwikkelaar op die middelvlak; goeie databasis en goeie koderingservaring = senior ontwikkelaar.

Dit is ‘n harde werklikheid wat selfs met 6-8 jaar onder hul gordel sukkel om die verwikkeldhede van die navraagoptimeerder te verklaar en verkies om hemels te kyk as hy gevra word oor databasis verstelling.

Hoekom?

Die rede is verbasend dat dit nie luiheid is nie (hoewel dit in sommige dele ook is).

Die punt is dat databasisse hul eie krag kan hanteer. Selfs tradisioneel, toe daar slegs die relatiewe soorte databasisse was om mee te doen, was dit ‘n wonderwerk en ‘n loopbaan op sigself; Deesdae het ons soveel soorte databasisse dat dit net onmoontlik is om te verwag dat ‘n enkele sterflike siel alles sal bemeester.

Dit gesê, daar is ‘n goeie kans dat u steeds gelukkig is met verhoudingsdatabasisse of deel is van ‘n span wat ‘n produk het wat op ‘n relasionele databasis vir ‘n lang, lang tyd bevredigend is. En in nege gevalle uit tien is jy op MySQL (of MariaDB). In hierdie gevalle lewer duik ‘n bietjie dieper onder die enjinkap groot voordele om die werkverrigting te verhoog en is dit die moeite werd om te leer.

Nuuskierig? Kom ons duik in!

Nie nuuskierig nie? Wel, duik in elk geval in, want u loopbaan hang daarvan af! ��

Optimaliseer die MySQL-navraagcache

Byna al die optimalisering op die gebied van rekenaars kom neer op cache. Aan die een kant handhaaf die SVE verskillende vlakke van die kas om sy berekeninge te bespoedig, en aan die ander kant maak webprogramme aggressief gebruik van caching-oplossings soos Redis om vooraf berekende resultate vir gebruikers te bedien eerder as om elke keer die databasis te slaan.

Maar hey, selfs die arme MySQL-databasis het sy eie navraagcache! Dit wil sê dat elke keer as u iets navraag doen, en die data nog steeds verouderd is, sal MySQL hierdie gebergte resultate lewer eerder as om die navraag weer te laat loop, wat die app belaglik vinniger maak.

U kan kontroleer of u navraag-kas beskikbaar het (let wel, beskikbaar, nie ingeskakel nie) in u databasis deur hierdie navraag in die databasiskonsole uit te voer:

MariaDB [(geen)]> TOON VARIABELS SOOS ‘have_query_cache’;
+——————+——-+
| Veranderlike naam | Waarde |
+——————+——-+
| have_query_cache | JA |
+——————+——-+

U kan dus sien dat ek MariaDB bestuur en dat ek cache-navrae beskikbaar het om aan te skakel. Dit is uiters onwaarskynlik dat u dit sal afskakel as u ‘n standaard MySQL-installasie gebruik.

Kom ons kyk of ek die navraag-kas eintlik aangeskakel het:

MariaDB [(geen)]> TOON VARIABELS SOOS ‘query_cache_type’;
+——————+——-+
| Veranderlike naam | Waarde |
+——————+——-+
| query_cache_type | AAN |
+——————+——-+

Ja ek doen. Maar as u dit nie doen nie, kan u dit aanskakel deur te sê:

MariaDB [(geen)]> SET GLOBALE query_cache_type = AAN;

Interessant genoeg, hierdie veranderlike aanvaar ook ‘n derde waarde wat ‘on-demand’ beteken, en dit beteken dat MySQL slegs die vrae wat ons sê, sal kas, maar ons sal dit nie hier inhaal nie.

Hiermee het u navraag gedoen en die eerste stap geneem na ‘n meer robuuste MySQL-opstelling! Ek sê die eerste stap, want hoewel ons dit aanskakel, is dit ‘n groot verbetering, maar ons moet die cache van die vrae instel om by ons opstelling te pas. Laat ons dus leer om dit te doen.

Die ander veranderlike van belang hier is query_cache_size, waarvan die funksie selfverduidelikend is:

MariaDB [(geen)]> TOON VARIABELS SOOS ‘query_cache_size’;
+——————+———-+
| Veranderlike naam | Waarde |
+——————+———-+
| query_cache_size | 16777216 |
+——————+———-+

Dus, ek het ‘n navraag-kas van ongeveer 16 MB. Let daarop dat, selfs as die cache vir navrae aangeskakel is, maar hierdie grootte nul is, die kas effektief af is. Daarom is dit nie genoeg om slegs een veranderlike te kontroleer nie. Nou moet u die grootte van ‘n navraag-kas stel, maar hoeveel moet dit wees? Let daarop eerstens dat die funksie vir die kasgeheue self 4 KB benodig om sy metadata op te slaan, so u moet bo alles kies wat u kies.

Gestel jy stel die grootte van die navraaggeheue op 500 KB:

MariaDB [(geen)]> SET GLOBALE query_cache_size = 500000;

Is dit genoeg gedoen? Wel, nee, want hoe die navraag-enjin eintlik gaan werk, hang van ‘n paar dinge af:

  • Eerstens moet die query_cache_size-veranderlike groot genoeg wees om die resultaat van u navrae te behou. As dit te klein is, sal niks geberg word nie.
  • Tweedens, as query_cache_size op ‘n te hoë getal gestel is, sal daar twee soorte probleme wees: 1) Die enjin sal ekstra werk moet doen om die resultate in hierdie massiewe geheue te berg en op te spoor. 2) As die meeste van die vrae veel kleiner groottes tot gevolg het, sal die kas gefragmenteer word, en die voordele verbonde aan die gebruik van ‘n kas kan verlore gaan.

Hoe weet jy dat die kas gefragmenteer word? Kontroleer die totale aantal blokke in die kas soos volg:

MariaDB [(geen)]> toon status soos ‘Qcache_total_blocks’;
+———————+——-+
| Veranderlike naam | Waarde |
+———————+——-+
| Qcache_total_blocks | 33 |
+———————+——-+

As die getal baie hoog is, is die kas gefragmenteer en moet daar gespoel word.

Om hierdie probleme te vermy, moet u dus seker maak dat die grootte van query_cache_size verstandig gekies word. As u gefrustreerd voel dat ek u nie hier met ‘n konkrete nommer gelaat het nie, is ek bang dat dit is soos dinge is sodra u verby ontwikkeling beweeg en ingenieurswese betree. U moet kyk na die program wat u gebruik en kyk wat die navraaggroottes vir die belangrike navraagresultate is en stel dan hierdie nommer in. En selfs dan maak u dalk ‘n fout. ��

Draad, draad swembaddens, wag en time-outs

Dit is waarskynlik die interessantste deel van hoe MySQL werk en om dit reg te kry, beteken dat u app ‘n paar keer vinniger maak!

threading

MySQL is ‘n multidraadbediener. Dit beteken dat elke keer dat daar ‘n nuwe verbinding met die MySQL-bediener is, ‘n nuwe draad oopgemaak word met die verbindingsdata en dit aan die kliënt oorgedra word (net vir die geval dat u wonder wat ‘n draad is, sien hierdie). Die kliënt stuur dan alle navrae oor hierdie draad en ontvang resultate. Dit laat ons ‘n natuurlike vraag stel: hoeveel drade kan MySQL oprol? Die antwoord lê in die volgende afdeling.

Draad swembad

Geen program in ‘n rekenaarstelsel kan soveel drade oopmaak as wat dit wil hê nie. Die rede hiervoor is tweeledig: 1) geheue van draadkoste (RAM), en die bestuurstelsel laat jou net nie berserk en alles opeet nie. 2) Die bestuur van, byvoorbeeld, ‘n miljoen drade is ‘n massiewe taak op sigself, en as die MySQL-bediener soveel drade kan skep, sal dit sterf deur die oorhoofse koste te hanteer..

Om hierdie probleme te voorkom, kom MySQL met ‘n draadpoel – ‘n vaste aantal drade wat aan die begin deel uitmaak van ‘n poel. Nuwe verbindingsversoeke laat MySQL een van hierdie drade optel en die verbindingsdata terugstuur, en as al die drade opgebruik is, word nuwe verbindings natuurlik geweier. Kom ons kyk hoe groot die draadpoel is:

ariaDB [(geen)]> wys veranderlikes soos ‘thread_pool_size’;
+——————+——-+
| Veranderlike naam | Waarde |
+——————+——-+
| thread_pool_size | 4 |
+——————+——-+

Dus, my masjien laat maksimum vier verbindings tegelyk toe. Dit is interessant om daarop te let dat die nommer 4 afkomstig is van die feit dat ek ‘n vierkernverwerker het, wat beteken dat my rekenaar slegs vier parallelle take op een slag kan uitvoer (ek praat hier van werklike parallelle take, nie gelyktydige take nie). Ideaal gesproke is dit die limiet wat die waarde van thread_pool_size behoort te dryf, maar by die styger masjiene verhoog dit tot ‘n sekere punt. As u nie wil sien dat alle nuwe verbindings laat wag nie en dit goed gaan om ‘n prestasietreffer te kry (dit is ‘n gebied wat u die beste kan beoordeel op grond van die prestasie van u app onder druk), kan dit ‘n goeie idee wees om dit tot 8 te stamp.

Dit is egter ‘n vreeslike idee om dit bo 16 te stel, tensy u ‘n 32-kernmasjien het, aangesien die werkverrigting aansienlik agteruitgaan. Die konyngat van draadpoele in MySQL gaan diep, maar as jy belangstel, hier is ‘n meer gedetailleerde bespreking.

Wag en time-outs

Sodra ‘n draad geskep en aan ‘n kliënt gekoppel is, sal dit ‘n vermorsing van hulpbronne wees as die kliënt vir die volgende paar sekondes (of minute) geen vrae stuur nie. As gevolg hiervan beëindig MySQL ‘n verbinding na ‘n periode van onaktiwiteit. Dit word beheer deur die wag_timeout-veranderlike:

MariaDB [(geen)]> wys veranderlikes soos ‘wag%’;
+—————+——-+
| Veranderlike naam | Waarde |
+—————+——-+
| wag_tyd | 28800 |
+—————+——-+

Die gevolglike waarde is in sekondes. So ja, MySQL is standaard ingestel op meer as 8 uur voordat dit die kabel snoer! Dit kan goed wees as u langdurige vrae het en eintlik op hulle wil wag (maar selfs agt uur is absurd!) Maar in die meeste gevalle vreeslik. As ‘n navraag uitgevoer word, word hierdie waarde op 0 (vir ewig beteken) gestel, maar oor die algemeen moet dit op ‘n baie lae waarde (byvoorbeeld 5 sekondes, of miskien nog minder) gestel word om die verbinding vir ander prosesse te bevry.

Tydelike tafels instel

Kom ons begin met die tydelike tabelle in MySQL.

Gestel ons het ‘n MySQL wat struktureel soos volg lyk: TABEL ‘N UNIE (TABEL B BINNE SLUIT C). Dit wil sê, ons is geïnteresseerd in die aansluiting van tabelle B en C, en dan ‘n vereniging van die resultaat met tabel A. uit te voer. MySQL sal eers aangaan om by die tabelle B en C aan te sluit, maar voordat dit ‘n unie kan doen, moet dit om hierdie data êrens te stoor. Dit is hier waar tydelike tabelle binnekom – MySQL gebruik hulle om data in tussenfases in komplekse navrae tydelik op te slaan, en sodra die navraag verby is, word hierdie tydelike tabel weggegooi..

Die vraag is nou: hoekom moet ons dit doen??

Net omdat die tydelike tabel, net ‘n navraagresultaat, is data wat deur MySQL gebruik word in die berekening, en die snelheid van die toegang daarvan (onder andere beperkings) sal bepaal hoe vinnig die navraag uitgevoer word. Dit sal byvoorbeeld ‘n paar keer vinniger wees om die tydelike tafel in RAM te berg as om dit op die skyf te stoor.

Daar is twee veranderlikes wat hierdie gedrag beheer:

MariaDB [(geen)]> wys veranderlikes soos ‘MariaDB [(geen)]> wys veranderlikes soos ‘tmp_table_size’;
+—————-+———-+

| Veranderlike naam | Waarde |

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

| tmp_table_size | 16777216 |

+—————-+———-+
‘;
+———————+———-+
| Veranderlike naam | Waarde |
+———————+———-+
| max_heap_table_size | 16777216 |
+———————+———-+

MariaDB [(geen)]> wys veranderlikes soos ‘tmp_table_size’;
+—————-+———-+
| Veranderlike naam | Waarde |
+—————-+———-+
| tmp_table_size | 16777216 |
+—————-+———-+

Die eerste, max_heap_table_size, vertel hoeveel RAM deur ‘n MySQL-tabel opgebruik kan word (“hoop” verwys hier na die datastruktuur wat gebruik word in RAM-toewysing en bestuur – lees meer hier), terwyl die tweede een, tmp_table_size, toon wat die maksimum grootte van die tydelike tabel is. In my geval is albei ingestel op 16 MB, hoewel die punt wat ek probeer maak dat die verhoging van slegs tmp_table_size nie in die algemeen sal werk nie, sal MySQL steeds beperk word deur max_table_heap_size.

Nou kom die punt: as die tydelike tabelle wat geskep word groter is as die limiet wat deur hierdie veranderlikes toegelaat word, sal MySQL gedwing word om dit op die hardeskyf te skryf, wat uiters swak werkverrigting tot gevolg het. Ons taak is nou eenvoudig: doen ons bes om die akkuraatste gegewengrootte vir tydelike tabelle te raai en hierdie veranderlikes tot so ‘n mate aan te pas. Ek wil egter waarsku teen absurditeit: om hierdie limiet op 16 GB te stel (as u aan soveel RAM benodig) as die meeste van u tydelike tafels van minder as 24 MB groot is, is dwaasheid – u mors eenvoudig RAM wat kan ‘ is al deur ander navrae of dele van die stelsel gebruik (byvoorbeeld, kas).

Afsluiting

Dit is nie moontlik om al die stelselveranderlikes in een artikel of selfs al die belangrikste in een artikel te dek wanneer die MySQL-dokumentasie self ‘n paar duisend woorde bevat nie. Terwyl ons hier ‘n paar universele veranderlikes behandel het, sal ek u aanmoedig om na die stelselveranderlikes te kyk vir die enjin wat u gebruik (InnoDB of MyISAM).

My mees wenslike uitkoms vir die skryf van hierdie artikel is dat u drie dinge wegneem:

  1. MySQL is ‘n tipiese sagteware wat werk binne die perke wat deur die bestuurstelsel bepaal word. Dit is nie ‘n geheimsinnige program wat God-weet-wat doen en onmoontlik is om te tem nie. Gelukkig is dit ook nie so moeilik om te verstaan ​​hoe dit opgestel word en beheer word deur sy stelselveranderlikes nie.
  2.  Daar is geen enkele instelling wat u MySQL-installasie sal laat zoom nie. U het geen ander keuse as om na u lopende stelsels te kyk nie (onthou, die optimisering kom nadat die app in produksie is, nie voorheen nie), die beste raaiskote en metings maak, en leef met die realiteit dat dit nooit perfek sal wees nie.
  3. Die instel van die veranderlikes is nie die enigste manier om MySQL te optimaliseer nie – doeltreffende skryfnavrae is nog ‘n groot saak, maar dit is iets wat ek in ‘n ander artikel sal aanspreek. Maar die punt is dat selfs al het u ‘n godagtige analise gedoen en hierdie parameters op die beste manier ingestel, is dit nog steeds vir u moontlik om alles tot stilstand te bring.

Wat is u gunsteling stelselveranderlike om te instel? ��

Tags:

  • databasis

Jeffrey Wilson Administrator
Sorry! The Author has not filled his profile.
follow me