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

Spor SELECT upit

[es] :: MySQL :: Spor SELECT upit

[ Pregleda: 2825 | Odgovora: 15 ] > FB > Twit

Postavi temu Odgovori

Autor

Pretraga teme: Traži
Markiranje Štampanje RSS

maroon984

Član broj: 284654
Poruke: 57
*.teol.net.



+1 Profil

icon Spor SELECT upit03.09.2014. u 08:43 - pre 116 meseci
Pozdrav,
Imam problem oko SELECT nad velikom tabelom.

MySQL je 5.5.24
Tabela:

Code:
CREATE TABLE `korisnik_log` (
  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `gtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `korisnik_id` mediumint(8) unsigned NOT NULL,
  PRIMARY KEY (`ID`, `gtime`)
) ENGINE=InnoDB AUTO_INCREMENT=155700486 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT

U tabelu se dnevno upiše 1.5 miliona zapisa.

Pretraga je tipa :

Code:
SELECT ID, gtime FROM korisnik_log WHERE gtime BETWEEN '2014-06-04' AND '2014-06-07' AND korisnik_id=34567;


Ovaj upit treba da mi vrati oko 1000 zapisa, a izvršavavanje traje po 10tak sec.

Da li postoji način da se ovo ubza?
Radio sam i particionisanje po vremenu, ali ne pomaže baš:

Code:
CREATE TABLE `korisnik_log` (
  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `gtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `korisnik_id` mediumint(8) unsigned NOT NULL,
  PRIMARY KEY (`ID`, `gtime`)
)
 PARTITION BY RANGE (unix_timestamp(gtime))
(PARTITION p0 VALUES LESS THAN (unix_timestamp('2014-01-01')) ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (unix_timestamp('2014-02-01')) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN (unix_timestamp('2014-03-01')) ENGINE = InnoDB,
 PARTITION p3 VALUES LESS THAN (unix_timestamp('2014-04-01')) ENGINE = InnoDB,
 PARTITION p4 VALUES LESS THAN (unix_timestamp('2014-05-01')) ENGINE = InnoDB




[Ovu poruku je menjao maroon984 dana 03.09.2014. u 11:38 GMT+1]
 
Odgovor na temu

nkrgovic
Nikola Krgović
Beograd

Član broj: 3534
Poruke: 2807

ICQ: 49345867
Sajt: https://www.twinstarsyste..


+655 Profil

icon Re: Spor SELECT upit03.09.2014. u 08:50 - pre 116 meseci
Nije mi jasno zasto imas ID, time kao primarni, ali OK. Zasto ne dodas index po korisnik_id? Pogledaj i explain, ne znam napamet da li ce ti taj primarni index posluziti za ove datumske operacije. BTW, koliki ti je tablespace, a koliki je innodb buffer?
Please do not feed the Trolls!

Blasphemy? How can I blaspheme? I'm a god!'
 
Odgovor na temu

djoka_l
Beograd

Član broj: 56075
Poruke: 3445

Jabber: djoka_l


+1462 Profil

icon Re: Spor SELECT upit03.09.2014. u 08:59 - pre 116 meseci
Dobro si primetio da je izbor primarnog ključa pogrešan.
Upit ne koristi ključ nego radi FULL TABLE SCAN, zato što ne može da iskoristi gtime iz primarnog ključa.
Trebalo bi da primarni ključ bude samo po id, a da doda indeks po korisnik_id i gtime.
 
Odgovor na temu

nkrgovic
Nikola Krgović
Beograd

Član broj: 3534
Poruke: 2807

ICQ: 49345867
Sajt: https://www.twinstarsyste..


+655 Profil

icon Re: Spor SELECT upit03.09.2014. u 09:35 - pre 116 meseci
Cuj, mene mrzi da pravim test schema da bi ovo njegovo uradio.... :) Zato i hocu da njega nateram da opali explain - pa nek nauci lepo sam :D Sledeci put ce brze resiti problem.

P.S. Mozda i nije POGRESAN. Pazi, on je NOT_NULL i AUTOINCREMENT, ali nije UNIQUE. Mozes rucno da ga izmenis da se pojavi duplikat. Samim tim, mozda postoji razlog zasto je on izabrao primarni kljuc da bude kompozitan. Zelim reci: Nikad ne reci "izbor je pogresan" dok ne znas ZASTO je takav izbor napravljen. :) To sto bi ja stavio da je ID primarni kljuc ne znaci da on mozda ne mora da ima ovakav. Zato ne volim da dajem takve pausalne ocene....
Please do not feed the Trolls!

Blasphemy? How can I blaspheme? I'm a god!'
 
Odgovor na temu

maroon984

Član broj: 284654
Poruke: 57
*.teol.net.



+1 Profil

icon Re: Spor SELECT upit03.09.2014. u 10:16 - pre 116 meseci
Kolonu gtime sam stavio u primarni ključ zbog particionisanja po toj koloni.
MySQL doc kaže:
Citat:
...every unique key on the table must use every column in the table's partitioning expression...


Kolona korisnik_id je key, samo sam skresao tabelu (ima jos poprilicno kolona), pa sam zaboravio ubaciti u izmjenjeni SQL.

Code:
PRIMARY KEY (`ID`, `gtime`),
KEY `korisnik_idx` (`korisnik_id`)

Prije particionisanja bilo je:

Code:
PRIMARY KEY (`ID`),
KEY `gtime_idx` (`gtime`),
KEY `korisnik_idx` (`korisnik_id`)


ali je tada bilo jos sporije.
 
Odgovor na temu

djoka_l
Beograd

Član broj: 56075
Poruke: 3445

Jabber: djoka_l


+1462 Profil

icon Re: Spor SELECT upit03.09.2014. u 10:30 - pre 116 meseci
OK, ali bez explaina teško može da se bilo šta zaključi.
Recimo, ne znam da li upit ide po gtime ili korisnik_id.
U upitu koji si postavio, ako je tačno da imaš 1.5 miliona zapisa dnevno, upit prolazi kroz 6 milona zapisa da bi našao log nekog korisnika (ako koristi indeks po gtime).
Ako koristiti korisnik_id, prolazi kroz sve logove tog korisnika, počevši od Velikog praska, umesto da se ograniči na 4 dana.
Ne znam koliko imaš korisnika, ali ključ (gtime, korisnik_id) ili (korisnik_id, gtime) bi bio mnogo restriktivniji.
 
Odgovor na temu

maroon984

Član broj: 284654
Poruke: 57
*.teol.net.



+1 Profil

icon Re: Spor SELECT upit03.09.2014. u 10:32 - pre 116 meseci
Na produkcionom serveru, tabela nije particionisana (to jos testiram), pa je explain:

Code:
mysql> explain select * from korisnik_log where gtime between '2014-08-05' and '2014-08-08' and korisnik_id=5444;
+----+-------------+----------------+------+------------------------+--------------+---------+-------+--------+-------------+
| id | select_type | table          | type | possible_keys          | key          | key_len | ref   | rows   | Extra       |
+----+-------------+----------------+------+------------------------+--------------+---------+-------+--------+-------------+
|  1 | SIMPLE      | korisnik_log   | ref  | gtime_idx,korisnik_idx | korisnik_idx | 3       | const | 352800 | Using where |
+----+-------------+----------------+------+------------------------+--------------+---------+-------+--------+-------------+
1 row in set (0.00 sec)



Znači ovdje je

Code:
CREATE TABLE `korisnik_log` (
  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `gtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `korisnik_id` mediumint(8) unsigned NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `gtime_idx` (`gtime`),
  KEY `korisnik_idx` (`korisnik_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT


Code:
innodb_buffer_pool_size = 18G

 
Odgovor na temu

djoka_l
Beograd

Član broj: 56075
Poruke: 3445

Jabber: djoka_l


+1462 Profil

icon Re: Spor SELECT upit03.09.2014. u 11:41 - pre 116 meseci
OK, i ja sam izveo sličnu računicu u glavi: ako za 4 dana dobiješ 1,000 log zapisa za jednog korisnika, to znači da jedan korisnik napravi godišnje oko 100,000 zapisa, pa onda, ako imaš istoriju poslednje 3 godine, prolazi se kroz 300,000 zapisa.
To se slaže sa planom, gde se vidi da se koristi korisnik_id i da se dohvata oko 353,000 zapisa.
Da imaš indeks (korisnik_id, gtime) dohvatao bi samo 1,000 zapisa.
 
Odgovor na temu

maroon984

Član broj: 284654
Poruke: 57
*.teol.net.



+1 Profil

icon Re: Spor SELECT upit03.09.2014. u 12:33 - pre 116 meseci
Znači da izbacim ove indekse, i ubacim jedan (korisnik_id, gtime)?

Da li je bitan raspored kolona u kompozitnom indeksu? (korisnik_id, gtime) ili (gtime, korisnik_id)?

 
Odgovor na temu

djoka_l
Beograd

Član broj: 56075
Poruke: 3445

Jabber: djoka_l


+1462 Profil

icon Re: Spor SELECT upit03.09.2014. u 12:44 - pre 116 meseci
Na osnovu jednog upita ne mogu se planirati indeksi.
U konkretnom slučaju, za ovaj jedan upit dovoljno je da postoji samo indeks nad korisnik_id i gtime bez obzira na redosled.
Ako se pored ovog upita pojavljuje i upit gde se izlistaju svi logovi za neki period (za sve korisnike), onda gtime treba da ide prvo.

Pravilo je da se indeks može iskoristiti ako je u where uslovu PREFIKS onoga što je u indeksu. Dakle, ako je indeks sastavljen od kolona (a,b,c,d) upiti koji postavljaju uslov po a, a i b, a i b i c, ili a i b i c id imaju koristi od tog indeksa (naravno, samo uslovi koji mogu da iskoriste index, recimo a like 'nesto%' može da iskoristi indeks, a uslov a like '%nesto' ne može).

Dopuna:
Ako se odlučiš za indeks (korisnik_id, gtime), onda ti više nije potreban indeks (korisnik_id), a možeš da zadržiš indeks (gtime).
Ako se odlučiš za (gtime, korisnik_id), onda dropni po (gtime), a ostavi (korisnik_id).
 
Odgovor na temu

maroon984

Član broj: 284654
Poruke: 57
*.teol.net.



+1 Profil

icon Re: Spor SELECT upit04.09.2014. u 08:34 - pre 116 meseci
Kada bih postavio indekse na taj način, da li mi particionisanje igra ikakvu bitnu ulogu?
 
Odgovor na temu

djoka_l
Beograd

Član broj: 56075
Poruke: 3445

Jabber: djoka_l


+1462 Profil

icon Re: Spor SELECT upit04.09.2014. u 09:35 - pre 116 meseci
Nisam stručnjak za particionaisanje na MySQL, ali iz mog iskustva sa Oracle, ne postoji "magičan" mehanizam koji rešava probleme nastale slabim dizajnom baze, neoptimalne upite i loše odabrane indekse.

DBA pravilo je da 95% tjuninga dobijaš pažljivom optimizacijom upita, dizajnom, indeksima, a 5% tako što se zezaš sa opcijama baze i nekakvim finim podešavanjima koje zna samo 3 gurua na svetu...

Imaš bazu, pa probaj. Sa indeksom koji sam ti dao, trebalo bi da ti problematičan upit padne na ispod sekunde.
Particionisanje može ponekad da pomogne, ali je mnogo korisnije za neke druge stvari...
 
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: Spor SELECT upit04.09.2014. u 09:55 - pre 116 meseci
- particionisanje po datumu ce da pomogne za log tabelu, posebno ako su uvek upiti unutar jedne particije
- obavezni key za korisnik_id
- gde je explain sa particionisane tabele? bez explain-a je sumanuto komentarisati
- kljuc (gtime, korisnik_id) bi ovde bio losa ideja, treba ti kljuc (korisnik_id, gtime)

pitanje - cemu ti sluzi ID? cisto da bi imao neki pk ili to ikad za ista koristis? za log tabelu je to prilican visak koji ne sluzi nicemu, razmisli da li ti treba uopste, ja bi pre koristio (korisnik_id, gtime) kao pk posebno ako ti se upiti zasnivaju na eq korisnik_id + range gtime
 
Odgovor na temu

maroon984

Član broj: 284654
Poruke: 57
*.teol.net.



+1 Profil

icon Re: Spor SELECT upit04.09.2014. u 12:45 - pre 116 meseci
ID sam stavio kao pk zato sto postoji mogucnost da dobijem za jednog korisnika par istih vremena, tj. kombinacija (korisnik_id, gtime) nije jedinstvena.
Da bih particionisao po gtime, treba i njega da stavim u pk, pa bi onda indeksi bili:

Code:
  PRIMARY KEY (`ID`, `gtime`),
  KEY `korisnik_idx` (`korisnik_id`),
  KEY `korisnik_gtime_idx` (`korisnik_id`, `gtime`)

 
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: Spor SELECT upit04.09.2014. u 13:47 - pre 116 meseci
onda taj ID maknes kao obican unique key, ne mora da bude primary

dalje

KEY `korisnik_idx` (`korisnik_id`),
KEY `korisnik_gtime_idx` (`korisnik_id`, `gtime`)

key korisnik_idx je ovde beskoristan jer vec imas key korisnik_id, nesto .. mysql uvek moze da koristi samo prvi clan kljuca ako mu treba tako da ti je samo kljuc po korisnik_idx redundandan i samo trosi vreme pri updateu i resurse na disku i u ramu .. potpuno beskoristan

dalje

ako ti se desava da ti korisnik, vreme nije unique znaci da ti logovanje nije bas idealno .. to mi govori da ti svaku akciju korisnika logujes .. to je lepo ali smara na kub ... mnogo ces manje da smaras bazu ako imas neki bafer gde grupises akcije korisnika i onda ih utuces u bazu jednom u minuti ili jednom u sekundi .. zavisi koliko brzo i koliko akcija imas .. no to obicno zahteva redizajn cele aplikacije tako da od toga verovatno nema nista
 
Odgovor na temu

farmaceut
Apoteka
Banja Luka

Član broj: 182739
Poruke: 55
62.68.101.*



+30 Profil

icon Re: Spor SELECT upit05.09.2014. u 19:20 - pre 116 meseci
Eksperimentisi malo. Np., pokusaj razbiti problem u sitnije djelove i napravi particije po HASH od "korisnik_id", odmah neki veci broj particija. Pazi na podesavanja max broja broj otvorenih fajlova uz puno particija. Takodje, za 1.5M upisa dnevno, ako uzmes da imas prosjecno 12 "aktivnih" sati imas oko 35 upisa po sekundi. To je prilicno I/O, pa nemoj pretjerati odmah.

Takodje, da li ti trebaju sve kolone (SELECT * ...) Ako su ti dovoljni samo indeksirani podaci, to moze ici puno brze np.:
SELECT korisnik_id, gtime

Ako imas 1.5 M dnevno redova, to je 500M+ redova godisnje... treba li toliki log da bude "live"?
 
Odgovor na temu

[es] :: MySQL :: Spor SELECT upit

[ Pregleda: 2825 | Odgovora: 15 ] > FB > Twit

Postavi temu Odgovori

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