FirebirdSQL
[gstat] add option to analyze all pages in the database
Hi
i see no way to know anything about other pages in database not releated to tables or indexes.
usage: gstat [options] <database> or gstat <database> [options]
Available switches:
-a analyze data and index pages
-d analyze data pages
-e analyze database encryption
-h analyze header page ONLY
-i analyze index leaf pages
-s analyze system relations in addition to user tables
-u username
-p password
-fetch fetch password from file
-r analyze average record and version length
-t tablename <tablename2...> (case sensitive)
-role SQL role name
-tr use trusted authentication
-z display version number
option -t accepts several table names only if used after <database>
please add such option to gstat.
We have real case when in few minutes database growed from ~40GB to 63 GB and it eat whole free space. After this happened we have tried to find the reason of this situation, but when we run gstat with all possible options it return sumarize 42 GB of database pages belonged to tables and indexes. We need to know what kind of other pages types was outside of tables and indexes space. We then can catch the reason of this situation.
I do not know what kind of pages can be there but i consider:
- free pages
- temp table pages?
- temp blob from procedures or queries.
- ......
IBSurgeon offers a database analyzer. There is no point to spoil their business.
IBSurgeon offers a database analyzer. There is no point to spoil their business.
That is not a good reason IMHO.
IBSurgeon offers a database analyzer. There is no point to spoil their business.
do you mean IBAnalyst? If yes, it work throw gstat, so same info as my app have...
So no info about other pages there.
- free pages - already available via SQL/API, could be added to gstat
- temp table pages - stored outside the database, thus irrelevant
- temp blob from procedures or queries - the only part that is missing, I guess
3. temp blob from procedures or queries - the only part that is missing, I guess
After transaction commit/rollback; are they marked as Free Pages or remaining as Temp Blob pages for future usage?
Pages of temporary blobs become part of some table, if blob materialized, else marked as free after transaction finished.
Number of free pages is available in isql (via show database command).
There is also many other types of pages that gstat didn't report about - TIP, PIP, SCNS.
Pages of temporary blobs become part of some table, if blob materialized, else marked as free after transaction finished.
Is this marked by Garbage Collector? Sweep? or other action? What if connection was interrupted or error like below occur?
I/O error during "WriteFile" operation for file "C:\BAZA\XXX.FDB"
Error while trying to write to file
There is not enough space on the disk.
Error extending file "C:\BAZA\XXX.FDB" by 33 page(s).
Currently allocated 4084160 pages, requested page number 4084192
MonitoringData: Cannot initialize the shared memory region
operating system directive SetFilePointer failed
There is not enough space on the disk.
Operating system call SetFilePointer failed. Error code 112
Number of free pages is available in isql (via show database command). There is also many other types of pages that gstat didn't report about - TIP, PIP, SCNS.
will be good to consolidate all info into one tool gstat
Pages of temporary blobs become part of some table, if blob materialized, else marked as free after transaction finished.
Is this marked by Garbage Collector? Sweep? or other action?
They are released and marked as free when transaction ends. No user action is required, if you worry about it.
What if connection was interrupted or error like below occur?
If server process crashed, that pages will be orphaned. Else they will be marked as free. gfix will mark orphan pages as free, if no other errors will be found.