Pre nekog sitnog vremena jedna firma me zamolila da im malo protrkeljam sistem da vidim sta bi moglo da se ubrza posto imaju veliku bazu, zahtev za velikim brojem upita (uglavnom read) ali im sve to vrlo traljavo radi. Po iskustvu pretpostavio sam neke osnovne greske u postavci sistema (los server, lose iskonfigurisan mysql, ugasen ili lose podesen query cache etc) ali kada sam stigao na teren – sve moje pretpostavke su bile pogresne. MySQL je bio vrlo optimalno konfigurisan, server je zmija (8 jezgara, 15000RPM diskovi u raidu, mnogo rama…)… sumnja je naravno odma pala na lose upite. Pogledavsi upite, ni tu nije bilo sta mnogo da se „ubrza“ … neke sitnice smo ubrzali par stotina puta no to nije mnogo uticalo na ukupnu sporost celog sistema, te sam se usresredio na samu strukturu i kvalitet podataka.
Baza ima stotinjak tabela od kojih su vecma sve poprilicno male po horizontali – sto generalno govori da je baza prilicno dobro normalizovana. Atributske tabelice su reda velicine par stotina do par hiljada slogova sto nije puno, ali „glavna“ velika tabela, iako dobro normalizovana, ima oko 250 miliona slogova. Upiti koji se izvrsavaju nad tom tabelom i mnoze sa atributskim tabelama zahtevaju prolazak kroz veliki broj slogova i ma koliko indexi bili korisni ako imate agregatnu funkciju nad 10000 slogova, to prosto znaci da morate da prodjete kroz 10000 slogova, procitate svaki, uzmete vrednost, ubacite je u agregatnu funkciju … tu indexi na zalost ne pomazu mnogo … Posle malo dublje analize zakljucio sam da bi bilo realno da se ta glavna tabela particionise po jednom delu primarnog kljuca. Ako pogledamo kako radi partitioning na mysql-u zakljucicemo da je svaka operacija nad vise particija „mnogo“ sporija nego u slucaju da nema particija ali sam ja ovaj put resio da batalim „opste brojke“ i izmerim sam u realnom sistemu kakva je razlika. Operacije koje idu nad CELOM tabelom (znaci SVIM particijama) su radile oko 2.5% sporije nego na neparticionisanim podacima – dakle prilicno zadovoljavajuci rezultat ako se pokaze da se upiti nad jednom particijom izvrsavaju brze (posebno sto tih upita ima znacajno vise). Podaci nad jednom particijom su radili oko 40 PUTA brze :). I ne samo da je brzina izvrsavanja svakog upita veca, tokom izvrsavanja upita stres nad samim serverom je mnogostruko manji (i do 200 puta manji IO, 30tak puta manji user cpu usage …)
Grafikoni koje vidite pokazuju CPU USAGE i AVERAGE LOAD na serveru u nekom vremenskom periodu. Pretpostavite trenutak kada smo ukljucili particionisanje podataka :)
MySQL podrzava partitioning od verzije 5.1. U osnovi, MySQL partitioning radi tako da vasu tabelu podeli u male tabele (svaka particija fizicki na disku postane zasebna tabela) i kada optimizer prepozna da moze upit da izvrsi nad samo jednom particijom, operaciju vrsi nad tom jednom „tabelicom“, ako upit mora da izvrsava nad vise tabela, onda se odredjuje da li moze da sekvencijalno koristi jednu pa drugu pa … ili mora da pravi „join“ izmedju particija. U slucaju da se pravi join tada je pristup particionisanim podaci nesto sporiji nego kada oni nisu particionisani. U slucaju upisa i slicno podatak se upisuje u „odgovarajucu tabelu“. Ono sto je kod particionisanja vrlo zgodno je to sto mozete
- Obrisati podatke iz particije (recimo da particionisete podatke po godini, brisanje svih podataka iz jedne godine se u ovom slucaju svodi na brisanje jednog fajla sa diska)
- Popraviti jednu particiju (u slucaju korupcije, repair se vrsi nad samo jednim segmentom tabele, ne nad celom tabelom)
- U slucaju da su vam particije tipa myisam, svaka particija moze da bude na svom disku i to posebno data posebno index sto vam daje mogucnost da rasprostrete tabelu na mnogo diskova povecavajuci time max IO koji masina moze da da
Ako resite da probate mysql paritioning, obavezno procitajte i deo o limitima istog.
e jebo si mu kevu svaka ti cast :) nisam ni sumnjao u tebe. E da sto se tice particionisanja sam sam ga radio ‘rucno’ i nad bazom imamo jedan php layer koji je ‘razmisljao’ koji dio tabele (tj ovaj put fizicki razlicite tabele) da query i ima li potrebe da zalazi u druge to je moglo da radi i da mysql ‘pogura’ preko 3-4M slogova jer sam bar u mom konkretnom slucaju ‘udario’ na 4M slogova plafon (sa 4.11 mysql cini mi se)
Koji su se fajl sistemi pokazali najbolji za cuvanje particija (recimo myisam tabela pa je rasirimo na vise diskova)?
Zanemari ZFS ;)
Свакако веома занимљиви (и очигледно добри) резултати.
Мени су за сада парцијални индекси у Постгресу решавали проблем па дељење табела није било неопходно (ради се о табелама са преко 100М редова — за Launchpad.net). Да ли то значи да MySQL нема парцијалне индексе (знам да DB2 нема, на пример — тамо је једно решење прављење погледа па индексирање истих, али то подразумева и измену свих упита да раде на погледима)?
Успут, поред парцијалних индекса, ипак је била неопходна и денормализација базе, пошто је потпуно нормализована радила упите кроз три табеле реда величине 100М, а то смо одговарајућим „спајањем“ смањили на једну. Мада, Постгрес доста добро ради и са „ширим“ релацијама, па то није био проблем, али не знам како се MySQL понаша у таквим ситуацијама.
Но, нама је акценат био на брзини извршавања упита, а оптерећење сервера никад није било до те мере изражено (уосталом, имамо и неколико реплика базе само за читање) — ограничење за све упите потребне за једну веб страницу нам је око 10 секунди (а то делимично и објашњава зашто нам оптерећење сервера никад и није било проблем — упите убијамо када прође 15-20 секунди од њиховог покретања).
Такође, кад се већ распитујем — има ли еквивалента „EXPLAIN“ и „EXPLAIN ANALYZE“ наредбама из Постгреса за МајЕсКјуЕл? :)
zoko, hvala hvala .. generalno mozes uvek da radis sharding sa extra layer-om samo u ovom slucaju to nije bilo potrebno.
vanja, za MyISAM ja koristim skoro iskljucivo XFS. U kombinaciji sa external locking-om za neke HA varijante OCFS2 i GFS2. Za InnoDB je poprilicno svejedno posto InnoDB ima „svoj“ file system.
danilo, MySQL nema partial indexe. Sto se denormalizacije tice, vrlo cesto se pribegava tom resenju iako u preko 50% slucajeva kada je to resenje primenjeno moglo se primeniti „neko bolje“ resenje. Ne znam sta je ispod lp-a tako da nemam razlog da sumnjam da ste vi to uradili na najbolji moguci nacin. Sto se „sirih“ relacija tice, mysql to radi vrlo dobro / odlicno. U verziji 5.1 i 5.5 cak dosta bolje od pgsql-a, dok u verziji 5.0 i manjim poprilicno losije od pgsql-a (pricamo o velikim kompleksnim join-ima) a i to samo ako pricamo o dva osnovna storage engine-a (myisam/innodb).
mysql ima explain i explain extended ali rade SAMO sa SELECT-om, (ne mozes da uradis explain update/delete .. ). Momci vec neko vreme rade da to malo extenduju ali o tome kada bude bilo gotovo.
Danilo, mysql ima mogucnost da particionira indexse i tabele koliko ja znam ali nisam 100% siguran
Sinisa, da i ne .. ono o cemu Danilo prica su „partial indexi“ koje ima pgsql. Na pgsql-u mozes da particionises *samo* indexe – data ti ostane neparticionisana, to ubrzava neke (ne sve) radnje sa podacima. Ne znam da li je planirano i particionisanje date za pgsql posto particionisanje samo indexa u 90% slucajeva radi posao. Ono sto je super kod ovog pristupa koji ima pgsql je sto mozes da imas neke indexe nad tabelom koji su particionisani i neke koji nisu, tako da nemas usporenje koje npr imas na mysql-u ako radis upit nad celom tabelom a ne samo iz nekoliko particija. mysql sa druge strane radi samo potpunu particionizaciju, dakle particionisu se i data i indexi, to ubrzava open neke (ne sve) radnje. Ovde je pristup samo iz jedne particije brzi nego u slucaju pgsql-a gde su samo indexi particionisani ali je penalty sa druge strane ako se vadi info iz cele tabele .. cak i ako se vadi iz „mnogo“ particija u najboljem slucaju je 10% sporije nego na neparticionisanoj tabeli. Najbolji pristup ima oracle koji particionise sve (i data i indexe) ali moze da ima lokalne i globalne indexe, tako da su lokalni indexi particionisani a globalni nisu – to je idealna varijanta koja ubrzava sve radnje, ali na zalost danas ni mysql ni pgsql to ne podrzavaju… za mysql znam da nece biti uskoro podrska za globalne indexe nad particionisanom tabelom, za pgsql – nemam pojma, Danilo mozda zna, ja sam malo izasao iz stosa sa pgsql-om a ceo pgsql tim iz sun-a je „otisao“ kada nas je preuzeo oracle tako da sam malo izgubio dodir i sa te strane..