Dziś, pokrótce, chciałbym opisać pewne parametry, które pojawiają się w statusie MySQL, a które mogą być bardzo przydatne do zorientowania się, co słychać w bazie danych. Dane te można odczytać przy pomocy polecenia SHOW STATUS, informacja w nich zawarta dotyczy tego, co dokładnie działo się na poziomie silnika bazodanowego. Mowa będzie dziś o danych zawartych w parametrach Handler_read_*.
Jak pisałem, parametry te przechowują informację o tym, co dzieje się na poziomie silnika bazodanowego. Oznacza to tyle, że dzięki nim MySQL udostępnia informację o tym, w jaki sposób rekordy były odczytywane przez silnik. Dla każdego administratora baz danych tego typu informacja to skarb – dzięki niej można ocenić jak wygląda charakterystyka ruchu w danym serwerze MySQL. Jakie dane możemy odczytać?
Handler_read_first – zawiera informację o tym, jak często odczytany został pierwszy rekord w indeksie. Jeśli pojawia się tu duża liczba, może to wskazywać na to, iż często wykonywane są pełne skany indeksów. W przypadku takiego skanu, co zrozumiałe, pierwszy rekord w indeksie będzie odczytany (bo odczytywane są wszystkie rekordy).
Przykład:
*************************** 1. row ***************************
Table: actor
Create Table: CREATE TABLE `actor` (
`actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`first_name` varchar(45) NOT NULL,
`last_name` varchar(45) NOT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`actor_id`),
KEY `idx_actor_last_name` (`last_name`)
) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8
1 row in set (0,00 sec)
mysql> FLUSH STATUS;
Query OK, 0 rows affected (0,00 sec)
mysql> SHOW SESSION STATUS LIKE 'Handler_read%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 0 |
| Handler_read_key | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
+-----------------------+-------+
6 rows in set (0,00 sec)
SELECT last_name from actor;
[cut]
200 rows in set (0,00 sec)
mysql> SHOW SESSION STATUS LIKE 'Handler_read%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 1 |
| Handler_read_key | 2 |
| Handler_read_next | 200 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
+-----------------------+-------+
6 rows in set (0,00 sec)
mysql> EXPLAIN SELECT last_name from actor\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: actor
type: index
possible_keys: NULL
key: idx_actor_last_name
key_len: 137
ref: NULL
rows: 200
Extra: Using index
1 row in set (0,02 sec)
Jak widać z planu zapytania, wykonany został pełny skan indeksu idx_actor_last_name. W zmiennych Handler_read_* widzimy, że odczytany został pierwszy rekord indeksu. Pojawia się także informacja o 200 kolejnych rekordów odczytanych z indeksu. Ciekawe jest pojawienie się wartości 2 w przypadku Handler_read_key – oznaczałoby to, że MySQL odczytał dwa rekordy na podstawie indeksu (czyli odczytane zostały także dane z tabeli, pomimo że teoretycznie wszystko co trzeba znajduje się w indeksie i dostęp do tabeli nie jest konieczny). Przetestowałem także prostszą tabelę:
Query OK, 0 rows affected (0,04 sec)
mysql> INSERT INTO test VALUES (1,1), (2,1), (3,2),(4,10);
Query OK, 4 rows affected (0,00 sec)
Rekord?w: 4 Duplikatów: 0 Ostrzeżeń: 0
mysql> FLUSH STATUS;
Query OK, 0 rows affected (0,00 sec)
mysql> SELECT a FROM test;
+------+
| a |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
+------+
4 rows in set (0,00 sec)
mysql> SHOW SESSION STATUS LIKE 'Handler_read%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 1 |
| Handler_read_key | 0 |
| Handler_read_next | 4 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
+-----------------------+-------+
6 rows in set (0,00 sec)
Jak widać, w tym przypadku wszystko się zgadza. Wykonano jeden pełny skan tabeli, odczytano cztery kolejne rekordy w indeksie.
Pełny skan indeksu to z pewnością nie jest najbardziej optymalne rozwiązanie i jest to pewne obciążenie dla procesora. Nie zmienia to faktu, że lepiej jest skanować indeks a nie całą tabelę. Podstawowa różnica jest taka, że indeks jest zazwyczaj mniejszy – mniej danych do przeanalizowania to mniejsze obciążenie.
Kolejna zmienna to Handler_read_key. Informuje ona o tym, ile razy odczytany został z tabeli rekord wybrany na podstawie danych z indeksu. Czyli w skrócie, ile razy indeks został poprawnie wykorzystany.
Query OK, 0 rows affected (0,00 sec)
mysql> SELECT * FROM actor WHERE last_name='MOSTEL';
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update |
+----------+------------+-----------+---------------------+
| 7 | GRACE | MOSTEL | 2006-02-15 04:34:33 |
| 99 | JIM | MOSTEL | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
2 rows in set (0,00 sec)
mysql> SHOW SESSION STATUS LIKE 'Handler_read%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 0 |
| Handler_read_key | 2 |
| Handler_read_next | 2 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
+-----------------------+-------+
6 rows in set (0,00 sec)
mysql> EXPLAIN SELECT * FROM actor WHERE last_name='MOSTEL'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: actor
type: ref
possible_keys: idx_actor_last_name
key: idx_actor_last_name
key_len: 137
ref: const
rows: 2
Extra: Using where
1 row in set (0,00 sec)
Jak widzimy, silnik MyISAM wykonał dwie operacje odczytania danych na podstawie wpisu w indeksie – jest to zrozumiałe, gdyż nie miał możliwość zrealizowania tego zapytania przy pomocy samego indeksu. Zwiększyły się także wartości Handler_read_next, co mogłoby świadczyć o tym, że za każdym razem sprawdzany był także następny rekord w indeksie.
Duża wartość obok Handler_read_key jest oznaką, że indeksy są wykorzystywane i ogólnie zapytania są poprawnie poindeksowane.
Kolejny parametr to Handler_read_last – pojawił się on w MySQL 5.5.7. Podaje on informację ile razy odczytywany był ostatni rekord w indeksie. Przekłada się to na ilość ORDER BY kolumna DESC, jako że w tym przypadku pierwszym odczytywanym rekordem z indeksu jest właśnie rekord ostatni.
Handler_read_next występował już we wcześniejszych przykładach. Podaje on informację o tym, ile razy pobrany został kolejny rekord z indeksu. Sytuacja taka występuje gdy wykonywany jest skan indeksu, bądź gdy wykonywane jest zapytanie typu RANGE, czyli pobieramy pewien ciąg danych:
Query OK, 0 rows affected (0,00 sec)
mysql> SELECT * FROM actor WHERE actor_id > 10 AND actor_id < 20;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update |
+----------+------------+-----------+---------------------+
| 11 | ZERO | CAGE | 2006-02-15 04:34:33 |
| 12 | KARL | BERRY | 2006-02-15 04:34:33 |
| 13 | UMA | WOOD | 2006-02-15 04:34:33 |
| 14 | VIVIEN | BERGEN | 2006-02-15 04:34:33 |
| 15 | CUBA | OLIVIER | 2006-02-15 04:34:33 |
| 16 | FRED | COSTNER | 2006-02-15 04:34:33 |
| 17 | HELEN | VOIGHT | 2006-02-15 04:34:33 |
| 18 | DAN | TORN | 2006-02-15 04:34:33 |
| 19 | BOB | FAWCETT | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
9 rows in set (0,00 sec)
mysql> SHOW SESSION STATUS LIKE 'Handler_read%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 0 |
| Handler_read_key | 2 |
| Handler_read_next | 9 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
+-----------------------+-------+
6 rows in set (0,00 sec)
mysql> EXPLAIN SELECT * FROM actor WHERE actor_id > 10 AND actor_id < 20\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: actor
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 2
ref: NULL
rows: 8
Extra: Using where
1 row in set (0,03 sec)
Handler_read_prev to informacja, ile rekordów zostało odczytanych z indeksu w odwrotnej kolejności. Sytuacja taka występuje, gdy wykorzystujemy indeks do optymalizacji sortowania typu DESC:
Query OK, 0 rows affected (0,00 sec)
mysql> SELECT a FROM test ORDER BY a DESC;
+------+
| a |
+------+
| 4 |
| 3 |
| 2 |
| 1 |
+------+
4 rows in set (0,00 sec)
mysql> SHOW SESSION STATUS LIKE 'Handler_read%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 0 |
| Handler_read_key | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 4 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
+-----------------------+-------+
6 rows in set (0,00 sec)
mysql> EXPLAIN SELECT a FROM test ORDER BY a DESC\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test
type: index
possible_keys: NULL
key: idx_a
key_len: 5
ref: NULL
rows: 4
Extra: Using index
1 row in set (0,00 sec)
Handler_read_rnd – ilość odczytów rekordów, bazujących na stałej pozycji. W skrócie – duża liczba świadczy o dużej ilości zapytań, które wykonują filesort. Zapytania takie to duże obciążenie dla serwera MySQL i powód, żeby przyglądnąć się dokładniej miksowi zapytań. Innym możliwym powodem jest występowanie JOINów, które nie w pełni korzystają z indeksów. Przykładem na zapytanie z filesort’em może być taki SELECT:
Query OK, 0 rows affected (0,00 sec)
mysql> SELECT * FROM film WHERE rental_rate < 3 AND film_id < 10 ORDER BY length;
[cut]
6 rows in set (0,00 sec)
mysql> SHOW SESSION STATUS LIKE 'Handler_read%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 1 |
| Handler_read_key | 21 |
| Handler_read_next | 9 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 6 |
| Handler_read_rnd_next | 0 |
+-----------------------+-------+
6 rows in set (0,00 sec)
mysql> EXPLAIN SELECT * FROM film WHERE rental_rate < 3 AND film_id < 10 ORDER BY length\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: film
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 2
ref: NULL
rows: 9
Extra: Using where; Using filesort
1 row in set (0,00 sec)
Ostatnia pozycja, Handler_read_rnd_next zbiera informację ile razy odczytany został kolejny rekord w tabeli. Jeśli widać, że wartość ta jest duża, może to świadczyć o dużej ilości zapytań wykonujących pełen skan tabeli. Z oczywistych względów staramy się, aby tego typu zapytania występowały jak najrzadziej – odczyt wszystkich danych z tabeli jest kosztowny i w przypadku większych tabel generuje znaczne obciążenie.
Query OK, 0 rows affected (0,00 sec)
mysql> SELECT * FROM film;
[cut]
1000 rows in set (0,01 sec)
mysql> SHOW SESSION STATUS LIKE 'Handler_read%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 1 |
| Handler_read_key | 2 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 1001 |
+-----------------------+-------+
6 rows in set (0,00 sec)
mysql> EXPLAIN SELECT * FROM film\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: film
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 952
Extra:
1 row in set (0,00 sec)
Jak widać, pełen skan tabeli poskutkował zwiększeniem wartości Handler_read_rnd_next.
Do czego administrator baz danych może wykorzystać te wartości? W jaki sposób może to zrobić? O tym napiszę w kolejnym poście.
Komentarze