W poprzednim poście pisałem o statystykach udostępnianych przez patch Google i przez Percona Server. Dziś kilka przykładów na wykorzystanie tych danych w praktyce.

Zaczynamy od podstawowej sprawy – obciążenie generowane przez użytkowników:

mysql> SELECT user,
busy_time,
cpu_time,
busy_time/x*100 AS '% busy_time',
cpu_time/z*100 AS '% cpu_time'
FROM information_schema.user_statistics
LEFT JOIN
(SELECT SUM(busy_time) AS x,
SUM(cpu_time) AS z
FROM information_schema.user_statistics
ORDER BY Cpu_time ASC) aaa
ON 1=1 ORDER BY Cpu_time ASC;
+-----------+-----------+----------+-------------+------------+
| user      | busy_time | cpu_time | % busy_time | % cpu_time |
+-----------+-----------+----------+-------------+------------+
| a         |        10 |        3 |      0.7386 |    11.5385 |
| b         |       900 |       11 |     66.4697 |    42.3077 |
| c         |       444 |       12 |     32.7917 |    46.1538 |
+-----------+-----------+----------+-------------+------------+
3 rows in set (0,00 sec)

Powyższe zapytanie listuje nam użytkowników w kolejności od generującego najmniejsze obciążenie do tego, który generuje obciążenie największe. Zapytanie jest szybkie i przyjemne, nie stanowi dużego obciążenia dla serwera, można je wykonywać w dowolnym momencie.

Kolejnym, często spotykanym problemem jest sprawdzenie, które indeksy są wykorzystywane w bazie, a które nie. Sprawa jest o tyle istotna, że każdy indeks to dodatkowe operacje dyskowe w czasie modyfikowania zawartości tabeli. Jeśli indeks jest wykorzystywany, to się to opłaca – zalety szybszych SELECTów przeważają nad wadami wolniejszych INSERTów czy UPDATEów. Gorzej, jeśli indeks jest rzadko wykorzystywany. Jak to sprawdzić? W przypadku Percona Server sprawa jest prosta:

mysql> SELECT * FROM information_schema.index_statistics ORDER BY rows_read;
+--------------+-----------------------+---------------------------+-----------+
| TABLE_SCHEMA | TABLE_NAME            | INDEX_NAME                | ROWS_READ |
+--------------+-----------------------+---------------------------+-----------+
| wordpress    | wp_comments           | comment_date_gmt          |         1 |
| mysql        | tables_priv           | PRIMARY                   |         1 |
| wordpress    | wp_comments           | comment_post_ID           |         1 |
| wordpress    | wp_comments           | PRIMARY                   |         2 |
| wordpress    | wp_users              | user_login_key            |         3 |
| wordpress    | wp_users              | PRIMARY                   |         3 |
| s            | articles              | show_on_home              |         6 |
| s            | articles              | PRIMARY                   |         8 |
| wordpress    | wp_usermeta           | meta_key                  |        16 |
| s            | comments              | articleid                 |        20 |
| s            | categories            | idx_seftitle              |        22 |
| wordpress    | wp_postmeta           | PRIMARY                   |        22 |
| wordpress    | wp_comments           | comment_approved          |        25 |
| s            | articles              | idx_category              |        27 |
| s            | categories            | PRIMARY                   |        47 |
| wordpress    | wp_usermeta           | user_id                   |        70 |
| wordpress    | wp_terms              | slug                      |        96 |
| wordpress    | wp_postmeta           | meta_key                  |       108 |
| wordpress    | wp_term_taxonomy      | term_id_taxonomy          |       135 |
| wordpress    | wp_posts              | post_parent               |       135 |
| wordpress    | wp_comments           | comment_approved_date_gmt |       362 |
| wordpress    | wp_posts              | post_name                 |       439 |
| wordpress    | wp_term_taxonomy      | taxonomy                  |       635 |
| wordpress    | wp_postmeta           | post_id                   |      1539 |
| wordpress    | wp_term_relationships | term_taxonomy_id          |      3186 |
| wordpress    | wp_posts              | PRIMARY                   |      3486 |
| wordpress    | wp_options            | option_name               |      4278 |
| wordpress    | wp_terms              | PRIMARY                   |      6799 |
| wordpress    | wp_term_taxonomy      | PRIMARY                   |      8121 |
| wordpress    | wp_term_relationships | PRIMARY                   |      8132 |
| wordpress    | wp_posts              | type_status_date          |    251234 |
+--------------+-----------------------+---------------------------+-----------+

Widzimy, że przykładowo w tabeli `wp_users` indeks `user_login_key` został wykorzystany do odczytania trzech rekordów. Przydałoby się teraz zobaczyć, czy stanowi on dodatkowe obciążenie:

mysql> SELECT * FROM information_schema.table_statistics;
+--------------+-----------------------+-----------+--------------+------------------------+
| TABLE_SCHEMA | TABLE_NAME            | ROWS_READ | ROWS_CHANGED | ROWS_CHANGED_X_INDEXES |
+--------------+-----------------------+-----------+--------------+------------------------+
| s            | comments              |        44 |            0 |                      0 |
| mysql        | db                    |         3 |            0 |                      0 |
| mysql        | tables_priv           |         1 |            0 |                      0 |
| wordpress    | wp_usermeta           |        86 |            2 |                      6 |
| s            | extras                |         2 |            0 |                      0 |
| mysql        | user                  |         5 |            0 |                      0 |
| wordpress    | wp_users              |         6 |            0 |                      0 |
| wordpress    | wp_options            |    448423 |         4078 |                   8156 |
| s            | articles              |       648 |            0 |                      0 |
| wordpress    | wp_posts              |    257875 |           62 |                    310 |
| wordpress    | wp_term_taxonomy      |      9072 |           20 |                     60 |
| s            | settings              |      1683 |           24 |                     24 |
| wordpress    | wp_contact_form_7     |         1 |            0 |                      0 |
| wordpress    | wp_postmeta           |     10426 |           38 |                    114 |
| wordpress    | wp_terms              |      6895 |            0 |                      0 |
| wordpress    | wp_term_relationships |     11318 |           13 |                     26 |
| wordpress    | wp_comments           |       463 |            2 |                     12 |
| s            | categories            |       251 |            0 |                      0 |
+--------------+-----------------------+-----------+--------------+------------------------+
18 rows in set (0,00 sec)

Jak widać, nie. W tabeli `wp_users` nie zachodzą zmiany w rekordach, tak więc indeks ten nie jest problemem.

Możemy sobie zrobić zestawienie najczęściej modyfikowanych tabel:

mysql> SELECT TABLE_NAME,
information_schema.index_statistics.TABLE_SCHEMA,
SUM(information_schema.index_statistics.ROWS_READ) AS "SUM(ROWS_READ)",
SUM(ROWS_CHANGED),
ROWS_CHANGED_X_INDEXES/ROWS_CHANGED AS "NUMBER OF INDEXES"
FROM information_schema.table_statistics
RIGHT JOIN information_schema.index_statistics USING(TABLE_NAME)
WHERE ROWS_CHANGED > 0
GROUP BY TABLE_NAME
ORDER BY SUM(ROWS_CHANGED) DESC;
+-----------------------+--------------+----------------+-------------------+-------------------+
| TABLE_NAME            | TABLE_SCHEMA | SUM(ROWS_READ) | SUM(ROWS_CHANGED) | NUMBER OF INDEXES |
+-----------------------+--------------+----------------+-------------------+-------------------+
| wp_options            | wordpress    |           4297 |              4097 |            2.0000 |
| wp_posts              | wordpress    |         255584 |               248 |            5.0000 |
| wp_postmeta           | wordpress    |           1675 |               114 |            3.0000 |
| wp_term_taxonomy      | wordpress    |           8908 |                60 |            3.0000 |
| wp_term_relationships | wordpress    |          11335 |                26 |            2.0000 |
| wp_comments           | wordpress    |            391 |                10 |            6.0000 |
| wp_usermeta           | wordpress    |             86 |                 4 |            3.0000 |
+-----------------------+--------------+----------------+-------------------+-------------------+
7 rows in set (0,00 sec)

Z powyższego zestawienia widać, że najgorzej wykorzystywane są indeksy w tabeli `wp_options` –  łącznie przy pomocy dwóch indeksów odczytano 4297 rekordów a zmodyfikowano w tej tabeli 4097 rekordów.

To są tylko przykłady zestawień i informacji, które można wydostać z udostępnionych statystyk. Dzięki temu, że information_schema jest traktowana jako baza danych, do generowania raportów można wykorzystać całą magię SQL.