A modern MySQL server contains a lot of useful meta-data in information_schema and performance_schema, which can help bring visibility into what is happening inside of your database server. However, sometimes this data is quite fine grained and needs finessing in order to get to that point 🙂
MySQL 5.7 includes a new SYS schema installed by default. SYS is a set of views, functions and procedures that aggregate the data in a way that is useful to DBAs trying to perform common tasks.
For Linux users I like to compare performance_schema to /proc, and SYS to vmstat.
SHOW PROCESSLIST
Having introduced SYS as a DBA-task-oriented-set-of-views, is there a more common DBA task than logging into a system and seeing what is running? Here is the SYS way to do it:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 |
mysql> SELECT * from sys.session\G *************************** 1. row *************************** thd_id: 29 conn_id: 4 user: root@localhost db: test command: Query state: alter table (read PK and internal sort) time: 6 current_statement: ALTER TABLE b add index(b) statement_latency: 5.78 s progress: 19.19 lock_latency: 203.70 ms rows_examined: 0 rows_sent: 0 rows_affected: 0 tmp_tables: 0 tmp_disk_tables: 0 full_scan: NO last_statement: NULL last_statement_latency: NULL current_memory: 4.85 MiB last_wait: wait/io/file/innodb/innodb_data_file last_wait_latency: Still Waiting source: fil0fil.cc:5623 trx_latency: 5.68 s trx_state: ACTIVE trx_autocommit: YES pid: 23988 program_name: mysql *************************** 2. row *************************** thd_id: 28 conn_id: 3 user: root@localhost db: test command: Query state: Sending data time: 0 current_statement: insert into a select null, repeat('b', 255) from a statement_latency: 303.54 ms progress: NULL lock_latency: 291.00 us rows_examined: 0 rows_sent: 0 rows_affected: 0 tmp_tables: 1 tmp_disk_tables: 0 full_scan: NO last_statement: NULL last_statement_latency: NULL current_memory: 1.43 MiB last_wait: wait/io/table/sql/handler last_wait_latency: Still Waiting source: handler.cc:3056 trx_latency: 410.06 ms trx_state: ACTIVE trx_autocommit: YES pid: 23969 program_name: mysql *************************** 3. row *************************** thd_id: 27 conn_id: 2 user: root@localhost db: mysql command: Query state: Sending data time: 0 current_statement: SELECT * from sys.session statement_latency: 23.01 ms progress: NULL lock_latency: 10.02 ms rows_examined: 0 rows_sent: 0 rows_affected: 0 tmp_tables: 4 tmp_disk_tables: 1 full_scan: YES last_statement: NULL last_statement_latency: NULL current_memory: 3.25 MiB last_wait: wait/synch/mutex/innodb/file_format_max_mutex last_wait_latency: 31.69 ns source: trx0sys.cc:781 trx_latency: 4.94 m trx_state: ACTIVE trx_autocommit: NO pid: 23950 program_name: mysql 3 rows in set (0.20 sec) |
Allow me to point out a few things here that are not present in SHOW PROCESSLIST:
- In row#1 you can see that I am running an ALTER TABLE command to add an index on a table. SYS has included the current stage of that execution (it is currently “read PK and internal sort”), and is 19% complete with that stage. It also includes the current execution time (5.78s) which is more precise.
- In row #2 you can see that I am running an INSERT .. SELECT command. It is an autocommit transaction that has been running for 410.06 ms. You can also see that is requires a temporary table to execute this statement.
- In row #3 you can see that I actually have a transaction that has been open for 4.94 minutes. It is my current connection where I am running the command SELECT * from sys.session from. Long running transactions can sometimes cause performance problems as they prevent some house-keeping operations from running.
- All three connections are created by mysql command line clients (program_name: mysql, pid: os pid), so this is a simulation, but your client library will also populate these connection attributes, and it could be useful in tracking down the source of a problem.
Conclusion
Hopefully this serves as a good demonstration that SYS is both easy to use, and contains a lot of the useful meta data that is hidden away in performance_schema. If this inspires you to look for other examples, I suggest running SHOW TABLES in SYS on your MySQL 5.7 installation. There is a lot of good stuff in there 🙂