Re: [firebird-support] gbak trigger(3) error
On 08/08/13 19:27, jack.mason58 wrote: gbak: ERROR:table/procedure has non-SQL security class defined From http://sourceforge.net/mailarchive/forum.php?thread_name=5106F9F2.4040200%40ibphoenix.comforum_name=firebird-devel At 01:07 a.m. 29/01/2013, Dmitry Yemanov wrote: AFAIR, this is a known issue. ODS 11.2 databases cannot be restored on older FB versions because it has other conventions to name security classes (SQL$number instead of SQL$tablename, this was done to fix the old security issue with longer than 27 chars metadata names) and these new names, after being backed up successfully, are not accepted by older FB versions due to their system triggers expecting the name in the old format (SQL$tablename). I don't know about any solution other than migrating via a script. Might be helpful? Cheers, Norm. -- Norman Dunbar Dunbar IT Consultants Ltd Registered address: 27a Lidget Hill Pudsey West Yorkshire United Kingdom LS28 7LG Company Number: 05132767
Re: [firebird-support] gbak trigger(3) error
It seems as if you are attempting to downgrade a database - at least, that's what my Google-fu turns up - but I don't have a very high Google-fu rating! ;-) There's a tracker on this very matter at http://tracker.firebirdsql.org/browse/CORE-2949. Cheers, Norm. -- Norman Dunbar Dunbar IT Consultants Ltd Registered address: 27a Lidget Hill Pudsey West Yorkshire United Kingdom LS28 7LG Company Number: 05132767
[firebird-support] Re: Can this CTE be optimized further, it takes 16secs but cummu. effect is 20-25min
Amazing!!! Look at the new plan (speed) below! Due to this the amalgamated report also works in a second or two, thanks a million S.E.T. So is it good advise to look for the NATURAL in plan and set indexes of the related table.field? Or there is more thoughts to it than just this? I still do see a NATURAL in the plan below but the fields IID (which is a PK) and IPID (which is a FK) have index on them, why would that be? Though many FKs in the database have index but there are still some that do not have a constraint on them thus do not have an index, is it a good practice to have all FK indexed constraint or not? Please advise BTW the GTT is a misnomer it is a regular table that has the iIDs that the user selected of items to report on. I had planned on a real GTT but due my program limitations about connections I could not use them and created regular tables instead. Thanks once again Kind regards Bhavbhuti PLAN (HEIR D INDEX (FK_MITEMS_IPID)) PLAN SORT (JOIN (JOIN (JOIN (HEIR P NATURAL, HEIR GTT INDEX (PK_MITEMS_IID)) PLAN (HEIR P2 INDEX (FK_MITEMS_IPID)), SGRNITEM INDEX (FK_SGOODSRECVNOTEITEM_IITEMID), TGRN INDEX (PK_TGOODSRECVNOTE_IID Executing statement... Statement executed (elapsed time: 0.000s). 67203 fetches, 2 marks, 135 reads, 2 writes. 0 inserts, 0 updates, 0 deletes, 4388 index, 10197 seq. Delta memory: 80560 bytes. Total execution time: 0.078s Script execution finished. --- In firebird-support@yahoogroups.com, Svein Erling Tysvær svein.erling.tysvaer@... wrote: I notice two things, Bhavbhuti: 1) GTT isn't needed, so you can simplify a little bit (though I don't think this will reduce the execution time much): WITH RECURSIVE Heir(iID, iPK) AS (SELECT p.iID, p.iID AS iPK FROM mItems p WHERE p.lHierarchialStock = 1 OR NOT EXISTS (SELECT 1 FROM mItems d WHERE d.iPID = p.iID) UNION ALL SELECT H.iID, p2.iID FROM mItems p2 JOIN Heir H on p2.iPID = H.iPK) ... 2) I don't like PLAN (HEIR D NATURAL) and PLAN (HEIR P2 NATURAL). Do you have an index for mItems.iPID (and is mItems.iPID defined as integer)? If not, then adding this index ought to make your query a lot faster. HTH, Set
[firebird-support] Re: Can this CTE be optimized further, it takes 16secs but cummu. effect is 20-25min
Amazing!!! Look at the new plan (speed) below! Due to this the amalgamated report also works in a second or two, thanks a million S.E.T. Congratulations, reducing the execution time from 20-25 minutes to a few seconds sounds very good. So is it good advise to look for the NATURAL in plan and set indexes of the related table.field? Or there is more thoughts to it than just this? I still do see a NATURAL in the plan below but the fields IID (which is a PK) and IPID (which is a FK) have index on them, why would that be? Though many FKs in the database have index but there are still some that do not have a constraint on them thus do not have an index, is it a good practice to have all FK indexed constraint or not? There's a bit more thought required than just setting indexes every NATURAL, although looking for NATURAL in your plan is a good starting point for further analysis and experiments. Normally (unless the table is big), it doesn't matter if the first table in your plan is NATURAL, but any later occurrence may mean (in my simplified understanding, it is not how Firebird really implements things, but I've never understood that) that NATURAL is used not once for the whole table, but iteratively for each potential record. Hence, I think I'd prefer NATURAL for the first table in the plan if that table contains 1 million records over NATURAL for a table containing 5 records that appear later in the plan. In your case, no index can be used for the remaining NATURAL. In order for an index to be used, you would need to remove 'OR NOT EXISTS (SELECT 1 FROM mItems d WHERE d.iPID = p.iID)' and then have an index for p.lHierarchialStock. That means that the result would be different, and since I guess 1 isn't very selective for lHierarchialStock, the result could be that it took longer to use an index than to go NATURAL. So I think your query now is about as good as it gets. Set
[firebird-support] Re: Can this CTE be optimized further, it takes 16secs but cummu. effect is 20-25min
Thanks a lot SET for the help and the explanation. I will keep a look out for the plans and look for such NATURALs Kind regards Bhavbhuti --- In firebird-support@yahoogroups.com, Svein Erling Tysvær svein.erling.tysvaer@... wrote: Amazing!!! Look at the new plan (speed) below! Due to this the amalgamated report also works in a second or two, thanks a million S.E.T. Congratulations, reducing the execution time from 20-25 minutes to a few seconds sounds very good. So is it good advise to look for the NATURAL in plan and set indexes of the related table.field? Or there is more thoughts to it than just this? I still do see a NATURAL in the plan below but the fields IID (which is a PK) and IPID (which is a FK) have index on them, why would that be? Though many FKs in the database have index but there are still some that do not have a constraint on them thus do not have an index, is it a good practice to have all FK indexed constraint or not? There's a bit more thought required than just setting indexes every NATURAL, although looking for NATURAL in your plan is a good starting point for further analysis and experiments. Normally (unless the table is big), it doesn't matter if the first table in your plan is NATURAL, but any later occurrence may mean (in my simplified understanding, it is not how Firebird really implements things, but I've never understood that) that NATURAL is used not once for the whole table, but iteratively for each potential record. Hence, I think I'd prefer NATURAL for the first table in the plan if that table contains 1 million records over NATURAL for a table containing 5 records that appear later in the plan. In your case, no index can be used for the remaining NATURAL. In order for an index to be used, you would need to remove 'OR NOT EXISTS (SELECT 1 FROM mItems d WHERE d.iPID = p.iID)' and then have an index for p.lHierarchialStock. That means that the result would be different, and since I guess 1 isn't very selective for lHierarchialStock, the result could be that it took longer to use an index than to go NATURAL. So I think your query now is about as good as it gets. Set
Re: [firebird-support] Performance with heavily updated table
On 08/08/2013 11:49, Svein Erling Tysvær wrote: Can you show us the statistics when the database is slow? Exactly what numbers would you like? - we can re-run and generate anything that would be helpful. I'm only wondering if the gap between oldest (interesting/active) transaction and next transaction is noticeable. Though this is starting to sound like this could be something else, and that means that I've got no clue. The gap between OIT and next transaction was around 2,000,000. I did a manual sweep and the gap changed to zero, but the performance of the query did not change. (I'd said in an earlier message that I'd been told that running a sweep made no difference, and I have now seen this for myself.) I installed Firebird 2.5 to see whether its trace feature would give any more information. But, run against exactly the same database (a copy of the file), it behaved as one would have expected, not as 2.1.5. Here is the STATS output from doing the COUNT(*) on 2.5: Reads = 1489 Writes 0 Fetches = 263870 which is reasonable as gstat shows the table as occupying a bit under 1,500 pages, suggesting that a table scan reads each page once, which is exactly what one would expect. Here is the STATS output from the same query on the same database using 2.1.5: Reads = 81652 Writes 0 Fetches = 393459 and, with doing 81,000 reads instead of 1,500 reads, it not surprisingly takes *much* longer. If we backup/restore the database the numbers for 2.1.5 revert to around 1,500. If we then run updates on this table for a few hours the numbers get gradually worse again. We don't want to stop all our processes dead in order to do a backup and restore every couple of hours. What other solutions are there? There are by the way just under 130,000 records in the table. I note that using 2.5 there are just over 260,000 fetches, which is a bit more than two per record, although I haven't worked out what the second is. Using 2.1.5 on the database that hasn't recently been restored there are just over 360,000 fetches, which is a bit more than *three* per record. So one might ask: (1) What is the extra fetch per record? (2) Why in many but not all cases (80,000 out of 130,000) does it appear to cause a cache miss and page read? and, of course: (3) How do I stop it happening? -- Tim Ward [Non-text portions of this message have been removed]
Re: [firebird-support] gbak trigger(3) error
On Fri, 09 Aug 2013 07:41:27 +0100, Norman Dunbar nor...@dunbar-it.co.uk wrote: On 08/08/13 19:27, jack.mason58 wrote: gbak: ERROR:table/procedure has non-SQL security class defined From http://sourceforge.net/mailarchive/forum.php?thread_name=5106F9F2.4040200%40ibphoenix.comforum_name=firebird-devel At 01:07 a.m. 29/01/2013, Dmitry Yemanov wrote: AFAIR, this is a known issue. ODS 11.2 databases cannot be restored on older FB versions because it has other conventions to name security classes (SQL$number instead of SQL$tablename, this was done to fix the old security issue with longer than 27 chars metadata names) and these new names, after being backed up successfully, are not accepted by older FB versions due to their system triggers expecting the name in the old format (SQL$tablename). I don't know about any solution other than migrating via a script. Might be helpful? This might indicate that the gbak version used is from an older version of Firebird. Mark
[firebird-support] Re: gbak trigger(3) error
Thanks for your response, but this doesn't appear to be relevant as we do not know what ODS is, have never used it, and created the database brand new under Firebird 2.5. --- In firebird-support@yahoogroups.com, Norman Dunbar Norman@... wrote: On 08/08/13 19:27, jack.mason58 wrote: gbak: ERROR:table/procedure has non-SQL security class defined From http://sourceforge.net/mailarchive/forum.php?thread_name=5106F9F2.4040200%40ibphoenix.comforum_name=firebird-devel At 01:07 a.m. 29/01/2013, Dmitry Yemanov wrote: AFAIR, this is a known issue. ODS 11.2 databases cannot be restored on older FB versions because it has other conventions to name security classes (SQL$number instead of SQL$tablename, this was done to fix the old security issue with longer than 27 chars metadata names) and these new names, after being backed up successfully, are not accepted by older FB versions due to their system triggers expecting the name in the old format (SQL$tablename). I don't know about any solution other than migrating via a script. Might be helpful? Cheers, Norm. -- Norman Dunbar Dunbar IT Consultants Ltd Registered address: 27a Lidget Hill Pudsey West Yorkshire United Kingdom LS28 7LG Company Number: 05132767
[firebird-support] Re: gbak trigger(3) error
Thanks for your response. The version of gbak is 2.52 right off the Firebirdsql.com website yesterday. The database was backed up successfully using that version of gbak, but would not restore without the indicated error. gbak has another problem (will not reliably backup one of our databases) so we would use another backup agent if we could. --- In firebird-support@yahoogroups.com, Mark Rotteveel mark@... wrote: On Fri, 09 Aug 2013 07:41:27 +0100, Norman Dunbar Norman@... wrote: On 08/08/13 19:27, jack.mason58 wrote: gbak: ERROR:table/procedure has non-SQL security class defined From http://sourceforge.net/mailarchive/forum.php?thread_name=5106F9F2.4040200%40ibphoenix.comforum_name=firebird-devel At 01:07 a.m. 29/01/2013, Dmitry Yemanov wrote: AFAIR, this is a known issue. ODS 11.2 databases cannot be restored on older FB versions because it has other conventions to name security classes (SQL$number instead of SQL$tablename, this was done to fix the old security issue with longer than 27 chars metadata names) and these new names, after being backed up successfully, are not accepted by older FB versions due to their system triggers expecting the name in the old format (SQL$tablename). I don't know about any solution other than migrating via a script. Might be helpful? This might indicate that the gbak version used is from an older version of Firebird. Mark
[firebird-support] Re: gbak trigger(3) error
Thanks for your response. We are not downgrading a database; quite the opposite. Interbase 6.0 is about 10 years old and since we upgraded our server from Windows 7 to Linux Mint, we wanted to upgrade our database server to one currently supported, and since the Interbase format is supported by Firebird --- In firebird-support@yahoogroups.com, Norman Dunbar Norman@... wrote: It seems as if you are attempting to downgrade a database - at least, that's what my Google-fu turns up - but I don't have a very high Google-fu rating! ;-) There's a tracker on this very matter at http://tracker.firebirdsql.org/browse/CORE-2949. Cheers, Norm. -- Norman Dunbar Dunbar IT Consultants Ltd Registered address: 27a Lidget Hill Pudsey West Yorkshire United Kingdom LS28 7LG Company Number: 05132767
Re: [firebird-support] Re: gbak trigger(3) error
... and since the Interbase format is supported by Firebird I think I have an idea on why you have problems. From where and when is the information that Interbase format is supported by Firebird?
[firebird-support] gbak reliability problem
Whether we use the old Interbase 6.02 gbak or the current 2.52 (as of yesterday) Firebird gbak, gbak will not reliably backup one of our Interbase databases. The structure of the failing database table is quite simple: one integer, one timestamp, and one blob. The blob appears to be the problem. The database is backed up successfully until this table is attempted. Even though the database is on a remote system that is quite slow to access, most of the database backs up within a couple of minutes, including 145,000 records of another table Sometimes gbak will back up the entire table, and complete the entire database successfully, sometimes it will hang showing no recordss backed up, sometimes 20,000 records backed up, sometimes 40,000 records backed up. When it hangs, it hangs for about an hour (we haven't actually timed it) and then aborts. The backup command is part of a Windows batch file: C:\program files\firebird\firebird_2_5\bin\gbak -b -v lserver:C:\BFL\SMTBDB\sales C:\backups\Friday\sales.bak
[firebird-support] gbak 2.52 will not run outside installation directory
If you copy gbak.exe 2.5.2 from the installation directory into another directory, gbak will not execute correctly in the new directory. For older versions of gbak, we could copy gbak into a working directory so it was simple to execute a backup or restore command in a Windows cmd window without having to include the path name of the installation directory.
Re: [firebird-support] gbak 2.52 will not run outside installation directory
If you copy gbak.exe 2.5.2 from the installation directory into another directory, gbak will not execute correctly in the new directory. For older versions of gbak, we could copy gbak into a working directory so it was simple to execute a backup or restore command in a Windows cmd window without having to include the path name of the installation directory. It probably can't find the client library fbclient.dll. -- 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] Re: gbak trigger(3) error
Firebird was developed to be a better Interbase and has improved on Interbase over the years. We have applications that run identically and equally well whether connected to an Interbase server or a Firebird server. In none of the cases is it Firebird to Firebird. It is gbak that has the problems, not Firebird. --- In firebird-support@yahoogroups.com, André Knappstein Knappstein@... wrote: ... and since the Interbase format is supported by Firebird I think I have an idea on why you have problems. From where and when is the information that Interbase format is supported by Firebird?
Re: [firebird-support] Re: gbak trigger(3) error
On Fri, 09 Aug 2013 15:10:31 -, jack.mason58 jack.maso...@yahoo.com wrote: Where do I find fbstat? It is not included in the 2.5.2 download I pulled yesterday from firebirdsql.com. It is not in the bin directory nor the top directory. 03/19/2013 11:23 AM 241,664 gstat.exe ODS is On Disc Structure. If you run a gstat -h database_name (or fbstat -h database on Linux Mint!) then it will display something like the following: You need gstat, it is just called fbstat on Linux Mint. Mark
Re: [firebird-support] Re: gbak trigger(3) error
Hi ! Em 9/8/2013 12:10, jack.mason58 escreveu: Where do I find fbstat? It is not included in the 2.5.2 download I pulled yesterday from firebirdsql.com. It is not in the bin directory nor the top directory. It's GSTAT It's a long long time that I used Interbase... But I think you should use gbak from Interbase to produce the back-up and restore it using gbak from firebird. I don't know if the back-up produced by IB 6 gbak will be compatible with FB 2.5 gbak. when I migrate from IB 6 it was to FB 0.9... If FB 2.5 gbak could not understand IB 6 gbak back-up, you could try a two step migration IB 6 - FB 1 - FB 2.5 or a 3 step migration IB 6 - FB 1 - FB 1.5 - FB 2.5 As far as I know gbak will be retro compatible, but IB 6 is very old version, don't know how long the retro compatibility is preserved. Another option is to extract SQL Metadata using IB isql (-X option), then create a new FB 2.5 database, run the generated script, and pump the data from the IB server to FB server. see you !
RE: [firebird-support] gbak reliability problem
I think I've had this before, a long time ago. If I remember right, I ended up using the -L switch to ignore transactions in limbo. From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of jack.mason58 Sent: Friday, August 09, 2013 6:43 AM To: firebird-support@yahoogroups.com Subject: [firebird-support] gbak reliability problem Whether we use the old Interbase 6.02 gbak or the current 2.52 (as of yesterday) Firebird gbak, gbak will not reliably backup one of our Interbase databases. The structure of the failing database table is quite simple: one integer, one timestamp, and one blob. The blob appears to be the problem. The database is backed up successfully until this table is attempted. Even though the database is on a remote system that is quite slow to access, most of the database backs up within a couple of minutes, including 145,000 records of another table Sometimes gbak will back up the entire table, and complete the entire database successfully, sometimes it will hang showing no recordss backed up, sometimes 20,000 records backed up, sometimes 40,000 records backed up. When it hangs, it hangs for about an hour (we haven't actually timed it) and then aborts. The backup command is part of a Windows batch file: C:\program files\firebird\firebird_2_5\bin\gbak -b -v lserver:C:\BFL\SMTBDB\sales C:\backups\Friday\sales.bak [Non-text portions of this message have been removed]
Re: Odp: [firebird-support] Re: FB30 question about row_number
liviusliv...@poczta.onet.pl [2013-08-08 22:24] : Hi, I know how to do select with this, but i ask how should row_number work without specify order by in over clause. It numerate records in some internal order or it should numerate it in retrival order. You do select an without order you should have always sequence 1 2 3 4? And only when you specify order in over clause you can get it as e.g. 2 4 1 3? ROW_NUMBER: Assigns a sequential number to each row in the result set, or said another way number of the current row within its partition, counting from 1 so a sequential number for the set (partition) involved. Ordering is used for order-relative functions such as row_number. like for Select First ... or Select .. rows, an Order BY is required if you want something predictible I guess that what it is said for MsSQL is also true for Firebird : There is no guarantee that the rows returned by a query using ROW_NUMBER() will be ordered exactly the same with each execution unless the following conditions are true. Values of the partitioned column are unique. Values of the ORDER BY columns are unique. Combinations of values of the partition column and ORDER BY columns are unique.
Re: Odp: [firebird-support] Performance with heavily updated table
Karol Bieniaszewski wrote If this is really big update count and pages of that table are filled near 100% then row version go to another page and to process one row engine must read all pages as many times as versions pages But may be i'am wrong - i do not know super internal details about Firebird work Karol is exactly right - at least I believe he is. When you do a count (*) on a table with no back versions, the pages of the table are read one by one, so the load on the page cache is minimal - some metadata, parts of the RDB$PAGES table, a pointer page, and one active data page. So the total number of reads is about the total number of pages in the table and nothing is ever forced out of cache. When you update a record, the old version (or the difference between the two versions) is written elsewhere. In the best case, elsewhere is on the same page - if the full back version is preserved, then it's not re-written at all, Firebird just rearranges some pointers. If you update so many record on a page that there is no place for the old version (full or difference) the the old version goes on an overflow page. If you do all your updates in storage order, then all the old versions from page n go to the same overflow page. If, as is more likely, you update the records in random order, each overflow page will have records from many different primary data pages. In your case, I'd guess that each overflow page has back versions from about 100 different data pages. So, my guess is that you read a record, which causes Firebird to chase down the old versions to see if any can be removed. That brings in an overflow page, but there's still an older version on a different overflow page. That's three page reads for that record. The next record also has two back versions on two different overflow pages ... two more page reads. And so on. Sooner or later, the cache fills and pages get released. Naturally, given the way the world works, the next overflow page you need is the one that was just released. question: How many writes do you see when you run the count (*)? Good luck, Ann irrelevantSorry this is so late, Jim and I have beens sailing on the Saint John River in Canada, warm fresh water, out beyond the reach of cell phones. We're back in civilization temporarily so he can replace the wireless mouse my dog ate and resume coding his next project./irrelevant
Re: [firebird-support] Performance with heavily updated table
On Aug 9, 2013, at 9:20 AM, Tim Ward t...@telensa.com wrote: The gap between OIT and next transaction was around 2,000,000. I did a manual sweep and the gap changed to zero, but the performance of the query did not change. (I'd said in an earlier message that I'd been told that running a sweep made no difference, and I have now seen this for myself.) Hmmm.. Very odd indeed. Clearly the sweep succeeded, since the gap went to zero, and that should mean that all the overflow pages have been released from the table. Do you create any new records while you're doing the updates? I wrote rather glibly about overflow pages, but those pages are actually in the table and can be used to store new records. That might reduce the record density - if new records were created during the update phase. From your description, I'd guess that's not what's happening. I really don't know why the primary data pages that were created during the initial storage of the records would be read more than once. I installed Firebird 2.5 to see whether its trace feature would give any more information. But, run against exactly the same database (a copy of the file), it behaved as one would have expected, not as 2.1.5. Here is the STATS output from doing the COUNT(*) on 2.5: Reads = 1489 Writes 0 Fetches = 263870 which is reasonable as gstat shows the table as occupying a bit under 1,500 pages, suggesting that a table scan reads each page once, which is exactly what one would expect. Interesting that the writes are zero - something has done your garbage collection for you. Here is the STATS output from the same query on the same database using 2.1.5: Reads = 81652 Writes 0 Fetches = 393459 Reads went up by a factor of 80 while fetches (requests for a page in cache or not) went up by a factor of less than .5. Maybe 2.5 uses a large cache? (1) What is the extra fetch per record? Back versions. (2) Why in many but not all cases (80,000 out of 130,000) does it appear to cause a cache miss and page read? Luck and cache management. and, of course: (3) How do I stop it happening? If 2.5 has a larger default cache size and you're using the default cache size, increase the cache in 2.1.5. Good luck, Ann who really should read all her mail before responding.
[firebird-support] Manual Deletion Question
During development, we generate a number of empty databases with the same file name, each to replace the previous version. Question: Is it required, or desirable, manually to delete an existing, empty FB table prior to generating a new one with the same file name and location (path)? Best, jwc from iMac [Non-text portions of this message have been removed]
