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 (IMG:style_emoticons/default/questionmark.gif) ?

[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