Optimizacija u MySQL 5.1

Optimizacija u MySQL 5.1

offline
  • Pridružio: 01 Apr 2005
  • Poruke: 797
  • Gde živiš: Niš

Uvod

Sistem za upravljanje bazama podataka (database management system, DBMS) je softverska komponenta namenjena rukovođenju bazama podataka, velikim skupom struktuiranih podataka, i izvršavanju operacija nad podacima koje zahteva veliki broj korisnika. Tipične upotrebe ovih sistema uključuju bankarske sisteme, računovodstvo, sve vrste web i desktop aplikacija i mnoge druge.

Osnove MySQL-a


MySQL је višenitni, višekorisnički SQL sistem za upravljanje bazama podataka (DBMS), sa oko deset miliona instalacija. MySQL AB obezbeđuje MySQL kao slobodan softver pod GNU licencom.

phpMyAdmin je klijent pisan u programskom jeziku PHP za potrebe administriranja MySQL sistema baza podataka . On pruža usluge kreiranja I uništavanja baza podataka, tabela, vrsta, izvođenje raznih operacija sa tabelama, dumpovanje baza I tabela, podešavanje engine-a, kreiranje, brisanje korisnika, izvršavanje SQL upita I mnoge druge.

U unix okruženju moguća je administracija MySQL servera direktno iz komandne linije korišćenjem mysql klijenta.

Popularnost ovog DBMS-a je sve veća, s’obzirom da je besplatan i da je konkurentan sa ostalim DBMS-ovima po kriterijumu funkcionalnosti i performansi. Veoma se dobro pokazao u kombinaciji sa PHP-om. Popularnost ove kombinacije se može pripisati open source prirodi oba proizvoda te postojanjem besplatnih verzija za sve glavne funkcionalnosti, što olakšava njihovo učenje i korišćenje kako početnicima tako i profesionalcima.


Optimizacija sistema

Najbitniji faktor optimizacije jednog sistema leži u osnovi njegovog projektovanja. Moramo unapred znati kakvu vrstu procesiranja podataka naš sistem obavlja i koje su njegove osnovne komponente, kao I šta u tom sistemu predstavlja “usko grlo”, kako bi znali gde traba izvršiti optimizaciju. Koreni neoptimalnosti, na koje najčešće ne možemo da utičemo leže u:
• Pretraživanje podataka na hard disku. Kod savremenih diskova ova vremena su ispod 10ms. Ovo vreme se postepeno smanjuje napretkom tehnologije. Optimizacija bi se ovde svela na distribuiranje podataka na više hard diskova.
• Čitanje i upis podataka. Kada se glava diska nalazi na željenoj poziciji, treba pročitati podatke. Kod novih diskova brzina čitanja je oko 20MB/s. Naravno da je lakše optimizovati pretraživanje podataka, jer možemo čitati podatke paralelno sa više diskova.
• Procesorsko vreme. Kada podatke imamo u memoriji, moramo da ih obradimo da bismo dobili željeni rezultat.
• Propusni opseg memorije. Kada je procesoru potrebno više podataka nego što može da stane u njegov interni keš, propusni opseg glavne memorije postaje usko glo. Ovo nije uobičajen problem za većinu sistema, ali I o tome se treba voditi računa kada se žele vrhunske performanse.
Ograničenja MySQL-a
Kada koristimo MyISAM engine za smeštanje podataka, MySQL koristi ekstremno brz sistem zaključavanja tabela koji dozvoljava više čitaoca ili jednog pisca. Najveći problem sa ovim engine-om za smeštanje podataka se javlja kada imamo konstantne update-ove I spore select upite na jednoj tabeli. Ako je ovo problem sa određenim tabelama, poželjno je koristiti neki drugi engine za smeštanje podataka radi postizanja boljih performansi. To mou biti InnoDB, HEAP, NDB…
MySQL može da radi sa transakcionim i netransakcionim tabelama. Ukoliko radimo sa netransakcionim tabelama dobičemo na brzini, ali neamo mogućnost da povratimo podatke u slučaju da nešto krene naopako sa operacijom koja se izvršava (npr ako se ona iznenadno prekina iz nekog razloga). U tu svrhu se može koristiti ključna reč IGNORE u kombinaciji sa INSERT ili UPDATE.

Projektovanje prenosivih aplikacija
Zbog toga što svaki od DBMS implementira različite delove SQL standarda, dosta je teško napraviti prenosivu(kompatibilnu) aplikaciju. Jednostavno je postići kompatibilnost sa prostim selelectima i insertima, ali što oni postaju komplikovaniji, to je teže zadržati kompatibilnost uz očuvanje performansi. Tako da ovde projektant mora da napravi kompromis između prenosivosti i performansi.
Da bi kompleksna aplikacija bila prenosiva, pre početka samog projektovanja moramo da odlučimo sa kojim DBMS-ovima mora da bude kompatibilna, i da onda vidimo koje mogućnosti svaki od njih pruža. Možemo koristiti MySQL crash-me program da bi pronašli funkcije, tipove, i ograničenja DBMS-a. Na primer korišćenjem crash-me programa, možemo dobiti informaciju da ne možemo da koristimo imena kolona duža od 18 karaktera ako želimo da radimo sa DB2 ili Informix-om.
Crash-me i MySQL benchmark su nezavisni od konkretnoh DBMS-ova. Napisani su u Perl-u I koriste DBI interfejs. Korišćenje DBI interfejsa rešava mnoge probleme kompatibilnosti jer nudi metode pristupa koje su nezavisne od konkretnog DBMS-a.
MySQL benchmark
MySQL benchmark nam omogućava da predvidimo ponašanje i performanse operacija koje nam nudi ovaj DBMS. Izvorni kod benchmark-a je dostupan i nalazi se u sql-bench direktorijumu u bilo kojoj MySQL izvornoj distribuciji.
Ovaj benchmark je jednonitni, tako da će uzeti minimalno vreme za izvršenje tražene operacije. U planu je implementacija višenitnog benchmarka koji će dati realniju sliku ponašanja sistema.
Optimizacija SELECT upita
Prvi faktor koji utiče na sve upite koji se izvršavaju jeste složenost korisnikovih privilrgija. Korišćenje jednostavnijih privilegija u GRANT upitu omogućava MySQL-u da optimizuje (redukuje) proveru privilegija kada kada korisnik izvršava upite. Na primer ako ne ograničavamo pristup na nivou tabela ili kolona, server ne mora da proverava tabelu privilegija tabela i tabelu privilegija kolona. Slično tome, ako ne postavimo ograničenja vezana za resurse, server ne mora da provrava korišćenje resursa.
Tako ako imamo veliko opterećenje sistema sa dosta upita koji se vrlo frekventno izvršavaju, pojednostavljenje šeme privilegija može umnogome da optimizuje rad.
Ako je problem vezan za specifične MySQL izraze ili funkcije, možemo primeniti tajming test korišćenjem BENCHMARK() funkcije iz mysql klijenta. Njena sintaksa je BENCHMARK(loop_count,expression). Povratna vrednost uvek nula, ali mysql štampa aproksimativnu procenu vremena izvršenja upita. Na primer;
mysql> SELECT BENCHMARK(1000000,1+1);
+------------------------+
| BENCHMARK(1000000,1+1) |
+------------------------+
| 0 |
+------------------------+
1 row in set (0.32 sec)
Ovaj rezultat je dobijen na Pentium II 400MHz mašini, što pokazuje da MySQL može da izvrši 1.000.000 sabiranja za 0.32 sekunde na takvom sistemu.
Sve MySQL funkcije su optimizovane do visokog nivoa, mada ima i nekih izuzetaka. BENCHMARK() je odlična alatka za lociranje funkcije koja pravi probleme u našim upitima.
Optimizacija upita korišćenjem ključne reči EXPLAIN
EXPLAIN tbl_name
Ili:
EXPLAIN [EXTENDED | PARTITIONS] SELECT select_options
Može se koristiti kao sinonim za DESCRIBE ili kao način da se dobiju informacije o tome kako MySQL izvršava SELECT upit:
• EXPLAIN tbl_name je sinonim za DESCRIBE tbl_name ili SHOW COLUMNS FROM tbl_name.
• Kada izvršimo SELECT upit sa ključnom rečju EXPLAIN, MySQL prikazuje informacije prosleđene od strane optimizatora o planu izvršenja upita. To je objašnjenje kako MySQL procesira upit, uključujući i informacije o tome na koji način su tabele spojene, kojim redosledom…
• Konstrukcija EXPLAIN PARTITIONS je dostupna sa verzijom MySQL 5.1.5. Korisna je samo kada ispitujemo upite koji uključuju parcijalizovane tabele.
Uz pomoć EXPLAIN, možemo videti gde su nam neophodni indeksi za brže izvršenje SELECT upita. Možemo takođe videti kada optimizator spaja tabele optimalnim redosledom, a kada ne. Možemo ga naterati da koristi redosled stajanja tabela u optimalnom redosledu. Da bi spajali tabele u redosledu navedenom u upitu, koristimo SELECT STRAIGHT_JOIN umesto SELECT.
Ukoliko imamo problem da indeksi nisu korišćeni kada mi verujemo da trebaju, možemo pokrenuti ANALYZE TABLE za statistiku o kardinalnosti ključeva, što može dovesti do promene ponašanja optimizatora.
Brzina SELECT upita
Generalno, kada želimo da izvršenje našeg sporog SELECT … WHERE upita ubrzamo, prva stvar koju treba da proverimo jesu indeksi. Sve veze među tabelama treba da budu ostvarene korišćenjem indeksa. Možemo koristiti EXPLAIN da otkrijemo koji indeksi se koriste u našem SELECT upitu.
Evo još nekih načina za ubrzanje upita nad MyISAM tabelama:
• Da bi pomogli MySQL-u da optimizuje upite, koristimo ANALYZE TABLE ili pokrećemo myisamchk-analyze nad tabelom pošto ona biva napunjena podacima.Ovo osvežava vrednost svakog indeksa koji ukazuje na više kolona koje imaju istu vrednost (za UNIQUE indekse je uvek jedan). MYSQL ovo koristi da odluči koji indeks da izabere pri spajanju dve tabele kada je spajanje bazirano na nekom izrazu koji nije konstanta. Takođe možemo koristiti SHOW INDEX FROM tbl_name da bi ispitali cardinalnost veze. myisamchk --description –verbose prikazuje informacije o indeksima takođe.
• Da bi sortirali podatke u zavisnosti odindeksa, koristimo myisamchk --sort-index --sort-records=1 (uz pretpostavku da želimo sortiranje po indeksu 1). Ovo je dobar način da ubrzamo izvršenje upita ukoliko imamo unique indeks pomoću kog želimo da pročitamo sve vrste u redosledu koji zavisi od tog indeksa. Kada prvi put sortiramo veliku tabelu na ovaj način, to može da potraje.
Optimizacija WHERE klauzule
Ovde će biti reči o optimizaciji WHERE klauzule. U primerima ćemo koristiti SELECT upite, ali ista logika važi i za DELETE i UPDATE upite.
MySQL optimizator sa svakom novom verzijom postaje sve bolji, ali uvek ima mesta za optimalnije napisan upit, koji MySQL ne može da optimizuje bez naše pomoći.
Evo nekih optimizacija koje MySQL čini kada je u pitanju WHERE klauzula:
• Uklanjanje suvišnih zagrada:
o ((a AND b) AND c OR (((a AND b) AND (c AND d))))
o -> (a AND b AND c) OR (a AND b AND c AND d)
• Evaluacija konstanti:
o (a<b AND b=c) AND a=5
o -> b>5 AND b=c AND a=5
• Uklanjanje suvišnih uslova:
o (B>=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6)
o -> B=5 OR B=6
• COUNT(*) nad jednom tabelom bez WHERE klauzule biva direktno pročitana iz MyISAM i MEMORY tabela. Ovo je takođe slučaj za bilo koji NOT NULL izraz kada radimo sa samo jednom tabelom.
• Brza detekcija nevalidnih izraza. MySQL brzo detektuje da neki SELECT upit neće vratiti podatke.
• HAVING je integrisan sa WHERE ako ne koristimo GROUP BY ili agregacione funkcije (COUNT(), MIN(), i slične).
• Za svaku tabelu u spoju, jednostavniji WHERE je konstruisan da bi se dobila što brža evaluacija i što brže preskakanje nepotrebnih vrsta.
• Konstantne tabele bivaju pročitane pre bilo kojih drugih tabela u upitu. Konstantne tabele su:
o Prazna tabela ili tabela sa jednom vrstom.
o Tabela koja je korišćena u WHERE klauzuli na PRIMARY KEY ili UNIQUE indeksu.
Sve tabele u narednom primeru su korišćene kao konstantne:
SELECT * FROM t WHERE primary_key=1;
SELECT * FROM t1,t2
WHERE t1.primary_key=1 AND t2.primary_key=t1.id;
• Najbolja kombinacija za spajanje tabela se dobija pokušavanjem svih mogućnosti. Ako sve kolone u ORDER BY i GROUP BY klauzuli pripadaju istoj tabeli, ta tabela treba da bude prva u spoju.
• Ako postoji ORDER BY klauzula i druga GROUP BY klauzula, ili ako ORDER BY ili GROUP BY klauzula sadrži kolone tabela koje nisu prve u redu za spajanje, kreira se privremena (temporary) tabela.
• Ako koristimo SQL_SMALL_RESULT opciju, MySQL koristi privremenu tabelu koja se nalazi u glavnoj memoriji.
• Pre izbacivanja vrsta, one koje ne zadovoljavaju HAVING klauzulu bivaju odbačene.
Evo primera nekih veoma brzih upita:
SELECT COUNT(*) FROM tbl_name;

SELECT MIN(key_part1),MAX(key_part1) FROM tbl_name;

SELECT MAX(key_part2) FROM tbl_name
WHERE key_part1=constant;

SELECT ... FROM tbl_name
ORDER BY key_part1,key_part2,... LIMIT 10;

SELECT ... FROM tbl_name
ORDER BY key_part1 DESC, key_part2 DESC, ... LIMIT 10;
MySQL rešava sledeće upite korišćenjem samo stabla indeksa:
SELECT key_part1,key_part2 FROM tbl_name WHERE key_part1=val;

SELECT COUNT(*) FROM tbl_name
WHERE key_part1=val1 AND key_part2=val2;

SELECT key_part2 FROM tbl_name GROUP BY key_part1;
Sledeći upiti koriste indekse da dobave vrste u sortiranom redosledu, bez dodatnog prolaska algoritma za sortiranje:
SELECT ... FROM tbl_name
ORDER BY key_part1,key_part2,... ;

SELECT ... FROM tbl_name
ORDER BY key_part1 DESC, key_part2 DESC, ... ;

Optimizacija LEFT JOIN i RIGHT JOIN upita
Optimizator spoja izračunava redosled u kome tabele trebaju biti spajane. Možemo sami nametnuti redosled spajanje korišćenjem by LEFT JOIN or STRAIGHT_JOIN i na taj način pomoći optimizatoru da svoj deo posla obavi mnogo brže, zato što je potrebno manje permutacija tada proveriti.Primetimo da ako imamo upit sledećeg tipa:
SELECT *
FROM a JOIN b LEFT JOIN c ON (c.key=a.key)
LEFT JOIN d ON (d.key=a.key)
WHERE b.key=d.key;
MySQL radi “full scan” nad tabelom b, zato što LEFT JOIN zahteva da je pročita pre d.
Optimizacija bi se u ovo slučaju svela na obrtanje redosleda u kome navodimo a i b u FROM klauzuli:
SELECT *
FROM b JOIN a LEFT JOIN c ON (c.key=a.key)
LEFT JOIN d ON (d.key=a.key)
WHERE b.key=d.key;
Za LEFT JOIN, ako je uslov u WHERE klauzuli uvek false za generisanu NULL vrstu, LEFT JOIN se pretvara u običan JOIN. Na primer, uslov u WHERE klauzuli će biti false u sledećem upitu ako t2.column1 bude NULL:
SELECT * FROM t1 LEFT JOIN t2 ON (column1) WHERE t2.column2=5;
Zato je sigurnije da napišemo klasičan join:
SELECT * FROM t1, t2 WHERE t2.column2=5 AND t1.column1=t2.column1;
Ovo može biti brže zato što MySQL može koristiti tabelu t2 pre t1 ako to daje bolje rezultate kod planiranja upita. Da bi nametnuli specifičan redosled koristimo STRAIGHT_JOIN.

Optimizacija INSERT upita
Vreme potrebno za unos vrste u tabelu je determinisano sledećim faktorima, gde brojka u zagradi označava aproksimaciju proporcije ukupnog vremena:
• Povezivanje: (3)
• Slanje upita serveru: (2)
• Parsiranje upita: (2)
• Unos vrste: (1 × veličina vrste)
• Unos indeksa: (1 × broj indeksa)
• Zatvaranje: (1)
Veličina tabele usporava unos indeksa za logN, indeksi implementirani pomoću B-stabla.
Možemo koristiti sledeće metode da optimizujemo INSERT upite:
• Ako unosimo više vrsta sa istog klijenta u isto vreme, koristimo INSERT upite sa više VALUES listi da bi uneli više vrsta istovremeno. Ovo je u većini slučajeva nekoliko puta brže od korišćenja odvojenih INSERT upita za svaku vrstu.
• Ako dodajemo podatke u nepraznu tabelu, možemo koristiti bulk_insert_buffer_size promenljivu da učinimo unos podataka bržim.
• Ako više klijenata unosi mnogo vrsta, možemo dobiti ubrzanje korišćenjem INSERT DELAYED konstrukcije.
• Sa MyISAM tabele, možemo koristiti konkurentne inserte da bi dodavali vrste za vreme izvršenja SELECT upita.
• Kada učitavamo tabele iz tekstualne datoteke, koristimo LOAD DATA INFILE. Ovo je obično oko 20 puta brže od INSERT upita.

Optimizacija UPDATE upita
Update upiti su optimizovani kao i SELECT upiti, sa dodatkom za upis. Brzina pisanja zavisi od količine podataka koje osvežavamo i broja indeksa koje osvežavamo. Indeksi koji ostaju nepromenjeni nisu obuhvaćeni UPDATE operacijom.
Drugi način da dobijemo brze update-ove je da ih čuvamo i onda uradimo više njih odjednom kasnije. Izvršenje više update-ova odjednom je mnogo brže od pojedinačnog izvršenja svakog od njih ako pritom vršimo zaključavanje tabela.
Za MyISAM tabele koje korisre dinamički format vrsti, update-ovanje vrste sa povećanjem njene dužine, može je podeliti na dve vrste. Ako ovo činimo često, veoma je važno da koristimo OPTIMIZE TABLE konstrukciju.
Optimizacija DELETE upita
Vreme potrebno za brisanje vrste je proporcionalno broju indeksa. Da bi obrisali vrste što brže, možemo povećati veličinu keša ključeva korišćenjem key_buffer_size sistemske promenljive.
Ostali načini optimizacije
Evo još nekoliko načina optimizacije u MySQL-u:
• Korišćenje perzistentne(stalne) konekcije na bazu ubrzava sve operacije sa bazom, jer nemeamo gubljenje resursa I vremena na povezivanje, a to je jedna od zahtevnijih operacija. Ako pak ne možemo da koristimo perzistentnu konekciju I stalno inicijalizujemo mnogo novih konekcija ka bazi, možemo izmeniti vrednost thread_cache_size sistemske promenljive.
• Treba izbegavati komplikovane SELECT konstrukcije nad MyISAM tabelama koje se frekventno update-uju, da bismo izbegli probleme sa zaključavanjem koji se javljaju između čitaoca i pisca.
• Replikacija može poboljšati neke performanse. Možemo distribuirati klijentovo pretraživanje na replikacione servere da bi podelili protok podataka. Da bismo izbegli usporenje mastera dok pravi backuo-ove, možemo ih praviti korišćenjem slave servera.
• Da bi popravili rezultate koji se dobijaju korišćenjem ARCHIVE tabela, možemo koristiti OPTIMIZE TABLE.
• Za MyISAM tabelekoje se često menjaju, možemo pokušati da izbegnemo sve tipove kolona promenljive dužine(VARCHAR, BLOB, and TEXT). Ovakve tabele koriste dinamički format za vrste, što je kritično kod frekventnog update-a.
• Možemo koristiti INSERT LOW_PRIORITY kada želimo veći prioritet da damo SELECT upitima.
• Korišćenjem SELECT HIGH_PRIORITY možemo postići da preskočimo klijentski upis koji stoji u redu čekanja na izvršenje i momentalno izvršimo naš SELECT. LOW_PRIORITY i HIGH_PRIORITY uzimaju efekta samo kod engine-a za smeštanje podataka koji koriste samo zaključavanje na nivou tabela (MyISAM, MEMORY, MERGE).
• Korišćenje LOAD DATA INFILE za velike količine podataka je mnogo brže od INSERT upita.
• Korišćenje AUTO_INCREMENT polja je korisno, tako da svaka vrsta može biti identifikovana jedinstvenom vrednošću.
• Slike I drugi binarni sadržaji mogu biti sačuvani kao fajlovi. Zato treba čuvati samo adrese (putanje) do njih na serveru, a ne sadržaj celih fajlova u bazi. Većina Web servera je bolja u keširanju fajlova od samog DBMS-a, tako da je korišćenje fajlova generalno brže.
Optimizacija baze podataka je veoma kompleksan zadatak, zato što zahteva razumevanje principa rada celokupnog sistema. Mada je moguće primeniti neke loičke optimizacije sa malo osnovnog znanja o sistemu ili aplikaciji koju želimo optimizovati. Jedno je sigurno, što bolju optimizaciju želimo, to više znanja moramo posedovati.

Ovde sam pokušao da objasnim i dam primer različitih načina optimizacije u MySQL-u. Naravno, uvek postoji način da sistem postane brži, potrebno je eksperimentisati i dublje proučavati sam sistem.



Registruj se da bi učestvovao u diskusiji. Registrovanim korisnicima se NE prikazuju reklame unutar poruka.
offline
  • Peca  Male
  • Glavni Administrator
  • Predrag Damnjanović
  • SysAdmin i programer
  • Pridružio: 17 Apr 2003
  • Poruke: 23058
  • Gde živiš: Niš

ovaj clanak bi bio i vise nego koristan u forumu 'Baze podataka'.
smem li tamo da ga pomerim, i izdvojim ga kao Članak ?



offline
  • bocke  Male
  • Moderator foruma
  • Glavni moderator Linux foruma
  • Veliki Pingvin
  • Guru
  • Pridružio: 16 Dec 2005
  • Poruke: 12247
  • Gde živiš: Južni pol

Odlican clanak... Upravo nesto petljam sa mysql+php kombinacijom... Tako da ce mi koristiti.

offline
  • Pridružio: 28 Apr 2005
  • Poruke: 3686
  • Gde živiš: The Circle

Izdvajate ovo u Baze podataka, odlicno stivo Wink

offline
  • Pridružio: 01 Apr 2005
  • Poruke: 797
  • Gde živiš: Niš

Naravno da možeš Peco. Ovaj članak je i nastao kao odgovor na pitanje o optimizaciji u MySQL-u koje je neko postavio na forumu 'Baze podataka'.
Bilo bi dobro kada bih negde mogao da attachujem verziju sa slikama i lepšim formatiranjem koju sam odradio u Ms Word-u.

offline
  • Pridružio: 28 Apr 2005
  • Poruke: 3686
  • Gde živiš: The Circle

Pa mozes ovde da attachujes Wink

offline
  • Pridružio: 01 Apr 2005
  • Poruke: 797
  • Gde živiš: Niš

Evo originalne full word verzije mog članka o optimizaciji. Nadam se da će mnogima koristiti.
https://www.mycity.rs/must-login.png

Ko je trenutno na forumu
 

Ukupno su 668 korisnika na forumu :: 36 registrovanih, 5 sakrivenih i 627 gosta   ::   [ Administrator ] [ Supermoderator ] [ Moderator ] :: Detaljnije

Najviše korisnika na forumu ikad bilo je 1567 - dana 15 Jul 2016 19:18

Korisnici koji su trenutno na forumu:
Korisnici trenutno na forumu: A.R.Chafee.Jr., amaterSRB, Apok, Bane san, Cufo, Cvijo_ue, darkangel, Dimitrise93, djordje92sm, esx66, Filodendron, FOX, Georgius, havoc995, jovan.krcmar, Kubovac, matorigile, Megapurpletv, Mihajlo, mirbat, nenad81, perko91, rkekoke, rodoljub2, saputnik plavetnila, Sibin, SOVO515, SsssssNOVI, USSVoyager, Vlada1389, vladetije, vlvl, voja64, Vojo Ostoic, wolverined4, |_MeD_|