Różny czas wykonywania zapytań na tej samej tabeli, Dwie takie same tabele 160tyś i 260tyś - znacząco różny czas wykonywan Opcje

[Radek_1]

Mam dwie takie same tabelki w różnych bazach (ale ten sam serwer):

[SQL] pobierz, plaintext 
CREATE TABLE IF NOT EXISTS `baza` (
  `UID` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) CHARACTER SET latin1 NOT NULL,
  `lvl` int(3) DEFAULT NULL,
  `voc` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
  `guild` varchar(30) DEFAULT NULL,
  `sex` varchar(7) DEFAULT NULL,
  `world` varchar(20) NOT NULL,
  `joined` int(10) NOT NULL,
  `lastlogin` int(10) DEFAULT NULL,
  `created_acc` int(10) DEFAULT NULL,
  `city` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`UID`),
  KEY `name` (`name`),
  KEY `guild` (`guild`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC AUTO_INCREMENT=185322 ;
[SQL] pobierz, plaintext 

W jednej jest 165tyś rekordów (23mb), w drugiej 270 tyś (33mb). Wykonuje paczki po 4 update na każdej bazie. Zapytanko wygląda mniej więcej tak:

[PHP] pobierz, plaintext 
	mysql_query("UPDATE baza SET lvl=".$record[level].", voc='".$record[vocation]."', guild='".$record[guild]."', sex='".$record[sex]."', lastlogin=".$record[lastlogin].", created_acc='".$record[created]."', city='".$record[residence]."' WHERE UID=".$record[uid]);
[PHP] pobierz, plaintext 

Najpierw baza z 165tyś rekordów i czasy wykonania 4 update w cyklu:
0.0023970603942871 sec
0.0020239353179932 sec
0.002047061920166 sec
0.0020139217376709 sec
0.0020439624786377 sec
0.0020511150360107 sec
0.0022640228271484 sec
0.0016040802001953 sec
0.0011930465698242 sec
0.0021450519561768 sec
0.0013589859008789 sec
0.0019218921661377 sec

Suma czasu dla 50 zapytań update: 4.3591480255127 sec


To samo na większej bazie (270tyś), też po 4 zapytania w cyklu:
0.012955188751221 sec
0.0023949146270752 sec
1.7737629413605 sec
0.16048979759216 sec
0.24561595916748 sec
0.15996599197388 sec
0.22124600410461 sec
0.26379299163818 sec
0.2989809513092 sec
0.18192505836487 sec
0.0041689872741699 sec
0.027604103088379 sec
0.53889012336731 sec

Suma dla 50 zapytań: 8.4705171585083 sec


Czym mogą być spowodowane tak duże rozbieżności w czasue wykonania tak prostego update? Różnica w ilości rekordów nie jest duża, zaledwie 100tyś. Co można zrobić aby poprawić efektywność zapytań?

Ten post edytował Radek_1 12.01.2013, 01:59:14

[markonix]

Na rozbieżność nie mam pomysłu ale update możesz spróbować zmienić w jedno zapytanie mysql - na pewno się da.
W CI np. jest update_batch.

[wiiir]

juz sam sobie odpowiedziales na to pytanie:
1) inna baza = inna konfiguracja sewera inny hardware maszyny

co wiecej ?
2) indexy lub trigery wszedzie to samo?
3) nie wiem jak w mysql bo zajmuje sie oracla-em ale czy w mysql jest cos takiego jak "high water mark"?
-jezeli tak to sprawdz ile wynosi, jak duzo to zrob kopie tabeli insetrujac dane z poprzedniej, stara dropnij i rename nowej - wykonaj updaty na nowej i porownaj czasy

narazie wiecej mi nie przychodzi do glowy

EDIT:
jeszcze jedno mi przyszlo do glowy
czy na koncu jest commit? Jak jest wykonywany commit? czy sam update sie tyle kreci?
Commit 300tys rekordow potrwa troche dluzej niz 100tys rekordow to chyba jasne co nie?

Ten post edytował wiiir 14.01.2013, 20:57:47

[Radek_1]

1) Hardwarowo to powinno być to samo, w ramach jednego hostingu obie bazy działają, powinny mieć te same parametry.
2) Tak, indeksy te same, triggerów nie ma.
3) Nie bardzo wiem co to jest "high water mark", ani tym bardziej jak sprawdzić jego rozmiar.
4) Nie ma żadnego commitu (autocommit po wykonaniu zapytania, chyba).


Dokładnie to mierzylem czas w ten sposób dla obu przypadków:

[PHP] pobierz, plaintext 
	$after1 = microtime(true);		
	foreach($chars as $record) {
    	mysql_query("UPDATE baza SET lvl=".$record[level].", voc='".$record[vocation]."', guild='".$record[guild]."', sex='".$record[sex]."', lastlogin=".$record[lastlogin].", created_acc='".$record[created]."', city='".$record[residence]."' WHERE UID=".$record[uid]);
	}
	echo "<br>".(microtime(true)-$after1) . " sec";
[PHP] pobierz, plaintext 

Czyli wykonywałem 4 update i zwracałem ile czasu to zajęło.
Różnica między danymi to dokładnie 110tyś rekordów. Dziwne, że różnica jest aż o dwa rzędy wielkości.


markonix, zerkne co i jak z tym update_batch

[wiiir]

100 tys rekordow to nie jest tak mało dla update-a szczegolnie jak sa indexy

[Radek_1]

Skopiowałem bazę danych z 2 do drugiej 1 (tj. tam gdzie było 160tyś wrzuciłem dane z tej w której było 270tyś). Uruchomiłem skrypt i... czasy są nadal rzędu 0.002sek.
Jedyny wniosek jaki można z tego wyciągnąć to to, że obie bazy muszą działać na innych maszynach, ta druga z 270tyś musi być wolniejsza, albo bardziej obciążona.
Ciekawe, bo hosting jest na home.pl i myślałem, że wszystkie bazy danych które można utworzyć w odrębie konta są na jednej maszynie i muszą mieć dokładnie takie same parametry.

Ten post edytował Radek_1 16.01.2013, 00:19:57

[wiiir]

wykonaj update z taka sama liczba rekordow na 2 roznych maszynach i porownaj wyniki

[alegorn]

przejdz na innodb, myisam przy update zaklada lock na cala tabele.

sprawdzanie na roznych serwerach jest bez sensu. wyniki nie beda miarodajne, nawet jesli dostaniesz dokladnie takie same zasoby - to obciazenie maszyny moze byc inne, a wtedy wyniki wyjda ci diametralnie rozne.

dlaczego te pola :

[SQL] pobierz, plaintext 
`name` varchar(30) CHARACTER SET latin1 NOT NULL,
`voc` varchar(30) CHARACTER SET latin1 DEFAULT NULL,
[SQL] pobierz, plaintext 

sa w latin1 skoro pozniej definiujesz tabele jako utf?? jaki w tym sens?

skoro danych masz mniej niz 1kk to po co zdefiniowales tak wielki index jak dla mnie by wystarczyl smalint(6)

wykonaj

[SQL] pobierz, plaintext 
SELECT * FROM baza  PROCEDURE ANALYSE()
[SQL] pobierz, plaintext 

i zastanow sie, czy zasugerowane zmiany nie mają sensu.

pozdrawiam,
Jacek.

edit: drobne korekty

Ten post edytował alegorn 17.01.2013, 11:47:25

[Radek_1]

dlaczego te pola :

[SQL] pobierz, plaintext

`name` varchar(30) CHARACTER SET latin1 NOT NULL,
`voc` varchar(30) CHARACTER SET latin1 DEFAULT NULL,


sa w latin1 skoro pozniej definiujesz tabele jako utf?? jaki w tym sens?

Pewne zaszłości po źle zaprojektowanej bazie danych na początku

skoro danych masz mniej niz 1kk to po co zdefiniowales tak wielki index questionmark.gif jak dla mnie by wystarczyl smalint(6)

Rekrody zmieniają się dość dynamicznie, przekroczylem już dawno 1kk

wykonaj
SELECT * FROM baza PROCEDURE ANALYSE()
i zastanow sie, czy zasugerowane zmiany nie mają sensu.

Nie znałem tego polecenia.

Patrzę na wyniki i się zastanawiam, czy warto dodawać do INT atrybut UNSIGNED?
Albo pole voc, które teraz jest INT(1) podpowiada mi, aby zmienić na: ENUM('0','1','2','3','4','5','6','7','8'), będzie działać szybciej? mniej miejsca w bazie zajmować?
Tak samo pole highscore z INT(1) na ENUM('0','1'), to już nie lepiej dać BOOLEAN?

Dzięki za pomoc i wskazówki

Edit:
Zmieniłem z Myisam na Innodb, wielkość tabelki z 25mb podskoczyła do 65.6mb, to normalne?

Ten post edytował Radek_1 17.01.2013, 21:27:57