INFORMATION_SCHEMA contains new tables, GLOBAL_STATUS, SESSION_STATUS, GLOBAL_VARIABLES, and SESSION_VARIABLES, that correspond to the output from the SHOW {GLOBAL|SESSION} STATUS and SHOW {GLOBAL|SESSION} VARIABLES statements.
This was reason enough for me to compile MySQL 5.1 from source and take a look:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.1.12-beta-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use information_schema
Database changed
mysql> show tables;
+---------------------------------------+
| Tables_in_information_schema |
+---------------------------------------+
| CHARACTER_SETS |
| COLLATIONS |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS |
| COLUMN_PRIVILEGES |
| ENGINES |
| EVENTS |
| FILES |
| GLOBAL_STATUS |
| GLOBAL_VARIABLES |
| KEY_COLUMN_USAGE |
| PARTITIONS |
| PLUGINS |
| PROCESSLIST |
| REFERENTIAL_CONSTRAINTS |
| ROUTINES |
| SCHEMATA |
| SCHEMA_PRIVILEGES |
| SESSION_STATUS |
| SESSION_VARIABLES |
| STATISTICS |
| TABLES |
| TABLE_CONSTRAINTS |
| TABLE_PRIVILEGES |
| TRIGGERS |
| USER_PRIVILEGES |
| VIEWS |
+---------------------------------------+
27 rows in set (0.00 sec)
So what can you do with it? Certainly a lot. You want to know when your server has been restarted the last time? Here's what to do:
mysql> SELECT (NOW() - INTERVAL VARIABLE_VALUE SECOND)
AS server_start_time
-> FROM information_schema.GLOBAL_STATUS
-> WHERE VARIABLE_NAME = 'UPTIME';
+---------------------+
| server_start_time |
+---------------------+
| 2006-09-23 20:38:05 |
+---------------------+
1 row in set (0.00 sec)
There are other nice things you can do. You can create a monitoring table and an event to copy the values of all Com% status variables into the monitoring table regularily, like so:
SELECT NOW() AS ts,
VARIABLE_NAME, VARIABLE_VALUE
FROM information_schema.GLOBAL_STATUS
WHERE VARIABLE_NAME LIKE 'Com%'
And that's certainly not everything you can do ;-).
2 comments:
Great! That's what I have been waiting for very long!
Calculating performance formulas is also easier with these tables:
select
@key_reads := (
select variable_value
from INFORMATION_SCHEMA.GLOBAL_STATUS
where VARIABLE_NAME = 'Key_reads') as key_reads,
@Key_read_requests := (
select variable_value
from INFORMATION_SCHEMA.GLOBAL_STATUS
where VARIABLE_NAME = 'Key_read_requests') as key_read_requests,
100 - ((@key_cache/@Key_read_requests)*100) as `key cache efficiency`\G
*************************** 1. row ***************************
key_reads: 41959.0000000
key_read_requests: 8161031.0000000
key cache efficiency: 99.742716330816535400000000000000
Giuseppe: your query looks perfectly correct, but when I run it I always get a key cache efficiency of NULL (although e.g. key_reads is 116 and key_read_requests is 10121, so there's no division by zero or something like that).
Ronald: my happiness that I can access all these values by queries exceeds my sorrow that I have to deal with two tables instead of one ;-).
Post a Comment