Re: [firebird-support] file size and transactions
Hello shg_siste...@yahoo.com.ar, Friday, September 15, 2017, 2:53:19 AM, you wrote: > I read somewhere that the "read only" transactions are absolutelly > harmless to the GC and sweeping. Is that so? It is true for READ ONLY transactions that are started in READ COMMITTED isolation mode. In other isolation modes, long-running transactions in both READ ONLY and READ WRITE will block GC and evade sweeps. Helen
Re: [firebird-support] file size and transactions
>>Karol, >>I was referring to the default transaction state of snapshot (repeatable >>read) >>Other transaction states will have different effects. In your case >>read commited needs only the newest record version (or record versions >>created by >>the transaction itself) so its not going to block anything. >>Paul Thank You Paul, I asked to confirm if my knowledge is still up to date and luckily it is :) regards, Karol Bieniaszewski
RE: [firebird-support] file size and transactions
> I read somewhere that the "read only" transactions are absolutelly harmless > to the GC and sweeping. Is that so? >>Read only transactions only physically have an impact on the TIP/Header >>page(s), however a long running read only transaction >>will have the same effect as a long running read write transaction in that it >>will block GC and sweeps until it >>commits. <> Karol, I was referring to the default transaction state of snapshot (repeatable read) Other transaction states will have different effects. In your case read commited needs only the newest record version (or record versions created by the transaction itself) so its not going to block anything. Paul
Re: [firebird-support] file size and transactions
> I read somewhere that the "read only" transactions are absolutelly harmless > to the GC and sweeping. Is that so? >>Read only transactions only physically have an impact on the TIP/Header >>page(s), however a long running read only transaction >>will have the same effect as a long running read write transaction in that it >>will block GC and sweeps until it >>commits. >>Paul Hi, may i ask why readonly readcommited transaction block GC? Is this true? regards, Karol Bieniaszewski
RE: [firebird-support] file size and transactions
<> Not really, 2 weeks or 2 months would be a huge time. > I read somewhere that the "read only" transactions are absolutelly harmless > to the GC and sweeping. Is that so? Read only transactions only physically have an impact on the TIP/Header page(s), however a long running read only transaction will have the same effect as a long running read write transaction in that it will block GC and sweeps until it commits. Paul
Re: [firebird-support] file size and transactions
Hello Helen!! THANKS A MILLION FOR YOUR VERY COMPLETE ANSWER I did a simple routine to trap in a TXT log file all the active transactions. I realized that I need to be SYSDBA in order to run a query against MON$TRANSACTIONS. So, every time a user enters or exits from the applitaction I save a log of all the transactions. I found that I had some read/write transactions running for 2 hours. And that is a HUGE time I think! I read somewhere that the "read only" transactions are absolutelly harmless to the GC and sweeping. Is that so? Sergio.
Re: [firebird-support] file size and transactions
Hello shg_siste...@yahoo.com.ar, Thursday, September 14, 2017, 2:10:23 AM, you wrote: > Hello! I'm noticing a small increase of my database size. Now it > has 566,992 KB. After a backup/restore the size goes to 533,640 KB. > I don't have long running read/write transactions (well... I think) There is nothing wrong with your transaction statistics. The reason why the database "grows" is this: A Firebird database is stored in one file. This file is made up of "pages" - chunks of disk that the Fb engine keeps account of individually. In your case, each page is 4Kb. The engine is constantly performing maintenance on these pages - this is called garbage collection and sweeping. GC and sweeping are related to each other but they are not exactly the same. Between them, they identify pages that no longer contain record versions that are needed for anything, so they release these pages for re-use. Thus, changing or deleting records does NOT reduce the size of the database file. A newly restored database has no spare pages waiting for re-use. That is why the file has its "minimum" size immediately after a restore. It has exactly the number of pages needed to store everything that was backed up. Garbage pages are not backed up at allwhich is why a badly managed database can really only be properly cleaned up by a full backup and restore. At the same time as GC and sweeping are going on, the engine is acquiring new pages (two at a time - in your case, 8 KB)) from the operating system whenever it has insufficient empty pages available to store a new record or record version from an insert or update. Old record versions, from updates and deletes, stay on their pages until the GC/sweep process ultimately frees them. When transactions are not well managed, the housekeeping gets "stuck" on pages that the bad management keeps in an "interesting" state. If the programmer has not taken good care of committing transactions that are finished, the engine has no choice but to keep those pages in an "interesting" state. That is when you would see an increasingly large gap between the oldest transaction (oldest interesting, or OIT) and the oldest snapshot (OST). Then, you can query MON$TRANSACTIONS to try to discover where the problems are. But your stats look fine. Know, too, that the engine needs pages for other things besides data - it maintains index pages, blob pages, etc. Once a page has been used as one type of page (data or index or blob) it cannot be reused for another type of storage. So some of your operations could be consuming quite a lot of new pages and this is not an unhealthy sign, either! ;-) Helen
Re: [firebird-support] file size and transactions
No. I only point you how to check this. From your header you can read that oldest active and next transaction have only 5 transactions gap. Other numbers you can progress by running sweep. Regards,Karol Bieniaszewski Oryginalna wiadomość Od: "shg_siste...@yahoo.com.ar [firebird-support]" <firebird-support@yahoogroups.com> Data: 13.09.2017 17:29 (GMT+01:00) Do: firebird-support@yahoogroups.com Temat: Re: [firebird-support] file size and transactions thanks Karol! So, do you mean that the transactions numbers showed in the database header indicate that I'm having long running read/write transactions somewhere?
Re: [firebird-support] file size and transactions
thanks Karol! So, do you mean that the transactions numbers showed in the database header indicate that I'm having long running read/write transactions somewhere?
Re: [firebird-support] file size and transactions
Hi, Run Select * from mos$transactions Then you will see if you have long running Regards,Karol Bieniaszewski null
