Re: [firebird-support] Re: Best way to delete millions of rows
23.11.2016 9:06, Norbert Saint Georges n...@tetrasys.eu [firebird-support] wrote: > For tables of several hundred millions of recordings, if we organize > the first eight bytes in a less cahotic way, this will lighten Firebird > by making it faster? Yes, that's why sequential GUID was invented. -- 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] Re: Best way to delete millions of rows
22.11.2016 21:01, 'Leyne, Sean' s...@broadviewsoftware.com [firebird-support] wrote: >> There would be a lot of advantage, for Firebird, in using this kind of guid? > > Yes, Firebird indexes use prefix compression so the leading 14 chars of the > above values > would be stored under a single entry. For that Microsoft invented "sequential GUID" quite a lot time ago. BTW: CHAR(16) CHARACTER SET OCTETS is a better way to keep GUID in database. -- 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] Re: Best way to delete millions of rows
> > @Ann > > I will run some test and see what happens if the guids are generated > > in way where the last part varies. Like this: > > 39db9ec6-178e-77b4-5d7b-d4e969b0cd98 > > 39db9ec6-178e-e4ba-54ed-92347a131663 > > 39db9ec6-178e-c95b-c709-a42e349410df > > There would be a lot of advantage, for Firebird, in using this kind of guid? Yes, Firebird indexes use prefix compression so the leading 14 chars of the above values would be stored under a single entry. The reality is that UUID/GUID while "universally" unique do have parts which are unique to a source system, so they are common for all UUID/GUID from that system. Sean
Re: [firebird-support] Re: Best way to delete millions of rows
On Nov 22, 2016, at 6:56 AM, kragh.tho...@yahoo.com [firebird-support] wrote: > > Pagesize is 16384 and pagebuffers is 256. Servermode is superclassic. > If I drop the primary key/index everything works as expected. > Interesting. GUIDs produce really fat indexes. Firebird uses prefix compression and GUIDs vary in the first bytes so they don't compress well. Keys generated sequentially will be on different index leaf pages. Since records are deleted and garbage collected in the order they were stored, index garbage collection could easily touch a different page for each key. With only 256 buffers, index garbage collection is running pages through the cache wildly - with luck they're cached by the file system. I'm not saying that shutting down the machine for seconds or minutes at a time is acceptable, but that the place to look is in index garbage collection with a small page cache and large randomly generated key values. For what little it's worth, I'd bet that a 64bit primary key generated by a sequence would not show this problem. Nor, for that matter would a GUID that had been re-organized to put the volatile bytes at the end... Good luck, Ann
Re: [firebird-support] Re: Best way to delete millions of rows
22.11.2016 9:18, kragh.tho...@yahoo.com [firebird-support] wrote: > I know that this will trigger garbage collection. However i don't believe > this is expected > behavior, when a action from one user crashes the entire server and causes > the server to > be unresponsive for 1-5 minutes while GC is running, even users on other > databases on the > same server is affected by this. You are right. Create a ticket in tracker with your testcase, please. -- 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] Re: Best way to delete millions of rows
Hi, i agree with you i suppose that time spend by GC should be measured and if it take to much time then stop GC and try again leter. E.g. when i do delete from milions_table and then do SELECT COUNT(*) form milions_table it should not clear whole garbages and stop query until GC finished. e.g. if GC spend 1 seconds, it should stop taking this execution anymore and my SELECT COUNT(*) form milions_table should run now without GC actions. regards, Karol Bieniaszewski W dniu 2016-11-22 09:18:50 użytkownik kragh.tho...@yahoo.com [firebird-support] napisał: Hey I know that this will trigger garbage collection. However i don't believe this is expected behavior, when a action from one user crashes the entire server and causes the server to be unresponsive for 1-5 minutes while GC is running, even users on other databases on the same server is affected by this. Posted by: kragh.tho...@yahoo.com
Re: [firebird-support] Re: Best way to delete millions of rows
---In firebird-support@yahoogroups.com, wrote : >> Here you delete some records, but then count whole table. >>Add same "where" condition as in "count" query > > I think the execution of select count is for garbage collection. Sure > If count of records that does not exists is executed, will be the garbage > collection executed for the deleted records? Exactly. No need to count not deleted records :) Regards, Vlad
Re: [firebird-support] Re: Best way to delete millions of rows
> > Here you delete some records, but then count whole table. > Add same "where" condition as in "count" query > > Regards, > Vlad I think the execution of select count is for garbage collection. If count of records that does not exists is executed, will be the garbage collection executed for the deleted records? Jesus >
