Slow queries

In some cases, the database server may not be able to respond quickly to a request. If a request takes more than 10 seconds to process, it is considered slow.

For each slow request, the following data is stored:

• Request time. The total time that the server took to complete the request,
• Blocking time. The time that was taken into account as the execution time of the request, but at the same time no actions took place in the database due to a conflict with other processes or waiting for the completion of other operations.
• The number of lines viewed. The number of table rows that were read from disk when the query was executed.
• Number of scanned lines (tmp). The number of rows read from temporary tables that were created to execute the query.

With an index, there is no need to look at the information in the table row. Therefore, using indexes reduces the number of rows scanned and increases the speed of query execution. Also, when using indexes, server resources are significantly saved.

Slow Query Log

To create a slow query log, you must run with the parameter:

--log-slow-queries[=file_name] mysqld 

This will create a log file. Data about requests that took longer than specified in the parameter is saved here:

long_query_time

The query execution time does not include table locking time. Slow query logging is performed immediately after the query completes. That is, in the log, queries are listed in order of completion, not in order of start.

By default, the filename matches the hostname followed by slow.log. If the path is not specified in the file name, it is saved in the data directory.

By using the slow query log, you can determine which operations should be optimized. If the log is large, use the command to get a summary of the requests:

mysqldumpslow 

To display queries that do not work with indexes, use the key:

--log-long-format 

On Linux, the command used to interrupt the command is Kill, which interrupts the process. It allows the server to continue working after major changes and upgrades without need for a reboot.

The user can interrupt any of his processes (cannot interrupt the system process or the process of another user). Root can interrupt the system process and the process of any user.

The kill command syntax is:

# kill [signal or option] process id

The main signals of the kill command:

By default, SIGTERM is used as the safest way to terminate a process. You can use both the number and the name of the signal.

To view all processes and their IDs run:

# ps -A

To interrupt the process with the specified PID, you need to run the following command (For example, for 1684):

# kill -9 1684

As a result, the process with PID = 1684 will be "killed".

The pkill command is used to terminate a process by name rather than ID. For example:

# pkill mysqld

The killall command is used to terminate all instances and child subprocesses. For example:

# killall mysqld

This will terminate all instances and child subprocesses of mysqld.

If you still have questions, please create a ticket to technical support.