How to Identify and Kill Queries with MySQL Command-Line Tool

Is your application slow? Are users complaining about lag? This slowdown might be because your MySQL server is struggling under the weight of a long-running or problematic database query.

When your WordPress site or web application relies heavily on its database (and most do!), a single poorly performing query can have a massive impact.

Although many third-party tools are available to help with specific problems, in this article, we will use the built-in MySQL command-line tool, which offers a direct, powerful, and quick way to diagnose these issues.

We’ll guide you through using the command line to:

1. View currently running processes using SHOW PROCESSLIST.
2. Identify the specific slow query or problematic process ID.
3. Safely terminate (KILL) the query when required.

Let’s get started!

Prerequisites

Before you can manage MySQL queries, you’ll need to ensure that you have the necessary access and permissions on your server. You’ll need the following:

1. Shell access to your server
2. MySQL user account with specific privileges. For administrative tasks like this, it is common to use the MySQL root user as it has all the necessary privileges.

How to Kill MySQL Queries via Command Line

Step 1: Connecting to Your MySQL Server via Command Line

You can access the MySQL command-line interface via SSH once you’ve connected to your server. The most common way to connect locally is using the MySQL root user. Open your SSH terminal and execute the following command:

mysql -u root -p   

Let’s break this down:

• mysql: Invokes the MySQL command-line client program.
• -u root: Specifies that you want to log in as the MySQL user named root. Replace root if you are using a different administrative MySQL user.
• -p: Tells the client to prompt you for the password. It’s more secure than typing the password directly in the command line.

📖 Suggested read: How to Change/Reset MySQL Root Password on Ubuntu Linux?

After running this command, you’ll be prompted to enter a password. Paste or type the MySQL root password you retrieved from your RunCloud dashboard. You’ll be greeted with the MySQL monitor prompt (mysql>) if the credentials are correct.

Step 2: Viewing Running Processes in MySQL

Now that you’re connected to your MySQL server via the command line, you can run the SHOW PROCESSLIST command to get a snapshot of all the active connections (threads) to your database server and what they are doing at that precise moment. Simply type the following at the mysql> prompt and press ‘Enter’:

SHOW PROCESSLIST;

While this command is useful, it truncates the actual SQL query being executed in the ‘Info’ column. For more effective troubleshooting, especially when dealing with complex or long queries, it’s highly recommended to use the extended version:

SHOW FULL PROCESSLIST;

The FULL keyword allows you to see the complete SQL statement, which is necessary for diagnosis.

The output of either command presents a table with several columns, and understanding these columns is key to identifying problematic queries:

• Id: This is the unique identifier for the connection thread. You will need this number later if you decide to terminate a query or connection using the KILL command.
• User: Shows the MySQL username associated with the connection thread. This helps you trace the query back to a specific application user or system process.
• Host: Displays the hostname or IP address (and port) from which the connection originates. This is useful for identifying queries coming from specific application servers, cron jobs, or even unexpected locations.
• DB: This column indicates the thread’s current default database. If no database is selected, it will be NULL.
• Command: Describes the type of command the thread is currently executing. For example, the query command means that the thread is actively executing an SQL statement.
• Time: This is one of the most important columns for performance troubleshooting. It tells us the amount of time (in seconds) that the thread has spent in its current state. For ‘Query’ states, a high ‘Time’ value is a strong indicator of a long-running, potentially problematic query.
• State: This column provides more granular details about what the thread is doing within its current command. Some states are benign (starting, checking permissions), but others often point towards bottlenecks or issues:
  • System lock: The query is waiting to acquire a lock on a table or row currently held by another thread. This is a common cause of application hangs.
  • Sending data: The thread is processing and sending results back to the client. If this state persists for a long time, it might indicate a query returning a huge result set or network latency.
  • Writing to net: Similar to sending data, indicates network transfer activity.
• Info: This column displays the actual SQL statement being executed by the thread.

📖 Suggested read: SQLite vs MySQL vs PostgreSQL (Detailed Comparison)

If you are using RunCloud, you can use the Slow Script Monitoring functionality from your RunCloud dashboard to identify slow database operations over time. This method is ideal for less technical users as it doesn’t require connecting to your server via SSH or performing any other command-line operations.

An alternative method offers more flexibility for users comfortable with SQL.

MySQL provides the PROCESSLIST table within the information_schema database. You can query this table directly using standard SQL SELECT statements to create powerful filters.

For instance, to find all actively running queries (Query command) that have been executing for more than 60 seconds, and order them by the longest running first, you could use:

SELECT id, info FROM information_schema.PROCESSLIST
WHERE COMMAND = 'Query' AND TIME > 60
ORDER BY TIME DESC;

This approach can be very helpful on busy servers where the output of SHOW PROCESSLIST is overwhelming. You can pinpoint the threads causing performance degradation or blocking by carefully examining the process list output. Similarly, you can filter visually by ‘User’ or ‘Host’ if you suspect a particular application or job is causing trouble.

📖 Suggested read: How to Connect a MySQL Database to PHP (A Developer’s Guide)

Step 3: Analyzing the Query (Optional but Recommended)

Before killing a slow or seemingly stuck MySQL query, it’s important to investigate the underlying cause first to ensure it doesn’t happen again. You can begin by copying the complete query text from the ‘Info’ column associated with the problematic process.

Once you have this query text, the next critical step is understanding its execution plan. In a separate MySQL session, execute EXPLAIN <query_text>; to get an overview of your SQL command. Replace the <query_text> with the SQL statement you retrieved.

This EXPLAIN command provides insights into how MySQL intends to execute the query. This can reveal potential bottlenecks, such as full table scans, which would indicate potentially missing indexes on columns used in WHERE or JOIN clauses, inefficient join types, or an unexpectedly high number of rows being examined.

Fixing the underlying issue leads to long-term performance gains. For example, let’s assume optimizing a frequent query saves just 20% of its CPU time. That could mean your current server can handle significantly more traffic, or you might even be able to downsize to a smaller, cheaper AWS instance, which would directly save money while providing a faster experience for your users.

📖 Suggested read: MariaDB vs MySQL – A Detailed Comparison & How You Should Choose

Step 4: Killing the Query or Connection (KILL)

Once you’ve identified a suspicious query using the command described above, you can use the kill command to terminate the thread and manually restore server performance.

KILL QUERY <process_id>;

This is generally the preferred first attempt. This command tries to terminate only the specific statement that the thread is currently executing, leaving the connection itself open. This is less disruptive to the connecting application.

For example, if process ID 12345 is running a slow query, you would run:

KILL QUERY 12345;

Remember that KILL QUERY might not take effect instantly if the thread is performing an operation that cannot be safely interrupted (like writing to disk). In such cases, it will wait until the thread reaches a point where it can be safely terminated.

KILL CONNECTION <process_id>;

If the KILL QUERY command doesn’t work or if you need to terminate the entire connection associated with the thread, you can forcefully terminate the connection. This terminates the statement and drops the client connection.

KILL CONNECTION 12345;

In the following example, we can see that the database forcefully terminated the connection from the client. Therefore, you should always use this command with caution as it can lead to unexpected errors.

Step 5: Verifying the Kill

After issuing a KILL QUERY or KILL CONNECTION command, you must confirm that it worked.

The most straightforward way to do this is to run SHOW PROCESSLIST; again immediately. If the kill was successful, the process ID you targeted should no longer be in the list.

Occasionally, you might see the thread you attempted to kill still listed, but with ‘Killed’ appearing in the Command column. This usually means MySQL has registered the kill request but hasn’t terminated the thread yet. This can happen if the thread is engaged in an operation that cannot be interrupted instantly, such as waiting for disk I/O or performing cleanup tasks.

The thread will disappear shortly after showing the ‘Killed’ state. However, if you used KILL QUERY and the thread persists, it might indicate the query itself is resistant to termination in its current state. In such scenarios, you can use the more forceful KILL CONNECTION command to terminate the connection and release its resources.

Important Considerations and Best Practices for Killing MySQL Queries

While the MySQL command-line tool provides a direct way to manage running queries, using the KILL command should always be done thoughtfully and with an understanding of the potential repercussions.

• Kill with Caution: Terminating queries, especially KILL CONNECTION, isn’t always clean. Be aware of the potential consequences:
  • Transaction Rollbacks: If you kill a thread executing Data Manipulation Language (DML) statements like INSERT, UPDATE, or DELETE within a transaction (particularly with InnoDB), the entire transaction will typically be rolled back to ensure data consistency. This might be desirable, but it’s important to understand it will happen.
  • Application Errors: Applications are often not designed to handle unexpected database connection drops. Killing a connection might result in application-level errors, incomplete operations, or confusing states for end-users.
  • Resource Cleanup: While modern storage engines such as InnoDB are good at cleaning up, forcefully killing threads can sometimes, albeit rarely, leave behind temporary tables or orphaned locks that might require manual cleanup later.
• Don’t Kill System Threads: Exercise extreme caution when viewing the process list. You might see threads run by internal system users (e.g., system user, event_scheduler) or replication users (often named repl or similar). Avoid killing these threads unless you have a deep understanding of MySQL internals and are sure it’s necessary and safe, as doing so can disrupt essential background processes, break replication, or even lead to server instability.
• Focus on Root Cause Analysis: Killing a query is almost always a temporary band-aid, not a permanent solution. The most important step after resolving an immediate performance crisis is to investigate why the query was slow or problematic in the first place. Was it due to missing indexes? Poorly written SQL? Inefficient application logic? A bad schema design? It is always recommended that the application code be analyzed to identify and fix the underlying issue. Otherwise, the problem is likely to recur.
• Proactive Prevention with max_execution_time: You can consider setting the max_execution_time system variable. This allows you to define a timeout (in milliseconds). The server will automatically abort queries exceeding this time limit, preventing runaway read queries from consuming excessive resources.

Final Thoughts

Identifying and killing problematic queries manually using MySQL’s command-line tool is an essential skill for any serious developer or server administrator. Knowing how to spot performance bottlenecks quickly can save your application from crashes, downtime, and user frustration.

But even with the right techniques, managing servers directly through the terminal takes time, demands technical expertise, and leaves too much room for human error.

That’s where RunCloud can transform your workflow.

RunCloud provides a simple, powerful platform that handles the heavy lifting of server management for you. Instead of spending hours troubleshooting MySQL issues through command-line sessions, you can:

• Monitor server performance and database health visually through an intuitive dashboard
• Use built-in Slow Script Monitoring to proactively catch issues before they affect users
• Automate backups, deployments, and SSH alerts – all without touching the command line
• Easily manage MySQL databases, users, and permissions without memorizing commands

Thousands of developers and businesses already trust RunCloud to manage their mission-critical servers – and for good reason. It saves time, reduces stress, and gives you peace of mind that your applications are running at their best.

Ready to experience better server management? Sign up for RunCloud today.

Stop putting out fires. Start focusing on building, growing, and delivering better results – with RunCloud by your side.

Frequently Asked Questions About Managing MySQL Queries

Managing a MySQL server often raises important questions, especially when diagnosing slow queries or optimizing database performance. Below, we answer the most common questions developers and administrators ask about viewing, analyzing, and safely killing MySQL queries.

Ready to simplify server management and focus on what matters most? Sign up for RunCloud and see why thousands of developers and businesses trust it for fast, secure, and reliable server operations.