SYS Schema Second Steps

The SYS Schema is a valuable set of tools for any MySQL DBA and last entry covered in the installation and first, tentative steps. You also should peruse Mark Leith’s presentation from the recent MySQL Central @ Oracle Open World. There is a lot in the SYS Schema and this blog entry is a look what has been so nicely gift wrapped at a cursory level. I hope to explore further and in greater detail but right now is still exploration at a simple level.

Simple Query Information
So what can bee seen from running a simple query like SELECT Name FROM City LIMIT 6?

mysql>
*************************** 1. row ***************************
query: SELECT NAME FROM `City` LIMIT ?
db: world
full_scan: *
exec_count: 1
err_count: 0
warn_count: 0
total_latency: 394.55 us
max_latency: 394.55 us
avg_latency: 394.55 us
lock_latency: 132.00 us
rows_sent: 6
rows_sent_avg: 6
rows_examined: 6
rows_examined_avg: 6
rows_affected: 0
rows_affected_avg: 0
tmp_tables: 0
tmp_disk_tables: 0
rows_sorted: 0
sort_merge_passes: 0
digest: 12f76baa0640720ddc461eb65682adc4
first_seen: 2014-12-11 13:43:21
last_seen: 2014-12-11 13:43:21
1 row in set (0.00 sec)


Checking on the table statistics

mysql> select * from schema_table_statistics where table_name='City'\G
*************************** 1. row ***************************
table_schema: world
table_name: City
total_latency: 373.83 us
rows_fetched: 36
fetch_latency: 373.83 us
rows_inserted: 0
insert_latency: 0 ps
rows_updated: 0
update_latency: 0 ps
rows_deleted: 0
delete_latency: 0 ps
io_read_requests: 12
io_read: 80.73 KiB
io_read_latency: 87.32 ms
io_write_requests: 0
io_write: 0 bytes
io_write_latency: 0 ps
io_misc_requests: 11
io_misc_latency: 251.77 us
1 row in set (0.06 sec)

There are ways to dog into what running statements are doing and hopefully we can circle back to that in the future. This is more like exploring what is in the toy box that can be glanced over quickly. And SYS Schema is a big toy box.

Schema Synonyms?
Ever need to call a database by another name? SYS Schema comes with several handy procedues such as this one.
mysql> CALL sys.create_synonym_db('world','world_innodb');
+----------------------------------------------+
| summary |
+----------------------------------------------+
| Created 3 views in the world_innodb database |
+----------------------------------------------+
1 row in set (0.08 sec)

Query OK, 0 rows affected (0.08 sec)

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sakila |
| sys |
| world |
| world_innodb |
+--------------------+
7 rows in set (0.00 sec)
Very neatly the world and world_nnodb tables point to the same data!

Problematic Queries
It is also easy to spot queries having problems.
mysql> select * from statements_with_errors_or_warnings\G
*************************** 1. row ***************************
query: SELECT `sys` . `format_stateme ... , `sys` . `format_time` ( ...
db: sys
exec_count: 7
errors: 1
error_pct: 14.2857
warnings: 0
warning_pct: 0.0000
first_seen: 2014-12-11 13:42:13
last_seen: 2014-12-11 13:47:36
digest: 220e9caf13a5e5bfe8f73987bc26e443
1 row in set (0.00 sec)

mysql>


And How Is Your MySQL Memory?
And to find the total memory being used globally by the server.
mysql> select * from memory_global_total;
+-----------------+
| total_allocated |
+-----------------+
| 500.68 MiB |
+-----------------+
1 row in set (0.01 sec)