Run multiple jobs but limit number of sessions - PostgreSQL: pg_timetable

If you are using pg_timetable to run multiple jobs on PostgreSQL, you need to understand how to calculate the number of database sessions, so you don't overload your system. Recently, a related question came up on pg_timetable's Github Discussion page: What do you do when you get the following error message: "Failed to send chain to the execution channel?"
TLDR: See the bottom of the post for Key Takeaways.

Q: Run multiple jobs?

I have started multiple (20) jobs using timetable.add_job(). These are long-running, CPU-intensive jobs.
I have checked the timetable.log regularly and I sometimes found the following error message:
Failed to send chain to the execution channel
But the chain is running forward.
Could you explain to me what this message is?

A: Understand channel capacity

Assuming you're running the latest version, you're out of the capacity of the channel for passing chains to workers.
The channel accumulates jobs and then workers pull jobs one by one from this channel. If all of your workers are busy and new jobs are fetched for execution, the channel will be out of capacity to hold new jobs.

Solution: run multiple jobs but limit the number of sessions

• increase the number of workers
• make chains less aggressive, e.g. execute not every minute but every 10 minutes
• use AFTER interval chains, so new chains will start only AFTER the period of time the last one finished

Q: Rules regarding active sessions?

I now have 2 workers. I initiated 10 multiple chains using self-destruction and job_max_instances = 1.
After the 10 chains finished their run, I see 10 active pg_timetable sessions in pg_stat_activity and timetable.active_session contains 39 rows.
When will the 10 active sessions be idle? Because I suspect that if I want to schedule another 10 chains, maybe the active sessions in pg_stat_activity will grow up to 20, which is not suitable for my 4 cpu db server.
UPDATE: when I launched the next 10 chains, the active sessions were gone from pg_stat_activity.
But I would like to know what the rule is regarding this.

A: Overview of workers' parameters

Let's assume that when we speak about 2 workers, we are talking about 2 pg_timetable instances.
Each pg_timetable instance can use max --cron-workers + --interval-workers parallel routines according to the manual.

Solution:

In the worst scenario (highest load) we need:

• a separate connection for each of routines,
• a separate connection for the regular chain receiver (Scheduler.retrieveChainsAndRun()),
• a separate connection for the interval chain receiver (Scheduler.retrieveIntervalChainsAndRun())
• and another connection for the database logger (LogHook.send()).

That means the max connection (session) number for one pg_timetable instance (worker) would be:

In other words, if you want to limit the number of the database sessions you should decrease the --cron-workers and/or --interval-workers start parameters (default value 16 for both).

job_max_instances applies only to the chain. Since you have self-destructed chains it does nothing. This parameter allows you to run the same chain in parallel, but it's limited to the job_max_instances value, e.g. I want to start some chain every minute to process something, but no more than 10 chains at once. When the old chain finishes, the system will start a new one, so, at any point in time, there will be no more than job_max_instances chains running.

Key Takeaways - run multiple jobs on PostgreSQL using pg_timetable

• To avoid error messages, you need to understand the capacity of a channel for passing chains to workers.
• The max connection (session) number for one pg_timetable instance (worker) is:
  PgSQL

  max_conn = --cron-workers + --interval-workers + 3

  1	max_conn = --cron-workers + --interval-workers + 3

• If you want to limit the number of the database sessions you should decrease the --cron-workers and/or --interval-workers start parameters (default value 16 for both).
• Regularly check the timetable.log table for errors.

For more reading on using pg_timetable to schedule cron jobs, see my previous blog.

Stay safe, healthy, and wealthy!
Be happy! Peace! Love! ❤

Pavlo

This information about limiting active sessions is republished from messages on CYBERTEC's pg_timetable Github page:

Failed to send chain to the execution channel #427
and
active sessions #428

Texts were edited for proofreading and clarity.

In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Twitter, Facebook, or LinkedIn.