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_type
musi być WŁĄCZONY (1) lub ŻĄDANY (2)query_cache_limit
- określa maksymalny rozmiar wyniku, który trafi do pamięci podręcznejquery_cache_size
być różne od zera. Podczas korzystania z DEMAND buforowane będą tylko żądania zawierające dyrektywęSQL_CACHE;
query_cache_min_res_unit
minimalny 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 rozmiarzequery_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_blocks
pokazuje, 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
„Zaleca się, aby ocenić skuteczność pamięci podręcznej, dzieląc wartość zmiennej Qcache_hits on Qcache_hits + Com_select
, ponieważ podczas przetwarzania żądania zwiększa się licznik Qcache_hits (jeśli żądanie jest przetwarzane z pamięci podręcznej) lub Com_select (jeśli żądanie nie jest buforowane). Ta metoda jest sugerowana w „Mysql Performance Optimization” O'reilly
Jest inny sposób w Internecie
qcache_hit_ratio = qcache_hits / (qcache_hits + qcache_inserts + qcache_not_cached)
Jeśli ta wartość jest > 0,8, to 80% twoich żądań jest buforowanych, co jest bardzo dobrym wskaźnikiem.
Jeśli %
trafienie w pamięci podręcznej jest niskie, musisz zwiększyć wartość query_cache_size
.
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_prunes
zwiększy się tylko nieznacznie. Aby to zrobić, zaleca się porównanie różnicy wartości Qcache_lowmem_prunes
na godzinę i liczby żądań otrzymanych przez mysql w tej samej godzinie.
„W praktyce do obliczenia query_cache_size można użyć jednej z 2 formuł:
query_cache_size = (number of requests for 10 minutes)*(average response volume on request) * 1,2
Lub
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_size
zwiększyliśmy wartości, po czym należy zwrócić uwagę na wartości Qcache_total_blocks
i Qcache_free_blocks
. Qcache_queries_in_cache
MySQL 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_cached
jest 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 = 0
wyłą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 od
SQL_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.