MySQL: Jak mogę obniżyć „Maksymalne możliwe użycie pamięci”?

Niedawno miałem problemy z thrashowaniem w wyniku braku pamięci. (Mój VPS ma łącznie 256 mln)

Próbuję dostroić MySQL za pomocą mysqltuner.pl i uzyskać następujące wyniki:

-------- Statystyki ogólne ---------------------------------------- ----------
[-] Pominięto sprawdzanie wersji skryptu MySQLTuner
[OK] Aktualnie działająca obsługiwana wersja MySQL 5.0.51a-3ubuntu5.4-log
[OK] Działa w architekturze 64-bitowej

-------- Statystyki silnika pamięci masowej --------------------------------------- ----
[-] Status: + Archiwum -BDB -Federated -InnoDB -ISAM -NDBCluster 
[-] Dane w tabelach MyISAM: 114 mln (tabele: 454)
[!!] Suma podzielonych tabel: 34

-------- Wskaźniki wydajności ---------------------------------------- ---------
[-] Up: 40 s (570 q [14.250 qps], 23 conn, TX: 154K, RX: 23K)
[-] Odczyt / zapis: 100% / 0%
[-] Całkowita liczba buforów: 338,0 mln globalnych + 2,7 mln na wątek (maks. 20 wątków)
[!!] Maksymalne możliwe użycie pamięci: 392,9 mln (153% zainstalowanej pamięci RAM)
[OK] Wolne zapytania: 0% (5/570)
[OK] Najwyższe wykorzystanie dostępnych połączeń: 15% (3/20)
[!!] Rozmiar bufora klucza / całkowite indeksy MyISAM: 8,0 mln / 9,4 mln
[!!] Wskaźnik trafień bufora klucza: 57,1% (7 pamięci podręcznych / 3 odczyty)
[OK] Wydajność bufora zapytań: 21,9% (7 z pamięci podręcznej / 32 wybory)
[OK] Zapytanie ściąga pamięć podręczną dziennie: 0
[OK] Sortowanie wymagające tabel tymczasowych: 0% (0 sortowania temp / 1 sortowania)
[OK] Tabele tymczasowe utworzone na dysku: 0% (0 na dysku / łącznie 32)
[OK] Współczynnik trafień w pamięci podręcznej wątków: 86% (3 utworzone / 23 połączenia)
[OK] Współczynnik trafień w pamięci podręcznej tabeli: 26% (128 otwartych / 484 otwartych)
[OK] Zastosowano limit otwartych plików: 25% (259 / 1K)
[OK] Blokady tabeli nabyte natychmiast: 100% (492 natychmiast / 492 blokad)

-------- Zalecenia ----------------------------------------- ------------
Ogólne zalecenia:
    Uruchom OPTIMIZE TABLE, aby defragmentować tabele w celu uzyskania lepszej wydajności
    MySQL zaczął działać w ciągu ostatnich 24 godzin - zalecenia mogą być niedokładne
    Zmniejsz ogólny ślad pamięci MySQL dla stabilności systemu
Zmienne do dostosowania:
  *** Maksymalne użycie pamięci MySQL jest niebezpiecznie wysokie ***
  *** Dodaj RAM przed zwiększeniem zmiennych bufora MySQL ***
    key_buffer_size (> 9,4 mln)

Ale jestem trochę zdezorientowany, jak zmniejszyć maksymalne zużycie pamięci? Wygląda na to, że opiera się na key_buffer i max_connections, ale musi być coś jeszcze zaangażowanego?

my.cnf:

key_buffer = 8 mln
max_allowed_packet = 12 mln
thread_stack = 128 KB
thread_cache_size = 8
max_connections = 20
table_cache = 128
tmp_table_size = 256 M.
max_heap_table_size = 256M
join_buffer_size = 256 KB
query_cache_limit = 8M
query_cache_size = 64M

Próbowałem przeczytać artykuły na temat strojenia MySQL, ale wydają się one skierowane do osób, które już wiedzą, co robią! Każda pomoc będzie mile widziana. Dzięki!

Masz serwer o pojemności 256 MB, ale nie możesz z tego wszystkiego korzystać - pamiętaj, że jest narzut na system operacyjny. Dodaj do tego fakt, że nie angażujesz się tak, jak wspominali inni ludzie, i na pewno będziesz tu rzucał. 256M wystarcza tylko na małą DB, 20 połączeń to dużo z tym, co skonfigurowałeś.

1) zmniejsz maksymalną liczbę połączeń do 4 (używasz 3 z 20)

2) lepiej zoptymalizuj pamięć podręczną zapytań; 8M jest naprawdę duże, a 64M ogółem to dużo na podstawie twoich hitów / suszonych śliwek; wypróbuj kombinację 4/32 i zobacz, jak idzie. Naprawdę myślę, że kombinacja 2/24 będzie dla ciebie odpowiednia.

3) nie masz rodzajów wymagających tabel tymczasowych, dlaczego jest tam czasownik max_heap_table_size? Skomentuj to, użyj ustawień domyślnych

4) czy faktycznie masz 128 stolików? Spróbuj wyciąć ten table_cache na pół do 64 lub 48

5) zmniejsz rozmiar wątku_pamięci_cache do 4

6) zoptymalizuj te tabele, aby zmniejszyć fragmentację

Oto niektóre rzeczy na początek. Wygląda na to, że rzuciłeś kilka liczb w konfiguracji bez żadnego faktycznego profilowania, aby wiedzieć, czego potrzebujesz i stworzyć bałagan; jeśli wszystko inne zawiedzie, wróć do ustawień domyślnych i pozbądź się niestandardowych ustawień i zacznij od nowa, korzystając z przewodników dostrajania wydajności dostępnych w Google. Uzyskaj wyniki POKAŻ ZMIENNE i POKAŻ STATUS, znajdź jeden z bajillionowych przewodników dostrajania i podłącz swoje rzeczywiste liczby rzeczywiste do ich równań, a to powie ci dokładne liczby, które musisz umieścić w pliku konfiguracyjnym.

Nie jestem guru MySQL i nie mogę zdiagnozować problemu z tymi informacjami, ale próbowałem wyszukać formułę w kodzie źródłowym. Oto on:

server_buffers + total_per_thread_buffers * max_connections

Gdzie:

server_buffers = key_buffer_size + innodb_buffer_pool_size + innodb_additional_mem_pool_size + innodb_log_buffer_size + query_cache_size

i:

total_per_thread_buffers = read_buffer_size + read_rnd_buffer_size + sort_buffer_size + thread_stack + max_allowed_packet + join_buffer_size

Teraz musisz sprawdzić każdą z tych wartości i dowiedzieć się, która z nich odpowiada za tak ogromną liczbę. I nie ufaj temu skryptowi bez zastrzeżeń - próbowałem uruchomić go na jednym z moich serwerów DB i obliczyłem, że maksymalna pamięć to 140% całkowitej ilości pamięci fizycznej, ale system działa od lat bez żadnych problemów ze stabilnością.

Powodzenia!

Jeśli dobrze pamiętam, MySQL Tuner używa następującej formuły do ​​oszacowania maksymalnego użycia:

read_buffer_size + read_rnd_buffer_size + sort_buffer_size + thread_stack + join_buffer_size

Należy pamiętać, że nie jest to w 100% poprawne i jest w rzeczywistości jedynie szacunkiem, ponieważ niektóre ustawienia w MySQL nie mają określonego limitu.

Możesz zacząć zmniejszać niektóre ustawienia w pliku konfiguracyjnym i ponownie uruchomić tuner, ale radzę uzyskać pomoc eksperta, jeśli nie masz czasu marnować zmiany pliku my.cnf, ponownego uruchomienia go i uruchomienia tunera.

Korzystanie z oprogramowania mysqlcalculator.com może zaoszczędzić wiele godzin.