Mysql – Getting Database Request Statistics on MySQL

MySQL

Is there a possible way to query (or in anyway) get the number of requests (select, insert, update, delete, etc) that a specific schema is doing on a MySQL Database.

The reason behind this is that we would want to monitor and isolate a schema that uses a lot of server resource. Once identified, we would want to group all schema in a single hardware that can handle high consuming schema.

Also, we wanted to have a report showing this statistics to be presented.

MySQL Version : 5.6.16

Thanks in Advance.

Best Answer

Jaime's answer is pretty good (and thanks for the credit). However, that only deals very specifically with "Table IO" (i.e latency at the storage engine handler layer), and file IO, per schema.

I'd like to suggest a slightly alternate answer, which gives exactly what was asked for. This deals with statement latency over all, and counts the numbers of each types of statement:

SELECT schema_name,
       substring_index(digest_text, ' ', 1) AS sql_type,
       COUNT(*) AS count,
       sys.format_time(SUM(sum_timer_wait)) AS latency
  FROM performance_schema.events_statements_summary_by_digest
 GROUP BY schema_name, substring_index(digest_text, ' ', 1)
 ORDER BY schema_name, SUM(sum_timer_wait) DESC;

+--------------------+----------+-------+---------------+
| schema_name        | sql_type | count | total_latency |
+--------------------+----------+-------+---------------+
| NULL               | CALL     |     2 | 584.49 ms     |
| NULL               | SELECT   |     2 | 247.07 ms     |
| NULL               | SHOW     |     1 | 16.82 ms      |
| NULL               | CREATE   |     1 | 9.60 ms       |
| NULL               | INSERT   |     1 | 268.62 us     |
| NULL               | SET      |     3 | 225.49 us     |
| performance_schema | SHOW     |     2 | 243.25 ms     |
| performance_schema | SELECT   |     1 | 52.18 ms      |
| performance_schema | INSERT   |     2 | 335.52 us     |
| sys                | CREATE   |   120 | 19.16 s       |
| sys                | SELECT   |    83 | 1.54 s        |
| sys                | DESC     |     2 | 627.22 ms     |
| sys                | DROP     |    38 | 504.05 ms     |
| sys                | SHOW     |     2 | 139.58 ms     |
| sys                | INSERT   |     1 | 88.10 ms      |
| sys                | SET      |     3 | 421.86 us     |
+--------------------+----------+-------+---------------+

The NULL schema_name is for statements executed without a default database (i.e no "USE dbname").

You will also need sys installed, but could skip the sys.format_time(...), and just select the SUM(...), in which case the value will be in picoseconds for the latency (same as Jaime's).

Since I can't yet comment (my first answer on here!), I'll also note here that performance schema table data does not replicate (even DML against the setup_* tables in performance_schema), and that the above query will work with the default configuration with 5.6 (performance_schema and the statement instrumentation are both enabled by default there).

Related Question