[firebird-support] Nbackup, Default I/O ON or OFF
Hi Everyone, I can't see the default option for Direct I/O in my system. On or Off? It's an ubuntu 64bit with firebird 2.5.0 classic. There's a story about it in the nbackup-backups file but I can't clearly figure out and be sure what the default is. It's no big deal but, Maybe it can be written in the help like : Use or not direct I/O when scanning database (Default: Off) Maybe it can be written on the console as a single line when nbackup is started? And why am I searching on this.. My system crawled while doing a -B 0 level nbackup today, which I have never tried before. Everything was fine when it finished. But the db file is 28gb having many connections and it took about 10minutes to complete. And now I'm hasitating to start the -B 1 level nbackup. Will it suspend every connection? Will it take long? Should I update to 2.5.2 first? Thank you
[firebird-support] RE: Incremental Backup
Hi Dmirty, Where do we get 2.5.3? Is guess it's not an official release yet. Thank you
[firebird-support] RE: Incremental Backup
Ok, it was here :) http://web.firebirdsql.org/download/snapshot_builds/linux/fb2.5/ http://web.firebirdsql.org/download/snapshot_builds/linux/fb2.5/ Is it ok to use this on a production environment?
Re: [firebird-support] Nbackup, Default I/O ON or OFF
Hello, atunccekic! Friday, December 20, 2013, 12:35:54 PM, you wrote: adct It's no big deal but, adct Maybe it can be written in the help like : adct Use or not direct I/O when scanning database (Default: Off) I think there is a mistake, because nbackup is a part of Firebird server, so it can't change Forced Write mode for the database where clients are working. So, -d option can be used only for the target file, not when scanning database But, yes, it is inetersing to know the default of -d option, anyway. adct And why am I searching on this.. My system crawled while doing adct a -B 0 level nbackup today, which I have never tried before. adct Everything was fine when it finished. you are lucky. nbackup can lock if some of fb_inet_server processes has not finished writing changed pages. You will see that your delta grows constantly. adct But the db file is 28gb having many connections and it took adct about 10minutes to complete. 10 minutes for 28gb I think is ok. You have good hardware (disks), and I'm sure that you made nbackup to another physical disk, or at least to another logical disk on your RAID 10. adct And now I'm hasitating to start the -B 1 level nbackup. adct Will it suspend every connection? no. why? adct Will it take long? In FB 2.x nbackup always scan the whole DB, for any nbackup level. So, it will take only a bit less time for -b 1, -b 2, etc. This behavior will be fixed in Firebird 3.0 (only changed pages will be scanned). adct Should I update to 2.5.2 first? yes, sure, by lot of other reasons. But not all things related to nbackup are fixed. -- Dmitry Kuzmenko, www.ib-aid.com
RE: [firebird-support] arithmetic exception, numeric overflow, or string truncation
the stored procedure to for inserting data as:
create procedure insertorg (
in_title varchar(30),
in_level smallint,
in_parent bigint)
as
declare variable insertsql varchar(90) = 'insert into organisation (title,
levels, parent) values (:ptitle, :plevel, :pparent)';
begin
if ((in_level =1 and in_parent is null)
or (in_level 1 and in_parent is not null) ) then
execute statement (insertsql) (ptitle := in_title, plevel := in_level,
pparent := in_parent );
end ^
when i try to use the procedure to insert a new record in the table i get the
following error message:
arithmetic exception, numeric overflow, or string truncation string right
truncation at procedure 'insertorg' line: 3, col: 1.
however if i issue the insert command directly eg: insert into organisation
(title,
levels, parent) values ('main dept',1,null); this works fine. (There
is a before inset trigger on the table that takes care of issuing a value for
the primary key).
What in the procedure is causing this error? I've tried the procedure without
the if then statement and still receive the same error.
Either simple or the email adding a few extra spaces: As far as I can see,
insert into organisation (title,
levels, parent) values (:ptitle, :plevel, :pparent)
contains 93 characters if spaces are counted and that cannot fit into
varchar(90).
HTH,
Set
[firebird-support] RE: Nbackup, Default I/O ON or OFF
Hi Dmitry, first thank you for your satisfying answers. I realized i had some mistakes while trying to ask, so I still have some minor things in my mind. First, as you said, it's a mistake in my question, it should be I can't see the default option for NBACKUP'S Direct I/O SETTING in my system. Does nbackup, take the behavior of firebird server's forced writes, if it's not given as a parameter? Or is it turned on by default, or off? I've used the same raid array to store the backup. Yes, it's a good idea to use an other drive. I was not expecting this kind of slow down. For first time nbackup users like me, it can be a good thing if it writes a confirmation message to the console saying, using another physical disk is strongly recommended, do you wish to continue? Just an idea. Now, I'm hasitating to start a level 1 backup, because if I start a B 1 level backup and if it takes about 6-7 minutes and slow down the operations tremendously again, they will cut my head off :)
[firebird-support] RE: Nbackup, Default I/O ON or OFF
I tried the second level backup (the incremental backup) It took about 1 minute with B 1 and now I have the hourly backup :-) That's great. As nbackup was working, there was no performance issues this time. I couldn't see any performance degradation. But I guess, B 0 level (full backups) should be used with caution if the db size is huge with many users on it and the some users like me use the same disk array for a backup! :) I'll schedule a full backup weekly on sunday nights. Thanks a lot.
Re: [firebird-support] RE: Nbackup, Default I/O ON or OFF
Hello, atunccekic! Friday, December 20, 2013, 1:27:41 PM, you wrote: adct I realized i had some mistakes while trying to ask, so I still adct have some minor things in my mind. adct First, as you said, it's a mistake in my question, it should be adct I can't see the default option for NBACKUP'S Direct I/O SETTING in my system. no, no mistake in your question, I said, that there may be a mistake in description of -d parameter for nbackup. The physics is the following (at least for Windows, don't know about Linux: - Forced Writes ON/OFF and nbackup -d - are special parameters for CreateFile. - Since file (database) is opened with some parameters, application (Firebird) need to reopen file with other parameters to get FW ON or OFF or file direct access. Since nbackup does not disconnect all users, I think it does not force Firebird to reopen database file with other parameters, because at least direct file I/O disables operating system cache for the file specified. Thus, turning database file to direct access can cause performance loss, immediately, and for the time that nbackup will scan the database. That's are my thoughts only, I haven't looked into nbackup source code. By the way, Firebird 2.5 have FileSysystemCacheThreshold parameter. If you set it to 0, FB will open databases in direct I/O mode. So, you can check it by yourself. adct Does nbackup, take the behavior of firebird server's forced adct writes, if it's not given as a parameter? Or is it turned on by default, or off? It must not, at all, at least because FW affects OS lazy write cache, and when nbackup works, Firebird only reads from DB, not writes. But, for delta file, I don't know, FW is used as for database, or OFF, etc. adct I've used the same raid array to store the backup. Yes, it's a adct good idea to use an other drive. I was not expecting this kind adct of slow down. yes, good RAID 10 can eliminate performance issue for reading and writing at one hard drive. adct For first time nbackup users like me, it can be a adct good thing if it writes a confirmation message to the console adct saying, using another physical disk is strongly recommended, do adct you wish to continue? Just an idea. well, we think that DB admin will be clever enough to check free space on the drive, if he put nbackup to the same drive where database is :-) adct Now, I'm hasitating to start a level 1 backup, because if I adct start a B 1 level backup and if it takes about 6-7 minutes and adct slow down the operations tremendously again, they will cut my head off well, -d on means direct I/O on, but anyway, nbackup will read every page in the database, so, you will get performance penalty during nbackup. Also, read comments at http://tracker.firebirdsql.org/browse/CORE-2316 -- Dmitry Kuzmenko, www.ib-aid.com
[firebird-support] Help with a query
I have two tables, for example TableA and TableB. Each row of TableA contains a foreign key to a row of TableB. This a 1 to 1 relation, so each row of TableA is linked to one different row of TableB. The foreign key field can also be NULL, in this case the TableA row is not connected to a TableB row. All TableB rows depend logically on TableA rows, so all TableB rows should be linked by a TableA row. I would like to check for program errors during development, so I would like to know if there are orphans TableB rows. May somebody suggest me a good way to find the rows in TableB that are not connected from any row in TableA? Thanks in advance Fulvio Senore ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo Groups Links * To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ * Your email settings: Individual Email | Traditional * To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) * To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com * To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com * Your use of Yahoo Groups is subject to: http://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] Help with a query
Hi Fulvio, I have two tables, for example TableA and TableB. Each row of TableA contains a foreign key to a row of TableB. This a 1 to 1 relation, so each row of TableA is linked to one different row of TableB. The foreign key field can also be NULL, in this case the TableA row is not connected to a TableB row. All TableB rows depend logically on TableA rows, so all TableB rows should be linked by a TableA row. I would like to check for program errors during development, so I would like to know if there are orphans TableB rows. May somebody suggest me a good way to find the rows in TableB that are not connected from any row in TableA? Why not reverse the link then, and make sure the foreign key field in TableB is NOT NULL? But if that's not an option, you can periodically check for orphans with select columns from TableB b where not exists (select * from TableA a where a.fk = b.target) Kind regards, Paul Vinkenoog ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo Groups Links * To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ * Your email settings: Individual Email | Traditional * To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) * To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com * To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com * Your use of Yahoo Groups is subject to: http://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] RE: Nbackup, Default I/O ON or OFF
Thank you Dmitry :) Regards
Re: [firebird-support] RE: Nbackup, Default I/O ON or OFF
Thank you Dmitry :) By the way, what they have done to yahoo in general? Things gone bad in my opinion. I can't delete any of my messages here. I can't sort my mail by sender in yahoo mail anymore...
Re: [firebird-support] RE: Nbackup, Default I/O ON or OFF
Several things have gone wrong. For instance, I can't leave this or any other groups, no matter what PC or browser I use. I get: Sorry, an error occurred while loading the content. So much for We've improved your Yahoo Groups experience. Check out what's new:
Re: [firebird-support] Help with a query
Try something like this: Select b.* from TableB b where not exists(select 1 from TableA a where a.KeyField = b.KeyField) HTH Woody (TMW) -Original Message- From: Fulvio Senore Sent: Friday, December 20, 2013 7:15 AM To: firebird-support@yahoogroups.com Subject: [firebird-support] Help with a query I have two tables, for example TableA and TableB. Each row of TableA contains a foreign key to a row of TableB. This a 1 to 1 relation, so each row of TableA is linked to one different row of TableB. The foreign key field can also be NULL, in this case the TableA row is not connected to a TableB row. All TableB rows depend logically on TableA rows, so all TableB rows should be linked by a TableA row. I would like to check for program errors during development, so I would like to know if there are orphans TableB rows. May somebody suggest me a good way to find the rows in TableB that are not connected from any row in TableA? Thanks in advance Fulvio Senore ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo Groups Links ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo Groups Links * To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ * Your email settings: Individual Email | Traditional * To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) * To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com * To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com * Your use of Yahoo Groups is subject to: http://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] Help with a query
On Fri, 20 Dec 2013 14:15:38 +0100, Fulvio Senore mail...@fsoft.it wrote: I have two tables, for example TableA and TableB. Each row of TableA contains a foreign key to a row of TableB. This a 1 to 1 relation, so each row of TableA is linked to one different row of TableB. The foreign key field can also be NULL, in this case the TableA row is not connected to a TableB row. All TableB rows depend logically on TableA rows, so all TableB rows should be linked by a TableA row. I would like to check for program errors during development, so I would like to know if there are orphans TableB rows. May somebody suggest me a good way to find the rows in TableB that are not connected from any row in TableA? To me it sounds like you have the dependencies reversed: TableB should have a foreign key to TableA, and not the other way around. Mark
Re: [firebird-support] Help with a query
Il 20/12/2013 16:32, Mark Rotteveel ha scritto: On Fri, 20 Dec 2013 14:15:38 +0100, Fulvio Senore mail...@fsoft.it wrote: I have two tables, for example TableA and TableB. Each row of TableA contains a foreign key to a row of TableB. This a 1 to 1 relation, so each row of TableA is linked to one different row of TableB. The foreign key field can also be NULL, in this case the TableA row is not connected to a TableB row. All TableB rows depend logically on TableA rows, so all TableB rows should be linked by a TableA row. I would like to check for program errors during development, so I would like to know if there are orphans TableB rows. May somebody suggest me a good way to find the rows in TableB that are not connected from any row in TableA? To me it sounds like you have the dependencies reversed: TableB should have a foreign key to TableA, and not the other way around. Many thanks to everybody who helped with my question. I was also wondering if I can find a better design, but it's good to know how to solve my problem. Fulvio Senore
[firebird-support] NBackup
Good Afternoon I'm trying to run nBackup Firebird through command line below C: \ nbackup.exe-U sysdba-P masterkey-B 0 C: \ nomedobanco.FDB C: \ nomedobackup.TMPNBK but it appears to me the following error The application failed to initialize properly (0xc07b). Click OK to close the application. What to do? Someone has gone through something similar?
Re: [firebird-support] NBackup
Hello, alessandroyamasaki! Friday, December 20, 2013, 8:16:17 PM, you wrote: agc I'm trying to run nBackup Firebird through command line below agc C: \ nbackup.exe-U sysdba-P masterkey-B 0 C: \ nomedobanco.FDB C: \ nomedobackup.TMPNBK exactly nbackup-U ? and other spaces/nospaces ? do you have access rights to the C:\ folder? -- Dmitry Kuzmenko, www.ib-aid.com
Re: [firebird-support] Stored Procedure slowing down
Hello, MarkC! Friday, December 20, 2013, 11:06:07 PM, you wrote: Mwc I have a stored procedure using a FOR SELECT loop through Mwc roughly 1,000 records, that normally runs in about 4 seconds. In Mwc one location it sometimes takes up to 30 seconds. If the Mwc firebird service is stopped and started, usually makes the Mwc procedure time drop back to around 4 seconds. However, this Mwc week, starting and stopping the service did not help. Looking for Mwc any ideas of what would cause a procedure's performance to slow down like this. record versions, garbage, etc. -- Dmitry Kuzmenko, www.ib-aid.com
Re: [firebird-support] NBackup
Hi Yes, I have access to the folder. Thank you for your attention. The problem was the quotes resolved C:\nbackup.exe -U sysdba -P masterkey-B 0 C:\nomedobanco.FDB C:\nomedobackup.TMPNBK []´s 2013/12/20 Dmitry Kuzmenko k...@ibase.ru Hello, alessandroyamasaki! Friday, December 20, 2013, 8:16:17 PM, you wrote: agc I'm trying to run nBackup Firebird through command line below agc C: \ nbackup.exe-U sysdba-P masterkey-B 0 C: \ nomedobanco.FDB C: \ nomedobackup.TMPNBK exactly nbackup-U ? and other spaces/nospaces ? do you have access rights to the C:\ folder? -- Dmitry Kuzmenko, www.ib-aid.com
Re: [firebird-support] Stored Procedure slowing down
Good Day Dmitry, Firebird version 1.5.6. Windows OS is Server 2012. I'm using FlameRobin 0.9.2.1851 to run the procedure manually. My users use a delphi 4 application. I have this implemented in a number of locations, but nowhere else have problem with this procedure slowing down Thanks for looking this over. Mark
Re[2]: [firebird-support] RE: Nbackup, Default I/O ON or OFF
Hello, atunccekic! Friday, December 20, 2013, 5:58:08 PM, you wrote: adct Thank you Dmitry I decided to refresh my memory, because started to forgot things I new before :-) So, several facts: -d ON - turns ON direct I/O, i.e. turns off operating system cache. on Windows it uses FILE_FLAG_NO_BUFFERING option (see comments below) -d OFF - turns OFF direct I/O, i.e. turns ON operating system cache. it can show bad performance when database size is bigger than RAM, at least for Windows. Turning -d ON does not spoil OS cache, so, performance left for the hard drive subsystem. So, it need to be tested wich one to use - ON or OFF. Also, as I see in nbak.cpp, -d option affects only diff file (delta), not database file. You can check functions BackupManager::openDelta BackupManager::setForcedWrites And, it inherits Forced Writes state from the database for delta. If it's ON for database, it will be ON for delta (wich is seems logical). So, the description of -d that nbackup show is wrong. As I said, it related only to the delta file. What I coudn't find (sorry, I don't understand C++ and not so friendly with Firebird sources) is defaults for -d option. But, as I see, some people use -d OFF to avoid performance problems, so, I can guess, that default is -d ON. Have I satisfied your interest? :-) -- Dmitry Kuzmenko, www.ib-aid.com
Re[2]: [firebird-support] Stored Procedure slowing down
Hello, MarkC! Saturday, December 21, 2013, 1:29:56 AM, you wrote: Mwc Good Day Dmitry, Mwc Firebird version 1.5.6. Mwc Windows OS is Server 2012. Mwc I'm using FlameRobin 0.9.2.1851 to run the procedure manually. Mwc My users use a delphi 4 application. I have this implemented in Mwc a number of locations, but nowhere else have problem with this procedure slowing down check database at that location with gstat -r ... and look at record versions info of the tables the procedure works with. -- Dmitry Kuzmenko, www.ib-aid.com
RE: Re[2]: [firebird-support] Stored Procedure slowing down
Hi, There are 4 tables used in the sp. Two of them with version info of 0. The other 2 look like this: Primary table: Average version length: 9.08, total versions: 13781, max versions: 15 Secondary table: Average version length: 25.92, total versions: 13, max versions: 2 Thanks, Mark
[firebird-support] Re: Nbackup, Default I/O ON or OFF
21.12.2013 01:32, Dmitry Kuzmenko wrote: I decided to refresh my memory, because started to forgot things I new before :-) So, several facts: -d ON - turns ON direct I/O, i.e. turns off operating system cache. on Windows it uses FILE_FLAG_NO_BUFFERING option (see comments below) -d OFF - turns OFF direct I/O, i.e. turns ON operating system cache. it can show bad performance when database size is bigger than RAM, at least for Windows. Turning -d ON does not spoil OS cache, so, performance left for the hard drive subsystem. So, it need to be tested wich one to use - ON or OFF. Correct so far. Also, as I see in nbak.cpp, -d option affects only diff file (delta), not database file. You can check functions BackupManager::openDelta BackupManager::setForcedWrites Wrong. The -D switch has nothing to do with the BackupManager, so it cannot affect delta at all. You should be looking at nbackup.cpp (the utility, not the engine) and see that it affects how the database file is being read by nbackup after it has been locked. And, it inherits Forced Writes state from the database for delta. If it's ON for database, it will be ON for delta (wich is seems logical). True, but it's about ForcedWrites, not the -D switch. What I coudn't find (sorry, I don't understand C++ and not so friendly with Firebird sources) is defaults for -d option. The default is ON on Windows and OFF on POSIX. Dmitry
