Re: [Firebird-devel] Compiled statement cache
On 10/02/2022 14:44, Dmitry Yemanov wrote: > 10.02.2022 20:18, Adriano dos Santos Fernandes wrote: >> >> We should have better strategy for request cache inside the statement. >> If they are cheap to create, it would make no sense to never destroy >> them like now. > > Looking at Statement::getRequest() I see it as dirty cheap, just a > matter of few allocations. > Yes, but the impure space stores more allocations as execution is happening and this also serve as cache for later executions. So I think we have at least maintain a few ready instead of destroy all of them directly. And it seems we currently do not track later allocations stored in requests (for example vlu_string). And requests uses the statement pool. So maybe requests should have they own pool. In non-shared cache they could be sub-pool of the statement pool, but with shared cache they probably would need to be child of the attachment pool. >> Please note that requests (both DSQL and JRD) are also created from the >> statement pools. >> >> But if there are active requests, I think the statement should not even >> be considered to be taken out of cache. It's necessary in this case, so >> in reality it does not use cache space. >> >> So I think cache size (to remove least recent used) should not consider >> active (necessary) statements. > > Getting rid of long-running but rare statements may be useful. If we > speak LRU, then the cached statement should be stamped when a new child > request is created. So we may defer "uncaching" of active request until > it's freed by user (if its statement weren't re-stamped in the > meantime). It's size should not be taken into account, as you say. > Yes. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Compiled statement cache
10.02.2022 20:18, Adriano dos Santos Fernandes wrote: We should have better strategy for request cache inside the statement. If they are cheap to create, it would make no sense to never destroy them like now. Looking at Statement::getRequest() I see it as dirty cheap, just a matter of few allocations. I would calculate size of statement as sum of DSQL statement pool + JRD statement pool after it is prepared. Sounds reasonable. Please note that requests (both DSQL and JRD) are also created from the statement pools. But if there are active requests, I think the statement should not even be considered to be taken out of cache. It's necessary in this case, so in reality it does not use cache space. So I think cache size (to remove least recent used) should not consider active (necessary) statements. Getting rid of long-running but rare statements may be useful. If we speak LRU, then the cached statement should be stamped when a new child request is created. So we may defer "uncaching" of active request until it's freed by user (if its statement weren't re-stamped in the meantime). It's size should not be taken into account, as you say. Dmitry Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Compiled statement cache
On 10/02/2022 12:30, Vlad Khorsun wrote: > So, main benefit visible to end-user is to save prepare and check access > time, correct ? Yes. > Also, good written apps (that re-uses prepared statements) > will not see much changes - at least until impl of shared metadata cache. > I consider ORM written applications as good ones. And I would not consider them good if they cache statements in the client requiring extra efforts to avoid locks in DDL changes in another connections. Also good written applications uses connection pools and will benefit from the cache. It's like saying the current sharing of JRD statements from procedures/functions/triggers inside the attachment has no value because it's not shared. So I do not agree with your points. > >> Also reuse of cached statements reduces memory consumption of individual >> uncached identical statements. > > I.e. when application uses more than one instance of the same > statement in > the same connection ? Hard to imagine, but everything possible... > ORMs tends to execute parameterized queries that is always executed again and again in the course of the application execution. This is very common pattern. > The main idea is to allow to disable user cache but not system cache. > Are you going to move IRQ_REQUESTS and DYN_REQUESTS into such system cache, > or am I too optimistic ? > As I said in another answer, I consider this a different topic. >> If they are not, them when roles are different than one present in >> cached statement, a verifyAccess would need to be called on the >> statement get from the cache before it's usage is allowed. > > I ask because such restrictions could make stmt cache less useful. > > For example, we could keep list of already verified set of credentials > (user name + roles list) with cached stmt to not include it into key. > Yes, this is good. > I want to clarify - what happens when app prepares (or execute) two > identical stmts ? First instance could be taken from cache, ok. What > happens with second instance ? > First statement is prepared like before (parse, DSQL passes, GEN, JRD compile, JRD passes, DSQL messages) and then inserted into the cache. Will then create DSQL request with dsqlStatement->createRequest(). Second prepare will see the statement key in the cache and get DsqlStatement from it, check access and do dsqlStatement->createRequest(). Statement cache is basically a map>. DSqlStatement has the (Jrd)Statement inside it. So cached statements avoid all DSQL and JRD compilation passes. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Compiled statement cache
On 10/02/2022 14:13, Dimitry Sibiryakov wrote: > Dmitry Yemanov wrote 10.02.2022 18:07: >>> But as I said and Vlad also said, we can remove roles from key and >>> verify (with verification cache) after get statement from cache. This >>> would be better. >> >> Yes, this gets my vote too. > > On the other hand ACL verification used to be a known bottleneck in > the past so caching it may gain more that everything else in total. > It's why I said: *with verification cache*. ACL changes would them invalidate verification cache only. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Compiled statement cache
On 10/02/2022 13:13, Dmitry Yemanov wrote: > 08.02.2022 16:36, Adriano dos Santos Fernandes wrote: >> >> First what should be the statement key in the cache? >> >> I've peek these: >> - statement text >> - clientDialect >> - isInternalRequest >> - current client charset (as external engines may change it) > > Cannot the UTF8-translated SQL text (which is currently inside > Statement::sqlText) be the key, to avoid dependency on the charset? I > don't think that semantically different statements may have the same > UTF8 representation. However, it would make sense to have a single > cached statement for the same statement executed from different client > charsets. > Answered in another topic. >> I see three approaches: >> - 1. Timeout after its put in the cache, updated when it is get from it >> - 2. LRU based on memory consumption and max cache size >> - 3. Both 1. and 2. > > I'd start with 2. Ok. > How are you going to calculate the memory consumption? > Size of the statement pool + impureSize? > I don't think the impureSize should be taken. It's related to request executions. We should have better strategy for request cache inside the statement. If they are cheap to create, it would make no sense to never destroy them like now. I would calculate size of statement as sum of DSQL statement pool + JRD statement pool after it is prepared. Please note that requests (both DSQL and JRD) are also created from the statement pools. But if there are active requests, I think the statement should not even be considered to be taken out of cache. It's necessary in this case, so in reality it does not use cache space. So I think cache size (to remove least recent used) should not consider active (necessary) statements. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Compiled statement cache
Dmitry Yemanov wrote 10.02.2022 18:07: But as I said and Vlad also said, we can remove roles from key and verify (with verification cache) after get statement from cache. This would be better. Yes, this gets my vote too. On the other hand ACL verification used to be a known bottleneck in the past so caching it may gain more that everything else in total. -- WBR, SD. Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Compiled statement cache
On 10/02/2022 13:34, Dimitry Sibiryakov wrote:
> Dmitry Yemanov wrote 10.02.2022 17:28:
>>> Only if such translation is made right. Remember charset introducers.
>>
>> They're a problem, but it may only cause a cache miss, not a false
>> match, right?
>
> Yes, they can only cause miss but I'm not sure about literals as
> whole. Are they stored in execution tree already transliterated or with
> charset mark?
> Can the query "insert into t values ('абв')" sent from attachment with
> different charsets (win1251 and utf-8 for example) hit the cache at all?
> Should it?..
>
Please also note that transform queries to parameterized ones may be
problematic for future optimizer improvements (histograms).
Adriano
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Compiled statement cache
On 10/02/2022 13:28, Dmitry Yemanov wrote: > > In the ideal world, maybe. But this is *much* more complicated than it > seems at the first glance. > I'd even go further and say we should not slow down things to catch this. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Compiled statement cache
10.02.2022 19:54, Adriano dos Santos Fernandes wrote: I come with this requirement because verifyAccess is currently part of compilation. But as I said and Vlad also said, we can remove roles from key and verify (with verification cache) after get statement from cache. This would be better. Yes, this gets my vote too. Dmitry Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Compiled statement cache
On 10/02/2022 13:21, Dimitry Sibiryakov wrote: > Dmitry Yemanov wrote 10.02.2022 17:13: >> Cannot the UTF8-translated SQL text (which is currently inside >> Statement::sqlText) be the key, to avoid dependency on the charset? I >> don't think that semantically different statements may have the same >> UTF8 representation. However, it would make sense to have a single >> cached statement for the same statement executed from different client >> charsets. > > Only if such translation is made right. Remember charset introducers. > But apparently to transform the query before using it as a cache key > is a right idea. Two queries different only by some whitespaces, > comments or case (unless in literals) should not miss the cache. > I think the main cache objective is not to lose time processing text and caching these cases, like ad hoc different queries may have. Only basic left/right trim would be more than enough. Applications uses queries and they do not create extra random spaces in statement text everytime they prepare/execute a query. Applications also tends to use the same character set. But main problem is because messages (both DSQL and JRD) are completely different when different charsets are used. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Compiled statement cache
On 10/02/2022 13:20, Dmitry Yemanov wrote: > 10.02.2022 16:01, Adriano dos Santos Fernandes wrote: >> >> (Jrd)Statement is reused - new jrd_req are get from same statement. > > IIRC, the existing cache of internal requests preserves jrd_req's. Am I > right that after the jrd_req->Statement refactoring the cost of creation > of new jrd_req is trivial, so it does not make sense to preserve them? > Also, in the refactoring already made, the only change about Statement/jrd_req is that now Statement could be created without an initial jrd_req. Previously the internal API always created an initial jrd_req. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Compiled statement cache
On 10/02/2022 13:20, Dmitry Yemanov wrote: > 10.02.2022 16:01, Adriano dos Santos Fernandes wrote: >> >> (Jrd)Statement is reused - new jrd_req are get from same statement. > > IIRC, the existing cache of internal requests preserves jrd_req's. Am I > right that after the jrd_req->Statement refactoring the cost of creation > of new jrd_req is trivial, so it does not make sense to preserve them? > I'm talking about compiled cache at DSQL level. We currently have one or two internal DSQL request, but I don't think it's a way to go now (or directly related to this) and expand it. I have some ideas for it which would replace GDML by something better, without preprocessor but also without lose type safety, but I think this is another topic. So in this thread I'm not considering changes in internal (GDML) requests. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Compiled statement cache
On 10/02/2022 12:43, Dmitry Yemanov wrote: > 10.02.2022 15:57, Adriano dos Santos Fernandes wrote: >> >>> >>> If we need to take roles into an account - only for attachment with same >>> USER. >> >> Even without shared cache, user can change its roles with SET ROLES and >> new prepared statements should work as before even when they were >> previously cached with different roles. > > I'm not sure I get why security credentials should affect the cache at > all. From the runtime POV, all BLR/SQL operations > (current_user/current_role/rdb$*_roles) are redirected to Attachment, > AFAIK we don't store anything role-specific inside the statement tree. > From the security POV, we just need to execute verifyAccess() for the > request retrieved from the cache. > > What am I missing? > I come with this requirement because verifyAccess is currently part of compilation. But as I said and Vlad also said, we can remove roles from key and verify (with verification cache) after get statement from cache. This would be better. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Compiled statement cache
Alex Peshkoff via Firebird-devel wrote 10.02.2022 17:35: If we see solid effect from new cache - certainloy, it will be worth to make it better prepare search key. I.e. let's start from something simple and see does it make sense. That's why I suggested to ask kdv for stats: to prevent a lot of work without visible gain. -- WBR, SD. Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Compiled statement cache
On 2/10/22 19:21, Dimitry Sibiryakov wrote: Dmitry Yemanov wrote 10.02.2022 17:13: Cannot the UTF8-translated SQL text (which is currently inside Statement::sqlText) be the key, to avoid dependency on the charset? I don't think that semantically different statements may have the same UTF8 representation. However, it would make sense to have a single cached statement for the same statement executed from different client charsets. Only if such translation is made right. Remember charset introducers. But apparently to transform the query before using it as a cache key is a right idea. Two queries different only by some whitespaces, comments or case (unless in literals) should not miss the cache. I think that for the first time it\s enough to implement simple most cache. IMHO more precise detection (like different whitespaces, etc.) of same statements hardly makes big dufference in most cases. If we see solid effect from new cache - certainloy, it will be worth to make it better prepare search key. I.e. let's start from something simple and see does it make sense. Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Compiled statement cache
Dmitry Yemanov wrote 10.02.2022 17:28:
Only if such translation is made right. Remember charset introducers.
They're a problem, but it may only cause a cache miss, not a false match, right?
Yes, they can only cause miss but I'm not sure about literals as whole. Are
they stored in execution tree already transliterated or with charset mark?
Can the query "insert into t values ('абв')" sent from attachment with
different charsets (win1251 and utf-8 for example) hit the cache at all? Should
it?..
--
WBR, SD.
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Compiled statement cache
10.02.2022 19:21, Dimitry Sibiryakov wrote: Only if such translation is made right. Remember charset introducers. They're a problem, but it may only cause a cache miss, not a false match, right? But apparently to transform the query before using it as a cache key is a right idea. Two queries different only by some whitespaces, comments or case (unless in literals) should not miss the cache. In the ideal world, maybe. But this is *much* more complicated than it seems at the first glance. Dmitry Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Compiled statement cache
Dmitry Yemanov wrote 10.02.2022 17:13: Cannot the UTF8-translated SQL text (which is currently inside Statement::sqlText) be the key, to avoid dependency on the charset? I don't think that semantically different statements may have the same UTF8 representation. However, it would make sense to have a single cached statement for the same statement executed from different client charsets. Only if such translation is made right. Remember charset introducers. But apparently to transform the query before using it as a cache key is a right idea. Two queries different only by some whitespaces, comments or case (unless in literals) should not miss the cache. -- WBR, SD. Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Compiled statement cache
10.02.2022 16:01, Adriano dos Santos Fernandes wrote: (Jrd)Statement is reused - new jrd_req are get from same statement. IIRC, the existing cache of internal requests preserves jrd_req's. Am I right that after the jrd_req->Statement refactoring the cost of creation of new jrd_req is trivial, so it does not make sense to preserve them? Dmitry Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Compiled statement cache
08.02.2022 16:36, Adriano dos Santos Fernandes wrote: First what should be the statement key in the cache? I've peek these: - statement text - clientDialect - isInternalRequest - current client charset (as external engines may change it) Cannot the UTF8-translated SQL text (which is currently inside Statement::sqlText) be the key, to avoid dependency on the charset? I don't think that semantically different statements may have the same UTF8 representation. However, it would make sense to have a single cached statement for the same statement executed from different client charsets. - active roles Already wrote about that. Then there is when statements should go out of cache? I see three approaches: - 1. Timeout after its put in the cache, updated when it is get from it - 2. LRU based on memory consumption and max cache size - 3. Both 1. and 2. I'd start with 2. How are you going to calculate the memory consumption? Size of the statement pool + impureSize? Should it be enabled by default? Maybe, with a reasonable size. As Mark says, it doesn't make sense to disable new features by default ;-) Dmitry Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Compiled statement cache
Dmitry Yemanov wrote 10.02.2022 16:43: I'm not sure I get why security credentials should affect the cache at all. From the runtime POV, all BLR/SQL operations (current_user/current_role/rdb$*_roles) are redirected to Attachment, AFAIK we don't store anything role-specific inside the statement tree. From the security POV, we just need to execute verifyAccess() for the request retrieved from the cache. What am I missing? I guess Adriano is going to invalidate the cache on ACL changes. -- WBR, SD. Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Compiled statement cache
10.02.2022 17:35, Dimitry Sibiryakov wrote: Vlad Khorsun wrote 10.02.2022 16:30: More, I already implemented simple cache of prepared statements near 5 years ago and I know when it is useful :) It was not ported into Firebird because of limited usage and its simplicity. But customer which uses it, was very happy. Isn't it inside of EXECUTE STATEMENT implementation? No. Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Compiled statement cache
10.02.2022 15:57, Adriano dos Santos Fernandes wrote: If we need to take roles into an account - only for attachment with same USER. Even without shared cache, user can change its roles with SET ROLES and new prepared statements should work as before even when they were previously cached with different roles. I'm not sure I get why security credentials should affect the cache at all. From the runtime POV, all BLR/SQL operations (current_user/current_role/rdb$*_roles) are redirected to Attachment, AFAIK we don't store anything role-specific inside the statement tree. From the security POV, we just need to execute verifyAccess() for the request retrieved from the cache. What am I missing? Dmitry Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Compiled statement cache
Vlad Khorsun wrote 10.02.2022 16:30: More, I already implemented simple cache of prepared statements near 5 years ago and I know when it is useful :) It was not ported into Firebird because of limited usage and its simplicity. But customer which uses it, was very happy. Isn't it inside of EXECUTE STATEMENT implementation? -- WBR, SD. Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Compiled statement cache
10.02.2022 14:56, Adriano dos Santos Fernandes wrote: On 10/02/2022 06:44, Vlad Khorsun wrote: 08.02.2022 15:36, Adriano dos Santos Fernandes wrote: Hi! I have refactored DSQL statements/requests (continued worked of many time ago) to separate shared (statement) and specific (request) parts. It seems this ground work for compiled statement cache is complete and I have even did a (very very) initial version of a compiled statement cache working. First, please, describe - what goals\benefits of this cache ? Especially, taking into account current per-attachment metadata. In ideal world applications prepare their statement and execute them when that is going to be repeated. I would even consider a bug if they do not do that correctly in situations where an application process starts and do things in a loop. But it's not surprise that many applications are not well coded and sometimes does not do this. Competitor DBMSs has cache and works well in this case too. But more important to make bugged applications better, it's to make more well coded applications better. For example if ORM frameworks (you like it or not - they are used a lot) cache prepared statements for better performance, that will have side effects: - It cannot control server memory usage. - It locks not currently used objects preventing database changes. I have seem in test a not very complex statement having it's prepare time reduced by 50% when it's cached. So, main benefit visible to end-user is to save prepare and check access time, correct ? Also, good written apps (that re-uses prepared statements) will not see much changes - at least until impl of shared metadata cache. I'm not against of caching statements by the engine, I just want to explore both sides of coin. More, I already implemented simple cache of prepared statements near 5 years ago and I know when it is useful :) It was not ported into Firebird because of limited usage and its simplicity. But customer which uses it, was very happy. Also reuse of cached statements reduces memory consumption of individual uncached identical statements. I.e. when application uses more than one instance of the same statement in the same connection ? Hard to imagine, but everything possible... Now I think it's better to discuss its semantics. First what should be the statement key in the cache? All what affects statement compilation process, at least. I've peek these: - statement text - clientDialect - isInternalRequest Why it is important ? Do we have internal DSQL requests now ? Yes. Is it makes sense to have two caches - for internal and for user statements ? In long term I think yes, but it's not two cached, it's just a piece of the key. And not used cached internal requests would go out of cache. Anyway, it's very easy to disable it if it's considered as not important now. The main idea is to allow to disable user cache but not system cache. Are you going to move IRQ_REQUESTS and DYN_REQUESTS into such system cache, or am I too optimistic ? - current client charset (as external engines may change it) - active roles If\when shared metadata will be implemented - will it be possible to use cached compiled statement created in one attachment to use by another attachment ? It should be the easier part in the process of shared metadata changes. If yes, does it means that another attachment should use same client charset Yes. and active roles to be able to use cached compiled statement ? About roles, they may not necessary be part of the cache key. If they are not, them when roles are different than one present in cached statement, a verifyAccess would need to be called on the statement get from the cache before it's usage is allowed. I ask because such restrictions could make stmt cache less useful. For example, we could keep list of already verified set of credentials (user name + roles list) with cached stmt to not include it into key. ... And you not explained cache usage - when and how cached statement should be used. I'm not sure you want more additional information than one I replied here. I want to clarify - what happens when app prepares (or execute) two identical stmts ? First instance could be taken from cache, ok. What happens with second instance ? Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Compiled statement cache
On 2/10/22 15:57, Adriano dos Santos Fernandes wrote: On 10/02/2022 06:59, Alex Peshkoff via Firebird-devel wrote: If we need to take roles into an account - only for attachment with same USER. Even without shared cache, user can change its roles with SET ROLES and new prepared statements should work as before even when they were previously cached with different roles. Some of them should not wotk at all - provided access to some objects in them was granted to previous role only. Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Compiled statement cache
Adriano dos Santos Fernandes wrote 10.02.2022 14:08: Some times yes, some times no (it's identical statement reprepared or executed). In this case you could ask IBSurgeon for their statistics about query prepare/execute ratio beforehand to estimate possible gain from the cache. IIRC during conferences they shown really awful numbers. -- WBR, SD. Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Compiled statement cache
On 10/02/2022 10:04, Dimitry Sibiryakov wrote: > Adriano dos Santos Fernandes wrote 10.02.2022 13:56: >> But it's not surprise that many applications are not well coded and >> sometimes does not do this. Competitor DBMSs has cache and works well in >> this case too. > > Such applications used to build the queries ad-hock with data as > literals. Some times yes, some times no (it's identical statement reprepared or executed). > The competitors has a way to forcefully parameterize such > queries and without it the cache is useless because every query is > different. > Yes, this is possible and some do it. But I do not see it in the scope of a first version. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Compiled statement cache
Adriano dos Santos Fernandes wrote 10.02.2022 13:56: But it's not surprise that many applications are not well coded and sometimes does not do this. Competitor DBMSs has cache and works well in this case too. Such applications used to build the queries ad-hock with data as literals. The competitors has a way to forcefully parameterize such queries and without it the cache is useless because every query is different. -- WBR, SD. Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Compiled statement cache
On 10/02/2022 07:36, Dimitry Sibiryakov wrote: > Alex Peshkoff via Firebird-devel wrote 10.02.2022 10:59: >>> If\when shared metadata will be implemented - will it be possible to >>> use cached compiled statement created in one attachment to use by >>> another >>> attachment ? >> >> If we need to take roles into an account - only for attachment with >> same USER. > > It depends on what exactly is going to be cached. If only execution > plan is cached - it is not affected any by current user, any by role. > Every DSQL structure (messages structures, for example) is cached. (Jrd)Statement is reused - new jrd_req are get from same statement. Things related with the DSQL request (message buffers) are created for each request. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Compiled statement cache
On 10/02/2022 06:59, Alex Peshkoff via Firebird-devel wrote: > > If we need to take roles into an account - only for attachment with same > USER. > Even without shared cache, user can change its roles with SET ROLES and new prepared statements should work as before even when they were previously cached with different roles. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Compiled statement cache
On 10/02/2022 06:44, Vlad Khorsun wrote: > 08.02.2022 15:36, Adriano dos Santos Fernandes wrote: >> Hi! >> >> I have refactored DSQL statements/requests (continued worked of many >> time ago) to separate shared (statement) and specific (request) parts. >> >> It seems this ground work for compiled statement cache is complete and I >> have even did a (very very) initial version of a compiled statement >> cache working. > > First, please, describe - what goals\benefits of this cache ? Especially, > taking into account current per-attachment metadata. > In ideal world applications prepare their statement and execute them when that is going to be repeated. I would even consider a bug if they do not do that correctly in situations where an application process starts and do things in a loop. But it's not surprise that many applications are not well coded and sometimes does not do this. Competitor DBMSs has cache and works well in this case too. But more important to make bugged applications better, it's to make more well coded applications better. For example if ORM frameworks (you like it or not - they are used a lot) cache prepared statements for better performance, that will have side effects: - It cannot control server memory usage. - It locks not currently used objects preventing database changes. I have seem in test a not very complex statement having it's prepare time reduced by 50% when it's cached. Also reuse of cached statements reduces memory consumption of individual uncached identical statements. >> Now I think it's better to discuss its semantics. >> >> First what should be the statement key in the cache? > > All what affects statement compilation process, at least. > >> I've peek these: >> - statement text >> - clientDialect >> - isInternalRequest > > Why it is important ? Do we have internal DSQL requests now ? Yes. > Is it makes sense to have two caches - for internal and for user > statements ? > In long term I think yes, but it's not two cached, it's just a piece of the key. And not used cached internal requests would go out of cache. Anyway, it's very easy to disable it if it's considered as not important now. >> - current client charset (as external engines may change it) >> - active roles > > If\when shared metadata will be implemented - will it be possible to > use cached compiled statement created in one attachment to use by another > attachment ? It should be the easier part in the process of shared metadata changes. > If yes, does it means that another attachment should use same > client charset Yes. > and active roles to be able to use cached compiled > statement ? > About roles, they may not necessary be part of the cache key. If they are not, them when roles are different than one present in cached statement, a verifyAccess would need to be called on the statement get from the cache before it's usage is allowed. >> Do you see any thing more? >> >> Then there is when statements should go out of cache? >> >> I see three approaches: >> - 1. Timeout after its put in the cache, updated when it is get from it >> - 2. LRU based on memory consumption and max cache size >> - 3. Both 1. and 2. >> >> I think we can start with 1. > > For me 2 is a must, 1 good to have but less important. > Ok. >> Should it be enabled by default? >> >> I think yes. > > Yes for internal requests. Not sure about user requests. > I would better put small max. memory usage for it than disable. It would impact a lot some usage patterns - for example - reports with sub-reports where sub-report always prepare its identical parameterized query. > And you not explained cache usage - when and how cached statement > should be used. > I'm not sure you want more additional information than one I replied here. Adriano Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Compiled statement cache
Alex Peshkoff via Firebird-devel wrote 10.02.2022 10:59: If\when shared metadata will be implemented - will it be possible to use cached compiled statement created in one attachment to use by another attachment ? If we need to take roles into an account - only for attachment with same USER. It depends on what exactly is going to be cached. If only execution plan is cached - it is not affected any by current user, any by role. -- WBR, SD. Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Compiled statement cache
On 2/10/22 12:44, Vlad Khorsun wrote: I've peek these: - statement text - clientDialect - isInternalRequest Why it is important ? Do we have internal DSQL requests now ? Yes we have - but not too much. Is it makes sense to have two caches - for internal and for user statements ? - current client charset (as external engines may change it) - active roles If\when shared metadata will be implemented - will it be possible to use cached compiled statement created in one attachment to use by another attachment ? If we need to take roles into an account - only for attachment with same USER. Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
Re: [Firebird-devel] Compiled statement cache
08.02.2022 15:36, Adriano dos Santos Fernandes wrote: Hi! I have refactored DSQL statements/requests (continued worked of many time ago) to separate shared (statement) and specific (request) parts. It seems this ground work for compiled statement cache is complete and I have even did a (very very) initial version of a compiled statement cache working. First, please, describe - what goals\benefits of this cache ? Especially, taking into account current per-attachment metadata. Now I think it's better to discuss its semantics. First what should be the statement key in the cache? All what affects statement compilation process, at least. I've peek these: - statement text - clientDialect - isInternalRequest Why it is important ? Do we have internal DSQL requests now ? Is it makes sense to have two caches - for internal and for user statements ? - current client charset (as external engines may change it) - active roles If\when shared metadata will be implemented - will it be possible to use cached compiled statement created in one attachment to use by another attachment ? If yes, does it means that another attachment should use same client charset and active roles to be able to use cached compiled statement ? Do you see any thing more? Then there is when statements should go out of cache? I see three approaches: - 1. Timeout after its put in the cache, updated when it is get from it - 2. LRU based on memory consumption and max cache size - 3. Both 1. and 2. I think we can start with 1. For me 2 is a must, 1 good to have but less important. Should it be enabled by default? I think yes. Yes for internal requests. Not sure about user requests. > And it may have per database configuration of the timeout > value. Sure. Cache invalidation: Cached (and unused) statements should not lock objects preventing DDL changes. Assuming that cached statements never lock anything exclusively (there should be bug if that happens), then when someone tries to lock something exclusively it should ask caches to release the statements having that objects. Or, I think it's completely ok for an initial implementation, any try to exclusively lock an object may ask caches for complete invalidation. Enough for start, IMHO. And you not explained cache usage - when and how cached statement should be used. Regards, Vlad Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
