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 ?

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.

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

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.

- 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...

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!).

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

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.

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

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

lepiej


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.

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