MySQL query log settings
SQL DM for MySQL retrieves (completely or partially) the General query log and the Slow query log from the MySQL servers it connects to, and analyzes them. Here, you see how to set up details for the connection, so that log analysis are available with SQL DM for MySQL. You 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 SUPER 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 continue to grow. You can enable and disable it, as well (SQL DM for MySQL sends the appropriate SET of statements to MySQL).
Logs written to files
First, 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.
If it is the first time you configure a server with this option, click the Fetch query log details button. MySQL server detects (it is stored in server variables) what logs are enabled and how logging is configured. Click Test Path to verify the path. SQL DM for MySQL connects and verify the existence of the file (but not its content).
The log files can be accessed from the local file system (if SQL DM for MySQL and MySQL is running on the same computer) or by using SFTP (if SQL DM for MySQL and MySQL is running on different computers). 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 SQL DM for MySQL can access them as if they were local files. No additional SSH/SFTP configuration is required in this case: the operating system takes 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.
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 Save a pop up is displayed where a user can set the new value to corresponding MySQL Server.
By default, MONyog(SQL DM for MySQL) service runs under Local System Account. If you have Slow query or General query logs in a Mapped Network Drive, SQL DM for MySQL is not able to reach it. You need to use UNC notation for SQL DM for MySQL to be able to access them. See FAQ 31 for details.
Logs written to MySQL tables
It is supported by MySQL from version 5.1. Also, SQL DM for MySQL supports when this option is available. Here, 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. SQL DM for MySQL can retrieve the server log by sending simple SELECT statements. Only the MySQL user used by SQL DM for MySQL to connect to MySQL must have SELECT privileges to the tables.
In the Query Analyzer tab select the MySQL server, the type of log (including the 'pseudo log') you want to analyze (Slow Query Log, General Query Log, or Sniffer), and click Analyze to start the analysis:
After getting the results of your Analysis, click a query to obtain the Query Details and Query Explain tabs with detailed information about the selected query:
With the General Query Log there are few specific problems:
- With multi-line queries only record the first line of the statement. The reason is that, as the log does not record the statement DELIMITERs, there is no way to tell where a multi-line statement ends. Even the option to 'Show full' is not displayed more than one line as SQL DM for MySQL 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 displays empty in the Query Analyzer output. It is not a bug in SQL DM for MySQL 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.
Filter settings
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. The filtering settings are stored for that particular session which is not permanent.
For example:
SELECT * FROM customer_master WHERE cust_id = 23 AND address = 'r;#23 fleet street';
becomes,
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 SQL DM for MySQL.
If All is selected in the list, is not considering any timeframe and just displays all queries within the specified size/chunk. Also note, it is the smallest of those two settings that 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 SQL DM for MySQL database that is considered. The selected log chunk needs to have statements for the selected period. If not, then SQL DM for MySQL of course only display data from the first log record available.
Include User and Host Information: If this option is selected it displays the User and Host of that particular query and it groups the query analyzer table based on user@host and query.
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 and requires 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. 