Optimalizace MySQL a dotazů

[Dakado]

Zdravím, chtěl bych se optat na pár věci ohledně MySQL databáze, respektivě jak ji co nejvíce optimalizovat, jelikož databáze je pod extrémní zátěží a ve špičce probíhá více než 150 dotazů za vteřinu a právě v této chvíli se docela hodně zpožduje odpověd databáze na dotazy.
Konkretně průměr dotazů vypadá takto:
ø za hodinu: 171 124
ø za minutu: 2 852
ø za sekundu: 48

Nevím jestli je MySQL špatně nastavená, nebo nestíhá takový nápor dotazů (což mi přijde nepravděpodobné), MySQL má vyhrazených 6 GB RAM, a 4 jádra Xeonu 1650 v2, ale limity tohoto vyhrazeného výkonu vůbec nedosahuje. Nejvíce četné dotazy jsou select, set a update. Veškeré dotazy probíhají z mého vlastního softwaru napsaného v Javě, takže je možno optimalizovat i z druhé strany.

Problém je v tom, že dochází velmi často k otevírání zhruba 200 tabulek a mám pocit že se ty tabulky pořád ukládájí a otevírají z disku, protože využití paměti MySQL je velmi malé, ač v configu má všude přiřazené nehorázně vysoké hodnoty paměti.


Poprosil bych Vás o nějaké tipy, či rady.

Děkuji.

[Reklama]

[CZechBoY]

Zdravím,
nastavení MySQL serveru je defaultní nebo optimalizované (třeba přes MySQLTunner)?
Něco ve slow query logu?

[Dakado]

Nastavení je default ale s přidanými hodnotami paměti RAM.
Slow query mám vypnuté, bojím se aby tohle logování nezpůsobilo ještě větši latenci.

[CZechBoY]

Zkus ten mysqltunner.
Píšeš, že stejně mysql využívá málo paměti, tak ji ještě zvyš.

[Dix]

Obecne o HW bys mel hodne premyslet, protoze vetsinou tam ziskavas nejvetsi performance boost.
1) Jak velka je databaze? Radove v MB/GB
2) Vejde se ti ta DB do pameti?
3) Na jaky disk ukladas? (klasika 3.5 vs SSD ti muze znamenat nekolikanasobne vyssi rozdil)

Jinak si myslim, ze vetsinou zas tak extremne samotnou DB nevytunis. Spis by ses mel zabyvat, jestli ta tvoje Java aplikace je optimalni, co se dotazovani tyce. Napr. jak je namodelovany DB schema, eager vs lazy loading, pocet JOIN operaci - to jsou veci, ktery ti mohou vyrazne zrychlit dotazy a tim pak i samotnou DB

[omilis]

Tak optimalizací můžeš získat několikanásobně vyšší výkon (ale samozřejmě ne vždy). Když jsem ve škole dělali na Oraclu, tak se po optimalizaci zvýšila propustnost DB asi 3x. MySQL sice nemá takové možnosti jako Oracle, ale i tak bych optimalizaci určitě udělal.

Používáš MySQL Workbench? Měl by tam jít pěkně zobrazit plán vykonání dotazu, kde zjístíš nejslabší místa (např. sekvenční průchod tabulkou pro zjíštění jedné hodnoty).

Dále bych se podíval na ORM. Používaš nějaký framework, nebo sis to napsal sám? Protože jsou frameworky psány obecně, tak můžou být pomalejší.

[Dix]

Jasne, nerikam ze to nedokazes zoptimalizovat. 3x zvysit propustnost, to muselo byt pro nejaky hodne modelovy priklad, kdy jste se snazili doopravdy sit to na miru konkretnim dotazum. Jinak mi to prijde az podezrele velke zvyseni.

Ja jsem spis chtel rict, ze dneska ve vetsine pripadu ta pomalost DB byva spis neefektivne napsanyma aplikacema (napr. nevhodne pouzivanym ORM frameworkem apod) nebo kolikrat neprilis stastne zvolenym HW. Casove (financne) pak tenhle investovanej cas se vetsinou vyplaci vic.

Taky jsme si to (neni to tak davno, co jsem "vychodil") ve skole vsechno pocitali. Jak moc ti pomuze to ci to. A jak dlouho to trva, kdyz se musi delat fulltable scan atd... Ale vim, ze to z povahy nebyva uplne snadne a byva doopravdy jednodussi a efektivnejsi nejdriv ladit ty veci, co jsem napsal

[omilis]

to muselo byt pro nejaky hodne modelovy priklad, kdy jste se snazili doopravdy sit to na miru konkretnim dotazum

Tak o tom právě ta optimalizace je, uděláš si analýzu, které dotazy se používají a jak často a pro ty nejčastější optimalizuješ. Budeš nejspíše chtít optimalizovat SELECTy, tak tam přidáš index, který ale zhorší INSERT a UPDATE, což ti nejspíše nevadí. Ale samozřejmě nebudeš dávat INDEX na všechny sloupce.

150 dotazů/s mi nepřijde tak moc na HW, který napsal. Proto bych hledal nejdříve optimalizaci. Viděl jsem databáze, kde byly indexy použity úplně špatně (např. jeden index přes všechny sloupce a autor si myslel, že se mu pak všechny SELECTy s podímnkou na kterýkoliv sloupec budou provádět rychleji, ...). Neříkám, že je to tenhle případ, ale začal bych tam. Ještě pak to ORM. Než investovat tisíce do nového HW, tak raději 2 týdny ladit.

[Dix]

To mas pravdu, ale vetsinou v aplikaci mas kombinaci takovych dotazu. Ale je pravda, ze vzdycky jedna cast prevlada. To doopravdy zalezi na konkretni aplikaci, to bychom tady mohli polemizovat...

Ja osobne bych stejne ale nejdriv zacal ladit tu aplikaci a az potom RDBMS. Napr. promyslet, jestli na urcity ukony se nevyplati pouzivat urcity stupen cache apod.
Nerikam, ze musi hned kupovat novy HW, ale ze to byva vec na kterou se v podobnych uvahach zapomina. Treba to jak je velka DB taky ovlivni performance. Zda se ti vejde nebo nevejde do pameti (jak moc casto DB musi pristupovat na disk)...