How Octane handles DB connections?

[kodmanyagha]

Hello everybody.

I love Laravel and Octane projects. I have a question about DB handling in Octane. I'm an old Java developer. In java we can connect to DB with two methods. One is create new connection for every operation (this is worst becouse if we receive high load then Mysql is becoming ridiculous). The second method is creating a connection pool. For example I'm creating 10 connections and keep them alive. When I want to execute an SQL query then I'm getting an available connection from pool, execute the SQL command and release the connection to the pool. This is best option. I can automatically scale (increase/decrease) connection count by the high load.

I want to learn how Octane handles the DB connections. Is it creating new connection for every DB operation or is there a connection pool? Thanks.

[kodmanyagha]

No one knows this?

[ttrig]

It keeps database connections in memory by the "warm" DatabaseManager.

https://github.com/laravel/octane/blob/1b2d5f5528ade10f4b9858fd3265a0aa12eba648/src/Concerns/ProvidesDefaultConfigurationOptions.php#L77

[kodmanyagha]

What means "warm"?

[ttrig]

What means "warm"?

Get the container bindings / services that should be pre-resolved by default.

It being warm has little to do with your question.

[judgej]

If a connection comes out of memory, but the database engine has since timed it out, then the framework will create a new connection. Does that new connection then become the new "warm" connection for that worker, saved back to memory?

The documentation is a little light on the details of just when things happen, such as when a worker database connection is first made - when the worker boots, when the connection is first used in a query? Will the first query always be a little slower as the connection is made, or will the connection itself be "warm" and ready to go?

[ankurk91]

Octane does not support connection pooling yet, that is biggest missing feature

[xuandung38]

Bump. Don't let this topic sink into oblivion.

[eusonlito]

On regular Laravel installation connection is open on first database request and remain open all process life (request, command, job, schedule...).

With Octane, you can configure how to handle connections from config listeners https://github.com/laravel/octane/blob/1.x/config/octane.php

By default every request call to Octane::prepareApplicationForNextOperation() and Octane::prepareApplicationForNextRequest() methods to reconnect to database as a regular request https://github.com/laravel/octane/blob/1.x/src/Concerns/ProvidesDefaultConfigurationOptions.php#L37

[binaryfire]

@eusonlito From what I can see here, the DisconnectFromDatabase class is commented out: https://github.com/laravel/octane/blob/9236600f2d2f5b93569f4ec9042f59ecbf63c06b/config/octane.php#L105

Doesn’t that mean that Octane keeps database connections around and theGiveNewApplicationInstanceToDatabaseManager class will reuse them rather than disconnecting / reconnecting?

[binaryfire]

Hi everyone. I think this video from @themsaid answers the question nicely: https://www.youtube.com/watch?v=Zf6o7ag5WPI

TL;DR "Octane opens a db connection when a worker starts and that same connection will be used to handle all incoming requests to that worker. This saves server resources because opening db connections is quite expensive."

[Alyka]

I use Octane with Swoole server, but my application kept hitting the max_connection in Postgres. It's either Octane does not use a DB connection pool or Swoole was spinning up over 100 workers for few requests. I had to use pgbouncer to solve the problem. Octane probably does not solve the problem out-of-the-box

[illusi03]

It's still has an issue for now or has solved ?
If yes, can somebody provide the link discussion please

[oleksiikhr]

If someone is using Read & Write Connections + Laravel Octane and wants it to work similar to the nginx + php-fpm approach, I can tell you how I did it.

If you look at the source, it picks a random ip from a list and makes a connection, this works in the old approach, but when we connect and don't reset the connection, this approach doesn't work anymore.

Let's say we have:

• 3 read connections
• 1 write connection

We also know that if we make different queries by selecting different connection name (DB::connection($name)), these N connections will remain active.

Actually, we can duplicate the configs 3 times, where each will be 1 IP in read, and 1 IP in write (duplicated). This would be something like mysql1, mysql2, mysql3.

Create middleware that for every next query, will take the next connection from our list and set it via DB::setDefaultConnection($name).

Done, the only thing left to do is "tweak" it, to share "write" instance connection for all connections (reduce the number of connections to the database), to handle exception if connection for any of the replicas is not available (select the next one, according to round-robin principle) so that we don't go into infinite loop if all replicas are unavailable, reset state, etc.

Result:

• All the same 3 read connections, but split as separate configs, and connection selection is done not by random within laravel, but in middleware on a round-robin principle
• 1 write connection, which is used by 1 instance.
• If you have 6 workers, each worker will have 3 read + 1 write connections open, and each database will hold 6 connections (plus another 6 if write === read mysql1 connection).

Maybe as a temporary solution, but at least you won't have all requests going to one of the replicas (if the first IP was randomly selected in each worker) and you don't have to reconnect all the time if you drop the connection. Make this middleware + duplicate the configs, you don't need to edit anything else, the whole application will use the default connection to the database.

[alexb-y42]

We ran into a problem recently that a database transaction that was started when processing an http request and wasn't committed or rolled back (due to a bug in the code) continued to exist and be used by the worker for new requests causing a weird inconsistent behaviour, as that worker was essentially using its own data snapshot isolated from the rest of the db by an open transaction.
It would be nice (not to say more) to have a listener (say, on RequestHandled) that checks if there are any uncommitted transactions.

[klipitkas]

We have the same issue using Planetscale reaching transaction timeouts using Octane with Swoole.