Buforowanie w MySQL

7.1 Buforowanie po stronie DB

MySQL używa wysoce skalowalnych algorytmów podczas pracy z tabelami, więc MySQL może działać nawet przy niewielkiej ilości pamięci. Oczywiście, aby uzyskać lepszą wydajność, potrzebujesz więcej pamięci RAM.

Aby wyświetlić aktualne ustawienia, połącz się z bazą danych

#mysq -u root -p

mysql> show variables like 'query_cache%';
+------------------------------+-----------+
| Variable_name                | Value     |
+------------------------------+-----------+
| query_cache_limit            | 1048576   |
| query_cache_min_res_unit     | 4096      |
| query_cache_size             | 134217728 |
| query_cache_type             | ON        |
| query_cache_wlock_invalidate | OFF       |
+------------------------------+-----------+
5 rows in set (0.00 sec)

Najpierw upewnij się, że buforowanie jest włączone. Zmienny:

• query_cache_typemusi być WŁĄCZONY (1) lub ŻĄDANY (2)
• query_cache_limit- określa maksymalny rozmiar wyniku, który trafi do pamięci podręcznej
• query_cache_sizebyć różne od zera. Podczas korzystania z DEMAND buforowane będą tylko żądania zawierające dyrektywęSQL_CACHE;
• query_cache_min_res_unitminimalny rozmiar przydzielonego bloku pamięci do przechowywania wyników buforowanego zapytania. MySQL nie przechowuje pamięci podręcznej w jednym dużym kawałku pamięci, zamiast tego przydziela bloki o minimalnym rozmiarze query_cache_min_res_unit(domyślnie = 4 KB) na żądanie. Ostatni taki blok jest obcinany do rozmiaru danych, a pozostała pamięć jest zwalniana.

Efektem buforowania jest to, że kiedy serwer otrzymuje żądanie, sprawdza, czy skrót żądania znajduje się w pamięci podręcznej. Jeśli hash się zgadza - serwer natychmiast zwraca wynik - bez parsowania żądania, optymalizacji itp. narzut – któremu towarzyszy mechanizm buforowania – przeglądanie pamięci podręcznej, zapisywanie wyniku zapytania do pamięci podręcznej itp.

A jeśli masz wiele małych żądań w pamięci podręcznej, może to prowadzić do fragmentacji pamięci z powodu dużej liczby wolnych bloków. A to z kolei powoduje usuwanie zapisanych w pamięci podręcznej wpisów z powodu braku pamięci. W takim przypadku sensowne jest zmniejszenie wartości query_cache_min_res_unit. Jeśli większość zapytań generuje duże wyniki, zwiększenie tego ustawienia może poprawić wydajność.

Spróbujmy ocenić efekt. Patrzymy, jak zmieniają się liczniki trafień w pamięci podręcznej (Qcahe_hits), liczba żądań uznanych za nieważne z powodu braku pamięci (Qcache_lowmem_prunes), łączna liczba żądań typu SELECT (i tylko one są buforowane):

#mysq -u root -p
mysql> show status like 'Qcache%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| Qcache_free_blocks      | 715       |
| Qcache_free_memory      | 130369640 |
| Qcache_hits             | 24209     |
| Qcache_inserts          | 16215     |
| Qcache_lowmem_prunes    | 0         |
| Qcache_not_cached       | 444       |
| Qcache_queries_in_cache | 1740      |
| Qcache_total_blocks     | 4225      |
+-------------------------+-----------+
8 rows in set (0.00 sec)

7.2 Bieżący stan pamięci podręcznej

Do monitorowania pamięci podręcznej zapytań używana jest SHOW STATUS:

mysql> SHOW STATUS LIKE 'Qcache_%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 10       |
| Qcache_free_memory      | 16755496 |
| Qcache_hits             | 49812    |
| Qcache_inserts          | 103999   |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 180      |
| Qcache_queries_in_cache | 6        |
| Qcache_total_blocks     | 28       |
+-------------------------+----------+
8 rows in set (0.00 sec)

• Qcache_free_blockspokazuje, ile wolnych bloków znajduje się w pamięci podręcznej (będzie się zmniejszać wraz ze wzrostem żądań w pamięci podręcznej);
• Qcache_total_blocks— liczba zajętych bloków;
• Qcache_free_memory- pokazuje wolną „dostępną” pamięć do buforowania;
• Qcache_hits- liczba żądań, których wyniki zostały pobrane z pamięci podręcznej, bez faktycznego dostępu do bazy danych;
• Qcache_inserts- liczba żądań, które zostały dodane do pamięci podręcznej;
• Qcache_lowmem_prunes- liczba żądań, które zostały usunięte z pamięci podręcznej z powodu braku pamięci;
• Qcache_not_cached- liczba żądań, które nie zostały zapisane w pamięci podręcznej z powodu użycia funkcji zarządzania czasem itp.;
• Qcache_queries_in_cache- liczba żądań znajdujących się w pamięci podręcznej.

Możesz zobaczyć całkowitą liczbę zapytań SELECT:

mysql> show status like 'Com_select';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 16719 |
+---------------+-------+
1 row in set (0.00 sec)

7.3 Wydajność buforowania

qcache_hit_ratio = qcache_hits / (qcache_hits + qcache_inserts + qcache_not_cached)

Bieżącą wartość można wyświetlić w następujący sposób:

SHOW VARIABLES LIKE 'query_cache_size';

Ponownie pojawia się pytanie: jak dobrać odpowiednią wartośćquery_cache_size?

To pomoże Qcache_lowmem_prunes. Ta zmienna przechowuje liczbę żądań, które zostały usunięte z pamięci podręcznej z powodu konieczności buforowania nowych żądań. Konieczne jest dążenie do takiego rozmiaru pamięci podręcznej, przy którym Qcache_lowmem_pruneszwiększy się tylko nieznacznie. Aby to zrobić, zaleca się porównanie różnicy wartości Qcache_lowmem_prunesna godzinę i liczby żądań otrzymanych przez mysql w tej samej godzinie.

query_cache_size = (number of requests for 10 minutes)*(average response volume on request) * 1,2

query_cache_size = (number of requests for 10 minutes)*(average response volume on request) * 1,2
query_cache_size = (natężenie ruchu w ciągu 10 minut) * 1,2 "

Spowoduje to buforowanie żądań przez 10 minut + dodatkowe 20% pamięci na fragmentację pamięci podręcznej i dodatkową rezerwę buforowania

Możesz obliczyć liczbę i średni wolumen odpowiedzi na żądanie, używając odpowiednio zmiennych Bytes_sent

I tak query_cache_sizezwiększyliśmy wartości, po czym należy zwrócić uwagę na wartości Qcache_total_blocksi Qcache_free_blocks. Qcache_queries_in_cacheMySQL przechowuje pamięć podręczną w blokach. Na 1 żądanie potrzebne są 2 bloki: jeden na sam tekst żądania, drugi na wynik.

Jeśli weźmiemy pod uwagę tabelę z wartościQcache%

Całkowita liczba bloków pamięci podręcznejQcache_total_blocks – 28

Żądanie 6 jest teraz buforowane, co oznacza, że ​​6 * 2 = 12 bloków jest zajętych

Darmowe bloki Qcache_free_blocks – 10. Im więcej bezczynności Qcache_free_blocks, tym większy stopień „fragmentacji” pamięci podręcznej.

Jeśli większość zapytań ma niewielką ilość danych wynikowych, warto zmniejszyć minimalny rozmiar bloku pamięci podręcznej query_cache_min_res_unit, który domyślnie wynosi 4 KB.

Jeśli większość żądań zwraca dużo danych, warto zwiększyć rozmiar bloku pamięci podręcznej.

Najważniejsze jest osiągnięcie minimalnej wartości Qcache_free_blocks.

Jeśli licznik Qcache_not_cachedjest duży, możesz spróbować zwiększyć zmienną query_cache_limit- pozwoli to zwiększyć limit i buforować wyniki zapytań, które „nie pasują”.

Za korzystanie z pamięci podręcznej zapytań odpowiadają następujące zmienne konfiguracyjne:

• query_cache_size— rozmiar pamięci podręcznej zapytań. query_cache_size = 0wyłącza użycie pamięci podręcznej;
• query_cache_limit- rozmiar maksymalnej próbki przechowywanej w pamięci podręcznej;
• query_cache_wlock_invalidate- określa, czy dane zostaną pobrane z pamięci podręcznej, jeśli tabela, do której należą, jest zablokowana do odczytu.
=

Aby włączyć buforowanie zapytań mysql, wystarczy dodać następujące wiersze do pliku my.cnf (sekcja [mysqld]):

query_cache_size = 268435456
query_cache_type =1 
query_cache_limit =1 048576

I uruchom ponownie usługę.

7.4 Gdzie pamięć podręczna nie jest używana

Nie buforowane:

• Żądania odSQL_NO_CACHE
• Przygotowane zapytania(Prepared statements);
• Zapytania będące podzapytaniami zapytania zewnętrznego;
• Zapytania wewnątrz procedur składowanych i funkcji;
• Zapytania korzystające z funkcji:

  BENCHMARK (), CONNECTION_ID (), CONVERT_TZ (), CURDATE (), CURRENT_DATE (), CURRENT_TIME (), CURRENT_TIMESTAMP (), CURTIME (), DATABASE (), ENCRYPT ()z jednym argumentem, FOUND_ROWS (), GET_LOCK (), LAST_INSERT_ID (), LOAD_FILE (), MASTER_POS_WAIT (), NOW (), RAND (), RELEASE_LOCK (), SLEEP (), SYSDATE (), UNIX_TIMESTAMP ()bez argumentów, USER (),UUID ();

• Zapytania używające przechowywanych funkcji, zmiennych użytkownika lub odwołujących się do tabel w systemowych bazach danych mysql lub INFORMATION_SCHEMA;
• • Wnioski w następujących formach:
• SELECT ... IN SHARE MODE
• SELECT ... FOR UPDATE
• SELECT ... INTO OUTFILE ...
• SELECT ... INTO DUMPFILE ...
• SELECT * FROM ... WHERE autoincrement_col IS NULL
• Zapytania z tabelami tymczasowymi lub w ogóle bez korzystania z tabel;
• Żądania generujące ostrzeżenia (ostrzeżenia);

Możesz zdefragmentować pamięć podręczną za pomocą polecenia:

mysql>flush query cache;

Wyczyść - polecenie:

mysql>flush query cache;

Najważniejsze

Nigdy nie pracuj w środowisku produkcyjnym z ustawieniami domyślnymi. Spowoduje to, że większość zasobów serwera nie będzie używana. Właściwe dostrojenie MySQL może kilkakrotnie poprawić wydajność bazy danych. To nie tylko przyspieszy aplikację, ale także poradzi sobie z dużym obciążeniem.