Jak zlepšit odezvu webového projektu s větší databází a návštěvností

Naprogramoval jsem jeden web o dětských táborech - http://www.taboreni.cz/tabor/ - kde je relativně málo obsahu. Bez kešování SQL dotazů se ale některé stránky (jako kategorie na homepage) nebo třeba výpis konkrétné kategorie generuje pekelně dlouho - 6-10 sekund není vzácností.

Web jede na solidním serveru, nejpomalejší SQL dotazy jsou většinou několikanásobné joiny s agregací apod. Kešování výstupů má za následek to, že některé části webu uživatel nevidí aktuální (třeba počty táborů v kategorii se aktualizují 1x za hodinu, inzerce 1x za 30 minut a pod.).

Teď otázka - rád bych vytvořil jiný webový projekt který předpokládá několikanásobně větší návštěvnost (očekávám min. 5 tisíc denně) a zhruba 3x takovou databázi co do struktury.

Ptám se tedy, jak to vlastně u větších projektů je "udělané", že fungují rychle. Bude se jednat o web podobný webům mobilmania.cz, bandzone.cz a pod. Nicméně řádově složitější databáze, řekněme že v některých "aspektech" by to mohlo být podobné malému facebooku.

Prosím o rady od lidí, co tuší jak na to. Aktuálně jsem ve stádiu návrhu a úvodních brainstormingů s kamarády...

ps: používám DB server Firebird 2.5, PHP 5, Apache 2

Mám web s výrazně větší návštěvností, technologie ASP.NET a MS SQL.
Jen v několika bodech na co se zaměřit:
- dobrý návrh datového modelu i aplikace.
- udělat chytré kešování - to co popisujete je k ničemu (rozmyslete si, kdy je třeba měnit obsah keše a jak velký obsah keše)
- optimalizovaná DB a dotazy do ní (indexy, relace atd.)
- bacha na agregace - lepší je někdy použít přepočtů a agregovaná data mít uložená v dalším sloupečku DB tabulky - někdy to jde a jindy ne to si musíte rozmyslet.
- z DB tahat jen data, která jsou skutečně třeba.
- rozmyslet si nasazení - měl by to zvládnout jeden server, čím je vytížený dále?
- analyzovat slabá místa a definovat nejčastější a nejnáročnější operace na čas a výkon.
- ergonometrie uživatelského přístupu.

Máte chybně navrženou databázi a/nebo napsané dotazy, pravděpodobně obojí.
Zapněte si na pár hodin logování pomalých dotazů (obdoba v MySQL je popsaná tu). Log vám řekne, které dotazy musíte přepsat.

Se správnou strukturou a dotazy nebude 5 tisíc denně určitě žádný problém. Návštěvnost je zatěžující jen pokud přichází požadavky naráz. Při pěti tisících lidech denně se většina z nich mine (myšleno v počítačovém čase, ne v lidském).

Souhlasím s panem Schlemmerem. Návštěvnost 5k denně není příliš vysoká, chyba bude možná ani ne ve struktuře tabulek jako takové, možná bych i volil klasiku MySQl server, dále pak byste se měl asi podívat na to, jestli máte správně indexovány tabulky - indexy volte v souladu s DB dotazy, tedy tvořte i indexy nad více sloupci. DB dotazy, zejména joinované, chytře formulujte tak, aby WHERE a ORDER BY respektovalo postupnou eliminaci, a pokud možno co největší filtraci výsledků už nad primární tabulkou. Dále pak doporučuji, pokud přejdete na MySQL, optimálně nastavit kešování tabulek. Pokud si nejste jistý, zda MySQL čte z temp nebo file a jaké indexy používá, testujte dotazy pomocí EXPLAIN.

Ten web vypadá dobře, ale rozhodně nevidím důvod, proč by se měl načítat tak dlouho (myslím při vypnuté cache). Značí mi to spíš programátorskou chybu, protože těch dat tam zase tolik není. Ze zkušenosti... PHP je opravdu hodně pomalé, můžu to srovnat s Javou. Nicméně se dá jeho výkon jednoduše kompenzovat přidáním dalšího železa a balance loaderu.
S databází už je to horší, tak se výkon zvedá mnohem hůře. Nejlevnější možností je, hodně si vyhrát s optimalizací dotazů, správně nastavit indexy, atd ... Další možnost je pořídit si více serverů a DB mezi nimi replikovat. Pro čtení se pak může využívat více strojů a zároveň je to záloha. Když nějaký stroj vypadne, DB jede dál.

Facebook má také zpoždění. Jinak by to snad ani při takovém množství dat nebylo možné. Tzn. že nový příspěvek není hned viditelný pro všechny uživatele FB.
FB je sice v PHP, ale mají vlastní kompiler ( HipHop ), který PHP převede do C++ a výsledek ještě běží na vlastním serveru - žádný Apache, je to mnohonásobně rychlejší.

Další možností, která nemusí být vhodná, je použít třeba NoSQL databáze. Když už jsme u toho FB, tak ten používá http://en.wikipedia.org/wiki/Apache_Cassandra . Tohle se ale hodí jen na omezené případy.

Přečtěte si následující článek:

http://antirez.com/post/take-advanta...our-stack.html

Redis nemusí vyřešit veškeré Vaše potíže bez toho, abyste web kompletně předělával, nicméně se s většinou Vašich potíží tak, jak jste je popsal, bez velkých zásahů do kodu jednoduše vypořádá.


Martin

redhat: Vím, že to myslíte dobře, ale vaše rady jsou mířené úplně někam jinam. Hobby web jako Taboreni.cz určitě nepotřebuje replikovat DB ani dělit zátěž pomocí balance loaderu. Už vůbec ne, když nadupané servery, které takových stránek utáhnou desítky až stovky, stojí pár šupů.

PHP rozhodně taky není úzké hrdlo. Jeho rychlost je srovnatelná s dalšími jazyky pro webdevelopment
http://shootout.alioth.debian.org/u3...re-fastest.php

Sám tazatel přiznává, že jsou pomalé SQL dotazy:

Bez kešování SQL dotazů se některé stránky [...] generuj[í] pekelně dlouho

Tam se taky musí dívat při zrychlování. Ne na víc serverů, přepsání do jiného jazyka nebo přestup na NoSQL.

Původně odeslal johnyzpodoli

... nejpomalejší SQL dotazy jsou většinou několikanásobné joiny ...

Zkontrolujte zda máte indexy na sloupcích kde mají být, naopak to s nima nepřehánět, selecty a joiny se indexama zrychlují ale inserty, updaty apod zpomalují.

S joinama bych to všeobecně nepřeháněl, stejně tak lpěním na dokonalém relačním přístupu. Jiste tu bude hodně zastáncu toho, že spojit 2 db tabulky, kde každá má 100 tisíc záznamů kvůli výběru 20ti položek je optimální, ja budu vždy tvrdit opak. S rostoucí velikostí databáze se učelnost joinů snižuje.

Nicméně hádám, že máte sdílený hosting, tam bych hledal hlavní důvody.

Původně odeslal himynameismartin

Redis nemusí vyřešit veškeré Vaše potíže bez toho, abyste web kompletně předělával, nicméně se s většinou Vašich potíží tak, jak jste je popsal, bez velkých zásahů do kodu jednoduše vypořádá.

Přidání další vrstvy složitosti neodstraní příčinu problémů - chybně napsané dotazy/navrženou strukturu.
Jinak díky za odkaz na článek.

Původně odeslal Martin Schlemmer

Hobby web jako Taboreni.cz absolutně nepotřebuje replikovat DB, ani dělit zátěž pomocí balance loaderu.stojí pár šupů.

asi jsem to blbě napsal, ale Táboření je již vyřešené, zde nepotřebuji nějaké velké zásahy. Dotaz směřuji k novému projektu který chystám a kde očekávám několikanásobně větší návštěvnost. A právě po špatných zkušenostech s rychlostí u táboření se informuji na možnosti jak podobným problémům předejít.

Vzdělávajte se, přečtěte si nějaké tutorialy o indexech a JOINech.
Přemýšlejte o struktuře databáze dopředu, i s ohledem na výkon.

Při psaní dotazů průběžně používejte EXPLAIN nebo obdobu
http://stackoverflow.com/questions/1...ther-databases

Až budeš mít tu databázi pro nový projekt navrženou tak určitě testuj SQL dotazy na větším množství dat (asi bude nutné napsat nějaký generátor). Pokud bys to testoval na pár záznamech, řada problémů se vůbec neprojeví a ukáže se až v produkci (trochu pozdě...).

Původně odeslal matejka

Až budeš mít tu databázi pro nový projekt navrženou tak určitě testuj SQL dotazy na větším množství dat (asi bude nutné napsat nějaký generátor). Pokud bys to testoval na pár záznamech, řada problémů se vůbec neprojeví a ukáže se až v produkci (trochu pozdě...).

děkuji za dobrý nápad ;-)

Původně odeslal matejka

Až budeš mít tu databázi pro nový projekt navrženou tak určitě testuj SQL dotazy na větším množství dat (asi bude nutné napsat nějaký generátor).

Tak toto poznam. Urobili sme tu istu chybu. Mali sme databazu, kde sme v teste mali len len necelu tisicku zaznamov v nejvecsej tabulke. Potom isla do prevadzky a tych zaznamov v tabulke uz bolo nejakych 800 000 a vykon isiel do kytek. Nastastie to poriesilo par vhodne zvolenych indexov.

redhat : Maz nejaky odkaz o tom load balancingu, replikaciu a vysokych zataziac co sa oplati precitat? V praci mame MySQL databazu ktora ma 60 GB dat a v najvetsej tabulke mame tak 20 az 30 milionov zaznamov. Zatial sme to riesili len RAM-kou ale mozno nas caka inaksia cesta.

Těžko soudit, když ty dotazy nevidíme, ale odhadl bych to na utopistický přístup v návrhu databáze a dotazů.

Za podobnými problémy obvykle stojí:

1) Žádné nebo nevhodné používání indexů
2) Spojování podle textových nebo jiných nevhodných polí
3) Neobratné používání časově náročných funkcí v dotazech, typicky např. regulárních výrazů v joinech
4) Zbytečná snaha udělat vše jedním dotazem - je rychlejší 200 dotazů co zaberou 0.002s než jeden co zabere 5s.
5) "Univerzální datová struktura", která obsahuje část strukturálních dat v datech - vypadají dobře na papíře, ale v reálu jsou nepoužitelné a hlavně se naprosto míjejí s tím, na co je databázový engine optimalizovaný.
6) Striktní dodržování pravidla "žádné redundance" v datech - redundance je sice skutečně nežádoucí, ale v reálu existují případy, kdy je zkrátka výhodnějí tohle pravidlo porušit.

Snad vám to pomůže najít problém.

Původně odeslal johnyzpodoli

Web jede na solidním serveru, nejpomalejší SQL dotazy jsou většinou několikanásobné joiny s agregací apod

Často je rychlejší udělat několik jednodušších SQL dotazů než jeden složitý který bude obsahovat "všechno".

Takže výsledek tohoto threadu je, že po doplnění 4 indexů do databáze se web rozjel až neskutečně.

statistika pro /tabor/
průměrná doba pro generování stránky

- 3,8 sekundy bez keše a indexů,
- 0,08 sekundy s kešíbez indexů
- 0,18 sekundy s INDEXY

je vidět, že data z SQL serveru jdou pořád trošku pomaleji než z keše, na druhou stranu kompletní vygenerování stránky za 0,2 sekundy už mi připadá velice dobré a není třeba se tím tedy dál trápit. Zdá se, že výrazně omezím kešování, nechám ho asi pouze tam, kde se ta data opravdu "nemění".

děkuji za podnětné nápady, aktuálně studuji knihu kde se vše dočítám

No vida, blahopřeju, že jste se do toho pustil.
Kešování zkuste nastavit přímo na úrovni databáze. Znám jen MySQL, tam lze cachovat v paměti celé tabulky, indexy i přímo výsledky dotazů.

Super, tož to aspoň k něčemu bylo :)

Jinak 2 oham:
ad 4) Zbytečná snaha udělat vše jedním dotazem - je rychlejší 200 dotazů co zaberou 0.002s než jeden co zabere 5s.

- toto je velmi diskutabilní, aby dotaz trval 5s, to už by musel být opravdu hodně špatně napsaný a databáze až nechutně velká - Uvedu konkrétní příklad s joiny přes 11 tabulek, použitím case (6x), několik matematických oprací, jedná se o sesumírování výpisu produktů, přičemž se sbírají kromě základních dat také data z jiných tabulek (dostupnosti, výrobci, skupiny marže, cenová pásma, užv skupiny, pevné/akční ceny) a v jednom dotazu se přímo vypočítávají pomocí různých kriterií pro produkty jednou ze 6 metod (v podmínkách je volba, která se použije), zkusmo jsem si teď v adminu vypsal do prohlížeče 10000 produktů, dotaz samotný trval 1.2s na VPS 1:8 u WEDOS (60 GB HDD, 2 GB RAM, 2 proc./Fedora 15 ), zbytek skriptu nulanula nic. Ten VPS uvádím jako objektivnější kriterium než lokální konfig u sebe na laptopu. Kdybych sesbírání detailních dat řešil namísto joiny v následném cyklu, jednal bych se obral o možnost filtrovat dle kriterií už před výběrem dat a jednak by to bylo výrazně pomalejší, neboť kdyby jednotlivý dotaz zabral 0.0001 sekundy a další zlomky sekund by připadly na cyklus jako takový, tak se dostaneme k mnohem vyššímu číslu než je jeden a půl sekundy. Teď to fakt nemyslím konfrontačně, jen jsem zkoušel opravdu několik přístupů, když jsem systém ladil a velmi složité dotazy mi přišly vždy rychlejší. Je to takto testováno až do 500000 produktů, přičemž jako produktová data byly použity zmnožené produkty z HPtronic importu. U článků bude situace jednodušší o to, že není třeba vyhodnocovat ceny a lze se proto vyhnout matematickým operacím a dost pravděpodobně i CASE a IF. Nechci tvrdit, že to tak bude vždy, ale moje zkušenost je prostě taková a domnívám se, že větší počet jednoduchých dotazů na DB obecně není lepším řešením. Tedy možná je, ale snad pouze v případech, kdy web běží na opravdu slabém hostingu s velmi omezenými zdroji, kdy je nutno k tomuto přistoupit prostě proto, aby se nesesypal server kvůli přečerpání paměti.

Toto mi přišlo na první pohled jako zajímavá brožurka k tématu, zařadil jsem jí asi na 387. pozici knížek, který bych rád alespoň prolistoval :) http://use-the-index-luke.com/