da pocnem sa omiljenim citatom "there is no silver bullet" .. dakle, ne postoji unverzalno resenje ali neke "osnove" vezano za optimizaciju:
neki uvod:
- sve sto napisem u ovom postu vazi za linux, solaris i ostale unixolike sisteme, ako terate db server na windozama vi nemate potrebu da taj db server bude optimizovan, ali vecina stvari iz ovog posta moze da se primeni i na windoze
- DB server treba da bude dedicated. Nije obavezno, nije neophodno, ali za iole ozbiljniji DB server - ocekuje se da je to jedini app koji se vrti na masini
neke osnove:
ako zanemarimo klaster koji je posebna kategorija i nema mnogo veze sa obicnim mysql-om .. treba da obratimo paznju na osnovna 3 resursa koja su bitna za bazu podataka:
1. CPU
2. RAM
3. IO
CPU
- kada je MySQL u pitanju, jedna konekcija je jedan thread, dakle jedna konekcija / jedan upit ne moze da iskoristi vise od jednog jezgra
- kada je MySQL u pitanju, sorage enginei kao i sam kor mysql-a se JAKO LOSE SKALIRA ... max procesorskih jezgara koji MySQL ume da iskoristi je 8 (da, osam), sve preko toga samo "smeta" ..
- baze podataka RETKO KORISTE CPU, tj, vrlo retko ce vam usko grlo na serveru biti CPU
RAM
- sto vise rama to bolje :D
- MySQL ima nekoliko "storage engine-a", od "siroko koristenih" i "dzaba" su bitni MyISAM i InnoDB. MyISAM NIJE transakcioni engine dok InnoDB to jeste. SVE transakcije su MEMORY BASED, znaci, ako nema dovoljno rama da se cela transakcija izvrsi u ram-u - transakcija nece proci. Primer, ako imas tabelu od 2G i uradis u jednoj transakciji promenu cele tabele, dakle imas 2G izmena a imas samo 1G slobodnog ram-a, transakcija nece proci
- mysql ume da kesira rezultate u ram-u
- mysql uma da kesira pristup disku u ram-u
IO
- ovo je glavno usko grlo svake baze podataka, podaci se nalaze na disku i odatle ih treba procitati.
- tek kada se podaci fizicki zapisu na disk i dobije potvrda o tome - podaci su sigurni
- u IO spada i mreza, slanje i primanje paketa kroz mrezu isto opterecuje IO sistema
neke optimizacione osnove vezane za mysql bazirane na osnovama malopre pomenutim
1. CPU - ovde nema sta mnogo reci ... uzeti server sa vise od 8 jezgara da bude mysql server je "bacanje para", 4 jezgra je idealno, dakle, za mysql, mnogo bolje ce posao raditi dual core na 4GHz nego 16core masina na 2GHz ...
2. RAM
podesiti swappiness parametar kernela (linux) na 0 (default od 60 je ok za desktop ali zastrasujuce los za server), slican parametar postoji i za ostale unix-e
dati InnoDB-u sto vise rama za innodb_buffer_pool_size. To je bafer gde innodb kesira pristup disku, on to sam mnooogo bolje radi nego sistem cache tako da mu treba dati sto vise rama ovde. Za InnoDB only sistem 70-80% ram-a je idealna cifra, za sisteme koji nisu 100% innodb (dakle koriste i MyISAM ili neke druge storage engine) neka manja cifra je ok
query cache
- za sisteme sa mnogo vecim odnosom read : write veliki query_Cache je super stvar (kesira rezultate upita), (do 1G)
- na sistemima gde postoji veliki broj upisa/promena koriscenje "query hintova" gde se mysql-u kaze koje upite da kesira a koje ne je uputno uz veliki query cache (100M - 2G)
- na sistemima gde je broj upisa veliki i nad istim je podacima kao citanje, query cache treba ugasiti ili setovati na vrlo malu cifru (1-3M)
Ako ne znas sta i kako, ok je staviti nekih 10-20M pa pratiti query cache hit ratio
ostali baferi .. ostale bafere podesiti vezano za upotrebu aplikacije - ovde ne postoje "generalni saveti"
3. IO
- NEMOJTE KORISTITI NAS za storage subsystem, SAN je ok ali NAS ubija performanse jaaaako
- za MyISAM razdvojite indexe od podataka na razlicite spindlove (na razlicite diskove - ne razlicite particije istog diska)
- za InnoDB log bacite na razlicite spindlove od onih gde je data fajl
- LVM ne sluzi nicemu ako koristiti InnoDB
- za InnoDB koristite ODIRECT kako bi zaobisli duplo kesiranje (izbegnete kesiranje fajl sistema) na linuxu
- InnoDB podatke stavite na "forcedirectio" particiju na solarisu (slicno kao ODIRECT na linuxu)
toliko za sada ... spremam malo "opsezniji" tekst EDIT:
http://mysql.crsndoo.com/wp/2009/03/optimizacija-mysql-servera/ eto ga tu ...
[Ovu poruku je menjao bogdan.kecman dana 07.03.2009. u 00:19 GMT+1]