Rudder

Hello,

Thanks for reporting that problem. I don't think the problem you are describing is a config one, more like a bug. Unfortunatly, our postgres expert is on hollidays this week.
I see here that the problem is with "select 1" which is the default request to test connection availability used by the pool manager. I will start to look for that.

Is there other request in errors? Or perhaps do you have more information on the table impacted? It would help a lot to understand why some transaction get stalled (and I'm sorry I can't help more for now).

Also: does the problem started concurrently with some special event? (I would understand that you don't have the least idea about that).

Thinking a bit more about it, the select 1 may be normal. As it is the query used by the pool to test connection health, it's normal to have some. I'm not sure about the status.

So, do you have other request? Where are reported the "dead row versions cannot be removed yet" entries if you try a VACUUM FULL VERBOSE ANALYZE?

Oh the problem is with the "SELECT 1" - I have tested by ending the query with pg_terminate_backend(8071) and
checked by ending jetty (which owns the connection from local port 48393).
As soon as the "SELECT 1" is gone - vaccum works.

Just to be sure I understand: VACUUM works before you kill jetty? Because it could be an other request done by Rudder which is also cleared when you kill jetty that block things.

OK, so I'm going to try to see if we can use something else (or at least not in a transaction). Thanks for both reporting and helping with the diagnostic!

VACCUM works if "SELECT 1" with state "idle in transaction" is gone.
I can do this by
a) ending the postgres process
or
b) ending jetty

With a "idle in transaction" query vaccum reports dead row versions and does not free disk space.

Is the "Seclect 1" only done at jetty start?
I ended the query 1 hour ago and it hasn't come back, yet.

OK, thank you very much for the clarification, there's no doubt it is the culprit.

"SELECT 1" is used in the connexion pool (hikaricp, for the records) to test if a connexion is alive (and spawn the correct number based on pool size). It should do it at regular interval.

So maybe there is a set of condition needed to have a select 1 stuck. It could be linked with something that happens only at startup (for ex something is not fully ready, and it breaks).

Now that the culprit is clearly identified, we know what to change. Thank you!

We have commited a patch that avoid using SELECT 1 as documented here: https://github.com/brettwooldridge/HikariCP (search for `connectionTestQuery`). You should be able to test with tomorrow nightly if it correct the problem for you.

Thank you - I will test the patch next week.

Idle in transaction means that a transaction began, and never ended.
So here a query started, and connection pool checked the validity of connection with select 1 (hiding previous query)

Removing the SELECT 1 may or may not fix the issue, but at least it will allow to see the actual query being performed

Tobias, I have one last question for you
Do you happen to have messages like

2019-04-19 00:01:31 CEST [11185]: [1-1] LOG:  sending cancel to blocking autovacuum PID 14822
2019-04-19 00:01:31 CEST [11185]: [2-1] DETAIL:  Process 11185 waits for AccessExclusiveLock on relation 16512 of database 16386.
2019-04-19 00:01:31 CEST [11185]: [3-1] STATEMENT:  vacuum full archivedruddersysevents
2019-04-19 00:01:31 CEST [14822]: [1-1] ERROR:  canceling autovacuum task
2019-04-19 00:01:31 CEST [14822]: [2-1] CONTEXT:  automatic analyze of table "rudder.public.archivedruddersysevents" 

I do have
LOG: checkpoints are occurring too frequently (18 seconds apart)
HINT: Consider increasing the configuration parameter "checkpoint_segments".
ERROR: canceling autovacuum task
CONTEXT: automatic vacuum of table "rudder.public.archivedruddersysevents"

although my housekeeping script does not have errors anymore (production).

I am setting up a test environment to check the nightly.

I installed latest nightly on the test server and since restart I haven't found
a "SELECT 1" or "idle in transaction". Looks good.

Hello, would you mind looking if you have a problem now? As we don't know exactly what triggered the problem, we need to check after some time.
So:

- do you have a query in "idle in transaction" (other than SELECT 1, since we replaced it) ?
- does the VACUUM say it can perform everything without dead row ?

Thanks !

Hello,

there are no "idle in transaction" queries ( and "SELECT 1" hasn't come up again either).
All vacuum operations have finished without warnings about "dead rows".

Fix seems to work - thank you.

That's great, thank you for letting us know. We are going to cut a release in the next couple of days with that fixe. Thank you again!