Re: [Firebird-devel] RFC: Timeouts

[Vlad Khorsun]

25.02.2017 21:40, Jiří Činčura wrote:
> OK, after reading the thread again and again, I think I'm starting to
> understand what was Vlad shooting for. And I think his implementation
> makes sense (so I'm fine moving it forward). This also makes sense
> reading some of Vlad's scenarios in docs (although I see it more like
> client-side only feature).
>
> *But.* With that I see another *highly* related feature. Some kind of
> resource governor. In our case (Sean, Mark, originally me) the simplest
> form of _working_ time consumed (so it's disk IO, index IO, memory, CPU
> all together - I think we don't have resources for extra detailed
> implementation). That would be only server-side configuration (_maybe_
> option for DPB), because I see it strictly as DBA safety mechanism.

   You absolutely right about resource governor. As for configuration it
better should\could be implemented as kind of user\user group quota's, i
think. But this is another story and another level of complexity.

Thanks,
Vlad


--
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

Re: [Firebird-devel] RFC: Timeouts

[Leyne, Sean]

> > ---
> >The feature could be useful for:
> > - database administrators get instrument to limit heavy queries from
> >consuming too much resources
> 
> The problem is that long running transactions does not always equate to
> "heavy queries".
> 
> (1) A NATURAL SELECT which returns 1M rows that a client fetches over 120
> minutes is completely different from (2) a SELECT that executes for 120
> minutes and returns nothing (cus the SELECT is invalid/badly constructed)
> 
> While the long transaction time of (1) does have a 'cost' when it comes to
> garbage collection, etc.
> 
> From an operational perspective, IMO, it is cases like (2) which are more 
> important to catch.

I don't know what happened but the line above was showed as quoted from Vlad 
posts, that was not the case.  

The last part of my post should have read:


>From an operational perspective, IMO, it is cases like (2) which are more 
>important to catch.

Why more?

Because (2) is entirely consuming a single CPU, likely creating a significant 
amount of disk IOs and thus is having a direct impact on the performance of 
other connections.



--
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

Re: [Firebird-devel] RFC: Timeouts

[Leyne, Sean]

>Sure. And it is stated in docs at first place:
> 
> ---
>The feature could be useful for:
> - database administrators get instrument to limit heavy queries from
>consuming too much resources

The problem is that long running transactions does not always equate to "heavy 
queries".

(1) A NATURAL SELECT which returns 1M rows that a client fetches over 120 
minutes is completely different from (2) a SELECT that executes for 120 minutes 
and returns nothing (cus the SELECT is invalid/badly constructed)

While the long transaction time of (1) does have a 'cost' when it comes to 
garbage collection, etc.

>From an operational perspective, IMO, it is cases like (2) which are more 
>important to catch.

Why more?  

Because (2) is entirely consuming a single CPU, likely creating a significant 
amount of disk IOs and thus is having a direct impact on the performance of 
other connections.


Sean

--
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

Re: [Firebird-devel] RFC: Timeouts

[Leyne, Sean]

Dmitry,

> That said, I'd vote against reworking the current design. Perhaps, we could
> additionally implement what Sean suggests, but *only* at the server side.

I expected that the "ExecutionQuota" would be something only executed at the 
server side, since that is the only place where the appropriate counter/timer 
could be maintained.

But the reality is that the new timeouts are just implemented at the server 
side, no?


> 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.

Really, to my mind the new quota feature would simply require that wherever the 
current timeout is tested, a separate test would be added to compare the 
statement "Runtime".  If any times exceeds their limit, the statement would be 
cancelled.

The only difference between the timers and the quota would be that the quota 
time accumulation would stop/start around fetches.


Sean


--
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

Re: [Firebird-devel] RFC: Timeouts

[Leyne, Sean]

> > it is the value that represent a direct CPU cost of a SQL statement.
> 
> You actually seem wanting CPU quotas. But they're not timeouts. A long-
> running statement may produce almost zero CPU load.

I have no problem with "ExecutionQuota" describing the functionality that I am 
referring to.

I do think, however, that the implementation of the quota is within reach, only 
requiring small/modest additions to the timeouts that Vlad has implemented.


Sean


--
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

Re: [Firebird-devel] RFC: Timeouts

[Dimitry Sibiryakov]

26.02.2017 12:39, Simonov Denis wrote:
> However, bidirectional cursors are still not supported on the
> client side.

   Yes. But still a new feature must be made with taking into account not only 
current 
features but future ones as well. Otherwise it could block development of them 
or will 
have to be modified to comply with them (which in turn make backward 
compatibility problems).


-- 
   WBR, SD.

--
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

Re: [Firebird-devel] RFC: Timeouts

[Simonov Denis]

Dimitry Sibiryakov  wrote Sun, 26 Feb 2017 14:16:53  
+0300:


>
>Shouldn't this timer run till call isc_free_statement(...,  
> DSQL_close) instead of fetch
> of the last record? Bidirectional cursors will still keep resources  
> after last fetch and
> current timeout won't help to free them.
>
>

May be. However, bidirectional cursors are still not supported on the  
client side. There's just announced their support for the user interface,  
but the error will be given when calling any functions.


--
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

Re: [Firebird-devel] RFC: Timeouts

[Dimitry Sibiryakov]

26.02.2017 11:55, Simonov Denis wrote:
> In terms of
> engine SQL statement must be completed to stop the timer, if it is a
> select query to include fetches of all records.

   Shouldn't this timer run till call isc_free_statement(..., DSQL_close) 
instead of fetch 
of the last record? Bidirectional cursors will still keep resources after last 
fetch and 
current timeout won't help to free them.


-- 
   WBR, SD.

--
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

Re: [Firebird-devel] RFC: Timeouts

[Simonov Denis]

I believe that the functionality is implemented correctly. In terms of  
engine SQL statement must be completed to stop the timer, if it is a  
select query to include fetches of all records. This is in good agreement  
with the new strategy for the garbage collection READ COMMITTED  
transactions, and DBA allows you to control whether the "bad" queries.

In the original version of the discussion offered a timeout on the client  
side. The fact that Mark Rotteveel offers quite possibly more correctly  
implemented on the client side, but this implementation does not help  
developers JDBC and ADO .NET drivers because they do not use fbclient.

--
The best regards,
Simonov Denis


--
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

Re: [Firebird-devel] RFC: Timeouts

[Mark Rotteveel]

On 25-2-2017 20:20, Vlad Khorsun wrote:
> 25.02.2017 13:03, Mark Rotteveel wrote:
>> 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.
>
>So, waiting for execute should be accounted in timeout but same waiting
> for fetch - should not ?

See my reply to Dmitry. For my needs (to fulfill the JDBC requirements), 
I would need a timeout on execute and - optionally - a timeout on 
individual fetches.

>> 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.
>
>Don't set timeout in this case.
>
>> 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."
>
>"I think" and "I'd prefer" - not arguments, sorry. I showed my arguments
> and they have not been disproved.

And I showed my arguments in that same post back in 2016 when I quoted 
the JDBC requirements.

>At that time (2016-08) the discussion was turned into a meaningless flame
> by DS (as he likes to do), and I saw no reason to continue to "feed the
> troll" (DS). Should note, nobody else continued that discussion, i.e. it was
> out of interest.

My replies where on the 21st of august and received no response, while 
DS latest post on that subject was on the 18th.

>If client does something not related with db before commit - this is bad
> practice and we can't make it good.

That is a very narrow view of transactions. My business actions are the 
transaction, and they need to be successfully completed before I commit.

>> 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'.
>
>If you exclude time between fetches there will be another users who will
> ask why statement still executed after specified timeout...

Maybe, but the JDBC requirements I need to fulfill are clear, and 
pointing to an authoritative source like the JDBC apidoc and saying "we 
follow these requirements" is simple. Having to explain an alternative 
logic that is not applied by other JDBC drivers is not.

>>  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.
>
>
> https://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#setQueryTimeout(int)
>
> -
> Sets the number of seconds the driver will wait for a Statement object to 
> execute to the given number of seconds. By default there
> is no limit on the amount of time allowed for a running statement to 
> complete. If the limit is exceeded, an SQLTimeoutException is
> thrown. A JDBC driver must apply this limit to the execute, executeQuery and 
> executeUpdate methods.
>
> Note: JDBC driver implementations may also apply this limit to ResultSet 
> methods (consult your driver vendor documentation for
> details).
> -
>
> i see that query timeout scope is vendor specific. I see nothing more.
>
>I agree, fetches could be included into or excluded from timeout scope.
> But i disagree that time between fetches could be treated separately.
> And JDBC is silent about it.

It says "may also apply this limit to ResultSet methods", so if the 
timeout is applied to ResultSet methods, the timeout is to be applied to 
individual method invocations.

[..]

>Mark. I don't want to argue again and again, it took to much time and
> power from me and not makes me happier. As i already said, if feature is
> considered bad\wrong\not correct, i rather abandon it. But i will not
> do what i consider as a wrong.
>
>It is possible to convince me, btw. And i still have a hope you also
> could be convinced ;)

As I also mentioned in my reply to Dmitry, I guess I just won't use this 
(or at least: not for select statements), as it doesn't fit my needs.

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

Re: [Firebird-devel] RFC: Timeouts

[Jiří Činčura]

OK, after reading the thread again and again, I think I'm starting to
understand what was Vlad shooting for. And I think his implementation
makes sense (so I'm fine moving it forward). This also makes sense
reading some of Vlad's scenarios in docs (although I see it more like
client-side only feature).

*But.* With that I see another *highly* related feature. Some kind of
resource governor. In our case (Sean, Mark, originally me) the simplest
form of _working_ time consumed (so it's disk IO, index IO, memory, CPU
all together - I think we don't have resources for extra detailed
implementation). That would be only server-side configuration (_maybe_
option for DPB), because I see it strictly as DBA safety mechanism.

-- 
Mgr. Jiří Činčura
Independent IT Specialist


--
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

Re: [Firebird-devel] RFC: Timeouts

[Vlad Khorsun]

25.02.2017 21:15, Jiří Činčura wrote:
>> But, client side already can set it own timer and cancel the statement.
>
> To add to what others said. Isn't this feature, also, about helping i.e.
> DBA to keep bad queries slowing down the server (considering (s)he has
> no control over the application's code itself)?

   Sure. And it is stated in docs at first place:

---
   The feature could be useful for:
- database administrators get instrument to limit heavy queries from consuming 
too
   much resources
- application developers could use statement timeout when creating\debugging 
complex
   queries with unknown in advance execution time
- testers could use statement timeout to detect long running queries and ensure 
finite
   run time of the test suites
- and so on
---

Regards,
Vlad


--
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

Re: [Firebird-devel] RFC: Timeouts

[Jiří Činčura]

> But, client side already can set it own timer and cancel the statement.

To add to what others said. Isn't this feature, also, about helping i.e.
DBA to keep bad queries slowing down the server (considering (s)he has
no control over the application's code itself)?

-- 
Mgr. Jiří Činčura
Independent IT Specialist

--
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

Re: [Firebird-devel] RFC: Timeouts

[Kovalenko Dmitry]

>But, client side already can set it own timer and cancel the statement.

At current time, you may forget about cancel of operation. Because it works
incorrectly.

Dmitry Kovalenko.


--
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

Re: [Firebird-devel] RFC: Timeouts

[Dmitry Yemanov]

25.02.2017 17:21, Adriano dos Santos Fernandes wrote:
>
> But, client side already can set it own timer and cancel the statement.

It was considered. However, it would mean that our implementation is 
completely useless for Java and .NET clients, they would have to 
implement timeouts from scratch.


Dmitry


--
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

Re: [Firebird-devel] RFC: Timeouts

[Adriano dos Santos Fernandes]

Em 25/02/2017 08:03, Mark Rotteveel escreveu:
> 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.
> 

It also seems server doing client jobs.

This timeout will start counting when engine receives the query, but
from client POV, it should start when client executes the statement.

That makes a whole difference in network and small timeout in milliseconds.


Adriano

--
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

Re: [Firebird-devel] RFC: Timeouts

[Adriano dos Santos Fernandes]

Em 25/02/2017 06:49, Dmitry Yemanov escreveu:
> 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.

But, client side already can set it own timer and cancel the statement.


Adriano

--
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

Re: [Firebird-devel] RFC: Timeouts

[Mark Rotteveel]

On 25-2-2017 11:15, Dmitry Yemanov wrote:
> 25.02.2017 12:37, Mark Rotteveel wrote:
>
>>> Do you/anyone know if these engines return full results sets or follow the 
>>> "page set" approach?
>>
>> As far as I know Oracle[1], PostgreSQL[2], SQL Server[3] support it. I
>> believe MySQL does as well. Don't know about other database systems, but
>> I assume most of them will support this (the SQL CLI standard also
>> defines it).
>
> AFAIK, in these databases page sets are primarily a transport/API
> feature. The important question, however, is whether the engine does
> some work during fetch() besides copying the row(s) for the next batch.
> I suspect InterBase/Firebird is the only engine that may process
> execute() as a no-op and perform all the data retrieval / joining / etc
> during fetching.

I know Oracle does, although it might depend on some optimizer decisions 
and hints (eg first_rows(n) hint). Not sure about the others.

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

Re: [Firebird-devel] RFC: Timeouts

[Mark Rotteveel]

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, 

Re: [Firebird-devel] RFC: Timeouts

[Vlad Khorsun]

25.02.2017 11:47, Mark Rotteveel wrote:
> On 25-2-2017 09:31, Vlad Khorsun wrote:
>>If think a bit deeper, it will be clear that there is no other way as 
>> send big result sets
>> in parts over the wire. Also, why don't you ask if they fully fetch 
>> resultset at the server side ?
>>
>>What you offer is very hard to use in practice as nobody able to explain 
>> why statement was
>> cancelled at this moment and it is impossible to predict moment when timeout 
>> should fire.
>>
>>Well, as we can't agree i offer to choose one of the following :
>> 1. Leave it as is
>> 2. Completely exclude fetches from timeout scope
>> 3. Remove the whole feature
>
> Would it be possible to apply the timeout to execute + first fetch?

   Yes. I'm afraid to ask - why...

> Or apply the timeout to the execute only, and separately for each fetch
> (where the fetch call would/could include a timeout value as well)?

   Here you again catched by the same trap - there is no direct relation 
between API
call fetch() and network packet op_fetch. It is unknown and not predictable. 
And it
will work absolutely differently for network and embedded cases. And it will be
different even for INET\XNET cases. Users will be "happy".

   If you need to set timeout on network wait, call it this way, ok. But don't 
mix
it with statemenet execution timeout. And be ready that only reliable way to 
handle
network timeout is to abort whole connection. While statement execution 
timeouts is
handled in a much less *harsh* way.

Regards,
Vlad

--
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

Re: [Firebird-devel] RFC: Timeouts

[Dmitry Yemanov]

25.02.2017 12:37, Mark Rotteveel wrote:

>> Do you/anyone know if these engines return full results sets or follow the 
>> "page set" approach?
>
> As far as I know Oracle[1], PostgreSQL[2], SQL Server[3] support it. I
> believe MySQL does as well. Don't know about other database systems, but
> I assume most of them will support this (the SQL CLI standard also
> defines it).

AFAIK, in these databases page sets are primarily a transport/API 
feature. The important question, however, is whether the engine does 
some work during fetch() besides copying the row(s) for the next batch. 
I suspect InterBase/Firebird is the only engine that may process 
execute() as a no-op and perform all the data retrieval / joining / etc 
during fetching.


Dmitry


--
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

Re: [Firebird-devel] RFC: Timeouts

[Dimitry Sibiryakov]

25.02.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.

   Nobody argued with that. Questionable is time between end of one fetch() 
call and start 
of the next fetch() call.


-- 
   WBR, SD.

--
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

Re: [Firebird-devel] RFC: Timeouts

[Dmitry Yemanov]

All,

Let me jump into discussion and share my own concerns.

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. 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.

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.

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.


Dmitry


--
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

Re: [Firebird-devel] RFC: Timeouts

[Mark Rotteveel]

On 25-2-2017 01:55, Leyne, Sean wrote:
>>MSSQL implements timeouts at client side:

 From a quick glance, in the ms-sql JDBC driver, the (client side) query 
timeout is applied for waiting for a response from the server for an 
execute, fetch, etc. Each action has its own the timeout timer, so each 
action has the full timeout duration.

>>Server-side statement timeouts implemented in MySQL:
>
>>PostgreSQL docs is very limited:

The pgjdbc driver has a client side timeout which only seems to cover 
the initial execute and row fetch. Depending on the configuration and 
transaction auto-commit status, that can be the whole result set, or 
just the first (fetchSize) rows. Subsequent fetches are not covered by 
this timeout.

> Do you/anyone know if these engines return full results sets or follow the 
> "page set" approach?

As far as I know Oracle[1], PostgreSQL[2], SQL Server[3] support it. I 
believe MySQL does as well. Don't know about other database systems, but 
I assume most of them will support this (the SQL CLI standard also 
defines it).

Side note: the way Firebird implements fetch is not a 'page set', but a 
row set, because a fetch contains complete rows, while the term page is 
usually reserved for a fixed amount of bytes.

[1]: https://oracle.github.io/odpi/doc/public_functions/dpiStmt.html 
(see dpiStmt_fetchRows)
[2]: 
https://www.postgresql.org/docs/9.6/static/protocol-flow.html#PROTOCOL-FLOW-EXT-QUERY
 
(search for "Once a portal exists, it can be executed using an Execute 
message.")
[3]: 
https://github.com/Microsoft/mssql-jdbc/blob/master/src/main/java/com/microsoft/sqlserver/jdbc/SQLServerResultSet.java#L1005

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

Re: [Firebird-devel] RFC: Timeouts

[Dmitry Yemanov]

25.02.2017 03:55, Leyne, Sean wrote:
>
> it is the value that represent a direct CPU cost of a SQL statement.

You actually seem wanting CPU quotas. But they're not timeouts. A 
long-running statement may produce almost zero CPU load.


Dmitry


--
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

Re: [Firebird-devel] RFC: Timeouts

[Mark Rotteveel]

On 25-2-2017 00:50, Vlad Khorsun wrote:
> 24.02.2017 20:14, Mark Rotteveel wrote:
> ...
>> It would be nice to know exactly what changes are involved in the wire
>> protocol for statement-specific timeouts without having to dive into the
>> implementation.
>
>Read, please, "Remote client implementation notes" at both 
> README.statement_timeouts
> and README.session_idle_timeouts. The only piece not described there could be 
> the way how
> Statement::setTimeout() pass user timeout value with op_execute and 
> op_execute2 packets.
> It is easy - if protocol version is at least 15, add 4-bytes with timeout 
> value to the
> contents of op_execute\op_execute2 packet:
>
> https://github.com/FirebirdSQL/firebird/commit/2c49e6fcf20b55cd87d497dae7309c593a68bd62#diff-eabce942e207c1fedfc16fe4a70fd258

Thanks

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

Re: [Firebird-devel] RFC: Timeouts

[Vlad Khorsun]

25.02.2017 2:55, Leyne, Sean wrote:
> Vlad,
>
> These connections perform only a few heavy weight SQL statements
 (taking max 3-4 of real execution time).
  > Most of the time is spent in the Firebird engine waiting for the
 next fetch, due to network latencies.

In the engine ?
>>>
>>> Yes, the engine would be **waiting** for the next fetch request from the
>> client.
>>
>>Engine never waits for the client ! This is against client-server 
>> architecture.
>
> Oh yes it does!

   Engine is passive (except of events notification). It does nothing by own 
initiative.

> I can Execute the SQL, and in 23 seconds the first "page" of rows will be 
> returned to the client.
 > At this point, the server stops and waits for the client to perform a 
 > "fetch".  So, only when I

   First, stop to mix server and engine. Second, *server* at this point doesn't 
stop, it continues
to fetch records from *engine* and caches it to be able to send it immediately 
to the client when
next op_fetch arrives.

...

>>> Based on your logic, the SELECT would be killed after 10 minutes, with only
>> 3,000 rows having been processed by the client application.
>>>
>>> Following my logic, any time waiting for a "fetch" would not count,
>>> and thus all 10,000 rows would be processed --
>>  > but the transaction/connection would be 'active' for 33 minutes.
>>
>>Following *logic* developer should set timeout based on application
>> processing time or (much better) fetch whole resultset, *commit* ASAP, and
>> then process data.
>> You describe very bad application (sorry) which holds open transaction 33
>> times longer than necessary.
>
> "Bad" or not has nothing to do with my point.

   I consider it has.

> I am saying that applications are not perfect, we need a solution that 
> provides the best
 > possible outcome for all usage patterns.

   We need a solution for *most* usage patterns and i am against support of bad 
practices.

> The fact that the transaction is open for longer that it should, has nothing 
> to do with my issue.

   It is impossible (and very wrong) to ignore such important details.

> I want to use timeout to control very bad SQL statements -- which is a 
> separate/unrelated
 > issue to the length of a transaction.

   Statement and transaction lifetime is bound, despite of you wishes.

> Perhaps we are talking about different timeout values?  Execution vs. 
> Transaction vs Connection?

   I speak about statement exection timeout. And i alredy wrote here that there 
is
no "transaction timeout" - does you read that ?

> I am more concerned with Execution timeout -- since it is the value that 
> represent a direct CPU
 > cost of a SQL statement.

   It represent a tens of things except of CPU cost. If you need to control CPU 
cost, ask for it
explicitly, but it is very, very different thing.

If you insist on changes in implementation, please, specify
 exactly what you need and where it is implemented in a such way.
>>>
>>> Add logic to stop and start the timer in locations where the server is
>>> waiting for client requests/"fetch" operations.
>>
>>No, sorry, without me. This is against my feeling of common sence and
>> against all my experience. I could agree to completely exclude fetches from
>> timeout scope, i.e. stop timer right after execute()\open(), but i'm not 
>> sure it
>> is correct way.
>
> Well, Jiri and Mark agree with me.  So, my POV is not unreasonable.

   You mixed few things in one, found no confirmation of existance of such 
feature and
doesn't listen to the arguments - yes, of course, it is very reasonable !

>>> 1- I don't know of any other engines which allow for results to be fetched 
>>> in
>> "pages".
>>  > It is the fact that the results can be fetch in "pages" with Firebird 
>> that, IMO,
>> raises  > the need for the additional level of 'accounting'.
>>
>>I see no relation of batch fetches (if you speak about it) with all said 
>> above.
>
> My point was that I don't know if those engines allow for results to be 
> return in pages
 > or whether result is returned as a single block.
>
> In which case, the execution time == the "cost" of the query since there 
> would be no
 > interaction with the client (there are no page fetches).
>
> Whereas your implementation currently just represents the elapsed time since 
> the query started --

   Yes, exactly.

> including time that the engine is doing nothing.

   And many other "interesting" times you do not want to consider.

>>MSSQL implements timeouts at client side:
>
>>Server-side statement timeouts implemented in MySQL:
>
>>PostgreSQL docs is very limited:
>
> Do you/anyone know if these engines return full results sets or follow the 
> "page set" approach?

   If think a bit deeper, it will be clear that there is no other way as send 
big result sets
in parts over the wire. Also, why don't you ask if they fully fetch resultset 
at the server side ?

   What you 

Re: [Firebird-devel] RFC: Timeouts

[Leyne, Sean]

Vlad,

> >>> These connections perform only a few heavy weight SQL statements
> >> (taking max 3-4 of real execution time).
> >>  > Most of the time is spent in the Firebird engine waiting for the
> >> next fetch, due to network latencies.
> >>
> >>In the engine ?
> >
> > Yes, the engine would be **waiting** for the next fetch request from the
> client.
> 
>Engine never waits for the client ! This is against client-server 
> architecture.

Oh yes it does!

I can Execute the SQL, and in 23 seconds the first "page" of rows will be 
returned to the client.  At this point, the server stops and waits for the 
client to perform a "fetch".  So, only when I navigate the result set to the 
point that the client app determines that more rows must be requested, nothing 
will be executing on the server process.


> > Based on your logic, the SELECT would be killed after 10 minutes, with only
> 3,000 rows having been processed by the client application.
> >
> > Following my logic, any time waiting for a "fetch" would not count,
> > and thus all 10,000 rows would be processed --
>  > but the transaction/connection would be 'active' for 33 minutes.
> 
>Following *logic* developer should set timeout based on application
> processing time or (much better) fetch whole resultset, *commit* ASAP, and
> then process data.
> You describe very bad application (sorry) which holds open transaction 33
> times longer than necessary.

"Bad" or not has nothing to do with my point.

I am saying that applications are not perfect, we need a solution that provides 
the best possible outcome for all usage patterns.

The fact that the transaction is open for longer that it should, has nothing to 
do with my issue.

I want to use timeout to control very bad SQL statements -- which is a 
separate/unrelated issue to the length of a transaction.


Perhaps we are talking about different timeout values?  Execution vs. 
Transaction vs Connection?

I am more concerned with Execution timeout -- since it is the value that 
represent a direct CPU cost of a SQL statement.


> >>If you insist on changes in implementation, please, specify
> >> exactly what you need and where it is implemented in a such way.
> >
> > Add logic to stop and start the timer in locations where the server is
> > waiting for client requests/"fetch" operations.
> 
>No, sorry, without me. This is against my feeling of common sence and
> against all my experience. I could agree to completely exclude fetches from
> timeout scope, i.e. stop timer right after execute()\open(), but i'm not sure 
> it
> is correct way.

Well, Jiri and Mark agree with me.  So, my POV is not unreasonable.


> > 1- I don't know of any other engines which allow for results to be fetched 
> > in
> "pages".
>  > It is the fact that the results can be fetch in "pages" with Firebird 
> that, IMO,
> raises  > the need for the additional level of 'accounting'.
> 
>I see no relation of batch fetches (if you speak about it) with all said 
> above.

My point was that I don't know if those engines allow for results to be return 
in pages or whether result is returned as a single block.

In which case, the execution time == the "cost" of the query since there would 
be no interaction with the client (there are no page fetches).

Whereas your implementation currently just represents the elapsed time since 
the query started -- including time that the engine is doing nothing.


>MSSQL implements timeouts at client side:

>Server-side statement timeouts implemented in MySQL:

>PostgreSQL docs is very limited:

Do you/anyone know if these engines return full results sets or follow the 
"page set" approach?


Sean


--
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

Re: [Firebird-devel] RFC: Timeouts

[Vlad Khorsun]

24.02.2017 20:14, Mark Rotteveel wrote:
...
> It would be nice to know exactly what changes are involved in the wire
> protocol for statement-specific timeouts without having to dive into the
> implementation.

   Read, please, "Remote client implementation notes" at both 
README.statement_timeouts
and README.session_idle_timeouts. The only piece not described there could be 
the way how
Statement::setTimeout() pass user timeout value with op_execute and op_execute2 
packets.
It is easy - if protocol version is at least 15, add 4-bytes with timeout value 
to the
contents of op_execute\op_execute2 packet:

https://github.com/FirebirdSQL/firebird/commit/2c49e6fcf20b55cd87d497dae7309c593a68bd62#diff-eabce942e207c1fedfc16fe4a70fd258

> BTW: What happened to protocol v14? It seems Firebird 4 jumped to
> protocol v15.

   Initial implementation of protocol 14 contains bug. When that bug was fixed 
protocol
15 was introduced.

Regards,
Vlad

--
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

Re: [Firebird-devel] RFC: Timeouts

[Vlad Khorsun]

24.02.2017 20:18, Mark Rotteveel wrote:

> I agree, I do think fetch time should be included, but not the time the
> engine is waiting for the next fetch.

   Excuse me but i consider it as terrible wrong and will not participate in it.

Regards,
Vlad


--
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

Re: [Firebird-devel] RFC: Timeouts

[Mark Rotteveel]

On 24-2-2017 07:42, Jiří Činčura wrote:
>> PS Last try to convince you: imagine application which set timeout to 10
>> sec, run some query and fetching results.
>> With current implementation it is expected that query will be cancelled
>> in a 10 sec after execution starts.
>> With you offer it is impossible to guess when it will be cancelled.
>
> Without thinking about the internals and logic, I would - intuitively -
> expect what Sean is describing. The timeout should be based on "working"
> time, not elapsed. At least that's what we would use - prevent some
> stupid queries to run. These queries do not often produce a lot of rows,
> just a few, but take a long time to process.
>
> The fetching time included seems to be skewed by network latency,
> application logic (do I fetch all at once into, i.e. array or process
> one by one), although thinking about your implementation I see what you
> aimed for.
>

I agree, I do think fetch time should be included, but not the time the 
engine is waiting for the next fetch.

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

Re: [Firebird-devel] RFC: Timeouts

[Mark Rotteveel]

On 22-2-2017 13:42, Vlad Khorsun wrote:
> 16.02.2017 12:59, Vlad Khorsun wrote:
>>
>>  Hi All,
>>
>>The code is committed at separate branch:
>>
>>  https://github.com/FirebirdSQL/firebird/tree/timeouts
>>
>> Documentation is there:
>>
>>  
>> https://github.com/FirebirdSQL/firebird/blob/timeouts/doc/README.statement_timeouts
>>
>>  
>> https://github.com/FirebirdSQL/firebird/blob/timeouts/doc/README.session_idle_timeouts
>>
>> If there will be no objections i plan to merge it into master next week.
>
>Done, testers are welcome ;)

It would be nice to know exactly what changes are involved in the wire 
protocol for statement-specific timeouts without having to dive into the 
implementation.

BTW: What happened to protocol v14? It seems Firebird 4 jumped to 
protocol v15.

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

Re: [Firebird-devel] RFC: Timeouts

[Jiří Činčura]

> PS Last try to convince you: imagine application which set timeout to 10
> sec, run some query and fetching results.
> With current implementation it is expected that query will be cancelled
> in a 10 sec after execution starts.
> With you offer it is impossible to guess when it will be cancelled.

Without thinking about the internals and logic, I would - intuitively -
expect what Sean is describing. The timeout should be based on "working"
time, not elapsed. At least that's what we would use - prevent some
stupid queries to run. These queries do not often produce a lot of rows,
just a few, but take a long time to process. 

The fetching time included seems to be skewed by network latency,
application logic (do I fetch all at once into, i.e. array or process
one by one), although thinking about your implementation I see what you
aimed for.

-- 
Mgr. Jiří Činčura
Independent IT Specialist

--
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

Re: [Firebird-devel] RFC: Timeouts

[Leyne, Sean]

> > These connections perform only a few heavy weight SQL statements
> (taking max 3-4 of real execution time).
>  > Most of the time is spent in the Firebird engine waiting for the next 
> fetch,
> due to network latencies.
> 
>In the engine ?

Yes, the engine would be **waiting** for the next fetch request from the client.

> Probably you want to say in the application\client ? Also it is
> very interesting how do you evaluate that numbers.

Consider:
- Timeout set to 10 minutes
- a SELECT, which when tested using local IBExpert,  that takes 1 minutes to 
execute and return a result set of 10,000 rows
- the real client application which takes 0.2 seconds per row to perform some 
required operation.

Based on your logic, the SELECT would be killed after 10 minutes, with only 
3,000 rows having been processed by the client application.

Following my logic, any time waiting for a "fetch" would not count, and thus 
all 10,000 rows would be processed -- but the transaction/connection would be 
'active' for 33 minutes.


>The main question is - do you need such benefit at all, and, if so, how 
> much
> efforts you are willing to spend on it ?

Yes, 'cus for me the true cost of a statement is not elapsed time but "working 
time".

Sitting waiting doesn't cost anything.


>If you insist on changes in implementation, please, specify exactly what 
> you
> need and where it is implemented in a such way.

Add logic to stop and start the timer in locations where the server is waiting 
for client requests/"fetch" operations.

Where else is it implemented?  

I really don't know anywhere, however:

1- I don't know of any other engines which allow for results to be fetched in 
"pages".  It is the fact that the results can be fetch in "pages" with Firebird 
that, IMO, raises the need for the additional level of 'accounting'.

2- my review of the MS SQL and Oracle documentation, all I found was about how 
the client could be configured to stop execution, not to have the server stop 
an operation itself.  The only server side timeout references, I found, were 
for operations where the engine was acting as a client to perform operations 
against another database/remote connections.


Sean

P.S.I am not even going to suggest that the time spent on garbage 
collection (due to classic) server should also be excluded for Classic server 
operations ;-].


--
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

Re: [Firebird-devel] RFC: Timeouts

[Vlad Khorsun]

23.02.2017 19:02, Leyne, Sean wrote:
...
> Unfortunately, I doubt that the feature as implemented has any benefits for 
> our applications.
>
> We have a number of our clients that access our databases via ODBC/JDBC 
> connections to perform data extract.
>
> These connections perform only a few heavy weight SQL statements (taking max 
> 3-4 of real execution time).
 > Most of the time is spent in the Firebird engine waiting for the next fetch, 
 > due to network latencies.

   In the engine ? Probably you want to say in the application\client ? Also it 
is very interesting how
do you evaluate that numbers.

 > Some of these extract functions/statements run for 60+ minutes.
>
> Without the ability to not have time "waiting for fetch" not included in the 
> timeout, we would need to set
 > default timeout value so high (ie. 90 minutes) that the real benefit of 
 > preventing/stopping "run away/stupid"
 > queries would be completely lost -- even with the ability to set timeout on 
 > a connection or transaction basis.

   The main question is - do you need such benefit at all, and, if so, how much 
efforts you are willing to
spend on it ?

If you still needs in it, i can suggest the following:
- don't set statement timeout on database level at all (leave it zero)
- control statement timeout in application:
   - at connection level set timeout to the value which will guard you from 
"stupid mistakes", say 10 min or so
 (it is easy to do right after connection is established)
   - set timeout to high value (90 min) at the stmt level when it is expected 
to run too long,
 (it requires support from access layer)

   If you insist on changes in implementation, please, specify exactly what you 
need and where it is implemented
in a such way.

Regards,
Vlad

PS Last try to convince you: imagine application which set timeout to 10 sec, 
run some query and fetching results.
With current implementation it is expected that query will be cancelled in a 10 
sec after execution starts.
With you offer it is impossible to guess when it will be cancelled.


--
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

Re: [Firebird-devel] RFC: Timeouts

[Leyne, Sean]

> >The code is committed at separate branch:
> >
> > https://github.com/FirebirdSQL/firebird/tree/timeouts
> >
> > Documentation is there:
> >
> >
> >
> https://github.com/FirebirdSQL/firebird/blob/timeouts/doc/README.state
> > ment_timeouts
> >
> >
> > https://github.com/FirebirdSQL/firebird/blob/timeouts/doc/README.sessi
> > on_idle_timeouts
> >
> > If there will be no objections i plan to merge it into master next week.
> 
>Done, testers are welcome ;)


Unfortunately, I doubt that the feature as implemented has any benefits for our 
applications.

We have a number of our clients that access our databases via ODBC/JDBC 
connections to perform data extract.

These connections perform only a few heavy weight SQL statements (taking max 
3-4 of real execution time).  Most of the time is spent in the Firebird engine 
waiting for the next fetch, due to network latencies.  Some of these extract 
functions/statements run for 60+ minutes.

Without the ability to not have time "waiting for fetch" not included in the 
timeout, we would need to set default timeout value so high (ie. 90 minutes) 
that the real benefit of preventing/stopping "run away/stupid" queries would be 
completely lost -- even with the ability to set timeout on a connection or 
transaction basis.


Sean


--
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

Re: [Firebird-devel] RFC: Timeouts

[Michal Kubecek]

On Wed, Feb 22, 2017 at 02:42:16PM +0200, Vlad Khorsun wrote:
> 16.02.2017 12:59, Vlad Khorsun wrote:
> >
> >The code is committed at separate branch:
> >
> > https://github.com/FirebirdSQL/firebird/tree/timeouts
> >
> > ...
> >
> > If there will be no objections i plan to merge it into master next week.
> 
>Done, testers are welcome ;)

Breaks build on openSUSE Factory for me. The patch below seems to fix it
(didn't check too thouroughly so far).

Michal Kubecek


--8<
diff --git a/builds/posix/firebird.vers b/builds/posix/firebird.vers
index 8ae5b91b8660..4e09817f754e 100644
--- a/builds/posix/firebird.vers
+++ b/builds/posix/firebird.vers
@@ -225,6 +225,7 @@ isc_dsql_prepare_m
 isc_dsql_release
 isc_dsql_set_cursor_name
 isc_dsql_sql_info
+fb_dsql_set_timeout
 
 # ESQL functions
 
--8<

--
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

Re: [Firebird-devel] RFC: Timeouts

[Vlad Khorsun]

16.02.2017 12:59, Vlad Khorsun wrote:
>
>  Hi All,
>
>The code is committed at separate branch:
>
>   https://github.com/FirebirdSQL/firebird/tree/timeouts
>
> Documentation is there:
>
>   
> https://github.com/FirebirdSQL/firebird/blob/timeouts/doc/README.statement_timeouts
>
>   
> https://github.com/FirebirdSQL/firebird/blob/timeouts/doc/README.session_idle_timeouts
>
> If there will be no objections i plan to merge it into master next week.

   Done, testers are welcome ;)

Regards,
Vlad


--
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

Re: [Firebird-devel] RFC: Timeouts

[Leyne, Sean]

> > If I read the documentation correctly, a statement which performed
> > (say) a SELECT against a table that
>  > follows a NATURAL scan, which is 'paused' awaiting the next Fetch, would
> run into the timeout, even  > though there is no "cost" to the engine of
> waiting.
> >
> > Am I correct?
> 
>Yes.

That doesn't seem quite right.

I always think of Timeout as been a measure of the amount of time the engine is 
busy/working.  Otherwise, why would "milli-second" based Timeout value have any 
use.

While waiting for a "fetch" the engine is doing "nothing", so IMO, it should 
not factor into the timeout.

{I have done some searches on how other engines handle this issue but haven't 
not found any good details/examples}


> > Separately, it would be "a good thing" if the Timeouts could be set in
> > Trigger or SP -- perhaps via
>  > special "SQL_Timeout" RDB$Context for the "User_Session" and
> "User_Transaction" namespaces.
> 
>For what goal ? 

To allow for the timeout to be customized.

Arguably, setting the "SQL_Timeout" value in the > "User_Transaction" namespace 
would allow the timeout value for the *current transaction* to be overridden in 
code, without the need for API settings.

Similarly, for the "User_Session", which would, of-course, apply to the next 
transactions of the current session/connection.


Sean


--
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

Re: [Firebird-devel] RFC: Timeouts

[Vlad Khorsun]

 Hi All,

   The code is committed at separate branch:

https://github.com/FirebirdSQL/firebird/tree/timeouts

Documentation is there:


https://github.com/FirebirdSQL/firebird/blob/timeouts/doc/README.statement_timeouts


https://github.com/FirebirdSQL/firebird/blob/timeouts/doc/README.session_idle_timeouts

If there will be no objections i plan to merge it into master next week.

Regards,
Vlad

--
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

Re: [Firebird-devel] RFC: Timeouts

[Mark Rotteveel]

On 18-8-2016 17:51, Dimitry Sibiryakov wrote:
>On the other hand it provides DBA a choice, not kill the query 
> unconditionally.
>You are not going to make an option "log, but do not kill", are you?..
>
>> PS why do you waste my time speaking about feature you not going to use ?
>
>Because you asked for comments (which is very unusual) and no real 
> consumers for this
> feature are anywhere nearby.
>

At my job, we use timeouts for all resources we use; if it takes too 
long we abort and either fail the task at hand (and maybe return a 
fallback) or retry it at a later time. If we fail too often in a short 
time window, we back off for a while.

If we consistently get timeouts, then it is time to consider either 
increasing the timeout, or investigating why a certain action takes too 
long.

We do this to achieve better throughput, ensure snappy response for 
customer facing applications (even if that means that occasionally part 
of the information is missing), **and** reducing loads on the services, 
database, etc that we use.

Mark
-- 
Mark Rotteveel

--
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] RFC: Timeouts

[Mark Rotteveel]

On 21-8-2016 09:39, Mark Rotteveel wrote:
> On 18-8-2016 00:19, Vlad Khorsun wrote:
>>> Considering API calls, network times, etc, I doubt < 1s would be good
>>> for anything here.
>>
>>What about 2500 ms ? :)
>>
>>I don't insist on milliseconds for statement timeouts. I want to hear more
>> opinions. Btw, PG uses milliseconds :)
>
> I'd prefer milliseconds, because that is the right granularity, and the
> JDBC API also uses timeouts in milliseconds.

Actually, I'm wrong, for query timeout JDBC expects seconds...

In any case, it says:

"""
Sets the number of seconds the driver will wait for a Statement object 
to execute to the given number of seconds. By default there is no limit 
on the amount of time allowed for a running statement to complete. If 
the limit is exceeded, an SQLTimeoutException is thrown. A JDBC driver 
must apply this limit to the execute, executeQuery and executeUpdate 
methods.

Note: JDBC driver implementations may also apply this limit to ResultSet 
methods (consult your driver vendor documentation for details).

Note: In the case of Statement batching, it is implementation defined as 
to whether the time-out is applied to individual SQL commands added via 
the addBatch method or to the entire batch of SQL commands invoked by 
the executeBatch method (consult your driver vendor documentation for 
details).
"""

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.

Mark
-- 
Mark Rotteveel

--
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] RFC: Timeouts

[Mark Rotteveel]

On 18-8-2016 00:19, Vlad Khorsun wrote:
>> Considering API calls, network times, etc, I doubt < 1s would be good
>> for anything here.
>
>What about 2500 ms ? :)
>
>I don't insist on milliseconds for statement timeouts. I want to hear more
> opinions. Btw, PG uses milliseconds :)

I'd prefer milliseconds, because that is the right granularity, and the 
JDBC API also uses timeouts in milliseconds.

Mark
-- 
Mark Rotteveel

--
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] RFC: Timeouts

[Dmitry Yemanov]

18.08.2016 20:12, Dimitry Sibiryakov wrote:
>
> May be there are simpler and safer solutions for these problems?

I doubt that.

> How about

How about an optimizer mistake (yes, this is possible even in MSSQL and 
Oracle) that causes a bad access path being chosen?

Don't substitute one issue with another. They all deserve solving 
independently.


Dmitry


--
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] RFC: Timeouts

[Dimitry Sibiryakov]

18.08.2016 18:59, Dmitry Yemanov wrote:
> Prevent production server from being unexpectedly overloaded *before*
> DBA can analyse such cases and take actions to avoid them. Typical cases:
>
> - possibly heavy ad-hoc queries parallel to fast well-tuned ones
> - unlucky database/application upgrade (e.g. some plans are screwed)
>
> i.e. if you don't want some guilty connection(s) to dramatically affect
> the whole system.

   May be there are simpler and safer solutions for these problems?
   How about to deny implicit Cartesian join?.. Or deny join of two natural 
streams, 
forcing them into hash or merge join?
   Btw, do merge outed joins already work? Can it prepare merged streams in 
parallel? 
Using hash-sort for that? In conjunction with upcoming tablespaces and manual 
placing them 
into independent drives, it can make Firebird lighting fast.
   How about versioned index nodes to prevent garbage collection from affecting 
performance of queries? Index-only scans could help as well. Vlad talked about 
implementing it two years ago, but obviously he gave up.

-- 
   WBR, SD.

--
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] RFC: Timeouts

[Dmitry Yemanov]

18.08.2016 19:01, Dimitry Sibiryakov wrote:
>
> Ok but then what is a _real_ problem that subj is supposed to _solve_?

Prevent production server from being unexpectedly overloaded *before* 
DBA can analyse such cases and take actions to avoid them. Typical cases:

- possibly heavy ad-hoc queries parallel to fast well-tuned ones
- unlucky database/application upgrade (e.g. some plans are screwed)

i.e. if you don't want some guilty connection(s) to dramatically affect 
the whole system.

You may also email Twitter guys and ask why they have patched MySQL to 
add the statement timeout support. They surely were solving some 
artificial problem nobody else could see ;-)


Dmitry


--
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] RFC: Timeouts

[Vlad Khorsun]

18.08.2016 18:59, Adriano dos Santos Fernandes wrote:
> On 18/08/2016 11:55, Vlad Khorsun wrote:
>>Global timeout is a last line of defense for DBA against bad apps, wrong 
>> queries,
>> developer mistakes, unlucky days (dropped some indices last week but now 
>> some queries
>> got crazy) and so on. It is *last* line, therefore it should be used with 
>> maximum care.
>>
>>
> But at the same time, applications should work, not just killed.

   Killed ? How ??? Application will receive error on fetch\execute - it will 
not be killed.

Or you speak about idle session timeout ?

> Therefore, I suggest something that makes the feature more useful and is
> not more difficult to implement.
>
> Timeouts split in alert-timeout and kill-timeout.
>
> You can define none, both or one of them.
>
> The kill-timeout is what you described.
>
> The alert-timeout just logs.
>
> You can define both, of course, the alert interval must be bellow the
> kill interval.
>
> With an alert-timeout DBA can understand how the applications and
> databases works and in future set an educated value for the kill-timeout.

   This is easily could be done using trace.

Regards,
Vlad


--
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] RFC: Timeouts

[Dimitry Sibiryakov]

18.08.2016 17:54, Dmitry Yemanov wrote:
> Logging slow queries is a completely different topic and it's already
> covered by trace/audit.

   Ok but then what is a _real_ problem that subj is supposed to _solve_?

PS: Was query cancellation stress-tested enough to be considered safe and 
robust?

-- 
   WBR, SD.

--
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] RFC: Timeouts

[Adriano dos Santos Fernandes]

On 18/08/2016 11:55, Vlad Khorsun wrote:
>Global timeout is a last line of defense for DBA against bad apps, wrong 
> queries,
> developer mistakes, unlucky days (dropped some indices last week but now some 
> queries
> got crazy) and so on. It is *last* line, therefore it should be used with 
> maximum care.
>
>
But at the same time, applications should work, not just killed.

Therefore, I suggest something that makes the feature more useful and is
not more difficult to implement.

Timeouts split in alert-timeout and kill-timeout.

You can define none, both or one of them.

The kill-timeout is what you described.

The alert-timeout just logs.

You can define both, of course, the alert interval must be bellow the
kill interval.

With an alert-timeout DBA can understand how the applications and
databases works and in future set an educated value for the kill-timeout.


Adriano


--
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] RFC: Timeouts

[Dmitry Yemanov]

18.08.2016 18:51, Dimitry Sibiryakov wrote:
>
>On the other hand it provides DBA a choice, not kill the query 
> unconditionally.
>You are not going to make an option "log, but do not kill", are you?..

Logging slow queries is a completely different topic and it's already 
covered by trace/audit.


Dmitry


--
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] RFC: Timeouts

[Dimitry Sibiryakov]

18.08.2016 17:26, Vlad Khorsun wrote:
>>What can be an advantage of this feature over cron-ed script chat check 
>> monitoring
>> > tables and send DBA mail with list of bad queries?
> - no need to create such script
> - no need to create such script for every database
> - no need to check mail
> - no need to go to workplace and kill query by hand
> - it just work and require no intervention

   On the other hand it provides DBA a choice, not kill the query 
unconditionally.
   You are not going to make an option "log, but do not kill", are you?..

> PS why do you waste my time speaking about feature you not going to use ?

   Because you asked for comments (which is very unusual) and no real consumers 
for this 
feature are anywhere nearby.

-- 
   WBR, SD.

--
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] RFC: Timeouts

[Dmitry Yemanov]

18.08.2016 13:04, liviuslivius wrote:
>
> I suppose better feature will be "timeout messaging" - something like
> TRIGGER ON STATEMENT_TIMEOUT
> TRIGGER ON TRANSACTION_TIMEOUT

Triggers are for developers. Asking DBAs to create special triggers is a 
bad idea.


Dmitry


--
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] RFC: Timeouts

[Vlad Khorsun]

18.08.2016 13:04, liviuslivius wrote:
> If i can start general discussion..
>
> do you really use such feature in real systems?
> I saw this in MSSQL environment and what was advice of DBA when someone reach 
> timeout?
> Increase timeout settings...

   IIRC, default query timeout in MSSQL is 30 sec, and it is not configured at 
database
level. More, it is pure client's feature. Therefore MSSQL devs
a) often not know it is ever exists (until it happens in application)
b) have no way except completely disable it or raise to high level in app code

i could be wrong, but this is what i know about it

> Kiling statement or transaction is not good as a general solution
> It must be customized for situations.
>
> I suppose better feature will be "timeout messaging" - something like
> TRIGGER ON STATEMENT_TIMEOUT
> TRIGGER ON TRANSACTION_TIMEOUT
>
> and inside it we have access to MON$ tables and we can cancel statement, 
> transaction if we need.
> Inside we can check e.g. individual context variables which eovercome some 
> default settings.
> We can post event and some admin client application can take some action
> i suppose you can run into more samples

   I'm very sceptical in that such trigger could have enough context info to 
make educated
decision. Also, in what execution context (attahcment\transaction) it should 
run ? If it
decided to not cancel statement - should it be fired again ? When ? I'm sure, 
there is a lot
more such questions with no easy answers...

> P.S.
> What about statements executing query to external database by EXECUTE 
> STATEMENT?

   This is implementation detail i going to speak later. I think, the most 
logical is to
apply rest of effective timeout to the external query (if target server 
supports timeouts)

Regards,
Vlad

--
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] RFC: Timeouts

[Dimitry Sibiryakov]

18.08.2016 16:55, Vlad Khorsun wrote:
>Global timeout is a last line of defense for DBA against bad apps, wrong 
> queries,
> developer mistakes, unlucky days (dropped some indices last week but now some 
> queries
> got crazy) and so on. It is *last* line, therefore it should be used with 
> maximum care.

   What can be an advantage of this feature over cron-ed script chat check 
monitoring 
tables and send DBA mail with list of bad queries?

>For example: as developer you may like to set session level timeout in 
> your dev tool
> to avoid need to kill manually query you writting and forget to specify join 
> condition
> for 25th and 26th tables in it.

   As a developer I would not ever run such query on production database. 
Besides, I have 
no idea how to choose the value of timeout.
   And yes, I would hardly be a user of this marketing feature.

-- 
   WBR, SD.

--
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] RFC: Timeouts

[Simonov Denis]

Dimitry Sibiryakov  wrote Thu, 18 Aug 2016 16:41:23  
+0300:

> 18.08.2016 15:33, Vlad Khorsun wrote:
>>Do you intentionally mixed timeouts set by DBA and by app developer  
>> or you really not
>> understand what is for what ?
>
>Yes, I don't understand.
>
>>Global timeout set by DBA usually measured in tens of minutes or in  
>> hours.
>
>Could you provide an example what such enormous timeouts can be good  
> for?
>I'm sure that almost everyone who hear about this feature first time  
> will imagine
> statement timeouts in a couple of seconds, not more.
>
>>  Are you advocate applications which could hold open cursor for a hours  
>> ?
>
>I know at least one such application.
>

Nobody forces to put a timeout, if you have applications that keep the  
cursor open for hours. Although most of the applications in Delphi RO RC  
transactions do just that.
Do not take the DBA for a fool, if he puts a specific parameter in the  
configuration, it must understand the consequences of their decisions.


-- 
Simonov Denis


--
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] RFC: Timeouts

[Vlad Khorsun]

18.08.2016 16:41, Dimitry Sibiryakov пишет:
> 18.08.2016 15:33, Vlad Khorsun wrote:
>>Do you intentionally mixed timeouts set by DBA and by app developer or 
>> you really not
>> understand what is for what ?
>
>Yes, I don't understand.
>
>>Global timeout set by DBA usually measured in tens of minutes or in hours.
>
>Could you provide an example what such enormous timeouts can be good for?
>I'm sure that almost everyone who hear about this feature first time will 
> imagine
> statement timeouts in a couple of seconds, not more.

   Global timeout is a last line of defense for DBA against bad apps, wrong 
queries,
developer mistakes, unlucky days (dropped some indices last week but now some 
queries
got crazy) and so on. It is *last* line, therefore it should be used with 
maximum care.

   Application-level timeouts is for developers, not for DBA's, and reasons to 
use it
completely different - starting from stress-testing and up to the enterprise 
policy
such as "no OLTP statements should run more than 2 sec". Therefore such 
timeouts should
be set in more individual way - at session and\or query level.

   For example: as developer you may like to set session level timeout in your 
dev tool
to avoid need to kill manually query you writting and forget to specify join 
condition
for 25th and 26th tables in it.

>>  Are you advocate applications which could hold open cursor for a hours ?
>
>I know at least one such application.

   It is allowed for you to not use timeouts with this app :)

Vlad

--
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] RFC: Timeouts

[Pavel Cisar]

Hi,

that's very neat idea. +1

best regards
Pavel Cisar
IBPhoenix

Dne 18.8.2016 v 12:04 liviuslivius napsal(a):
> If i can start general discussion..
>
> do you really use such feature in real systems?
> I saw this in MSSQL environment and what was advice of DBA when someone reach 
> timeout?
> Increase timeout settings...
>
> Kiling statement or transaction is not good as a general solution
> It must be customized for situations.
>
> I suppose better feature will be "timeout messaging" - something like
> TRIGGER ON STATEMENT_TIMEOUT
> TRIGGER ON TRANSACTION_TIMEOUT
>
> and inside it we have access to MON$ tables and we can cancel statement, 
> transaction if we need.
> Inside we can check e.g. individual context variables which eovercome some 
> default settings.
> We can post event and some admin client application can take some action
> i suppose you can run into more samples
>
>
> P.S.
> What about statements executing query to external database by EXECUTE 
> STATEMENT?
>
> regards,
> Karol Bieniaszewski
>
>
>
>
>
> --
> Firebird-Devel mailing list, web interface at 
> https://lists.sourceforge.net/lists/listinfo/firebird-devel
>

--
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] RFC: Timeouts

[Dimitry Sibiryakov]

18.08.2016 15:33, Vlad Khorsun wrote:
>Do you intentionally mixed timeouts set by DBA and by app developer or you 
> really not
> understand what is for what ?

   Yes, I don't understand.

>Global timeout set by DBA usually measured in tens of minutes or in hours.

   Could you provide an example what such enormous timeouts can be good for?
   I'm sure that almost everyone who hear about this feature first time will 
imagine 
statement timeouts in a couple of seconds, not more.

>  Are you advocate applications which could hold open cursor for a hours ?

   I know at least one such application.

-- 
   WBR, SD.

--
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] RFC: Timeouts

[Molnár Attila]

This is a GREAT idea! +1

And you might define the timeout in the CREATE/ALTER command (no need 
for config).

On 2016.08.18. 12:04, liviuslivius wrote:
> Kiling statement or transaction is not good as a general solution
> It must be customized for situations.
>
> I suppose better feature will be "timeout messaging" - something like
> TRIGGER ON STATEMENT_TIMEOUT
> TRIGGER ON TRANSACTION_TIMEOUT
>
> and inside it we have access to MON$ tables and we can cancel statement, 
> transaction if we need.


--
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] RFC: Timeouts

[liviuslivius]

If i can start general discussion..

do you really use such feature in real systems?
I saw this in MSSQL environment and what was advice of DBA when someone reach 
timeout?
Increase timeout settings... 

Kiling statement or transaction is not good as a general solution
It must be customized for situations.

I suppose better feature will be "timeout messaging" - something like 
TRIGGER ON STATEMENT_TIMEOUT
TRIGGER ON TRANSACTION_TIMEOUT

and inside it we have access to MON$ tables and we can cancel statement, 
transaction if we need.
Inside we can check e.g. individual context variables which eovercome some 
default settings.
We can post event and some admin client application can take some action
i suppose you can run into more samples


P.S.
What about statements executing query to external database by EXECUTE STATEMENT?

regards,
Karol Bieniaszewski





--
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] RFC: Timeouts

[liviuslivius]

> > Query timeout is good for queries that consume CPU resources and not 
> > finished in limited time.
> 
>CPU is important but not the only resource.
Yes, i know - time cost to take action as a whole like cpu + I/O + sync  should 
cause timeout -
statement as a whole

> 
> > But queries that feth e.g. 100 records for user and wait to fetch rest do 
> > not consume much resources
> > and user can fetch (rest or next portion) e.g. after 10 minutes.
> 
>It is too generic. Query could use 1GB for sorting and fetch very slow

yes and fetch time of "portion" should be considered by timeout
or maybe sumarize of fetches time - 
but memory consumption is different feature not releated to timeouts
query can eat 1GB but run very fast and other can eat 10MB but work very very 
long

> 
> > And that queries are not problem for DBA. Yes, such queries consume some 
> > resources but not extensivly.
> 
>See above, it is not as easy. Also, such queries not allows to commit 
> corresponding transaction
> and it could lead to blocked garbage collection, growing active part of TIP 
> and force other attachments
> to consume more resources.

yes but this is different feature like transaction timeouts - which is not so 
important from my POV
i only talking about statement timeouts.
And most systems i have seen operate without without transaction timeout 
feature 
because they check if some long running transaction is pending (query to 
MON$Transaction) and inform admins about details


regards,
Karol Bieniaszewski

--
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] RFC: Timeouts

[liviuslivius]

W dniu 2016-08-18 10:01:17 użytkownik Molnár Attila  napisał:
> Hi!
> 
> I think timeout should depend on these independent factors :
> - transaction parameters : RORC = false else true

why do you need to take different action for readonly transaction? 
If statement consume to much resources during time set in timeout why not stop 
it like in other transactions?

> - first fetch : not possible at the timeout moment = true else false

agree

> - average fetch time (start to measure after the first fetch) : very 

i do not know if average fetch time is good here
any fetch taking too much time should be treated as timeout


> high (config) = true else fales
> 
> and optionally (config)
> - plan : has NATURAL on some table (config) = true else false

disagree - plan natural can be better in many cases than index retrivial 
and have not correlation with timeouts feature

regards,
Karol Bieniaszewski

--
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] RFC: Timeouts

[Vlad Khorsun]

18.08.2016 10:37, liviuslivius wrote:
>
>
> W dniu 2016-08-18 09:26:22 użytkownik Vlad Khorsun  
> napisał:
>> 18.08.2016 10:08, liviuslivius пишет:
>>> Hi Vlad,
>>>
> I.e interactive Delphi application that fetch only really shown records 
> will get error
> when user press "Down" key,

 If user fetch one record per hour - yes, such application should be 
 better rewritten
>>>
>>> Is this query in different state that can be distinguished from "running" 
>>> queries? I see that yes.
>>> And should be possibility to exclude it from this feature or make 2 
>>> different settings for that.
>>
>>Could you show good reasons to do it ? Real use case also welcome.
...
> Query timeout is good for queries that consume CPU resources and not finished 
> in limited time.

   CPU is important but not the only resource.

> But queries that feth e.g. 100 records for user and wait to fetch rest do not 
> consume much resources
> and user can fetch (rest or next portion) e.g. after 10 minutes.

   It is too generic. Query could use 1GB for sorting and fetch very slow

> And that queries are not problem for DBA. Yes, such queries consume some 
> resources but not extensivly.

   See above, it is not as easy. Also, such queries not allows to commit 
corresponding transaction
and it could lead to blocked garbage collection, growing active part of TIP and 
force other attachments
to consume more resources.

> But feature like timeout is for queries that can utilize all server resources.


Regards,
Vlad


--
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] RFC: Timeouts

[Molnár Attila]

Hi!

I think timeout should depend on these independent factors :
- transaction parameters : RORC = false else true
- first fetch : not possible at the timeout moment = true else false
- average fetch time (start to measure after the first fetch) : very 
high (config) = true else fales

and optionally (config)
- plan : has NATURAL on some table (config) = true else false


If at least one of the factor is true then cancel the statement, else 
let the statement live.


On 2016.08.18. 9:37, liviuslivius wrote:
>
> W dniu 2016-08-18 09:26:22 użytkownik Vlad Khorsun  
> napisał:
>> 18.08.2016 10:08, liviuslivius пишет:
>>> Hi Vlad,
>>>
> I.e interactive Delphi application that fetch only really shown records 
> will get error
> when user press "Down" key,
  If user fetch one record per hour - yes, such application should be 
 better rewritten
>>> Is this query in different state that can be distinguished from "running" 
>>> queries? I see that yes.
>>> And should be possibility to exclude it from this feature or make 2 
>>> different settings for that.
>> Could you show good reasons to do it ? Real use case also welcome.
>>
>> Regards,
>> Vlad
>>
>> --
>> Firebird-Devel mailing list, web interface at 
>> https://lists.sourceforge.net/lists/listinfo/firebird-devel
>>
> Query timeout is good for queries that consume CPU resources and not finished 
> in limited time.
> But queries that feth e.g. 100 records for user and wait to fetch rest do not 
> consume much resources
> and user can fetch (rest or next portion) e.g. after 10 minutes.
> And that queries are not problem for DBA. Yes, such queries consume some 
> resources but not extensivly.
> But feature like timeout is for queries that can utilize all server resources.
>
> regards,
> Karol Bieniaszewski
>
>
>
> --
> Firebird-Devel mailing list, web interface at 
> https://lists.sourceforge.net/lists/listinfo/firebird-devel


--
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] RFC: Timeouts

[liviuslivius]

W dniu 2016-08-18 09:26:22 użytkownik Vlad Khorsun  
napisał:
> 18.08.2016 10:08, liviuslivius пишет:
> > Hi Vlad,
> >
> >>> I.e interactive Delphi application that fetch only really shown records 
> >>> will get error
> >>> when user press "Down" key,
> >>
> >> If user fetch one record per hour - yes, such application should be 
> >> better rewritten
> >
> > Is this query in different state that can be distinguished from "running" 
> > queries? I see that yes.
> > And should be possibility to exclude it from this feature or make 2 
> > different settings for that.
> 
>Could you show good reasons to do it ? Real use case also welcome.
> 
> Regards,
> Vlad
> 
> --
> Firebird-Devel mailing list, web interface at 
> https://lists.sourceforge.net/lists/listinfo/firebird-devel
> 

Query timeout is good for queries that consume CPU resources and not finished 
in limited time.
But queries that feth e.g. 100 records for user and wait to fetch rest do not 
consume much resources 
and user can fetch (rest or next portion) e.g. after 10 minutes. 
And that queries are not problem for DBA. Yes, such queries consume some 
resources but not extensivly.
But feature like timeout is for queries that can utilize all server resources.

regards,
Karol Bieniaszewski



--
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] RFC: Timeouts

[Vlad Khorsun]

18.08.2016 10:08, liviuslivius пишет:
> Hi Vlad,
>
>>> I.e interactive Delphi application that fetch only really shown records 
>>> will get error
>>> when user press "Down" key,
>>
>> If user fetch one record per hour - yes, such application should be 
>> better rewritten
>
> Is this query in different state that can be distinguished from "running" 
> queries? I see that yes.
> And should be possibility to exclude it from this feature or make 2 different 
> settings for that.

   Could you show good reasons to do it ? Real use case also welcome.

Regards,
Vlad

--
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] RFC: Timeouts

[Dimitry Sibiryakov]

18.08.2016 8:47, Vlad Khorsun wrote:
>What about 2500 ms ? :)
> If user fetch one record per hour - yes,

   2500 ms is much less that an hour, you know...

> such application should be better rewritten

   It will require to implement background fetch. Much out of skills for most 
Delphi 
programmers.

>DBA should decide what is more important for DB. Currently, DBA have no 
> way to control.

   IMHO, in proposed form it won't have more control, because there will be no 
sane range 
of timeouts values that could be useful to kill wrong DMLs but let selects to 
live.

   I would suggest to apply the timeout to each single execute() and fetch() 
call. This 
way selects will have the same quotes as DML.

-- 
   WBR, SD.

--
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] RFC: Timeouts

[Vlad Khorsun]

18.08.2016 0:58, Adriano dos Santos Fernandes wrote:
> Em 17/08/2016 15:44, Vlad Khorsun escreveu:
>
>>
>> a) Statement execution timeout
>> - timeout is set in milliseconds, there is no guarantee of exact precision
>>(especially under high load). The only promise is that timeout will not
>>happen earlier than specified
>
> Why milliseconds? If you're going to use seconds for session, seconds
> here would be less confusing.

   Initially i also thought about seconds. And, yes, i don't like to have 
different
measures for similar things. But, technically, it is possible and it could be
useful for testing purposes. Note, it have no sense for session idle timeout to
be specified in ms. So i decided to discuss this point and it is good that you
raise this question :)

> Considering API calls, network times, etc, I doubt < 1s would be good
> for anything here.

   What about 2500 ms ? :)

   I don't insist on milliseconds for statement timeouts. I want to hear more
opinions. Btw, PG uses milliseconds :)

>> - if timeout happens at moment with no client activity (for example between 
>> two
>>fetch() calls) it will not take immediate effect, i.e. cursor remains 
>> open and
>>resources is not freed
>
> What do you mean with "will not take immediate effect"?
>
> If it waits for the next fetch, it will not be effective for its purpose
> - track bad written applications.

   Probably you right. I just described how statement cancellation works 
currently.

Regards,
Vlad


--
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] RFC: Timeouts

[Adriano dos Santos Fernandes]

Em 17/08/2016 15:44, Vlad Khorsun escreveu:

> 
> a) Statement execution timeout
> - timeout is set in milliseconds, there is no guarantee of exact precision
>(especially under high load). The only promise is that timeout will not
>happen earlier than specified

Why milliseconds? If you're going to use seconds for session, seconds
here would be less confusing.

Considering API calls, network times, etc, I doubt < 1s would be good
for anything here.

> - if timeout happens at moment with no client activity (for example between 
> two
>fetch() calls) it will not take immediate effect, i.e. cursor remains open 
> and
>resources is not freed

What do you mean with "will not take immediate effect"?

If it waits for the next fetch, it will not be effective for its purpose
- track bad written applications.


Adriano

--
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] RFC: Timeouts

[Leyne, Sean]

>Other mainstream DBMS already have support for active statement and
> idle session timeouts. I know no example of transaction timeout, though. I
> think, transactions timeouts could bring more troubles than goods and in
> many cases could be replaced by idle session timeouts. Therefore i offer to
> omit transaction timeouts from further discussion (but i not insist).

I agree that transaction timeouts could be problematic and should be omitted 
from the initial timeout implementation.


Sean


--
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel

Re: [Firebird-devel] RFC: Timeouts

[Dimitry Sibiryakov]

17.08.2016 20:44, Vlad Khorsun wrote:
>- can't be greater than (non-zero) value at config

   I.e there is no way for DBA to make exceptions for some queries that are 
known to be 
good, but long, right?

> - timeout tracked since the moment of the execution start, i.e. timer is not
>reset by the fetch() calls
> - timeout timer is stopped when statement execution is finished - at the end 
> of
>exec() method or when the fetch() returns last record

   I.e interactive Delphi application that fetch only really shown records will 
get error 
when user press "Down" key, or DBA won't be able to use this feature on 
database which 
they are working with, at all, right?

-- 
   WBR, SD.

--
Firebird-Devel mailing list, web interface at 
https://lists.sourceforge.net/lists/listinfo/firebird-devel