Wydajność, Tabele a wydajność Opcje

[kezard]

Witam wszystkich!

Zwracam się z pytaniem do bardziej zaawansowanych użytkowników którzy wiedzą co nieco jak sprawuje się MySQL w sytuacjach dużej ilości danych. Pisze system wiadomości prywatnych i mam dylemat : trzymać wiadomości w jednej tabeli czy też podzielić tabele ze względu na foldery i w razie czego przenosić rekordy miedzy tabelami? Założyłem sobie następujące dane : 5000 kont użytkowników x (średnio) 20 wiadomości = 100k rekordów. Czy przy takiej ilości danych wydajne będzie trzymanie wszystkiego w jednej tabeli? Proszę o odpowiedzi.

Pozdrawiam.

[Mchl]

A na jakim sprzęcie?

[kezard]

Dobre pytanie. Na razie testuje tylko lokalnie na kompie. Może jesteś w stanie doradzić jaki serwer jest do tego potrzebny ? Może wiesz gdzie znaleźć jakieś testy żeby porównać wydajność tabel i czas oczekiwania przy roznych iloscach danych i konfiguracjach sprzetowych oraz jaki soft pozwala zrobic takie testy samemu?

[Wicepsik]

Jedna tabela wystarczy.

[kezard]

Jedna tabela wystarczy.

A mozesz podac jakies argumenty za?

[Mchl]

Może być taki, że mam tabele po kilkanaście milionów wierszy i nie ma problemów z wydajnością?

[misiek08]

Operowałem na tabeli MySQL, która miała 200-300MB i zaczynało się robić tragicznie. Potem przeszła ona w ~5,4GB i zapytanie z 2 warunkami WHERE, ORDER'EM i 1 JOIN'em trwało ok. 44sek. Lepiej podziel to jakoś między tabele. Może po prostu jakoś po id. Cokolwiek, jeśli spodziewasz się tylu rekordów, będzie wydajniejsze.

[Mchl]

Stary, gdzie 100000 wierszy, a gdzie 5GB? No chyba że będą tam sobie wiadomości po 500000 znaków pisać.

A jak chcesz dzielić to przez partycjonowanie, a nie rozrzucać po niewiadomo ilu tabelach.

[dodane]
Pozwoliłem sobie zrobić taki test:

Kod

DROP TABLE IF EXISTS `test`.`wiadomosci`;
CREATE TABLE  `test`.`wiadomosci` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `fromID` int(10) unsigned NOT NULL,
  `toID` int(10) unsigned NOT NULL,
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `status` enum('UNREAD','READ') NOT NULL,
  `message` text NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `index_2` (`fromID`,`toID`,`ts`,`status`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO wiadomosci VALUES (null,ROUND(RAND() * 2048),ROUND(RAND() * 2048),FROM_UNIXTIME(RAND() * POW(2,32) - 1), 'UNREAD', '');
INSERT INTO wiadomosci (fromID, toID, ts, status, message) SELECT ROUND(RAND() * 2048),ROUND(RAND() * 2048),FROM_UNIXTIME(RAND() * POW(2,32) - 1), 'UNREAD', '' FROM wiadomosci; -- powtorzone kilkanascie razy, az w tabali znalazlo sie ~1 300 000 wierszy

UPDATE wiadomosci SET message = 'tutaj wstawiłem 2048 znaków'; -- uwaga, to trochę trwa :D

SHOW TABLE STATUS LIKE 'wiadomosci';

> Avg_row_length: 2343
> Data_length: 2457862144
> Index_length: 39403520
> Data_free: 3521118208
> Auto_increment: 1376221

SELECT SQL_NO_CACHE * FROM wiadomosci WHERE fromID = 1 AND toID = 585; -- najpierw sprawdzilem czy taki istnieje;)
> 1 row in set (0.00 sec)

SELECT SQL_NO_CACHE * FROM wiadomosci WHERE fromID = 1 AND ts BETWEEN 20000101 AND 20100101;
> 44 rows in set (0.01 sec)

Raczej nie tak źle?
W zależności od konkretnych zapytań trzebaby jeszcze założyć dodatkowe indeksy. Np do zapytania

Kod

SELECT SQL_NO_CACHE * FROM wiadomosci WHERE toID = 585;

przydałby się indeks zaczynający się od kolumny toID.



Ten post edytował Mchl 22.06.2010, 21:08:05

[maly_swd]

spokojnie w jednej tabeli. Jak baza Ci sie rozrosnie to http://dev.mysql.com/doc/refman/5.1/en/partitioning.html i wtedy bedzie smigalo

[wlamywacz]

W tabeli 20 milionów rekordów + joiny + warunki i tylko 0.2 sekundy. Zależy od struktury tabeli i operacjach na niej.

[mkozak]

Zdecydowanie w jednej tabeli.

Tylko musisz to ładnie zaindexować (po userze, po folderze + to co będzie ci potrzebne do wyszukiwania np. data).

Jeżeli zrobisz w kilku tabelach i zaczną ci userzy przenosić maile pomiędzy tabelkami to będziesz miał bardzo nieprzyjemny efekt spadku wydajności wszystkich tabel (dużo operacji insert delete).
Musiał byś bardzo często przeprowadzać maintenance na tebelach.

[wookieb]

Może zróbmy tak...
Podaj strukturę tabeli oraz zapytania jakie na niej wykonujesz a my pomożemy dobrać odpowiednią strukturę i indeksy ok? Ewentualnie zmienić niektóre zapytania. 100k dla bazy to nie jest dużo.

Ten post edytował wookieb 29.06.2010, 09:27:22

[kezard]

W tabeli 20 milionów rekordów + joiny + warunki i tylko 0.2 sekundy. Zależy od struktury tabeli i operacjach na niej.

Na jakim sprzęcie ? Bo planuje wynająć dedyka a nie brałem nigdy wcześniej, możne coś doradzisz?

Tylko musisz to ładnie zaindexować (po userze, po folderze + to co będzie ci potrzebne do wyszukiwania np. data).

Rozumiem ze wystarczy dodać w tabeli dodatkowe indexy i poustawiać ich moc w kolejności która mi odpowiada tak?

Ogólnie to dziękuje wszystkim za podpowiedzi. Bardzo pomocne (IMG:style_emoticons/default/winksmiley.jpg)

Ten post edytował kezard 30.06.2010, 10:59:18

[mkozak]

Jak dedykowany to się nie masz czym martwić. Byle nie jakieś wirtualne G.
Weź sobie dyski 15k i zapas ramu.

[Mchl]

Jak dedykowany to się nie masz czym martwić. Byle nie jakieś wirtualne G.
Weź sobie dyski 15k i zapas ramu.

I nie zapomnij dać tego ramu MySQLowi.

[wlamywacz]

U mnie wystarcza zwykły hosting u znajomego. Powtarzam wszystko zależy od struktury tabeli i danych w niej.