Optimalizace db

vím že optimalizace mysql, dotazů, indexů atd je kapitola sama o sobě ale přesto...mohli byste tu všichni shrnout nějaký svoje tipy, všeobecný věci na co si dávat pozor, co sledovat a tak?začal bych téma pár obecnýma věcima co dělám já:- tabulky se snažím rozvhrnout tak aby nedocházelo k tomu že bude pár tabulek s extrémním množstvím dat a velkým provozem ale zároveň ani příliš malých tabulek. pokud mám třeba velké množství statistik tak používám zvlášť db pro systém a pro statistiky- indexy dávám většinou ze začátku na sloupce kde mám v dotazech group nebo where, případně na kterých se provádí join, samozřejmě ale jen na ty nejpoužívanější aby zase indexů nebylo zbytečně moc a u každé tabulky se snažím mít unikátní index pokud není situace že by to bylo opravdu zbytečné. potom případně ještě s indexy experimentuju za provozu- co se týká dotazů tak při insert uvádím sloupce kam se budou vkládat data, u select zase vždy sloupce co chci načíst a nikdy "*"- taky na každém dotazu kde to jde používám limit- pokud je aplikace s extrémním provozem kde se například počítá hodně statistik nebo jiných dat tak si na to udělám zvlášť tabulku kam dělám jen inserty a data pak přepočítávám scriptem co spouští cron, zase je potřeba odladit vhodnou dobu aby cron neběžel příliš dlouho nebo se jich nenakupilo několik naráz. při jeho spuštění udělám exec "cp" a "chown" na vytvoření kopie tabulky, do kopie potom nelezou další data a můžu s ní v klidu pracovat, navíc takhle je kopie vytvořená ve zlomku sekundy narozdíl od normálního kopírovaní v mysql (kdyby měl někdo zájem, uvedu příklad)- no a nakonec u aplikací s velkým provozem používám pro mysql ssd disk aby db nebyla omezená rychlostí čtení / zápisu diskusou to jen základy ale když tu každý přidá nějaký tipy, mohlo by z toho být zajímavý téma, pokud si ještě na něco vzpomenu tak doplním. co by třeba mě dost zajímalo tak rady k optimalizaci indexů a jestli lze nějak odsledovat jaký dotaz se hodně opakuje, nebo jaký dotaz žere hodně cpu/ram atd aby člověk věděl na co se při optimalizaci zaměřit.

Par navrhu a napadu pro tvorbu a optimalizace sql Pri velkych projektech pouzijte noSQL databazi. Co je noSQL databaze ? Je to databaze ktera neni relacni takze nevytvari relace (preklady selectu) priklad takove databaze je napr.: elasticsearch jeji vyhoda je ze v ni muze byt obsazeno klidne milion nebo miliony radku ktere lze krasne prohledavat rychle. Zapis do teto databaze zavisi od pameti ram pri 2gb ram je schopna zapsat rychle az cca 2.mil zaznamu.Ale podme k MySQL: Velky projekt velka zatez na MySQL ? Proc potom stavet databazi klasickym spusobem jak sme zvykli, co tabulka to nejaka rozdilna data priklad: zakladni uzivatelska data: id,login,heslo,emailprofilova uzivatelska data: id,user_id,jmeno, prijemni, adresaTato volba neni zas tak spatna no pri obrovskych projektech s nespocetnou masou dat neni moc prijatelna napr. jako facebook , mysql by to moc nechtelo zvladat nebo by to nacitalo prilis dlouho. A proto se zvoli noSQL struktura v MySQL.Jak to funguje ?Data se proste kopiruji a kopiruji, databaze roste objemove (v MB) no pri relacnich databazich je pro procesor a spracovani nacteni spousty zaznamu lepsi cist z nekolik radku vsechno nez z nekolik tabulkulek nekolik radku. Takze napriklad pro uzivatele mame tabulku: users - id - login - pass - email Tabulka jako kazda jina. V cem je tedy kouzlo ? Tabulka pro detail uzivatelu (profil) user_profile - id - user_id - login - email - address - forename - surname ... a dalsi dataU teto tabulky si vsimneme ze jseem z tabulky user skopiroval login email , tim padem nemusim joinovat users pro nacteni dat z ni. A takto to funguje u vsech tabulek, pokud chci vytvorit napriklad tabulku pro seznam cen tak ta by vypadala napriklad takto: - id - user_id - user_login - user_forename - user_surname - price - created - stateKde si skopiruji data z user_profile a user pro vypsani dat o uzivateli v cenach. A takhle jsou tvoreny vsechnyu tabulky, tim padem se vyhneme veskerym spojovanim tabulek.Nacteni tim padem probiha velice rychle. Samozrejme je potreba uvazovat i nad indexama dle toho co nejvic budeme vyhledavat atd... A samozrejme jako vsechno to ma i sve nevyhody. A to: pri update to muze trvat nekdy strasne dlouho jelikoz pri update se musi updatnout vsechny zaznamy ve vsech tabulkach v kterych se update nachazi. A proto je dobre pouziti memcache , pri castych update se vytizi mysql a nacita z memcache, pak se zmena updatne i v memcache. Problem s memcache je kdyz spadne servr tak je potrebne nahodit celou memcache znovu, co pri startovani pri velkych objemech dat muze trvat nekdy i 3 - 4 hod. :) zalezi dle objemu dat. V opacnem pripade je mozne do memcache ukladat data za behu co se zobrazilo hodi se rovnou do memcache a dalsi nacitani se jiz nacita z ni. Ale pro nacitani je to velice spolehlive. ;) A samozrejme jak vyse pan psal , nepouzivat select * .No a pokud jsme uz u toho memcache take neni na skodu ;)Doufam ze Vam to pomuze na ceste pri tvorbe velkych projektu.

S tou NoMysql strukturou bych byl v rámce optimalizace opatrný a tlumil přehnané nadšení z výkonu. Viz. například názor v http://frantovo.cz/blog/?q=humbuk-kolem-nosql-databazi s kterým celkem souhlasím.Nasadit NoMysql databázi může mít svůj smysl, ale spíš v ojedinělých případech, jinak totiž většinou zaděláváme na mnohem větší problémy. Ano třeba pro rozvětvené diskuze to může být zajímavé. Nicméně v čechách moc projektů s takovou návštěvností aby to mělo smysl použít nebude.Nasazovali jsme MongoDB s tím jak úžasně je to rychlé. Mě tedy vstávaly vlasy hrůzou na stylem psaní dotazů. Pro jednoduché selecty a inserty ok, ale propojit třeba 10 tabulek, to aby si na ted dotaz člověk psal dekompiler aby se v něm dalo vyznat. Navíc jakmile bylo třeba hlídat relace, provést nějakou kontrolu, nebo chtít reporty ... v tu chvíli byla ona úžasná rychlost noSQL řešení vykoupena asi 100násobným nárůstem náročnosti na podobné činnosti, vývoj a údržbu. Takže bych tohle řešení rozhodně nedoporučoval nějak globálně jako optimalizační techniku. Tou totiž není.Obecně k optimalizaci:- analyzovat co od DB chceme a co skutečně pro nás znamená DB optimalizace, může to totiž být víc věcí a často se některé mohou vylučovat. Někdy je vhodná denormalizace, pro reporty, někdy naopak normalizace pro zrychlení.- profilery a logování pro zjištění slabých míst a podle toho optimalizace dotazů, volba a úprava indexů, někdy taky změna workflow a postupů- měření času dotazů, může se zvyšovat HW, nebo dělit výkon na více DB strojů- slabé místo může být v přenosu dat, pokud tahám dat moc, nebo ve vysokém počtu volání DB protože naopak tahám dat na jeden dotaz málo - obecně použít indexy taky není samospásné, protože se tím snižuje rychlost zápisu, takže někde jsou vysloveně nevhodné, stejně tak záleží na typu indexu, někdy se hodí cluster indexy, někdy jiné. Občas je při dotazu vhodné explicitně vynutit jiné použití indexu nebo optimalizace SQL dotazu DB enginem, protože jde o jiné řešení než obecně univerzálně ideální- podstatné z hlediska optimalizace je také nastavení DB serveru, práce s pamětí, daty, časy, logování, transakce a spousta dalšího.Tohle je tak rozsáhlé téma, že dělat pro něj vlákno bude znamenat že se tu bude plést spousta rad dohromady a bojím se že přínos bude spíš negativní. Optimalizace databází se obvykle věnují celé knihy a to ještě rozdělené na nastavení DB stroje z hlediska administrátora a pak na optimalizace SQL z hlediska developera/db vývojáře.

Napsal duben;839662

S tou NoMysql strukturou bych byl v rámce optimalizace opatrný a tlumil přehnané nadšení z výkonu. Viz. například názor v http://frantovo.cz/blog/?q=humbuk-kolem-nosql-databazi s kterým celkem souhlasím.

Nasadit NoMysql databázi může mít svůj smysl, ale spíš v ojedinělých případech, jinak totiž většinou zaděláváme na mnohem větší problémy. Ano třeba pro rozvětvené diskuze to může být zajímavé. Nicméně v čechách moc projektů s takovou návštěvností aby to mělo smysl použít nebude.

Nasazovali jsme MongoDB s tím jak úžasně je to rychlé. Mě tedy vstávaly vlasy hrůzou na stylem psaní dotazů. Pro jednoduché selecty a inserty ok, ale propojit třeba 10 tabulek, to aby si na ted dotaz člověk psal dekompiler aby se v něm dalo vyznat. Navíc jakmile bylo třeba hlídat relace, provést nějakou kontrolu, nebo chtít reporty ... v tu chvíli byla ona úžasná rychlost noSQL řešení vykoupena asi 100násobným nárůstem náročnosti na podobné činnosti, vývoj a údržbu.

Takže bych tohle řešení rozhodně nedoporučoval nějak globálně jako optimalizační techniku. Tou totiž není.

Obecně k optimalizaci:

- analyzovat co od DB chceme a co skutečně pro nás znamená DB optimalizace, může to totiž být víc věcí a často se některé mohou vylučovat. Někdy je vhodná denormalizace, pro reporty, někdy naopak normalizace pro zrychlení.

- profilery a logování pro zjištění slabých míst a podle toho optimalizace dotazů, volba a úprava indexů, někdy taky změna workflow a postupů

- měření času dotazů, může se zvyšovat HW, nebo dělit výkon na více DB strojů

- slabé místo může být v přenosu dat, pokud tahám dat moc, nebo ve vysokém počtu volání DB protože naopak tahám dat na jeden dotaz málo

- obecně použít indexy taky není samospásné, protože se tím snižuje rychlost zápisu, takže někde jsou vysloveně nevhodné, stejně tak záleží na typu indexu, někdy se hodí cluster indexy, někdy jiné. Občas je při dotazu vhodné explicitně vynutit jiné použití indexu nebo optimalizace SQL dotazu DB enginem, protože jde o jiné řešení než obecně univerzálně ideální

- podstatné z hlediska optimalizace je také nastavení DB serveru, práce s pamětí, daty, časy, logování, transakce a spousta dalšího.

Tohle je tak rozsáhlé téma, že dělat pro něj vlákno bude znamenat že se tu bude plést spousta rad dohromady a bojím se že přínos bude spíš negativní. Optimalizace databází se obvykle věnují celé knihy a to ještě rozdělené na nastavení DB stroje z hlediska administrátora a pak na optimalizace SQL z hlediska developera/db vývojáře.

Ano to je pravda ja mluvil o projektech rozsahlych a take jsem popsal ze to ma i sve nevyhody. No pokud jste cetl jak by MySQL struktura noSQL fungovat mela tak by jste dosel k nazoru ze neco jako JOIN by se nepouzilo. Pracoval jsem na obrovskem projektu : beepl.comKde noSQL struktura byla samozrejmosti. Kouzlo je pouze v tom ze data kopirujete do novych tabulek. Je to pouzitelne i pro normalni projekty s vetsi navstevnosti a obrovskou databazi. Ano je pravda je to o navrzeni databaze a popremysleni nad tim kde se indexy hodi a kde zase ne to plati i pro tento priklad noSQL v MySQL. Vyuziti to ma mnoho, hlavne muj prispevek byl zameren na MySQL v strukture noSQL. Pro male projekty samozrejme je urcite vyhodnejsi a lepsi klasicka struktura jak pises.

softdream > moc pekne napsany, presne takhle to na mysql a velkych projektech delam ale takto bych to nevysvetlil :) o elasticsearch sem jeste neslysel. jak bude vic casu tak se na to budu muset podivat jestli by to zrovna pro me melo prinos u nekterych projektuduben > mohl bys trochu rozepsat to logovani pro zjisteni slabych mist? ve scriptu si behem vyvoje / optimalizace delam nejruznejsi logovani abych zjistil co nejvic ale docela by me zajimalo jestli se da primo na mysql automaticky logovat napriklad dotazy co trvali dlouhou dobu, zbytecne prochazeli celou tabulku atd. teoreticky kdyz ve stavu na phpadminu vidim hodnoty Slow_queries, Handler_read_rnd, Handler_read_rnd_next a dalsi tak by to mohlo nejak jit. pri optimalizaci by hodne pomohlo kdybych se podival kde sou vysoky hodnoty, na to si dal logovani a pak prosel co to zpusobuje za dotazy. pokud to teda jde

stix: Logovat to jde, já osobně takhle MySQL nenastavoval, dělám spíš s MSSQL. Ale admin mi na serveru logoval všechny pomalé query a posílal mi je v logu. Jde hlídat co bylo spuštěno, kdy a jak dlouho to trvalo.Malá nápověda k nastaveníhttp://dev.mysql.com/doc/refman/5.1/en/query-log.htmlhttp://www.mydigitallife.info/enable-logging-of-slow-queries-slow-query-log-in-mysql-database/

pridam dalsi postrehy: SSD DISKY ukazalo se ze u velkych projektu kde je opravdu extremni zapis (zaznamenal sem u 50-100k online, zapisy u kazde navstevy) se mysql na ssd disku opravdu nehodi. sice disk zvlada rychle ale kdyz je vystaveny takovemuto zapisu tak vydrzi tyden az dva a potom odejde (vyzkouseno s vice disky, o chybu hw neslo). v tomto pripade sou vhodnejsi SAS disky. MYSQL CACHE potvrdilo se mi ze v pripade kdy je velky pocet cteni ktere sou vetsinou unikatni se hodi cronem promazavat cache. ze zacatku se mi to moc nezdalo ale po spousteni ""FLUSH QUERY CACHE" kazdych 5min byl narust vykonu opravdu extremni. dostal sem se ze situace kdy server nestihal a byl nepouzitelny na situaci ze ted i s dvojnasobnym trafficem jede bez sebemensiho problemu. samozrejme toto muze byt dost idividualni ale urcite to stoji za pokus. FLASHCACHE nebudu rozepisovat az tak do detailu, ve zkratce - jedna se o cache modul od facebooku ktery taky muze hodne navysit vykon. na vyber sou tri mody, zalezi na vas a projektu co bude nejlepsi. vic info tady - https://github.com/facebook/flashcache/ MARIADB kdyz oracle koupil mysql tak nekolik puvodnich autoru se oddelilo a pokracovali ve vyvoji mysql vlastni cestou a to pod nazvem MariaDB. do budoucna se chcou vydat uplne vlastni cestou ale aktualne jeste nejsou moc rozdily oproti mysql a mariadb je vykonnejsi takze staci pouze vypnout mysql, nainstalovat mariadb a neni potreba menit scripty atd, hned fungujete s rychlejsi db. pro jistotu ale doporucuju procist si MariaDB vs. MySQL - Compatibility vic info tady - https://mariadb.org/ PERSISTENT CONNECTION pokud mate hodne spojeni na db, stoji za to vyzkouset zmenit zpusob spojeni aby se udrzovali trvale. k db se misto mysql_connect pripojite fci mysql_pconnect a nebudete volat mysql_close. spojeni se nebudou ukoncovat a php se pred pripojenim podiva jestli uz spojeni existuje a pokud ano tak ho pouzije (zatim nemam vyzkousenou situaci s vetsim trafficem a mysqli).

Napsal softdream;839615

Par navrhu a napadu pro tvorbu a optimalizace sql

Pri velkych projektech pouzijte noSQL databazi.

Toto si nemyslel vazne. Ze nie ?