Different Types of Logs Supported
MySQL query log settings
Monyog retrieves (completely or partially) the General query log and the
Slow query log from the MySQL servers it connects to, and analyzes them.
Here, we will see how to set up details for the connection, so that log
analysis will be available with Monyog. You will have to set up details
for the general query log and the slow query log independently. Enabling
slow query log 'log queries not using indexes' instead needs
privilege. Refer to the MySQL documentation on how to enable and
configure logging. MySQL server logs can be written to files on the
server machine or to tables in the MySQL database itself.
The MySQL server (since version 5.0) has an option to log (in the slow
log) queries that do not use an index. Such queries need not be slow if
there are only a few hundred or few thousand records in the table(s)
involved. But they are 'potentially slow' and should be identified if
they access tables, which will continue to grow. You can enable and
disable this from here too (Monyog will send the appropriate
statements to MySQL).
Note: Only DML and DDL queries are recorded in the slow query log.
Logs written to files
First, we will consider the situation where server logs are stored as files on the server machine. This is the most common situation and the only one available with MySQL servers before version 5.1. First time you configure a server with this option you should click the 'Fetch log details from MySQL' button. The MySQL server 'knows' (it is stored in server variables) what logs are enabled and how logging is configured. You do that independently for the slow log and the general log. It will return everything you need except the path to the log file. This you will have to enter yourself. Click 'Test Path' to verify that the path was entered correctly. Monyog will connect and verify the existence of the file (but not its content).
The log files can be accessed from the local file system (if Monyog and MySQL is running on the same computer) or by using SFTP (if Monyog and MySQL is running on different computers). You should note that you must use the file and path syntax of the machine where the logs are.
If the log files can be accessed from a shared drive, over a network, or from a network enabled file system (like NFS on Linux), then Monyog can access them as if they were local files. No additional SSH/SFTP configuration is required in this case: the operating system will take care of the file transfer transparently.
When 'via SFTP' option is chosen, then SSH server details as defined in 'SSH server details settings' are used to read the file from the remote system. Note that the SSH user must have read access to the log files!
If MySQL server version is greater than 5.1.6 then all the fields mentioned in log analyzer would be editable i.e. if a user changes and saves the settings by clicking on 'Save Current Log Details To MySQL' button, it would be reflected in the corresponding server.
Note that by default Monyog service runs under Local System Account. If you have Slow query or General query logs in a Mapped Network Drive, Monyog will not be able to reach it. You need to use UNC notation for Monyog to be able to access them. See FAQ 31 for details.
Logs written to MySQL tables
This option is supported by MySQL from version 5.1. Also, Monyog
supports when this option is available. Here, you click the 'Fetch Log
Details From MySQL' button. When this option is used there is no file
path to configure and no SSH details to consider. Monyog can retrieve
the server log by sending simple
SELECT statements. Only the MySQL
user used by Monyog to connect to MySQL must have
SELECT privileges to
In the 'Query Analyzer' tab select which of the MySQL servers you want and the type of log (including the 'pseudo log') you want to analyze. Next click 'Analyze' and analysis will start and after a while display an analysis result like for Slow Query log,
for General Query log,
Monyog's 'query sniffer' is a functionality that records a 'pseudo server log' and stores it in the Monyog embedded database. With 'query sniffer' enabled, Monyog can populate the 'pseudo server log' in three different ways at the intervals you specify:
- by utilizing Performance Schema tables
events_statements_history_long) and collecting snapshots at regular intervals.
- by sending the query
SHOW FULL PROCESSLISTto the MySQL server.
- or by connecting to a running instance of the MySQL-Proxy program that is used by one or more clients to connect to a MySQL server.
For MySQL 5.6.14 and above you can use Performance schema, Proxy and Processlist for query analysis. If using MySQL version less than 5.6.14 then only Proxy or Processlist can be used in Monyog.
Performance Schema on MySQL contains queries executed on server along with other information
- number of rows sent and examined
- number of temporary tables created on disk
- number of temporary tables created on memory
- number of joins performed and the type of join
- whether sorting happened and the type of sort
- whether index used
- whether good index used
Monyog uses performance schema statement digest
table(events_statements_summary_by_digest) to get the above
information and is dependent on the statements_digest in
setup_consumers table. By default, this is enabled. If not, it can be
enabled by executing
UPDATE performance_schema.setup_consumers SET enabled = 'YES' WHERE name = 'statements_digest';
Example query is available in
events_statements_history_long table and
has to be enabled and is dependent on the
events_statements_history_long in setup_consumers table. By default,
this is not enabled and should be enabled by executing
UPDATE performance_schema.setup_consumers SET enabled = 'YES' WHERE name = 'events_statements_history_long';
performance_schema.events_statements_summary_by_digest table size
is dependent on
performance_schema_digests_size global variable. By
default, this size is set to 5000 rows. Once it reaches this limit you
may lose the queries. Monyog provides an option to truncate the
performance schema digest table when it reaches 80% of
Performance schema based sniffer comes with different filters like: Queries with errors, Queries with warnings, Queries with missing indexes and Queries with poor indexes.
performance_schema truncates queries after 1024 characters and always
replaces literals with a wildcard (in other words: P_S contains a
summary/an aggregation only). So query listing not replacing literals is
not possible with this option. And finally also observe that no other
tool (or user) should be writing (including deleting or truncating) to
events_statements_history_long tables if this option is used as there
is only one of each table for all users (it is not a temporary table or
a materialized view or similar private for the user). This is a design
limitation with the tables in P_S as such and not a Monyog issue.
If using MySQL version less than 5.6.14 then only Proxy or Processlist can be used in Monyog. Although configuring a Proxy instance is a little more complicated, the PROXY-based sniffer has several advantages over the PROCESSLIST-based, including:
- All queries that was handled by the Proxy will be recorded by Monyog 'sniffer' when PROXY option is used. When PROCESSLIST option is used very fast queries may execute completely between two SHOW FULL PROCESSLIST queries and will then not be recorded.
- You can choose to analyze queries from specific client(s)/application(s) only. Simply let (only) the clients that you want to focus on at the moment connect through the Proxy.
- When using the PROXY option you can distribute part of the load generated by the sniffer on the machine that fits best in your deployment scenario (like on the one that has most free resources available) by deciding where to have the PROXY: The MySQL machine, the Monyog machine (if not the same) or quite another machine. The machine running MySQL will have no additional load due to the sniffer if the Proxy is not running on that machine.
Also note that, if more Monyog instances use the same PROXY they will use the same data collected, when the Proxy Sniffing is enabled by the first Monyog instance. To work with Monyog sniffer the MySQL Proxy instance must be started with the name of a LUA script called 'MONyog.LUA' (LUA is a scripting/programming language) as argument and is distributed with Monyog. You will find it in the Monyog program folder after installing (Windows and Linux RPM) or unpacking (Linux .tar.gz) the Monyog program package as downloaded from Webyog website. The MySQL Proxy program however you will need to download from MySQL website (we cannot include it for license reasons). Monyog works with Proxy versions from 0.61 to 0.81(latest currently) with the exception of 0.7x versions for windows and Mac due to a bug in those specific builds. For more information on Proxy click here.
To start a Proxy instance for use with Monyog use the command:
- For Older version:
Proxy installation folder>mysql-proxy --proxy-backend-addresses=192.168.x.x:3305 --proxy-address=192.168.y.y:4045 --proxy-lua-script=MONyog.lua
- For v0.81 and later:
Proxy installation folder>mysql-proxy --proxy-backend-addresses=192.168.x.x:3305 --proxy-address=192.168.y.y:4045 --admin-username=root --admin-password=root --admin-lua-script=MONyog.lua --proxy-lua-script=MONyog.lua
(It is assumed that the 'MONyog.LUA' was copied to the folder where the PROXY binary is). Also note that, if no port is specified the PROXY will listen on port 4040. Now you can connect to the Proxy from one or more clients/applications. The Proxy will send queries to MySQL and results back to the client. But when started with the LUA script for Monyog sniffer it will also send information to Monyog that Monyog will use to populate the sniffer 'pseudo log'.
Once this 'pseudo log' has been recorded (in either of the two ways described: PROCESSLIST or PROXY-based) the Monyog log analysis functionalities can operate on the 'pseudo log' as well as the 'real logs'. The data recorded in the 'pseudo log' will be purged automatically based on the 'data retention timeframe' option set by you.
Further some filtering options are provided. Note that this filtering happens before storing to the Monyog database. This will prevent the sniffer database to grow out of control! The filtering options are:
- User and host: You can choose to store queries executed only by a specific combination of users and/or hosts.
- Minimum time taken: For every PROCESSLIST returned to Monyog, the queries will be recorded in the embedded database only if they have been executing for a time greater than the specified minimum execution time. Furthermore, if a query with the same structure and details (like process ID) as one already recorded is encountered, the embedded database will be UPDATED, and the statement will be recorded only once. Note that, this setting should be somewhat larger than the sample interval (and also consider the latency of the connection). If set lower it won't really make much sense!
- Queries starting with: Enter any string and only queries
starting with that string will be recorded. Examples:
Also note that in
PROCESSLIST Sniffer we have an option 'Long Running
Query Options' where you can monitor the long running queries by
notifying or killing a query which takes more than a time specified by
you. You can also specify users whose queries will be ignored (i.e.
queries by such user will never be killed by Monyog and never raise an
alert even if they take a longer time to execute than the alert/kill
setting time you specified.
Clicking on the monitor only locked queries would only monitor those long queries that are locked.
You should note of course that the query sniffer will never be a
complete 'general log'. Very fast statements may or may not be recorded
as they may or may not finish executing between two
generated. The time interval between subsequent data collections for the
'pseudo log' depends on the connection to the MySQL server.
Note that identical queries will only be listed once and the 'count' column will tell how many times this query was executed.
With the General Query Log there are a few specific problems:
- With multi-line queries we will only record the first line of the statement. The reason is that, as the log does not record the statement DELIMITERs, there really is no way to tell where a multi-line statement ends. Even the option to 'Show full' will not display more than one line as Monyog has only stored one line. Refer to FAQ 23.
- It is not always possible to tell what user executed a specific query. When this is the case the 'user' column will display empty in the Query Analyzer output. It is not a bug in Monyog but a limitation with the general log itself.
You can sort the display by clicking on the column header. Note that statement grouping/counting and sorting is case insensitive.
When analyzing the slow server log (but not general log and not sniffer data) you can further click on a query and detailed information will display like this:
Explain plan is available in Query analyzer for Slow_log table based logging, Processlist based sniffer and performance schema based sniffer.
In this, there is an option to 'replace literals from the query'. The purpose of this option is to eliminate small differences between 'almost identical' queries. Currently 'quoted strings' and numbers are replaced with the dummy string '?' only. Note the filtering settings are stored for that particular session which is not permanent.
SELECT * FROM customer_master WHERE cust_id = 23 AND address = ’r;#23 fleet street’;
SELECT * FROM customer_master WHERE cust_id = ? AND address = ?;
The reading limit 'All' is selected it considers the whole file for analyzing but if the option is 'Last', it reads the last specified chunk in KB, MB or bytes out of the whole log file. Also, you can define a timeframe to be analyzed and the size of the 'log chunk' (in KB, MB and Bytes for file based logs and in rows for table-based logs) to be transferred to Monyog. Note if 'All' is selected in the list, it won't consider any timeframe but just displays all queries within the specified size/chunk. Also note it is the 'smallest' of those two settings that will have effect for the analysis! For analyzing the 'sniffer pseudo log' there is no 'chunk size' to be defined as the complete 'pseudo log' as stored in the Monyog database will be considered. The selected 'log chunk' will need to have statements for the selected period. If not then Monyog will of course only display data from the first log record available.
Include User and Host Information: If this option is selected it will display the 'user and host' of that particular query and it will group the query analyzer table based on 'user@host' and 'query'.
If Monyog is already installed in the machine and Sniffer is enabled, it will display only the 'user' info in Query Analyzer table because old Monyog never used to store the 'host' information in 'sniffer.data' table. Also note, that this option is not supported by MySQL Proxy. In General query log, if "connect" string is not included in the specified chunk, we will not display the 'user@host' information which will be just left as an empty space.
Export As CSV
The option to define the field delimiter is provided because some localized Windows programs for LOCALEs where comma " , " is used as a decimal sign will require a semicolon " ; " as field separator. This includes Microsoft Office programs (Excel and Access) and Microsoft text-ODBC driver on such localized Windows. On Linux, the situation is more non-uniform but also such localized OpenOffice Calc (spreadsheet) requires semicolon " ; " as field separator.
This feature is available in Monyog Enterprise and Ultimate.