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

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

[Norbert Saint Georges n...@tetrasys.eu [firebird-support]]

kragh.tho...@yahoo.com [firebird-support] a écrit :
> @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?

-- 
Norbert Saint Georges
http://tetrasys.fi







++

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] Re: Best way to delete millions of rows

[kragh.tho...@yahoo.com [firebird-support]]

@Vlad I tried with 1024, but with no luck - same thing happens.
 

 @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
 

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

[kragh.tho...@yahoo.com [firebird-support]]

@Vlad I tried with 1024, but with no luck - same thing happens.
 

 @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
 

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

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

---In firebird-support@yahoogroups.com,  wrote :
 
 > Pagesize is 16384 

  It is OK

> and pagebuffers is 256. 

  It is *terrible* ! Set it to 1024 and look what happens

> Servermode is superclassic. 

  Not matters in this test

 > If I drop the primary key/index everything works as expected. 

  Sure :)

Regards,
Vlad

Re: [firebird-support] Safe Thread - ODBC

[Tim Ward t...@telensa.com [firebird-support]]

On 22/11/2016 12:32, fabia...@itbizolutions.com.au [firebird-support] wrote:


The application does not use "begin transaction and commit", so we let 
the

database commit automatically
after a write process, we read the table from another thread inside 
the app
or from another app inside the same OS session, and the data is not 
there.
Of course after a few seconds (or milliseconds) the data is available. 
Our
concern is that we have a sequence of code that assume whatever was 
written
5 lines above is already available for reading, but it is failing 
every so

often.

When did I last see that one? - ah yes, it was using an Access database 
to communicate from one process on one machine to another on another. 
You'd receive the sync event (via another channel), poll the database, 
and find that the new data wasn't actually there yet. Turned out that 
there was a half-second delay before data got flushed to disk, as an 
optimisation in case you were just about to write something else. (Which 
could, once you'd discovered what was going on, which might take a few 
days or weeks, be turned off.)


I don't however expect to see exactly the same problem with Firebird 3 ...

--
Tim Ward

[firebird-support] Safe Thread - ODBC

[fabia...@itbizolutions.com.au [firebird-support]]

We have a multithreaded app written in Clarion 9 (win32), running on several 
win 2012 64 bits r2, connecting to Firebird 3 (latest build) via ODBC 32 
bits (latest ODBC). We are experiencing a rare propblem, that happens not 
very often. The issue is as follows:

The application does not use "begin transaction and commit", so we let the 
database commit automatically
after a write process, we read the table from another thread inside the app 
or from another app inside the same OS session, and the data is not there. 
Of course after a few seconds (or milliseconds) the data is available. Our 
concern is that we have a sequence of code that assume whatever was written 
5 lines above is already available for reading, but it is failing every so 
often. One solution we are considering is to use "explicit" begin 
transaction and commit instead of implicit, any thoughts? (in the past, when 
firebird was on version 1.5 or 2) we tried to use explicit transactions and 
the system would lock up every 20 minutes... probably deadly embrace, but we 
believe it was not related to the application itself, we attributed at the 
time to a possible bug somewhere betweeen the ODBC or the Firebird engine 
because the same app running against another DB engine would not lock up. 
Any suggestions?


Cheers,
Fabian

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] Digest Number 9396 (Temporary Tables)

[Lester Caine les...@lsces.co.uk [firebird-support]]

On 22/11/16 07:09, tiberiu_horv...@yahoo.com [firebird-support] wrote:
> I have code that uses temporary tables in at least 30 places in my
> programs. This is the way I did complicated queries. Most of these
> procedures are written 10 years ago (Interbase, Firebird 1.0). I cannot
> change everything (replace every query that uses temporary tables),
> cannot do this without proper testing. 

A lot of my code is come up on 20 years old and in the IB/FB1 days it
was a case that one had to build temporary tables in order to speed up
some report processes. Today those queries are a lot more efficient
using CTE queries ad removing the need for managing temporary tables. I
totally accept that the time taken to undertake changes may seem a waste
when things are working, but it is worth the time to pick them off over
time ...

-- 
Lester Caine - G8HFL
-
Contact - http://lsces.co.uk/wiki/?page=contact
L.S.Caine Electronic Services - http://lsces.co.uk
EnquirySolve - http://enquirysolve.com/
Model Engineers Digital Workshop - http://medw.co.uk
Rainbow Digital Media - http://rainbowdigitalmedia.co.uk

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

[kragh.tho...@yahoo.com [firebird-support]]

Pagesize is 16384 and pagebuffers is 256. Servermode is superclassic.  If I 
drop the primary key/index everything works as expected. 
 

 //Thomas

Re: [firebird-support] Digest Number 9396

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

Do you see the same behavior if the guid field is not indexed?

> On Nov 22, 2016, at 3:09 AM, tiberiu_horv...@yahoo.com [firebird-support] 
>  wrote:
> 
> 
> 
> All right, thank you all for your time !
> 
> I have code that uses temporary tables in at least 30 places in my programs. 
> This is the way I did complicated queries. Most of these procedures are 
> written 10 years ago (Interbase, Firebird 1.0). I cannot change everything 
> (replace every query that uses temporary tables), cannot do this without 
> proper testing. 
> 
> In the first phase I changed every field definiton of these temporary tables, 
> defined my domains 
> 
> Integer -> MyIntegerDomain
> 
> and changed the definition (DDL) of the temporary tables in my code. 
> 
> I sure will change the code / queries that uses those temporary tables. 
> 
> 
> 
> 
> 
> Tiberiu
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 

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

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

---In firebird-support@yahoogroups.com,  wrote :
 
 > I have finally had some more time to look into the problem I have been 
 > having, and have isolated and 
> reproduced the problem. The cause of the problem seams to be a Varchar(36) ID 
> column containing a 
> guid/uuid.

  What is database page size and page cache setting ?

Regards,
Vlad

  

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
 

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

[kragh.tho...@yahoo.com [firebird-support]]

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.  

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

[ma_go...@yahoo.com [firebird-support]]

This is not a bug, this is expected behaviour : sweep/garbage collection 
occures.