Co ustawić innodb_buffer_pool i dlaczego ..?


20

Mam 170 GB InnoDBindeksu i danych.

Muszę ponownie dopasować rozmiar innodb_buffer_pool, aby uzyskać lepszą wydajność. Maksymalny rozmiar tabeli InnoDB (indeks + dane) wynosi 28 GB.

Więc jaki powinien być optymalny rozmiar innodb_buffer_pool.

AKTUALIZACJA

zamierzamy migrować naszą lokalną bazę danych do ec2, więc ustawimy pamięć RAM zgodnie z bieżącymi statystykami innodb, dlatego potrzebuję wielkości puli buforów, abyśmy mogli tam mieć dostępną pamięć RAM.

Plik na tabelę jest włączony.

Używam maszyny z systemem Linux.


Jaka pamięć RAM na komputerze nie jest używana przez inne procesy? Używasz Windowsa, Linuksa czy czegoś innego? Jakiej wersji MySQL używasz? Dlaczego rozmiar stołu jest ograniczony do 28 GB?
Craig Efrein

Rozmiar stołu nie jest ograniczony .. Podałem rozmiar stołu do jednego punktu czasu. Wzrośnie, ponieważ będą wstawki w przyszłości ..
Abdul Manaf

Odpowiedzi:


25

Największa posiadana tabela stanowi 16,47% (28/170) wszystkich danych. Nawet jeśli tabela została wysoko napisana i przeczytana, nie wszystkie 28G tabeli jest ładowane do puli buforów w danym momencie. To, co musisz obliczyć, to ile puli buforów InnoDB jest ładowane w danym momencie na bieżącym serwerze DB .

Oto bardziej szczegółowy sposób określenia wielkości innodb_buffer_pool_size dla nowego serwera DB, biorąc pod uwagę zestaw danych aktualnie załadowany do puli buforów InnoDB bieżącego serwera DB.

Uruchom następujące polecenie na bieżącej instancji MySQL (serwer, z którego migrujesz)

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_data';  -- IBPDataPages 
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_total'; -- IBPTotalPages
SHOW GLOBAL STATUS LIKE 'Innodb_page_size'; -- IPS

Uruchom formułę IBPPctFull = IBPDataPages * 100.0 / IBPTotalPages.

SET @IBPDataPages = (SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_data'); -- SELECT @IBPDataPages;
SET @IBPTotalPages = (SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_total'); -- SELECT @IBPTotalPages;
SET @IBPPctFull = CAST(@IBPDataPages * 100.0 / @IBPTotalPages AS DECIMAL(5,2));
SELECT @IBPPctFull;

Jeśli wartość IBPPctFull wynosi 95% lub więcej, należy ustawić parametr innodb_buffer_pool_size na 75% pamięci RAM serwera DB.

Jeśli IBPPctFull jest mniejsza niż 95%, należy uruchomić wzoru: IBPSize = IPS X IBPDataPages / (1024*1024*1024) X 1.05.

SET @IBPSize = (SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME = 'Innodb_page_size'); -- SELECT @IBPSize;
SET @IBPDataPages = (SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_data'); -- SELECT @IBPDataPages;
SET @IBPSize = concat(ROUND(@IBPSize * @IBPDataPages / (1024*1024*1024) * 1.05, 2), ' GB' );
SELECT @IBPSize;

Liczba dla IBPSize (w GB) to liczba, która bardziej pasuje do rzeczywistego zestawu danych roboczych.

Teraz, jeśli IBPSize jest wciąż zbyt duży dla największej konfiguracji pamięci RAM Amazon EC2, użyj 75% pamięci RAM dla serwera DB Amazon EC2.


To naprawdę świetna informacja. Jeszcze jedną rzecz muszę wiedzieć, w jaki sposób MySQL Server ładuje dane i indeksy w puli buforów. Przejrzałem dokumentację i dowiedziałem się, że używa LRU, ale jak ładuje je w pamięci, tak jak w przypadku, gdy mam 2 GB dla pula buforów, ale wielkość tabeli żądana w zapytaniu jest znacznie większa niż ta .. ??
Abdul Manaf,

Tylko pula danych i strony indeksowe potrzebne do spełnienia zapytania są ładowane do puli buforów, a nie cała tabela. Stare dane i strony indeksu są obracane na podstawie algorytmu LRU wymienionego w dev.mysql.com/doc/refman/5.5/en/innodb-buffer-pool.html
RolandoMySQLDBA

Abdul, powinieneś iść z odpowiedzią Rolando, która lepiej odpowiada na twoje pytanie niż moje. Mam zamiar przepisać własną odpowiedź tylko dlatego, że muszę być jaśniejszy.
Craig Efrein

13

Podaję tę odpowiedź jako informację uzupełniającą do odpowiedzi Rolando poniżej.

Przed rozpoczęciem produkcji serwera

Oblicz innodb_buffer_pool_size na podstawie największych tabel najczęściej używanych przez MySQL. Aby zidentyfikować największe tabele na podstawie ich wielkości w bazie danych, możesz użyć tego skryptu:

select table_schema, table_name, round(data_length/1024/1024,2) as size_mb 
from information_schema.tables where table_schema like 'my_database' 
order by size_mb desc;


+--------------+-------------------------+---------+
| table_schema | table_name              | size_mb |
+--------------+-------------------------+---------+
| heavybidder     | user                 |   522.55|
| heavybidder     | bid                  |   121.52|
| heavybidder     | item_for_sale        |    10.52|
| heavybidder     | account_user         |    5.02 |
| heavybidder     | txn_log              |    4.02 |
| heavybidder     | category             |    0.02 |
+--------------+-------------------------+---------+

Teraz, gdy wiemy, które tabele są największe w naszej bazie danych, musimy ustalić, które są najczęściej używane. Aby to zrobić, użyłbym programu do profilowania, takiego jak Jet Profiler (JP), aby sprawdzić, które tabele są najczęściej używane. JP pokaże Ci, które tabele są najczęściej używane. Oto zrzut ekranu z tej sekcji w JP

wprowadź opis zdjęcia tutaj

Mając to na uwadze, teraz wiem, że tabele użytkowników i stawek zajmują około 640 MB miejsca na dysku, są bardzo często używane zgodnie z JP, co oznacza, że ​​MySQL będzie przechowywać swoje indeksy i dane w puli buforów jako Rolando wspomina poniżej w swoich komentarzach.

Aby upewnić się, że MySQL ma wystarczającą ilość pamięci do przechowywania danych dla moich największych i najczęściej używanych tabel, zdefiniowałbym innodb_buffer_pool_size na 640 MB.

Istnieją pewne dodatkowe uwagi, ale nie dotyczą one rozmiaru innodb_buffer_pool_size.

Czy to system 32-bitowy czy 64-bitowy? W systemie 32-bitowym jesteś ograniczony do 4 GB, chyba że aktywujesz PAE. W systemie Windows oznacza to uruchamianie wersji Windows Enterprise lub Datacenter.

Ile pamięci potrzebują inne procesy uruchomione w systemie? Na dedykowanym serwerze MySQL pozostawię od 5% do 10% na system operacyjny. W systemie Windows możesz użyć Process Explorer do analizy zużycia pamięci. W Linuksie masz sysstat, free, htop, top i vmstat.

Czy baza danych składa się tylko z tabel Innodb czy mieszanki Innodb i MyISAM? Jeśli jest to mieszanka tych dwóch elementów, odłożę pamięć na pamięć podręczną_klucza, połącz zmienne, pamięć podręczną zapytań itp. Możesz później obliczyć współczynnik trafień MyISAM po uruchomieniu serwera.

Po uruchomieniu serwera

Jaki jest obecny współczynnik trafień dla Innodb?

1 - (innodb_buffer_pool_reads / innodb_buffer_pool_read_requests).

Jaki jest współczynnik trafień w pamięć podręczną kluczy

1 - (Key_reads / Key_read_requests)

Zazwyczaj staram się uzyskać stosunek tak bliski 100%, jak to możliwe.

Jak dobrze twoje tabele mieszczą się w puli buforów

Możesz także sprawdzić, jak dobrze dane tabeli mieszczą się w puli buffer_pool, odwołując się do tego linku, który pozwala pokazać, „ile stron w puli buforów dla danej tabeli (cnt), ile z nich jest brudnych (brudnych) , a jaki procent dopasowań indeksu mieści się w pamięci (fit_pct). " Dotyczy tylko serwera Percona

http://www.mysqlperformanceblog.com/2010/12/09/how-well-does-your-table-fits-in-innodb-buffer-pool/

Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.