Query Analyzer and Processlist data

 

sniffer.data

 

CREATE TABLE IF NOT EXISTS [query_master](
[id] INTEGER PRIMARY KEY AUTOINCREMENT,
[query] TEXT,
UNIQUE([query]))

 

CREATE TABLE IF NOT EXISTS [query_snapshot] (
[pkeyid] INTEGER PRIMARY KEY AUTOINCREMENT,
[id] INTEGER,
[threadid] INTEGER,
[user] TEXT,
[querytime] INTEGER,
[uptime] INTEGER,

[host] TEXT DEFAULT)

 

Here, you see the same pattern as above: the [id] column in the [query_snapshot] table identifies a row in the [query_master] where the actual/textual query is saved. Also note, that a UNIQUE KEY is defined on the [query] column so that we can use an INSERT ... ON DUPLICATE KEY construction and thus ensure that the [query_master] table only has the same query stored once. But in [query_snapshot] table there will be one row for every instance of the query.

 

And actually with general/slow query log analyzers we use identical tables. The log CHUNK retrieved from the server will be parsed and the tables populated like - you see in your sniffer.data database. The tables used for log analysis however are MEMORY tables and will only be available in MONyog and only for as long as they are needed.

 

 

Processlist

 

Also, MONyog processlist feature uses a SQLite MEMORY table (for every server). The table structure is like this,

 

CREATE TEMPORARY TABLE IF NOT EXISTS [processlist](
[Id] INTEGER NOT NULL PRIMARY KEY,
[User] TEXT,
[Host] TEXT,
[Db] TEXT,
[Command] TEXT,
[Time] INTEGER,
[State] TEXT,
[Info] TEXT,
[Action] TEXT)

 

So, that is how the MySQL processlist displays in MONyog - unlike when connected to MySQL directly - it can be filtered, sorted etc. by using WHERE, ORDER BY, GROUP BY etc. with a SELECT query against the MONyog [processlist] table. But as it is a MEMORY table you can only query it from inside the MONyog processlist interface.

 

Information about the MONyog database schema itself

 

There is a schema_version table in all databases created by MONyog. Every time MONyog starts it will check here if the database is up to date with the current program version. If it is not, MONyog will perform the necessary schema upgrades at start-up. Schema definition reads:

 

CREATE TABLE IF NOT EXISTS [schema_version] (
[schema_desc] TEXT,
[schema_major_version] TEXT,
[schema_minor_version] TEXT,
PRIMARY KEY ([schema_major_version], [schema_minor_version]))