Page History
...
What is most important to understand here is the [timestamp_id] column occurring in both [snapshot_master] and [timestamp_master] tables. Actually, with MySQL and InnoDB you would probably create a Foreign Key from [snapshot_master] to [timestamp_master] for constraining and clarity. To get meaningful results you will need to JOIN
the two in the query or use a SUBQUERY
.
Let us explain This is basically how they work:
- Everytime SQL DM for MySQL sends a statement like
SHOW VARIABLES/STATUS
of some kind (or fetching a OS metric from Linux/proc folder) one row is INSERTED into [timestamp_master] table with information about current time and the metrics retrieved for each such statement will be is INSERTED into [snapshot_master]. The [snapshot_master] table contains the metrics details. The [timestamp_id] column identifies when metric details were like that. And also note Also, that timestamps in SQL DM for MySQL databases are unix_timestamps. - And actually, we do not always
INSERT
into [snapshot_master]. Only if the particular metric was changed since last time something was INSERTED for that metric we will INSERT again. So if you want to find the value of a metric at some particular time you will need to find the most recent value stored before that particular time for that particular metric. - Finally, note that the [snapshot_master] does not have the names of the metrics. It is not possible to know in advance what metrics the server will return returns as it depends on server details (version and configuration). And actually a server may be upgraded. And also saving
Saving each textual description only once which would save disk space. So [snapshot_master] only contains a number in the [metric_id] column referring to the textual description the [metric_master] table. So, if the query returns the name of the metric or the metric name should be used in a WHERE-clause also [metric_master] table must be referenced in the query. If you are familiar with the SHOW statements and what information they return you will easily identify the meaning of each row in [metric_master] table.
Note |
---|
The term 'metric' Metric here refers to the discrete values returned for SHOW statements themselves (SHOW GLOBAL VARIABLES; SHOW GLOBAL STATUS; SHOW SLAVE STATUS etc.). Whatever calculations SQL DM for MySQL does in its web interface are done after and not before storage. But we do one calculation before storing however: whenever a metric is INSERTED we will also retrieve that latest stored value for the same metric and calculate the difference. Both the current value and this difference is stored (in [metric_now] and [metric_diff] columns respectively). |
...
An example of a query that we actually execute (optimized for large SQLite databases) to populate a graph is ,as follows:
Code Block | ||
---|---|---|
| ||
SELECT metric_now FROM snapshot_master WHERE snapshot_master.metric_id = my_metric_id AND snapshot_master.timestamp_id IN( SELECT MAX(timestamp_id) FROM snapshot_master WHERE metric_id = my_metric_id AND timestamp_id <= ( SELECT MAX(timestamp_id) FROM timestamp_master WHERE server_timestamp <= my_metric_timestamp) ) |
...