Re: [firebird-support] Statement freezes firebird
Thanks for all your replies: The major problem is, that with firebird 2.1.3 the query also completes within 3 seconds. Something seems to have changed with the optimizer, execution plan or whatever (~ sorry I dont know the terms here) related to sorting, or double sorting, or sorting within "sub"-queries. BR Matthias On Fri, Jan 24, 2020 at 3:53 PM Karol Bieniaszewski liviusliv...@poczta.onet.pl [firebird-support] < firebird-support@yahoogroups.com> wrote: > > > Hi > > > > „select first(8) ID from TBL_TEST > > where ID not in (select first(2) ID from TBL_TEST order by ID desc) > > order by ID desc” > > > > few things: > > > > Just hint but „first” is not function First(8) is same as First 8 > > > > You should avoid NOT IN queries and use EXISTS/NOT EXISTS instead. > But if you really need it then such subquery should be well indexed. > > > >> „Is there a way to force limiting the result set before sorting it?” > > > > Create descending index on ID column, than this subquery will go throught > index without sorting. > > > > It will be instant i suppose > > > > Regards, > > Karol Bieniaszewski > > > > >
ODP: [firebird-support] Statement freezes firebird
Hi „select first(8) ID from TBL_TEST where ID not in (select first(2) ID from TBL_TEST order by ID desc) order by ID desc” few things: Just hint but „first” is not function First(8) is same as First 8 You should avoid NOT IN queries and use EXISTS/NOT EXISTS instead. But if you really need it then such subquery should be well indexed. >> „Is there a way to force limiting the result set before sorting it?” Create descending index on ID column, than this subquery will go throught index without sorting. It will be instant i suppose Regards, Karol Bieniaszewski
Re: [firebird-support] Statement freezes firebird
24.01.2020 15:19, Mark Rotteveel m...@lawinegevaar.nl [firebird-support] wrote: > It sounds to me like this is should be optimized by executing the > uncorrelated sub-queries only once. Yes, but Firebird optimizer cannot do it. -- WBR, SD. ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] Statement freezes firebird
On 2020-01-24 14:39, Dimitry Sibiryakov s...@ibphoenix.com [firebird-support] wrote: > 24.01.2020 14:29, Matthias Winkler spmm...@gmail.com [firebird-support] > wrote: >> I see the very same issue with firebird 3.0.4. Any ideas? > >You made nested loop on 18000*18000 records with sorting on each > itaretion. It cannot > be fast. It sounds to me like this is should be optimized by executing the uncorrelated sub-queries only once. Mark
Re: [firebird-support] Statement freezes firebird
24.01.2020 15:01, Matthias Winkler spmm...@gmail.com [firebird-support] wrote: > Is there a way to force limiting the result set before sorting it? No. It is also meaningless because you in fact will get several pseudo-random records. Such result hardly have a practical purpose. -- WBR, SD. ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] Statement freezes firebird
@SD: Is there a way to force limiting the result set before sorting it? On Fri, Jan 24, 2020 at 2:55 PM Dimitry Sibiryakov s...@ibphoenix.com [firebird-support] wrote: > 24.01.2020 14:49, Matthias Winkler spmm...@gmail.com [firebird-support] > wrote: > > The result are just 8 entries. Sorting those 3 entries should then take > no time at all, right? > >Wrong. Sorting happens before result set limit apply. > > > Any ideas? > >Rule number one: never use "not in". > >Learn how Firebird works: http://www.ibase.ru/performance/ >Pay a special attention to http://www.ibase.ru/dataaccesspaths/ > > > -- >WBR, SD. > > > > > > > ++ > > Visit http://www.firebirdsql.org and click the Documentation item > on the main (top) menu. Try FAQ and other links from the left-side menu > there. > > Also search the knowledgebases at > http://www.ibphoenix.com/resources/documents/ > > ++ > > > Yahoo Groups Links > > > >
Re: [firebird-support] Statement freezes firebird
24.01.2020 14:49, Matthias Winkler spmm...@gmail.com [firebird-support] wrote: > The result are just 8 entries. Sorting those 3 entries should then take no > time at all, right? Wrong. Sorting happens before result set limit apply. > Any ideas? Rule number one: never use "not in". Learn how Firebird works: http://www.ibase.ru/performance/ Pay a special attention to http://www.ibase.ru/dataaccesspaths/ -- WBR, SD. ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] Statement freezes firebird
The first to lines of the query alone give a result in ~3 seconds: select first(8) ID from TBL_TEST where ID not in (select first(2) ID from TBL_TEST order by ID desc) The result are just 8 entries. Sorting those 3 entries should then take no time at all, right? But by adding the last "order by" it just takes forever... select first(8) ID from TBL_TEST where ID not in (select first(2) ID from TBL_TEST order by ID desc) order by ID desc Any ideas? On Fri, Jan 24, 2020 at 2:39 PM Dimitry Sibiryakov s...@ibphoenix.com [firebird-support] wrote: > 24.01.2020 14:29, Matthias Winkler spmm...@gmail.com [firebird-support] > wrote: > > I see the very same issue with firebird 3.0.4. Any ideas? > >You made nested loop on 18000*18000 records with sorting on each > itaretion. It cannot > be fast. > > > -- >WBR, SD. > > > > > > > ++ > > Visit http://www.firebirdsql.org and click the Documentation item > on the main (top) menu. Try FAQ and other links from the left-side menu > there. > > Also search the knowledgebases at > http://www.ibphoenix.com/resources/documents/ > > ++ > > > Yahoo Groups Links > > > >
Re: [firebird-support] Statement freezes firebird
24.01.2020 14:29, Matthias Winkler spmm...@gmail.com [firebird-support] wrote: > I see the very same issue with firebird 3.0.4. Any ideas? You made nested loop on 18000*18000 records with sorting on each itaretion. It cannot be fast. -- WBR, SD. ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] Statement freezes firebird
2020. 01. 24. 14:29 keltezéssel, Matthias Winkler spmm...@gmail.com [firebird-support] írta: > @Mark: I see the very same issue with firebird 3.0.4. Any ideas? I suggest try with 3.0.5 also. Gabor ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] Statement freezes firebird
@Mark: I see the very same issue with firebird 3.0.4. Any ideas? On Fri, Jan 24, 2020 at 1:46 PM Mark Rotteveel m...@lawinegevaar.nl [firebird-support] wrote: > > > On 2020-01-24 13:29, Matthias Winkler spmm...@gmail.com > [firebird-support] wrote: > > Hello, > > > > With Firebird 2.1.7 this statement freezes my firebird server > > completely. Firebird will > > take 100% CPU. > > > >> select first(8) ID from TBL_TEST > >> where ID not in (select first(2) ID from TBL_TEST order by ID desc) > >> order by ID desc > > > > The used table has about 18`000 entries. > > > > If I remove ONE of the order statements the query runs fine and takes > > around 3s. > > > > Can somebody explain what execution plan is made for this statement > > and > > why it might sent my server into 100% CPU? > > > > Note: The statement here is simplified from my real usecase. The > > statement itself might > > not look useful. But that is not the point. > > Firebird 2.1 has been end-of-life for more than five years. Can you > reproduce this with Firebird 3.0? > > Mark > >
Re: [firebird-support] Statement freezes firebird
On 2020-01-24 13:29, Matthias Winkler spmm...@gmail.com [firebird-support] wrote: > Hello, > > With Firebird 2.1.7 this statement freezes my firebird server > completely. Firebird will > take 100% CPU. > >> select first(8) ID from TBL_TEST >> where ID not in (select first(2) ID from TBL_TEST order by ID desc) >> order by ID desc > > The used table has about 18`000 entries. > > If I remove ONE of the order statements the query runs fine and takes > around 3s. > > Can somebody explain what execution plan is made for this statement > and > why it might sent my server into 100% CPU? > > Note: The statement here is simplified from my real usecase. The > statement itself might > not look useful. But that is not the point. Firebird 2.1 has been end-of-life for more than five years. Can you reproduce this with Firebird 3.0? Mark
[firebird-support] Statement freezes firebird
Hello, With Firebird 2.1.7 this statement freezes my firebird server completely. Firebird will take 100% CPU. select first(8) ID from TBL_TEST where ID not in (select first(2) ID from TBL_TEST order by ID desc) order by ID desc The used table has about 18`000 entries. If I remove ONE of the order statements the query runs fine and takes around 3s. Can somebody explain what execution plan is made for this statement and why it might sent my server into 100% CPU? Note: The statement here is simplified from my real usecase. The statement itself might not look useful. But that is not the point. *best regards* *Matthias *
RE: [firebird-support] Help with error message (Firebird 3)
HiThe most probability is that you store it twice in same transacion or other connection do the same. Less probabiliti is that index is corrupted some way. Regards,Karol Bieniaszewski null
RE: [firebird-support] Help with error message (Firebird 3)
Have you tried doing the install interactively using ISQL?
How are you generating the "19", i.e. is there a chance that during debug
you have inserted and then rolled back or inserted then deleted a row with
that value. If it is using a generator, then it won't be re-used, i.e. if
you ran it again it would say 20.
"violation of PRIMARY or UNIQUE KEY constraint "INTEG_50" on table "ROSTER""
Is straight out of FB, so it thinks that there is the
Jason Chapman
JAC2 Consultancy Limited
Mob: 07966211959
Please Note: This email and any attachments are confidential and are
intended for the named addressee(s) only. If you are not the intended
recipient, please notify us immediately by replying to the sender, and
delete the email from your computer. It must not be distributed without our
consent.
Unless expressly stated, the opinions in this email and any attachments are
those of the sender and not the companies.
WARNING: Computer viruses can be transmitted by email and you should be
aware that emails may be intercepted by third parties. We have taken
reasonable care to avoid the transmission of viruses and run anti-virus
software on all servers and workstations. It is your responsibility to check
this email and any attachments for the presence of viruses as neither JAC2
Consultancy Limited nor the sender accept responsibility for any viruses
transmitted by this email and/or any attachments.
JAC2 Consultancy Limited is a company registered in England with the company
number 7447785 and VAT number 150847901
From: firebird-support@yahoogroups.com
Sent: 24 January 2020 06:29
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Help with error message (Firebird 3)
Hi All,
I am getting an error during an sql insert, done with Firedac within a
delphi vcl app.
The error is:
[FireDAC][Phys][FB]violation of PRIMARY or UNIQUE KEY constraint "INTEG_50"
on table "ROSTER"
Problematic key value is ("ROSTERID" = 19)
The field ROSTERID is the primary key field.
There is no record with the key value of 19 (the highest is 11) and there
are only 4 records in the table.
The sql insert is:
INSERT INTO ROSTER
(ROSTERID, COMPANYID, DESCRIPTION, START_DATE, END_DATE, STATUS,
CREATOR, SEND_PUB_NOTIFICATION)
VALUES (
19,
1,
'Roster Starting 19-02-2019 to 25-02-2019',
'2020-01-26',
'2020-02-01',
'D',
1,
False)
Can anyone tell me what the error means and what could be the cause.
Thanks,
Paul
[Non-text portions of this message have been removed]
Re: [firebird-support] Can i change write mode "On the fly" and does it take effect right away?
24.01.2020 09:48, Kjell Rilbe kjell.ri...@marknadsinformation.se [firebird-support] wrote: > > If yes, will the changed write mode take effect immediately, or does it > require a restart of the superserver process, all connections to be > closed, or what? I.o.w. when does the changed write mode actually take > effect? At the next time the database file open because this mode is mapped to flags in CreateFile() call. -- WBR, SD. ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] Firebird begin backup and forced writes off, timestamp update delayed
24.01.2020 09:08, Kjell Rilbe kjell.ri...@marknadsinformation.se [firebird-support] wrote: > I assume this is caused by forced writes off and the fact that Windows > may delay some writes for an arbitrary amount of time. In other words, I > assume that the Firebird engine does in fact not write to the main > databse file after alter database begin backup, but writes that were > made before this may be delayed by Windows for quite a while, meaning > it's not in fact safe to start copying the main database file until > Windows has flushed all writes. > > Now, my question is how to properly handle this to achieve safe and > reliable backups? Your assumption is right, but you don't need to worry. Even if Windows doesn't update file metadata and doesn't flush system buffers to the disk, copy routines will read the data from the system buffers. That's how cache is working. -- WBR, SD. ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
[firebird-support] Can i change write mode "On the fly" and does it take effect right away?
Related to my preceding question: Can I execute "gfix -write sync" and "gfix -write async" while the database is in active use? I.e. while it has connections from a supersevrer process? If yes, will the changed write mode take effect immediately, or does it require a restart of the superserver process, all connections to be closed, or what? I.o.w. when does the changed write mode actually take effect? I don't see this documented, but please point me to it if it is. Regards, Kjell [Non-text portions of this message have been removed]
Re: [firebird-support] Firebird begin backup and forced writes off, timestamp update delayed
Den 2020-01-24 kl. 09:08, skrev Kjell Rilbe kjell.ri...@marknadsinformation.se [firebird-support]: > > Hi, > > As posted here: > https://dba.stackexchange.com/questions/258124/firebird-begin-backup-and-forced-writes-off-timestamp-update-delayed > > > Feel free to reply here, at StackExchange, or both. I will make sure the > StackExchange question gets updated to show any helpful info that > appears here. > > We have a Firebird database that's almost 200 Gbyte in sice, and for > performance we have forced writes off. Please don't debate the risks of > forced writes, we are aware of them. This is Firebird 3.0.4 on Windows > Server 2016. > > For backup, we use alter database begin backup and alter database end > backup, and copy the main database file using FastCopy. We can see that > the delta file is created right away when executing alter database begin > backup. But the main database file usually gets an updated timestamp > quite some time later, often within a few minutes, but sometimes it > takes longer. > > I assume this is caused by forced writes off and the fact that Windows > may delay some writes for an arbitrary amount of time. In other words, I > assume that the Firebird engine does in fact not write to the main > databse file after alter database begin backup, but writes that were > made before this may be delayed by Windows for quite a while, meaning > it's not in fact safe to start copying the main database file until > Windows has flushed all writes. > > Now, my question is how to properly handle this to achieve safe and > reliable backups? > > Up to now I've scheduled file copy to 3 hours after alter database begin > backup, and I also included a dummy transaction right after the alter > database begin backup. > > But are there better approaches? > > I came up with the idea to use gfix to switch forced writes on before > executing alter database begin backup (and switch it back off later). I > assume this will cause the locked state to be flushed to disk right > away, but I also assume that writes that were made before switching > forced writes on will still suffer the arbitrary delay from Windows' > write cache. Correct? Or is gfix or the Firebird engine actually able to > force flush all previous writes that are already in the Windows write > cache? > > Another idea is to use Sysinternals Sync util to flush Windows' write > cache for the entire disk. For overall system performace, this would not > be a problem, considering backup is scheduled to a low-traffic time of > day. > > We could use nbackup instead of FastCopy. Would this help? In other > words: would nbackup's reads of the main database file see the new > still-cached versions of the database pages being copied, or would it > see the outdated on-disk versions? > > In fact, I'm not sure if FastCopy actually sees the new still-cached > versions or not, but it fails when it notices that the source file's > timestamp has been changed since it started copying, so it fails anyway. > There's no apparent way to avoid this. > Come to think of it, if it's possible, shouldn't alter database begin backup (as well as nbackup -L) always flush any writes still in Windows' cache? I mean, the purpose of the operation is to make sure the main database file is frozen, but if preceding cached writes are not flushed, this frozen state is actually not guaranteed. Question: Is it possible for alter database begin backup and nbackup -L to force flush any preceding writes that are still in Windows' cache? Regards, Kjell [Non-text portions of this message have been removed]
[firebird-support] Firebird begin backup and forced writes off, timestamp update delayed
Hi, As posted here: https://dba.stackexchange.com/questions/258124/firebird-begin-backup-and-forced-writes-off-timestamp-update-delayed Feel free to reply here, at StackExchange, or both. I will make sure the StackExchange question gets updated to show any helpful info that appears here. We have a Firebird database that's almost 200 Gbyte in sice, and for performance we have forced writes off. Please don't debate the risks of forced writes, we are aware of them. This is Firebird 3.0.4 on Windows Server 2016. For backup, we use alter database begin backup and alter database end backup, and copy the main database file using FastCopy. We can see that the delta file is created right away when executing alter database begin backup. But the main database file usually gets an updated timestamp quite some time later, often within a few minutes, but sometimes it takes longer. I assume this is caused by forced writes off and the fact that Windows may delay some writes for an arbitrary amount of time. In other words, I assume that the Firebird engine does in fact not write to the main databse file after alter database begin backup, but writes that were made before this may be delayed by Windows for quite a while, meaning it's not in fact safe to start copying the main database file until Windows has flushed all writes. Now, my question is how to properly handle this to achieve safe and reliable backups? Up to now I've scheduled file copy to 3 hours after alter database begin backup, and I also included a dummy transaction right after the alter database begin backup. But are there better approaches? I came up with the idea to use gfix to switch forced writes on before executing alter database begin backup (and switch it back off later). I assume this will cause the locked state to be flushed to disk right away, but I also assume that writes that were made before switching forced writes on will still suffer the arbitrary delay from Windows' write cache. Correct? Or is gfix or the Firebird engine actually able to force flush all previous writes that are already in the Windows write cache? Another idea is to use Sysinternals Sync util to flush Windows' write cache for the entire disk. For overall system performace, this would not be a problem, considering backup is scheduled to a low-traffic time of day. We could use nbackup instead of FastCopy. Would this help? In other words: would nbackup's reads of the main database file see the new still-cached versions of the database pages being copied, or would it see the outdated on-disk versions? In fact, I'm not sure if FastCopy actually sees the new still-cached versions or not, but it fails when it notices that the source file's timestamp has been changed since it started copying, so it fails anyway. There's no apparent way to avoid this. Mvh, Kjell [Non-text portions of this message have been removed]
