Miliony rekordów w bazie MySQL

Optymalizacja zapytań, dodanie indeksów, cachowanie.

A jeśli zapytania są tak proste, że nie ma co optymalizować, indeksy są dodane do pól po których rekordy są wyszukiwane a cachowanie trochę nie pasuje, ponieważ dane co 5 minut są aktualizowane?

@2solaris5:

Ukrywasz rzeczywiste zagadnienie, czyli kręcisz.

Czy jest się wtedy skazanym na sekundowe, paru-sekundowe oczekiwanie na wykonanie zapytania?

Co jest klientem bazy danych? Soluszyn desktopowe? Wieloużytkownikowe webowe? W jakim języku ?
Mozna robić coś w modelu 'async' ale to zupełnie inna opowieść i nie ma nic wspólnego z bazami danych

Też mnie ciekawi jak są strony, które mają ogrom statystyk (np. ceny kryptowalut z ostatnich lat). Jak to jest zrobione, że jest tak dużo danych i to się tak szybko ładuje?

Rozległem archiwa historyczne (nie podlegające zmianom) prawie nigdy nie trzyma się w bazach SQL, taka zmiana kontekstu pozbawia je zalet.
Największe soluszyny na pewno się rozprasza (i współczesne NoSQLe są bardziej podatne na rozpraszanie - SQL kocha integralność)

Czy w tak pojemnej tabeli przechowywać wszystkie rekordy czy może tylko ostatnie z 30 dni? A resztę trzymać w jakiejś innej?

I mieć inne raporty dla FV z ostatnich 30 dni, inne dla starszych ? Przerażające constraintsy na klucze obce ? Brr, strach nawet pomyśleć ...
To niemal zawsze jest zła pokusa. Pozorne rozwiązanie 1go problemu, które generuje 20 innych

@2solaris5:

Właśnie zerknąłem:
11mln w tabeli , drugie miejsce 1.3 mln - to mała baza, pięknie gania na limitowanych Expressie 1.5GB RAM.

26mln / 3mln - baza najwyżej średnia, dostaje chyba 8GB RAM

Profesjonalnie zaprojektowane, indeksy, dopóki nie udupcyć kwerendą z like % nie widać specjalnych problemów.

Plus https://4programmers.net/index.php/Forum/Bazy_danych/203038-partycjonowanie_bazy_danych

Niesamowite, naprawdę. Może mi Laravel Telescope to wszystko spowalnia też trochę. Spróbuję go wyłączyć dla niektórych jobsów. Bo właśnie zauważyłem, że w ciągu 10 dni stworzyło się logów na ponad 20 GB.

Swoją drogą - czy stosujesz jakąś specjalną konfigurację mysql czy zostałeś przy domyślnej?
I ile ramu przydzielić do wykorzystania serwerowi mysql?
W ogóle ile ramu posiadać na serwerze do takich dość dużych zbiorów?

@2solaris5:

Logowanie do SQL to uroda (dość mało inteligenta, żeby nie kląć) światka PHP.
To trochę się bierze z tego, ze na bieda-hostingu jest jednym z niewielu dostępnych w czasie rzeczywistym (logi z /var/log są dostępne np po północy )

Zobaczysz temat w szerszym horyzoncie, jak pogrzebiesz w YT u ewangelizatorów dużych projektów Javy. Są naprawdę piękne rozwiązania.

Być może w PHP / Lavarelu jakieś profesjonalne silniki logowania są dostępne, ale na pewno w tym środowisku się o tym nie rozmawia

2solaris5 napisał(a):

Swoją drogą - czy stosujesz jakąś specjalną konfigurację mysql czy zostałeś przy domyślnej?
I ile ramu przydzielić do wykorzystania serwerowi mysql?

Nie czuję się pro adminem bazowym. Ale zarówno MySQL jak i MariaDb maja wiodące artykuły nt optymalizacji, powszechnie znane. Stosuję 3-4 elementy z tych artykułów.
"Jak zawiodą wszystkie środki przeczytać dokumentację" ?

RAM ... to zależy, ile klient ma na serwery itd... dawniej (=20 lat temu) się cieszyliśmy, jak RAMu było na 15% bazy, dziś modne by było całą zmieścić w RAM.

Jeżeli przy kilku milionach rekordów masz problem z czasem odpowiedzi, to coś jest zrypane - struktura, indeksy, zapytanie, statystyki serwera. Sprawdzałeś plan zapytania, czy "patrzyłem w kod i jest dobrze"? Zmieniłeś w tym co się łączy do bazy poziom izolacji na coś czego potrzebujesz, czy jedziesz na defaulcie (repeatable read)?
Jeżeli używasz bazy danych SQL tylko po to, żeby ją co 5 minut wypełnić dodatkowymi danymi i żeby każdy request pobierał sobie te ostatnie 5 minut danych (być może agregując je sobie w jakiś sposób), to używasz złego narzędzia.

Generalnie dopóki nie sprecyzujesz dokładnie problemu to dyskutowanie o tym czy się da czy się nie da uzyskiwać odpowiedzi w czasie < 1 sek. jest bez sensu.
Nie znamy struktury ani rodzaju zapytań o jakich piszesz ale poteoretyzować można...

2solaris5 napisał(a):

A jeśli zapytania są tak proste, że nie ma co optymalizować, indeksy są dodane do pól po których rekordy są wyszukiwane a cachowanie trochę nie pasuje, ponieważ dane co 5 minut są aktualizowane?

Najprawdopodobniej to znaczy, ze baza jest źle zaprojektowana robisz wyszukiwanie po stringach, brakuje kluczy itp... Dopóki nie przeszukujesz dużych pól tekstowych zwykłym "like" to kilka milionów rekordów nawet dla MySQL nie jest problemem i odpowiedzi powinny trwać ułamek sekundy.

Czy jest się wtedy skazanym na sekundowe, paru-sekundowe oczekiwanie na wykonanie zapytania?

Nie.

Co jeśli po roku tych rekordów będzie z 20 milionów?

To znaczy, ze po 5 latach ich może być 100 milionów i nadal nie powinno być problemu.

Czy w tak pojemnej tabeli przechowywać wszystkie rekordy czy może tylko ostatnie z 30 dni? A resztę trzymać w jakiejś innej?

A to zależy co chcesz osiągnąć, jakie są Twoje dane i jakie są cele projektu.

Też mnie ciekawi jak są strony, które mają ogrom statystyk (np. ceny kryptowalut z ostatnich lat). Jak to jest zrobione, że jest tak dużo danych i to się tak szybko ładuje?

Jeśli działa to sprawnie to znaczy, że są zrobione "z głową"" i w sposób przemyślany. Nie ma jednej ogólnej dobrej rady na wszelkie zagadnienia związane z dużą ilością rekordów.
Rekord rekordowi nie równy :-)

Mam bazę do której wpada mi po 400 rekordów na minutę (dziś) w szczycie sezonu dochodzi do 2000 ale na potrzeby prezentacji co 3 minuty tworzę agregaty które lecą do Elasticseracha.
Czym innym jest ujęcie statystyczne na potrzeby prezentacji a czym innym praca na ostatnich bieżących rekordach w celach analizy. Jednak korzystam z bazy często i zależy mi na tym żeby to śmigało. Dlatego w głównej tabeli mam same kolumny INT nawet adresy IP i domeny trzymam w osobnych tabelach a w głównej przeszukiwanej trzymam tylko klucze. Dzięki temu cała tabela jest dość mała i może sobie siedzieć w Cache (MySQL robi to sam). W chwili obecnej baza ma 3,6 miliona rekordów zajmuje niecały GB. Proste pytania na takiej tabeli to jakieś milisekundy:

W powyższym przypadku na swoje potrzeby nie potrzebuję wykonywać dalszych optymalizacji a nadal w ramach standardowego MySQL są dostępne opcje takie jak:

• partycjonowanie tabel: https://dev.mysql.com/doc/refman/5.7/en/partitioning.html , https://dev.mysql.com/doc/refman/8.0/en/alter-table-partition-operations.html
• zmiana tabeli na tabelę typu RAM (memory storage): https://dev.mysql.com/doc/refman/8.0/en/memory-storage-engine.html

Gdyby była potrzeba to w dalszych etapach możemy skorzystać z dobrodziejst klastra: https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-innodb-cluster.html
lub replikacji bazy i loadbalancingu.

Tak wygląda moja struktura tabeli.
Rekordy obecnie wybieram tylko po polach server_id i created_at.
Konfiguracja MariaDB jest domyślna, nic nie zmieniałem.
Zdarza się, że wybranie za pierwszym razem danych trwa od 0.5 do 3.2 sekund.
Kolejne pobrania tych samych danych trwają 100-300ms.

Na chwile obecną moje zapytania są proste w stylu. No i jakas data to zwykle 1 dzień wstecz.

I tak w ogóle czy string jako id jest wolniejszy?

 SELECT * FROM room_statistics WHERE server_id = 5 AND created_at >= 'jakas data'

         Schema::create('room_statistics', function (Blueprint $table) {
            $table->uuid('id')->primary();
            $table->foreignId('server_id')->constrained()->onUpdate('cascade')->onDelete('cascade');
            $table->tinyInteger('status');
            $table->boolean('online');
            $table->integer('spoof')->default(false);
            $table->integer('players_1')->default(0);
            $table->integer('players_2')->default(0);
            $table->integer('players_3')->default(0);
            $table->integer('npcs')->default(0);
            $table->integer('monsters')->default(0);
            $table->integer('mounts')->default(0);
            $table->integer('interval')->default(0);
            $table->timestamps();

            $table->index('created_at');
        });

2solaris5 napisał(a):

Rekordy obecnie wybieram tylko po polach server_id i created_at.

Czy masz założone indeksy na tych kolumnach?
Wykonaj w SQL komendę

SHOW INDEX FROM `room_statistics`;

i wklej tu jej wynik.

@2solaris5: Pokaż plan tego zapytania. Klucz obcy nie jest z automatu indeksem. wróć też planem zapytania explain analyze select * from server statistics...

Ogólnie chwilowo nie mam rekordów, bo postawiłem bazę od nowa.

2solaris5 napisał(a):

Ogólnie chwilowo nie mam rekordów, bo postawiłem bazę od nowa.

Czyli wielka improwizacja :-)
Z kodu, który wkleiłeś wyżej wynika, że nie miałeś prawa mieć tego indeksu. Skąd zatem magicznie się pojawił 4 minuty temu?

         Schema::create('room_statistics', function (Blueprint $table) {
            $table->uuid('id')->primary();
            $table->foreignId('server_id')->constrained()->onUpdate('cascade')->onDelete('cascade');
            $table->tinyInteger('status');
            $table->boolean('online');
            $table->integer('spoof')->default(false);
            $table->integer('players_1')->default(0);
            $table->integer('players_2')->default(0);
            $table->integer('players_3')->default(0);
            $table->integer('npcs')->default(0);
            $table->integer('monsters')->default(0);
            $table->integer('mounts')->default(0);
            $table->integer('interval')->default(0);
            $table->timestamps();

            $table->index('created_at');
        });

Te 2 linijki ustawiają indeksy. Klucz obcy w laravelu automatycznie nadaje indeks.

$table->foreignId('server_id')->constrained()->onUpdate('cascade')->onDelete('cascade');
$table->index('created_at');

A w jaki sposób mierzysz ten czas i czy przypadkiem to nie jest select * bez limitów? To mogłoby oznaczać, że to czas przesyłania danych a nie wykonywania zapytania.
Strasznie skąpisz informacji i każesz się strasznie dużo domyślać.

1. Wrzuć gdzieś strukturę tej bazy najlepiej dumpa,
2. Napisz jakie konkretnie zapytanie SQL zadajesz, które Twoim zdaniem wykonuje się zbyt długo,
3. Napisz czy serwer masz lokalnie czy zdalny,

Daj szansę sobie pomóc.

2solaris5 napisał(a):

Na chwile obecną moje zapytania są proste w stylu. No i jakas data to zwykle 1 dzień wstecz.
I tak w ogóle czy string jako id jest wolniejszy?

String jako id jest wolniejszy jeśli nie ma indeksu. Z indeksem ta różnica jest dużo mniejsza.

--
-- Table structure for table `room_statistics`
--

DROP TABLE IF EXISTS `room_statistics`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `room_statistics` (
  `id` char(36) COLLATE utf8mb4_unicode_ci NOT NULL,
  `server_id` bigint(20) unsigned NOT NULL,
  `status` tinyint(4) NOT NULL,
  `online` tinyint(1) NOT NULL,
  `spoof` int(11) NOT NULL DEFAULT 0,
  `players_1` int(11) NOT NULL DEFAULT 0,
  `players_2` int(11) NOT NULL DEFAULT 0,
  `players_3` int(11) NOT NULL DEFAULT 0,
  `npcs` int(11) NOT NULL DEFAULT 0,
  `monsters` int(11) NOT NULL DEFAULT 0,
  `mounts` int(11) NOT NULL DEFAULT 0,
  `interval` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `room_statistics_server_id_foreign` (`server_id`),
  KEY `room_statistics_created_at_index` (`created_at`),
  CONSTRAINT `room_statistics_server_id_foreign` FOREIGN KEY (`server_id`) REFERENCES `servers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

1. Potrafi (bez mysql cache) za pierwszym razem zwracać rekordy nawet do 3.2 sekundy. Później przez chwile jest to normalna wartość. 100-300ms. Jak w tabeli jest ponad milion rekordów.

SELECT * FROM room_statistics WHERE server_id = 5 AND created_at >= 'jakas data'

1. To VPS. 4 rdzenie, 8 gb ramu, dysk nvme

Naprawdę nie umiesz wrzucić wszystkiego?
Napisałem Wrzuć gdzieś strukturę tej bazy
Struktura bazy to nie jedna tabela.
Co ma nam dać jedna tabela?

Na tej konfiguracji VPS powinno zapierniczać aż miło! Zatem COŚ ROBISZ ŹLE!

@katakrowa: Myślę, że przy tym zapytaniu nie ma co patrzeć na resztę bazy danych.

@2solaris5: Wrzuć plan zapytania (EXPLAIN). Ciężko się domyślić co sobie optymalizator SQL wymyślił. Czy na tej tabeli jest jedynie ruch insert+select, czy robisz jakieś update/delete?

piotrpo napisał(a):

@katakrowa: Myślę, że przy tym zapytaniu nie ma co patrzeć na resztę bazy danych.

Zapewne explain by wyjaśnił ale jak widać ciężko się doprosić.
3.2 sekundy na tabeli z samymi intami i rzekomymi indeksami to wynik absurdalny.

Niby nie jednak moje typy są następujące:

1. na kolumnie server_id jednak nie ma indeksu;
2. @2solaris5 pobiera wszystkie rekordy bez limitu;
3. skoro to VPS to kolega sam go pewnie konfigurował. Być może są włączone jakieś durne parametry w konfiguracji DB;
4. W sumie nawet nie wiemy czy na ten czas nie składa się czas odpowiedzi z DNS i czas tworzenia połączenia z DB bo wciąż nie wiemy skąd te wartość 3.2 sekundy.

Chciałbym dostać dumpa tej bazy żeby z ciekawości odpalić u siebie.

A może trzeba zrobić 1 indeks z oboma tymi polami? Nie byłbym taki pewien czy 2 osobne będą użyte przy takim zapytaniu

Właśnie o to chodziło @spaghetticoder! Dziękuję bardzo. Do tej pory nie miałem świadomości, że jeden indeks może zawierać dwie kolumny.