Understanding how database connections work in Laravel

Hi,

We use mysql on the back end on our system. We have many long running jobs (processing videos etc) and during the time these long running jobs are actually running we see a database connection being held open to our database server. It's causing us some problems as we scale.

I know in the old days of my PHP programming (pre Laravel) the connection to the database would stay open for the duration of the script and then exit. That is great for quick web hits, etc.

However, with these long running jobs (that sometimes can run for 1-2 hours or longer) it's a problem.

I wanted to see if someone could explain to me how the database connections work and if there is a way for me to somehow force the connection to get released or if you have any ideas on this, maybe it would time out or something and then when a model is called that needs data it would get a new connection?

Edited to add: There is a while (true) { sleep(2); } in my long running jobs that does the video processing. Possibly prior to that while loop being called I can disconnect from the database and then when the long running process ends I can reconnect to the database as the remainder of the job does indeed connect to the database and update date.

Thanks for your help.

-Rob

Niush

2 years ago

Level 50

Yes, Laravel (PHP) opens a connection to DB when it needs to. And, automatically closes the connection at the end of application.

In your case you might want to manually disconnect the database, and reconnect whenever you require (may be after the video is processed). Example:


// Video::find(1);
// ...

sleep(5); // database is connected initially

\DB::disconnect('mysql'); // disconnect

sleep(5); // database is NOT connected

\DB::reconnect('mysql'); // reconnected again

I think the manual reconnect is not really required. Laravel should do it automatically.

robdesilets

2 years ago

Level 5

Great thanks for the response. We are using Laravel Job queue (running under supervisor using Redis). Would it be correct to say that a job queue also keeps a connection to the database open? Although it's using Redis as the back end of the job queue would there be any reason it would also hold a database connection open?

Niush

2 years ago

Level 50

@robdesilets Yes, a queued job also needs to bootstrap the application (Laravel), just like handling HTTP requests. It opens database connection as usual.

For something like video processing, I would instead use services like; https://www.mux.com/

The video processing is CPU and Memory intensive causing your server to slow down. And, the queue worker that is handling the video processing will be locked. So you will require more workers to process more videos that needs more CPU.

While, these dedicated and battle-tested services can handle the hard part for you.

robdesilets

2 years ago

Level 5

@Niush Thanks for the great information, I really appreciate you taking the time to reply - and I hope others that read this in the future will find it informative.

My last question:

I [now] understand that Laravel bootstraps for a Worker so it will open up a database connection when it starts. I use Redis for my job queue and I can see in the source code for a Worker that it's constantly connecting to Redis for information (e.g to see if it needs to restart, etc). I actually extend the Worker class and made some changes to some method implementations.

While a Worker is hanging around waiting for jobs to pull off the queue why does it need a connection open to the database (since I use Redis)? I can understand that if it processes a job and in the handle() method for the job there are models then it would need to connect to the database to get their information, in which case it would connect and grab the data to populate the models.

When I start a worker if I was to disconnect from the database after it's booted do you see any inherent problems with that? It seems like the code that checks Redis to pull a job off the queue does not have anything to do with mysql. I know when the job fails it will make an entry in failed jobs, but I would think it would grab a new connection if the one that was created when the job started was closed (by me).

Thanks!

-Rob

Niush

2 years ago

Level 50

@robdesilets The database connection is not closed automatically in a queue worker. You can manually disconnect like you said, either extending the worker class. Or, you can also use Queue::looping to trigger DB::disconnect().

The purpose of queue worker is to keep handling jobs one after another, as fast as possible (without being Idle). So, the default Laravel behavior is not bad. But for long running job (like yours), it might be unnecessary.

Also, you might like this post by Mohamed Said, on how to prevent memory leaks on queue workers. Basically restart queue workers after certain number of jobs.

robdesilets

2 years ago

Level 5

Yes, I can see for the queues that process broadcast events and other real fast events quickly have a DB connection open is just fine.

For my Workers that use Redis and that have long running jobs, do you see any disadvantages to disconnecting from the database? From my end I don't see any problem (of course I will test) but just wanted to see if you could think of anything that I was missing. As mentioned looking at the Worker code it does not seem like it would be a problem.

Thanks again!

Niush

2 years ago

Level 50

@robdesilets As far as I know, it should not cause any problem. It's okay to disconnect.

Please or to participate in this conversation.