What does n8n executes every 30 minutes that loads the database?

Questions
1

Hi!

We are having issues with our hosted n8n, we had to upgrade our database (AWS RDS db.r5.12xlarge $12/hour) in order to support something that the n8n does every 30 minutes, as shown in the graph, otherwise the CPU goes to 100% and jobs became stalled. We are running a deployment with 2 worker nodes and 1 main node. The issues is not on the workers/main, it’s the database.

2023-10-09-232020_3294x1463_scrot
2023-10-09-232020_3294x1463_scrot3294×1463 205 KB

The top 2 queries

SELECT "ExecutionEntity"."id" AS "ExecutionEntity_id", "ExecutionEntity"."finished" AS "ExecutionEntity_finished", "ExecutionEntity"."mode" AS "ExecutionEntity_mode", "ExecutionEntity"."retryOf" AS "ExecutionEntity_retryOf", "ExecutionEntity"."retrySuccessId" AS "ExecutionEntity_retrySuccessId", "ExecutionEntity"."status" AS "ExecutionEntity_status", "ExecutionEntity"."startedAt" AS "ExecutionEntity_startedAt", "ExecutionEntity"."stoppedAt" AS "ExecutionEntity_stoppedAt", "ExecutionEntity"."work

SELECT DISTINCT "distinctAlias"."ExecutionEntity_id" AS "ids_ExecutionEntity_id" FROM (SELECT "ExecutionEntity"."id" AS "ExecutionEntity_id", "ExecutionEntity"."finished" AS "ExecutionEntity_finished", "ExecutionEntity"."mode" AS "ExecutionEntity_mode", "ExecutionEntity"."retryOf" AS "ExecutionEntity_retryOf", "ExecutionEntity"."retrySuccessId" AS "ExecutionEntity_retrySuccessId", "ExecutionEntity"."status" AS "ExecutionEntity_status", "ExecutionEntity"."startedAt" AS "ExecutionEntity_startedAt"

Information on your n8n setup

  • n8n version: 0.236.3
  • Database): Postgresq 13.10
  • n8n EXECUTIONS_PROCESS setting (default: own, main):
  • Running n8n via (Docker, npm, n8n cloud, desktop app): docker
  • Operating system: Amazon Linux
3

this seems to be cut off , but might be renaming columns in a table ?

SELECT DISTINCT "distinctAlias"."ExecutionEntity_id" AS "ids_ExecutionEntity_id" FROM (SELECT "ExecutionEntity"."id" AS "ExecutionEntity_id", "ExecutionEntity"."finished" AS "ExecutionEntity_finished", "ExecutionEntity"."mode" AS "ExecutionEntity_mode", "ExecutionEntity"."retryOf" AS "ExecutionEntity_retryOf", "ExecutionEntity"."retrySuccessId" AS "ExecutionEntity_retrySuccessId", "ExecutionEntity"."status" AS "ExecutionEntity_status", "ExecutionEntity"."startedAt" AS "ExecutionEntity_startedAt"

Essentially, the query is creating a set of unique IDs from the ExecutionEntity table while also gathering other related information within a subquery, which might be used for further manipulations or conditions in the full query. The renaming using the AS keyword helps in perhaps better formatting or clearer identification of the data in the output.

4

That sounds like it could be a scheduled workflow triggering pruning.

Can you try setting EXECUTIONS_DATA_PRUNE env variable to false, and also DB_LOGGING_MAX_EXECUTION_TIME to 0 to see if these spikes go away?

Also, 0.236.3 is a bit old now, and a lot has changed to improve DB load since then.
Please consider upgrading soon.

2 Likes
5

Thank you @netroy, we changed the data prune and it worked. Now we don’t have those spikes.

We’re also planning the upgrade, already upgraded our sandbox environment, later today will upgrade production

2023-10-12-113105_3704x917_scrot
2023-10-12-113105_3704x917_scrot3704×917 123 KB

1 Like
6

In the last version,1.12.0 it works.

1 Like
7

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.