[es] - Optimizacija upita, filesort, using temporary

bogdan.kecman
Bogdan Kecman
"specialist"
Oracle
srbistan

Član broj: 201406
Poruke: 15887
*.31.24.217.adsl2.beograd.com.

Sajt: mysql.rs

+2377 Profil

Re: Optimizacija upita, filesort, using temporary

^{14.08.2009. u 14:32 - pre 179 meseci}

dodaj posle select jedan SQL_NO_CACHE kako bi zaobisao kesh (ne na production serveru nego za test nad bekapom baze .. dakle da proverimo brzinu upita) .. pa odradi

Code:

SELECT SQL_NO_CACHE v.id, v.naslov, a.ime_prezime, v.mala_slika, v.uvod
FROM vijesti v, kategorije k, administratori a
WHERE k.kategorija_id = v.kategorija
AND v.autor = a.id
AND v.tip =1
AND v.media =0
AND v.pozicija
IN (
'5', '6', '7', '8', '9', '1000'
)
AND k.parent_id
IN ( 4, 5 )
AND v.id NOT
IN ( 22083, 22075, 22077, 22070 )
AND v.datum_objave <= '2009-08-13 10:58:00'
ORDER BY v.pozicija, v.datum_objave DESC
LIMIT 5

pusti ga 3-4 puta pa reci koliko traje upit ... a onda uradi sledece

Code:

SELECT SQL_NO_CACHE v.id, v.naslov, a.ime_prezime, v.mala_slika, v.uvod
FROM vijesti v, kategorije k, administratori a
WHERE k.kategorija_id = v.kategorija
AND v.autor = a.id
AND v.tip =1
AND v.media =0
AND v.pozicija
IN (
'5', '6', '7', '8', '9'
)
AND k.parent_id
IN ( 4, 5 )
AND v.id NOT
IN ( 22083, 22075, 22077, 22070 )
AND v.datum_objave <= '2009-08-13 10:58:00'
ORDER BY v.pozicija, v.datum_objave DESC
LIMIT 5

isto 3-4 puta .. pa daj brzine .. i na kraju ..

Code:

SELECT SQL_NO_CACHE v.id, v.naslov, a.ime_prezime, v.mala_slika, v.uvod
FROM vijesti v, kategorije k, administratori a
WHERE k.kategorija_id = v.kategorija
AND v.autor = a.id
AND v.tip =1
AND v.media =0
AND v.pozicija
IN (
'5', '6', '7', '8', '9'
)
AND k.parent_id
IN ( 4, 5 )
AND v.id NOT
IN ( 22083, 22075, 22077, 22070 )
AND v.datum_objave <= '2009-08-13 10:58:00'

3-4 puta .. pa brzine ...

tebe "dopunjavanje do 5" komada za slucaj kada ti fali neka pozicija vrlo verovatno "debelo kosta" tako da se mozda vise isplati da pokupis u 2 upita potrebne vrednosti ... dakle u jednom prolazu uzmes 5,6,7,8,9 i count(*) a onda u drugom prolazu ako ima potrebe uzmes jos onoliko koliko ti fali ..

generalno gledano, tebi query cache ovde radi posao .. bez obzira koliko je posecen sajt - sto poseceniji to bolje ... upiti su ti kesirani sve dok ne odradis update .. tj promenis sta treba da se nadje na naslovnoj - sto je regularno ... i opet, samo ce prvi ko cimne stranu da saceka 0.x sekundi za generisanje i opet imas sve upite kesirane ... doduse, 5.1 je malo bolji (ovaj poslednji) nego 5.0 posto 5.0 u nekim cudnim uslovima moze da se zabode pod velikim opterecenjem (odbodes ga sa "flush tables"). pritom - sa 20K slogova, tebi je cela ova tabela svejedno kesirana u ram-u ..

aj izgenerisi neke random podatke pa da probam par razlicitih promena nad samim upitom da vidimo sta moze tu jos da se uradi ... za pocetak radis order by pokoloni koja nije indexirana (to teoretski nevalja, prakticno nema nikakve razlike) ... imas 3x in .. to je sporo ... etc etc ... al .. sve je to nebitno dok se ne proba

arhimed
http://www.mysql.rs/ | http://elco.crsndoo.com/

Odgovor na temu

bogdan.kecman
Bogdan Kecman
"specialist"
Oracle
srbistan

Član broj: 201406
Poruke: 15887
*.31.24.217.adsl2.beograd.com.

Sajt: mysql.rs

+2377 Profil

Re: Optimizacija upita, filesort, using temporary

^{15.08.2009. u 06:00 - pre 179 meseci}

ovako, prvo sto terba da uradis da bi dobio na brzini je:

Code:

-- napravi administratori tabelu da bude fixed
alter table administratori modify korisnicko_ime char(40), modify ime_prezime char(40);

-- napravi kategorije tabelu da bude fixed
alter table kategorije modify naziv char(40);

-- ista prica sa tabelom vijesti
-- ovde sada imas dupli problem, ajde sto je TEXT za "uvod" cinio tabelu "dynamic"
-- (sto je za myisam dosta sporije od fixed) vec dodatno za svaki join sve kreirane
-- temp tabele ce biti na disku sto je znacajno sporije nego u ram-u (bez obzira koliko mu
-- das memorije za temp/heap tabele)
-- inace kada radis join UVEK se kreira temp tabela (bez obzira da li to explain prikaze ili ne)
--
-- ako ne mozes da smestis uvod u 255 karaktera, napravi zasebnu tabelu gde drzis uvod
-- pa ga povuci preko id-a kada budes morao ili ostavi TEXT ali onda konfigurisi server tako
-- da ti je tempdir na ram disku
alter table vijesti modify naslov char(100), modify mala_slika char(100), modify uvod char(255);

pre ove promene, sa ovom 20K slogova tabelom upit (iz originalnog posta) je trajao na mom desktopu izmedju 0.04 i 0.05 sekundi. posle ove promene (bez ikakve promene indexa / upita) upit traje 0.01 do 0.02sec

ako vratim uvod na TEXT brzina je 0.03 - 0.04

ako sada mysql namestim tako da u my.cnf dodam tmpdir=/dev/shm .. dakle kazem mu da koristi ram za mysql temp dir .. eh .. i nikakve promene ... 0.03 opet ...

jbg .. mnoooooooogo je brz .. sve to ispod 0.1sec je statisticka greska :) ... teba mi neka sporija masina za ovo testiranje :D

sve u svemu - osnovna sporost upita ti je u tome sto je v.uvod TEXT sto znaci da ce mysql da kreira temp tabelu na disku sto znaci da je upit sam po sebi ultra spor ... da bi to izbegao, ili izvbaci v.uvod iz upita ili ako moze da se uglavi u char(255) promeni ga u to, ili konfigurisi mysql da mu je tmpdir na ram disku kako bi zaobisao taj problem...

evo ga jedan "update vijesti set uvod=repeat(uvod, 10000);"

sada upiti traju malo duze :D

sa tmpdirom setovanim da pokazuje na /tmp (default) .. upit traje od 2.02 do 4.42sec zavisno od toga da li nesto drugo na masini trosi disk io... kada prebacim da je tmpdir na /dev/shm (ram disk) isti upit traje od 0.33 do 0.57sec

pritom, uzmi u obzir da masina "ne radi nista", dakle IO mi je free ... da imam konkurentne upite + neki apache koji drlja disk .. ovaj sa ram diskom bi ostao na 0.3-0.6sec dok bi onaj sto zavisi od io-a otisao na mnoooooooogo vise (pritom, ova masina ima 8G rama i ne radi nista trenutno dakle preko 4G rama je disk cache sto je dodatno pomoglo za onaj io bound query).

sad malo prebudzimo query ...

Code:

SELECT SQL_NO_CACHE
  v.id, v.naslov, a.ime_prezime, v.mala_slika, v.uvod
FROM
  vijesti v
  JOIN kategorije k ON (k.kategorija_id = v.kategorija)
  JOIN administratori a ON (v.autor = a.id)

WHERE
    v.tip = 1
AND v.media = 0
AND v.pozicija
IN ('5', '6', '7', '8', '9', '1000')
AND k.parent_id IN ( 4, 5 )
AND v.id NOT IN ( 22083, 22075, 22077, 22070 )
AND v.datum_objave <= '2009-08-13 10:58:00'
ORDER BY v.pozicija, v.datum_objave DESC
LIMIT 5;

isto to samo malo drugacije .. optimizer "manje pogadja" .. dobili smo ovde u proseku 0.1s ali onda ..

Code:

SELECT SQL_NO_CACHE
  v.id, v.naslov, a.ime_prezime, v.mala_slika, v.uvod
FROM
  administratori a
  JOIN vijesti v ON (v.autor = a.id)
  JOIN kategorije k ON (k.kategorija_id = v.kategorija)

WHERE
    v.tip = 1
AND v.media = 0
AND v.pozicija
IN ('5', '6', '7', '8', '9', '1000')
AND k.parent_id IN ( 4, 5 )
AND v.id NOT IN ( 22083, 22075, 22077, 22070 )
AND v.datum_objave <= '2009-08-13 10:58:00'
ORDER BY v.pozicija, v.datum_objave DESC
LIMIT 5;

i dobijemo ispod 2s

e sada "ovo nije normalno" :D ... dakle to je fora sto optimizer nekad ne odradi posao kako treba .... tako da realno to mozemo da zanemarimo :D ali je zgodno za spomenuti :D

ono sto je u celoj prici bitno je

Code:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: k
         type: range
possible_keys: PRIMARY,parent_id
          key: parent_id
      key_len: 4
          ref: NULL
         rows: 10
        Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: v
         type: ref
possible_keys: PRIMARY,datum_objave,kategorija
          key: kategorija
      key_len: 4
          ref: es.k.kategorija_id
         rows: 435
        Extra: Using where
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: a
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: es.v.autor
         rows: 1
        Extra:
3 rows in set (0.00 sec)

ovo nije optimalno ... da pogledamo upit sam za sebe ..

Code:

sta: v.id, v.naslov, a.ime_prezime, v.mala_slika, v.uvod -- prvi problem, v.uvod je text znaci imamo temp tabele na disku

kako:
v.autor =
v.tip =
v.media =
v.pozicija IN
v.id NOT IN
v.datum_objave <=
v.kategorija =

k.parent_id IN
k.kategorija_id = v.kategorija

a.id =
sortiraj: v.pozicija, v.datum_objave

bez gledanja u tabele ... "ovde ima puno polja koja bi bilo zgodno da su indexirana" ... a mysql ne ume da koristi mnogo indexa u istom upitu ...

krenemo od pozadi ... administratori .. koristi se samo id .. on je primarni kljuc .. tabela ima tri ipo sloga .. dakle tu nista nema sta da se pipa ...

onda kategorije .. tu nam treba kompozitni kljuc, dakle

Code:

alter table kategorije modify kategorija_id int(5);
alter table kategorije drop key parent_id;
alter table kategorije drop key naziv;
alter table kategorije drop primary key;
alter table kategorije add primary key (kategorija_id, parent_id);
alter table katogorije modify kategorija_id int(5) auto_increment;

dakle, sada tabela treba da izgleda:

Code:

mysql> show create table kategorije\G
*************************** 1. row ***************************
       Table: kategorije
Create Table: CREATE TABLE `kategorije` (
  `kategorija_id` int(5) NOT NULL AUTO_INCREMENT,
  `parent_id` int(5) NOT NULL DEFAULT '0',
  `naziv` char(40) DEFAULT NULL,
  PRIMARY KEY (`kategorija_id`,`parent_id`)
) ENGINE=MyISAM AUTO_INCREMENT=159 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

i sada upit traje 0.3sekunde ... dakle vise nego 10 puta krace ...

explain je blago drugaciji:

Code:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: v
         type: range
possible_keys: PRIMARY,datum_objave,kategorija
          key: datum_objave
      key_len: 9
          ref: NULL
         rows: 14185
        Extra: Using where; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: k
         type: ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: es.v.kategorija
         rows: 1
        Extra: Using where; Using index
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: a
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: es.v.autor
         rows: 1
        Extra:
3 rows in set (0.00 sec)

sad nam jos ostaju vesti ...

da probamo najtruluju foru:

Code:

alter table vijesti add key (id, autor, tip, media, pozicija, datum_objave);

sta kaze explain:

Code:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: v
         type: range
possible_keys: PRIMARY,datum_objave,kategorija,id
          key: datum_objave
      key_len: 9
          ref: NULL
         rows: 14185
        Extra: Using where; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: k
         type: ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: es.v.kategorija
         rows: 1
        Extra: Using where; Using index
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: a
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: es.v.autor
         rows: 1
        Extra:
3 rows in set (0.00 sec)

kaze da vidi nas key (id) ali nece da ga koristi ... aj da vidimo ocel biti nesto brze ako mu ga damo po forsiranju

Code:

mysql> explain SELECT SQL_NO_CACHE v.id, v.naslov, a.ime_prezime, v.mala_slika, v.uvod FROM vijesti v force key (id), kategorije k, administratori a WHERE k.kategorija_id = v.kategorija AND v.autor = a.id AND v.tip =1 AND v.media =0 AND v.pozicija IN ( '5', '6', '7', '8', '9', '1000' ) AND k.parent_id IN ( 4, 5 ) AND v.id NOT IN ( 22083, 22075, 22077, 22070 ) AND v.datum_objave <= '2009-08-13 10:58:00' ORDER BY v.pozicija, v.datum_objave DESC LIMIT 5\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: v
         type: range
possible_keys: id
          key: id
      key_len: 4
          ref: NULL
         rows: 20005
        Extra: Using where; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: k
         type: ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: es.v.kategorija
         rows: 1
        Extra: Using where; Using index
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: a
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: es.v.autor
         rows: 1
        Extra:
3 rows in set (0.02 sec)

super .. force radi, znamo sintaksu :D .. ali da li ima razlike u brzini:

ima .. malopre je bio 0.3 sad je 0.36-0.4 .. znaci sporo ... da probamo malo da promenimo redosled ...

Code:

alter table vijesti add key kljuc2 (datum_objave, id, autor, tip, media, pozicija);

SELECT SQL_NO_CACHE v.id, v.naslov, a.ime_prezime, v.mala_slika, v.uvod FROM vijesti v force key (kljuc2), kategorije k, administratori a WHERE k.kategorija_id = v.kategorija AND v.autor = a.id AND v.tip =1 AND v.media =0 AND v.pozicija IN ( '5', '6', '7', '8', '9', '1000' ) AND k.parent_id IN ( 4, 5 ) AND v.id NOT IN ( 22083, 22075, 22077, 22070 ) AND v.datum_objave <= '2009-08-13 10:58:00' ORDER BY v.pozicija, v.datum_objave DESC LIMIT 5\G

tu smo negde na 0.3 - 0.33 ...

sada tu mozes da se igras .. realno zgodno je da tu imas kljuc koji za prvo polje ima EQ koji najvise filtrira tabelu (dakle na primer ako ce tip da izbaci 90% kolona iz rezultata - njega stavis prvog ... range polja stavis poslednja - kao na primer ovaj datum) ..

elem .. imamo ga sada (bez hintova i bez ovog velikog kljuca) na 0.3 ... (jos uvek smo sa temp diskom na /tmp) ... sve to isto samo tmpdir stavimo na ram disk i brzina je: 0.09s

arhimed
http://www.mysql.rs/ | http://elco.crsndoo.com/

Odgovor na temu