Optimal way to use pools?

[Turbine1991]

I'm a little unsure of how the underlying pools are implemented/should be used.

• Should I be getting and releasing a connection for each query?
• Will a new connection have to be made every time I release the connection, incurring a connection delay? or will it reuse an existing released connection in the pool?

Thank you.

[rusher]

Hi @Turbine1991

If your application has to execute multiple queries, it's better to get a connection, executes the queries, then release the connection. like :

    const connection = await pool.getConnection();
    // execute multiple queries
    await connection.query("INSERT INTO ...");
    await connection.query("INSERT INTO ...");
    connection.release(); //release to pool

Will a new connection have to be made every time I release the connection, incurring a connection delay? or will it reuse an existing released connection in the pool?

When releasing a connection from pool (using conn.release()), pool stored the connection for next request. Internally, pool can release connection after some time beeing not used (option idleTimeout), but it will recreate connection if needed.

[Turbine1991]

Thank you for responding.

Am I right to assume that connection.batch(sql, values) is effectively equivalent to your example of dispatching multiple queries on a single connection?

Also, which facility may execute queries out-of-order, as opposed to sequentially? Pool connections, batch function?

Cheers.

[rusher]

Am I right to assume that connection.batch(sql, values) is effectively equivalent to your example of dispatching multiple queries on a single connection?

That depends :)

Batch has 3 different implementations, driver will choose the fastest one.

With MariaDB server 10.2+, a specific BULK command is send. from far the faster way If MariaDB server before 10.2 or MySQL server, rewrite query if possible (for INSERT) INSERT into t1 VALUES (?) => will be rewritten INSERT into t1 VALUES ('val1'), ('val2'), ... if not, just a basic loop.

[rusher]

to answer your second question, for queries out-of-order you need pool :

In the example before :

const connection = await pool.getConnection();
    // execute multiple queries
    await connection.query("INSERT INTO ...");
    await connection.query("INSERT INTO ...");
    connection.release(); //release to pool

=> connector send first "INSERT INTO ..." to server. <= server send result of first INSERT => connector send second "INSERT INTO ..." to server. <= server send result of second INSERT

Same example without on await:

const connection = await pool.getConnection();
    // execute multiple queries
    connection.query("INSERT INTO ...");
    await connection.query("INSERT INTO ...");
    connection.release(); //release to pool

query are send in order on one connection, then will be trait accordingly:

=> connector send first "INSERT INTO ..." to server. => connector send second "INSERT INTO ..." to server. <= server send result of first INSERT <= server send result of second INSERT

same using pool directly

    // execute multiple queries
await Promise.all([
  pool.query("INSERT INTO ..."),
  pool.query("INSERT INTO ...")
]);

Those to inserts will be send on different connections.

[Turbine1991]

Thank you for all this info. I really appreciate it.

One last question, what happens when it loses connection to the server? Will it halt all transactions until a timeout? Reattempt? Or just throw an error?

The reason is that I had an issue with MariaDB restarting itself once-twice a day (presumably because it consumed too much of the 1gb ram). In the npm library mysql2, it wouldn't reattempt the transaction, causing my program to fail. Is there any form of reattempt interval available?

[rusher]

It's a little bit more complex than that :/ If there is a network failure, there isn't any TCP FIN / RST that properly indicate that socket ended, so server and connector doesn't know about socket state until trying to read/write from it.

So: From driver point of view, there will be reads/new query quickly => driver will then throw an error without reattempt. There is no failover implementation for now. From server point of view: nothing is received on the connection that has a transaction, so it stay to that point until reaching @@wait_timeout (i'm not a specialist of server, there might be other option involved there).

If problem is that server fails, driver will throw an error on next query ( or during the query if there was one running). Reattempt have to be programmatically implemented.

I've missed the question. sorry for delay !