Re: Review of Boost.MySql

From: Dominique Devienne (ddevienne_at_[hidden])
Date: 2022-05-12 15:40:10

On Thu, May 12, 2022 at 4:25 PM Phil Endecott via Boost
<boost_at_[hidden]> wrote:
> Ruben Perez wrote (in the doc):
> >    Warning
> >    Because of how the MySQL protocol works, once you obtain a resultset,
> >   you must read it entirely (i.e. until it's complete) before engaging
> >   in any subsequent operation that implies communication with the server
> >  (e.g. issuing another query, preparing a statement, closing a
> >   statement...). Failing to do so results in undefined behavior.

> You are explicitly blaming the protocol for this restriction in
> that quote. It seems that that is not true, and that it is a
> limitation of the library.

That's not quite how I interpret it. The (basic) MySQL protocol does indeed
require the client to fully consume a resultset, and not interleave any other
operations until that's done, from what Ruben described.

The alternate sequence-diagram Ruben presents, shows writing the next
query *before* starting to read the first one. It's once you start reading
that you can't do anything else. And that mode is essentially like having
the two queries in the same packet, semicolons-separated, something
also not supported at this point, but which probably should be.

> > Pipeline modes usually specify an option on what to do when a query
> > in the pipeline fails. Here, you don't have that - subsequent queries
> > will be executed regardless of the result of previous ones.
>
> Transactions help here though. For example:
>
> Transaction t(conn);
> for (auto&& a: lots_of_things) {
>    execute_insert_stmt(a);
> }
> t.commit();

That's the pattern I also use, because my APIs throw, yielding the
implicit-rollback.
Although I'm not sure it will translate well to async code.

> I would expect pipelining to significantly improve the performance of
> that, especially if there were significant network latency.

It depends Phil. It really helps on slow, high-latency networks.
In a demo example, manually adding 50ms latency, Larenz Albe got a 4x
speedup [1].
But if you run on the LAN with sub-1ms latencies, and/or fast
Multi-Gb/sec (Cloud) networks,
it's less likely to matter that much I suspect.

[1]: https://www.cybertec-postgresql.com/en/pipeline-mode-better-performance-on-slow-network/