Re: [firebird-support] Re: Best way to delete millions of rows

[Dimitry Sibiryakov s...@ibphoenix.com [firebird-support]]

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

[Dimitry Sibiryakov s...@ibphoenix.com [firebird-support]]

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

['Leyne, Sean' s...@broadviewsoftware.com [firebird-support]]

> > @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

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

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

[liviuslivius liviusliv...@poczta.onet.pl [firebird-support]]

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

[hv...@users.sourceforge.net [firebird-support]]

---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

[Jesus Garcia jeg...@gmail.com [firebird-support]]

> 
> 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
>