Hi,
We use the Metabase hosted package and a Postgres database as a source of data. I have begun to notice through our RDS panel that the number of connections being held by Metabase is rising at an alarming rate and needs “rebooting” in order to drop them.
I think it may be related to this thread https://stackoverflow.com/questions/35146741/lot-of-show-transaction-isolation-level-queries-in-postgres.
All connections are idle and the queries themselves are a mix but contain DB level commands as opposed to SELECT statements i.e.
SHOW TRANSACTION ISOLATION LEVEL
Is there a JDBC string I can pass to drop idle connections that are hanging??
1 Like
Hi @Kieran_m
I’m fairly sure you’re seeing this issue:
https://github.com/metabase/metabase/issues/8679
It has been extremely difficult to reproduce, which makes it so much harder to fix.
Hi @flamber,
Yes, that is exactly the behaviour we are seeing – so I suppose there isn’t a fix for this yet?
Is it something being actively worked on by the Metabase team?
At our current rate (where we are just using a small number of beta test users) I am needing to manually reboot the database weekly. As we pay for this service it is something that I will need an SLA or timeline on?
@Kieran_m It is something we’re working on, but when we cannot consistently reproduce a problem, then it’s a lot harder to fix, since we have to guesstimate a possible solution, while making sure that it won’t have any negative side-effects for anyone else.
Can you send an email to support @metabase.com, so I can better track you and the problem?
It would be extremely helpful if you can provide as much information as possible.
A possible workaround would be to have this query run every hour, day or week (depending on how often the connections gets clogged):
DO $$ BEGIN
PERFORM pg_terminate_backend("pid")
FROM "pg_stat_activity"
WHERE "pid" <> pg_backend_pid()
AND "application_name" = 'PostgreSQL JDBC Driver'
AND "state" = 'idle'
AND "client_addr" IN ( -- IPs used by Metabase Cloud
'18.207.81.126', '3.211.20.157', '50.17.234.169'
);
END $$;
Can you try to capture any queries associated with the active connections/session? That may help to debug what’s happening.
@flamber thanks for getting back to me.
I will try and get an email across today but in the meantime, I will test your workaround – thanks for the suggestion.
@jeff_evans below is an example of the queries associated with the connections:
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED
SHOW TRANSACTION ISOLATION LEVEL
SELECT DISTINCT t.typname FROM pg_enum e LEFT JOIN pg_type t ON t.oid = e.enumtypid
SELECT NULL::text AS PKTABLE_CAT, pkn.nspname AS PKTABLE_SCHEM, pkc.relname AS PKTABLE_NAME, pka.attname AS PKCOLUMN_NAME, NULL::text AS FKTABLE_CAT, fkn.nspname AS FKTABLE_SCHEM, fkc.relname AS FKTABLE_NAME, fka.attname AS FKCOLUMN_NAME, pos.n AS KEY_SEQ, CASE con.confupdtype WHEN 'c' THEN 0 WHEN 'n' THEN 2 WHEN 'd' THEN 4 WHEN 'r' THEN 1 WHEN 'p' THEN 1 WHEN 'a' THEN 3 ELSE NULL END AS UPDATE_RULE, CASE con.confdeltype WHEN 'c' THEN 0 WHEN 'n' THEN 2 WHEN 'd' THEN 4 WHEN 'r' THEN 1 WHEN 'p' THEN 1 WHEN 'a' THEN 3 ELSE NULL END AS DELETE_RULE, con.conname AS FK_NAME, pkic.relname AS PK_NAME, CASE WHEN con.condeferrable AND con.condeferred THEN 5 WHEN con.condeferrable THEN 6 ELSE 7 END AS DEFERRABILITY FROM pg_catalog.pg_namespace pkn, pg_catalog.pg_class pkc, pg_catalog.pg_attribute pka, pg_catalog.pg_namespace fkn, pg_catalog.pg_class fkc, pg_catalog.pg_attribute fka, pg_catalog.pg_constraint con, pg_catalog.generate_series(1, 32) pos(n), pg_catalog.pg_class pkic WHERE pkn.oid = pkc.relnamespace AND pkc.oid = pka.attrelid AND pka.attnum = con.confkey[pos.n] AND con.confrelid = pkc.oid AND fkn.oid = fkc.relnamespace AND fkc.oid = fka.attrelid AND fka.attnum = con.conkey[pos.n] AND con.conrelid = fkc.oid AND con.contype = 'f' AND pkic.relkind = 'i' AND pkic.oid = con.conindid AND fkn.nspname = 'stripe' AND fkc.relname = 'transfers' ORDER BY pkn.nspname,pkc.relname, con.conname,pos.n