This is helpful.
The two pending queries looks like the job of bounce runner. Since it runs every 2m to check if any Member’s bounce score is above the threshold and if any warning needs to be sent or delivery needs to be disabled.
It looks to me that the database query has been running for 1min 58sec, which is longer than I’d expect. Are you seeing that query *always* there when you dump the list of queries?
I am not sure what the empty COMMIT queries are really.
Abhilash
On Mar 18, 2021, at 5:31 PM, s.coggins@cqu.edu.au wrote:
Just to add to this. Looking at the active queries in PostgreSQL there appear to be a bunch of commits that haven't finished. I don't know how it all fits together but it looks like the commits aren't finishing and transactions are staying open, so when I hit an open transaction all write operations for the DB hang until the transaction has finished.
postgres=# SELECT pid, age(clock_timestamp(), query_start), usename, query FROM pg_stat_activity WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%' ORDER BY query_start desc; pid | age | usename |
query--------+-----------------+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
15767 | 00:01:58.30353 | mailman | SELECT member.id AS member_id, member._member_id AS member__member_id, member.role AS member_role, member.list_id AS member_list_id, member.moderation_action AS member_moderation_action, mem ber.address_id AS member_address_id, member.preferences_id AS member_preferences_id, member.user_id AS member_user_id, member.bounce_score AS member_bounce_score, member.last_bounce_received AS member_last_bounce_received, member.la st_warning_sent AS member_last_warning_sent, member.total_warnings_sent AS member_total_warnings_sent + | | | FROM member JOIN mailinglist ON member.list_id = mailinglist.list_id JOIN preferences ON preferences.id = member.preferences_id
+ | | | WHERE mailinglist.process_bounces = true AND member.total_warnings_sent >= mailinglist.bounce_you_are_disabled_warnings AND preferences.delivery_status = 318033 | 00:05:28.255636 | mailman | DELETE FROM member WHERE member.id = 169331 15762 | 00:11:42.842237 | mailman | COMMIT 15740 | 00:11:59.12324 | mailman | COMMIT 15760 | 00:12:01.212976 | mailman | COMMIT 15757 | 00:12:01.542144 | mailman | COMMIT 15755 | 00:12:01.708328 | mailman | COMMIT 15754 | 00:12:01.804737 | mailman | COMMIT 15751 | 00:12:01.921644 | mailman | COMMIT 15745 | 00:12:02.72934 | mailman | COMMIT 15743 | 00:12:02.795885 | mailman | COMMIT 15742 | 00:12:02.948795 | mailman | COMMIT 15738 | 00:12:03.3471 | mailman | COMMIT 15664 | 00:12:08.480084 | mailman | COMMIT (21 rows)
I've checked my production system and I can see the same COMMIT queries running. Some for 2 days (since the last restart).
Mailman-users mailing list -- mailman-users@mailman3.org To unsubscribe send an email to mailman-users-leave@mailman3.org https://lists.mailman3.org/mailman3/lists/mailman-users.mailman3.org/
-- thanks, Abhilash Raj (maxking)