Zigawo Zitatu za Cache Pakati pa Select ndi Disk

Pulogalamu yanu ikayatsa mawu a SELECT, funsoli silikhudza sining disk kapena ngakhale flash storage — limadutsa magawo atatu a cache omwe amatsimikizira mwakachetechete ngati yankho lanu lifika mu microseconds kapena milliseconds. Kumvetsetsa zigawozi ndikusiyana pakati pa nsanja yabizinesi yomwe imakwera mopepuka ndi yomwe imamanga pansi pa katundu weniweni.

Chimachitika ndi Chiyani Pomwe Funso Losankha Likasiya Ntchito Yanu?

Pulogalamu yanu ikatumiza                                                                                                                     usasaimbe], izi zimafika pa njira yabwino imene opanga ambiri samayiona. Injini ya database imasokoneza pempholo I/O isanakwane, ndikuyika SQL kukhala dongosolo lakuphatikizira mkati ndikuyang'ana njira yake yoyamba yodzitchinjiriza: cache yotsatila. Ngati funso lomwelo lomwe lili ndi magawo ofanana lidachitidwa posachedwa, injiniyo imatha kubweza zotsatira zosungidwa popanda kukhudza tsamba limodzi la data. Izi nthawi zina zimatchedwachosungira chafunsokapena posungira zotsatira, komanso pa ntchito zowerenga kwambiri, zolembedwa zochepa - monga ma analytics dashboards ndi ma modules operekera malipoti - zikhoza kuthetsa kuchuluka kwa disk kuwerengedwa kwathunthu.

Chidziwitso chofunikira apa ndikuti cache yamafunso imakhudzidwa kwambiri ndikusintha kwa data. INSERT, UPDATE, kapena CHOFUTA zotsutsana ndi tebulo ilili zikulepheretsa zotsatira zosungidwa. Ichi ndichifukwa chake machitidwe olembera-olemera nthawi zambiri amalepheretsa kache yafunso kwathunthu ndikudalira zigawo zakuya m'malo mwake.

Kodi Buffer Pool Ndi Chiyani Ndipo Chifukwa Chiyani Ili Yofunika Kuposa Mukuganiza?

Chigawo chachiwiri cha cache - ndipo mosakayikira chofunika kwambiri mu machitidwe opangira - ndi dziwe la buffer (lotchedwa buffer yogawana mu PostgreSQL, InnoDB buffer pool mu MySQL). Ili ndi gawo la RAM lomwe injini ya database imagwiritsa ntchito kusunga masamba omwe apezeka posachedwa. Ngati funso silingatumizidwe kuchokera pazotsatira, injiniyo imayang'ana ngati masamba ofunikirawo ali kale mu buffer pool isanatulutse disk yowerengedwa.

Bafa dziwe limagwira ntchito motsatira mfundo zanthawi ndi malo: zomwe zapezeka posachedwa zitha kupezekanso, ndipo zomwe zasungidwa pafupi ndi zomwe anthu adafikirako zitha kupezeka posachedwa. Oyang'anira database amakonza kukula kwa dziwe ngati imodzi mwazisankho zapamwamba kwambiri zomwe amapanga. Malo osungira omwe ali ochepa kwambiri amachititsa kuti masamba atulutsidwe nthawi zonse, kumapanga chodabwitsa chotchedwa thrashing, pamene makina amatha nthawi yochuluka kuyang'anira cache zomwe zaphonya kusiyana ndi kufunsa mafunso.

Key Insight: M'ntchito zambiri za OLTP, dziwe lalikulu la bafa limatanthauza 95–99% ya data yonse yowerengedwa imachokera ku RAM. Seti yogwira ntchito - kagawo kakang'ono ka deta yanu yomwe mafunso amakhudza pafupipafupi - nthawi zambiri imakhala yaying'ono kwambiri kuposa kukula kwa database yonse. Kukulitsa dziwe lanu losungitsa chitetezo kuti ligwirizane ndi zomwe mukugwira ntchito, osati gulu lanu lonse la data, ndiye njira imodzi yokha yobwezera yomwe mungathe kuchita.

Kodi Cache Yogwiritsa Ntchito Imadzaza Bwanji Kusiyana Pakati pa RAM ndi Disk?

Ngakhale buffer pool ya database ikaphonya, funso silinalembedwe kuti disk yowona iwerengedwe. Makina ogwiritsira ntchito amakhala ndi cache yamasamba (yomwe imatchedwanso cache system cache), dera la RAM loyendetsedwa ndi kernel lomwe ma buffers amawerenga ndikulemba kuti atseke zida. Injini ya database ikapempha tsamba lomwe palibe ku dziwe lake, OS kernel imayang'ana posungira masamba ake isanapereke lamulo la I/O kwa wowongolera.

Chigawo chachitatu ichi ndi chosawoneka kwa opanga mapulogalamu koma chofunikira kwambiri pamakina omwe malo osungirako malo osungirako zinthu zakale sakuperekedwa. Cache ya tsamba la OS imagawidwa m'njira zonse, kotero imapikisana ndi seva yanu yogwiritsira ntchito, seva yapaintaneti, ndi mapulogalamu ena aliwonse omwe akuyenda pagulu lomwelo. Pa ma seva odzipatulira a database, mpikisanowu ndi wocheperako, ndipo cache ya OS imapereka mwayi wopeza mwayi wachiwiri. Pa zosungira zogawana kapena zotengera zomwe zili ndi malire okumbukira, kache ya OS nthawi zambiri imakhala yaying'ono kwambiri kuti isawathandize.

Ndi Cache Layer Iti Imene Ili Ndi Udindo Wopambana Kwambiri Pantchito?

M'makina opangira zinthu zenizeni padziko lapansi, dziwe la buffer limayang'anira zotulukapo zamasewera ambiri. Ichi ndichifukwa chake gawo lililonse limathandizira mosiyana pamagwiritsidwe ntchito:

• chosunga chotsatira cha mafunso: Phindu lapamwamba pa zowerengera zolemetsa, zambiri zokhazikika - mafunso ochitira lipoti, ma dashboard osungidwa, zomaliza za anthu. Zopanda ntchito pamatebulo olembetsera.
• Database buffer pool: The universal workhorse. Seva iliyonse yopangira database iyenera kuyang'aniridwa apa poyamba. Imagwira bwino ndi njira zofikira mwachisawawa komanso zotsatizana.
• Posungira tsamba la OS: Amapereka chitetezo pamene dziwe la bafa likucheperachepera. Zimathandizanso kwambiri pakuwunika motsatizana kwa matebulo akulu omwe angatulutse masamba otentha padziwe la bafa.
• Cache yosungiramo zinthu (zosanjikiza): Gawo lachinayi, lomwe nthawi zambiri limanyalanyazidwa - Ma NVMe SSD ndi owongolera a RAID amasunga zosunga zolembera zam'mwamba zokhala ndi batire kapena capacitor. Izi zimateteza kukhazikika popanda kusiya zolembera mopanda fsync latency.
• Application-layer cache (Redis, Memcached): Imakhala pamwamba pa nkhokwe yonse, ndikusunga zotsatira za mafunso otsatizana kapena zinthu zophatikizika kuti zisagunde mnkhokwe nkomwe — ndizoyenera mapulatifomu ambiri a SaaS omwe amagwiritsa ntchito masauzande ambiri ogwiritsa ntchito nthawi imodzi.

Kodi Mapulani Amakono Amakono Angagwiritsire Ntchito Bwanji Cache Zomangamanga Kuti Zikhale Zodalirika pa Sikelo?

Kwa mabizinesi omwe akugwira ntchito m'ma module ambiri - CRM, kasamalidwe ka polojekiti, malonda a e-commerce, analytics - kamangidwe ka cache kumatsimikizira kuyankha kwa nsanja pamene magulu akukula. Mapulatifomu omangidwa panjira yosungidwa bwino amatha kugwiritsa ntchito masauzande ambiri ogwiritsa ntchito nthawi imodzi popanda mtengo wofananira wa zomangamanga. Mfungulo ndiyo kupanga njira zopezera deta zomwe zimalemekeza malire a kache: kusunga deta yotentha kukhala yaying'ono ndi njira zodziwikiratu, kugwiritsa ntchito zowerengera zowerengera kuti mugawitse katundu wa pool, ndikuyika cache-layer cache monga Redis kutsogolo kwa malo osungirako zinthu zomwe zimapereka deta yofanana kwa ogwiritsa ntchito angapo nthawi imodzi.

Mewayz idapangidwa ndi malingaliro ake ndendende. Ndi ma module 207 ophatikizika amabizinesi omwe ali ndi mphamvu pa ogwiritsa ntchito 138,000, kusanja kwa data papulatifomu kudapangidwa kuti zowerengeka zambiri zizitumizidwa kuchokera ku cache - kusunga nthawi yoyankha mwachangu komanso mtengo wa zomangamanga uyenera kudziwika ngati mukuyenda pa $19/mwezi poyambira kapena $49/mwezi akatswiri.

Mafunso Ofunsidwa Kawirikawiri

Kodi kuyimitsa kache ya mafunso nthawi zonse kumapangitsa kuti database ikhale yabwino?

Osati nthawi zonse, koma zolemba zolemetsa zomwe zimachita. Chosungira chafunso chimafuna mutex yapadziko lonse lapansi kuti isasunthike, zomwe zimakhala zotsekereza chifukwa cha ndalama zambiri. MySQL 8.0 inachotsa cache yafunso kwathunthu pazifukwa izi. PostgreSQL sinakhazikitsepo cache yamafunso omangidwa, kudalira dziwe la buffer ndi caching-layer caching. Ngati chiŵerengero chanu chowerengera ndi kulemba ndichokwera kwambiri ndipo mafunso anu akungobwerezabwereza, chosungira cha mafunso chikhoza kukupatsani phindu lenileni - apo ayi, ikanipo mwayi wokonzekera ku dziwe la bafa.

Kodi ndingadziwe bwanji ngati dziwe langa losungiramo chitetezo ndiloyenera?

Yang'anirani kuchuluka kwa kugunda kwa ma buffer pool anu: kuchuluka kwa zopempha zamasamba zomwe zatumizidwa kuchokera ku dziwe motsutsana ndi zomwe zimafuna kuti disk iwerengedwe. Chiŵerengero chogunda pansi pa 95% pa ntchito ya OLTP ndi chizindikiro chowonjezera kukula kwa dziwe. Mu MySQL, funsani SHOW ENGINE INNODB STATUS ndipo yang'anani kuchuluka kwa kugunda kwa dziwe. Mu PostgreSQL, mawonedwe a pg_statio_user_tables amawulula milu ya milu yowerengedwa kuchokera ku disk motsutsana ndi dziwe la buffer. Yesetsani kusunga ndondomeko yanu yonse yogwira ntchito - osati deta yanu yonse - kukhala mu RAM.

Kodi pali ubale wotani pakati pa magawo a cache ndi kudalirika kwa ma SaaS ambiri?

Mu SaaS yobwereketsa zambiri, zigawo zosungira zimalepheretsa "oyandikana nawo aphokoso" pomwe kufunsidwa kolenda kwa mlendi kumachepetsa magwiridwe antchito a lendi ena onse. Caching yodziwa kugwiritsa ntchito malo okhala ndi TTL-based invoidation imasunga deta yotentha ya munthu aliyense ku Redis, kuchepetsa kwambiri kuthamanga kwa dziwe kuchokera ku akaunti zazikulu. Kulumikizana mulingo wapa database limodzi ndi dziwe lofunda la bafa kumawonetsetsa kuti zochitika zomwe zachitika muakaunti imodzi iliyonse sizichotsa masamba omwe adagawana kuchokera pankhokwe ndikupangitsa kuti pakhale kuchedwa kwapapulatifomu.

Cache layers si trivia ya database - ndi maziko omanga omwe amalekanitsa nsanja zomwe zimakhala mwachangu kuchokera kuzomwe zimafunikira kuzimitsa moto nthawi zonse. Ngati mukumanga kapena kuchita bizinesi yomwe ikufunika nsanja yokongoletsedwa kale kuti ikwaniritse zenizeni izi, onani Mewayz pa app.mewayz.com — Ma module 207, pulatifomu imodzi yogwirizana, yomangidwa kuti igwire ntchito modalirika kuyambira wogwiritsa ntchito woyamba kufika pa zana la chikwi.