I have several cron jobs running and the task that cron has to do is execute a file that in turn checks and compares crawled data with data in the database, if it exists then the script ends and if it does not exist it inserts the data to be used by another cron task at a later time.

I have MySQL connections set by pooling, this works as expected for front end users as when I watch the process list as I can see users of my application in real time re-use a sleeping connection in MySQL and not create a new one.

The issue I am having with cron jobs is that every time cron runs the same job it creates a new connection to MySQL on a new port and does not look to reuse its last connection for that same task from the pool via the same port even though I use connection.release();. Is this normal behaviour from cron?.. If it isn't normal then is there a way to force re-using of the same port by the same repeating query?.

The outcome of this is that after 3-4 days I have hit the connection limit and have to restart server to clear this and to begin again. Obviously I would sooner get to the root of the issue without having to resort to rebooting every few days, so any advice or pointers in the right direction would help.

The system is Ubuntu 18.04 and running node processes via cron. (npm MySQL library)

  • 1
    Cron Jobs are Programms started with no persistent process state, they simply can’t pool on the client side (at least not in between invocations). You can either have a server (and potentially trigger it with a cron job) or use reverse proxy style pooling components (which allow the cron job to open new connections but keep the database sessions open). However I wonder where is your problem with those new connections, it’s unlikely cron Jobs happen in high frequency. – eckes Mar 10 '19 at 19:46
  • @eckes thank you for your reply, at the time of writing the question I had 3 seperate (but related) cron jobs running at 15 minute intervals, I have since trimmed this down to 3 jobs now at 30 minute intervals. Based on what you say I think my best option is to remove them from pooling as it serves no purpose and rewrite the 3 scripts that cron runs into one script to use one connection to MySQL and query the tables three times in order of process by using Async Series on the MySQL queries. Do you see this has being a viable alternative? – MartinJJ Mar 10 '19 at 21:22
  • 1
    Pooling inside the script might help with validation, reconnect and if It is multithreading it even reduces connections. But it does not avoid tearing down all connections at the end of the script run. For periodical stuff it’s better to loop in a single script/daemon. – eckes Mar 10 '19 at 21:25
  • How often is the cron job run? Once a minute? One extra connection per minute is insignificant in the grand scheme of things. 1000 new connections/second is not a problem. – Rick James Mar 14 '19 at 2:32
  • @RickJames It was running at once every fifteen minutes between 9am and 9pm with a 12 hour cool off where the sleeping connections would time out and in the morning would start afresh, however the script also had 3 other cron jobs running which were dependent on it (again each of these ran at 15 minutes). So basically 16 cron jobs per hour took it to 128 before any wait timeouts came into effect which added on to other connections. Since I made this post though i changed it down to 2 runs at 30 minutes, I've now consolidated everything into one script which I will be uploading later today. – MartinJJ Mar 14 '19 at 11:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.