MySQL Optimalizálás

Adatbázis: SQL, XML DB

Sziasztok!
Mindenek előtt szeretném leszögezni, hogy nem igazán értek a MySQL beállításokhoz ezért előre is elnézést szeretnék kérni, ha sok mindenben pontatlan leszek.
A problémám az lenne, hogy a MySQL szerver iszonyatosan sok CPU-t zabál, de mindezek mellett semmi ramot. A célom az lenne, hogy gyorsítsam a kiszolgálást és normalizáljam a CPU terhelést.
A jelenlegi felállás:
VPS 1 (Web szerver):
-4CPU 4*3,5GHz
-4GB DDR3 1600MHz RAM
-30GB HDD 7200rpm RAID1-ben
VPS 2 (Adatbázis szerver):
-4CPU 4*3,5GHz
-4GB DDR3 1600MHz RAM
-30GB HDD 7200rpm RAID1-ben
A két gép között a kommunikáció belső hálózaton megy 1Gb/s-es kapcsolattal. A web szerver nginx+php5-fpm kombinációja.
Jelenleg az SQL fő motorja MyISAM és a táblák motorja is. Két adatbázisban vannak eltárolva az adatok, a táblák száma és a struktúra megegyezik. 6-6 tábla van, az egyik adatbázis ~22MB-ot foglal és ~169.000 sor van benne a másik adatbázis ~11MB-ot foglal és ~76.000 sort tartalmaz (phpmyadmin szerint)
MySQLTuner kimenet: http://pastebin.com/gCJ2bWke (sajnos az uptime kevés, mert próbálgatom a beállításokat jelenleg is)
MySQL konfiguráció: http://pastebin.com/CLVgKfJQ
A CPU terhelés az adatbázis szerveren csak a MySQL által 90-100%
A ram nem akadály, ha valamiért többre lenne szükség, hogy jobban fusson a rendszer akkor teszünk még bele.

  • 10546 megtekintés

( sarkanyolo v | 2014. 04. 13., v – 12:00 )

Mi a jellemző felhasználás phpmyadmin szerint?
Selectek vagy írás is jellemző?
A táblákon van primary key?

Nem csak az elsődleges kulcsokat nézném meg, de a where egyéb feltételeiben használt oszlopokra definiálható ill. order by/group by támogatására létrehozható indexeket is.

Ui. ha jól értem, a 114ezer lekérdezésnek durván az 1/20-a külön sortot igényelt (ahelyett, hogy egy létező indexre akaszkodott volna), és még annak is egy kis, de nem elhanyagolható része temp táblákba folyt ki.

[OK] Slow queries: 0% (0/114K)
...
[OK] Sorts requiring temporary tables: 1% (105 temp sorts / 6K sorts)

Továbbá úgy néz ki, hogy a táblák tele vannak lyukakkal ill. szuboptimális sorrendű rekordokkal, ami szemetet tartalmazó adat-/indexlapokat okoz -- ráférne a db-re egy vákuum/kompressz/reorg/ahogyeztamysqlhívja.

[!!] Total fragmented tables: 20

(Hályogkovács vagyok a partvonalon kívül.)

"Azokat az oszlopokat amire keresek indexeltem már."

Tkp. az elején kellett volna mondanom, amit most a végén (még mindig viszonylag vakon): amikor csak megoldható, egyoszlopos indexek helyett kompozit indexet (is) érdemes gyártani, ami pl. egyszerre támogatja a where szűrését és a group by/order by csoportosítását/rendezését.

Ezt most annál is inkább mondom, mert most látom, hogy óránként ~4M q szabadul az egyébként szolid db-idre, és ha a tegnap mutatott trend nem módosult nagyot, ebből 200k sortol, amelyből 12k temp táblába szalad.

Az adatbázis összes hókuszpókuszából a sort egy tipikusan CPU-evő stáció.

( blackluck v | 2014. 04. 13., v – 12:07 )

Meg kene nezni pontosabban mit takar a magas cpu terheltseg, ilyenkor milyen query-k futnak, netan nem-e csak egymasra varnak vagy diszkre.

De igy elso ranezesre:
query_cache_limit = 16M
query_cache_size = 32M
Vedd lejjebb a limitet (1-2M altalaban eleg), size-t viszont emeld fel hogy tobb query elferjen benne, de attol fugg milyen query-k futtnak tobbsegeben, lehet erdemesebb lenne demand-ra allitani query cache-t es csak azoknal a query-knel megadni a hasznalatat ahol szukseg van ra (elnezve az irasok aranyat konnyen lehet hogy gyakran kiuriti es feleslegesen kerul be akkor).

buffer size-okat lehet erdemes alligatni meg mysql szerver oldalon, peldaul, de utana ellenorizd pl mysqltuner-el hogy ne legyen tul sok ( Maximum possible memory usage: 985.5M (24% of installed RAM)):
sort_buffer_size = 2M
join_buffer_size = 4M
read_buffer_size = 4M
read_rnd_buffer_size = 4M

Elnezve hogy mennyi lock nem azonnali (Table locks acquired immediately: 67%), jo esellyel azert mert mas lock-olja kozben ami myisam eseten tabla lock-ot jelent, innodb tud soronkenti lock-ot is, de ez erosen fugg a query-ktol.

Egyebkent pedig a legfontosabb, ami az esetek 90-95%-aban meg szokta oldani a gondokat, hogy adatbazis strukturat es query-ket optimalizald.

Nekem is úgy tűnik, hogy ez inkább query probléma.

A query_cache rögtön lehet 128MB. Ha csak ilyen kevés az innodb adat, akkor 64MB-ra vissza lehet venni lazán a buffer pool size-t.

Ha belépsz a mysql konzolra, akkor "SHOW FULL PROCESSLIST" -el látni fogod, hogy milyen lekérdezés pörög. A SELECT.... elé EXPLAIN -t írva el fogja mondani, hogy mi a query planner álláspontja, azaz elég jól kiolvasható, hogy mennyire használ indexet vagy esetleg temp táblával áll neki kűzdeni.

Megnéztem a logokat leginkább az látszik, hogy "Waiting for table level lock" szóval várnak. :/ Ezt a tuner is mutatta csak nem tudom sehogy sem redukálni.

Az ajánlott beállításokat kipróbálom.

A tábla struktúra már többször át lett nézve és optimalizálva. A query-ket többen írták, elég sok van belőlük de át fogom futni amennyire csak tehetem.

( atomjani | 2014. 04. 13., v – 17:04 )

Ami esetleg segíthet:
1. Ahol select * from van, ott érdemes elgondolkozni, hogy tényleg minden adat kell e a táblából.
2. Ahol lehet, számokat kell használni. A where részben a like használata lassabb, mint a logikai műveletek, operátorok használata.

Lehet a rossz tervezéss következménye is.
A lekérdezések is lehetnek túl bonyolultak, egyes adatok lekérése felesleges vagy azükségtelenül ismételt.

Vagy valami nagyon nem jól lett csinálva, el lett cseszve valahol, vagy ez a webes alkalmazásnak szüksége van ilyen komoly mysql használatra.

Segíthet az, hogy amelyik táblából gyakori az adatok lekérése, de a tartalma alig változik, indexeled. Különböző cache megoldások is segíthetnek.
......................
Egymás segítésére még: http://pc-kozosseg.com

Mivel nem érkezett konkrét probléma, ezért magamnak válaszolok, hogy miért nincs értelme az állításnak, még ha technikailag igaz is a kiragadott részlet:

1) Triviálisan: almát körtével. Véges számú, kizárólag logikai művelettel és operátorral (jelentsen ez bármit is, AFAIK az operátorral egy műveletet jelölünk, de mindegy) nem lehet mintát illeszteni stringekre. A like pedig arra való.
2) A teljes "használjunk minden helyett számot" elv... igen, a számok összehasonlítása gyorsabb lesz, mint szövegrészletek (karakterkészlet egyeztetéssel stb.). DE! Nézzük az eseteket:
*) azért használjunk, mert kevés és ritkán változó elem van (pl. megyék, országok stb.). Két (összesen három, a harmadikat lásd később) eset lehet:
a) használjuk a DBMS által adott eszközt (ENUM), ami elvégzi helyettünk a string -> int leképezést, cserébe az elemek módosulása séma-módosítással is jár és elég költséges lehet.
b) simán számokat tárolunk a DBMS-ben és az alkalmazás ad jelentést a számoknak. Ekkor pl. minden egyes adatbázis back-up mellé a futó alkalmazásról is kell egy mentést csinálnunk, egyszerűen azért, mert anélkül van egy nagy adag zajunk.
*) a másik eset, hogy "megszámlálható" (meg tudunk adni egy string -> int leképezést, még ha az egy keresőtáblát jelent is), de gyakran változó értékkeszletről van szó. Erre megoldás ugye kidobálni őket egy (azon, string) táblába, és az azonosítót tárolni, viszont utána a join-nál bukó a dolog (megfelelően indexelve amit nyersz a réven...).
3) A LIKE használata mindenhol VALÓSZÍNŰLEG lassabb lesz, mint bármilyen tisztán logikai kiértékelés - például HAVING clause-ban, lekérdezett kifejezésben etc. Merthogy számításigényes feladat. DE! Ha a LIKE-al keresett mezőn van index és 'foo%' alakú kifejezést keresel [vagyis tudja használni az indexet], az akár még gyorsabb is lehet, mint egy indexeletlen (-> full table scan) logikai kifejezésektől függő kiértékelés minden sorra (két elemből álló értékkészletű oszlopra indexet tenni meg...).
...

BlackY
--
"en is amikor bejovok dolgozni, nem egy pc-t [..] kapcsolok be, hanem a mainframe-et..." (sj)

Nem volt arról szó, hogy csak abból áll, de egy elfelejtett index vagy index pár (join-nál) igen vidám dolgokra képes. :) Az ilyen folyamatos CPU tekerések legtöbb esetben ilyesmiből jönnek:
- marha nagy result setet kér le a delikvens (lemarad a limit, a where és bónuszként még php-ból rendezi is és csodálkozik, hogy lassú)
- semmilyen kulcs nincs definiálva a táblán még elsődleges sem és 34873483743847343487db rekordon kell végignyálazni
- készül egy "remekbeszabott" relatív egyszerű join-os query, ami szintén rohadt nagy eredménytáblát hoz és még tmp táblával is bohóckodik az sql szerver
- hiányzik az index itt vagy ott... (pl. termék tulajdonságok táblánál, ahol egy termékhez több tulajdonság jöhet és remekül indexelhető)

"- marha nagy result setet kér le a delikvens (lemarad a limit, a where és bónuszként még php-ból rendezi is és csodálkozik, hogy lassú)"

Na az azért nem túl CPU igényes művelet. Az csak szarlapátolás, a rendezés, szűrés több CPU-t tud igényelni.

----------------
Lvl86 Troll, "hobbifejlesztő" - Think Wishfully™

Jó, az tény, hogy nálunk is a CPU idő 90%-a a PostgreSQL-re esik a két Pg-s rendszerünknél. Igaz, nálunk az egyik 10Gb, a másik 30Gb, van benne vagy 250 tábla és ennek megfelelő bonyolultságú queryk. Mondjuk a mérethez azért hozzátartozik, hogy indexek nélkül azért kevesebb lenne, de muszáj, mert sokféle szűrés van és van pár n*1M, és 1-2 n*10M rekordos tábla is.

----------------
Lvl86 Troll, "hobbifejlesztő" - Think Wishfully™

Azon töröm a fejem, hogy az a 11 és 22MB a memória foglalás vagy a fizikai mérete az adatbázisnak?
Utóbbi esetben az egész a memóriában lehet, akkor meg nem nagyon van diszkre várakozás, akár normális is lehet a kitömött CPU, nem? (Oracle-től láttam ilyet, MySQL elég távol áll tőlem)

Nem, de tapasztalatom szerint 10k rekord felett kell az index. Még, ha akár csak egy id = 123 is van a where-ben. Ez a minden rosszul van megtervezve és a "régen rossz" az index dolog ez így elég bullshit.

És 10k rekord az *nagyon* nem mennyiség. (De gondolom most jön majd az, hogy az az adatbázis, ami 60G, az biztos el van cseszve ésatöbbi ésatöbbi ésatöbbi.)

----------------
Lvl86 Troll, "hobbifejlesztő" - Think Wishfully™

Ha valaki egy ilyet el mer ejteni bárhol, főleg egy "MySQL Optimalizálás" topicban, hogy az indexek használata már réges régen rossz, az (megpróbáltam kultúráltan) bazdmeg takarodjon vissza tanulni. Ne bosszantsd már a felesleges hülyeségeiddel a népeket, főleg ne adj tanácsot ha ilyen istenverte hülye vagy.

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

"Az már régen rossz, ha egy táblát indexelni kell."

Ezt nem lehet jóhiszeműen értelmezni.
Ilyet állítani bármilyen jól tervezett adatbázis, bármilyen frankó tárolóira szétkent IO-jú táblájáról lehet, de célszrű krumpliorral, félméteres cipővel, pepita ruciban, mert akkor esetleg csak kuncogok rajta, és nem a hajtúimet kezdem dobálni, sikítófrász közben.

Tkp. aszondja, hogy régen rossz, ha egy táblában adat van.

Még akkor sem. Hol olcsóbb végignyálazni egy csomó rekordot, mint kikeresni indexből és az alapján megnézni a rekordokat? Ezzel az érvvel az sql index nélkül nem sokkal több, mint egy /dev/shm-be rakott csv file.

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

Sőt, 5.1-nél volt egy ilyen is, hogy egy táblán egyszerre csak egy indexet tud használni. Mellesleg, ha az index statisztikája nem éppen up-to-date, akkor lehet, hogy a rosszabb szelektivitású indexet fogja használni.

-----------
"Pontban 0:00-kor nem nagyon szoktak véletlen dolgok történni"

Az az adatbáziskezelő, amelyik friss statisztikák alapján nem tudja eldönteni, hogy mit csináljon, monnyonle!

Az az adatbáziskezelő-futtató, aki nem tud magának friss statisztikákat tartani, szintén monnyon le!

És igen, ebben, és csak ebben az esetben már tényleg felesleges indexekről beszélni, sőt generalice inkább a négyzetrácsos füzetet ajánlanám, mint kevesebb baj forrását.

Ez igaz labor körülmények között, de amíg nincs failover node és nem teheted meg, hogy rebuildeled az indexeket (online indexelés hiányában), addig force index/force key a barátod és senki sem fog lemondani :(

-----------
"Pontban 0:00-kor nem nagyon szoktak véletlen dolgok történni"

Nem es igen. Alapbol nem optimalizal, kulonbozo cacheket tart fennt azoknak a dolgoknak, amikkel egyszer mar talalkozott. Ezert lesz az, hogy az elso query baromi lassu, aztan gyors lesz. Nyomsz bele egy updat/insert/delete, ami invalidalja a cachet es megint baromi lassu lesz. Az index update nem olyan lassu, ha jol valasztod meg a tipusat (hashmap-be illeszteni baromi gyors es kivenni is, btree-be is, viszont mig az elobbinel utkozes, a masiknal meg a balance borulas okozhat problemat). Ismerve az ertekeket azert egesz jol be lehet allitani, az meg hogy az index felesleges, az egy alapveto bullshit

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

Szerintem amire te gondolsz az a query cache, hogy a mar lefuttatott query-ket hash-elve cache-eli (felteve hogy beture ugyanaz a query), ahol valoban ha erintett tablan valtozas van akkor torlodik a cache-bol.
De egyebkent van query execution plan (optimizer) ami vizsgalja tablamereteket, lehetseges indexeket, azok kardinalitasat, query-ben levo keresesi felteteleket es amit meg jol esik neki ami alapjan eldonti hogy hajtsa vegre a query-t, hasznalja-e lehetseges indexeket. Viszont ezeket a plan-eket nem cache-eli.

Amugy igaz, hogy megfelelo indexekkel sokat lehet nyerni es hulyeseg, hogy nincs rajuk szukseg csak tudni kell optimalizalni.

Oké. Megvárom, hogy az indexeléssel hogyan oldjátok meg a problémát. Mert máig nem sikerült megoldani.
Ha jól emlékszem, valaki azt mondta, hogy ha rossz táblát indexelünk, az is tud problémát okozni.
Okoskodni mindenki tud, nemcsak én. Emelem a kalapom az előtt, aki szakmailag nagyon profin vágja a dolgokat és tud jó tanácsokat adni a probléma kiderítésében. Én a jelenlegi tudásom alapján igyekszek hasznos segítséget adni, miközben tudom, hogy sokat kell még tanulnom. Sose állítottam, hogy profi vagyok és tudomásom szerint ez az oldal és a fórum nemcsak a profiknak szól.
Ha netán valami hülyeséget mondok, azért elnézést kérek, velem is néha előfordul ez. Azt viszont senkitől sem kértem, hogy megkergüljön. Ha valaki hülyeséget mond, akkor max kijavítom vagy elmondom, hogy nem jó úton áll a meglátásom szerint. Ez nem olyan nagy dolog részemről.

......................
Egymás segítésére még: http://pc-kozosseg.com

Ne fogd vissza magad, hanem mielott isteni kinyilatkoztatasokra ragadod magad legalabb olvass utana a temanak objektiv forrasokbol. Ne "facebookon osztottak meg", "ismerosnel lattam/hallottam", hanem elso kezbol, sajat magad altal kiprobalva, a tapasztalatokat a dokumentaciora visszavezetve mondj objektiv dolgokat. Mert egy profi igy dolgozik.
-- 

Ki oda vagyik, hol szall a galamb, elszalasztja a kincset itt alant.

Első lépésnek elgondolkozhatnál azon, hogy talán nem véletlen ennyi negatív visszajelzés és, hogy talán nem kellene ekkora arcal hangoztatni a védhetetlent.

Aztán jó lenne utánanézni, hogy mire való az index (keresés mefgyorsítása) és, hogy hogyan működnek (bármely egyetem random prog alapokkal foglalkozó tárgya, bináris fa, B-fa, hashmap és társainál keresgéld). Igen, tipikusan ezek azok a dolgok, amire flegmán azt szokták mondani, hogy úgy sem kell sehova.

Node optimalizálás: első lépésnek elfelejted az ilyen blődségeket, hogy használj mindenhova számot. Legrosszabb, amit tehetsz egy RDBMS-sel, ha megpróbálsz okosabb lenni a tervezőiknél. Második lépés, hogy nagyon alaposan elolvasod az eldugott részeit is a dokumentációnak. Minden RDBMS más, más jellemgörbékkel.

Utána nem a kódot kezded el megolvasni, egyrészt mert lehet, hogy nincs is meg nálad, másrészt félre fog vezetni. Első lépésnek mérni kezdesz el, naplózni a slow queryket ÉS megnézed, hogy miből van sok. Azért a működés alapján, mert kód alapján messze nem biztos, hogy a valós szűk keresztmetszeteket fogod megkapni. Aztán elemzed, hogy mit csinál egy query, erre ott az EXPLAIN, normálisabb eszközök grafikusan is ábrázolják neked, hogy mi mit csinál. És ez alapján elkezdesz indexeket fel/lepakolni. Ha nincs látványosan elbaszva a query, akkor azt kezded el később átgyúrni. És sosem megérzésre, hanem mindig mérés alapján.

Általában itt is igaz az az alapelv, hogy a fejlesztő jobban tudja, hogy miért azt akarta, mint te, szóval messze lehet, hogy az elsőre eléggé unoptimálisnak tűnő megoldás a legjobb. Hja és itt se kezdj el okosabb lenni, mint az RDBMS: ahogy a fordítókat, ezeknek az optimalizátorát is a tipikus "kódfordulatokra" próbálják felkészíteni. Ha elkezded keresztbe túrni a lelkivilágát sokszor csak inkább rontasz a helyzeten.

Hogyan kell használni az indexeket? Ha kicsi a tábla (általában <1000 rekord), nem érdemes, mert nem biztos, hogy használni fogja, mivel több az indexek beolvasása, mint végignyálazni a táblát.) Ha van is már benne valami adat (10k rekordnál már szinte biztos) akkor általában erdemes azokra a mezőkre rakni, amelyek alapján rendezel vagy szűrsz. (Okosabb db-k esetén akár kifejezésre is, pl. LOWER(mező). Egyébként nem véletlen van unique index olyan mezőkre, amelyek egyedi értékeket tartalmaznak.

Persze, ha az indexelt mező folyamatosan frissítve van az persze szívás. Ilyenkor mérni kell és mérlegelni, hogy mi a több: az olvasás vagy az írás/frissítés. (Régen egyébként gáz volt az insert is pl. MySQL-nél, ma nem tudom mi a helyzet.)

És, hogy honnan tudod, hogy hova kell indexet rakni? Egyrészt bizonyos dolgokra tervezéskor következtethetsz. Többi meg mérés, mérés mérés és még több mérés. Elvégre is mérnökszakma ez, nem hittérítés.

Hja és nincsenek örök igazságok, attól, hogy a legtöbb rendszernél pl. 95%-ban olvasás van, attól még nem mindenre igaz. Mérni, mérni és elemezni, aztán cselekedni. Ez az egyetlen örök igazság.

----------------
Lvl86 Troll, "hobbifejlesztő" - Think Wishfully™

Ezt valószínűleg még akkor tanították neked, amikor a fejlesztőket viszont legalább VALAMENNYIRE megtanították adatbázist tervezni.
Mármint valamennyira azon túl, hogy van tábla, abban meg adatok az ő típusukkal.

Jó ideje nem tanítják (ennek nyilvánvaló jelei vannak), de én ennek kifejezetten örülök...

Mondok konkrétumot.

Fogja magát a fejlesztő, és elkezd DB2-ben úton útfélen CLOB-ot használni, mert olvasta, hogy minden típusból annak az elképzelhető legnagyobb a tárolóhossza. Azt is olvassa, mert megkerülhetetlenül leírják, hogy a bufferpoolba nem kerül be a LOB adat, de ezt már nem érti, és az ide kapcsolódó link már nem tartozik szorosan a megvalósítandó feladatához.

Miután kitör a tesztpánik, mert a fejlesztői db-nél 10x akkora adattal a válaszidők az ott tapasztal 100x-osai, fogod magad, megkérdezed, hogy a LOB-ot csak úgy szimpátiaalapon, vagy okkal használják túl. A bizonytalan választra megtizedeled őket (nem a fejlesztőket: a LOB-okat) varchar/char javára. Mire beköszönt a határtalan öröm.

Bármilyen hasonlóság a valósággal csak a véletlen műve.

Teljesen egyetértek abban, hogy a fejlesztő feladata a caching technikák alkalmazása, és pl. az is hogy nem írunk lekéréseket egy for ciklusba.
A file cache, memcache sokat szokott segíteni. Az én tapasztalatom is az, hogy szerver oldalon túl sok mindent nem lehet tenni a terhelés csökkentése
érdekében. :-/

ezt használtam régen, hasznos lehet: http://www.day32.com/MySQL/tuning-primer.sh

Azért a cache se mindenható, ráadásul elég sok problémát felvet (tranzakció-biztonság, konzisztencia stb.). Persze amit lehet cachelni azt lehet, csak azért van túloldala a lónak.

BlackY
--
"en is amikor bejovok dolgozni, nem egy pc-t [..] kapcsolok be, hanem a mainframe-et..." (sj)

"az is hogy nem írunk lekéréseket egy for ciklusba."

Azert ezt sem kezelnem axiomakent. Ha varhatoan tobbszazezer rekordot erintene a ciklustorzs, akkor jobb egy for ciklussal szepen page-lve vegimenni rajta, mint az osszes rekordot lehuzni.
-- 

Ki oda vagyik, hol szall a galamb, elszalasztja a kincset itt alant.

Nem tudom, hogy lehet e egy rossz tervezést korrigálni. Sem azt, hogy lehet e az adatbázis szerkezetét átalakítani úgy, hogy a táblákban lèvő adatok átkerüljenek egy másik táblába akár más formában is. Ha igen, akkor a weboldal összes sql parancsait modósítani, újraírni kell. De ez még semmi, mert lehet, hogy ez miatt php kódrészeket is módosítani kell.
Én azon az állásponton vagyok, hogy próbáljuk meg jól megtervezni és akkor kevesebb probléma lesz később.

......................
Egymás segítésére még: http://pc-kozosseg.com

Én általában RDBMS-ről beszélek, nem PHP+MySQL duóról.
Egy elcseszett tervet csak újratervezéssel és az alkalmazás+adatbázis átírásával tudsz rendesen javítani. Minden egyéb kb. gányolás. (legalábbis Oracle + Java párosnál ezt tapasztaltam, mással meg nem nagyon volt dolgom)

"Nem tudom, hogy lehet e egy rossz tervezést korrigálni"

Azert kemeny, hogy szerinted más nincs a világon, csak,tervezési hiba.

"Sem azt, hogy lehet e az adatbázis szerkezetét átalakítani úgy, hogy a táblákban lèvő adatok átkerüljenek egy másik táblába"

INSERT INTO ujtabla (SELECT ...)

Aztán ALTER-rel fixalod, ami nem jo, majd megcsinalod a külső kulcsokat, etc. Meg csak táblát sem kell elore létrehozni.

----------------
Lvl86 Troll, "hobbifejlesztő" - Think Wishfully™""

Sem azt, hogy lehet e az adatbázis szerkezetét átalakítani úgy, hogy a táblákban lèvő adatok átkerüljenek egy másik táblába akár más formában is.

INSERT ... SELECT, ahogy saxus már írta

Ha igen, akkor a weboldal összes sql parancsait modósítani, újraírni kell.

Ha végtelen idő állt korábban rendelkezésedre és jól volt tervezve/implementálva a rendszered, akkor ugye az interfészt és az implementációt elkülönítetted: ahelyett, hogy egy táblaszerkezet az interface-d, legyenek tárolt eljárások azok. Ennek több előnye van: ha csak és kizárólag tárolt eljárásokon keresztül kommunikálsz (és kommunikálhatsz) kívülről az adatbázissal, akkor "ingyen" kapsz pl. hozzáférés-kezelést, és a belső szerkezeted bármikor módosíthatod, ha a tárolt eljárásokban leköveted ezt és ugyanazt az adathalmazt ugyanabban a formátumban adod vissza, ráadásul előre pontosan tudni fogod hogy pontosan milyen lekérdezések fognak lefutni, így tudsz azokra optimális szerkezetet kitalálni.

BlackY
--
"en is amikor bejovok dolgozni, nem egy pc-t [..] kapcsolok be, hanem a mainframe-et..." (sj)

Én mostanában egyre többször találkozom adattárházas múltból érkező webre fejlesztőkkel. Képesek akkora lekérdezéseket és queryket írni, hogy azért önmagában több óra lenne számlázható. :) A probléma nem is ott van, hogy a query hosszú/durva, hanem hogy ahhoz szoktak, hogy tökmind1 a futásidő. Gondolok itt arra, hogy 5 vagy 30mp is teljesen elfogadható egy kvázi singleuseres appban, mert majd megvárják, mert ilyen és ez ott rendben is van. Egy forgalmas weboldalnál már a fél mp-nél hosszabbak is problémásak. Sokan próbálnak valamilyen (webes léptékkel) írdatlan mennyiségből on-the-fly adatot előállítani aztán flashelnek, hogy kettő darab látogatótól már fulladozik az SQL szerver és alatti a diszkek.

Nekem is kellett/kell adott adathalmazból mindenféle riportot generálnom és szintén nem volt különösebb szempont a futásidő, ráadásul cronból fut néhány óránként. Ezt a juzerekkel egyeztetve állítottuk be, nem is volt igény, hogy realtime legyen. Weben viszont olyat is sikerült összehoznom, hogy a remek táblát beindexelve az sima SELECT szárnyakat kapott, de a sok százezer rekord miatt az a néhány insert ami jött percenként megfektette a dolgokat az index újraépítés miatt... Át kellett tervezni a db-t, mert nem sikerült jól felmérni forgalmat és ahhoz képest a szerver lehetőségeit.

Aztán vannak olyan webfejlesztők, akik különösebb gondolkodás nélkül lekódolják a dolgokat aztán csodálkoznak.

A kedvenc kérdésem, amikor egy tökismeretlen rendszerről kérdezik meg: "na akkor milyen szerver kell alá, ha tv reklám is lesz, de olyat mondjak ami biztosan bírja". Ehhez lazán kapcsolódó kérdéskör a "lassúaszerverolddmeg" jellegű csoda, bár ezek néhány slow query log után le szoktak csitulni és rájönnek, hogy azt a 450k sort már 2 hónapja is törölhették volna, illetve nem fut az X karbantartó izékéjük...

Hogy index ügyben is hozzászóljak. :) A PostgreSQL például szintén intelligensen nézi meg, hogy megéri-e az indexet használni a rekordszám függvényében. Ugyanakkor, ha tudjuk, hogy sok adat érkezik már az elején kell az indexelés. Az EXPLAIN és Postgres-ben az EXPLAIN ANALYZE roppant jól használható a lekérdezések optimalizálására. Nem egyszer derült ki, hogy inkább kettőben vagy más logikával érdemesebb futtatni a SELECT-et, hogy ne menjen át vörösizzásba a vas. :)

Attol hogy select * from -ot atirod direktre, pl select id,name,email from -ra, nem lesz gyorsabb a query-d, kiveve ha covering index-ed van, vagyis maga az index tartalmazza az osszes adatot es nem kell kulon megkeresnie.
Egyebkent viszont hosszutavon megkimelheted magad tobb szivastol, ha direkt select-eket futtatsz, mert ha hozza kell adni valami plusz oszlopot a tablahoz akkor nem kell ujraidnod miatta a query-ket.

( malcolm | 2014. 04. 13., v – 17:45 )

Kapcsold be a slow-query-log-ot, illetve írd be még a mysql konfigjába a log-queries-not-using-indexes bejegyzést.
Ezzel a lassú lekérdezéseket naplózó állományba bekerülnek a rosszul indexelt lekérdezések is.
Szerintem lesz benne bőven...

( KissT | 2014. 04. 13., v – 20:01 )

Monitorozodd amugy valamivel magat a szervert ? Mert siman lehet hogy mashol van a szuk keresztmetszet.
Amugy meg sub

( mhmxs v | 2014. 04. 13., v – 21:41 )

Ez az eszköz a már futó MySQL logból tud tanácsokat adni

( banyek | 2014. 04. 14., h – 00:44 )

"
[--] Reads / Writes: 58% / 42%
"

Sok a write-od, a MyISAM tablak irashoz tabla szinten lockolnak, lassuak lesznek a queryk.
Terj at innodb-re.

Allitsd be a slow-log-ot, es nezd vegig pt-query digesttel, mit mond.
Csinalj indexeket.
De eloszor terj at innodb-re. Semmi okod nem lehet MyISAM mellett maradni, ha ilyen kicsik az adatbazisok, az osszes rekordod ugyis a buffer poolba kerul, tokmindegy, hogy alapvetoen gyorsan olvasnad-e a diszkrol.

Javítson ki valaki ha félreértem, de nekem úgy tűnik, elsősorban nem a sebességgel van gondja a kérdezőnek, hanem mindössze a 100%-on pörgő processzor miatt aggódik.
Még az sem teljesen tiszta, hogy MySQL+myisam engine mellett a lockok okozhatnak-e növekedést a processzor terhelésében. Hogy a load magasabb miatta, azt inkább el tudom fogadni, de nem ismerem eléggé, szóval kizárni sem merem, hogy a lockok miatt megy a proci 100%-on.

"nem a sebességgel van gondja a kérdezőnek, hanem mindössze a 100%-on pörgő processzor miatt aggódik."

Nincs gondja a sebességgel, mert a műveletei az emberi türelemhez képest gyorsan végrehajtódnak, hála a kis adatmennyiségnek és gyors CPU-nak.
Viszont éppen a kis adatmannyiségből és gyors CPU-ból következik, hogy itt valami nagyon aluloptimalizált -- vagyis a gyorshoz képest sokkal gyorsabbnak kéne lennie, kisebb prochasználat mellett, ergo végeredményben mégis a sebességgel van gondja, csak nem érzi.

Ha maxon jaratnak egy rendszert, akkor legfeljebb 70-80%-os CPU usage lehet, peakek elerhetik a 100-at. Ha konstans 100% a kihasznaltsag, az az esetek 95%-aban valamilyen szuk keresztmetszetre vagy adatbazis-felepitesi problemara utal (hianyzo indexek, nagy joinok, tul sok parallel write, stb).

Ne felejtsd el, hogy a mysql igyekszik mindent cachelni, foleg a nagy lekerdezeseket, mert o se szeret jobban dolgozni, mint egy atlag rendszergazda :-)
-- 

Ki oda vagyik, hol szall a galamb, elszalasztja a kincset itt alant.

illetve a gyakran írt tábláknál meg kellene nézni, hogy az azokon használt indexekre tényleg szükség van-e.

Láttam már olyat, hogy szétindexelt táblák "jó lesz ez még egyszer valamire" alapon elhelyezett, egyébként teljesen fölösleges indexeinek folyamatos frissítésével töltötte idejét a server...

( handler | 2014. 04. 15., k – 14:11 )

Konnyen lehet, hogy rendezes vagy join amivel az idot tolti. Pontos diagnozis es meres nelkul nem lesz eredmeny. Hogy nez ki a stack trace, amikor 100%-on porog a cpu? (hasznald a "Full technology demonstration" alatti scriptet)

http://poormansprofiler.org/