AW: AW: [firebird-support] How to interprete gstat-results
Hi Thomas, I will study this :) Von: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Gesendet: Donnerstag, 14. April 2016 16:12 An: firebird-support@yahoogroups.com Betreff: Re: AW: [firebird-support] How to interprete gstat-results Hi Olaf, > Database "D:\INTERBASE\IFLS.GDB" > > Database header page information: > > Flags 0 > > Generation 2939162 > > System Change Number 0 > > Page size 4096 You might consider increasing to 8K, especially when the tree depth of an index starts to become > 3. To be checked with gstat -i. > ODS version 12.0 Indeed, the database was created with Firebird 3. ;-) > Oldest transaction 2893677 The "oldest interesting" transaction (OIT) in a state other than committed, e.g. also rolled back. There are no record versions for transactions < 2893677 available (cause they have been garbage collected), thus one could say why the marker is called "interesting". > Oldest active 2929833 The oldest transaction currently active/running. This is the lowest transaction ID which should be visible in MON$TRANSACTIONS. > Oldest snapshot 2878316 This is the oldest transaction started in snapshot mode. Hope I remembered that correctly. > Next transaction 2938824 Transaction id for the next new transaction. > Sequence number 0 > > Next attachment ID 87910 > > Implementation HW=Intel/i386 little-endian OS=Windows > CC=MSVC > > Shadow count 0 > > Page buffers 0 The default value in firebird.conf is used for the size of the page cache of the database. Might be too small, depending on your use case. > Next header page 0 > > Database dialect 3 > > Creation date Mar 7, 2016 18:33:37 > > Attributes force write > > > > Variable header data: > > Sweep interval: 5000 That's not a good idea. Because if the difference of OIT and OST (oldest snapshot) reaches that threshold, a sweep will kick in, visits all records in the database and removes out-dated record versions which are not interesting for other transactions anymore. Regarding OIT/OAT movement/gaps etc., commit retaining is one of your #1 enemies. Usually used behind the scene by drivers with auto commit = ON. -- With regards, Thomas Steinmaurer <http://www.upscene.com> http://www.upscene.com Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc. > > *END* > > > > > > > > Von: firebird-support@yahoogroups.com <mailto:firebird-support@yahoogroups.com> > [mailto:firebird-support@yahoogroups.com] > Gesendet: Donnerstag, 14. April 2016 14:03 > An: <mailto:firebird-support@yahoogroups.com> firebird-support@yahoogroups.com > Betreff: Re: [firebird-support] How to interprete gstat-results > > > > > >> Hello @ll, >> >> >> >> we are using firebird 3.0 RC2 and have some questions about open >> transactions. (Clients connect over odbc) The gstat output gives 7 >> transactions between the oldest and the current transaction, if we look > into >> the monitoring table, we see only 10 transactions. The "oldest one" we >> cannot find in this table. What is the oldest transaction and which roll >> does the garbage collector play? If we have many open transactions, the >> performance is bad. What role plays the attachments? What is the best way > to >> check the performance bottleneck in such cases? > > Please post an entire gstat -h output. > > -- > With regards, > Thomas Steinmaurer > < <http://www.upscene.com> http://www.upscene.com> <http://www.upscene.com> http://www.upscene.com > > Professional Tools and Services for Firebird > FB TraceManager, IB LogManager, Database Health Check, Tuning etc. > > > >
Re: AW: [firebird-support] How to interprete gstat-results
Hi Olaf, > Database "D:\INTERBASE\IFLS.GDB" > > Database header page information: > >Flags 0 > >Generation 2939162 > >System Change Number0 > >Page size 4096 You might consider increasing to 8K, especially when the tree depth of an index starts to become > 3. To be checked with gstat -i. >ODS version 12.0 Indeed, the database was created with Firebird 3. ;-) >Oldest transaction 2893677 The "oldest interesting" transaction (OIT) in a state other than committed, e.g. also rolled back. There are no record versions for transactions < 2893677 available (cause they have been garbage collected), thus one could say why the marker is called "interesting". >Oldest active 2929833 The oldest transaction currently active/running. This is the lowest transaction ID which should be visible in MON$TRANSACTIONS. >Oldest snapshot 2878316 This is the oldest transaction started in snapshot mode. Hope I remembered that correctly. >Next transaction2938824 Transaction id for the next new transaction. >Sequence number 0 > >Next attachment ID 87910 > >Implementation HW=Intel/i386 little-endian OS=Windows > CC=MSVC > >Shadow count0 > >Page buffers0 The default value in firebird.conf is used for the size of the page cache of the database. Might be too small, depending on your use case. >Next header page0 > >Database dialect3 > >Creation date Mar 7, 2016 18:33:37 > >Attributes force write > > > >Variable header data: > >Sweep interval: 5000 That's not a good idea. Because if the difference of OIT and OST (oldest snapshot) reaches that threshold, a sweep will kick in, visits all records in the database and removes out-dated record versions which are not interesting for other transactions anymore. Regarding OIT/OAT movement/gaps etc., commit retaining is one of your #1 enemies. Usually used behind the scene by drivers with auto commit = ON. -- With regards, Thomas Steinmaurer http://www.upscene.com Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc. > >*END* > > > > > > > > Von: firebird-support@yahoogroups.com > [mailto:firebird-support@yahoogroups.com] > Gesendet: Donnerstag, 14. April 2016 14:03 > An: firebird-support@yahoogroups.com > Betreff: Re: [firebird-support] How to interprete gstat-results > > > > > >> Hello @ll, >> >> >> >> we are using firebird 3.0 RC2 and have some questions about open >> transactions. (Clients connect over odbc) The gstat output gives 7 >> transactions between the oldest and the current transaction, if we look > into >> the monitoring table, we see only 10 transactions. The "oldest one" we >> cannot find in this table. What is the oldest transaction and which roll >> does the garbage collector play? If we have many open transactions, the >> performance is bad. What role plays the attachments? What is the best way > to >> check the performance bottleneck in such cases? > > Please post an entire gstat -h output. > > -- > With regards, > Thomas Steinmaurer > <http://www.upscene.com> http://www.upscene.com > > Professional Tools and Services for Firebird > FB TraceManager, IB LogManager, Database Health Check, Tuning etc. > > > >
AW: [firebird-support] How to interprete gstat-results
Hello Thomas.. Database "D:\INTERBASE\IFLS.GDB" Database header page information: Flags 0 Generation 2939162 System Change Number0 Page size 4096 ODS version 12.0 Oldest transaction 2893677 Oldest active 2929833 Oldest snapshot 2878316 Next transaction2938824 Sequence number 0 Next attachment ID 87910 Implementation HW=Intel/i386 little-endian OS=Windows CC=MSVC Shadow count0 Page buffers0 Next header page0 Database dialect3 Creation date Mar 7, 2016 18:33:37 Attributes force write Variable header data: Sweep interval: 5000 *END* Von: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Gesendet: Donnerstag, 14. April 2016 14:03 An: firebird-support@yahoogroups.com Betreff: Re: [firebird-support] How to interprete gstat-results > Hello @ll, > > > > we are using firebird 3.0 RC2 and have some questions about open > transactions. (Clients connect over odbc) The gstat output gives 7 > transactions between the oldest and the current transaction, if we look into > the monitoring table, we see only 10 transactions. The "oldest one" we > cannot find in this table. What is the oldest transaction and which roll > does the garbage collector play? If we have many open transactions, the > performance is bad. What role plays the attachments? What is the best way to > check the performance bottleneck in such cases? Please post an entire gstat -h output. -- With regards, Thomas Steinmaurer <http://www.upscene.com> http://www.upscene.com Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: [firebird-support] How to interprete gstat-results
> Hello @ll, > > > > we are using firebird 3.0 RC2 and have some questions about open > transactions. (Clients connect over odbc) The gstat output gives 7 > transactions between the oldest and the current transaction, if we look into > the monitoring table, we see only 10 transactions. The "oldest one" we > cannot find in this table. What is the oldest transaction and which roll > does the garbage collector play? If we have many open transactions, the > performance is bad. What role plays the attachments? What is the best way to > check the performance bottleneck in such cases? Please post an entire gstat -h output. -- With regards, Thomas Steinmaurer http://www.upscene.com Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
[firebird-support] How to interprete gstat-results
Hello @ll, we are using firebird 3.0 RC2 and have some questions about open transactions. (Clients connect over odbc) The gstat output gives 7 transactions between the oldest and the current transaction, if we look into the monitoring table, we see only 10 transactions. The "oldest one" we cannot find in this table. What is the oldest transaction and which roll does the garbage collector play? If we have many open transactions, the performance is bad. What role plays the attachments? What is the best way to check the performance bottleneck in such cases? Thank you Regards Olaf
