in Databases

Killing processes in MySQL and Postgres

MySQL

In MySQL you can run “show processlist;” or “show full processlist;” to get a list of all of the queries that are running on the system and how long they have been running. If you have a runaway query, you can get the process id and kill it from within MySQL.

kill [process id];

Postgres

Postgres works in a similar fashion. One way to show the running queries in Postgres is with the pg_stat_activity function. You can do a “select * from pg_stat_activity;” and see a lot of information about the connections and queries that are running. There is a pid column that denotes the process id. Instead of using the kill command which sysadmins are already familiar with from working in a *nix command line, Postgres uses a function called pg_terminate_backend. You can run the below command once you have the pid of the query/connection you want to kill.

select pg_terminate_backend([pid]);

The advantage Postgres gets by using a function is that you can easily expand what pids get killed based on a where clause against pg_stat_activity. For instance, if I wanted to kill all connections to the database ‘testdb’, I could run the below command.

select pg_terminate_backend(pid) from pg_stat_activity 
where datname = 'testdb';

You can alter the above query to utilize whatever where clause you’d like to match. I can see a lot of value in matching on the below list for various reasons:

  • Usernames
  • Database names
  • Application names
  • Hostnames
  • Duration (now minus query start)
  • The waiting flag
  • Query State
  • Contents within the query column

Conclusion

The method Postgres uses isn’t as intuitive as what I would expect as a sysadmin or coming from MySQL, but I can see a lot of value to the way it is implemented and think it could be more efficiently used to manage runaway queries than what I am familiar with.

Write a Comment

Comment

  1. With MySQL, I tend to resort to outputing a processlist to a flatfile and then manipulating with sed or other textutils. It’s neat how Postgres has internal functionality to achieve the same results.