One of the downsides of a multi-process DBMS environment, such as MySQL, is the challenge posed by “unrequired” processes.
While these processes do not contribute to the overall DBMS performance or serve the business applications, they continue to occupy resources such as RAM and on-disk storage. As a result, the database server is unable to carry out routine tasks and serve legitimate user requests.
As more and more processes and threads get stuck and the database’s performance starts to affect the application response times and the overall user experience, the only way out is to terminate these processes.
In this tutorial, we will take a close look at how to locate and kill MySQL processes. In addition to the basics, we will consider killing processes generated by a user.
Table Of Contents
How to Terminate MySQL Processes
The process of terminating MySQL processes has two parts. We will now discuss these parts in order.
The Prerequisites
Before you try out the commands mentioned below, make sure you have the following:
- A system running a Linux distribution
- A server with MySQL or MariaDB
- A user account with sudo or root permissions
Step #1: Get the MySQL Process List
Before you can identify and kill processes, you need to connect to the MySQL server (hosted locally or remotely).
Start by launching the terminal and logging into your MySQL shell as the root user. Use the following command:
# mysql -u root -p
Enter the password when the system prompts. Once the MySQL shell is active, you will see the prompt change to mysql>.
Now, we can start the process by retrieving a list of all current sessions by running the following statement:
mysql> SHOW PROCESSLIST;
The result will show the following details:
You can see that the results show process id (Id) and the duration the process has been active. The longer a process has been active, the higher the chances that it is stuck and needs to be terminated to release the resources.
Checking the active process list is an essential activity in Linux system management. We have a comprehensive tutorial on how to list active processes.
Step #2: Terminate MySQL Processes
Now that you have identified the process(es) you need to terminate, you can proceed to use the KILL statement with the process ID. For instance, in the previous step, you can see that there is a process with an ID of 8.
We will now run the following command to terminate this process:
mysql> KILL 8;
After executing the command, the shell will show the status of the query along with the number of rows that were impacted. (for instance, Query OK, 0 rows affected (0.06 sec)).
Note that this statement terminates the database connection and all associated operations.
Before running the KILL statement, make sure that you are logged in as a MySQL root user.
Terminate All MySQL Processes by a User
The main challenge in terminating MySQL processes is that you can’t terminate multiple processes at the same time. This means if you need to delete multiple processes for a user, you would have to spend a lot of time.
However, you can use a nifty trick to terminate all processes associated with a specific user.
The trick involves the CONCAT function to generate a script comprising a list of KILL commands for each process.
Consider the following example where we are applying this idea on a user named root. You can replace this username with your intended user.
mysql> SELECT CONCAT('KILL ',id,';') FROM information_schema.processlist WHERE user='root' INTO OUTFILE '/tmp/process_list.txt';
This statement generates a file named process_list.txt, but you can rename it as you prefer. Open this file to verify that it contains the processes you intend to terminate.
Once you’ve verified the process list, execute the following statement:
mysql> SOURCE /tmp/process_list.txt;
You can refine the list of processes in the output file by incorporating specific conditions into your statement. For instance, you can add time > 1000 to the statement to include only the processes that are active for more than 1000 seconds. In this case, the SELECT CONCAT statement will be as follows:
mysql> SELECT CONCAT('KILL ',id,';') FROM information_schema.processlist WHERE user='root' and time>1000 INTO OUTFILE '/tmp/process_list.txt';
Also Read: How To Rename a Database in MySQL: 3 Easy Methods
Also Read: 3 Simple Methods to Delete Duplicate Rows in MySQL Database
Conclusion
Identifying and terminating MySQL processes are essential for maintaining optimal system performance.
However, you should be very careful in selecting processes to prevent any unintended disruptions in your MySQL server operations.
By integrating these practices, you’ll enhance the stability and efficiency of your databases, reinforcing the robustness of your RedSwitches server environment. Always remember to verify the specific processes you’re about to kill before executing any MySQL commands, as this careful approach will safeguard your data and server integrity.
If you’re looking for a robust server for your Linux projects, we offer the best dedicated server pricing and deliver instant dedicated servers, usually on the same day the order gets approved. Whether you need a dedicated server, a traffic-friendly 10Gbps dedicated server, or a powerful bare metal server, we are your trusted hosting partner.
FAQs
Q. How can I view running processes in MySQL to identify long-running Mysql queries?
To view running processes in MySQL, you can use the SHOW PROCESSLIST command in the query console. This command displays information about the active connections and threads, including each query’s process ID, the type of command being executed, and resource usage, which can help you identify any long-running queries that might be causing performance issues.
Q. What is the SUPER privilege, and why is it necessary for terminating MySQL processes?
The SUPER privilege in MySQL grants a user the ability to perform high-level administrative operations, including the ability to terminate running processes. To kill a query or connection, a user must have this privilege to ensure that db operations, especially those affecting concurrent connections and the server’s overall performance, are managed securely and efficiently.
Q. How can I kill a specific process in MySQL, and what information do I need?
To kill a specific process in MySQL, you need the process ID (PID) of the running query. You can obtain this by executing the SHOW PROCESSLIST command. Once you have the PID, you can use the KILL command followed by the PID to terminate the process. This action can help manage resource usage and resolve performance issues caused by problematic queries.
Q. Can terminating a query in MySQL affect other active processes or database operations?
Terminating a query in MySQL is generally safe and should not affect other active processes. However, it’s important to exercise caution, as killing certain processes might lead to partial data updates or rollbacks, potentially impacting database consistency. Always ensure you’re terminating the correct process to avoid unintended consequences on database operations within the relational database management system.
Q. What are the risks of having too many concurrent connections or threads in MySQL?
Having too many concurrent connections or threads in MySQL can lead to resource contention, where queries compete for system resources such as CPU and memory. This can cause performance issues, slow down query execution, and lead to an unresponsive database system. Monitoring and managing the number of active connections through the threads table and server configuration is crucial to maintaining optimal performance.
Q. How does MySQL handle separate threads for each client connection?
MySQL assigns a separate thread for each client connection in a process known as the connection thread. This design allows mysqld (the MySQL server daemon) to handle multiple database queries and operations concurrently, enhancing the system’s ability to manage multiple active processes and improve performance for a relational database management system.
Q. What should I do if I receive an error message when attempting to kill a process in MySQL?
If you receive an error message when attempting to kill a process in MySQL, first verify that you have the necessary PROCESS privilege to terminate server processes. Additionally, ensure the process ID you’re trying to kill is correct and still active. If the problem persists, consult the MySQL documentation for error message details or consider seeking help from forums or communities with expertise in database management.
Q. How can I prevent long-running queries from causing performance issues in MySQL?
To prevent long-running queries from causing performance issues, consider implementing query optimization techniques, indexing, and proper database schema design. Monitoring tools and setting thresholds for query execution times can also help identify and mitigate potential issues before they impact system performance. Additionally, configuring max connection limits and timeouts can help manage resource usage and prevent system overloads.
Q. Are there tools or features within MySQL that can help manage resource usage and performance issues related to running processes?
MySQL offers various tools and features to manage resource usage and performance issues, including the Performance Schema, which provides detailed information on server performance and resource usage. The MySQL Workbench and other third-party tools also offer graphical interfaces for monitoring and managing running processes, active connections, and performance metrics, helping database administrators optimize their relational database management systems more effectively.
Q. How can I use the command-line tool to identify and kill long-running queries in MySQL on Ubuntu?
To identify and kill long-running queries in MySQL on Ubuntu, you can use the following command-line approach. First, log in to the MySQL server using the MySQL command-line client by running mysql -u yourusername -p and entering your password. Once logged in, you can run SHOW PROCESSLIST to view currently running MySQL processes. Identify the process ID (PID) of the query you want to terminate, then use the KILL [process_id] command to stop the running query. This method allows you to manage and maintain the performance of your MySQL database efficiently.
Q. What are the steps to restart MySQL server on Ubuntu to refresh system resources, and how does it impact currently running queries and connections?
You can use the following command-line instructions to restart the MySQL server on Ubuntu, ensuring a refresh of system resources and potentially resolving performance bottlenecks. First, ensure you’re logged in to your Ubuntu system with sufficient privileges. To restart the service, run the following command: sudo systemctl restart MySQL service. This action will stop and then start the MySQL server, terminating all currently running queries and active connections. It’s important to note that while this can free up system resources and address some performance issues, it may also lead to data loss or corruption if transactions are not correctly committed. Therefore, it’s advisable to ensure all important data is saved and that the system is in a stable state before proceeding with the restart.
