Kilka mniejszych tabel, czy jedna duuuża, pomysł na zwiększenie wydajności mySQL... ale czy dobry ??? Opcje

[modic]

Sprawa wygląda tak, piszę dzienniczek ucznia i jest tam tabela z ocenami (każda ocena oddzielny rekord), przewiduję że może osiągnąć ponad 100 000 000 rekordów.

Przy testowaniu wydajności z ok 1.5 mln ocen, przy pobieraniu oceny ucznia, albo oceny z przedmiotu w danej klasie, zapytania wykonywały się w ok 0.6 sec, często krócej, (0.1 przy 30 tyś ocen) na tabelę są założone 4 indeksy, po ich usunięciu pokazanie ocen jednego ucznia trwało ponad 60 sec :/
Usuwanie, albo edycja pojedynczych rekordów trwa jednak zdecydowanie za długo, raz 0.2 a raz nawet 2 sec.
Ale z drugiej strony ta tabela może być często aktualizowana.

Tak więc zastanawiam się czy nie dobrym wyjściem było by zamiast jednej dużej tabeli OCENY dla wszystkich szkół w systemie, zrobić automatycznie dla każdej szkoły oddzielną tabelę na oceny, np OCENY_SZKOLA01, OCENY_SZKOLA02
Liczba szkół nie powinna przekroczyć 200, a w jednej tabeli byłby max 1mln rekordów.

Serwer to nie dedyk, jakiś współdzielony, nie znam jego parametrów.

Tak wiec pytam, jak oceniacie ten pomysł, dobry, zły i czy mogą być jakieś negatywne konsekwencje, a może macie inny pomysł na poprawę wydajności, oczywiście poza budowaniem własnego data center ?

[Mchl]

Pomysł zły.
Lepiej partycjonować.

Jakiego typu tabela?

Nie mówiąc o tym, że na współdzielonym serwerze to może masz 128MB RAM, a może nie, więc masz ciągle odczyty i zapisy na dysku.

[modic]

Serwer to maszyna współdzielona, ale stoi na nim kilka dużych serwisów, więc raczej ma więcej niź 126MB ram

Typ tabel to InnoDB

Wersja serwera: 5.0.84-v2+tld1-log

Partycjonowanie było by dobre, nawet nie wiedziałem że jest coś takiego, ale chyba byłaby konieczna aktualizacja mysql

[Mchl]

Serwer to maszyna współdzielona, ale stoi na nim kilka dużych serwisów, więc raczej ma więcej niź 126MB ram

Chodzi o to ile jest dostępne dla Ciebie. A jeżeli wszystkie te duże serwisy stoją na jednym serwerze mysql, to nie dziw się, że Twoje rzadziej używane tabele są zrucane na dysk, kosztem częściej używanych tabel z tamtych serwisów.

Ten post edytował Mchl 25.02.2010, 11:03:17

[Pilsener]

100 000 000 rekordów

- zapomnij. Właściwą wydajność zapewnisz tylko poprzez partycjonowanie danych i ich rozdzielenie na kilka serwerów, pomyśl o tym, ile mkw zajmuje serwerownia np. dużego banku czy choćby jakaś baza danych linii lotniczych. Najlepiej od razu pomyśl o miejscu w jakimś data center i swoich serwerach, wyjdzie taniej.

I w ogóle nie wiem, czy mysql jest odpowiedni do tak dużej liczby danych...

[Mchl]

Sama ilość rekordów o niczym nie mówi. Liczy się objętość. Do 500GB MySQL sobie radzi na jednym serwerze całkiem dobrze (ale nie na współdzoelonym!).

[modic]

Dzięki za podpowiedzi

sam serwer to dedyk, ale do dyspozycji jest tylko połowa mocy obliczeniowej, w każdym razie do 5 mln ocen jeszcze sobie radzi, ale przy większym ruchu mogą być problemy, a o wydajności wato pomyśleć wcześniej niż gdy jest troszkę za późno, choć zanim się uzbiera te 5 mln ocen to się troszkę zejdzie, jeden uczeń to ok 200 ocen przez rok

tabela oceny z 5 mln rekordów to ok 600mb, są


A skoro już jesteśmy przy temacie partycjonowania, w którym jestem jak narazie zielony, to czy takie oznaczenie klucza głównego będzie prawidłowy, ew. czy nie będzie z nim problemów:

[SQL] pobierz, plaintext 
PRIMARY KEY (`oceny_id`,`oceny_szkola`,`oceny_year`,`oceny_semestr`,`oceny_przedmiot`,`oceny_uczen`,`oceny_kolumna`,`oceny_ocena`,`oceny_ocena_pm`)
[SQL] pobierz, plaintext 

bo mysql wyrzuca mi błąd: "A PRIMARY KEY must include all columns in the table's partitioning function"

w efekcie mam takie coś:

[SQL] pobierz, plaintext 
CREATE TABLE IF NOT EXISTS `oceny` (
  `oceny_id` int(11) NOT NULL AUTO_INCREMENT,
  `oceny_szkola` int(11) NOT NULL DEFAULT '0',
  `oceny_year` int(11) NOT NULL DEFAULT '0',
  `oceny_semestr` int(11) NOT NULL DEFAULT '0',
  `oceny_przedmiot` int(11) NOT NULL DEFAULT '0',
  `oceny_uczen` int(11) NOT NULL DEFAULT '0',
  `oceny_kolumna` int(2) NOT NULL DEFAULT '0',
  `oceny_ocena` int(1) NOT NULL DEFAULT '0',
  `oceny_ocena_pm` varchar(2) NOT NULL,
  PRIMARY KEY (`oceny_id`,`oceny_szkola`,`oceny_year`,`oceny_semestr`,`oceny_przedmiot`,`oceny_uczen`,`oceny_kolumna`,`oceny_ocena`,`oceny_ocena_pm`),
  KEY `oceny_year` (`oceny_year`),
  KEY `oceny_przedmiot` (`oceny_przedmiot`),
  KEY `oceny_uczen` (`oceny_uczen`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8
 
PARTITION BY LIST (oceny_szkola)
(
	PARTITION s1 VALUES IN (1) ENGINE = InnoDB,
	PARTITION s2 VALUES IN (2) ENGINE = InnoDB,
	PARTITION s3 VALUES IN (3) ENGINE = InnoDB
);
[SQL] pobierz, plaintext 

cel był taki żeby każda szkoła miała oddzielną partycję i oceny były pobierane tylko z jednej partycji

[Mchl]

Do duszy taki klucz główny.
Przeczytaj definicję klucza głównego i sprawdź czy to co wymyśliłeś ją spełnia.

Wskazówka: Klucz ma zawierać wszystkie kolumny identyfikujące partycje, a nie wszystkie kolumny tabeli

P.S. Jedna szkoła na partycję to może być lekki overkill.

Ten post edytował Mchl 12.03.2010, 22:07:15

[modic]

hehe

Też bym wolał np PRIMARY KEY (`oceny_id`), ale mam mały problem i nie wiem jak go obejść

Gdy podzielę tabelę na partycję wg kolumny 'oceny_szkola' to AUTO_INCREMENT mogę dodać tylko do kolumny 'oceny_szkola', inaczej wywala błąd, ew zrobić PRIMARY KEY tak jak zrobiłem, czyli w dupny sposób.
Efekt jest taki że rekordy w tabeli 'oceny_id' są puste i nie można edytować, ani usuwać ocen, bo nie mogę do niej zastosować AUTO_INCREMENT

Oczywiście problem by znikł gdyby partycje były np typu RANGE i były tworzone wg 'oceny_id', ale w skrajnym przypadku przeszukiwane mogłyby być nawet wszystkie partycje, a raczej byłoby lepiej gdyby przeszukiwana była tylko jedna (tak mi się wydaje, mogę się mylić).

Chyba jednak zrobię ten range...

P.S.
niekoniecznie overkill, 1000 uczniów w 5 lat jest w stanie uzbierać 1 mln ocen, czyli sporo

[Mchl]

PRIMARY KEY (`oceny_id`,`oceny_szkola`) próbowałeś?
Co to jest milion ocen... I to jeszcze w takich krótkich rzędach.

PS. Zamiast

Kod

  `oceny_year` int(11) NOT NULL DEFAULT '0',

lepiej

Kod

  `oceny_year` year NOT NULL DEFAULT '0',

PPS. Chyba, że to rok szkolny... nie pomyślałem... ale nawet wtedy TINYINT raczej wystarczy. Podobnie jak w innych kolumnach, zamist INT możesz dać mniejsze typy. Jeden, dwa bajty, przemnożone przez te miliony ocen, dadzą Ci konkretne oszczędności.

Ten post edytował Mchl 13.03.2010, 09:34:12

[modic]

faktycznie, PRIMARY KEY (`oceny_id`,`oceny_szkola`) zadziałało, wielkie dzięki za pomoc