Najlepsze ustawienia pamięci podręcznej MySQL dla dedykowanego serwera MySQL 8 GB pamięci RAM używającego tylko InnoDB (baza danych 5 GB)

Jestem dość wielkim noobem, jeśli chodzi o konfigurację MySQL pod kątem wydajności. I szczerze mówiąc, nie martwię się o precyzyjne dostrojenie, aby wycisnąć każdą ostatnią wydajność z MySQL, ale wiem, że najważniejszą rzeczą, która zapewnia jedne z najlepszych rezultatów, jest prawidłowe konfigurowanie buforów / buforów.

Próbowałem uprościć sprawę, używając tylko InnoDB jako silnika pamięci. Mam dedykowany serwer dla MySQL. Ma 8 GB pamięci RAM, jak mam to przydzielić, aby zmaksymalizować wydajność? Chciałbym móc dopasować całą bazę danych do pamięci w celu uzyskania najlepszej wydajności. Baza danych ma około 5 GB. czy to możliwe?

Ile pamięci powinienem przeznaczyć na pamięć podręczną zapytań? Ile kosztuje pula buforów InnoDB? Ile kosztuje reszta komputera (tj. Procesy niezwiązane z MySQL)? Itp.

Ponieważ nie używam MyISAM, tak naprawdę nie muszę wkładać dużo pamięci do pamięci podręcznej kluczy, prawda?

Jest to trudne bez wiedzy o samej bazie danych. Jest kilka narzędzi, o których powinieneś wiedzieć;

• http://mysqltuner.pl/
• MySQL Tuning Primer

O przechowywaniu całej bazy danych w pamięci; Wszelkie zapytania, które dokonują zmian w bazie danych, pozostaną otwarte, dopóki zapis nie zostanie wykonany na dysku. Jedyną rzeczą, która może uniknąć wąskiego gardła dysku, jest kontroler dysku z pamięcią podręczną zapisu.

Zacznę od następujących zmian w stosunku do wartości domyślnych:

key_buffer_size = 128M
thread_stack = 128K
thread_cache_size = 8
table_cache = 8192
max_heap_table_size = 256M
query_cache_limit = 4M
query_cache_size = 512M

innodb_buffer_pool_size = 4G 

# This is crucial to avoid checkpointing all the time:
innodb_log_file_size = 512M

# If you have control on who consumes the DB, and you don't use hostnames when you've set up permissions - this can help as well.
skip_name_resolve

Potem zobaczyłem, jak się sprawy mają, i wypróbowałem różne rzeczy na podstawie (między innymi) wyników wyżej wymienionych narzędzi. Chciałbym również sporządzić wykres trendów za pomocą narzędzia do monitorowania, takiego jak Munin lub Cacti , aby zobaczyć, z jakim obciążeniem faktycznie mam do czynienia. Osobiście mam duże doświadczenie z wtyczkami MySQL dostarczonymi z Munin.

IMHO powinieneś być w stanie iść z

innodb_buffer_pool_size=5G

To byłoby 62,5% pamięci RAM z wystarczającą ilością pamięci RAM dla systemu operacyjnego serwera oraz pamięcią dla połączeń DB

@kvisle zaleca korzystanie z mysqltuner.pl. Ten skrypt jest doskonały do ​​oceny ilości pamięci RAM, którą należy poświęcić na rozmiar_łączenia_buforu, rozmiar_buforu sortowania, rozmiar_buforu odczytu i rozmiar_buforu odczytu. Te 4 bufory dodane razem są mnożone przez max_connections. Ta odpowiedź jest dodawana do buforów statycznych (innodb_buffer_pool_size + key_buffer_size). Połączone kwoty są zgłaszane. Jeśli ta łączna suma przekracza 80% pamięci RAM, wówczas należy zmniejszyć te rozmiary buforów. mysqltuner.pl będzie bardzo pomocny w tym zakresie.

Ponieważ wszystkie dane to InnoDB, możesz bardzo zmniejszyć key_buffer_size (bufor pamięci podręcznej dla indeksów MyISAM) (zalecam 64M).

Oto post, który napisałem w DBA StackExchange, aby obliczyć zalecany rozmiar innodb_buffer_pool_size .

AKTUALIZACJA 15.10.2011 19:55 EDT

Jeśli wiesz, że będziesz mieć 5 GB danych, to moja pierwsza rekomendacja była OK. Jednak zapomniałem dodać jedną rzecz:

[mysqld]
innodb_buffer_pool_size=5G
innodb_log_file_size=1280M

Rozmiar pliku dziennika musi wynosić 25% puli buforów InnoDB

AKTUALIZACJA 16.10.2011 13:36 EDT

Reguła 25% opiera się wyłącznie na dwóch plikach dziennika. Chociaż możliwe jest użycie wielu plików dziennika innodb, dwa zwykle działają najlepiej.

Inni wyrazili przy użyciu 25%

• http://lists.mysql.com/mysql/203879
• http://teaching-2.cs.uml.edu/MySQLdocs/MySQL_Manual/InnoDB_start.html
• http://www.issociate.de/board/post/395589/innodb_buffer_pool_size._How_to_setup_with_more_than_5G_?.html

Jednak, szczerze mówiąc, ktoś z oryginalnej firmy InnoBase Oy wyraził opinię, że nie stosuje reguły 25% z powodu posiadania większej puli buforów InnoDB .

Oczywiście reguła 25% nie może działać, gdy ma się dużo pamięci RAM. W rzeczywistości największy dozwolony rozmiar pliku dziennika_wpisu_logowania przy użyciu tylko 2 plików dziennika to 2047 mln, ponieważ łączny rozmiar pliku dziennika musi być mniejszy niż 4G (4096 mln)

PRZYPADEK: Jeden z moich klientów pracodawcy ma serwer DB z 192 GB pamięci RAM. Nie ma możliwości posiadania plików dziennika 48G. Po prostu używam maksymalnego rozmiaru pliku dziennika innodb, 2047M. Komentarz @ Kvisle do mojej odpowiedzi zawiera po prostu link stwierdzający, że nie musisz ograniczać się do dwóch plików dziennika. Jeśli masz N plików dziennika, nie mogą one łącznie 4G. Moja zasada 25% jest po prostu w idealnym świecie (serwer DB z 8 GB lub mniej).