SQL lekérdezés gyorsítás

Adatbázis: SQL, XML DB

Sziasztok!
Egy cégnél dolgozok, ahol több telephely van. Adott egy központi adatbázis, több mint 2 gigabyte, és ebből a módosult sorokat szinkronizálja a rendszer a különböző távoli telephelyekre. A távoli adatbázisok 6-700 mega körül vannak maximum.
A központi telephelyen vezetékkel és vezeték nélkül is csatlakoznak a hálózatra, viszont itt a 2 gigás adatbázisból dolgozunk.
A problémák a következőek:
-Egy-egy lekérdezés még helyi hálózaton is sok idő, átlagosan 3-4 perc, valamint a 160 ezer feletti sorból álló cikktörzs 7-8 perc.
-Nyilván a programozónak kellene javítani, hogy ne az egész sql táblát kérje le, de erre egyelőre nincs mód.
A kérdésem a következő:
-Hogyan lehetne felgyorsítani hardver szinten a folyamatot?
-A szerver vinyója egy 15k-s hp, esetleg egy ssd gyorsítana?
-Jobb hálózati infrastruktúra javítana? Ha igen, milyen?

Köszönet!

  • 6136 megtekintés

( meridian v | 2014. 11. 25., k – 09:53 )

Inkább a programot optimalizáljátok mint alkatrészre költsetek, mert gondolom az adatbázis csak bővül és folyamatosan lassul, s rövid és hosszú távon és kifizetődőbb egy optimálisabb működés mint a folyamatos hw beszerzés.

Persze, es az SSD-t ki csereli ki? :) (nem, nem a programozo...)
Es akkor meg nem is merultunk bele a vallalati kornyezetben torteno beszerzes egyeb bugyraiba, jovahagyasokkal, adminisztracioval, miegyebekkel. Gyakorlatilag a hardver maga a legolcsobb tenyezo az egesz folyamatban. Ennel meg egy programozo is kevesebb macera :P

"ha egy SSD-re nem futja,"

(Nagynevű multi - amelyet a számítástechnika atyjaként szokás emlegetni - egyik (valószínűleg még sok) leányánál idén 0 dolláros a HW büdzsé. Ebben benne van az amortizáció is. Ami rendkívüli ezen felül kellene, azért pallosjoggal és értetlenséggel felruházott jóváhagyók seregét kell térden állva szeretni.
Nem mellesleg a cég a kormány stratégiai partnere - szóval ez nem tőkekivonás, hanem valami új terminusz technikusz a statisztikák kedvéért. Mondjuk: a "lassú rohadás" meg is felelne.)

talan veletlen ugyanannal a multinal vagyunk? ;-)

ettol fuggetlenul _mindenutt_ keresztul lehet ezt vinni, nem kell a cegrol negativ kepet festeni. mar kifejtettem mashol: mindenki egyeni felelossege a sajat ceget jobba tenni.

(ha nem ugyanott dolgozunk, akkor az elso kijelento mondat persze alaptalan lehet, a masodik viszont all)

( pirate | 2014. 11. 25., k – 09:56 )

Elsosorban programozoi oldalrol, de ugye ez nem jatszik.

- tobb RAM
- SSD (vagy tobb SSD RAID0-ban?, termeszetesen backup-al!)
- mit jelent a jobb halozati infrastruktura? 100mbit-rol gigabitre? rosszabb biztos nem lesz, ha van ra penz rajta!
- mysql tuning

---
"Már nem csak tehetségekből, de a hülyékből is kifogytunk..."

azokat ki is kell irni, aztan ha kelloen sok query fut, ami lockol is, nem mindegy hogy mennyi ido alatt vesi ki a valtozasokat. Azert te is latod, hogy elegge el van kurva a szoftver ahhoz, hogy celszeru ssd-re migralni meg ezt az apro adatbazist is

// Happy debugging, suckers
#define true (rand() > 10)

( Szenti v | 2014. 11. 25., k – 09:58 )

Mindenekelőtt ki kellene mérni, hogy mi okozza a lassulást (pl.: rossz kverik; hálózati probléma; diszk I/O szűk keresztmetszet; memóriahasználat; stb.)! Amíg a pontos ok nem ismert, addig felesleges bármit is csinálni.

Ha szar a (szoftver) architektúra, nehogy hardverbővítéssel próbáljátok megoldani!

+1 Ilyen méreteknél gyakorlatilag nincs esélye van annak, hogy a hw kevés - a sw-t KELL megnézni, hogy mit csinál, mit nem csinál, bár a "módosult sorokat szinkronizálja a távoli telephelyekre" a rendszer felépítésével és kialakításával kapcsolatban nem sok jót ígér...

+1 a mérésnek, nem nagy meló (viszonyítva természetesen az átíráshoz)

de szerintem lehet átmenetileg erőből megoldani a dolgot, főleg ha nem para pénzügyileg, és van is aki megcsinálja, de persze közben kell javítani a kódot is

-
Változékony Grails alkalmazás konfiguráció facepalm

( joco01 v | 2014. 11. 25., k – 10:06 )

Az a gyanúm, hogy alábecsülted az algoritmikus komplexitás fontosságát. Egy jó programozó egy új hardver árából képes lehet algoritmikusan gyorsítani a lassú lekérdezéseken. Egy jó hardver csinálhat a 7 percből 3-mat, ami az adatbázis növekedésével úgyis visszalassul, viszont egy O(N^2)->O(N)->O(logN) algoritmikus gyorsítás 7 percből 7 másodpercet vagy 7 ezredmásodpercet (hasra ütött számok).

--

( jailhouse v | 2014. 11. 25., k – 10:43 )

indexek, indexek, indexek

----------------------------
Előnevelt csirke kapható!

Messze nem ilyen egyszerű. Ha a szerencsétlen programozó csak select * from táblát ismer és/vagy nincs where, vagy nem elég szelektiv a where és a programban szűri le az adatokat, akkor rakhatsz rá akárhány indexet, csak az insertet lassitod vele, mert table scan lesz belőle.

Feljebb már leirták a megoldást: meg kell MÉRNI, hogy mi a szűk keresztmetszet. Egy adott lekérdezés, egy adott form, egy adott keresés és ha ez megvan, akkor megnézni, hogy mit csinál a szerver. HDD-t teker, 100% cpu-t teker, swap-pel, hálókábelt maxolta ki a kliens felé stb-stb. Utána lehet megmondani, hogy lehet-e bármit javitani a helyzeten hardveres oldalról vagy csak pénzkidobás.

Én egyébként azt sem jelentem ki kategórikusan, hogy ne lehetne hardveresen hosszútávon gyógyitani béna programot. Volt már olyan, hogy olcsóbb volt memóriát bőviteni annyival, hogy az egész adatbázis beleférjen 2x, mint a szoftvert javitani. Ez 5 éve volt, ma is vigan és gyorsan működik az alkalmazás, mert nem nőtt annyival az adatbázis mérete évek alatt sem. De nyilván ehhez is meg kellett nézni, hogy hol a gond, ez esetben a datapage-ek hdd-ről felolvasása volt a gond.

( kallaics | 2014. 11. 25., k – 10:59 )

Én is azt mondom,hogy a progit kéne optimalizálni.

( zeller | 2014. 11. 25., k – 11:41 )

Ki kéne dobni a dBase+Clipper párost meg a 16MHz-es 286-os gépet... Komolyan. Ezek az idők arra az időszakra emlékeztetnek...

Tudok neked olyan kínai által kreált sql lekérdezést adni, ami egy 8 Core, 24GB RAM -os gépen 10 percig fut! Olyan inner join, al selectes lekérdezés, amit mondjuk 3 óra alatt, de átírtuk és 3 másodperc alatt kiadja ugyanazt az eredményt! Szóval a fejlesztőknek is legalább az SQL for Dummies könyvet el kéne olvasni mielőtt bármilyen SQL parancsot kiadnak!

Az elején az ember átlagos selecteket gyárt, mert nem fog left joinolni és egyéb manővereket végrehajtani. Aztán jön a kurva lassú select korszak, mert megtanulja az extra dolgok használatát, ezt követően pedig egy másik korszak, amikor már megtanulta őket helyesen használni:)

// Happy debugging, suckers
#define true (rand() > 10)

A világ legjobb optimalizálójával ellátott motoroknál is előfordul, hogy INDOKOLATLANUL és NAGYSÁGRENDEKKEL lelassul egy normális lekérdezés a minimális bővítése után. Bizisten olyat is láttam már, amikor a szelektivitás növelése adott egy faktort a lefutási időhöz: a bevont expertek konzíliuma csak alterálta az incredible és a shouldn't kifejezéseket.

( uid_17626 | 2014. 11. 25., k – 12:11 )

Szerintem reménytelen és költséges vállalkozás az elbaszott kódot pénzzel és hw-rel helyettesíteni.

Személyes tapasztalatom az, hogy ha egy db gépet egy db gépre cserélsz nem valószínű, hogy dupla sebességet is eltudsz érni még akkor sem, ha a két gép között akár 10 év korkülönbség van, persze feltéve, ha szar a lekérdezés. Egy-egy indexszel és jó sorrendbe rakott WHERE záradékkal szinte végtelen gyorsulás tapasztalható.

( xclusiv v | 2014. 11. 25., k – 18:11 )

A topic-kal kapcsolatban már mindent leírtak, de nekem ez ragadta meg a figyelmemet:

"-A szerver vinyója egy 15k-s hp, esetleg egy ssd gyorsítana?"

Egy vinyó nem vinyó, a "jövendő" rendszer alá célszerű lenne RAID-et tenni...

( denke v | 2014. 11. 25., k – 20:21 )

Szia!

Talan az lenne a legegyszerubb, (ha nem titkos) ha bemasolnad nekunk az erintett tablak strukturajat (show create table parancssal, ne explain!), es a vegrehajtott lekerdezest. Evvel az ezen a forumon levo szakik legalabb a trivialis hibakat ki fogjak neked szurni, pl indexeles hianya, felesleges feltetelek (pl. ""where `valami` like '%'""), roszul megfogalmazott lekerdezesek stb.

Ja es ne felejtsd el a mysql szerver cache konfigfilejat se bemasolni, lehet valami cache meret el van szurva.
Illetve nem lenne rosz maga a vas jelenlegi konfiguracioja, kernel es (mysqld / mariadb / percona / akarmi.) verzioszama.

A HW bovitest en sem javasolnam amig nincs tobb info, a kovetkezo miatt:
A lekerdezesed ugyis csak egyetlen szalon fog futni, tehat ha nem az okozza a lassulast, hogy egyszerre tobbsz lekerdezes fut, akkor egy erosebb (tobb magos) proc csak a penztarcatokat teszi majd konnyebbe.
Amennyiben van eleg ram a gepben, hogy a linux kernel file cache-be beleferjen az oszes tabla, ugy majdhogynem felesleges ssdre valtani, nem fog jelentos sebessegnovekedest jelenteni.

( STP | 2014. 11. 25., k – 20:19 )

SQL szerver típusa?
Szerver memória mérte?
Cache állapotok?
És a megállapítás. A programozó nem ismeri az SQL-t. Csak használja.

( gabrielakos v | 2014. 11. 26., sze – 07:59 )

Mennyi memória van az adatbázis szervernek adva?
2gbyte-os adatbázis az a vicc kategória egyébként, normál esetben ez simán befér a memóriába, tehát rakhatod alá a világ diszkjét, ssd-jét, akkor se lesz lényegesen gyorsabb (a második lekérdezéstől). De még ha 100x-osan gyorsítanál hw-ből, ez akkor is lassú lenne.

A leírtakból nekem teljesen az jön le, hogy irdatlanul el van cseszve az egész, főleg szoftveresen.
Adatbázist is kell tuningolni és a lekérdezéseket kell optimalizálni.

Legutóbb pár hete volt ilyen esetem, 30mp-es query-t gyorsítottunk fel 9 ms-ra.
Körülbelül 80 millió rekord van az adatbázis legnagyobb táblájában (ajánlatok).

--
Gábriel Ákos
http://ixenit.com

( Kayapo | 2014. 11. 26., sze – 13:21 )

Bár igen kevés az info...
Nyilván a lekérdezések optimalizálása a legfontosabb...
Nyilván a főnök(ök) azt gondolják, hogy arra kár időt áldozni mert jön az új verzió...

Azonban pár megjegyzés (megbántani senkit nem szeretnék, tehát tekintse minden olvasó csak tippelgetésnek):
Majdnem biztos, hogy a bajodat, bajotokat, a "full table scan" okozza, vagyis a lekérdezések annyira sok oszlopot és összevont táblát érintenek, hogy halmaz műveleteket nem igazán lehet rajtuk elvégezni. Az is valószínű, hogy a táblákban van duplikált adat és hol innen hol onnan kérdez le a kód, így az esetleges cache -elésre is keresztet vethetsz.

Amit én tennék és ezt az új kóddal is meg tenném még az éles üzembe állítás előtt, azokat a lekérdezéseket amik 2 másodpercnél hosszabb ideig futnak és/vagy 100 sornál hosszabb eredményt adnak, elemzésnek vetném alá:
- Keletkezik-e temp file?
- Miért keletkezik temp file? Kevés a memória, vagy fileban rendezést igényel a lekérdezés (full table scan)
- Kerül-e közvetlenül fileba egy lekérdezés eredménye

Na és akkor, hogy mit tudsz tenni:
- Ha kiderül, hogy kevés a memória egyértelmi mit tehetsz
- egyéb esetekben azt kell megtudni, hogy hova keletkeznek a cache file -ok és azokat (ha van elég memória) valamilyen RAMDISK szerű file rendszerre kell tenni.
- A hosszú ideig futó query -ket lehet erre kitalált programokkal elemeztetni és az eredmények alapján módosítani az sql kiszolgáló beállításait

Mindez az új alkalmazásnak is jót fog tenni.

----
올드보이
http://molnaristvan.eu/

( lx | 2014. 11. 26., sze – 19:22 )

Amellett, hogy az esetek sokkilences százalékában igaz az itt többször leírt gyorsdiagnózis, hogy ezen nem lehet db-oldalról segíteni, matematikai esély mégis van arra, hogy mégis. Mármint a tapasztalat szerint.

Hasonló méretű, akkoriban kb. 2G-s adatbázisra kötött alkalmazás kezdett bizonyos rekrodszám fölött drámaian lassulni. A kötelező kör a lehetséges indexek keresésével és létrehozásával kis enyhülést hozott, de még mindig nagyon nem volt az igazi. A bufferpool (a.k.a.: cache) növelésével a teljesítmény tovább romlott, ami már komolyabb analízisre ítélt.

A táblák részletes átvizsgálása közben kiderült, hogy amikor a szervező/fejlesztő/mittoménki szűknek találta az időt a tervezésre, vagy csak lusta volt kideríteni, hogy mekkora lehet bizonyos karakteres adatok maximális hossza, alapból odadobott egy-egy pármegásra deklarált CLOB mezőt; táblánként akár tucatnyit.

Azt vagy nem olvasta, vagy nem értette, hogy ezek az adatok nem kerülnek be a bufferpoolba - jó esetben is csak a rendszer fájlcaché-be (ez csökkent akkor, amikor a dbmotornak lefoglalt memóriát megnöveltem) - vagyis primitív adatokban matató primitív lekérdezések direkt diszkrángatásba torkollottak (ami persze látszott a metrikákból is, csak érthetetlen volt az ok).

A gondatlanul definiált mezők charra/varcharra átfaragása után lett ööm é bódóttá, meg lett egy mini beszt- és vörsztprektisz is, amit lehetőség szerint előadok az új fejlesztőknek a békés egymásmellettélés érdekében.