[Dirty Harry]

Tere.

Tuli virtuaalserveri teenusepakkujalt selline automaatne e-mail:

Spoiler

MySQL serveri jälgimisrobot on tuvastanud, et Teie kasutajakonto alt on tehtud liiga palju serverit koormavaid MySQL päringuid.

Kui see jutt tundub Teile arusaamatu, edastage palun see e-mail oma kodulehekülje haldajale. On tähtis, et selles e-mailis sisalduva info põhjal võetakse midagi ette, sest vastasel korral võidakse Teie kodulehekülg automaatselt sulgeda, et vältida serveri ülekoormamist.

Käesoleva kirjaga on kaasas fail queries.txt, mis sisaldab kõikiMySQL päringuid, mis on kulutanud serveri ressursse rohkem kui 5 sekundit. Kuna MySQL päringuid võidakse teha ühes sekundis kuni mitusada tükki, on väga oluline, et need kõik oleksid kiired ja täpsed.

Palun analüüsige kaasasolevas failis väljatoodud päringuid ning optimeerige neid seejärel selliselt, et need saaksid serverilt vastuse lühema aja jooksul. See tähendab eelkõige indeksite lisamist veergudele, mille järgi tehakse enamus päringutest, ning konkreetsete tulemusveergude määramine SELECT * asemel. Tuleb arvestada, et igasuguse tabelite liitmise puhul (näiteks LEFT JOIN) peavad liitmiseks kasutatavad kattuvad tabelite veerud kindlasti olema indekseeritud. Ka tuleb jälgida, et MySQL tabel ei kasvaks liiga suureks. Mittevajalik ja ebaoluline info tuleb aeg-ajalt kustutada.

Optimeerimise kohta võite leida kasulikku infot järgnevatelt
lehekülgedelt:
http://linuxformat.co.uk/wiki/index.php/PHP_-_Optimising_MySQL_queries
http://hackmysql.com/optimize
http://www.databasejournal.com/features/mysql/article.php/1382791

Kaasas logi:

Spoiler

# User@Host: xxxxxx_yyyyyyyy[xxxxxx_yyyyyyyy] @ localhost []
# Query_time: 5.036314 Lock_time: 0.000114 Rows_sent: 1 Rows_examined: 12783

use xxxxxx_yyyyyyyy; SET timestamp=1395961378; SELECT COUNT(*) AS max FROM pixelpost_pixelpost AS p, pixelpost_tags AS t WHERE t.alt_tag LIKE '' AND p.id = t.img_id AND p.datetime<='2014-03-28 02:02:52' GROUP BY t.tag ORDER BY max DESC LIMIT 1;

# User@Host: xxxxxx_yyyyyyyy[xxxxxx_yyyyyyyy] @ localhost []
# Query_time: 5.525273 Lock_time: 0.000123 Rows_sent: 1986 Rows_examined: 14768

SET timestamp=1395961380; SELECT ROUND(COUNT(*)/95,1) AS rank, tag, COUNT(*) as cnt FROM pixelpost_pixelpost AS p, pixelpost_tags AS t WHERE t.alt_tag LIKE '' AND p.id = t.img_id AND p.datetime<='2014-03-28 02:02:50' GROUP BY tag ORDER BY tag;jne, järjest analoogsed päringud.

Kuna ma MySQL'i nüansse väga ei tunne siis ei oska hästi midagi peale hakata. Tegu on Pixelpostil jooksva leheküljega, mis siis kasutab all MySQL baasi. Arvestades, et Pixelposti näol on tegu aastaid arendusest maas oleva lahendusega siis ma ise kardan, et äkki on tegu mingitsorti (spämmi)robotiga, mis käis indekseerimas.

Igaljuhul - mida võiks ette võtta? Ma ise mõtlen juba tükk aega, et peaks Pixelposti pealt migrama kodulehe millegi teise peale kuna ilmselgelt pole Pixelposti näol enam tegu turvalise lahendusega. Või annaks kuidagi lehte/baasi kaitsta? Või peakski indeksite lisamise suunas vaatama?

[kullar]

Proovi lasta käima EXPLAIN käsuga mõned päringud ja postita vastused siia. Lase käima selliselt:

EXPLAIN SELECT COUNT(*) AS max
FROM pixelpost_pixelpost AS p, pixelpost_tags AS t
WHERE t.alt_tag LIKE '' AND p.id = t.img_id AND p.datetime<='2014-03-28 02:02:52'
GROUP BY t.tag
ORDER BY max DESC
LIMIT 1

Ehk iga päringu ette pane EXPLAIN käsk ja paned mõned tulemused siia. See aitab näha, et kas väljd on üldse indekseeritud, kui pole, siis tuleks seda teha.

[Dirty Harry]

Sedasorti tulemus on (ei osanud kuidagi viisakalt teksti siia foorumisse poetada): https://www.dropbox.com/s/rdr85v905az0wzc/mysql_v2ljund.PNG
Kas saab selle põhjal midagi otsustada?

[andresv]

esiteks count(*) vist tuleks asendada millegi konkreetse countimiseg, count(p.id) näiteks. Ja nagu öeldud tuleb vanu andmeid aegajalt kustutada (arhiveerida)
Arvatavasti "AND p.datetime<='2014-03-28 02:02:52' " tingimus tagastab liiga plaju ridu ja tulemus ei mahu enam mällu ning sorteerimiseks kasutatakse faile kettal mis teeb asja kordi aeglasemaks.

võibolla aitab ka p.datetime veerule indeksi tegemine.

[renekas]

Where lausete väljadele tee indeksid ja kui countid siis counti indekseeritud väljadelt mitte tervet tabelit.

[kullar]

Pane selline index, mis teeb joini kiiremaks. Vaatan, et kuupäeval on index olemas. Kui paned indexi ära, siis pane uuesti siia see explain tulemus.

[renekas]

pixelpost_pixelpost ID peale ka kui juba ei ole.

[Dirty Harry]

Midagi tegin:
https://www.dropbox.com/s/zr3b8wp2ap2afkh/mysql_v2ljund_2.PNG

[kullar]

[renekas]

Kus mina tean kas on primary key või mitte, kohtan pidevalt selliseid tabeleid kuhu pole üldse contrainte pandud.

[kullar]

[serk]

Tuimalt indeksid peale või
Klge mehed, lugege natuke ennem ja tehke endale selgeks, mis asi on indeks ja kuidas ta töötab.

Indeksit kasutatakse juhul, kui andmehulk mida päritakse tabelist, jääb ca 3% mahu sisse. Kui tagastatavate andmete hulk on suurem, teeb indeks asja üldjuhul hullemaks. Kui vaadata antud päringut, siis probleem on andmehulgas mida päritakse.
EXPLAIN SELECT COUNT(*) AS max
FROM pixelpost_pixelpost AS p, pixelpost_tags AS t
WHERE t.alt_tag LIKE '' AND p.id = t.img_id AND p.datetime<='2014-03-28 02:02:52'
GROUP BY t.tag
ORDER BY max DESC
LIMIT 1

võetakse ju kõik andmed mis on vanemad kui 28.03 - mis seal siis oodata? Arvata võib, et ajaloos on palju palju datat. Full Scan tehakse sellisel juhul anyway, olenemata kas paned sinna indeksi või mitte. Sa kammid lihtsalt kõik andmed oma kuupäeva filtri tõttu läbi.

Ja see on nagu kõige tobedam "häkk" mida teha annab, arvates, et see piirab kuidagi baasi päringu mahtu või tööd: "ORDER BY max DESC LIMIT 1 " Isegi loogiliselt mõeldes peaks aru saama, mis siin valesti on koormuse mõistes(miks see ei aita koormust lahendada)

pane datetime betweeni vahele, from ja to, IO väheneb sul kolaki kohe. Peale seda vaata andmehulka mida päritakse. Kui see jääb 3% sisse, pane indeksid peale. Peale seda vaata päringut jälle EXPLAIN planiga ja kas asjad paranesid või mitte.
Samuti võid partitsioneerida tabeleid, pakun, et arhiveerimine siin juhul on keeruline. isegi PhpMyAdmin minuarust suudab näidata indeksi mõtekust veergudel, kasuta kasvõi seda abina enda jaoks.

[2korda2]

Saan ma õigesti aru - päring loendab/grupeerib kirjeid vastavalt veeru TAG väärtusele (mida see sisaldab? aga teised päringus kasutatud veerud?) ja tagastab siis suurima väärtuse (kirjete arvu) ilma TAG väärtuseta? Mida selline päring sisuliselt annab? Näiteks: "siin foorumis on postituste arvu järgi suurimas teemas 420 postitust". No mida sellise asjaga peale hakata? Oleks seal "tag, count(*)", saaks aru (suurim teema postituste arvu järgi on X ja selles on Y kirjet).

PS. ma eeldan, et p.id on PK ja t.img_id-le on defineeritud FK, mis viitab esimesele?
PPS. Ma pole ise MySQLi tarbinud, ainult suured kommertstooted.