How to disable the slow query log. Query profiling in MySQL
concept
Server logs (log files, server log)- files stored on the server that contain server system information, as well as logging all possible data about the visitor to the web resource.
Logs are used by system administrators to analyze visitors, studying the patterns of behavior of certain groups of users, as well as obtaining various information about them, such as: the browser used, IP address, data on the geographic location of the client, and much more. In addition to the analysis, in this way you can find out about unauthorized access to the site, find out more precisely by whom it was made, and transfer data about this case to the appropriate authorities.
The data in the log file, in its pure form, will not be understandable to ordinary users who will see in all this just a set of characters in an incomprehensible order. But for system administrators and web developers, this is quite readable text, and quite useful information.
Sequence of events
Each time a client accesses a web resource, several events are triggered at once, the sequence of which we will talk about.
1. Making a page request. When you enter an address in the browser line, or when you click on an active web link, say, from a search results page, the browser searches and connects to the server on which the page is located and requests it. At the same time, it sends the following information to the server:
- IP address of the client computer that requests the page (if using a proxy server, the IP address of your proxy);
- address of the Internet page requested by the user (IP address);
- exact time and the date the request was made;
- data on the actual location of the client (if a proxy server is used, then the actual address of the proxy);
- information about the browser used by the client (name, version, etc.);
- data about the web page from which the client was transferred.
2. Transfer of the requested data. The requested data (web page, files, cookies, etc.) is transferred from the server to the user's computer.
3. Writing to the server log. After all, a log entry occurs, which indicates all the data that appeared in the last two events. This is all the information sent in the first paragraph, as well as information about the transmitted data.
How to view server logs
Log files are stored in a file access log no matter what type of web server you use (Apache, Nginx, squid proxy etc.) This file is text document, on each line of which, one call is recorded. Recording formats in access log quite a lot, but the most popular is combined, in which the record has the following form and sequence:
Code: %h %l %u %t \"%r\" %>s %b \"%(Referer)i\" \"%(User-Agent)i\"
Where:
%h- host/IP address from which the request was made;
%t- the time of the request to the server and the time zone of the server;
%r- version, content and type of request;
%s- HTTP status code;
%b- the number of bytes sent by the server;
%(Referrer)- URL source of the request;
%(User-Agent)- HTTP header with information about the request (client application, language, etc.);
%(Host)- the name of the Virtual Host being accessed.
in finished form given string looks like this:
127.0.0.1 - - "GET /index.php HTTP/1..0 (compatible; MSIE 7.0; Windows NT 5.1)"
Reading the logs manually will take quite a lot of time and effort. Therefore, experienced webmasters use special software called "Log file analyzers". They analyze all the data that is quite difficult for human reading and produce structured data. These are programs such as: Analog, WebAnalizer, Webalizer, Awstats, Webtrends, etc. kinds of special software quite a lot, among them there are paid programs, as well as free. Therefore, I am sure that everyone will find something to their liking.
Where to find site logs
If you have regular hosting, then most likely you will have to write to your hosting provider and ask him for logs. Also, quite often, you can request them through the host panel. Different hosts do it differently. For example, to request from my hosting provider, just make one click on home page panels:
If you have access to the server's system folders, then you can find the logs at /etc/httpd/logs/access_log in 99 cases out of 100.
Error log error.log
Error.log- a file in which logs are also kept. But not visitors, but errors that occurred on the server. As in the case with access log, each line of the file is responsible for one error that has occurred. The recording is made taking into account such information as: the exact date and time the error occurred, the IP address to which the error was issued, the type of error, and the reason for its occurrence.
Conclusion
Logs are quite a powerful and informative tool to work with. But in our time, they are being replaced by such tools as Yandex.Metrica, Google Analytics, etc., thereby simplifying our lives. However, if you plan to develop, grow and learn something new, I definitely recommend that you get to know this topic better.
Query profiling in MySQL used to evaluate the performance of your application. When developing medium and large applications, you have to deal with hundreds of requests distributed throughout your code, which are executed every second. Without query profiling techniques, it can be very difficult to find what is causing your application to suffer performance.
What is the slow query log in MySQL?
The MySQL slow query log is a log that flags slow and potentially problematic queries. MySQL supports this functionality by default, but it is disabled. By setting certain server variables, we can specify which requests we are interested in. Most often, we need queries that take a certain amount of time to complete or queries that do not process indexes correctly.
Setting profiling variables
The main variables for configuring the request log:
slow_query_log G slow_query_log_file G long_query_time G / S log_queries_not_using_indexes G min_examined_row_limit G / S
Comment: G - global variables, S - system variables
- slow_query_log - boolean value including log
- slow_query_log_file - absolute path to the log file. Directory owner must be user mysqld, and the directory must have the correct read and write permissions. Most often, the mysql daemon runs as a user. mysql.
To check, run the following commands:
Ps -ef | grep bin/mysqld | cut -d" " -f1
The output of the command will give you the current username and mysqld username. An example of setting up the /var/log/mysql directory:
cd /var/log sudo mkdir mysql sudo chmod 755 mysql sudo chown mysql:mysql mysql
- long_query_time - time in seconds to check the duration of the query. For example, with a value of 5, every request longer than 5 seconds will be logged.
- log_queries_not_using_indexes - boolean value, enables saving queries not using indexes. Such queries are very important in the analysis.
- min_examined_row_limit - specifies the minimum value for the number of data rows to be analyzed. A value of 1000 will ignore queries that return less than 1000 rows of values.
You can set these variables in the MySQL configuration file, dynamically through the MySQL GUI, or command line MySQL. If the variables are specified in the configuration file, then the server will set them the next time it is started. Typically, this file is located at /etc, /usr, /etc/my.cnf, or /etc/mysql/my.cnf. Here are the commands to search for the configuration file (sometimes you should expand the search to other root directories):
Find /etc -name my.cnf find /usr -name my.cnf
When you find the file, add the required variables in the section:
; ... slow-query-log = 1 slow-query-log-file = /var/log/mysql/localhost-slow.log long_query_time = 1 log-queries-not-using-indexes ; no value needed here
Changes will only take effect the next time you start MySQL, if you need to dynamically change parameters, use other methods of setting variables:
mysql> SET GLOBAL slow_query_log = "ON"; mysql> SET GLOBAL slow_query_log_file = "/var/log/mysql/localhost-slow.log"; mysql> SET GLOBAL log_queries_not_using_indexes = "ON"; mysql> SET SESSION long_query_time = 1; mysql> SET SESSION min_examined_row_limit = 100;
You can check the values of variables in the following way:
Mysql> SHOW GLOBAL VARIABLES LIKE "slow_query_log"; mysql> SHOW SESSION VARIABLES LIKE "long_query_time";
The main disadvantage of dynamic installation is that the values will be lost at system startup. It is recommended to specify important parameters in the MySQL config.
The note: Syntax for dynamically setting parameters via the SET command and using configuration file slightly different, like slow_query_log / slow-query-log . You will find a complete description of the syntax in the official DBMS documentation. The Option-File format is used for the configuration file, System Variable Name - variable names when setting values dynamically.
Data generation for query profiling
We have considered the main points of profiling settings, now we will create the queries of interest to us. This example was used on a running MySQL server without any presets log. Sample queries can be run both through the MySQL GUI and DBMS command tools. When monitoring the log of slow queries, it is common to open two windows with a connection: one to run queries, the other to view the log.
$> mysql -u -p mysql> CREATE DATABASE profile_sampling; mysql> USE profile_sampling; mysql> CREATE TABLE users (id TINYINT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(255)); mysql> INSERT INTO users (name) VALUES ("Walter"),("Skyler"),("Jesse"),("Hank"),("Walter Jr."),("Marie"),("Saul "),("Gustavo"),("Hector"),("Mike"); mysql> SET GLOBAL slow_query_log = 1; mysql> SET GLOBAL slow_query_log_file = "/var/log/mysql/localhost-slow.log"; mysql> SET GLOBAL log_queries_not_using_indexes = 1; mysql> SET long_query_time = 10; mysql> SET min_examined_row_limit = 0;
Now we have a database with test data. We started profiling, but we deliberately set the response time and the number of lines to be small. To view the log, use the command:
Cd /var/log/mysql ls -l
In theory, the log file should not yet exist, since we have not made queries to the database. If it exists, then it means that profiling was configured earlier, and this can distort the results of the example. Run in console:
MySQL > USE profile_sampling; mysql> SELECT * FROM users WHERE id = 1;
Our query uses the Primary Key index from the table. The request worked very quickly using the index, so it should not be reflected in the log. Note that the log file should not have been created.
Now do the following:
Mysql> SELECT * FROM users WHERE name = "Jesse";
We have not used indexes here. Now we should see this request in the log:
Sudo cat /var/log/mysql/localhost-slow.log # Time: 140322 13:54:58 # [email protected]: root @ localhost # Query_time: 0.000303 Lock_time: 0.000090 Rows_sent: 1 Rows_examined: 10 use profile_sampling; SET timestamp=1395521698; SELECT * FROM users WHERE name = "Jesse";
Let's consider one more example. Raise the bar for the number of lines in your response and run the following query:
mysql> SET min_examined_row_limit = 100; mysql> SELECT * FROM users WHERE name = "Walter";
The request will not be reflected in the log, since we did not exceed 100 lines in the response to the request.
The note: If the data is not displayed in the log, then the following factors should be considered first. The first is the rights to the directory where the log file is stored. The group and user must match the mysqld user, the rights must be chmod 755. Second, profiling may have been configured earlier. Remove any existing profiling variable values from the configuration file and restart the server, or set the variables dynamically. If you used the dynamic method, then log out and log back into the MySQL console.
Analysis of query profiling data
Consider the above example:
# Time: 140322 13:54:58 # [email protected]: root @ localhost # Query_time: 0.000303 Lock_time: 0.000090 Rows_sent: 1 Rows_examined: 10 use profile_sampling; SET timestamp=1395521698; SELECT * FROM users WHERE name = "Jesse";
Here we see:
- The time the request was started
- The user who made the request
- Opening hours requests
- Block duration
- Number of rows selected
- Number of lines parsed
This data is very useful, as with its help we will be able to find and eliminate the cause of the system slowdown. Also, a MySQL developer or administrator will always be able to see problematic queries and I would like to note that finding them here is much faster than studying the application code. With long profiling, you can track the operating conditions at low speed.
Using mysqldumpslow
The log constantly writes data, as a rule, it writes much more than it is read from it. With a large log size, it becomes problematic to read it. MySQL includes the mysqldumpslow tool, which helps keep log integrity. The program itself is combined with MySQL (on Linux systems). To use it, run the required command and pass it the path to the log file:
sudo mysqldumpslow -t 5 -s at /var/log/mysql/localhost-slow.log
There are a number of options to help customize the output of the command. In the example below, we will see the last five requests sorted by average duration. As a result, reading the log becomes much more convenient. (output modified to show real values in log):
Count: 2 Time=68.34s (136s) Lock=0.00s (0s) Rows=39892974.5 (79785949), [email protected] SELECT PL.pl_title, P.page_title FROM page P INNER JOIN pagelinks PL ON PL.pl_namespace = P.page_namespace WHERE P.page_namespace = N ...
What we see:
- Count - the number of occurrences of the request in the log
- Time - average and total request time
- Lock - table lock time
- Rows - Number of selected rows
The command excludes numeric and string query data, that is, queries with the same WHERE clause will be considered the same. Thanks to this tool, you do not have to constantly look at the log. Due a large number command parameters, you can sort the output as you like. There are also third-party developments with similar functionality, such as pt-query-digest .
Request Breakdown
There is one more tool that you should pay attention to, which allows you to break down complex queries. Most often, you have to take a query from the log, and then run it directly in the MySQL console. First you need to enable profiling, and then run the query:
Mysql > SET SESSION profiling = 1; mysql> USE profile_sampling; mysql> SELECT * FROM users WHERE name = "Jesse"; mysql> SHOW PROFILES;
After profiling is enabled, SHOW PROFILES will show a table linking the Query_ID and the SQL statement. Find the matching Query_ID and run the following query (replace # with your Query_ID):
Mysql> SELECT * FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID=#;
Sample output:
SEQ STATE DURATION 1 starting 0.000046 2 checking permissions 0.000005 3 opening tables 0.000036
STATE- a step in the request execution process, DURATION- step duration in seconds. This tool is not used very often, but it can sometimes be extremely useful in determining the cause of a slow request.
Detailed description of columns:
Detailed steps description:
The note: This tool should not be used in server production mode, except for parsing specific requests.
Performance of the slow query log
The last question is how profiling affects the performance of the server as a whole. In the server's production mode, you can quite safely use such logging, it should not affect either the CPU or I/O. However, you should pay attention to the size of the log file, it should not become prohibitive. Also, from experience, I would like to note that setting the value of the long_query_time variable is equal to 1 second or more.
Important: don't use profiling tool - SET profiling = 1 - to record all queries, i.e. general_log variable in product mode and when heavy loads not recommended to use.
Conclusion
Query profiling can be very helpful in isolating a problematic query and in assessing overall performance. Also, the developer can study how they work MySQL queries his applications. The mysqldumpslow tool helps you view and process the query log. After identifying problematic queries, it remains to tune them for maximum performance.
MySQL query profiling is a useful technique for analyzing the overall performance of database-driven applications. When developing medium and large applications, as a rule, hundreds of requests are distributed over a large code base, and the database processes many requests per second. Without query profiling, it becomes very difficult to determine where and why application bottlenecks occur. This tutorial describes some useful query profiling techniques using MySQL's built-in tools.
MySQL slow query log
The MySQL slow query log (or slow query log) is the log to which MySQL sends slow and potentially problematic queries.
This feature comes with MySQL but is disabled by default. MySQL determines which queries to log into this log using special variables that allow you to profile the query based on the application's performance requirements. Typically, this log includes queries that take longer to process, and queries that have incorrect indexes.
Profiling variables
The basic server variables for configuring the MySQL slow query log are:
slow_query_log global
slow_query_log_file global
long_query_time global/session
log_queries_not_using_indexes global
min_examined_row_limit global/session
slow_query_log - Boolean variable to enable and disable the slow query log.
slow_query_log_file - absolute path of the query log file. The file's directory must be owned by the mysqld user and have appropriate read and write permissions. The mysql daemon will most likely be started as mysql, but to be sure, run the following command in a Linux terminal:
ps -ef | grep bin/mysqld | cut -d" " -f1
The output will show the current user and the mysqld user.
cd /var/log
mkdir mysql
chmod 755 mysql
chown mysql:mysql mysql
- long_query_time - time in seconds to check the query length. If set to 5, all requests that take more than 5 seconds to process will be logged.
- log_queries_not_using_indexes - boolean An that determines whether queries that do not use indexes should be logged. In the analysis, such queries are important.
- min_examined_row_limit - Specifies the minimum number of rows to parse. With a value of 1000, all queries that parse less than 1000 rows will be ignored.
MySQL server variables can be set in the MySQL config file or dynamically using the user interface or command line. MySQL rows. If the variables are set in the configuration file, they will persist across server restarts, but you must restart the server to activate them. The MySQL configuration file is usually located in /etc/my.cnf or /etc/mysql/my.cnf. To find the configuration file, type (may need to expand the search to other root directories):
find /etc -name my.cnf
find /usr -name my.cnf
Once you find the configuration file, add the required variables to the section:
….
slow-query-log = 1
slow-query-log-file = /var/log/mysql/localhost-slow.log
long_query_time = 1
log-queries-not-using-indexes
You must restart the server for the changes to take effect. If changes need to be activated immediately, set the variables dynamically:
mysql> SET GLOBAL slow_query_log = "ON";
mysql> SET GLOBAL slow_query_log_file = "/var/log/mysql/localhost-slow.log";
mysql> SET GLOBAL log_queries_not_using_indexes = "ON";
mysql> SET SESSION long_query_time = 1;
mysql> SET SESSION min_examined_row_limit = 100;
To check the values of variables:
mysql> SHOW GLOBAL VARIABLES LIKE "slow_query_log";
mysql> SHOW SESSION VARIABLES LIKE "long_query_time";
One disadvantage of changing MySQL variables dynamically is that the variables will be lost when the server is restarted. Therefore, all important variables that need to be saved should be added to the file.
Generate a query for profiling
Now you are familiar with the settings for slow query logs. Try generating query data for profiling.
Note: The example shown here was run on a running MySQL instance with no slow query logs configured. These test queries can be run via GUI or the MySQL command line.
When monitoring the slow query log, it is useful to open two terminal windows: one connection for sending MySQL statements, and the second one for viewing the query log.
Go to MySQL server using the console as a user with SUPER ADMIN privileges. First, create a test database and table, add dummy data to it, and enable the slow query log.
Note: Ideally, this example is best run in an environment without any other applications using MySQL to avoid cluttering up the query log.
$> mysql -u -p
mysql> CREATE DATABASE profile_sampling;
mysql> USE profile_sampling;
mysql> CREATE TABLE users (id TINYINT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(255));
mysql> INSERT INTO users (name) VALUES ("Walter"),("Skyler"),("Jesse"),("Hank"),("Walter Jr."),("Marie"),("Saul "),("Gustavo"),("Hector"),("Mike");
mysql> SET GLOBAL slow_query_log = 1;
mysql> SET GLOBAL slow_query_log_file = "/var/log/mysql/localhost-slow.log";
mysql> SET GLOBAL log_queries_not_using_indexes = 1;
mysql> SET long_query_time = 10;
mysql> SET min_examined_row_limit = 0;
You now have a test database and a table with some data. The slow query log is enabled. We deliberately set a high request processing time and disabled the check for the number of rows. To view the log, type:
cd /var/log/mysql
ls-l
So far, the folder should not contain a log of slow requests, since on this moment there were no requests. If there is already such a log, it means that the database has already encountered slow queries since you enabled the slow query log support. This may skew the results of this example. Return to the MySQL tab and run:
mysql> USE profile_sampling;
mysql> SELECT * FROM users WHERE id = 1;
The executed query simply retrieves the data and uses the index of the first key from the table. This query was fast and used an index, so it is not logged in the slow query log. Go back to the directory and check that no query log has been created. Now return to the MySQL window and run:
mysql>
This query does not use an index. Now, something like this should appear in the /var/log/mysql/localhost-slow.log log:
# Time: 140322 13:54:58
use profile_sampling;
SET timestamp=1395521698;
One more example. Increase the minimum number of lines to parse and send a request like this:
mysql> SET min_examined_row_limit = 100;
mysql> SELECT * FROM users WHERE name = "Walter";
The data will not be added to the log because the query parsed less than 100 rows.
Note: If the data was not added to the log, there are several factors to check. First, check the permissions of the directory where the log is created. It should be owned by the mysqld user/group and have chmod 755 privileges. You should then check to see if the server has other slow query settings that override your settings. Reset the default values to remove all slow query variables from the config file and restart the server. It is also possible to dynamically set global variables to their default values. If you are making changes dynamically, log out and log in to MySQL to update the settings.
Analysis of query profiling data
Consider the following data:
# Time: 140322 13:54:58
# [email protected]:root@localhost
# Query_time: 0.000303 Lock_time: 0.000090 Rows_sent: 1 Rows_examined: 10
use profile_sampling;
SET timestamp=1395521698;
SELECT * FROM users WHERE name = "Jesse";
This entry displays:
- Request execution time
- Who sent it
- How long the request was processed
- Length
- How many rows were returned
- How many rows were parsed
This is useful because any request that violates the performance requirements specified in the variables is logged. This allows a developer or administrator to quickly track down non-working requests. In addition, query profiling data can help you determine which circumstances are degrading application performance.
Using mysqldumpslow
Profiling can be included in database-driven applications to ensure moderate data traffic.
As the log grows in size, it becomes difficult to parse all the data, and problematic queries are easily lost in it. MySQL provides a tool called mysqldumpslow that helps avoid this problem by splitting the slow query log. The binary is linked to MySQL (on Linux), so you can simply run the command:
mysqldumpslow -t 5 -s at /var/log/mysql/localhost-slow.log
The command can take various parameters to customize the output. The example above will display the top 5 requests sorted by average request time. Such lines are more readable and also grouped as requested.
Count: 2 Time=68.34s (136s) Lock=0.00s (0s) Rows=39892974.5 (79785949), [email protected]
SELECT PL.pl_title, P.page_title
FROM page P
INNER JOIN pagelinks PL
ON PL.pl_namespace = P.page_namespace
WHERE P.page_namespace = N
…
The output shows the following data:
- Count: how many times the request was logged.
- Time: Average and total request time (in brackets).
- Lock: table lock time.
- Rows: The number of rows returned.
The command excludes numeric and string values, so identical queries with different WHERE conditions are treated as identical. The mysqldumpslow tool eliminates the need to constantly look at the slow query log, instead allowing regular automated checks. The mysqldumpslow command options allow you to execute complex expressions.
Request Breakdown
Another profiling tool to keep in mind is the complex query breakdown tool. It allows you to identify problematic queries in the slow query log and run it in MySQL. First you need to enable profiling, and then run the query:
mysql> SET SESSION profiling = 1;
mysql> USE profile_sampling;
mysql> SELECT * FROM users WHERE name = "Jesse";
mysql> SHOW PROFILES;
Once profiling is enabled, SHOW PROFILES will show a table that associates the Query_ID with the SQL statement. Find the Query_ID corresponding to the running query and run the following query (replace # with your Query_ID):
mysql> SELECT * FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID=#;
The command will return a table:
SEQ | STATE | DURATION |
1 | starting | 0.000046 |
2 | checking permissions | 0.000005 |
3 | opening tables | 0.000036 |
… | … | … |
STATE is the step in the query execution process and DURATION is the time it takes to complete that step in seconds. It's not a very useful tool, but it can help you determine which part of the query execution is causing the most latency.
Note A: This tool should not be used in a production environment.
Slow query log performance
It remains only to figure out how the slow query log affects performance. It is generally safe to run a slow query log in a production environment; neither CPU nor I/O should be affected. However, you should have a strategy for monitoring the log size so that the log does not become too large for file system. In addition, when running a slow query log in a production environment, long_query_time should be set to 1 or higher.
Conclusion
The slow query log can help you identify problematic queries and evaluate overall query performance. This gives the developer a detailed view of how MySQL queries are executed in the application. The mysqldumpslow tool allows you to manage slow query logs and easily include them in your development process. By identifying problematic queries, you can optimize query processing to improve performance.
Tags:Event logs are the first and easiest tool for determining system status and identifying errors. There are four main logs in MySQL:
- error log- standard error log that is collected during server operation (including start and stop);
- Binary Log- a log of all database change commands, needed for replication and backups;
- General Query Log— the main log of requests;
- Slow Query Log- log of slow queries.
Error log
This log contains all errors that occurred while the server was running, including critical errors, as well as stopping, enabling the server, and warnings. This is where you should start in case of system failure. By default, all errors are printed to the console (stderr), you can also write errors to syslog (default in Debian) or a separate log file:
Log_error=/var/log/mysql/mysql_error.log
# Errors will be written to mysql_error.log
We recommend that you keep this log enabled to quickly identify errors. And to understand what this or that error means, MySQL has the perror utility:
Shell> perror 13 64 OS error code 13: Permission denied OS error code 64: Machine is not on the network
# Explains the meaning of error codes
Binary (aka binary) log
All commands for changing the database are recorded in the binary log, which is useful for replication and recovery.
It turns on like this:
log_bin = /var/log/mysql/mysql-bin.log expire_logs_days = 5 max_binlog_size = 500M
# Indicates the location, expiration date, and maximum size file
Please note that if you are not going to scale the system and implement fault tolerance, then it is better not to enable the binary log. It is resource intensive and reduces system performance.
Request Log
This log contains all received SQL queries, information about client connections. It can be useful for index analysis and optimization, as well as identifying erroneous queries:
General_log_file = /var/log/mysql/mysql.log general_log = 1
# Includes log and specifies file location
It can also be enabled/disabled while the MySQL server is running:
SET GLOBAL general_log = "ON"; SET GLOBAL general_log = "OFF";
# You don't need to restart the server to apply
Slow query log
The log is useful for identifying slow, that is, inefficient queries. Read more in this article.
View logs
To view the logs on Debian (Ubuntu) you need to run:
# Error log tail -f /var/log/syslog #Query log tail -f /var/log/mysql/mysql.log # Log of slow queries tail -f /var/log/mysql/mysql-slow.log
# If the logs are not specified separately, they are located in /var/lib/mysql
Log rotation
Do not forget to compress (archive, rotate) log files so that they take up less space on the server. To do this, use the utility logrotate by editing the config file /etc/logrotate.d/mysql-server:
# - I put everything in one block and added sharedscripts, so that mysql gets # flush-logs"d only once. # Else the binary logs would automatically increase by n times every day. # - The error log is obsolete, messages go to syslog now./var/log/mysql.log /var/log/mysql/mysql.log /var/log/mysql/mysql-slow.log( daily rotate 7 missingok create 640 mysql adm compress sharedscripts postrotate test -x /usr/bin/mysqladmin || exit 0 # If this fails, check debian.conf! MYADMIN="/usr/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf" if [ -z "`$MYADMIN ping 2>/dev/null`" ]; then # Really no mysqld or rather a missing debian-sys-maint user? # If this occurs and is not an error please report a bug. #if ps cax | grep -q mysqld; then if killall -q -s0 -umysql mysqld; then exit 1 fi else $MYADMIN flush-logs fi endscript )
# Compresses and archives the necessary logs, cleans files
DDL Log
MySQL also keeps a data description language log. It collects data from operations like DROP_TABLE and ALTER_TABLE. The log is used to recover from failures that occurred during such operations. DDL Log is a binary file that is not meant to be read by the user, so do not modify or delete it.
The most important
Always enable error logging, use the query log to check the application's connection to the database, verify requests and work. The slow query log is useful for optimizing MySQL performance.