Add connection pool (manage multiple connections)

[clue]

This project should support handling multiple MySQL client connections using a single client instance (connection pool). This allows sending multiple queries at once over independent connections. As such, a slow query on one connection would no longer block other queries. Here's what this could look like:

Coming soon: @reactphp with built-in connection pool to support concurrent database queries? 🔥 #async #php

– https://twitter.com/another_clue/status/1419633478216388612

I've filed #147 a while ago to discuss a simpler MysqlClient API that could be good starting point to build on top of. This appears to be preferable to implementing this as a decorator around the existing (lazy) connection.

However, certain use cases could challenge this abstraction, specifically when dealing with transactions that should not span multiple independent connections by default. Other projects solve this by providing explicit control over handling underlying connections for these use cases, see e.g. https://github.com/mysqljs/mysql#pooling-connections.

Refs existing PRs #27, #148, #172, https://github.com/szado/reactphp-connection-pool and others (ping @dshemin, @skydiablo, @wpjscc, @szado)
Probably builds on top of #147, #87 and others

We welcome contributions, reach out if you want to support this project or become a sponsor ❤️

[SimonFrings]

Status update: We started to come up with a roadmap to introduce the new MySQL connection pool feature.

To begin, we plan to replace the existing Factory with a more straightforward MysqlClient constructor (as outlined in issue #147). This change will serve as a preliminary step towards implementing the connection pool more smoothly. When working on the pool feature itself, we'll also plan to introduce a few new concepts:

• configuration for the maximum number of connections allowed
• configuration for the minimal number of connections
• configuration for a number of free connections to use for new queries (for example, always have 2 open connections ready for usage to improve performance)
• a queue for queries if all connections are currently in use

When it comes to establishing a first stable solution, defining a maximum number of connections appears to be the most important aspect. While the other configuration options should also be part of the overall feature, they can be added gradually in follow-up pull requests if needed. Let's embrace continuous improvement here!

We'll start working on an implementation the next few weeks and keep you updated along the way 👍

[wpjscc]

Hi , What kind of connection pool will be achieved in future work

such as

                                server pool
                                    |-------------------------mysql server
                                    |
                                    |
                                    |    
            +-----------------------+-------------------------+
            |                                                 |
            |                                                 |
    +----------------+                              +-----------------+
    |       |        |                              |         |       |
    |       |        |                              |         |       |
    |   connect pool |                              |    connect pool |
    |       |        |                              |         |       |
    |       |        |                              |         |       |
    |       |        |                              |         |       |
    |       app      |                              |        app      |
    +----------------+                              +-----------------+

or such as

    +----------------+
    |   connect pool --------------------------> mysql server
    |       |        |
    |       |        |
    |       |        |
    |       app      |
    +----------------+

For sever pools, it is very useful for multiple apps. If you do not want to add MySQL connections。

Currently, I had implemented these two methods, but they have not been well tested yet.

Looking forward to future work!

[WyriHaximus]

@wpjscc We'll implement the second connection pool, the one running inside your application code. So each instance will have it's own connection pool, and you can connect that pool to an intermediate pool between your application instances and the server. Doing that with my own PostgreSQL databases. We, however, will not provide that intermediate pool.

[SimonFrings]

@wpjscc thanks for your input on this 👍

As @WyriHaximus said, we want to focus on the MySQL connection pool first which is the main feature here. The server pool from your example above would be something like a load balancer for most use cases, so I don't think this is something that we have to introduce in ReactPHP.

FYI: We're planning to release a v0.6.0 the next days and introduce the connection pool feature in a fresh v0.7.0.