close_statement makes the immediately subsequent operation slow

[anarthal]

• When running a close statement op, the immediately subsequent operation takes a big amount of time (~40ms).
• This is due to the fact that close statement doesn't send any response back. We wait until the server sends a TCP-level ACK, which happens ~40ms later.
• The official C API doesn't suffer this. From my tests, it looks like they don't wait for the entire packet to be written before writing the next one. From Asio's API, doing this reliable doesn't look viable.
• A possible solution is to send a pipelined COM_PING packet with the close statement request. Ping is very lightweight and forces the server to write an OK packet.
• Tested with the async API async_close_statement, but very much likely affects the sync API, too.
• Tested under Ubuntu 22.04 (Linux 6.2.0).

[sheldonlyr]

How can we avoid this performance issue on application level? Any suggestions?

[anarthal]

At this moment, you can use reset_connection/async_reset_connection, which doesn't suffer from this. This function will wipe any session state modifications from the connection, including prepared statements. This has the neat effect of batch-closing all statements you opened.

On the other hand, [async_]reset_connection wipes all session variables, too. This includes any time_zone or character settings you did. The character set/collation will get reset to the server's default, and not to whatever you specified in handshake_params (MySQL being MySQL). You can retrieve which charset will your server use by running SELECT @@global.character_set_client, @@global.character_set_connection, @@global.character_set_results;.

If you're using single-use connections (you're calling close, then connecting them again), you don't have to call [async_]close_statement at all.

If you can provide more information about your use case, I may be able to give you some extra ideas.

[sheldonlyr]

Hi! Thanks for so quick answer！

[sheldonlyr]

Oh, one more question😅 Does connection pool can be used in Boost 1.84? I'm really looking forward to the addition of this feature.

[anarthal]

I'm afraid it will be 1.85. I'm currently finishing docs and tests for the feature, so it can't make the times required by Boost for 1.84. I expect it to be in master in a few weeks though, and I'd be more than happy to have some early feedback.