MySQL posiada ciekawy mechanizm, który odpowiednio wykorzystany może znacznie przyspieszyć czas wykonywania się zapytań. Mechanizm ten to cache zapytań (query cache), a sprowadza się on do tego, że MySQL przeznacza pewną ilość pamięci na przechowywanie wyników zapytań. Działa to tak, że do cache trafia informacja o jakie zapytanie chodzi i jaki wynik zwróciło. Każde zapytanie jest sprawdzane pod kontem tego, czy jego wynik nie znajduje się w cache. Jeśli tak jest, wynik jest pobierany i przesyłany do klienta bez konieczności wykonywania całego zapytania. Jeśli nie, zapytanie jest normalnie wykonywane. Co ważne, w standardowej wersji MySQL porównywana jest cała treść zapytania. W efekcie zapytanie:

SELECT /* komentarz */ * FROM tabela WHERE kolumna='optymizacja';

jest innym zapytaniem niż:

SELECT * FROM tabela WHERE kolumna='optymizacja';

pomimo, że różnią się one tylko komentarzem. Zmodyfikowane wersje MySQL (jak na przykład Percona Server) potrafią sobie z komentarzami radzić, usuwając je po prostu przed sprawdzaniem cache. Co jest oczywiste, pobranie wyniku z cache jest rozwiązaniem szybszym niż wykonywanie całego zapytania, wydawałoby się, że zwiększanie query cache jest znakomitym sposobem na poprawę wydajności serwera MySQL. Czy tak jest faktycznie?

Niestety, sporo osób administrujących MySQL zapomina, że samo sprawdzenie, czy wynik danego SELECTa jest w cache, jest operacją, która też trochę kosztuje, tym bardziej, że narzut ten dotyczyć będzie każdego wykonywanego na serwerze zapytania. Sprawdźmy jak to wygląda w praktyce.

Tworzymy tabelę o następującej strukturze:

mysql> SHOW CREATE TABLE testqcache\G
*************************** 1. row ***************************
Table: testqcache
Create Table: CREATE TABLE `testqcache` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`nr` int(20) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_nr` (`nr`)
) ENGINE=MyISAM AUTO_INCREMENT=1000001 DEFAULT CHARSET=latin2
1 row in set (0.00 sec)

Zapełniona została tak, aby w kolumnie nr zawierała unikalne wartości od 1 do 1000000. Wydajność testować będę skryptem php, który uruchamia cztery wątki. Każdy z nich wykonuje zapytanie typu:

SELECT * FROM testqcache WHERE nr=$numer;

gdzie wartość zmiennej numer będzie obejmować zakres 1/4 zawartości całej tabeli. Zakresy dla poszczególnych wątków się nie powtarzają (pierwszy to 1 – 250000, drugi 250001 – 500000 i tak dalej). Przed każdą pętlą czyszczona jest zawartość cache zapytań, tak aby kolejne pętle z niej nie korzystały. Wielkość cache jest wystarczająca aby nie było konieczne czyszczenie jej zawartości w trakcie wykonywania zapytań. Wyniki są następujące:

1. cache włączony (query_cache_type=1) – średnia to 10307.7 qps
2. cache wyłączony (query_cache_type=0) – średnia to 20149.7 qps

Co istotne, w test został wykonany na Percona Server w wersji 5.1.49-12. Wersja ta zbudowana jest z patchem umożliwiającym pełne wyłączenie cache zapytań. W przypadku standardowego MySQL ustawienie query_cache_type na 0 nie wyłącza całkowicie cache. Pewne odwołania nadal są wykonywane.
Aby był pełny obraz, sprawdziłem jak wygląda wydajność, gdy dane są pobierane z cache – dla tych danych średni wynik to 26306.8 qps.

Profil zapytania z włączonym cache wygląda następująco:

mysql> SET PROFILING=1 ; SELECT * FROM testqcache WHERE nr=346244; SET PROFILING=0;
Query OK, 0 rows affected (0.00 sec)

+--------+--------+
| id     | nr     |
+--------+--------+
| 346245 | 346244 |
+--------+--------+
1 row in set (0.01 sec)


mysql> SHOW PROFILE FOR QUERY 1;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000008 |
| Waiting on query cache mutex   | 0.000002 |
| checking query cache for query | 0.000026 |
| Opening tables                 | 0.000005 |
| System lock                    | 0.000004 |
| Table lock                     | 0.000004 |
| Waiting on query cache mutex   | 0.000011 |
| init                           | 0.000015 |
| optimizing                     | 0.000010 |
| statistics                     | 0.000087 |
| preparing                      | 0.000013 |
| executing                      | 0.000002 |
| Sending data                   | 0.000029 |
| end                            | 0.000003 |
| query end                      | 0.000002 |
| freeing items                  | 0.000007 |
| Waiting on query cache mutex   | 0.000022 |
| Waiting on query cache mutex   | 0.000001 |
| storing result in query cache  | 0.000014 |
| logging slow query             | 0.000001 |
| cleaning up                    | 0.000002 |
+--------------------------------+----------+
21 rows in set (0.00 sec)

Profil zapytania z wyłączonym cache wygląda następująco:

mysql> SET PROFILING=1 ; SELECT * FROM testqcache WHERE nr=346244; SET PROFILING=0;
Query OK, 0 rows affected (0.00 sec)

+--------+--------+
| id     | nr     |
+--------+--------+
| 346245 | 346244 |
+--------+--------+
1 row in set (0.01 sec)

mysql> SHOW PROFILE FOR QUERY 1;
+--------------------+----------+
| Status             | Duration |
+--------------------+----------+
| starting           | 0.000037 |
| Opening tables     | 0.000012 |
| System lock        | 0.000005 |
| Table lock         | 0.000008 |
| init               | 0.000027 |
| optimizing         | 0.000012 |
| statistics         | 0.000096 |
| preparing          | 0.000019 |
| executing          | 0.000003 |
| Sending data       | 0.000040 |
| end                | 0.000003 |
| query end          | 0.000002 |
| freeing items      | 0.000028 |
| logging slow query | 0.000001 |
| cleaning up        | 0.000003 |
+--------------------+----------+
15 rows in set (0.00 sec)

Widać, że w przypadku zapytania wykorzystującego cache w profilu pojawiają się dodatkowe etapy:

Waiting on query cache mutex
checking query cache for query
storing result in query cache

Łącznie trwają one 0.000058 sekundy, podczas gdy całe zapytanie trwa 0.000268 sekundy.

Jakie z powyższego można wyciągnąć wnioski?

Po pierwsze, metodologia testu była tendencyjna. Pełna zgoda – chodziło o pokazanie sytuacji skrajnej, w której wykonywana jest spora liczba zapytań i żadne z nich nie ma możliwości skorzystania z cache. W takiej sytuacji wyłączenie cache to wzrost wydajności o prawie 100%. Dobrze jest pamiętać o tym przykładzie. Co prawda, w realnym środowisku produkcyjnym aż tak niekorzystnej sytuacji raczej nie zastaniemy, ale niektóre typy zapytań do takiej sytuacji dążą. Zapytania, w których jednym z warunków w WHERE jest timestamp, a które są wykonywane rzadziej niż raz na sekundę, w ogóle z cache nie skorzystają. Jedno zapytanie na sekundę, to nie dużo. Jeśli jednak takie zapytanie jest wykonywane dla każdej z kilkunastu tabel, a baz danych z takimi tabelami jest kilkadziesiąt, zaczyna to wyglądać inaczej.

Po drugie, trzeba znać swoje zapytania – czy są losowe, czy powtarzalne? Dużo szybkich, czy mało dłuższych? W przypadku szybkich zapytań, tak jak w powyższym przykładzie, oczekiwanie związane z obsługą cache zapytań może sięgnąć nawet 20% całości czasu potrzebnego na wykonanie zapytania. Dodatkowo, query cache jest chroniona przez mutex, co sprawia że w przypadku większej ilości jednoczesnych zapytań może stanowić punkt, który hamuje szybkość działania całego serwera – na raz do cache może się odwoływać tylko jeden wątek.

Po trzecie, rozwiązania, które w zamyśle mają zwiększać wydajność, w pewnych, szczególnych sytuacjach mogą być źródłem problemów – wtedy trzeba się ich po prostu pozbyć.