On 25-2-2017 10:49, Dmitry Yemanov wrote:
> Depending on the plan, statement may take 99% of its "working" time
> inside execute() or inside fetch() or that time could be distributed
> among the API calls. Neither client nor DBA has any control on that. So
> I consider seriously wrong removing fetching time from the accounting.
>
> From the client side, a timeout can be seen from two different angles.
> It could be either statement execution time (including fetches, see
> above) - this is what we have implemented now.
But it is not the execution time (which, in my view, is the time spent
in the engine) that is constrained by the current implementation - as I
understood it (correct my if I'm wrong) -, it is the total wall-clock
time which includes time 'waiting' on the user and doing nothing in the
server.
> Or it could be the API
> call timeout, to avoid "blocking" for more than the specified time. This
> means that any higher-level accounting should be done by the application
> or connectivity library. I guess the latter approach gonna cost much
> more due to often timer resets. It also adds more work to the
> client-side developers.
>
> Now about what is "working time". If the client specifies 10 seconds, I
> really doubt it expects to see the statement timing out after 30
> seconds. From the client POV, "working time" includes all the waits,
> round-trips and so on. It's not about server resources, they are outside
> client's business. It's rather about an application reaction time. So I
> consider Vlad's position perfectly valid. And I'm surprised that Mark
> and Jiri disagree.
As a client of a database, I want to make progress with my work, which
means that processing rows is making progress, while waiting for an
execute or a fetch of rows is not making progress. Yes, I might also
have a deadline time for doing my own work, but that is not something I
want my database driver to dictate or control.
I will also quote what I said last year (2016-08-21):
"I think a single timeout that is measured over the entire execute + all
fetches is too brittle. I'd prefer if the timeout is applied to the
execute and each individual fetch."
> For server-side statement timeouts, situation is a bit different. It's
> really about long-running queries and server resources. DBA can hardly
> guess about application logic (time between fetches) or about network
> latencies. So if we speak only about engine doing some work, performing
> disk I/O or waiting for something, then Sean's point has some value. Of
> course, it has nothing to do with CPU time spent, it's more about time
> spent inside the engine for whatever reason. But it surely defines
> "long-running" statements from one side, as something being served by
> the engine.
>
> From another side, we have long-running "sleeping" (even if
> occasionally) statements that block metadata objects, occupy memory and
> freeze transaction counters. These are different resources but it does
> not mean they should be ignored. Vlad's solution accounts that, Sean's
> does not.
That can still happen, even if the statement is not being executed, but
only prepared, or when my client does a lot of other things before
finally committing the transaction. A statement level timeout does not
necessarily solve that problem.
And as a statement is only really cancelled - if I understood it
correctly - when the next fetch occurs, executing a statement,
retrieving the first row(s) and then doing nothing for a long time will
still tie up those resources.
> So we have different requirements for the same feature and they conflict
> with each other. The positive side of the implemented solution is that
> it suits both client-side and server-side usage. Sean's suggestion does
> not fit client-side usage, IMO. Also, if the timer is suspended/resumed
> too often (per every fetch call), it gonna be costly.
>
> That said, I'd vote against reworking the current design. Perhaps, we
> could additionally implement what Sean suggests, but *only* at the
> server side. But I have no idea how to mix them nicely. Having two
> independent timeouts looks ugly, complicates the engine code and is
> likely to confuse users.
The problem I foresee is that if I implement this in Jaybird, that I am
going to get confused or irate Jaybird users, who either expect the
timeout only to be applied to the execute, or only on 'time spent
waiting for the server'.
From the perspective of JDBC the statement query timeout is to be
applied to the execute, and optionally for result set method calls. The
wording in the specification is a bit vague, but the expectation is that
the timeout is applied to each method individually.
The requirements of JDBC would not entirely fit with having a 'total
time spent in engine' timeout, but it is better than a 'lifetime of
statement' timeout in my opinion.
That would then mean I'd probably have to not use this and implement a
client side timeout mechanism, which I'd rather not, and I still have to
deal with limitations explicitly set by the user (through a set
statement timeout) or managed by the DBA.
I appreciate the effort by Vlad, but looking back in this thread, when
the initial RFC was posted in August last year, the same concerns were
voiced, and they have not been addressed at all. If anything, those
concerns were dismissed with disdain ("It is allowed for you to not use
timeouts"), similar to the reactions now ("Excuse me but i consider it
as terrible wrong and will not participate in it.", and other similar
dismissive replies). Personally, it makes me feel like my opinions and
contributions are not welcome.
I understand we can have differences of opinion, and it is not always
nice to get your work critiqued/criticized, but I believe that we are
all in this to make Firebird better. Also understand that we (Sean.
Jiri, me) have a predominantly client-side view without knowledge of all
the inner-workings of the engine, which might sometimes cause
misinterpretation or blind spots in our arguments. I'd appreciate if the
core developers take that into consideration, instead of feeling
cornered or attacked (because that is the vibe I'm getting from the
responses). I'd also appreciate if you could take our view into
consideration, because I get the feeling that you have a predominantly
engine-side view of things.
Mark
--
Mark Rotteveel
------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, SlashDot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
