Autor Zpráva
iFery
Profil
Zdravím,

rozjel jsem si svůj vlastní projekt, kde mám aktuálně v databázi přes 3 500 000 záznamů (cca 300MB) a dotazy, které na webu používám nejčastěji, trvají okolo 2,5s, ty složitější i přes 5s. Nad sloupci, kde to dávalo smysl, mám indexy. Dotazy se většinou skládají z několika join + sum/count, občas union.

A řeším teď, co s tím dál? Přes databáze nejsem žádný guru, takže dál už nevím jak databázi optimalizovat. Má smysl dál ještě dotazy optimalizovat - jestli je tu možnost, to rapidně zlepšit? Případně to raději ubouchat železem? Je to neziskový projekt, takže se mi do něj aktuálně nechce tolik investovat, pokud to nebude nezbytně nutné.

Jinak web běží na standardním hostingu, kde používám mariadb.

Díky za rady!
Kajman
Profil
iFery:
Má smysl dál ještě dotazy optimalizovat - jestli je tu možnost, to rapidně zlepšit?

Na explain koukáte? Používáte indexy nad více sloupci, pokud to dotazu pomůže?

Zkuste sem poslat typický dotaz, jeho explain a strukturu použitých tabulek včetně seznamů indexů, třeba jste ještě nějakou možnost přehlédl. Ale obvávám se, že pokud tam jsou sum, count, tak ta moc velký prostor nebude - leda mít data předpočítaná.
iFery
Profil
Kajman:
Na explain moc nekoukám, vzhledem k tomu že z toho nejsem moc moudrej, takže to budu muset ještě více nastudovat, abych z toho věděl víc. Indexy mám většinou pouze na jednim sloupcem.

Tady je dotaz, je dost možný, že řeknete že je skládám zbytečně moc složitě a špatně.
pastebin.com/HAZMEmqM

Explain + struktura (nevím moc, jak jinak to případně vyexportovat):
imgur.com/a/yAwca


Uvidíte, zda ty dotazy neskládám špatně a jestli nakonec nebude lepší v mém případě to ubouchat železem, pokud to má smysl vůbec.

Díky!
TomášK.
Profil *
Zkusil jsem vytvořit tabulku se 3 miliony záznamů, slopci id (primární klíč) a náhodně číslo 1 - 32678.

select sum(val) from t; 1.73 sec
select count(*) from t; 2.81 sec
select count(*), sum(val) from t group by val; 2.03 sec

select count(*), sum(t.val) from t join t t2 on t.id = t2.val where t.id < 1000; 0.12 sec
select count(*), sum(val) from t where val = 30000; 0.00 sec

Pokud ty dotazy pracují s rozumným počtem záznamů, můžou být pořád rychlé. Záleží na konkrétních dotazech. Server by měl mít dost paměti, aby nemusel číst z disku, jinak si myslím, že to moc ubít železem nepůjde.

A tipuju, že tam půjde něco, co jde cachovat / předpočítat, což je zřejmě nejjednodušší cesta, jak to vyřešit.


iFery
Na explain moc nekoukám, vzhledem k tomu že z toho nejsem moc moudrej, takže to budu muset ještě více nastudovat, abych z toho věděl víc.

Nastuduj, pro optimalizaci dotazů je to potřeba, aspoň nějaké základy. Na první pohled tam třeba vidím, že se tam dělá řazení dočasné tabulky s 1 517 885 záznamy bez použití indexu. Zdá se mi, že by ten dotaz mohl jít napsat tak, aby běžel třeba 200 ms.
Kajman
Profil
V tabulce zapasy bych přidal index nad dvěma sloupci (soutez_id, id) - ten by mohl nahradit současný jednosloupcový index soutez_id. A index id je tam stejný jako index primary, takže nejspíše stačí nechat jen primary.

V tabulce statistiky bych přidal index nad třemi sloupci (zapas_id, hrac_id, typ_id).

Vzhledem k limitu by mohl být výpočet odehraných utkání/minut poddotazem v select části - ale je potřeba ověřit, zda nedojde naopak ke zpomalení. Případně přesunout limit s order by dovnitř h2.

Uvnitř h2 by mohlo být ve where části omezení s.typ_id in (1,3,4,5) ale možná to naopak zpomalí.

Místo where h1.pocet_minut is not null je možné dát podmínku do having v h1 a změnit mezi hráči a h1 left join na join.

Dotaz může havarovat na některých konfiguracích díky ss.tym_id v h1.

A největší zrychlení by bylo při předpočítané tabulce, kde bude mít každý hráč pro každou sezónu a tým sečtené minuty, góly atp. Tato tabulka se může aktualizovat automaticky pomocí triggerů nad tabulkami statistiky a statistiky_stridani. Nebo ji lze aktualizovat po každém zadaném zápase do dané sezóny.

Edit:
V tabulce statistiky_stridani by se hodil třísloupcový index (zapas_id, hrac_id, tym_id).
iFery
Profil
TomášK.:
Díky moc, zkusím prostudovat.

Můžu případně poprosit, jak můžu přidat index nad dočasnou tabulkou? Díky!

Kajman:
Přidal jsem třísloupcový index do tabulky statistiky_stridani, to zatím vypadá, že pomohlo nejvíc. Zkusím ještě ostatní doporučení, aktuálně mi dává asi největší smysl předpočítaná tabulka. Díky moc, pokud by tě ještě cokoliv napadlo, budu rád za každou radu.
TomášK
Profil
iFery
Nad dočasnou tabulkou (ve smyslu v jakém je v explain) index vytvořit nejde. Co jde zlepšit je vytvořit index (tuším, že to je ten třísloupcový), který bude ve pořadí, které chceš z tabulky získat. Pak nebude potřeba vytvářet dočasnou tabulku a řadit ji, podle toho indexu půjde vytvořit už seřazená.

Vaše odpověď

Mohlo by se hodit


Prosím používejte diakritiku a interpunkci.

Ochrana proti spamu. Napište prosím číslo dvě-sta čtyřicet-sedm: