What we’re after

We should research creating a celery task to check for database queries over five minutes long. We should also be able to run a task manually that will clear out any long running queries. Ex- cf run-task api "python manage.py clear_long_running_query" -m 4G --name clear-queries https://github.com/fecgov/fecfile-web-api/pull/645/commits/439912a0d4f6c526697a708184f7f0d81fe9ca22

Action item(s)

• Research creating a celery task to display queries over 5 mins
• Research creating a manual task to clear long running queries
• Research if we can create create a dashboard of the task log in Kibana
• Research if the #bots-long-query slack channel displays the long running queries

Completion criteria

• Research and next steps are documented

References/resources/technical considerations

SQL for canceling queries:

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
where (lower(query) like '%select count(*) as count_1%'
or  lower(query) not like '%rollback%'   
or  lower(query) not like '%rollback%'  
or  lower(query) not like '%refresh %' )
and datname <>'<database_name>'
and usename ='<username>'
and  (now() - pg_stat_activity.query_start) > interval '5 minutes'
order by pg_stat_activity.query_start desc

SQL for finding long running queries:

SELECT  pg_terminate_backend(pid)   FROM pg_stat_activity   WHERE datname <>'<database_name>'   and usename ='<username>'   AND (now() - pg_stat_activity.query_start) > interval '2 minutes';