RE: [firebird-support] RE: Stored Proc optimisation advice
Get the statistics on the various runs of the sub-procedure- reads, writes, fetches, and marks. No writes. 47 000 indexed reads. Lets try that one again. I'd like both reads and fetches, with statistics for a run of the subquery that's fast and one that's slow. Did you mean that computing one account balance involved forty-seven thousand indexed reads? That's some account! Yup. BUT, the weird thing is, it's that SAME account that is sometimes fast and sometimes slow. It just depends on what other table/account you've queried beforehand. Gstat will tell you how deep your indexes are. Firebird indexes are very broad based triangles, with a single page at the top, lots of pages on the next level down, and enormous numbers of pages on the level below that. The taller (or deeper) the triangle, the slower it is. The height and width are determined by page size and key size. An index with four or more levels is an indication that you should increase the page size for your database. Run gstat and search for the indexes used by this sub-procedure. Max depth in the whole database is 3 ;-) I'm sure you mentioned it somewhere, but what is the database page size? And the cache size? Generation 1311 Page size 8192 ODS version 11.2 Oldest transaction 1292 Oldest active 1293 Oldest snapshot 1293 Next transaction1294 Bumped transaction 1 Sequence number 0 Next attachment ID 9 Implementation ID 16 Shadow count0 Page buffers1000 was 150, but I tried increasing to see if it would make a difference - it didn't seem to have any effect Next header page0 Database dialect3 Creation date Sep 11, 2012 11:43:22 Attributes force write, no reserve Sweep interval: 20
Re: [firebird-support] RE: Stored Proc optimisation advice
Get the statistics on the various runs of the sub-procedure- reads, writes, fetches, and marks. No writes. 47 000 indexed reads. Lets try that one again. I'd like both reads and fetches, with statistics for a run of the subquery that's fast and one that's slow. Did you mean that computing one account balance involved forty-seven thousand indexed reads? That's some account! Yup. BUT, the weird thing is, it's that SAME account that is sometimes fast and sometimes slow. It just depends on what other table/account you've queried beforehand. Gstat will tell you how deep your indexes are. Firebird indexes are very broad based triangles, with a single page at the top, lots of pages on the next level down, and enormous numbers of pages on the level below that. The taller (or deeper) the triangle, the slower it is. The height and width are determined by page size and key size. An index with four or more levels is an indication that you should increase the page size for your database. Run gstat and search for the indexes used by this sub-procedure. Max depth in the whole database is 3 ;-) I'm sure you mentioned it somewhere, but what is the database page size? And the cache size? Generation 1311 Page size 8192 ODS version 11.2 Oldest transaction 1292 Oldest active 1293 Oldest snapshot 1293 Next transaction1294 Bumped transaction 1 Sequence number 0 Next attachment ID 9 Implementation ID 16 Shadow count0 Page buffers1000 was 150, but I tried increasing to see if it would make a difference - it didn't seem to have any effect Next header page0 Database dialect3 Creation date Sep 11, 2012 11:43:22 Attributes force write, no reserve Sweep interval: 20 Having no reserve isn't a good option for a regular read/write production database, because this basically prevents having back record versions on the same page as the primary record version, thus additional page reads are necessary. -- With regards, Thomas Steinmaurer http://www.upscene.com/
Re-8: [firebird-support] Restore fails due to duplicate Value in unique Index
Hello Alexey, copying a database that has no accessing instances is the recommended way, i read. Why is it a database corruption? What happened there? FBFirstAid 2.6 said everything`s fine! Here`s a snippet of it`s LOG: 13.09.2012 13:04:53 INFO: Open database files: A:\ams4database\DB0.FDB 13.09.2012 13:04:53 INFO: Analyzing database low-level structures... 13.09.2012 13:04:53 INFO: Process database file #1 of 1 files. 13.09.2012 18:43:13 INFO: Actual PageCount: 6894935 found in database 13.09.2012 18:43:13 INFO: Found 17371 reserved and/or undefined pages. 13.09.2012 18:43:13 INFO: == DATABASE IS READY FOR DIAGNOSING AND REPAIRING. 13.09.2012 18:43:13 INFO: == Now choose Diagnose or Repair. 14.09.2012 09:17:21 INFO: --- Starting diagnose 14.09.2012 09:17:21 INFO: Running procedure: Header page check 14.09.2012 09:17:21 INFO: ODS Major = 11 (32779) 14.09.2012 09:17:21 INFO: ODS Minor = 2 14.09.2012 09:17:21 INFO: Next transaction = 68793348 14.09.2012 09:17:21 INFO: Oldest transaction = 68793346 14.09.2012 09:17:21 INFO: Oldest active = 68793347 14.09.2012 09:17:21 INFO: Oldest snapshot = 68793347 14.09.2012 09:17:21 INFO: PageSize is Ok = 4096 14.09.2012 09:17:21 INFO: Running procedure: Checking of RDB$Pages consistency 14.09.2012 09:18:58 INFO: Checking of RDB$Pages consistency: Ok 14.09.2012 09:18:58 INFO: Running procedure: Low-level check of all relations 14.09.2012 09:18:58 INFO: Relation RDB$PAGES (0) is OK 14.09.2012 09:18:58 INFO: Relation RDB$DATABASE (1) is OK 14.09.2012 09:18:58 INFO: Relation RDB$FIELDS (2) is OK 14.09.2012 09:18:58 INFO: Relation RDB$INDEX_SEGMENTS (3) is OK 14.09.2012 09:18:59 INFO: Relation RDB$INDICES (4) is OK 14.09.2012 09:18:59 INFO: Relation RDB$RELATION_FIELDS (5) is OK 14.09.2012 09:18:59 INFO: Relation RDB$RELATIONS (6) is OK 14.09.2012 09:18:59 INFO: Relation RDB$VIEW_RELATIONS (7) is OK 14.09.2012 09:18:59 INFO: Relation RDB$FORMATS (8) is OK 14.09.2012 09:18:59 INFO: Relation RDB$SECURITY_CLASSES (9) is OK 14.09.2012 09:18:59 INFO: Relation RDB$FILES (10) is OK 14.09.2012 09:18:59 INFO: Relation RDB$TYPES (11) is OK 14.09.2012 09:18:59 INFO: Relation RDB$TRIGGERS (12) is OK 14.09.2012 09:19:00 INFO: Relation RDB$DEPENDENCIES (13) is OK 14.09.2012 09:19:00 INFO: Relation RDB$FUNCTIONS (14) is OK 14.09.2012 09:19:00 INFO: Relation RDB$FUNCTION_ARGUMENTS (15) is OK 14.09.2012 09:19:00 INFO: Relation RDB$FILTERS (16) is OK 14.09.2012 09:19:00 INFO: Relation RDB$TRIGGER_MESSAGES (17) is OK 14.09.2012 09:19:00 INFO: Relation RDB$USER_PRIVILEGES (18) is OK 14.09.2012 09:19:00 INFO: Relation RDB$TRANSACTIONS (19) is OK 14.09.2012 09:19:00 INFO: Relation RDB$GENERATORS (20) is OK 14.09.2012 09:19:00 INFO: Relation RDB$FIELD_DIMENSIONS (21) is OK 14.09.2012 09:19:00 INFO: Relation RDB$RELATION_CONSTRAINTS (22) is OK 14.09.2012 09:19:00 INFO: Relation RDB$REF_CONSTRAINTS (23) is OK 14.09.2012 09:19:00 INFO: Relation RDB$CHECK_CONSTRAINTS (24) is OK 14.09.2012 09:19:00 INFO: Relation RDB$LOG_FILES (25) is OK 14.09.2012 09:19:00 INFO: Relation RDB$PROCEDURES (26) is OK 14.09.2012 09:19:00 INFO: Relation RDB$PROCEDURE_PARAMETERS (27) is OK 14.09.2012 09:19:00 INFO: Relation RDB$CHARACTER_SETS (28) is OK 14.09.2012 09:19:00 INFO: Relation RDB$COLLATIONS (29) is OK 14.09.2012 09:19:00 INFO: Relation RDB$EXCEPTIONS (30) is OK 14.09.2012 09:19:00 INFO: Relation RDB$ROLES (31) is OK 14.09.2012 09:19:00 INFO: Relation RDB$BACKUP_HISTORY (32) is OK . . . . 14.09.2012 09:42:05 INFO: Low-level check of all relations: Ok 14.09.2012 09:42:05 INFO: --- Finished diagnose regards Maik Sommer IT-Systemadministrator processed by David.fx Subject: Re: Re-6: [firebird-support] Restore fails due to duplicate Value in unique Index (13-Sep-2012 17:46) From:Alexey Kovyazin a...@ib-aid.com To: firebird-support@yahoogroups.com Hello Maik, database, stopped the default instance and made a copy to a different volume, so i have a database i can Analyse. Currently, i made another copy, that i use to Analyse it with IBFirstAid 2.6. This is a database corruption, but our IBFirstAID will not help in this case, since it's a system index problem. You can contact support at ib-aid.com to get professional recovery support through remote desktop. Regards, Alexey Kovyazin IBSurgeon (www.ib-aid.com) database, stopped the default instance and made a copy to a different volume, so i have a database i can Analyse. Currently, i made another copy, that i use to Analyse it with IBFirstAid 2.6. I think this is a very weird issue Mit freundlichen Grüßen aus der Lutherstadt Maik Sommer IT-Systemadministrator processed by David.fx Subject: Re: Re-4: [firebird-support] Restore fails due to duplicate Value in unique Index (13-Sep-2012 14:29) From: Thomas Steinmaurer t...@iblogmanager.com mailto:ts%40iblogmanager.com To:
RE: [firebird-support] RE: Stored Proc optimisation advice
Attributes force write, no reserve Having no reserve isn't a good option for a regular read/write production database, because this basically prevents having back record versions on the same page as the primary record version, thus additional page reads are necessary. Thanks Thomas! Is this by any chance related to the USE_ALL_SPACE option you have when restoring a database? Or is GFIX the only way of updating it?
Re: [firebird-support] RE: Stored Proc optimisation advice
On Fri, Sep 14, 2012 at 3:44 AM, Maya Opperman m...@omniaccounts.co.zawrote: I'd like both reads and fetches, with statistics for a run of the subquery that's fast and one that's slow. Did you mean that computing one account balance involved forty-seven thousand indexed reads? That's some account! Yup. BUT, the weird thing is, it's that SAME account that is sometimes fast and sometimes slow. It just depends on what other table/account you've queried beforehand. Right, but in order to understand why one is fast and one is slow, it would be good to get statistics on a fast run and a slow one, regardless of which account is actually being balanced. Max depth in the whole database is 3 ;-) Good. I'm sure you mentioned it somewhere, but what is the database page size? And the cache size? Page size 8192 Page buffers1000 was 150, but I tried increasing to see if it would make a difference - it didn't seem to have any effect Attributes force write, no reserve Sweep interval: 20 Very odd.. Good luck, Ann [Non-text portions of this message have been removed]
[firebird-support] (unknown)
pa href=http://www.khanelholey.com/belleducation/geoffreygordon51/;http://www.khanelholey.com/belleducation/geoffreygordon51//a/p [Non-text portions of this message have been removed]
RE: [firebird-support] Re: Can't continue after bugcheck
rxsaccessdb (Server)Wed Sep 12 10:28:55 2012 INET/inet_error: read errno = 104 rxsaccessdb (Server)Wed Sep 12 10:29:34 2012 Database: /opt/firebird/db/edi.fdb operation was cancelled internal gds software consistency check (error during savepoint backout (290)) From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Dmitry Yemanov Sent: Wednesday, September 12, 2012 11:54 AM To: firebird-support@yahoogroups.com Subject: [firebird-support] Re: Can't continue after bugcheck 12.09.2012 19:42, Rick Debay wrote: Rarely I'll get this error, and it goes away after recreating the connection. Neither gbak (backup restore) nor gfix (validate) report errors. What should be my next steps? Look at firebird.log and figure out the reason. It's logged right before you get that error. Dmitry
[firebird-support] (unknown)
pa href=http://serkanozg.eu.pn/beliefelephant/jonathanlewis40/;http://serkanozg.eu.pn/beliefelephant/jonathanlewis40//a/p [Non-text portions of this message have been removed]
