Hello [email protected], 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 all....which 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
