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 (alth
> > ---
> >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 re
>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
qu
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 appro
> > 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
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
ha
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.
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? Bidirection
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,
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
On 25-2-2017 19:36, Dmitry Yemanov wrote:
>> 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 met
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 featu
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
25.02.2017 13:03, Mark Rotteveel wrote:
> 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.
> 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
Independe
25.02.2017 14:03, Mark Rotteveel wrote:
> 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 perspe
>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
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.
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
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,
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.
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 fetc
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 ?
>>
>>W
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 a
25.02.2017 13:01, Dimitry Sibiryakov 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 remov
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 fetchi
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 wron
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
On 25-2-2017 09:39, Dmitry Yemanov wrote:
> 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.
I'd say, we w
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
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
-
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 implem
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
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
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
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
-
24.02.2017 2:41, Leyne, Sean wrote:
>
>>> 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
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
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
> 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
> > 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 nex
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 he
> >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/fire
22.02.2017 16:16, Michal Kubecek wrote:
> 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/
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 mas
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
>
>
> > 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?
18.02.2017 1:46, Leyne, Sean wrote:
> Vlad,
>
>
>> Documentation is there:
>>
>> https://github.com/FirebirdSQL/firebird/blob/timeouts/doc/READM
>> E.statement_timeouts
>>
>> https://github.com/FirebirdSQL/firebird/blob/timeouts/doc/READM
>> E.session_idle_timeouts
>>
>> If there will be
Vlad,
> Documentation is there:
>
> https://github.com/FirebirdSQL/firebird/blob/timeouts/doc/READM
> E.statement_timeouts
>
> https://github.com/FirebirdSQL/firebird/blob/timeouts/doc/READM
> E.session_idle_timeouts
>
> If there will be no objections i plan to merge it into master
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/ti
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 ?
>
>Bec
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
>
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 prefe
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
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 (
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 p
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
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?
-
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
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/
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
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
---
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 s
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
> m
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 u
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
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?
> I
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
18.08.2016 10:14, Dimitry Sibiryakov wrote:
> 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...
Do you intentionally mixed timeouts set by DBA and by app developer or you
real
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 "
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 sit
> > 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
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 durin
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,
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 optiona
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 h
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
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
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
>>
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
>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 offe
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
Hi All,
There are user requests to implement statement, transaction and attachment
timeouts:
http://tracker.firebirdsql.org/browse/CORE-658
http://tracker.firebirdsql.org/browse/CORE-985
http://tracker.firebirdsql.org/browse/CORE-4238
More - it was decided by TTG to implement this feat
83 matches
Mail list logo
