Quantcast

Every other execution of embedded isql slow

classic Classic list List threaded Threaded
26 messages Options
12
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Every other execution of embedded isql slow

Jiří Činčura-2
Hi,

I have a weird behavior on FB 3.0.2 32b, confirmed on 3 machines
(Win2012 R2, Win 10 CU, Win 10 AU). When I execute `isql -x
database.fdb` the first run is fast. When I execute it then again the
run is slow, like 5-10x slower. Then again fast and slow, fast, slow,
fast, slow etc.

The whole repro is here
http://www.uschovna.cz/zasilka/MAMF5RKT9XYHSAL7-GDI/ . Just run test.bat
from cmd two times.

Any ideas what's wrong?

--
Mgr. Jiří Činčura
https://www.tabsoverspaces.com/

------------------------------------------------------------------------------
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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Every other execution of embedded isql slow

Vlad Khorsun-2
09.05.2017 21:34, Jiří Činčura wrote:

> Hi,
>
> I have a weird behavior on FB 3.0.2 32b, confirmed on 3 machines
> (Win2012 R2, Win 10 CU, Win 10 AU). When I execute `isql -x
> database.fdb` the first run is fast. When I execute it then again the
> run is slow, like 5-10x slower. Then again fast and slow, fast, slow,
> fast, slow etc.
>
> The whole repro is here
> http://www.uschovna.cz/zasilka/MAMF5RKT9XYHSAL7-GDI/ . Just run test.bat
> from cmd two times.
>
> Any ideas what's wrong?

   This is not trivial story, so prepare for long reading ;)

   Start from simplest case, when Firebird server is not running and we deal
with single isql and embedded connect.

   Things is going in this order:
1. isql tried to attach to the target database
2. dispatcher ask every known provider (Remote, Engine12, Loopback) to
    attach database
3. Remote provider returns isc_unavailable, as expected
4. Embedded provider (Engine12) opens database and return success
5. isql extracts database objects and run few queries.
    To show GRANT's, isql query SEC$DB_CREATORS virtual relation.
    Engine creates internal attachment to the security database to fill
    SEC$DB_CREATORS with data.
6. dispatcher ask every known provider to attach security database
7. Remote provider returns isc_unavailable, as expected
8. Embedded provider opens security database and return success
...
9. isql query SEC$GLOBAL_AUTH_MAPPING virtual relation and engine
    creates new internal attachment to the security database to fill
    SEC$GLOBAL_AUTH_MAPPING with data.
...


   So, when there is no Firebird server running, your case works as expected -
fast on every execution. Note, every execution creates internal attachments
to the security database twice.

   When Firebird server is running, things are bit different. Note, usually
Firebird holds cached connection to the common security database.


1. isql tried to attach to the target database (using file path)
2. dispatcher ask every known provider to run attach request
3. Remote provider returns isc_unavailable, as expected
4. Embedded provider opens database and return success
5. isql query SEC$DB_CREATORS virtual relation.
    Engine creates internal attachment to the security database to fill
    SEC$DB_CREATORS with data.
6. dispatcher ask every known provider to attach security database
7. Remote provider returns isc_unavailable, as expected
8. Embedded provider returns isc_io_error, as security database file
    is already opened by the Firebird server in exclusive mode.
9. Loopback provider also fails as there login data is empty and server
    denies this attachment requiest
10. Dispatcher returns isc_io_error
... and so on

   The most interesting step there is (9) when server denies attachment
request. There is built-in ability to avoid users to attach with wrong
passwords too often - after every 4th sequiential failed login by the
same user within the 8 seconds server delays its answer for the 8 seconds.
Note, we have 2 failed logins at every run of your script. Thus every
second execution is delayed by the server by 8 seconds.

   The question i have no good answer for : should code which fills SEC$XXX
tables use any providers other than embedded (Engine12) ?

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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Every other execution of embedded isql slow

Jiří Činčura-2
Interesting. So the workaround would be to not have running server while
the embedded stuff is running? Not sure I can do that, but I'll think
about. Right now it's killing the performance of the embedded in my
case. It got from 6 minutes to 34 minutes on single app run. Not good.

>    The question i have no good answer for : should code which fills
>    SEC$XXX
> tables use any providers other than embedded (Engine12) ?

I don't think I can help you here. But if that solves the slowdown ...
you know my answer. :)

--
Mgr. Jiří Činčura
https://www.tabsoverspaces.com/

------------------------------------------------------------------------------
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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Every other execution of embedded isql slow

Vlad Khorsun-2
16.05.2017 8:03, Jiří Činčura wrote:
> Interesting. So the workaround would be to not have running server while
> the embedded stuff is running? Not sure I can do that, but I'll think
> about. Right now it's killing the performance of the embedded in my
> case. It got from 6 minutes to 34 minutes on single app run. Not good.

   Try to supply correct user credentials (explicitly or set ISC_USER\ISC_PASSWORD)
or allow Win_Sspi on server side

>>     The question i have no good answer for : should code which fills
>>     SEC$XXX
>> tables use any providers other than embedded (Engine12) ?
>
> I don't think I can help you here. But if that solves the slowdown ...
> you know my answer. :)

   ;)

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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Every other execution of embedded isql slow

Dimitry Sibiryakov-3
In reply to this post by Jiří Činčura-2
16.05.2017 7:03, Jiří Činčura wrote:
>>    The question i have no good answer for : should code which fills
>>    SEC$XXX
>> tables use any providers other than embedded (Engine12) ?
> I don't think I can help you here. But if that solves the slowdown ...
> you know my answer. :)

   Imagine a server with two engines: Engine12 and Engine13. Should it have two security
databases?
   Imagine a cluster of Firebird servers. Should they have separate security databases or
they can use single one?


--
   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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Every other execution of embedded isql slow

Vlad Khorsun-2
16.05.2017 11:27, Dimitry Sibiryakov wrote:
> 16.05.2017 7:03, Jiří Činčura wrote:
>>>     The question i have no good answer for : should code which fills
>>>     SEC$XXX
>>> tables use any providers other than embedded (Engine12) ?
>> I don't think I can help you here. But if that solves the slowdown ...
>> you know my answer. :)
>
>     Imagine a server with two engines: Engine12 and Engine13. Should it have two security
> databases?

   Yes. Two, at least.

>     Imagine a cluster of Firebird servers. Should they have separate security databases or
> they can use single one?

   Currently, we only allow embedded access to the security database. It was done by security
reasons. For a cluster there could be different security requirements and different solutions.
When\if we start to work on cluster then we should consider this question too (among many
other questions)

Vlad

PS What do you want to argue this time ?

------------------------------------------------------------------------------
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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Every other execution of embedded isql slow

Dimitry Sibiryakov-3
16.05.2017 10:41, Vlad Khorsun wrote:
> PS What do you want to argue this time ?

   Single user database is simpler to maintain. That's my whole point.


--
   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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Every other execution of embedded isql slow

Alex Peshkoff
In reply to this post by Vlad Khorsun-2
On 05/13/17 23:25, Vlad Khorsun wrote:

>     The question i have no good answer for : should code which fills SEC$XXX
> tables use any providers other than embedded (Engine12) ?

IMHO that to be changed. In FB4 I plan to learn config to remove
unneeded (i.e. non-local) providers from the list of configured
providers, that should help with having single security database per
server. What about a cluster - I think we should not try to make
decisions about it right now.



------------------------------------------------------------------------------
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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Every other execution of embedded isql slow

Vlad Khorsun-2
In reply to this post by Dimitry Sibiryakov-3
16.05.2017 11:44, Dimitry Sibiryakov wrote:
> 16.05.2017 10:41, Vlad Khorsun wrote:
>> PS What do you want to argue this time ?
>
>     Single user database is simpler to maintain. That's my whole point.

   How it is related with subject ?

Vlad

PS No database even more simlper to maintain

------------------------------------------------------------------------------
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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Every other execution of embedded isql slow

Dimitry Sibiryakov-3
In reply to this post by Alex Peshkoff
16.05.2017 10:49, Alex Peshkoff wrote:
>  In FB4 I plan to learn config to remove
> unneeded (i.e. non-local) providers from the list of configured
> providers, that should help with having single security database per
> server.

   Why not to add a separate providers list for security database? Actually, as the
database is described in databases.conf, it is enough to add "Providers" setting into its
parameters.


--
   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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Every other execution of embedded isql slow

Dimitry Sibiryakov-3
In reply to this post by Vlad Khorsun-2
16.05.2017 10:52, Vlad Khorsun wrote:
>    How it is related with subject ?

   Subject is caused by bad design of authentication module, right?


--
   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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Every other execution of embedded isql slow

Jiří Činčura-2
In reply to this post by Vlad Khorsun-2
>    Try to supply correct user credentials (explicitly or set
>    ISC_USER\ISC_PASSWORD)

Doesn't work. Doing ".\isql.exe -user SYSDBA -password masterkey -x
COMPARE_TARGET.FDB" surfaces the same behavior.

> or allow Win_Sspi on server side

Not desired. Not possible.

What's hard to comprehend to me is why embedded even doing *anything*
with the server. I would expect the embedded to be completely
independent.

--
Mgr. Jiří Činčura
https://www.tabsoverspaces.com/

------------------------------------------------------------------------------
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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Every other execution of embedded isql slow

Dimitry Sibiryakov-3
16.05.2017 11:07, Jiří Činčura wrote:
>>    Try to supply correct user credentials (explicitly or set
>>    ISC_USER\ISC_PASSWORD)
> Doesn't work. Doing ".\isql.exe -user SYSDBA -password masterkey -x
> COMPARE_TARGET.FDB" surfaces the same behavior.

   Add "Providers=Engine12" to databases.conf for security database. It would be also good
to add "ServerMode=Classic" for it, but with 3.0 it is impossible.


--
   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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Every other execution of embedded isql slow

Vlad Khorsun-2
In reply to this post by Dimitry Sibiryakov-3
16.05.2017 11:58, Dimitry Sibiryakov wrote:
> 16.05.2017 10:52, Vlad Khorsun wrote:
>>     How it is related with subject ?
>
>     Subject is caused by bad design of authentication module, right?

   I see a problem with implementation, not with design, so far.

   Do you have a list of "bad" decisions in auth design ? Do you have
something better to offer here ?

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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Every other execution of embedded isql slow

Vlad Khorsun-2
In reply to this post by Alex Peshkoff
16.05.2017 11:49, Alex Peshkoff wrote:
> On 05/13/17 23:25, Vlad Khorsun wrote:
>
>>      The question i have no good answer for : should code which fills SEC$XXX
>> tables use any providers other than embedded (Engine12) ?
>
> IMHO that to be changed. In FB4 I plan to learn config to remove
> unneeded (i.e. non-local) providers from the list of configured
> providers, that should help with having single security database per
> server.

   I prefer to introduce new protocol prefix to specify embedded connect.
I.e. something like embd://database. We even have such request at tracker, IIRC.

   Also, i offer to make providers to know its protocol prefix. It allows to
not waste time in trying to connect to the remote database with embedded
provider and opposite - do not try Loopback for explicitly specified embedded.

   Auth module should use explicitly specified embedded connection, IMO.

   Btw, it possible even now - it could pass config with "Providers = Engine12"
via DPB, AFAIU

> What about a cluster - I think we should not try to make
> decisions about it right now.

   Sure

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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Every other execution of embedded isql slow

Vlad Khorsun-2
In reply to this post by Jiří Činčura-2
16.05.2017 12:07, Jiří Činčura пишет:
>>     Try to supply correct user credentials (explicitly or set
>>     ISC_USER\ISC_PASSWORD)
>
> Doesn't work. Doing ".\isql.exe -user SYSDBA -password masterkey -x
> COMPARE_TARGET.FDB" surfaces the same behavior.

   Could you try ISC_USER\ISC_PASSWORD ? Explicit -user\-password not works
as it is not passed into internal attachment to the security database.

>> or allow Win_Sspi on server side
>
> Not desired. Not possible.

   Understand

> What's hard to comprehend to me is why embedded even doing *anything*
> with the server. I would expect the embedded to be completely
> independent.

   I have same opinion and looks like we discussing it now ;)

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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Every other execution of embedded isql slow

Alex Peshkoff
In reply to this post by Vlad Khorsun-2
On 05/16/17 12:39, Vlad Khorsun wrote:

> 16.05.2017 11:49, Alex Peshkoff wrote:
>> On 05/13/17 23:25, Vlad Khorsun wrote:
>>
>>>       The question i have no good answer for : should code which fills SEC$XXX
>>> tables use any providers other than embedded (Engine12) ?
>> IMHO that to be changed. In FB4 I plan to learn config to remove
>> unneeded (i.e. non-local) providers from the list of configured
>> providers, that should help with having single security database per
>> server.
>     I prefer to introduce new protocol prefix to specify embedded connect.
> I.e. something like embd://database. We even have such request at tracker, IIRC.

Why invent wheels? Such access is referenced typically in browsers as
file:///root/Documents/Dir-300.pdf


>
>     Also, i offer to make providers to know its protocol prefix. It allows to
> not waste time in trying to connect to the remote database with embedded
> provider and opposite - do not try Loopback for explicitly specified embedded.
>
>     Auth module should use explicitly specified embedded connection, IMO.

Yes. That's simpler than play with providers list.

>     Btw, it possible even now - it could pass config with "Providers = Engine12"
> via DPB, AFAIU

It's done in some modules, code filling sec$ tables is just missing this.



------------------------------------------------------------------------------
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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Every other execution of embedded isql slow

Dimitry Sibiryakov-3
In reply to this post by Vlad Khorsun-2
16.05.2017 11:26, Vlad Khorsun wrote:
>    Do you have a list of "bad" decisions in auth design ?

1) Mapping done by engine.
2) Tie between auth and wire encryption.

   This leads that encapsulation of authentication in auth plugins to be broken.

> Do you have something better to offer here ?

   I gave up offering of anything here years ago.

--
   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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Every other execution of embedded isql slow

Dimitry Sibiryakov-3
In reply to this post by Alex Peshkoff
16.05.2017 12:01, Alex Peshkoff wrote:
> Why invent wheels? Such access is referenced typically in browsers as
> file:///root/Documents/Dir-300.pdf

   Why to hardcode prefixes at all? Regexp-based databases.conf can take care of it in
customizable way.


--
   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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Every other execution of embedded isql slow

Jiří Činčura-2
In reply to this post by Dimitry Sibiryakov-3
>    Add "Providers=Engine12" to databases.conf for security database. It
>    would be also good
> to add "ServerMode=Classic" for it, but with 3.0 it is impossible.

The server might not be under my control. I control only the app using
the embedded Firebird.

--
Mgr. Jiří Činčura
https://www.tabsoverspaces.com/

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