2

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.

6

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).

  • Upvote this guy! I chose to show him your original query because I thought the handler statistics were more interesting for what he wants than the queries. But your answer is more accurate. – jynus Mar 10 '15 at 11:36
  • 1
    When making the choice that the question states, I believe both statements and their output have value.. :) – Mark Leith Mar 10 '15 at 12:18
  • Hi Guys.. jynus and Mark Leith., sorry for my delayed response. Got caught up between two tasks lately. Anyway, I have tried both SQL that you have suggested and it works flawlessly, I did install the SYS Schema of Mark. Will create a report based on the SQLs that you have provided. Many thanks for your help. – Jeff Rey Asuncion Mar 19 '15 at 7:47
  • Any idea what it might mean when #1146 - Table 'performance_schema.events_statements_summary_by_digest' doesn't exist ? – Matt Clark Jan 14 '17 at 4:31
2

Some people use the pt-table-checksum tool using the slow log or tcpdump as a reference, some people use the Google/Percona user_stats plugin (very useful in 5.5), and as @eroomydna says, even an audit plugin can be helpful (even if it is mostly used as a filtered general log for security.

Why use an external tool, that in some cases may slow down your reads if you can check the detailed statistics on performance_schema in 5.6 with no slowdown (with the default config). This is a report that I generated with MySQL Workbench using the default configuration:

performance_schema

I only installed the SYS schema through this tool, but it is not necessary, just run this query (if you haven't disabled P_S):

SELECT pst.object_schema AS table_schema,
pst.sum_timer_wait AS total_latency,
pst.count_fetch AS rows_fetched,
pst.sum_timer_fetch AS fetch_latency,
pst.count_insert AS rows_inserted,
pst.sum_timer_insert AS insert_latency,
pst.count_update AS rows_updated,
pst.sum_timer_update AS update_latency,
pst.count_delete AS rows_deleted,
pst.sum_timer_delete AS delete_latency,
fsbi.count_read AS io_read_requests,
fsbi.sum_number_of_bytes_read AS io_read,
fsbi.sum_timer_read AS io_read_latency,
fsbi.count_write AS io_write_requests,
fsbi.sum_number_of_bytes_write AS io_write,
fsbi.sum_timer_write AS io_write_latency,
fsbi.count_misc AS io_misc_requests,
fsbi.sum_timer_misc AS io_misc_latency
FROM performance_schema.table_io_waits_summary_by_table AS pst
LEFT JOIN (SELECT LEFT(SUBSTRING_INDEX(SUBSTRING_INDEX(REPLACE(file_name, '\\', '/'), '/', -2), '/', 1), 64) AS table_schema,
SUM(count_read) AS count_read,
SUM(sum_number_of_bytes_read) AS sum_number_of_bytes_read,
SUM(sum_timer_read) AS sum_timer_read,
SUM(count_write) AS count_write,
SUM(sum_number_of_bytes_write) AS sum_number_of_bytes_write,
SUM(sum_timer_write) AS sum_timer_write,
SUM(count_misc) AS count_misc,
SUM(sum_timer_misc) AS sum_timer_misc
FROM performance_schema.file_summary_by_instance
GROUP BY table_schema) AS fsbi
ON pst.object_schema = fsbi.table_schema
ORDER BY pst.sum_timer_wait DESC\G

This is the result I get, grouped by schema (database):

*************************** 1. row ***************************
     table_schema: test
    total_latency: 35301990
     rows_fetched: 4
    fetch_latency: 35301990
    rows_inserted: 0
   insert_latency: 0
     rows_updated: 0
   update_latency: 0
     rows_deleted: 0
   delete_latency: 0
 io_read_requests: 140
          io_read: 110368
  io_read_latency: 13658437110
io_write_requests: 0
         io_write: 0
 io_write_latency: 0
 io_misc_requests: 572
  io_misc_latency: 130559074740
*************************** 2. row ***************************
     table_schema: mysql
    total_latency: 0
     rows_fetched: 0
    fetch_latency: 0
    rows_inserted: 0
   insert_latency: 0
     rows_updated: 0
   update_latency: 0
     rows_deleted: 0
   delete_latency: 0
 io_read_requests: 517
          io_read: 2137405
  io_read_latency: 55914443250
io_write_requests: 0
         io_write: 0
 io_write_latency: 0
 io_misc_requests: 430
  io_misc_latency: 226340632890

Credit to Mark Leith, as the above is a modified query in SYS schema.

  • Thanks jynus, thanks for providing a detailed feedback on my question. Im currently checking the details and link that you have provided. I havent gotten my performance_schema enabled yet. Though, I am quite unaware if this is safe to enable when we have a Galera MySQL Cluster. (Sorry, i forgot to mention this earlier). Can I also request for your feedback on it's impact when i enable it on a clustered environment? I was worrying that the data on the performance_schema of the first node will get replicated on the other node? – Jeff Rey Asuncion Mar 10 '15 at 6:27
  • Yes, you should have mentioned that you weren't using a traditional MySQL node first! :-) Which vendor are you using for your nodes: Galera, Percona, MariaDB? For example, it is disabled by default on MariaDB. On my tests, the default configuration didn't give me a meaningful slowdown in the latest versions, unless you activate extended debug. You mileage may vary, but it is worth in most cases for the extra information. – jynus Mar 10 '15 at 11:45
  • Hi @jynus, Thanks again for your help. Just a follow up question. What's the unit of the values being showed in your above query result? Also, when I tried it on our database, why do i get a more than 1 row pointing to one schema only? – Jeff Rey Asuncion Mar 19 '15 at 7:56
  • It is GROUP BY table_schema, so if you get more than one row, they are different, collation-wise (or the function to extract the schema has failed). Regarding units, by default it is nanoseconds, but check it (or setup it, it is configurable) at SELECT * FROM performance_schema.setup_timers WHERE name = 'wait' – jynus Mar 19 '15 at 10:51
  • 1
    No results are always shown in picoseconds, no matter what timer is set.. – Mark Leith Mar 19 '15 at 13:37
0

There is no core method for gathering this information. It could be sought by utilising an audit plugin. By collecting all of the queries occurring in the server you could analyse the logs. Using the McAfee Audit plugin you can filter the events to only long on certain schemas and that might make lighter work of the analysis.

0

I suggest that there is an even better thing to search for -- The queries that have the most impact on the system.

Do that by setting long_query_time to a low value (Perhaps 1 (second)). Turn on the slowlog. Wait awhile. Then run pt-query-digest. It will list the 'slow' queries, starting with the 'worst'.

'Worst' is defined as the total elapsed time consumed by all copies of similar queries.

'Similar' queries differ only in constants (numbers and strings).

You could set long_query_time to 0 to grab all queries; that would actually be more 'correct'. But the slowlog would fill up fast, so watch disk consumption.

Back to your question. Look at the Database used by the first few queries.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.