Navigacija
Lista poslednjih: 16, 32, 64, 128 poruka.

Kako izbjeći Using temporary; Using filesort

[es] :: MySQL :: Kako izbjeći Using temporary; Using filesort

[ Pregleda: 5193 | Odgovora: 14 ] > FB > Twit

Postavi temu Odgovori

Autor

Pretraga teme: Traži
Markiranje Štampanje RSS

Tyler Durden
Tyler Durden
Beograd

Član broj: 4312
Poruke: 3379
*.home.otenet.gr.



+1365 Profil

icon Kako izbjeći Using temporary; Using filesort18.05.2015. u 16:29 - pre 107 meseci
Imam poveći upit sa deset join-ova (je li to mnogo?) i na kraju group by koji mi "prouzrokuje" Using temporary; Using filesort
Pošto ne mogu da prikažem čitav kod i upit, evo samo taj dio zbog kojeg se javlja filesort i ekipa. Da li neko može iz ovoga da mi kratko objasni zašto MySQL ne koristi indekse.


Code:
select sql_no_cache article_metadata.id from article_metadata inner JOIN city ON article_metadata.city_id = city.id where `city_name` = 'beograd' group by article_metadata.id

Code:
| Field     | Type                 | Null | Key | Default | Extra          |
+-----------+----------------------+------+-----+---------+----------------+
| id        | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| city_name | varchar(45)          | NO   |     | NULL    |                |
+-----------+----------------------+------+-----+---------+----------------+

Code:
+------------------------+----------------------+------+-----+---------+----------------+
| Field                  | Type                 | Null | Key | Default | Extra          |
+------------------------+----------------------+------+-----+---------+----------------+
| id                     | int(10) unsigned     | NO   | PRI | NULL    | auto_increment |
| city_id                | smallint(5) unsigned | NO   | MUL | NULL    |                |
+------------------------+----------------------+------+-----+---------+----------------+

Beneath civilization's fragile crust, cold chaos churns...
 
Odgovor na temu

bogdan.kecman
Bogdan Kecman
"specialist"
Oracle
srbistan

Član broj: 201406
Poruke: 15887
*.dynamic.sbb.rs.

Sajt: mysql.rs


+2377 Profil

icon Re: Kako izbjeći Using temporary; Using filesort18.05.2015. u 16:40 - pre 107 meseci
Citat:
Tyler Durden: Imam poveći upit sa deset join-ova (je li to mnogo?)


da, mnogo je, sve preko 5 dovodi do toga da je optimizeru gotovo nemoguce da dodje do optimalnog resenja posto je broj kombinacija uzasno velik i provera koliko bi koja kombinacija kostala da bi se doslo do idealne traje uzasno puno

Citat:
Tyler Durden:
i na kraju group by koji mi "prouzrokuje" Using temporary; Using filesort
Pošto ne mogu da prikažem čitav kod i upit, evo samo taj dio zbog kojeg se javlja filesort i ekipa. Da li neko može iz ovoga da mi kratko objasni zašto MySQL ne koristi indekse.


Code:
select sql_no_cache article_metadata.id from article_metadata inner JOIN city ON article_metadata.city_id = city.id where `city_name` = 'beograd' group by article_metadata.id

Code:
| Field     | Type                 | Null | Key | Default | Extra          |
+-----------+----------------------+------+-----+---------+----------------+
| id        | smallint(5) unsigned | NO   | PRI | NULL    | auto_increment |
| city_name | varchar(45)          | NO   |     | NULL    |                |
+-----------+----------------------+------+-----+---------+----------------+

Code:
+------------------------+----------------------+------+-----+---------+----------------+
| Field                  | Type                 | Null | Key | Default | Extra          |
+------------------------+----------------------+------+-----+---------+----------------+
| id                     | int(10) unsigned     | NO   | PRI | NULL    | auto_increment |
| city_id                | smallint(5) unsigned | NO   | MUL | NULL    |                |
+------------------------+----------------------+------+-----+---------+----------------+


super ali bez
- strukture tabele
- explain-a koji radi ono sto ne zelis
tesko da ti neko realno pomogne

sta ti kaze explain za ovo sto si prikazao? ja bi rekao da koristi kljuceve za ovaj join tako da

group by ce uvek da pravi temp tabelu (dal u ramu ili na disku zavisi od dosta toga), filesort ti je verovatno zbog group by, prethodnu temp tabelu koju napravi zbog join-a sortira da bi brze radio group by .. opet napamet bez tabele i upita tesko je reci posto kao sto rekoh ovaj upit bi trebalo da radi ok

dalje, koji mysql? za ovakav upit moras da teras min 5.6 ili 5.7 posto ce optimizer na 5.0/5.1 da se udavi skroz sa tim 10way join-om

dalje, najlaksi nacin da ubrzas optimizer i dodjes do optimalnog exec plan-a je da forsiras kljuceve koji hoces da se koriste: https://dev.mysql.com/doc/refman/5.6/en/index-hints.html

 
Odgovor na temu

Tyler Durden
Tyler Durden
Beograd

Član broj: 4312
Poruke: 3379
*.home.otenet.gr.



+1365 Profil

icon Re: Kako izbjeći Using temporary; Using filesort18.05.2015. u 19:37 - pre 107 meseci
Evo explain-a
Code:
| id | select_type | table            | type | possible_keys                                                                                | key            | key_len | ref         | rows | Extra                                        |
+----+-------------+------------------+------+----------------------------------------------------------------------------------------------+----------------+---------+-------------+------+----------------------------------------------+
|  1 | SIMPLE      | city             | ALL  | PRIMARY                                                                                      | NULL           | NULL    | NULL        |   90 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | article_metadata | ref  | PRIMARY,idx_fk_city_id | idx_fk_city_id | 2       | nii.city.id |   13 | Using index                                  |
+----+-------------+------------------+------+----------------------------------------------------------------------------------------------+----------------+---------+-------------+------+----------------------------------------------+


MySQL je 5.6.24 ali ono sto me vec smorilo jeste sto kazes da je ovo mnogo JOIN-ova.
Samo nisam siguran kako da to izbjegnem posto je baza relativno velika, i potpuno normalizovana. A ovaj upit mi treba za neku generalnu i opstu pretragu pa mi zato treba pomalo podataka sa svake strane :(
Beneath civilization's fragile crust, cold chaos churns...
 
Odgovor na temu

Tyler Durden
Tyler Durden
Beograd

Član broj: 4312
Poruke: 3379
*.home.otenet.gr.



+1365 Profil

icon Re: Kako izbjeći Using temporary; Using filesort18.05.2015. u 19:41 - pre 107 meseci
Da li bi kreiranje view-a pomoglo u ovom slucaju?
Sto se tice previse join-ova? Ili cak generalno da li bi nesto pomoglo.
Beneath civilization's fragile crust, cold chaos churns...
 
Odgovor na temu

bogdan.kecman
Bogdan Kecman
"specialist"
Oracle
srbistan

Član broj: 201406
Poruke: 15887
*.dynamic.sbb.rs.

Sajt: mysql.rs


+2377 Profil

icon Re: Kako izbjeći Using temporary; Using filesort18.05.2015. u 20:22 - pre 107 meseci
Citat:
Tyler Durden: Da li bi kreiranje view-a pomoglo u ovom slucaju?
Sto se tice previse join-ova? Ili cak generalno da li bi nesto pomoglo.


jok. view je prilicno beskorisna stvar sto se tice optimizacije

Citat:
Tyler Durden: Evo explain-a
MySQL je 5.6.24 ali ono sto me vec smorilo jeste sto kazes da je ovo mnogo JOIN-ova.


ok, bar teras 5.6 ..

naravno da je mnogo joinova, kako 10 joinova nije mnogo?! razmisli samo koliko je to kombinacija za optimizer da pronadje valjan exec path



Citat:
Tyler Durden:
Samo nisam siguran kako da to izbjegnem posto je baza relativno velika, i potpuno normalizovana. A ovaj upit mi treba za neku generalnu i opstu pretragu pa mi zato treba pomalo podataka sa svake strane :(


pa, stored procedura, napravis sam optimalno temp tabele sa kljucevima iz kojih izvadis podatke


sto se ovog upita tice on ti koristi article_metadata.idx_fk_city_id index.
za tabelu city imas "where `city_name` = 'beograd'" a nemas index na city_name tako da je nema isplativ kljuc tu da koristi tako da je tu where/temporary a onda posto ti treba kljuc po id na toj temp tabeli koju pravi sa where radi filesort da bi mnozio sa article_metadata ..
za pocetak dodas kljuc na city_name (btw i nije ti nesto normalizovana ako imas upit koji pita city_name, waljda taj where treba da bude where city.id = nekiid ?! ovako je normalizacija tu beskorisna)

obavezno kad dodas taj kljuc odradi explain extended i posle toga show warnings (imas dodatne podatke u warnings)
 
Odgovor na temu

bogdan.kecman
Bogdan Kecman
"specialist"
Oracle
srbistan

Član broj: 201406
Poruke: 15887
*.com
Via: [es] mailing liste

Sajt: mysql.rs


+2377 Profil

icon Re: Kako izbjeći Using temporary; Using filesort18.05.2015. u 20:29 - pre 107 meseci
btw ako imas vremena na neku dev masinu turi mysql 5.7 uvuci tu bazu i
uradi explain vidi koliko se razlikuje posto 5.7 ima dosta novih
mogucnosti u optimizer-u kada su join-ovi i group by u pitanju
 
Odgovor na temu

Tyler Durden
Tyler Durden
Beograd

Član broj: 4312
Poruke: 3379
*.home.otenet.gr.



+1365 Profil

icon Re: Kako izbjeći Using temporary; Using filesort18.05.2015. u 20:33 - pre 107 meseci
Jeste normalizovana :)
I sa city.id = nekiid je bila potpuno ista situacija, što me dodatno zbunilo.
Sad ću da probam ove tvoje savjete, a u međuvremenu sam ja nešto bunario po internetu i naletio sam na optimizer_search_depth. Mijenjao sam vrednosti i za vrijednost 10 dobio sam Using where; Using index tamo gdje je bio filesort :-)
E sad, upit se i nije nešto drastično ubrzao. Sa filesort upit traje 0.58s do 0.60s svaki put, a sa ovim čačkanjem izvršava se oko 0.50s.

Bonus pitanje, šta znači NULL u koloni Extra?
Beneath civilization's fragile crust, cold chaos churns...
 
Odgovor na temu

bogdan.kecman
Bogdan Kecman
"specialist"
Oracle
srbistan

Član broj: 201406
Poruke: 15887
*.dynamic.sbb.rs.

Sajt: mysql.rs


+2377 Profil

icon Re: Kako izbjeći Using temporary; Using filesort18.05.2015. u 20:48 - pre 107 meseci
dje ba vidis null u extra?

elem, obrati paznju da imas 5.6!!!

upali profiling
odradi set profiling=1; select kukulele join join join join join .... ; select * from i_s.profiling .. i gledaj gde se trosi vreme ... (ili ako te mrzi da citas iz baze, samo "show profile

sta je fora, optimizer_search_depth je uzasno opasna varijabla, ako pustis, posebno za 10way join i gore, optimizer da divlja duboko moze da ti se desi da ti 99% vremena trajanja upita ode na optimizer!!!! pricao sam o tome na google day ne znam dal si slusao ..

elem, profiling ti je tu da pomogne gde se gubi vreme, dalje imas 5.6, u odnosu na pateticni explain koji ti generise klasican explain / explain extended mozes da dobijes json kod explain-a mnooogo detaljnije .. sad ovako json je ruzno citat ali uzmi workbench (da, znam da je uzasno bagovit, teraj ga na windozi posto na lindzi umire ko zvecka jbg mono na lindzi je jos uvek patetican i to je najveci izvor bagova, ali on je jedini koji za sada ume da cita taj json kako valja) i iz njega odradi explain (bese desni klik na upit pa explain) on povuce json i isparsira ga i prikaze ti grafikon koji dosta dobro moze da se razume sta se desava uzimajuci u obzir podatke koje nemas u klasicnom explain izlazu ... to je na 5.6 i 5.7 dosta jaka alatka!!!

finalno, osim eventualnog dodavanja nekog indexa, ja ti ne savetujem nikako da dizes optimizer_search_depth vec da hintujes (ili bolje forsiras) indexe tako da ti upit radi najbrze moguce .. na taj nacin ne gubis vreme u optimizeru, nemas razliku u exec plan-u zavisno od toga koliko je up-to-date table stats (a znas da na innodb ne moze stat da bude 100% up-to-date kao na myisam) i tako dalje i tako dalje, a kapiram da ti umes da kazes koji indexi treba da se forsiraju za koji deo upita .. (u najgorem slucaju probaj razne kombinacije i prati promenu explain-a i promenu brzine upita pa ce nabodes iz 10tog pokusaja :D )

ono sto obavezno treba da proveris je http://dev.mysql.com/doc/refma...s.html#sysvar_optimizer_switch ( SELECT @@optimizer_switch\G )
bitne stavke za tebe su bka, engine_condition_pushdown i materialization


 
Odgovor na temu

Tyler Durden
Tyler Durden
Beograd

Član broj: 4312
Poruke: 3379
*.home.otenet.gr.



+1365 Profil

icon Re: Kako izbjeći Using temporary; Using filesort18.05.2015. u 21:35 - pre 107 meseci
Napisao sam gore da je ovo samo "isjecak" od veceg explain-a :)
I NULL dobijam za dva reda...


Ovo su rezultati profilinga
Code:
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000256 |
| checking permissions | 0.000008 |
| checking permissions | 0.000003 |
| checking permissions | 0.000002 |
| checking permissions | 0.000002 |
| checking permissions | 0.000002 |
| checking permissions | 0.000002 |
| checking permissions | 0.000002 |
| checking permissions | 0.000002 |
| checking permissions | 0.000002 |
| checking permissions | 0.000002 |
| checking permissions | 0.000005 |
| Opening tables       | 0.000061 |
| init                 | 0.000139 |
| System lock          | 0.000023 |
| optimizing           | 0.000060 |
| statistics           | 0.002512 |
| preparing            | 0.000059 |
| Creating tmp table   | 0.000053 |
| Sorting result       | 0.000005 |
| executing            | 0.000004 |
| Sending data         | 0.580217 |
| Creating sort index  | 0.000222 |
| end                  | 0.000005 |
| removing tmp table   | 0.000030 |
| end                  | 0.000005 |
| query end            | 0.000007 |
| closing tables       | 0.000025 |
| freeing items        | 0.000028 |
| cleaning up          | 0.000018 |
+----------------------+----------+


Citam sad da je sending data u stvari samo izvrsavanje upita. Moze li taj segment da se razbije na dodatne korake?


Sto se tice hintovanja indeksa, tu mu kulira nacisto. Cak i sa force index. Je l' to znaci da nema sanse da ga natjeram da koristi taj indeks?
Zasto kad explain u koloni possible_keys prijavljuje PRIMARY?
Beneath civilization's fragile crust, cold chaos churns...
 
Odgovor na temu

Tyler Durden
Tyler Durden
Beograd

Član broj: 4312
Poruke: 3379
*.home.otenet.gr.



+1365 Profil

icon Re: Kako izbjeći Using temporary; Using filesort18.05.2015. u 21:47 - pre 107 meseci
update sto se tice koriscenja indeksa
sad kad promjenim where u city.id = nekiid onda koristi primarni kljuc, ali upit traje identicno dugo, plus u extra koloni i dalje stoji Using index; Using temporary; Using filesort
Beneath civilization's fragile crust, cold chaos churns...
 
Odgovor na temu

bogdan.kecman
Bogdan Kecman
"specialist"
Oracle
srbistan

Član broj: 201406
Poruke: 15887
*.dynamic.sbb.rs.

Sajt: mysql.rs


+2377 Profil

icon Re: Kako izbjeći Using temporary; Using filesort18.05.2015. u 22:04 - pre 107 meseci
sending data je finalni prolaz kroz datu znaci sve temp tabele su odradjene u executing i sending data prolazi sada kroz taj finalni rezultat i radi "filtriranje" i slanje toga (kako koji red nadje posalje klijentu, zato je sending data). sta ti kaze show warnings posle explain-a ?

ako te force index kulira znaci da ne moze da ga upotrebi (zasto, nemam pojma bez tabela i upita)

jel mzo bar ceo explain da bacis ovde + show warnings, xxxx-uj imena tabela ako je frka :D

inace ono sto se meni dojmi ovako napamet je da mysql skocka tu finalnu temp tabelu brzo ali je doticna povelika i naravno bez indexa i onda prolaz kroz nju ide letnji dan do podne.. obicno resenje koje ide tu (ja ga ne volem uopste al je cesto i radi) je da ti napravis sam tu temp tabelu, dakle ti rucno create pa insert select a ta tabela ima index i ti onda finalno select iz te temp tabele i finalno drop tako da imas full kontrolu i max brzinu .. naravno odrzavanje postaje za klasu kompleksnije al jbg .. drugo resenje je naravno denormalizacije koristenjem materialized view, naravno mysql ne podrzava materialized view tako da se to pravi rucno tako sto imas normalnu tabelu umesto view i trigere u tabelama okolo koji updateuju tu tabelu on change tako da odrzavas tu tabelu up2date i na taj nacin imas sitno sporiji update/delete/insert iz tih ostalih tabela ali ti ovi spori upiti rade mega brzo jer imas denormalizovanu pripremljenu datu spremnu za citanje ... i to je isto cest nacin za resavanje, isto nije idealan al ..
 
Odgovor na temu

Tyler Durden
Tyler Durden
Beograd

Član broj: 4312
Poruke: 3379
*.home.otenet.gr.



+1365 Profil

icon Re: Kako izbjeći Using temporary; Using filesort19.05.2015. u 20:19 - pre 107 meseci
Moram da priznam kako sam dosta gluposti u pravljenju ovog upita napravio na koje sam sada naišao nakon stote iteracije :-)
Prvo sam krenuo da pravim upit za neki naaaajopštiji slučaj, koji se skoro i neće koristiti praktično, pa sam zato imao prilično podivljao upit koji skoro da nije imao nikakve uslove. Onda sam se uhvatio filesort i temporary iako to nije bio razlog sporog izvršavanja.
Kada sam napokon krenuo ispočetka kako treba (jedan join pa ; drugi join pa ;) lako sam došao do toga gdje se upit u stvari zaglavi.
Izbacio sam jednu tabelu koja nije bila potrebna za tu fazu prikaza podataka i izvršavanje se spustilo na < 0,20s što je odlično.
Čak sam poslije dodao jedan podupit kao join i sve radi odlično. Kida MySQL :-)
Malo sam pojednostavio sad kako sam sredio stvari, nije baš tako prosto bilo niti sam baš sve zabrljao, ali definitivno sam pogrešno pristupio ovome.

Inače, sad prvi put od tebe čujem za materialized view pa sam malo uzeo da čitam, i sviđa mi se ideja, pogotovo što bi fino poslužila u ovom mom slučaju bez obzira na poboljšanja koja su iskrsla.
Kapiram da to može i pješke da se emulira baš tako kako si ti opisao, ali ipak je native najbolja varijanta, pa me zanima ima li u planu tako nešto za buduće verzije MySQL-a?
Beneath civilization's fragile crust, cold chaos churns...
 
Odgovor na temu

bogdan.kecman
Bogdan Kecman
"specialist"
Oracle
srbistan

Član broj: 201406
Poruke: 15887
*.dynamic.sbb.rs.

Sajt: mysql.rs


+2377 Profil

icon Re: Kako izbjeći Using temporary; Using filesort19.05.2015. u 23:00 - pre 107 meseci
to za upit, zeznuto je kad ne mozes da ostavis ceo upit pa se ne vidi problem znas i sam, bitno da imas ideju kako da dodjes do resenja

sto se tice materialized, trenutno ne postoji worklog za materialized views na mysql-u i koliko ja znam nase developere nece ga ni biti osim ako neki ozbiljan klijent ne pozeli tako nesto, a poznajuci nase klijente ne verujem da ce to da se desi ikad ...

ono sto imamo od materialized price je u 5.6 i 5.7 imamo materialized temporary tables ... to je posebna vrsta subquery optimizacije (dakle ne join nego subquery) gde u slucaju da moze da se detektuje da se neki temp table koji se generise u svakom prolazu kroz upit ne menja optimizer ce napraviti staticno tu tabelu samo jednom... bas sam skoro radio na nekom bagu vezano za to (nije bug klijent samo nije razumeo kako to radi): https://bugs.mysql.com/76685
dokumentacija: https://dev.mysql.com/doc/refm.../en/subquery-optimization.html

 
Odgovor na temu

Tyler Durden
Tyler Durden
Beograd

Član broj: 4312
Poruke: 3379
*.dynamic.sbb.rs.



+1365 Profil

icon Re: Kako izbjeći Using temporary; Using filesort29.10.2015. u 11:51 - pre 102 meseci
bogdane, sta znaci kad u show profile; imam gomilu redova sending data i executing?
Code:

+---------------------+----------+
| Status              | Duration |
+---------------------+----------+
| executing           | 0.000004 |
| Sending data        | 0.014858 |
| executing           | 0.000005 |
| Sending data        | 0.011187 |
| executing           | 0.000006 |
| Sending data        | 0.000501 |
| executing           | 0.000004 |
| Sending data        | 0.002169 |
| executing           | 0.000004 |
| Sending data        | 0.002725 |
| executing           | 0.000010 |
| Sending data        | 0.000307 |
| executing           | 0.000006 |
.......
| Sending data        | 0.001326 |
| executing           | 0.000004 |
| Sending data        | 0.005117 |
| Creating sort index | 0.000537 |
| end                 | 0.000008 |
| removing tmp table  | 0.000491 |
| end                 | 0.000010 |
| query end           | 0.000007 |
| closing tables      | 0.000003 |
| removing tmp table  | 0.000191 |
| closing tables      | 0.000030 |
| freeing items       | 0.000038 |
| cleaning up         | 0.000158 |

Beneath civilization's fragile crust, cold chaos churns...
 
Odgovor na temu

bogdan.kecman
Bogdan Kecman
"specialist"
Oracle
srbistan

Član broj: 201406
Poruke: 15887
*.dynamic.sbb.rs.

Sajt: mysql.rs


+2377 Profil

icon Re: Kako izbjeći Using temporary; Using filesort29.10.2015. u 12:53 - pre 102 meseci
da li si limitirao i_s.profiling sa id-em upita on tu cuva za sve upite od kad si startovao profiling? deluje da je to vise upita spojenih .. ako jesi stavio where onda moze da bude mozda da imas neki subselect koji se vrti u petlji za neki join (losa ideja)
 
Odgovor na temu

[es] :: MySQL :: Kako izbjeći Using temporary; Using filesort

[ Pregleda: 5193 | Odgovora: 14 ] > FB > Twit

Postavi temu Odgovori

Navigacija
Lista poslednjih: 16, 32, 64, 128 poruka.