How to Kill Long Running Queries From the Shell Step by Step

A report query has run for two hours, the application is timing out behind it, and the database is the shared resource. Stopping one query is a routine operation with a few ways to get wrong: the wrong session, a kill that causes a long rollback and a script that kills too broadly. This post shows the steps for PostgreSQL and MySQL, listing first, and the settings that stop the repeats. It follows finding slow queries. Examples use PostgreSQL 17 and MySQL 8.4.

PostgreSQL: find

psql -d postgres -c "
select pid, usename, state, now() - query_start as running, wait_event_type, left(query, 70) as query
from pg_stat_activity
where state <> 'idle' and pid <> pg_backend_pid() and now() - query_start > interval '5 minutes'
order by running desc"

  pid  | usename |        state        |   running    | wait_event_type |  query
-------+---------+---------------------+--------------+-----------------+---------------------
 28411 | report  | active              | 02:04:17.2   |                 | select ... from events
 28955 | app     | idle in transaction | 00:41:09.8   | Client          | update accounts set ...

The first is a query running for two hours. The second is a session sitting idle in transaction: it did work, never committed, and holds locks and prevents vacuum from cleaning up. Both matter, in different ways. The wait_event_type column says what the session waits for; a Lock there means it is blocked by another session, and the blocker is the one to look at (pg_blocking_pids(pid) gives its pid).

PostgreSQL: cancel, then terminate

psql -d postgres -At -c "select pg_cancel_backend(28411)"
sleep 5
psql -d postgres -At -c "select pg_terminate_backend(28411)"

pg_cancel_backend sends a cancel request, like Ctrl-C in psql: the query stops with an error and the session stays. pg_terminate_backend ends the whole connection and rolls back the open transaction. Try the first, and use the second for sessions that ignore it or are idle in a transaction. You need to be the same role, a member of that role, or superuser; pg_signal_backend membership grants the right to others' non-superuser sessions. Never kill the process at the operating system level with kill -9 on a backend: it makes PostgreSQL restart all connections.

MySQL

mysql -e "select id, user, time, state, left(info, 70) as query from information_schema.processlist where command = 'Query' and time > 300 order by time desc"
mysql -e "kill query 18204"
mysql -e "kill 18204"

KILL QUERY stops the current statement and keeps the connection; KILL (or KILL CONNECTION) closes it. The time column is seconds in the current state. To kill by a filter, generate the statements and review them before running: mysql -N -e "select concat('kill query ', id, ';') from information_schema.processlist where user='report' and time > 300" > kills.sql; cat kills.sql. Only after reading it, pipe it to mysql.

Rollback takes time

Killing a statement that has changed millions of rows triggers a rollback, which is often as slow as the work was, and the locks stay until it finishes. In MySQL, show engine innodb status shows the rolling-back transaction; do not restart the server to speed it up, since recovery would then have to finish the rollback anyway. For a read-only query, the kill is instant. Think before killing a big update: sometimes waiting for it to finish is faster.

Prevent repeats

psql -c "alter role report set statement_timeout = '10min'"
psql -c "alter database app set idle_in_transaction_session_timeout = '5min'"
mysql -e "set global max_execution_time = 600000"

A role-level statement_timeout cancels any query from the reporting user after ten minutes, so a runaway report cannot hold the database. idle_in_transaction_session_timeout ends sessions that forget to commit. MySQL's max_execution_time (in milliseconds) applies to read-only SELECT statements. Also set a lock_timeout in migration scripts so that they give up instead of queueing behind a long query and blocking everyone behind them.

Where this does not apply

Do not kill replication threads, autovacuum workers (unless you know why) or backup sessions; filter by backend_type = 'client backend' in PostgreSQL and by user in MySQL. Managed services offer the same functions with their own admin roles. A killed query is a symptom treated: the lasting fix is an index, a rewritten query or a replica for reports; see database size checks and safe credentials for scripts.

Finding the right statement to stop

Killing the wrong query is worse than waiting. In PostgreSQL, list candidates with pg_stat_activity, showing the process ID, user, state, how long the query has run and its text, filtered to the active ones and sorted by duration. Read the state column carefully. active means it is running now. idle in transaction means a client opened a transaction and is doing nothing, often holding locks and blocking others, and is a frequent root cause of pile-ups and of bloat. idle means a connection with no work, harmless to kill and usually pointless. When the symptom is blocked queries, the useful question is who blocks whom, answered by pg_blocking_pids(pid), which names the sessions holding the locks that a waiting query needs. The blocker is often a transaction that has been open for an hour, not the longest-running query. Identify it by the user and the application name, and confirm that it is safe to stop before you do.

Do not kill system processes. Autovacuum workers, especially one running to prevent transaction ID wraparound, replication processes and background writers should be left alone.

Cancel first, terminate second

PostgreSQL gives two levels. pg_cancel_backend(pid) sends a cancel request that stops the current statement and leaves the connection and the session alive, so the application sees an error for that query and can carry on. pg_terminate_backend(pid) ends the whole session, rolling back its open transaction, and the client sees a lost connection. Always try the cancel first and wait a few seconds. A session that ignores the cancel, because it is stuck in a state where it cannot check for interrupts or is not running a statement at all, then gets terminated. MySQL has the same pair: KILL QUERY id stops the statement and keeps the connection, KILL id ends the connection. Find the id with SHOW FULL PROCESSLIST, which includes the full text of the query, or query the performance schema or information schema process list tables for filtering. Requires the right privilege, since killing another user's session needs administrator rights, and a limited monitoring account should not have it.

Rollback takes time and may hurt

Stopping a long write does not return the database to normal instantly. A killed transaction must be rolled back, and rolling back work that took an hour can take a comparable time, during which the locks stay held and the table remains busy. In MySQL InnoDB, killing a large update or delete can leave the server rolling back for a long while, and the damage is greater if you then restart the server, which resumes the rollback at startup before accepting connections. In PostgreSQL, rollback is cheap because aborted row versions are ignored and cleaned later by vacuum, which means the cost shows up as bloat and vacuum work afterwards and not as a long wait. Knowing this changes the decision: for a huge write that is nearly done, waiting may be quicker than killing. For one that has just started, cancelling is cheap. Never restart the database to clear a query without understanding the recovery time. Watch the activity view after killing to confirm the session is gone and the blocked queries have moved.

Preventing the next one

Set timeouts so the database stops runaway queries by itself. In PostgreSQL, statement_timeout limits how long any statement may run, lock_timeout limits how long it waits for a lock, and idle_in_transaction_session_timeout ends sessions that sit inside an open transaction, and all can be set per role or per database so reporting users get different limits from the application. MySQL has max_execution_time for read statements, in milliseconds, and innodb_lock_wait_timeout for lock waits. Give analysts and batch jobs their own accounts with tight limits, so an expensive exploratory query cannot take down the service. In the application, fix the cause: add the missing index, break a big batch into chunks that commit often, and close transactions promptly. Log the killed sessions with the user, the query and the reason, so a pattern, such as a nightly report that always blocks the application, is visible and gets fixed.

See PostgreSQL server signaling functions and the MySQL KILL statement.