Zakladanie indeksow, prosba o pomoc Opcje

[gazelek]

Po raz pierwszy mam do czynienia z dosc spora baza danych (500 tys rekordow). Mam skrypt, ktory w petli wykonuje zapytanie (okolo 150-200 razy na jedno wywolanie skryptu)

[SQL] pobierz, plaintext 
SELECT minimum,maksimum,kurs_zamkniecia FROM notowanie WHERE indeks_id = '$idIndeksu' AND data<='$data' LIMIT 2
[SQL] pobierz, plaintext 

Zalozylem indeks w tabeli notowanie, typu BTREE na kolumny indeks_id i data, jednak nie zaobserwowalem przyspieszenia wykonania skryptu

Czas wykonania skryptu: 88.978815078735s

Jest to czas porownywalny z poprzednim czasem wykonania skryptu. Czy cos jeszcze da sie zrobic w celu przyspieszenia wykonania skryptu?

Jesli podalem zbyt malo szczegolow, to prosze o informacje (IMG:style_emoticons/default/smile.gif)

[blooregard]

Zerknij tutaj:
http://di.com.pl/porady/25700,0,Porada_MyS..._aplikacji.html

[gazelek]

Przykladowe zapytanie

[SQL] pobierz, plaintext 
SELECT * FROM notowaniee WHERE indeks_id=1 AND DATA<=1189302400 LIMIT 2
[SQL] pobierz, plaintext 

trwa 0.9271 sekundy. Przeczytalem artykul z linka, sprawdzilem describe'm jak wyglada sytuacja z kluczami. Dla powyzszego zapytania sprawdzanych jest 2292 rekordy.

W skrypcie wykonywany jest tez update (badalem czas wykonania, jest na poziomie 0,14 s - czyli dla 200 rekordow same update'y trwaja 28 sekund).

W przypadku update'a rowniez w warunku znajduja sie tylko pola indeks_id i data, wiec do tej sytuacji odnosi sie jeden i ten sam indeks.

Tak wyglada zapytanie tworzace tabele:

[SQL] pobierz, plaintext 
CREATE TABLE IF NOT EXISTS `notowaniee` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `indeks_id` int(11) NOT NULL,
  `kurs_otwarcia` float(8,2) NOT NULL,
  `minimum` float(8,2) NOT NULL,
  `maksimum` float(8,2) NOT NULL,
  `kurs_zamkniecia` float(8,2) NOT NULL,
  `wolumen` int(7) NOT NULL,
  `data` int(11) NOT NULL,
  `trend` enum('nd','+','-') collate utf8_polish_ci NOT NULL,
  `srednia1` float(8,2) NOT NULL,
  `srednia2` float(8,2) NOT NULL,
  `srednia3` float(8,2) NOT NULL,
  `adxDecyzja` enum('kupno','sprzedaz','czekaj') collate utf8_polish_ci NOT NULL,
  `adx` float(6,2) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `indeksid_index` USING BTREE (`indeks_id`),
  KEY `Index_4` USING BTREE (`data`,`indeks_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci AUTO_INCREMENT=491694 ;
[SQL] pobierz, plaintext 

Zapytania wykonuja sie szybciej jesli w warunku podam id (pierwsze zapytanie wykonuje sie okolo 10 razy szybciej).


Poki co nie mam pomyslu jak rozwiazac problem, w tej chwili skrypt dziala a ja zaobserwowalem, ze po zalozeniu indeksow dziala on gorzej niz przed (wczesniej wykonywal sie w okolo 70s, teraz jest to juz 100s - a ilosc danych sie nie zmienila).

[blooregard]

Usuń te indeksy BTREE i załóż normalne indeksy na kolumny, z których pobierasz dane ( minimum,maksimum,kurs_zamkniecia).
Zobaczymy, co to da.

[gazelek]

Zalozylem trzy indeksy na kolumny maksimum, minimum, kurs_zamkniecia (czy moze mialem zalozyc jeden potrojny na wszystkie trzy)

Czas wykonania skryptu: 102.5153901577s
Czas wykonania skryptu: 97.040512084961s

Czyli raczej niewiele to dalo. Ponizej struktura tabeli dotyczaca kluczy

[SQL] pobierz, plaintext 
PRIMARY KEY  (`id`),
  KEY `Index_4` (`kurs_zamkniecia`),
  KEY `Index_2` (`minimum`),
  KEY `Index_3` (`maksimum`)
[SQL] pobierz, plaintext 

@blooregard: dzieki za pomoc, troche sie pogubilem przy tych indeksach i potrzebuje jakiegos wsparcia (IMG:style_emoticons/default/smile.gif)

Edit: dalem sobie describe dla zapytania z powyzszego postu, ilosc przeszukiwanych rekordow wzrosla do 493tys.... ;/

Edit edit: Eureka! Zalozylem zwykly index na indeks_id i data i oto efekt:

Czas wykonania skryptu: 2.2445020675659s

Dzieki za pomoc!

Ten post edytował gazelek 13.04.2010, 21:57:02

[blooregard]

A ja się zakręciłem (IMG:style_emoticons/default/smile.gif)

Chodziło o indeks na kolumnę, której używasz w warunku WHERE, czyli właśnie indeks_id, ale zdążyłes sam już do tego dojść (IMG:style_emoticons/default/smile.gif)

[gazelek]

Dalej mam problem (IMG:style_emoticons/default/smile.gif) skrypt przyspieszyl, bo puscilem zapytania na danych, na ktorych wykonywal sie wczesniej. Symulacja przeszla jednak dalej i operuje na danych, ktorych wczesniej jeszcze nie brala. Mam wiec zapytanie:

[SQL] pobierz, plaintext 
SELECT id,minimum,maksimum,kurs_zamkniecia FROM `notowaniee` WHERE indeks_id = '53' AND data<='1221436800' ORDER BY id ASC LIMIT 2
[SQL] pobierz, plaintext 

ktore trwa 1.9007 sekund(y). W tabeli notowaniee zalozony jest jeden indeks na dwie kolumny (data i indeks_id). I co tu jeszcze zrobic, zeby uzyskac przyspieszenie?(IMG:style_emoticons/default/smile.gif)

[Mchl]

Która to wersja MySQL?

Możesz podać wynik EXPLAIN?

[edit]

Wiesz co... zrobiłęm sobie tą tabelę, wpakowałem do niej ~600000 losowych wierszy i u mnie to zapytanie nie zajmuje nawet 0.001s...
Może serwer nie daje rady?

Ten post edytował Mchl 14.04.2010, 16:53:05

[gazelek]

Oto wynik dzialania EXPLAIN dla powyzszego zapytania

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE notowaniee range Index_2 Index_2 8 NULL 2824 Using where; Using filesort

MySQL w wersji 5.0.37. Tabele uzywaja jako silnika InnoDB, czy to moze miec jakis wplyw?

[Mchl]

U siebie też dałem InnoDB

U ciebie jest Using filesort u mnie nie. Zrzuca tabelę na dysk, stąd takie opóźnienie.

Jak jest zdefiniowany Index_2 w końcu?

Pokaż co Ci się wyświetli jak puścisz

Kod

SHOW VARIABLES LIKE 'inno%';

[gazelek]

Wynik dzialania

Variable_name Value
innodb_additional_mem_pool_size 2097152
innodb_autoextend_increment 8
innodb_buffer_pool_awe_mem_mb 0
innodb_buffer_pool_size 15728640
innodb_checksums ON
innodb_commit_concurrency 0
innodb_concurrency_tickets 500
innodb_data_file_path ibdata1:10M:autoextend
innodb_data_home_dir
innodb_doublewrite ON
innodb_fast_shutdown 1
innodb_file_io_threads 4
innodb_file_per_table OFF
innodb_flush_log_at_trx_commit 1
innodb_flush_method
innodb_force_recovery 0
innodb_lock_wait_timeout 50
innodb_locks_unsafe_for_binlog OFF
innodb_log_arch_dir
innodb_log_archive OFF
innodb_log_buffer_size 1048576
innodb_log_file_size 10485760
innodb_log_files_in_group 2
innodb_log_group_home_dir .\
innodb_max_dirty_pages_pct 90
innodb_max_purge_lag 0
innodb_mirrored_log_groups 1
innodb_open_files 300
innodb_rollback_on_timeout OFF
innodb_support_xa ON
innodb_sync_spin_loops 20
innodb_table_locks ON
innodb_thread_concurrency 8
innodb_thread_sleep_delay 10000

Zapytanie tworzace tabele:

[SQL] pobierz, plaintext 
CREATE TABLE IF NOT EXISTS `notowaniee` (
 /* tu kolumny, takie jak kilka postow wyzej */
  PRIMARY KEY  (`id`),
  KEY `Index_2` (`indeks_id`,`data`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci AUTO_INCREMENT=496647 ;
[SQL] pobierz, plaintext 

Co do serwera - skrypt wykonuje sie u mnie na komputerze, mam 2x2.0 GHz i 4 Gb Ramu....

Obecnie czas wykonania dla 160 iteracji petli wynosi 180 sekund.

Ten post edytował gazelek 14.04.2010, 19:24:48

[Mchl]

innodb_buffer_pool_size masz ustawione na 15MB a ta tabela przy ~600000 rekordów zajmuje ponad 60MB. Nie mieści się po prostu w pamięci. Jak masz 4GB, to możesz spokojnie podbić ten paramter do 1GB.

[gazelek]

Dzieki, teraz dziala jak zloto (IMG:style_emoticons/default/smile.gif) ~3s (IMG:style_emoticons/default/smile.gif) potestuje jeszcze, ale mam nadzieje ze juz powinno byc dobrze. Pozdro!