Maksymalne wykorzystanie pamięci MySQL

Chciałbym wiedzieć, jak można ustawić górny limit ilości pamięci używanej przez MySQL na serwerze Linux.

W tej chwili MySQL będzie nadal zajmować pamięć przy każdym nowym zapytaniu, tak że w końcu zabraknie pamięci. Czy istnieje sposób na ustanowienie limitu, aby MySQL nie wykorzystywał więcej niż ta kwota?

Maksymalne wykorzystanie pamięci MySQL w dużej mierze zależy od sprzętu, ustawień i samej bazy danych.

Sprzęt komputerowy

Sprzęt jest oczywistą częścią. Im więcej pamięci RAM tym lepiej, szybciej dyski FTW . Nie wierz jednak w te comiesięczne lub cotygodniowe listy informacyjne. MySQL nie skaluje liniowo - nawet na sprzęcie Oracle. To trochę trudniejsze niż to.

Najważniejsze jest to, że nie ma ogólnej zasady dotyczącej tego, co jest zalecane dla Twojej konfiguracji MySQL. Wszystko zależy od aktualnego wykorzystania lub prognoz.

Ustawienia i baza danych

MySQL oferuje niezliczone zmienne i przełączniki do optymalizacji jego zachowania. Jeśli napotkasz problemy, naprawdę musisz usiąść i przeczytać instrukcję (f'ing).

Jeśli chodzi o bazę danych - kilka ważnych ograniczeń:

• Silnik Stół ( InnoDB, MyISAM...)
• rozmiar
• indeksy
• stosowanie

Większość porad MySQL na temat stackoverflow powie Ci o 5-8 tak zwanych ważnych ustawieniach. Po pierwsze, nie wszystkie z nich mają znaczenie - np. Alokowanie dużej ilości zasobów do InnoDB i nieużywanie InnoDB nie ma większego sensu, ponieważ te zasoby są marnowane.

Lub - wiele osób sugeruje, aby podnieść max_connectionzmienną - cóż, mało wiedzą, że oznacza to również, że MySQL przydzieli więcej zasobów, aby je zaspokoić max_connections- jeśli kiedykolwiek będzie to potrzebne. Bardziej oczywistym rozwiązaniem może być zamknięcie połączenia z bazą danych w DBAL lub obniżenie w wait_timeoutcelu zwolnienia tych wątków.

Jeśli złapiesz mój dryf - jest naprawdę dużo, dużo do przeczytania i nauczenia się.

Silniki

Silniki stołowe to dość ważna decyzja, wiele osób zapomina o nich wcześnie, a potem nagle zaczyna walczyć ze MyISAMstołem o rozmiarze 30 GB, który blokuje całą ich aplikację.

Nie chcę przez to powiedzieć, że MyISAM jest do niczego , ale InnoDBmożna go dostosować, aby odpowiadał prawie lub prawie tak szybko, jak MyISAMi oferuje coś takiego jak blokowanie wierszy, UPDATEpodczas gdy MyISAMblokuje całą tabelę, gdy jest zapisywana.

Jeśli masz swobodę uruchamiania MySQL na własnej infrastrukturze, możesz również chcieć sprawdzić serwer Percona, ponieważ zawiera wiele wkładów od firm takich jak Facebook i Google (wiedzą one szybko), zawiera również własny drop - Percona. w zamian za InnoDBtzw XtraDB.

Zobacz moje podsumowanie konfiguracji percona-server (i -client) (na Ubuntu): http://gist.github.com/637669

Rozmiar

Rozmiar bazy danych jest bardzo, bardzo ważny - wierz lub nie, większość ludzi w Intarwebs nigdy nie radziła sobie z dużą i intensywną konfiguracją MySQL, ale one naprawdę istnieją. Niektórzy ludzie będą trollować i mówić coś w stylu „Użyj PostgreSQL !!! 111”, ale na razie zignorujmy je.

Najważniejsze jest to: sądząc po rozmiarze, należy podjąć decyzję o sprzęcie. Naprawdę nie można sprawić, by baza danych 80 GB działała szybko na 1 GB pamięci RAM.

Indeksy

Nie jest: im więcej, tym weselej. Należy ustawić tylko potrzebne wskaźniki i sprawdzić ich użycie EXPLAIN. Dodaj do tego, że MySQL EXPLAINjest naprawdę ograniczony, ale to dopiero początek.

Sugerowane konfiguracje

O tych my-large.cnfi my-medium.cnfplikach - nawet nie wiem, dla kogo zostały napisane. Skręć swój własny.

Podkład tuningowy

Świetny początek to elementarz tuningowy . Jest to skrypt bash (wskazówka: trzeba linux), który trwa wyjście SHOW VARIABLESi SHOW STATUSi zawija je w miejmy nadzieję użytecznych rekomendacji. Jeśli twój serwer działał jakiś czas, zalecenie będzie lepsze, ponieważ będą dane, na których można je oprzeć.

Jednak baza do strojenia nie jest magicznym sosem. Nadal powinieneś czytać wszystkie zmienne, które proponuje zmienić.

Czytanie

Bardzo lubię polecać blog mysqlperformanceblog . To świetne źródło wszelkiego rodzaju porad związanych z MySQL. I to nie tylko MySQL, oni też dużo wiedzą o odpowiednim sprzęcie lub zalecają konfiguracje dla AWS itp. Ci goście mają wieloletnie doświadczenie.

Kolejnym świetnym zasobem jest oczywiście planet-mysql .

Używamy tych ustawień:

etc/my.cnf
innodb_buffer_pool_size = 384M
key_buffer = 256M
query_cache_size = 1M
query_cache_limit = 128M
thread_cache_size = 8
max_connections = 400
innodb_lock_wait_timeout = 100

dla serwera o następujących specyfikacjach:

Dell Server
CPU cores: Two
Processor(s): 1x Dual Xeon
Clock Speed: >= 2.33GHz
RAM: 2 GBytes
Disks: 1×250 GB SATA

Użycie pamięci bazy danych to złożony temat. MySQL Wydajność Blog ma dobrą pracę pokrycia swoje pytanie, a wykazy wielu powodów jest to niezwykle niepraktyczne „rezerwa” pamięci.

Jeśli naprawdę chcesz nałożyć sztywny limit, możesz to zrobić, ale musisz to zrobić na poziomie systemu operacyjnego, ponieważ nie ma wbudowanych ustawień. W Linuksie możesz użyć ulimit , ale prawdopodobnie będziesz musiał zmodyfikować sposób uruchamiania MySQL, aby to narzucić.

Najlepszym rozwiązaniem jest strojenie serwera, tak aby połączenie zwykłych ustawień pamięci MySQL skutkowało ogólnie mniejszym zużyciem pamięci przez instalację MySQL. Będzie to oczywiście miało negatywny wpływ na wydajność Twojej bazy danych, ale niektóre ustawienia, które możesz zmienić, my.inito:

key_buffer_size
query_cache_size
query_cache_limit
table_cache
max_connections
tmp_table_size
innodb_buffer_pool_size

Zacznę tam i zobaczę, czy możesz uzyskać pożądane wyniki. Istnieje wiele artykułów na temat dostosowywania ustawień pamięci MySQL.

Edytować:

Zauważ, że niektóre nazwy zmiennych uległy zmianie w nowszych wersjach 5.1.x MySQL .

Na przykład:

table_cache

Jest teraz:

table_open_cache

mysqld.exe używał 480 MB pamięci RAM. Okazało się, że dodałem ten parametr do my.ini

table_definition_cache = 400

to zmniejszyło użycie pamięci z ponad 400 000 KB do 105 000 KB

w /etc/my.cnf:

[mysqld]
...

performance_schema = 0

table_cache = 0
table_definition_cache = 0
max-connect-errors = 10000

query_cache_size = 0
query_cache_limit = 0

...

Dobra praca na serwerze z 256 MB pamięci.

Jeśli chcesz zoptymalizować swój kontener docker mysql, może pomóc poniższe polecenie. Udało mi się uruchomić kontener Docker mysql z domyślnego 480 MB do zaledwie 100 MB

docker run -d -p 3306: 3306 -e MYSQL_DATABASE = test -e MYSQL_ROOT_PASSWORD = tooor -e MYSQL_USER = test -e MYSQL_PASSWORD = test -v / mysql: / var / lib / mysql --name mysqldb mysql --table_definition_cache = 100 --performance_schema = 0 --default-authentication-plugin = mysql_native_password