[es] - Upit je napunio log slow queries

luka357
Banja Luka

Član broj: 16848
Poruke: 34
*.nezavisne.com.

Profil

^{18.06.2009. u 21:30 - pre 180 meseci}

Jedan od upita je sam napunio log slow queries. Malo sam zbunjen jer slicni ili skoro isti upiti to ne rade. Razlika izmedju njih je sto tamo nema uslova "vijesti`.vijesti_meni NOT IN (15)".

Greska je otkrivena kad je baza pocela da otkazuje pristup, a to se odrazilo na prikazivanje stranica. Ovo nije bio sam moj problem nego citavog servera. Naknadno mi je receno kako je na bazi promjenjeno "max connections" na 50, a "max user connections" na 35.

Kad sam ovaj kod ispitao lokalno on nije pokazivao nikakva usporavanja. Dok je u logovim na serveru to bio slucaj.

Code:
SELECT
`vijesti`.vijesti_id,
`vijesti`.vijesti_meni,
`vijesti`.vijesti_naslov,
DATE_FORMAT( `vijesti`.vijesti_dat_prikazi, '%d.%m.%Y %H:%i') as datum,
DATE(`vijesti`.vijesti_dat_prikazi) as prikazi,
`vijesti`.vijesti_status,
`vijesti`.vijesti_broj,
`meni`.idmeni,
`meni`.ime,
`meni`.link
FROM vijesti `vijesti`, meni `meni`
WHERE (`vijesti`.vijesti_meni = `meni`.idmeni)
AND (`vijesti`.vijesti_meni NOT IN (15))
AND (`vijesti`.vijesti_dat_prikazi <= NOW())
AND (`vijesti`.vijesti_status = 'Aktivna')
ORDER BY prikazi DESC, `vijesti`.vijesti_broj DESC LIMIT 0, 15;

Zaglavlje loga:

Code:
Query_time: 24  Lock_time: 0  Rows_sent: 15  Rows_examined: 23394
Query_time: 12  Lock_time: 0  Rows_sent: 15  Rows_examined: 23394
Query_time: 37  Lock_time: 0  Rows_sent: 15  Rows_examined: 23394

Sat bih trebao promjeniti da poboljsam upit i na sta da obratim paznju.

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: Upit je napunio log slow queries

^{18.06.2009. u 21:43 - pre 180 meseci}

daj
- explain extended tog upita (explain extended select ...)
- tabele koje upit koristi (show create table ...)

upit ti traje 20+ sekundi ... ono sto je problem je: Rows_sent: 15 Rows_examined: 23394 ... dakle ti si prosao kroz 23 hiljade rekorda da bi vratio 15 ....

AND (`vijesti`.vijesti_meni NOT IN (15)) je sporije od AND (`vijesti`.vijesti_meni <> 15)

`vijesti`.vijesti_status = 'Aktivna' , zasto ovo nije neki ID nego poredis stringove? ili mozda neki set ako bas oces da se vidi sta je u pitanju ?

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

Odgovor na temu

luka357
Banja Luka

Član broj: 16848
Poruke: 34
*.nezavisne.com.

Profil

Re: Upit je napunio log slow queries

^{18.06.2009. u 23:33 - pre 180 meseci}

Create table

Code:
vijesti | CREATE TABLE `vijesti` (
  `vijesti_id` int(10) NOT NULL auto_increment,
  `vijesti_meni` int(11) NOT NULL default '0' COMMENT 'ID od menija',
  `vijesti_naslov` varchar(200) NOT NULL,
  `vijesti_lid` text NOT NULL,
  `vijesti_tjelo` longtext NOT NULL,
  `vijesti_autor` varchar(50) NOT NULL default '',
  `vijesti_datum` datetime NOT NULL default '0000-00-00 00:00:00',
  `vijesti_dat_izmjene` timestamp NOT NULL default '0000-00-00 00:00:00' on update CURRENT_TIMESTAMP,
  `vijesti_broj` int(7) NOT NULL default '1' COMMENT 'Brojanje otvaranja',
  `vijesti_poredak` int(2) NOT NULL default '0',
  `vijesti_polozaj` enum('Arhiva','Top','Glavna') NOT NULL default 'Arhiva',
  `vijesti_dat_prikazi` datetime NOT NULL default '0000-00-00 00:00:00',
  `vijesti_naslovna_polozaj` enum('Rubrike','Top','Naslovna') NOT NULL default 'Rubrike',
  `vijesti_naslovna_poredak` int(2) NOT NULL default '0',
  `vijesti_status` enum('Neaktivna','Aktivna') NOT NULL default 'Neaktivna',
  PRIMARY KEY  (`vijesti_id`),
  KEY `vijesti_meni` (`vijesti_meni`)
) ENGINE=MyISAM AUTO_INCREMENT=23752 DEFAULT CHARSET=utf8 |

explain extended tog upita

Code:
explain extended SELECT  `vijesti`.vijesti_id,
    ->                         `vijesti`.vijesti_meni,
    ->                         `vijesti`.vijesti_naslov,
    ->                         DATE_FORMAT(`vijesti`.vijesti_dat_prikazi, '%d.%m.%Y %H:%i') as datum,
    ->                         DATE(`vijesti`.vijesti_dat_prikazi) as prikazi,
    ->                         `vijesti`.vijesti_status,
    ->                         `vijesti`.vijesti_broj,
    ->                         `meni`.idmeni,
    ->                         `meni`.ime,
    ->                         `meni`.link
    ->         FROM vijesti `vijesti`, meni `meni`
    ->         WHERE (`vijesti`.vijesti_meni = `meni`.idmeni)
    ->         AND (`vijesti`.vijesti_meni NOT IN (15))
    ->                 AND (`vijesti`.vijesti_dat_prikazi <= NOW())
    ->                 AND (`vijesti`.vijesti_status = 'Aktivna')
    ->         ORDER BY prikazi DESC,
    ->                         `vijesti`.vijesti_broj DESC LIMIT 0, 15;
+----+-------------+------------+------+---------------+--------------+---------+--------------+------+----------------------------------------------+
| id | select_type | table      | type | possible_keys | key          | key_len | ref           | rows | Extra                                       |
+----+-------------+------------+------+---------------+--------------+---------+-----------+------+----------------------------------------------+
|  1 | SIMPLE      | meni    | ALL  | PRIMARY       | NULL         | NULL    | NULL         |   24 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | vijesti | ref  | vijesti_meni  | vijesti_meni | 4       | meni.idmeni | 1065 | Using where                               |
+----+-------------+------------+------+---------------+--------------+---------+---------------------------+------+---------------------------------+
2 rows in set, 1 warning (0.00 sec)

Sad kad gledam izgleda mi da najveci problem radi uslov "`vijesti`.vijesti_meni = `meni`.idmeni"

Odgovor na temu

Shinhan
PHP programmer
Subotica

Član broj: 12327
Poruke: 372
*.static.isp.telekom.rs.

Jabber: shinhan@elitesecurity.org
ICQ: 400847988

+4 Profil

Re: Upit je napunio log slow queries

^{19.06.2009. u 07:19 - pre 180 meseci}

A tabela meni?
Ako je meni.idmeni primary key (što je vrlo verovatno) onda ti taj join nije slaba karika nego neki od drugih neindexiranih polja.

Takođe, indexiraj vesti_broj, vesti_dat_prikaz i vesti_status

"Common sense is not so common." - Voltaire

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: Upit je napunio log slow queries

^{19.06.2009. u 12:45 - pre 180 meseci}

daj i taj drugi create table ..

problem ti je ovde

Citat:

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

Odgovor na temu

luka357
Banja Luka

Član broj: 16848
Poruke: 34
*.nezavisne.com.

Profil

Re: Upit je napunio log slow queries

^{19.06.2009. u 16:52 - pre 180 meseci}

Code:
CREATE TABLE `meni` (
  `tip` enum('grupa','meni','submeni','tip') NOT NULL default 'grupa',
  `idmeni` int(4) NOT NULL auto_increment,
  `ime` varchar(30) NOT NULL,
  `status` enum('on','off') NOT NULL default 'on',
  `poredak` int(2) NOT NULL default '0',
  `od` int(4) NOT NULL default '0',
  `private` enum('meni','admin') NOT NULL default 'meni',
  `link` varchar(30) NOT NULL,
  PRIMARY KEY  (`idmeni`)
) ENGINE=MyISAM AUTO_INCREMENT=26 DEFAULT CHARSET=utf8

Ovo je druga tabela.

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: Upit je napunio log slow queries

^{22.06.2009. u 07:44 - pre 180 meseci}

u extra sam frci tako da nisam stigao ovo da probam, ali ako imas test sistem na kome mozes da probas ... uradi sledece:

Code:

alter table vijesti add key i_comp(vijesti_meni,vijesti_status,vijesti_dat_prikazi);

pa onda uradi ponovo explain .. i proveri koliko sada traje taj isti upit ... javi kako je proslo (posalji novi explain)

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

Odgovor na temu

luka357
Banja Luka

Član broj: 16848
Poruke: 34
81.93.77.*

Profil

Re: Upit je napunio log slow queries

^{22.06.2009. u 13:01 - pre 180 meseci}

Uradio sam explain nakon sto sam dodao indexe i po onome sto je prikazano nista se nije mjenjalo.
Razlika izmedju testne baze i baze na serveru je u kolicini podataka i verziji MySQL. U testnom ima manje do 4x, dok je testna 5.1 a na serveru 5.0

Code:
explain extended SELECT
`vijesti`.vijesti_id,
`vijesti`.vijesti_meni,
`vijesti`.vijesti_naslov,
DATE_FORMAT( `vijesti`.vijesti_dat_prikazi, '%d.%m.%Y %H:%i') as datum,
DATE(`vijesti`.vijesti_dat_prikazi) as prikazi,
`vijesti`.vijesti_status,
`vijesti`.vijesti_broj,
`meni`.idmeni,
`meni`.ime,
`meni`.link
FROM vijesti `vijesti`, meni `meni`
WHERE (`vijesti`.vijesti_meni = `meni`.idmeni)
AND (`vijesti`.vijesti_meni NOT IN (15))
AND (`vijesti`.vijesti_dat_prikazi <= NOW())
ORDER BY prikazi DESC, `vijesti`.vijesti_broj DESC LIMIT 0, 15;

id     select_type     table     type     possible_keys     key     key_len     ref     rows     filtered     Extra
1     SIMPLE | vijesti |range | vijesti_meni,vijesti_dat_prikazi,i_comp |    vijesti_dat_prikazi |    8 |    NULL |    9071 |    100.00 | Using where; Using filesort
1     SIMPLE | meni |    eq_ref | PRIMARY | PRIMARY |    4 |    fokus.vijesti.vijesti_meni |    1 |    100.00      |

Pokusao sam da ogranicim upit na manji broj podataka i to sam uspio. On se izvrsava puno brze.
prvi upit 0:00:00.156
drugi upit 0:00:00.062

Drugi upit:

Code:
explain extended SELECT
`vijesti`.vijesti_id,
`vijesti`.vijesti_meni,
`vijesti`.vijesti_naslov,
DATE_FORMAT( `vijesti`.vijesti_dat_prikazi, '%d.%m.%Y %H:%i') as datum,
DATE(`vijesti`.vijesti_dat_prikazi) as prikazi,
`vijesti`.vijesti_status,
`vijesti`.vijesti_broj,
`meni`.idmeni,
`meni`.ime,
`meni`.link
FROM vijesti `vijesti`, meni `meni`
WHERE (`vijesti`.vijesti_meni = `meni`.idmeni)
AND (`vijesti`.vijesti_meni <> 15)
AND (`vijesti`.vijesti_dat_prikazi <= NOW())
AND (`vijesti`.vijesti_status = 'Aktivna')
AND ( `vijesti`.vijesti_dat_prikazi >= DATE_SUB(NOW(), INTERVAL 48 HOUR))
ORDER BY prikazi DESC, `vijesti`.vijesti_broj DESC LIMIT 0, 15;

1    SIMPLE          vijesti |    range |    vijesti_meni,vijesti_dat_prikaz,i_comp|    vijesti_dat_prikaz|    8 |    716 |    Using where; Using filesort
1    SIMPLE | meni |    eq_ref | PRIMARY | PRIMARY | 4 |    fokus.vijesti.vijesti_meni |    1 |

Ispravi me ako gresim. Mislim da je usporavanje izazvala prevelika kolicina podataka koje zahvatam sa upitom, kao sto si napomenuo. Tragom toga dosao sam do zakljucka da ogranicim period u intervalu -48 casova. Sa tim cu uvijek biti siguran da nece prolaziti kroz citavu bazu nego samo kroz posljednje unose.

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: Upit je napunio log slow queries

^{22.06.2009. u 18:33 - pre 180 meseci}

Citat:

bolje je ... nije koristio index ovde uopste .. sada je odlucio da koristi vijesti_dat_prikazi ... bolje ista nego nista .. :)

probaj da ga nateras da koristi i_comp

http://dev.mysql.com/doc/refman/5.1/en/index-hints.html

mozda bude bolje

Code:

SELECT
`vijesti`.vijesti_id,
`vijesti`.vijesti_meni,
`vijesti`.vijesti_naslov,
DATE_FORMAT( `vijesti`.vijesti_dat_prikazi, '%d.%m.%Y %H:%i') as datum,
DATE(`vijesti`.vijesti_dat_prikazi) as prikazi,
`vijesti`.vijesti_status,
`vijesti`.vijesti_broj,
`meni`.idmeni,
`meni`.ime,
`meni`.link
FROM vijesti `vijesti` force key i_comp, meni `meni`
WHERE (`vijesti`.vijesti_meni = `meni`.idmeni)
AND (`vijesti`.vijesti_meni <> 15)
AND (`vijesti`.vijesti_dat_prikazi <= NOW())
AND (`vijesti`.vijesti_status = 'Aktivna')
AND ( `vijesti`.vijesti_dat_prikazi >= DATE_SUB(NOW(), INTERVAL 48 HOUR))
ORDER BY prikazi DESC, `vijesti`.vijesti_broj DESC LIMIT 0, 15;

takodje mozes da napravis jos par indexa

alter table vijesti add key i_comp1 (vijesti_status,vijesti_meni,vijesti_dat_prikazi);
alter table vijesti add key i_comp2 (vijesti_dat_prikazi,vijesti_meni,vijesti_status);
alter table vijesti add key i_comp3 (vijesti_dat_prikazi,vijesti_status,vijesti_meni);

dakle sa razlicitim rasporedom kolona, pa pogledas koji ti najbolje radi (radis force prvog, pa drugog, pa treceg)

sve u svemu, osnovni problem koji ja ovako na brzaka vidim sa upitom je sto on realno vraca puno vrednosti .. to sto tebi treba samo prvih 15 je super ali on da bi ordario order by mora kroz sve njih da prodje ..

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

Odgovor na temu

luka357
Banja Luka

Član broj: 16848
Poruke: 34
*.nezavisne.com.

Profil

Re: Upit je napunio log slow queries

^{22.06.2009. u 21:34 - pre 180 meseci}

Upotrebom i_comp2 index upit se najbrze izvrsava. Taj index prihvatio je i sam MySQL.

Dodao sam jos u njega i "vijesti_broj" sto ga je dodatno ubrzalo.
Definitivno pala je odluka da se koristi upit:

Code:
SELECT
`vijesti`.vijesti_id,
`vijesti`.vijesti_meni,
`vijesti`.vijesti_naslov,
DATE_FORMAT( `vijesti`.vijesti_dat_prikazi, '%d.%m.%Y %H:%i') as datum,
DATE(`vijesti`.vijesti_dat_prikazi) as prikazi,
`vijesti`.vijesti_status,
`vijesti`.vijesti_broj,
`meni`.idmeni,
`meni`.ime,
`meni`.link
FROM vijesti `vijesti` USE INDEX (i_comp2) , meni `meni`
WHERE (`vijesti`.vijesti_meni = `meni`.idmeni)
AND (`vijesti`.vijesti_meni <> 15)
AND (`vijesti`.vijesti_dat_prikazi <= NOW())
AND (`vijesti`.vijesti_status = 'Aktivna')
AND ( `vijesti`.vijesti_dat_prikazi >= DATE_SUB(NOW(), INTERVAL 48 HOUR))
ORDER BY prikazi DESC, `vijesti`.vijesti_broj DESC LIMIT 0, 15;

Branko hvala puno na pomoci. Sad sam naucio jos nesto novo vezano za indexe i kako se ponasaju.

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: Upit je napunio log slow queries

^{22.06.2009. u 21:47 - pre 180 meseci}

imas dosta toga : http://dev.mysql.com/doc/refman/5.1/en/query-speed.html

generalno ima cudo pravila kada moze da se koristi koji index, kada moze da se koristi vise od jednog indexa i slicno ... mysql u jednom upitu nikad ne moze da koristi vise od 3 indexa a i to je retko (dakle ne moze uvek ni 3), zato je zgodno praviti kompozitne indexe. Takodje ima fora kod kompozitnih po redosledu u indexu sta kako moze da se koristi u upitu ... (turbo smor prica a sve ima da se procita ispod ove strane svrha posta) ..

sve u svemu, lepo da sad radi .. pobi ove nepotrebne indexe i ostavi samo taj (visak indexa usporava update/insert) .. takodje, uvek pre "testiranja" odradi optimize table (to nemoj olako da radis na production serveru posto traje a dok traje ne moz pisat po tabelama) ili analyze table ... (optimize na kraju sam odradi analyze). Analyze table postavi validne vrednosti za kardinalnost indexa te je optimizeru mnogo lakse da se odluci za kvalitetniji index. Ako si ti siguran koji index oces da koristis onda je zgodno da ga forceujes kako bi izbegao los izbor optimizera u slucaju losih podataka za kardinalnost

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

Odgovor na temu