Akordimi i Variablave të Sistemit MySQL për Performancë të Lartë

Për shumicën e zhvilluesve të aplikacioneve, baza e të dhënave është një altar i perëndive demonike të mbetur më së miri i papranuar. Por nuk duhet të jetë kështu!


Gjëra të tjera janë të barabarta, niveli i komoditetit që një zhvillues ka me bazën e të dhënave themelore përcakton nivelin e tyre të vjetërsisë. Pak baza e të dhënave dhe përvojë e vogël e kodimit = zhvillues i ri; pak bazë të dhënash dhe përvojë e mirë e kodimit = zhvilluesi i nivelit të mesëm; databazë e mirë dhe përvojë e mirë e kodimit = zhvillues i vjetër.

Shtë një realitet i ashpër që edhe devijon me 6-8 vjet nën luftën e rripit të tyre për të shpjeguar ndërlikimet e optimizuesit të pyetjes dhe preferon të shikojë në qiell kur pyetet për akordimi i bazës së të dhënave.

Përse?

Uditërisht, arsyeja nuk është dembelizëm (edhe pse në një pjesë është).

Pointështja është se bazat e të dhënave janë një forcë e tyre për të luftuar. Edhe tradicionalisht, kur ekzistonin vetëm llojet e të dhënave relacionale për t’u marrë me të, zotërimi i tyre ishte një mrekulli dhe një rrugë karriere në vetvete; këto ditë, kemi kaq shumë lloje të dhënash që është thjesht e pamundur të presësh një shpirt të vetëm, të vdekshëm, për të zotëruar gjithçka.

Thënë kjo, ka një shans të mirë që jeni akoma të kënaqur me bazat e të dhënave relacionale ose jeni pjesë e një ekipi që ka një produkt që funksionon në një bazë të dhënash relacionale në mënyrë të kënaqshme për një kohë të gjatë, të gjatë. Dhe në nëntë raste nga dhjetë, ju jeni në MySQL (ose MariaDB). Për këto raste, zhytja vetëm pak më thellë nën kapuç jep përfitime masive në rritjen e performancës së aplikacionit dhe është çdo vlerë që mësohet.

Kurioz? Le të zhyten!

Jo kurioz? Epo, zhyteni gjithsesi, sepse karriera juaj varet nga kjo! ��

Optimizoni cache e pyetjes MySQL

Pothuajse i gjithë optimizimi në fushën e kompjuterave zbret në caching. Në njërën anë, CPU mban disa nivele të cache për të shpejtuar llogaritjet e tij, dhe nga ana tjetër, aplikacionet në ueb përdorin agresivisht zgjidhje caching si Redis në server rezultatet e preokupuara te përdoruesit sesa të godasin bazën e të dhënave çdo herë.

Por hej, edhe baza e të dhënave e dobët e MySQL ka cache-në e vet të pyetjes! Kjo do të thotë, çdo herë që të kërkoni diçka, dhe të dhënat janë ende të zbehta, MySQL do t’i shërbejë këtyre rezultateve të ruajtura në vend që të ekzekutoni përsëri kërkesën, duke e bërë aplikacionin në mënyrë qesharake më të shpejtë.

Ju mund të kontrolloni nëse keni cache të pyetjes në dispozicion (shënim, në dispozicion, jo të aktivizuar) në bazën e të dhënave tuaja duke ekzekutuar këtë pyetje në tastierën e të dhënave:

MariaDB [(asnjë)]> SHOW SHUM VARIABLES LIKE ‘have_query_cache’;
+——————+——-+
| Variabël_name | Vlera |
+——————+——-+
| kanë_query_cache | PO |
+——————+——-+

Kështu që, ju mund të shihni që unë jam duke ekzekutuar MariaDB dhe se kam memorie të kërkimit në dispozicion për t’u ndezur. Extremelyshtë jashtëzakonisht e pamundur që ju ta keni fikur nëse përdorni një instalim standard MySQL.

Tani le të shohim nëse e kam të fshehtë në të vërtetë quhet e ndezur:

MariaDB [(asnjë)]> SHOW SHUM VARIABLES LIKE ‘query_cache_type’;
+——————+——-+
| Variabël_name | Vlera |
+——————+——-+
| query_cache_type | ON |
+——————+——-+

Po, e bëj Por në rast se nuk e bëni këtë, mund ta ndizni duke thënë:

MariaDB [(asnjë)]> Vendosni GLOBAL query_cache_type = ON;

Shtë interesante, kjo variabël gjithashtu pranon një vlerë të tretë që nënkupton “kërkesë”, që do të thotë MySQL do të fshehë vetëm ato pyetje të cilave u themi atyre, por ne nuk do të hyjmë këtu.

Me këtë, ju keni caching të kërkimit dhe keni ndërmarrë hapin e parë drejt një konfigurimi më të fortë të MySQL! Unë them hapin e parë sepse ndërsa e aktivizojmë atë është një përmirësim i madh, duhet të bëjmë kërkimin e caching që të përshtatet me konfigurimin tonë. Pra, le të mësojmë ta bëjmë atë.

Variabli tjetër i interesit këtu është query_cache_size, funksioni i të cilit është vetë-shpjegues:

MariaDB [(asnjë)]> SHOW SHUM VARIABLES LIKE ‘query_cache_size’;
+——————+———-+
| Variabël_name | Vlera |
+——————+———-+
| query_cache_size | 16777216 |
+——————+———-+

Kështu që, unë kam një cache të kërkimit me madhësi rreth 16 MB. Vini re se edhe nëse caching query është i ndezur, por kjo madhësi është zero, caching është në mënyrë efektive. Kjo është arsyeja pse kontrollimi i vetëm një ndryshore nuk është e mjaftueshme. Tani, duhet të vendosni një madhësi të cache query, por sa duhet të jetë? Së pari, ju lutem vini re se veçoria e caching query do të duhet në vetvete 4 KB për të ruajtur metadatat e saj, kështu që çfarëdo që të zgjidhni duhet të jetë mbi atë.

Le të themi se keni vendosur madhësinë e cache të pyetjes në 500 KB:

MariaDB [(asnjë)]> Vendosni GLOBAL query_cache_size = 500000;

A po e bën këtë mjaft shumë? Epo, jo, sepse mënyra se si motori i pyetës do të përfundojë duke varet, varet edhe nga disa gjëra:

  • Së pari, ndryshorja query_cache_size duhet të jetë mjaft e madhe për të mbajtur rezultatin tuaj të pyetjeve tuaja. Nëse është shumë e vogël, asgjë nuk do të mbahet e fshehur.
  • Së dyti, nëse query_cache_size është vendosur në një numër shumë të lartë, do të ketë dy lloje të problemeve: 1) Motori do të duhet të bëjë punë shtesë ruajtjen dhe gjetjen e rezultateve të pyetjeve në këtë zonë masive të kujtesës. 2) Nëse shumica e pyetjeve rezultojnë në madhësi shumë më të vogla, cache do të copëtohet, dhe përfitimet e përdorimit të një cache do të humbasin.

Si e dini se cache po copëtohet? Kontrolloni numrin e përgjithshëm të blloqeve në cache si kjo:

MariaDB [(asnjë)]> tregojnë statusin si ‘Qcache_total_blocks’;
+———————+——-+
| Variabël_name | Vlera |
+———————+——-+
| Qcache_total_blocks | 33 |
+———————+——-+

Nëse numri është shumë i lartë, cache është e fragmentuar dhe duhet të skuqet.

Pra, për të shmangur këto probleme, sigurohuni që madhësia e query_cache_size është zgjedhur me mençuri. Nëse ndjeheni të irrituar që nuk jua lashë me një numër konkret këtu, kam frikë se kështu do të ndodhin gjërat pasi të kaloni zhvillimin e kaluar dhe të futeni në inxhinieri. Duhet të shikoni në aplikacionin që po ekzekutoni dhe të shihni se cilat janë madhësitë e pyetjes për rezultatet e rëndësishme të pyetjes dhe më pas vendosni këtë numër. Dhe madje edhe atëherë ju mund të përfundoni duke bërë një gabim. ��

Threading, pishina me fije, pritje dhe afate kohore

Kjo është ndoshta pjesa më interesante se si funksionon MySQL dhe marrja e duhur do të thotë ta bëni aplikacionin tuaj disa herë më të shpejtë!

filetim

MySQL është një server me shumë fije. Kjo do të thotë, sa herë që ka një lidhje të re me serverin MySQL, ajo hap një fije të re me të dhënat e lidhjes dhe ia kalon një dorezë asaj klientit (vetëm në rast se po pyesni se çfarë fije është, shihni kjo). Klienti më pas dërgon të gjitha pyetjet mbi këtë fije dhe merr rezultate. Kjo na çon të bëjmë një pyetje natyrale: sa fije mund të tjerr MySQL? Përgjigja qëndron në pjesën tjetër.

Pishina me fije

Asnjë program në një sistem kompjuterik nuk mund të hapë sa më shumë tema që dëshiron. Arsyeja është e dyfishtë: 1) Threads kushtojnë kujtesën (RAM), dhe sistemi operativ thjesht nuk do t’ju lejojë të shkoni më mirë dhe të hani të gjithë. 2) Menaxhimi, të themi, një milion fije është një detyrë masive më vete, dhe nëse serveri MySQL mund të krijonte ato shumë fije, do të vdiste duke u përpjekur të merrej me sipër.

Për të shmangur këto probleme, MySQL vjen me një grup të fijeve – një numër fiks të temave që janë pjesë e një pishine në fillim. Kërkesa të reja për lidhje bëjnë që MySQL të zgjedhë njërën nga këto fije dhe të kthejë të dhënat e lidhjes, dhe nëse të gjitha temat janë përdorur, lidhjet e reja refuzohen natyrisht. Le të shohim se sa i madh është pishina e fijeve:

ariaDB [(asnje)]> tregojnë variablat si ‘thread_pool_size’;
+——————+——-+
| Variabël_name | Vlera |
+——————+——-+
| thread_pool_size | 4 |
+——————+——-+

Pra, makina ime lejon një maksimum prej katër lidhjeve në të njëjtën kohë. Shtë interesante të theksohet se numri 4 vjen nga fakti se unë kam një procesor katër-core, që do të thotë se kompjuteri im mund të kryejë vetëm 4 detyra paralele në një kohë (Unë po flas për detyra vërtet paralele këtu, jo për ato të njëkohshme). Në mënyrë ideale, ky është kufiri që duhet të shtyjë vlerën e thread_pool_size, por në makinat e rritura më të mira përfitojnë një pikë. Nëse nuk doni t’i bëni të gjitha lidhjet e reja të presin dhe janë në rregull për të marrë një hit të performancës (përsëri, kjo është një zonë që mund të gjykoni më së miri bazuar në performancën e aplikacionit tuaj nën ngarkesë), duke e bërë atë deri në 8 mund të jetë një ide e mirë.

Sidoqoftë, vendosja e saj përtej 16 është një ide e tmerrshme nëse nuk keni një makinë 32-bërthamore, pasi performanca degradon në mënyrë të konsiderueshme. Vrima e lepujve të pishinave të fijeve në MySQL shkon thellë, por nëse jeni të interesuar, këtu është një diskutim më i hollësishëm.

Në pritje dhe pushime kohore

Sapo të krijohet një fije dhe t’i bashkëngjitet një klienti, do të ishte humbje e burimeve nëse klienti nuk dërgoi pyetje për disa sekondat e ardhshëm (ose minutat). Si rezultat, MySQL përfundon një lidhje pas një periudhe pasiviteti. Kjo kontrollohet nga ndryshorja e pritjes:

MariaDB [(asnjë)]> tregojnë variablat si ‘prisni%’;
+—————+——-+
| Variabël_name | Vlera |
+—————+——-+
| kohën e pritjes | 28800 |
+—————+——-+

Vlera që rezulton është në sekonda. Pra, po, MySQL është vendosur të presë 8+ orë para se të prish kordonin! Kjo mund të jetë e mirë nëse keni pyetje për një kohë të gjatë dhe në të vërtetë dëshironi t’i prisni ato (por edhe atëherë, tetë orë është absurde!) Por e tmerrshme në shumicën e rasteve. Kur një pyetje ekzekutohet, kjo vlerë vendoset në 0 (do të thotë përgjithmonë), por në përgjithësi, kjo duhet të vendoset në një vlerë shumë të ulët (5 sekonda, për shembull, ose ndoshta edhe më pak) për të çliruar lidhjen për proceset e tjera.

Akordimi i tabelave të përkohshme

Le të fillojmë me ato që janë tabela e përkohshme në MySQL.

Supozoni se kemi një MySQL që në mënyrë strukturore duket si kjo: TABELA NJ A BASHK (TABELA B INNER BASHKPO C). Kjo do të thotë, ne jemi të interesuar të bashkojmë tabelat B dhe C, dhe më pas të kryejmë një bashkim të rezultatit me tabelën A. Tani, MySQL së pari do të vazhdojë të bashkohet me tabelat B dhe C, por para se të mund të kryejë një bashkim, duhet për t’i ruajtur këto të dhëna diku. Kjo është ajo ku hyjnë tabela të përkohshme – MySQL i përdor ato për të ruajtur të dhënat në faza të ndërmjetme në pyetje komplekse përkohësisht, dhe pasi të mbarojë pyetja, kjo tabelë e përkohshme hidhet.

Tani shtrohet pyetja: pse duhet të shqetësohemi me gjithë këtë?

Thjesht sepse tabela e përkohshme, vetëm një rezultat i pyetjes, është të dhëna që janë duke u përdorur nga MySQL në llogaritje, dhe shpejtësia e hyrjes së saj (midis kufizimeve të tjera) do të përcaktojë se sa shpejt kërkesa ekzekutohet. Për shembull, ruajtja e tryezës së përkohshme në RAM do të jetë disa herë më e shpejtë sesa ta ruajë atë në disk.

Ekzistojnë dy ndryshore që kontrollojnë këtë sjellje:

MariaDB [(asnjë)]> tregojnë variablat si ‘MariaDB [(asnjë)]> tregojnë variablat si ‘tmp_table_size’;
+—————-+———-+

| Variabël_name | Vlera |

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

| tmp_table_size | 16777216 |

+—————-+———-+
‘;
+———————+———-+
| Variabël_name | Vlera |
+———————+———-+
| max_heap_table_size | 16777216 |
+———————+———-+

MariaDB [(asnjë)]> tregojnë variablat si ‘tmp_table_size’;
+—————-+———-+
| Variabël_name | Vlera |
+—————-+———-+
| tmp_table_size | 16777216 |
+—————-+———-+

E para, max_heap_table_size, na tregon se sa RAM mund të përdoret nga një tabelë MySQL (“grumbull” këtu i referohet këtu strukturës së të dhënave të përdorura në ndarjen dhe menaxhimin e RAM – lexoni më shumë këtu), ndërsa ajo e dyta, tmp_table_size, tregon se cila është madhësia maksimale e tabelës së përkohshme. Në rastin tim, të dy janë vendosur në 16 MB, megjithëse pika që unë po përpiqem ta bëj atë që vetëm rritja e tmp_table_size nuk do të funksionojë si e përgjithshme, MySQL do të mbetet akoma e kufizuar nga max_table_heap_size.

Tani vjen pika: nëse tabelat e përkohshme që krijohen janë më të mëdha se kufiri i lejuar nga këto ndryshore, MySQL do të detyrohej t’i shkruaj ato në hard disk, duke rezultuar në performancë jashtëzakonisht të dobët. Puna jonë tani është e thjeshtë: bëjmë çmos të hamendësojmë madhësinë më të saktë të të dhënave për tabela të përkohshme dhe tweak këto ndryshore në atë kufi. Sidoqoftë, do të doja të tregoja kujdes ndaj absurditetit: vendosja e këtij kufiri në 16 GB (duke supozuar se e keni këtë shumë RAM) kur shumica e tabelave tuaja të përkohshme janë më të vogla se 24 MB në madhësi është marrëzi – thjesht po harxhoni RAM që mund të ‘ janë përdorur nga pyetje ose pjesë të tjera të sistemit (cache, për shembull).

përfundim

Nuk është e mundur të mbulohen të gjitha variablat e sistemit në një artikull, apo edhe të gjitha ato të rëndësishme në një artikull kur vetë dokumentacioni MySQL përfshin disa mijëra fjalë. Ndërsa kemi mbuluar disa ndryshore universale këtu, unë ju inkurajoj të shikoni në variablat e sistemit për motorin që po përdorni (InnoDB ose MyISAM).

Rezultati im më i dëshirueshëm për të shkruar këtë artikull është që ju të largoni tre gjëra:

  1. MySQL është një pjesë tipike e softuerit që funksionon brenda kufijve të vendosur nga sistemi operativ. Nuk është ndonjë program misterioz që bën Zoti-di-çfarë dhe është i pamundur të shuhet. Gjithashtu, për fat të mirë, nuk është aq e vështirë për të kuptuar se si vendoset dhe kontrollohet nga ndryshoret e sistemit të tij.
  2.  Nuk ka asnjë cilësim të vetëm që do ta bëjë instalimin tuaj të MySQL të zmadhojë. Ju nuk keni zgjidhje tjetër përveç të shikoni brenda sistemeve tuaja të funksionimit (mbani mend, optimizimi vjen pasi aplikacioni të jetë në prodhim, jo ​​më parë), të bëni supozimet dhe matjet më të mira dhe të jetoni me realitetin se kurrë nuk do të jetë perfekt.
  3. Akordimi i variablave nuk është mënyra e vetme për të optimizuar pyetjet e shkrimit efikas është një tjetër çështje e madhe, por është diçka që do të trajtoj në një artikull tjetër. Por çështja është, edhe nëse keni bërë një analizë të ngjashme me perëndinë dhe keni rregulluar më së miri këto parametra, është akoma e mundur që ju të sillni gjithçka në një ndalesë mahnitëse.

Cila është ndryshueshme e sistemit tuaj të preferuar për akordim? ��

TAGS:

  • Baza e të dhënave

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