Autor Zpráva
janbarasek
Profil
Ahoj,
ve svém vyhledávači řeším problém, který jsem myslel, že snad nikdy řešit nebudu. V databázi mám tabulku "vyhledavani", kde si eviduji veškeré položené dotazy (a u každého jejich četnost, čas posledního hledání, IP adresu, uživatele - pokud je přihlášen, ...). Nicméně nastává problém v tom, že tato databáze je opravdu rozsáhlá a prakticky při každém hledání nového dotazu se do ní ukládá záznam (a při hledání stejného dotazu se updatne počet hledání).

Celý problém je v tom, že přístup do této tabulky zabírá téměř 300ms, zatímco kompletní přijmutí dotazu, zpracování, vyhledání a výdej se stihne pod 100ms, takže tato tabulka dosti zdržuje.

Stojím před těžkým rozhodnutím, co s tím udělat. Proto bych ocenil vaše praktické rady, mě napadlo toto:

1. Nějakým SQL příkazem smazat všechny záznamy delší než 150 znaků, které byly hledány pouze jednou. V databázi jsem si našel, že téměř 38% objemu hledání dělá IP adresa 66.249.67.248, která patří Googlu. Vážně, mrkněte se, co všechno chudák indexuje (300 tisíc záznamů, většina z toho je k ničemu). Můžete mi prosím poradit, jaký SQL příkaz by takto snadno smazal Googlem napáchané škody a trochu pročistil DB, abych tam měl jen uživatelské dotazy?
2. Vymyslet způsob, jak poznat relevantní dotaz k uložení, který by se mohl hodit. Jde o to, že na základě předchozích dotazů zpětně vyhodnocuji, kde jsou potenciální díry a co doplnit. Napadlo mě ukládat jen dotazy kratší 150 znaků (protože delší se opakovaně tak často pokládat nebudou).

Napadlo mě relevantní dotazy poznat podle:
- IP adresy (udělat black list adres, u kterých se dotaz ukládat nebude)
- Podle zapnutého JavaScriptu (uložit dotaz až zpětně Ajaxem), neuloží to ale u některých uživatelů (otázkou je, jak moc to vadí)
- Podle lokality (asi podle GeoIP API), ale asi by to utlouklo nějaký API server
3. Mám problém s postupně rostoucí velikostí. Začal jsem jako malá webová služba, teď ale mám narvané databáze. Nechal jsem si vypsat originální dotazy za poslední měsíc a MySql hlásí toto:
Zobrazeny záznamy 248400 - 248499 ( 295 142 celkem, Dotaz trval 0.3190 sekund)
Potřebuji nějakou radu, jak pořešit výkon, co se týče databáze. Jak by jste s dotazy uživatelů pracovali vy? Já považuji za důležitě, je uchovávat, abych mohl například kontrolovat trendy nebo podle toho dělat analýzu, co zrovna programovat.

Děkuji za jakoukoli radu.
mimochodec
Profil
1. "smazat všechny záznamy" ... "IP adresa 66.249.67.248, která patří Googlu" - kde je problém?


No a když píšeš o sledování trendů, tak je sleduj a když tam najdeš cokoliv, co nemá smysl, ošetři si, abys další takový pokus do databáze neuložil.
abc
Profil
1. Nějakým SQL příkazem smazat všechny záznamy delší než 150 znaků, které byly hledány pouze jednou.
Kde je problém? WHERE LENGTH (hledani) > 150 AND hledano = 1

2. Black list do DB a kontrolovat v insertu

3. Jak vypadají dotazy? Používáš nějaké ORM? Explainy dotazů jsou ok?
Kubo2
Profil
janbarasek:
Vážně, mrkněte se, co všechno chudák indexuje (300 tisíc záznamů, většina z toho je k ničemu).
To je až taký problém založiť súbor mathematicator.cz/robots.txt a vložiť do neho nasledujúce dva riadky:

User-Agent: *
Disallow: /search.php
Tori
Profil
janbarasek:
V databázi mám tabulku "vyhledavani", kde si eviduji veškeré položené dotazy [...] problém je v tom, že přístup do této tabulky zabírá téměř 300ms
Udělejte si tabulku třeba vyhledavani_archiv se stejnou strukturou jako vyhledavani, ale navíc unikátní index nad sloupcem s SQL dotazy. Každý den v době nejnižší návštěvnosti (předpokládám okolo 03-04h ráno) do ní přelijete data z vyhledavani, s využitím ON DUPLICATE KEY UPDATE zvýšíte počitadlo u stejných dotazů (k tomu potřebujete ten unik.klíč). Potom můžete z vyhledavani smazat všechny zkopírované záznamy. (Hranici, co se bude kopírovat a mazat si určíte třeba podle ID.) V tabulce vyhledavani pak můžete zrušit indexy (které při této velikosti už asi zpomalují), a ukládat vždy jen nový záznam (bez updatu počtu, to se vyřeší až při přesypání do archivní tabulky).

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: