Autor: Jean Petrić, Engineering Manager at Productive
Napomena: U članku je izostavljeno većinu internih detalja o proizvodu. Nekoliko puta sam koristio pojednostavljena objašnjenja za MySQL indekse, pogotovo ako za isti koncept već postoji detaljnije objašnjenje online. Poveznice sam koristio gdje je prikladno.
U prvom dijelu teksta sam dao kontekst o tome kako radimo određene stvari u Productiveu. Zatim sam ukratko pojasnio deadlockove, te na kraju prikazao pristup koji smo upotrijebili da ih riješimo.
Asinkroni jobovi u Productiveu
U Productiveu koristimo Sidekiq za schedulanje asinkronih jobova. Neki od tih jobova su slanje emailova korisnicima kada zaborave evidentirati radno vrijeme. Drugi su batch jobovi koji trebaju ažurirati financijske podatke, kopirati određene objekte tako da je transakcija valjana, itd. Prije nekoliko mjeseci počeli smo primjećivati nagli porast deadlockova u aplikaciji. Konkretno, ti deadlockovi su se počeli pojavljivati kod batch Sidekiq jobova koji obrađuju velik broj relativno teških transakcija na našoj bazi podataka.
Što su deadlockovi?
Deadlockovi u bazama podataka se događaju kada se dvije transakcije ne mogu provesti jer jedna sadrži lock koji drugoj treba. Deadlockovi nisu problem sami po sebi, sve dok ne utječu na bazu podataka tako da u potpunosti zaustave određene transakcije. Međutim, potrebno je imati mehanizam za ponavljanje deadlockanih transakcija. Obično je dovoljno ponoviti samo drugu transakciju (onu koja je izazvala deadlock), jer MySQL upravo nju zaustavi kako bi oslobodio lock i omogućio da se nastavi prva transakcija.
Otkrivanje uzroka deadlockova u produkciji je teško. Teško ih je reproducirati, jer često ovise o opterećenju baze u određenom trenutku. Uključivanje dodatnog logiranja specifično za deadlockove je isto izazovno jer ti logovi mogu biti vrlo veliki. Čak i kad pronađete transakcije i njihove lockove, nije uvijek odmah jasno kako bi ih bilo najbolje riješiti.
Eksperiment
Priprema
Budući da je broj deadlockova u aplikaciji rastao iz mjeseca u mjesec, morali smo nešto poduzeti kako bismo spriječili ozbiljnije probleme. Napravili smo odvojenu testnu okolinu koja je vrlo slična produkcijskoj. Također smo promijenili dio produktnog koda kako bismo mogli lakše simulirati problematične Sidekiq jobove. Okruženje smo povezali s našim sustavom za nadzor kako bismo mogli bilježiti logove. Čim smo počeli masovno pokretati jobove, odmah smo primijetili deadlockove.
Otkrivanje deadlockova
Postoji više načina za otkrivanje deadlockova. Mi smo koristili dva. Prvi je innodb status, koji prikazuje operativne informacije o mehanizmu za pohranu, uključujući zadnji zabilježeni deadlock. Ovako izgleda ispis iz innodb statusa:
Ispis prikazuje samo jedan, zadnji otkriveni deadlock, s ispisom detalja o tablici u kojoj se deadlock dogodio, kao i lockane zapise i tip korištenih lockova. Ove se informacije bilježe za obje transakcije koje su sudjelovale u deadlocku.
Drugi način je baza podataka performance_schema koja sadrži podatke o svim trenutno aktivnim lockovima. Evo primjer kako izgleda ispis tablice data_locks, koja prikazuje sve trenutačno aktivne lockove (podatci u ovoj tablici su „živi”, što znači da se lockovi pojavljuju i nestaju u stvarnom vremenu, kako se SQL upiti izvršavaju):
Iz ovog ispisa možete vidjeti imaju li neki SQL upiti previše lockanih redova, što ponekad može dovesti do deadlockova (u našem slučaju je).
Analiza deadlockova
Analiza deadlockanih upita i njihovih lockova nije nam pomogla da identificiramo konkretne dijelova koda koji su uključeni u njihovo izvršavanje. A čak i da je, trebalo bi osigurati da sve transakcije izvršavaju upite istim redoslijedom kako bismo izbjegli deadlockove.
Proučavanjem innodb statusa otkrili smo koji se indeksi koriste i u kojim tablicama. Ispis jasno prikazuje koja je SQL transakcija bila uključena u deadlock, te koji je indeks i tip locka korišten. Svaki novi deadlock prebriše innodb status, tako da refreshanjem statusa možete analizirati koji se deadlockovi pojavljuju.
Iz ispisa smo uočili da MySQL ponekad koristi dva različita indeksa u istoj tablici u različito vrijeme. U oba slučaja su indeksi indeksirali jedan stupac (jednostavni indeks). Potrebno je istaknuti da će se različiti zapisi lockati ovisno o tome koji je indeks korišten, budući da tako MySQL osigurava integritet podataka.
Kada smo saznali o kojim se indeksima i tipovima lockova radi, daljnje smo analizirali performance_schemu, točnije njezinu data_locks tablicu. Ova se tablica sastoji od „živih” podataka o svim aktivnim lockovima. U njoj smo pronašli problematične lockove i zapise indeksa koje su zalockali (treba imati na umu da MySQL zaključava indeksne zapise, a ne same retke).
Optimizacija indeksa
Ova tablica nam je pomogla da shvatimo sljedeće: kada su dva indeksa korištena pojedinačno, MySQL mora lockati širi raspon indeksnih zapisa nego što je stvarno potrebno. Na primjer, naš je job trebao ažurirati jedan zapis D, ali je MySQL zalockao sve zapise između B i F, jer je to bio najmanji indeksni zapis koji je mogao zalockati. Dakle, zalockano je više redaka nego što je bilo potrebno, opterećenje Sidekiq jobova je bilo veliko, i ovo je dovelo do značajnog broja deadlockova.
Kao rezultat analize smo zaključili da trebamo optimizirati ta dva indeksa. Kako su problematični SQL upiti koristili oba indeksirana stupca u WHERE uvjetima, odlučili smo ukloniti te indekse. Umjesto njih, stavili smo složeni (kompozitni) indeks koji u sebi sadrži oba stupca. Naša hipoteza je bila da će manje redova biti zalockano ako su indeksni zapisi precizniji.
Kombiniranje dva stupca znači da MySQL može lakše pronaći zapise koje je potrebno ažurirati, što posljedično znači da će manje redaka biti zalockano. Kada smo proveli eksperiment u testnom okruženju, broj deadlockova je pao s 50,000 na 0.
Drugi eksperiment
Novi deadlockovi
Nakon ponovnog pokretanja eksperimenta s novim složenim indeksom, ponovno su se pojavili deadlockovi, ali sada u drugoj tablici. Simptomi su bili slični kao i oni od prije, pa smo opet pogledali innodb status i lockove i pokušali zamijeniti jednostavne indekse složenim. No, kada smo ponovili eksperiment vidjeli smo da se broj deadlockova zapravo udvostručio (s oko 300 na oko 600) s novim složenim indeksom, pa smo ga odlučili ne koristiti.
Zbunjeni ovakvim rezultatom, odlučili smo problem pogledati detaljnije. Tek kada smo pregledali shemu baze podataka, primjetili smo BEFORE INSERT trigger. Unutar njega se nalazio upit s dva stupca uvjeta u WHERE uvjetu. Jedan od tih stupaca imao je uzorak čija je tipizirana vrijednost za većinu kupaca bila „{n}“.
Ponovni pokušaj sa složenim indeksima
Za razliku od prvog eksperimenta gdje je kompozitni indeks riješio problem, isti pristup nam nije pomogao u ovoj situaciji. Problem je bio manjak varijabilnosti u stupcu s uzorkom (>98% uzorka je bilo {n}), zbog čega MySQL nije mogao suziti pretragu i zalockati manje zapisa. Rezultat toga je bio da je zalockao gotovu cijelu tablicu, što je značilo da su deadlockovi bili neizbježni. Nismo mogli pronaći druge stupce da oblikujemo prikladniji indeks, niti promijeniti trigger. Budući da je broj deadlockova u drugoj tablici bio relativno nizak, odlučili smo ne mijenjati ništa.
Zaključak
Na kraju smo odlučili živjeti s ponekim deadlockom. Shvatili smo da nam se na produkciji dogodi oko 50 deadlockova u periodima najvećeg opterećenja, ali često i manje. Točnije, zadnjih mjeseci ih promatramo, i tjedno ih imamo 16 u prosjeku. Važno je naglasiti i da optimizacija indeksa nije uvijek prikladno rješenje za deadlockove. Kao što smo pokazali u članku, sve ovisi o konkretnoj situaciji.
Također, deadlockovi nisu nužno loši, dokle god postoji strategija za ponovno pokretanje transakcija i baza radi normalno. Sve u svemu, više od 50,000 deadlockova smo riješili jednom jednostavnom promjenom indeksa.
Zaključno, našli smo način da se nosimo s deadlockovima izvan onoga što predlaže službena MySQL dokumentacija. Optimizacija indeksa se pokazala kao jednostavno, ali učinkovito rješenje. Međutim, promišljeno dodavanje indeksa isplati se i više nego da se kasnije uklanjaju zbog problema koje mogu prouzročiti.
