Re[2]: [firebird-support] Deadlock
Hello, dixonepperson! Monday, March 17, 2014, 6:45:55 PM, you wrote: dyc That raises three questions: dyc 1) Should I put a WAIT in the procedure with a LOCK TIMEOUT? depends of how you want to handle update/delete conflicts. dyc 2) Would it be better to a START TRANSACTION / COMMIT inside the dyc procedure as opposed to putting the transaction control in the FbClient? you can't do it, at all. dyc 3) If I put it in the FbClient, what are the options I should dyc choose? Would it be better to put it on the FbCommand or the FbConnection dyc read about IB/FB transaction parameters. -- Dmitry Kuzmenko, www.ib-aid.com
Re[4]: [firebird-support] Deadlock
Hello, Mark! Monday, March 17, 2014, 7:30:16 PM, you wrote: you can't do it, at all. MR No, but you can of course use IN AUTONOMOUS TRANSACTION within the stored MR procedure. See Mark! :-) By the question it is clear that author have very sketchy knowledge about transactions (if any). So, I decided not to speak about autonomous transactions at all, because it can lead to misused feature situation. (at least because autonomous have same paramaters that have parent transaction, so, wait here can double problems). p.s. Sorry, Dixon. -- Dmitry Kuzmenko, www.ib-aid.com
Re: [firebird-support] The difference in queries
Hello, Lukasz! Monday, March 10, 2014, 12:33:13 AM, you wrote: LB Hi, LB Inquiries made ??at isolation level SERIALIZABLE are automatically blocked LB So, for example, by performing a SQL query : LB I have done automatically blockade record , right? SERIALIZABLE do not lock records, it locks tables, completely. So, your question seems to be strange, at least. -- Dmitry Kuzmenko, www.ib-aid.com
Re[2]: [firebird-support] Readcomitted ReadOnly Transaction - Commit or Rollback?
Hello, Ralf! Thursday, February 27, 2014, 2:08:00 PM, you wrote: start and commit/rollback of any transaction will write at least header page and transaction inventory page (s). The exception is read-only database. RJ I assume the transaction inventory page(s) gets written multiple times for a transaction? RJ Something like setting active, setting commit/rollback, deleting from page? I don't know all the details of FB sources (I hate C/C++, sorry), but yes, when new transaction starts TIP is extended, and if it is new TIP page, so, there are more writes, than one (pointer to the next TIP page, etc). But no deleting. TIP is always increasing and updating only, because TIP stores transactions state and nothing more. And it grows up to MaxInt transactions (in FB3 - unsigned int, so, 2x more). BTW, about read-only read-committed transactions - they intended to be long, and, starting and committing lot of such transactions seems useless. If you have lot of clients that do some reading from time to time, maybe it's better to lower server load by using some middleware, which will have connection pool with long-running read_only read-committed transactions. This is just a note, of course, I don't know architecture of your system. -- Dmitry Kuzmenko, www.ib-aid.com
Re: [firebird-support] Change page size from 8k to 16k?
Hello, Kjell! Friday, February 28, 2014, 3:23:34 AM, you wrote: KR I'm going to do a gbak backup/restore on my 100 Gbyte database this KR weekend, and was wondering if perhaps I should bump up the page size KR from 8 kbyte to 16 kbyte. KR What should I look for? KR I'm running gstat -a -i right now. Is this the appropriate options to KR get the relevant figures? No. The best way, always, minimum gsta -h, or maximum gstat -r. Only gstat -r shows record size, versions, etc. You should look at indices, that have depth greater than 3 (equal to 4 or more). Anyway, you may always compare performance. 100gb is not such a big database that could not be set to 8k back at appropriate time if you will find that something become slower at 16k. -- Dmitry Kuzmenko, www.ib-aid.com
Re: [firebird-support] Find all records on all tables that violate constraints?
Hello, Kjell! Friday, February 28, 2014, 3:29:34 AM, you wrote: KR Hi, KR It seems I've been a bit sloppy when introducing new constraints in my KR database. At least I found one table with a record that has null in a KR column that I've altered to not null. KR So, is there any nice convenient way to find all such records in all KR tables? I'm thinking an execute statement that iterates system tables to KR find all not null columns and select where XXX is null on each such KR table and column...? I think that would suffice in this case, KR considering the set of constraints I have. I'm sorry, maybe you will not like my answer, but when I wanted to find not null columns that have nulls, I wrote simple application, with cycle for each table executing query searching for nulls in not null columns (where field is null) by simple scanning records with non-buffering query component (I did it with IBX.TIBSQL). I know that it work long, but anyway, the task is to find record with null that must not have null, so, scanning all records for the whole database is necessary measure. Thus, if you expert in execute statement - write it. Otherwise write plain program with serveral simple for/while cycles. My old app is about 70 lines in Delphi, and it is too big to post it here. If you want me to be ashamed for the old crappy code, send me private message, I will give you link to exe and pas sources. :-) -- Dmitry Kuzmenko, www.ib-aid.com
Re: [firebird-support] Find all records on all tables that violate constraints?
Hello, Kjell! Friday, February 28, 2014, 3:29:34 AM, you wrote: KR It seems I've been a bit sloppy when introducing new constraints in my KR database. At least I found one table with a record that has null in a KR column that I've altered to not null. KR So, is there any nice convenient way to find all such records in all KR tables? Well, I have an idea that can make huge speedup of the whole database scanning process - you should check only those tables, that have rdb$format 1, because tables, that have rdb$format = 1 was not altered since last restore or their first creation. -- Dmitry Kuzmenko, www.ib-aid.com
Re: [firebird-support] Readcomitted ReadOnly Transaction - Commit or Rollback?
Hello, Ralf! Wednesday, February 26, 2014, 4:15:33 PM, you wrote: RJ when ending a ReadCommited ReadOnly Transaction and releasing RJ resources what's considered Best Practice? Commit or Rollback. And RJ if someone can elaborate and why it would be welcome. simple question. if there were no changes made inside read/write transaction, engine will convert Rollback to Commit, since there are no reason to do silly work. :-) (the engine does this also when there were not much changes made before Rollback - it undo changes and makes Commit. Only if there were huge amount of changes, which server can't remember, Rollback becomes Rollback). And, read only readcommitted transaction is committed (or pre-committed) at it's start, so, Rollback or Commit for that transaction are the same, i.e. only freeing transaction handle. From my point of view, Rollback for read only read committed transaction is a nonsense, in any meaning. RJ When monitoring the system via the Windows PerformanceCounters i RJ see writes to the database. In my test environment when having RJ around 400 Readonly Transactions per second (and nothing else) it RJ sums up to around 2.5MB per second. start and commit/rollback of any transaction will write at least header page and transaction inventory page (s). The exception is read-only database. RJ Looking at Mon$Transactions at the same time shows 0 RJ Page_Writes/Page_Marks. this is about work done inside transaction, not by transaction itself. The burth and death of transaction is being counted by attachment, not by transaction. -- Dmitry Kuzmenko, www.ib-aid.com
Re: [firebird-support] Trigger not working
Hello, Werner! Saturday, February 22, 2014, 9:31:19 PM, you wrote: W I have the following trigger which doesn't work for me, i.e. the columns W in winerackit are never updated by it. W In my current test case the 'fk_winerackit_id' column is set to Null, W so I would expect this trigger to fire and set the 'usedcapacity' column W to '0' as it was '1' before the update to the bottag table. W CREATE OR ALTER trigger bottag_biud0 for bottag W active after insert or update or delete position 0 Wif (old.fk_winerackit_id is not Null) then Wif (new.fk_winerackit_id is not Null) then maybe that ? http://www.firebirdsql.org/refdocs/langrefupd21-ddl-trigger.html In multi-action triggers, both context variables OLD and NEW are always available. If you use them in the wrong situation (i.e. OLD while inserting or NEW while deleting), the following happens: - If you try to read their field values, NULL is returned. - If you try to assign values to them, a runtime exception is thrown. I think, when using new/old in multi-action triggers you should directly use context variables INSERTING, UPDATING and DELETING to avoid ambiguity of the code. -- Dmitry Kuzmenko, www.ib-aid.com
Re: [firebird-support] Firebird 2.5 under Windows 8 64bit
Hello, Dirk! Thursday, February 20, 2014, 4:03:58 PM, you wrote: DS The following errors are repeatedly found in the firebird.log DS operating system directive CreateFile failed DS Access denied first check what antivirus software or proxy is running on that server. Any soft that intercepts file operations, network operations, etc, can affect Firebird working with DB or clients via network. -- Dmitry Kuzmenko, www.ib-aid.com
Re: [firebird-support] Required information for Firebird database performance optimization
Hello, Alexey! Tuesday, February 18, 2014, 10:10:09 PM, you wrote: AK I am a bit tired of repeated topics regarding database performance, so I AK decided to publish short version of our performance questionnaire: AK http://ib-aid.com/articles/item157 These questios are also useful becase even just reading them can give an idea where the problem is or what to check in the system. For examle, we ask Is OS running as Virtual Machine instance? because, sure, any RDBMS under virtial machine will work slower than on plain hardware. :-) Of course, not that simple, but ... -- Dmitry Kuzmenko, www.ib-aid.com
Re: [firebird-support] Udf returning VARCHAR
Hello, liviuslivius! Friday, January 31, 2014, 12:21:37 PM, you wrote: l i try to find any example of writing and declaring udf function l returning varchar instead of Char l without success. l Is this possible? yes, but correct way is to use CSTRING. It can be simply used inside udf, and that udf will be 100% compatible with any outside CHAR/VARCHAR variables, fields or literals. -- Dmitry Kuzmenko, www.ib-aid.com
Re[2]: [firebird-support] Multiple Embedded Connections
Hello, Paul, Alan! PV Windows Embedded now contains a SuperClassic instead of a SuperServer engine. PV File locks are shared, so a database can be accessed by one or more Embedded PV servers and a regular Classic or SuperClassic server at the same time. PV Consult the Firebird 2.5 Release Notes for full details. Yes, but this provocates some people to use embedded for multi-tier middleware, where definitely server (not embedded) must be used. AM It appears that updates from one process are visible to the other(s) but I'm AM not sure how the second would know if a transaction is being managedin the AM first - is there a lock file being written to somewhere that I can't find? I need to note that Embedded Firebird is a dll that works in an address space of it's caller (exe). Thus EXE itself becomes server. And if that server have bugs, chances to get broken database are much higher, especially if you want to use multiple exe+embedded on one DB. -- Dmitry Kuzmenko, www.ib-aid.com
Re: [firebird-support] Outdated indices
Hello, Evelyne! Thursday, January 9, 2014, 8:57:13 PM, you wrote: EG The problem is that the index seems to keep having old values EG until there is not a single connection to the database (even if I EG commit every transaction). This causes me real performance EG problems on 24/7 systems ... some query wich ran in less than a second now EG take a minute to go. your problem is versions, because index keep all keys for all versions. If you have active transactions, garbage collection does not work, and obsolete keys and versions are not cleaned. Check gstat -r to see how many versions tables have when you have performance problems. -- Dmitry Kuzmenko, www.ib-aid.com
Re: [firebird-support] Re: Nbackup, Default I/O ON or OFF
Hello, Dmitry! Saturday, December 21, 2013, 8:27:26 AM, you wrote: 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 DY Wrong. The -D switch has nothing to do with the BackupManager, so it DY cannot affect delta at all. oops, yes, I was wrong, missed between nbk and delta. Of course -d option related to resulting nbk file. DY You should be looking at nbackup.cpp (the utility, not the engine) and DY see that it affects how the database file is being read by nbackup after DY it has been locked. Wish I knew where that sources are. I see only nbackup.cpp in JRD, not in utilities/nbackup. Be patient, I'm bad with C/C++. 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). DY True, but it's about ForcedWrites, not the -D switch. here is Delta inherits FW settings, not nbk, because for nbk FW is useless, as I understand. What I coudn't find (sorry, I don't understand C++ and not so friendly with Firebird sources) is defaults for -d option. DY The default is ON on Windows and OFF on POSIX. Nice. That usually frightens me. -- Dmitry Kuzmenko, www.ib-aid.com
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] 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
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[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: [firebird-support] RE: I dont know why sweep is not doing clean
Hello, sopranoeli! Friday, December 6, 2013, 11:44:31 PM, you wrote: syca I do not mind the difference between Oldest active and Next syca transaction. why? you have some transaction (not read only read_committed) running for 2 days. Why auto-sweep is not running - this is another story. Have you looked into firebird.log that auto-sweep is really never run? -- Dmitry Kuzmenko, www.ib-aid.com
Re: [firebird-support] Installing Firebird manually in Windows
Hello, alberto! Monday, November 11, 2013, 10:40:16 PM, you wrote: a I installed firebird manually using instreg and instsvc as you said, but a I have a problem using a rfunc.dll (put it into udf folder). a I´m not sure about if the problem is 32bit-version of firebird into a a 64bit-Windows. If I use 32bit installation with setup, everything is ok a but if I install using a command line with zip version I have problems with the dll because a firebird can´t find functions inside from this dll. Any solution? Thanks well. Be sure that you unzipped subfolders, i.e. in directory where you placed FB you have bin, udf, and other subfolders. rfunc.dll may (I don't remember) want ib_util.dll. check it at UDF subfolder. You may also try to copy ib_util.dll into Firebird/bin subfolder. Then, rfunc also may want fbclient.dll. I mean, udf dll can have dependencies. You may find them using ProcessMonitor from Microsoft. Watch fbserver.exe or fb_inet_server.exe (I don't know what you use) and try to call select with your function. At that moment (only first time) FB will try to find and load declared module. Declared module may try to find and load other modules. Look for error, wrong path, non-existing file, etc. -- Dmitry Kuzmenko, www.ib-aid.com
Re[3]: [firebird-support] Unable to get Firebird ODBC driver working in 64-bit
Hello, bandersnoot! Sunday, November 10, 2013, 11:52:56 PM, you wrote: bgc What was screwing me up was that the server already had a bgc 32-bit client installed. So I was never going to get a 64-bit bgc client working with 64-bit perl. 64bit app - 64bit ODBC - 64bit firebird client - network - 32/64 bit Firebird 32bit app - 32bit ODBC - 32bit firebird client - network - 32/64 bit Firebird So, everything in the line up to network must have same capacity. Moreover, by defaul 64bit Windows have CPL for 32-bit ODBC. So, you need to run proper odbcad32.exe (yes, again stupid name). And, Firebird ODBC looks for fbclient.dll, not for gds32.dll. Sad to say, but you cannot find what bitness have fbclient.dll without special programs. And, the final - do not copy fbclient.dll to gds32.dll. Use instclient. -- Dmitry Kuzmenko, www.ib-aid.com
Re: [firebird-support] Passing a comma separate string to a Procedure
Hello, firebirddev! Monday, November 11, 2013, 2:34:01 AM, you wrote: ff In the procedure I am using the IN operation, like: ff WHERE NAME_LIST in (:I_NAME_LIST); parameter can contain ony one value. FB does not have any idea about what you put into the string, and wich separator you will use in it, and so on. Thus, construct NAME_LIST in (:I_NAME_LIST) will not work, because it will use contents of :I_NAME_LIST as one value. Instead, for example, you may pass string '~12~23~267~675~' where ~ is a delimiter. So, query will be Select ... From Table T Where :ParamStr Containing '~'||T.ID||'~' result will show all rows with T.ID equal to 12, 23, 267 and 675. -- Dmitry Kuzmenko, www.ib-aid.com
Re[4]: [firebird-support] Restore error during unique index creation
Hello, Bob! Sunday, October 13, 2013, 8:21:12 AM, you wrote: BM Do you have any other tips for getting the best performance during backup BM and restore? Our current situation is a backup of the production database BM on server1 intiated on server2, with the backup file residing on server2. BM Server2 initiates the restore (using service manager), with the database BM being stored on the same disk array as the backup file. BM A 113GB production database produces a backup file of 99GB, and a newly BM restored database of 108GB. This entire process takes almost 48 hours. oops. As I know (from our test), backup and restore are disk bound, and does not depend on Firebird cache size or db page size, etc. First, yes, the fastest way to backup and restore is to use Services API (gbak -se ...). On Linux same results will be using local protocol (db name and path without server name). Second, you need to have all timings to understand how much time these processes takes. I mean backup restore restore with -i. Usually restore takes 3-5 times longer than backup. Third, when making backup over network (as I understood), you need to be sure that network is faster than making backup to disk. Otherwise you are wasting time. restore with -i option is needed to understand: - how fast data is being restored. I can't find data right now, but seems that this must be close to the backup time. - how fast/slow indices are being created during restore. Here maybe you need to reconfigure TEMP settings (point it to fast disk), to check (and maybe delete) indices with very bad selectivity, etc. And, the last one - to compare this with other results. For example, one system I have data right now makes backup 30 minutes and restore 4 hours on 36gb database. These numbers are for single-user mode, and worse for online - backup takes 2 hours. Something like this. :-) -- Dmitry Kuzmenko, www.ib-aid.com
Re[2]: [firebird-support] Restore error during unique index creation
Hello, Thomas! Sunday, October 13, 2013, 11:41:45 AM, you wrote: TS wish. Also -g for suppressing garbage collection in the production TS database might be an option if it is all about backup speed. +1 to that. Moreover, I think that for production system using gbak -b without -g is NOT an option :-) -- Dmitry Kuzmenko, www.ib-aid.com
Re[2]: [firebird-support] Restore error during unique index creation
Hello, Thomas! Sunday, October 13, 2013, 11:32:13 AM, you wrote: TS With a scale 1 TPC-H backup database, I can reduce restore time from TS 7min 58sec to 5min 57sec by using 16384 buffers instead of 1024. Firing TS Perhaps you become CPU bound in your tests more quicker than I did. well, seems you are right. TS If you have a look on a thread in firebird-devel started on Sept. 10, TS 2012, where I was mentioning InterBase XE3 got improved by parallel TS index creation at restore. Sounds like a good move in InterBase XE3, TS although I haven't tried that enhancement. hmm, I tried that feature, and found that it can increase performance only when disk subsystem have huge IO capabilities, like SSD. Testing that at least not on good RAID 10 will be total waste of time. In my case, disk was the stopper, for any number of assistant threads, from 1 to 6 (cores I have). Maybe improvement can be seen on disks with 600-1000 IOPS and more. Strange, but Embarcadero must give positive examples about this feature, but it's not. Instead we have only this: This helps the restore response time where there are idle threads/cores that facilitate the engine doing the job that much quicker by benefiting from sharing the cache for table data. -- Dmitry Kuzmenko, www.ib-aid.com
Re[4]: [firebird-support] Restore error during unique index creation
Hello, Bob! BTW, client gave his timings: 160gb database backup - 1 hour restore - 11 hours, because of long indices with few unique values. HDDs - Dell md3220 with 12 SAS drives, RAID 10. This is not for backup/restore speed, but for performance, because they have ~400-500 active users (simultaneous). -- Dmitry Kuzmenko, www.ib-aid.com
Re: [firebird-support] RE: oldest active transaction not available
Hello, malte! Monday, October 14, 2013, 12:22:00 PM, you wrote: mtec the indices are used, but only the server knows why he scans the whole table. mtec Statement Statistics: Reads: 1,0 GB Inserts: 25 Indexed Reads: mtec 25 Writes: 180,0 KB Updates: 0 Sequential Reads: 8.259.025 mtec Deletes: 25 Table Scan: 100% mtec mtec btw: after backup and restore the behavior is ok, but that can't be the solution... Garbage? Visit seminar about transaction control in applications at Siegburg (22 Nov) or Prague (25 Nov). -- Dmitry Kuzmenko, www.ib-aid.com
Re: [firebird-support] Computed index
Hello, Tim! Wednesday, October 9, 2013, 3:29:09 PM, you wrote: TW I've got a query containing TW ... WHERE ... (MYFIELD in (1,3,4,5,7,8,9,11,12,13)) TW which runs horribly slowly, because (quite reasonably) the query use myfield+0 in (1,3,4,5,7,8,9,11,12,13) so, it will not use index on myfield, and other indices can give better performance. -- Dmitry Kuzmenko, www.ib-aid.com
Re: Odp: [firebird-support] Computed index
Hello, liviuslivius! Wednesday, October 9, 2013, 4:35:50 PM, you wrote: lpop First try using or not in in your query hmm, what do you mean? where field in (1,2) is totally equivalent to where (field = 1) or (field = 2) so, or or in is not different here. -- Dmitry Kuzmenko, www.ib-aid.com
Re[2]: [firebird-support] oldest active transaction not available
Hello! Wednesday, October 9, 2013, 10:57:53 PM, you wrote: I can't find the oldest active transaction in mon$transactions and sweep has no effect. Even Sinatica Monitor and IBExpert doesn't show this number. How can I rollback this transaction? oldest active transaction is the oldest one in mon$transactions. Also you may look into mon$database for it's number. IP oldest active transaction does not mean oldest of currently running transactions, IP but oldest transaction that was active when some other currently active transaction started. You described Oldest Snapshot transaction, not Oldest Active. OAT is a really oldest currently active transaction. OST - is oldest transaction, that was active when oldest active started, yes, and it does not exist anymore in mon$transactions. -- Dmitry Kuzmenko, www.ib-aid.com
Re[2]: [firebird-support] Drawbacks of high LockHashSlots value
Hello, fabianoaspro! Tuesday, September 3, 2013, 11:15:45 PM, you wrote: fgc So what is the problem with changing this to 10009 by example? What is the fgc drawback? none found. I think that Fabiano wanted to know about any drawbacks, but ... here is only profit :-) -- Dmitry Kuzmenko, www.ib-aid.com
Re[2]: [firebird-support] I can move Firebird database
Hello, Jorge! Monday, August 12, 2013, 8:54:17 PM, you wrote: JAB http://tkb.amano.com/phpkb/article.php?id=181 could help. really? This piece of auto-generated crap can help? You read the description of that solution? :-) In the case of moving db+secondary files the only HEX editor, or old tool named GLink can help. fortunately I saved it at my site long time ago http://www.ibase.ru/download/glink.zip Marco, if you will use glink, first test it on COPIES of your DB and all secondary files. I even suggest to do it on another computer, to be sure that glink by mistake will not touch any original database files. -- Dmitry Kuzmenko, www.ib-aid.com
Re[2]: [firebird-support] big change after restore
Hello, Nick! Tuesday, July 30, 2013, 12:37:09 PM, you wrote: NU I am running classic firebird 2.1, but I don't think its GC as I ran both a NU sweep and a backup and still got the same, longer, time to be sure that this is not GC, you may - after inserting and updating data run gstat -r or IBAnalyst here you can see, are there record versions, or not - after running sweep run gstat -r or reload stats in IBAnalyst here you can see, was sweep or GC effective (i.e. less versions or not) - after restore run gstat -r or reload stats in IBAnalyst here you can compare size of your table, and see was it fragmented or not -- Dmitry Kuzmenko, www.ib-aid.com
Re[2]: [firebird-support] big change after restore
Hello, Nick! Tuesday, July 30, 2013, 1:41:21 PM, you wrote: NU gstat -r output: NU pre backup/restore you again checked select * from table at that moment? and it is 4.8 sec? NU post backup/restore and here - 0.3 sec? NU I obviously expect some improvement from backup/restore but 15x seems more NU than can be accounted for I doubt that your measures are correct. Since I see 130k records, I think that 4.8 seconds of select * from table takes FetchAll, i.e. transferring all records to client application. And, 0.3 seconds for the same query on 130k records means that FetchAll is not in effect, ony some parts of records are being read. Or, (which is a bit suspicious), your hard drive is very slow at random reads, if at first case this table pages were fragmented in the DB. -- Dmitry Kuzmenko, www.ib-aid.com
Re: [firebird-support] Re: How to maximize cpu usage for Firebird?
Hello, trskopo! Thursday, July 18, 2013, 10:39:04 AM, you wrote: t I have only 1 database in this machine. You said SuperClassic use 1 core per database connection. t For 1 connection I got cpu's utilization about 16%, so, if there t are 8 connection to the same database, running the same task, cpu's utilization will t jump to 100%? Classic and SuperClassic uses cores for multiple users, SuperServer - use only one core. But, if you use Classic or Superclassic, it does not guarantee that you will have 100% CPU load. RDBMS is not a calculator to load only CPU. RDBMS mostly cracks data. And data is on disk or in memory. So, if you have bad disk or memory I/O, CPU will mostly lower than 100%, because CPU load depends on what Firbird engine is doing to execute your query. -- Dmitry Kuzmenko, www.ib-aid.com
Re: [firebird-support] How to use arrays?
Hello, W! Monday, July 1, 2013, 12:15:21 AM, you wrote: WO Hello WO Somebody knows how to use arrays? do not use arrays, ever. Arrays are stored inside blobs, so, it's simpier to use blob for your client array data. Because the only thing you can do in SQL/DML with arrays is selecting array element. Insert and update you can do only at client side, writing array as a blob. Or, you may use embedded sql preprocessor gpre, but I think you will not like that idea :-) But, if you are just curious, check procedures in employee.fdb and InterBase's documentation. p.s. someone thinks that arrays are better than blobs, because they can update only part of array, not the whole array. But, this is not true - new array is written the whole, same as if you will try to modify 1 byte in 1mb blob - 1 mb will be written. -- Dmitry Kuzmenko, www.ib-aid.com
Re: [firebird-support] Re: problem for restore FB2.1 backup in FB2.5
Hello, Maziar! Friday, June 28, 2013, 5:45:38 PM, you wrote: M but how fix it ? M I think only i use ascii charset in metadata but my data use unicode_fss extract metadata script from old db (isql -x). check script for all literals, comments, etc for their charset create new db from corrected script. transfer your data from old db to new db. -- Dmitry Kuzmenko, www.ib-aid.com
Re[2]: [firebird-support] Incremental Backup
Hello, Hugo! Thursday, June 27, 2013, 1:28:18 AM, you wrote: HE My database size: 4 GB I doubt to use nbackup for that size of database. because usual gbak -b -g must take around 5 minutes, not more. Restore of that db also must take around 15 minutes. And, the timing I mention is for cheap SATA II drives (1 for db and 1 for backup), not for cool RAID storage, or even for SSD - there usual backup and restore speed will be like lightning. You may start to think about nbackup when your db will reach ~20gb size. HE Could you help me with the nbackup? HE What the best use? Keys, frequency, etc... HE How to restore? read http://www.firebirdsql.org/manual//nbackup.html as many times, until you will understand it. nbackup -b 0 is kinda file copy, with the near same speed nbackup with higher levels will read whole db, but write only changes, so, it is faster. But, if you use Classic, consider not to use nbackup with Firebird less than 2.5.2 (or even 2.5.3). -- Dmitry Kuzmenko, www.ib-aid.com
Re: [firebird-support] GBak Backup Restore Problem
Hello, Tupy...! Tuesday, June 25, 2013, 11:23:47 PM, you wrote: Tn Dear Friends, Tn We have a table with 60 rows where a not null type SmallInt column has 0 (zero) as content. Tn When restoring, GBak returns null for this column. As this column Tn is not null, we get an error message (Error: Validation error for column .). Tn Between these two steps (backup and restore), no further action is done with the backup file. Tn I ask you = What can cause this problem? Is there any solution Tn for this? Any idea about this problem? 1. somebody altered column to not null default 0 while column had null data 2. somebody added column not null default 0. solution - update that column to 0 like this update table set field = 0 p.s. gbak backups data, it does not check correspondence of data and constraints. If it is broken (somehow, by db corruption or by actions I mentioned), restore will show that column data does not correspond to it's check. -- Dmitry Kuzmenko, www.ib-aid.com
Re[2]: [firebird-support] GBak Backup Restore Problem
Hello, Tupy...! Wednesday, June 26, 2013, 12:07:07 AM, you wrote: Tn backup and then the restore. This mean = all the conditions were Tn assured that the DB were in good conditions and, during the backup/restore process, the Tn data at this column was lost. The funny thing is that gbak is not a rocket sience, it simply does select * from table and writes the data to the backup file. So, if you have problem, the source is in the database or in the backup. I also do not see what Firebird version do you use. p.s. please, do not overquote. -- Dmitry Kuzmenko, www.ib-aid.com
Re[4]: [firebird-support] GBak Backup Restore Problem
Hello, Tupy...! Wednesday, June 26, 2013, 12:35:34 AM, you wrote: Tn I will not repeat (ooops, I am already in the way !) that the Tn source DB (for the backup) is ok. currently no evidence that it is ok :-) Tn GBak ! This mean = or the problem happens at the backup process, or during the restore Tn process. usually process is - backup see data, store it - restore creates metadata, restores data, data does not fit to constraints - error. Tn But as we have no tool to see the backup content, it´s Tn not possible to know where It's possible. Tool name is IBBackupSurgeon. You can dig in that direction, too. Tn We experimented to restore first only the DB structure and then Tn restore the data, and then we got no error. Using the same backup file. You can't do that. You can restore metadata+data or only metadata. Gbak is unable to restore data into existing database. So, please explain what you are doing. Tn We have FB 2.1. this is misty. I know 5 versions of 2.1. 2.1.0, 2.1.1, 2.1.2... and all of them up to 2.1.4 have bugfixes. -- Dmitry Kuzmenko, www.ib-aid.com
Re: [firebird-support] How SET AUTODDL works?
Hello, W! Sunday, June 23, 2013, 9:47:54 PM, you wrote: WO I usually write a COMMIT or a ROLLBACK when want to finish a transaction. WO However in the documentation of ISQL said that SET AUTODDL ON finish the WO transaction with a COMMIT. remember that ISQL is just a program, that can be rewritten by anyone, so, you may design your own SET AUDODDL behavior. WO SET AUTODDL ON; WO INSERT INTO MyTable (Column1, Column2) VALUES (Value1, Value2); WO QUIT; WO When I return to ISQL the table has not recorded the INSERT. Of course, if WO I write EXIT instead of QUIT the INSERT is recorded but that is the WO standard behaviour of ISQL. Yes, QUIT = ROLLBACK, EXIT = COMMIT. And, consider that AUTODDL ON forces commit before NEXT sql statement. So, the code here may be like that: OnNewStatement if InTransaction and AutoDDL then Commit; ExecuteNewStatement; -- Dmitry Kuzmenko, www.ib-aid.com
Re[2]: [firebird-support] How SET AUTODDL works?
Hello, W! Sunday, June 23, 2013, 10:26:17 PM, you wrote: WO I had tried it and still doesnt work: WO SET AUTODDL ON; WO INSERT INTO MyTable (Column1, Column2) VALUES (Value1, Value2); WO INSERT INTO MyTable (Column1, Column2) VALUES (OtherValue1, OtherValue2); WO QUIT; WO And when I return to ISQL and write SELECT * FROM MyTable the two previous WO inserts are not there. well, this is not DDL. DDL is CREATE/ALTER/DROP, etc. INSERT/DELETE/UPDATE/EXECUTE is DML, not DDL. -- Dmitry Kuzmenko, www.ib-aid.com
Re: [firebird-support] Aliases and Windows 7
Hello, Walter! Thursday, May 30, 2013, 3:20:13 PM, you wrote: W I had to use a Windows 7 machine as Firebird server...after W updating firewall rules everything is ok, but Firebird seems to ignore the aliases.conf W file...I place the fdb file in a shared folder, with full access W granted to everyone, and create the alias. why, why, why?!?!? :-) Firebird isn't a file server, it's a client-server technology. Nobody needs to have shared access to database file to access data. Only Firebird needs access to it. So, no shared folders, files, etc. W I can access the database locally providing W the full path, by if I try to use the alias I got this error: W Unsuccessful execution caused by a system error that precludes W successful execution of subsequent statements. W I/O error during CreateFile (open) operation for file XFILES. W Error while trying to open file. sure, this is CORRECT. And, you did wrong adding shared folder to aliases.conf. -- Dmitry Kuzmenko, www.ib-aid.com
Re[2]: [firebird-support] Re: Aliases and Windows 7
Hello, Norman! Thursday, May 30, 2013, 7:06:36 PM, you wrote: ND Also, it shouldn't make a difference, but is this a 32 or 64 bit Windows ND 7 installation? And have you installed the corresponding bit size of ND Firebird? Norman, if you speak about 32/64 bit correspondence, explain it. Not to me, but to person who you ask about it. Usually most people doesn't understand 32/64 at all. But, at least for client software there no difference of the server bitness. Marcus Bajohr - respect for the list of no-go. -- Dmitry Kuzmenko, www.ib-aid.com
Re: [firebird-support] Unable to INSERT INTO TABLE
Hello, Allstars!
Wednesday, May 15, 2013, 9:42:24 PM, you wrote:
A I am fairly new to FIREBIRD SQL. I am using WINSQL as my database
management system.
as I see, WINSQL does not support InterBase or Firebird directly, so,
you work through ODBC, which is not good.
A It creates the table and says (0) rows affected.
have you created table by provided SQL statement, or used some kind
of wizard, like enter table name, add field, ...?
A Fine. I look at
A the database explorer and the table is there.
have you tried to insert data into this table? Just 1 record?
A When i try to insert into the table, it says that the table is
A unknown.
you need to check the name of the table. As I asked before,
when you enter
create table allBillOfMaterials(...
Firebird translates table name to uppercase and then you will see
ALLBILLOFMATERIALS, not allBillOfMaterials in the DB.
If you see allBillOfMaterials, then you created table by wizard
(or something), and it used name as
create table allBillOfMaterials(...
If yes, allBillOfMaterials allBillOfMaterials.
If name have mix of upper and lower characters, you MUST doublequote
it. If you will make mistake in case of any symbol, server will
say that table is unknown.
I suggest you to drop this table and create it again, as
create table ... as you wrote in your question.
FB will convert names to uppercase, and you will never have that
case sEnSiTiViTy problem.
A The id(pk) should be created automatically I'm thinking.
Nope. Firebird does not have autoicrement fields. PK is not an
autoincrement. You need to create trigger to fill PK with the
generator (sequence), if you do not pass PK value into insert
statement.
A insert into allBillOfMaterials('', totalTitles, bomNumber, bomDescription)
A select distinct
A count(bom.num) as Total
A ,bom.num as BOM Number
A ,bom.description as BOM Description
A from bom
A inner join bomitem on bomitem.bomid = bom.id
A inner join part on part.id = bomitem.partid
A inner join bomitemtype on bomitemtype.id = bomitem.typeid
A inner join customset on customset.recordid = part.id
A where bomitem.typeid = 20
A and customset.info = 'Book'
A group by bom.num, bom.description
A order by bom.description
A
A ++
A Visit http://www.firebirdsql.org and click the Resources item
A on the main (top) menu. Try Knowledgebase and FAQ links !
A Also search the knowledgebases at http://www.ibphoenix.com
A ++
A Yahoo! Groups Links
--
Dmitry Kuzmenko, www.ib-aid.com
Re[2]: [firebird-support] Re: use of -USE_ALL_SPACE
Hello, Nick! Tuesday, May 7, 2013, 12:34:57 PM, you wrote: NU As I understand this now, restoring the database with use_all_space will NU store the data as compactly as possible, then I do gfix -use reserve and NU all subsequent changes will use the 80/20 rules for where the data goes sure, you're right. -- Dmitry Kuzmenko, www.ib-aid.com
Re[2]: [firebird-support] Can't access MON$ tables
Hello, Michael! Monday, May 6, 2013, 12:05:03 AM, you wrote: MDS thing is, I downloaded the latest versions of Firebird and FlameRobin last MDS night and created a new database. Before I did that I removed an older MDS version (2.1, IIRC). Would there have been stuff left behind that tells FB MDS what ODS version to use? mmm, what? ODS is a database file format. Each FB version can create DB only in it's own format, and can work with existing db's with the older format. MDS There were a couple of the old databases still MDS registered to FlameRobin and of course the new version was aware of them. old database have old ODS. nothing can change it. You may only backup this db and restore with new FB, but this means that DB will be created as new, with new format, and data will be moved from backup to new DB (with the new ODS). MDS Anyway, I unregistered the server and the databases, then reregistered the MDS server and created a database and now the ODS version is 11.2. registering/unregistering have nothing to the database file format. -- Dmitry Kuzmenko, www.ib-aid.com
Re: [firebird-support] Re: use of -USE_ALL_SPACE
Hello, Nick! Thursday, May 2, 2013, 6:33:15 PM, you wrote: NU as I now understand it, if I restore the database with -USE_ALL_SPACE NU then do gfix -use reserve I will have a database which has all the static NU data on full pages and the data that does change will quickly go back to NU the 80/20 page usage. Something like 90% is static so I'm hoping this will No. I wonder where these rumours or false guesses comes from. No_reserve is a global switch, and after it is set, FB will not reserve space anymore at data pages. Gbak, as Ann said (and I says it constantly), is an ordinal application that inserts data to DB as any other application do. No magic, at all. For example, InterBase XE have new behavior and options to 1. change no_reserve option as ALTER DATABASE at runtime 2. change no_reserve option for selected tables. In Firebird, to set this option off, you may only use gfix, once, for all the database. And you can't do it during gbak. So, currently, this option in FB is only useful for read-only databases. That's it. Because option cannot be set for selected tables, only for the whole database. -- Dmitry Kuzmenko, www.ib-aid.com
Re: [firebird-support] Slow connection establishment to database
Hello, Stefan! Wednesday, April 24, 2013, 6:57:40 PM, you wrote: SH During connection establishment, fbclient.dll tries to load a file it SH usually finds one level above itself (firebird.conf? firebird.msg? SH firebird.log?) use ProcessMonitor to watch where is the delay. Since it's about 10-20 seconds, you will easily see, when it happens and what folder fbclient tries to open. -- Dmitry Kuzmenko, www.ib-aid.com
Re[2]: [firebird-support] Re: AGAIN (i know, but different) Max optimized... or not?
Hello, fabianoaspro! Tuesday, April 16, 2013, 5:46:48 PM, you wrote: fgc If you use this code inside a SP you must recompile it for using this new fgc index. Just FYI - procedures does not store query plans, so they never need to be recompiled. Procedure may not use new index because when it was run, server computed plan, and still use it, while procedure is being in use, i.e. in server metadata cache. When last user who run procedure disconnects, procedure's metatada being unloaded from cache. And new run will create new plan for this procedure. And, doing backup/restore never recompile any procedure/trigger/view. p.s. All I said is true for all InterBase (from 5.0) versions and for all Firebird versions. -- Dmitry Kuzmenko, www.ib-aid.com
Re[2]: [firebird-support] multiple connection strings
Hello, Nick! Friday, April 12, 2013, 5:36:19 PM, you wrote: NU I've run some tests, using firebird 2.1.5 on centos 5.9, a database of 36Gb: NU 22 mins $ gbak -user sysdba -pass xxx -backup localhost:/opt/db/mydb.fdb NU /opt/archive/db/nu3.fbk NU 28 mins $ gbak -user sysdba -pass xxx -backup -se localhost:service_mgr NU /opt/db/mydb.fdb /opt/archive/db/nu2.fbk strange. NU 29 mins $ gbak -user sysdba -pass xxx -backup /opt/db/mydb.fdb NU /opt/archive/db/nu1.fbk as I said, -se and local protocol on Linux nearly the same. NU 22 mins $ gbak -user sysdba -pass xxx -backup localhost:/opt/db/mydb.fdb stdout /opt/archive/db/nu0.fbk well, what I can say - seems that different systems and environment produces different results. And, such tests must be made for every production, to be sure that fastest method is used. -- Dmitry Kuzmenko, www.ib-aid.com
Re[2]: [firebird-support] gbak speed
Hello, Nick! Thursday, April 11, 2013, 1:31:17 PM, you wrote: NU the database backup are already seperate devices were you thinking to add -g option to gbak command line? NU On 11 April 2013 10:25, Marcus Bajohr marcus.baj...@gmail.com wrote: ** Hej, from my little knowledge i have: are the db and the backup on different devices? Or are they both on the raid? I've reduced the backup time from 1,5 mours to 40 minutes for a ~55 GB db by simply writing the backup to another physical dev cheers, Marcus Nick Upson mailto:n...@telensa.com Donnerstag, 11. April 2013 11:19 Hi, I'm need to speedup my database backup, its currently taking 6 hours to backup a 74Gb database. Using firebird 2.1.5 on centos, database is on fast drives with raid 10. How does this compare with the experience of others? the command line used is: (and I do need the garbage collect) gbak -user sysdba -pass XXX -backup localhost:/opt/db/mydb.fdb stdout /var/archive/db/mydb.fbk [Non-text portions of this message have been removed] -- Dmitry Kuzmenko, www.ib-aid.com
Re[2]: [firebird-support] gbak speed
Hello, André! Thursday, April 11, 2013, 2:42:01 PM, you wrote: How does this compare with the experience of others? AK Hmmm. A quick calculation shows: AK My biggest database is 1/74 of the size of yours. AK I need ~5 Minutes to backup a freshly restored database, backup takes AK longer if the restore is longer ago (up to 6.5 minutes). too slow. at my old desktop with Athlon 64 x2 5200 and 3 SATA II drives backup of 2.7GB database from one drive to another took from 3.5 to 5 minutes, depending on protocol used. The fastest way to do backup is to use Services API, i.e. -se option of gbak. And, to use -g option of course. So, with worst 5 minutes result, 74/2.7 = 2 hours and 17 minutes. And, with the a bit modern hardware (but still desktop with several drives) restore of another database, 1.5GB backup size and 3.9gb resulting database also takes not more than 5 minutes, via -se. But, via localhost - around 1 hour. -- Dmitry Kuzmenko, www.ib-aid.com ++ 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://docs.yahoo.com/info/terms/
Re[4]: [firebird-support] gbak speed
Hello, Nick! Thursday, April 11, 2013, 2:59:52 PM, you wrote: NU Hi Dmitry, NU are you saying that I should remove localhost from the database connection NU string to get faster performance? and that will work ok with other NU processes running that do connect via localhost continuing to run at the NU same time (this is a 24/7 system)? I say that when you specify usual -b option, gbak transfers database contents from server to local file. With localhost data goes through tcp, wich can be slow. Option -se designed in IB 6.0 to make backup (and restore) by server, minimizing the network redundancy. And yes, during backup/restore tests I've got that -se option makes backup up to 2 times faster, and making restore up to 3-4 times faster. If you have database of 3-5 gb size on that server, you may try to see what difference will be. My opinion about 6 hours backup of 74gb database - it is too slow. But, it maybe slow because of your RAID, or something else. p.s. on Linux, specifying local database (without tcp) have same effect that -se option. On Windows - it's better to use -se. NU On 11 April 2013 11:53, Dmitry Kuzmenko k...@ibase.ru wrote: Hello, André! Thursday, April 11, 2013, 2:42:01 PM, you wrote: How does this compare with the experience of others? AK Hmmm. A quick calculation shows: AK My biggest database is 1/74 of the size of yours. AK I need ~5 Minutes to backup a freshly restored database, backup takes AK longer if the restore is longer ago (up to 6.5 minutes). too slow. at my old desktop with Athlon 64 x2 5200 and 3 SATA II drives backup of 2.7GB database from one drive to another took from 3.5 to 5 minutes, depending on protocol used. The fastest way to do backup is to use Services API, i.e. -se option of gbak. And, to use -g option of course. So, with worst 5 minutes result, 74/2.7 = 2 hours and 17 minutes. And, with the a bit modern hardware (but still desktop with several drives) restore of another database, 1.5GB backup size and 3.9gb resulting database also takes not more than 5 minutes, via -se. But, via localhost - around 1 hour. -- Dmitry Kuzmenko, www.ib-aid.com ++ 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 -- Dmitry Kuzmenko, www.ib-aid.com
Re[2]: [firebird-support] gbak speed
Hello, André! Thursday, April 11, 2013, 3:02:36 PM, you wrote: AK I don't use Services API nor -g in that case. by some reason or never thought about it? I wonder, why not to use -g option. AK I simply leave everything as is; am in the process of upgrading all AK and everything to current hardware, OS and FB 2.5.2. That old AK database is FB 1.5.6 CS with some 200 open connections all the time AK and 80% of RAM + 30% CPU constantly in use - which might explain. Sure, butI did clean test to understand where performance differ. -- Dmitry Kuzmenko, www.ib-aid.com ++ 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://docs.yahoo.com/info/terms/
Re: [firebird-support] multiple connection strings
Hello, Nick! Thursday, April 11, 2013, 4:06:22 PM, you wrote: NU all programs that access the database use the same path component as either NU localhost:/db/mydb.fdb - if running on the server and NU myhost:/db/mydb.fdb - if accessing it from elsewhere no difference. NU possible new setup: NU all programs that access the database use the same path component as either NU /db/mydb.fdb - if running on the server and myhost:/db/mydb.fdb - if NU accessing it from elsewhere mmm, why ? why to use local protocol? Yes, you can mix connection strings, but here is another point - what if you will need to move your applications from server? Will reconfigure connection string for all of them? NU Dmitry has suggested that I should get significant performance improvement NU by avoiding the tcp stack but I recall (perhaps wrongly) there being issues NU with this approach. you missed the point that I said about different gbak behavior. 1. gbak -b -g localhost:c:\db\db.fdb localfile.fbk here backup transfers data from server to localfile via tcp 2. gbak -b -g -se localhost:service_mgr c:\db\db.fdb localfile.fbk here gbak sends COMMAND to the server (via tcp) to make backup by itself. And localfile.fbk - is local to the server, not to gbak. For better understanding I can add that if you kill gbak with the first command line, the backup will stop, because gbak did it. With the second command line backup will still continue, because after gbak sent command via -se, it only waits for output from the server, not for the data of the database. And the server does backup, not gbak. And, switching protocol for your local server apps will not give you any visible performance improvement. Gbak is another thing, it reads (without -se) the whole database. So, for gbak speed protocol matters. -- Dmitry Kuzmenko, www.ib-aid.com
Re: [firebird-support] FireBird 2.5 How can i create 1 byte (0-255) column?
Hello, wan30032! Sunday, March 31, 2013, 7:23:14 PM, you wrote: w hello,now i used type is smallint. w but there are more than 4,000,000,000 records. w so i want to change with like ms sql tinyint. I need to say some, against char(1) etc. instead of smallint - rows are being compressed in Firebird. - record header without columns is around ~17 bytes (don't remember exact size), so, 2 bytes of smallint here long way off. - with char(1) character set octets you will always need to convert integer to char and back. So, I suggest you to try do the following: create table with your fields, and smallint instead tinyint. create table with your fields, and char(1) instead tinyint. Populate these tables with several million random values. Check size of tables (gstat -a). Decide what column type to choose. ! you may create test tables only with one column, but there is Firebird's special behavior when record size is less than 22-23 bytes (for example, 2 integers, without count of record header size) Firebird fill data pages less than 50%. Thus, table with that small row can be greater (in pages) than table with row around 30-40 bytes (here server fill pages at ~80%). -- Dmitry Kuzmenko, www.ib-aid.com
Re: [firebird-support] FB 2.5 shutdown restore question
Hello, Rick! Wednesday, April 3, 2013, 11:09:33 PM, you wrote: RD gfix -shut single -force 1 server:/opt/firebird/db/clone.fdb RD rm -fv /opt/firebird/db/clone.fdb RD gbak -v -use_all_space -r /mnt/usb/firebird/clone.fbk RD server:/opt/firebird/db/clone.fdb RD gfix -online normal server:/opt/firebird/db/clone.fdb RD Target shutdown mode is invalid for database RD /opt/firebird/db/clone.fdb point 1 - shutdown state is not stored into backup. During restore DB is itself in single-user mode, so, noone can connect until restore finishes. If there was error, gbak will not turn db into online state (to indicate error). Also, Sean suggested correct scenario, because, when you delete DB on Linux, and there is at least 1 connected user (with single-mode shutdown), and create new DB with the same name (by restore), there will be 2 different (!!!) db files - old one and new one. I don't remember what problems this may lead to, but suggest to avoid such situations. -- Dmitry Kuzmenko, www.ib-aid.com
Re: [firebird-support] Re: Firebird Logging
Hello, sean! Thursday, March 28, 2013, 6:11:17 PM, you wrote: syc We are not the original developer of the application, oops. syc but have syc now been given the task of maintaining/debugging this 750,000 + lines of Delphi 7 code. OMG. syc Does FireBird support isolation levels ? yes, but if you are not familiar with Firebird, all this idea to maintain/debug that application seems impossible. syc There are a number of syc transactions that are waiting for locks to be release, which can be solved by changing the syc isolation level to dirty/uncommitted reads. These are for read only transactions. really? multiversion architecture of Firebird allow to have lock conflicts only when 2 transactions update same record. There are no locks when you read data, at all. Of course, you may set special isolation level, that will show locks on reading data, that is being modified, but this is not default, and you need to set these transaction parameters deliberately. And, with the new information about application, I can say that Firebird 2.5 trace/audit will not help you. Because trace helps only when you know, what happens inside application, i.e. how it works. If not, you cant compare what happens in Firebird, and what happens in application. You just need to check the exception handling in application, and at the first time find every place where exceptions are being kept back by the code. -- Dmitry Kuzmenko, www.ib-aid.com
Re: [firebird-support] Re: Restoring database from 2.0 to 2.5 doesn't work even if -fix_fss_data is used
Hello, Christian! Tuesday, March 26, 2013, 3:48:11 PM, you wrote: CM This has to work: CM gbak -c -v -t -rep fitxer.fbk fitxer.ib -user SYSDBA -password excuse me for my punctiliousness, but -t option is already by default -c -rep combination is a nonsense. -c and -rep options are self-sufficient, differ from each other, and of course, need not to be specified at the same time. So, -c, or, -rep, not both. And, in your case, command line will be gbak -c -v fitxer.fbk fitxer.ib ... Thank you for your patience :-) p.s. -rep is equal to -r o. -- Dmitry Kuzmenko, www.ib-aid.com
Re[2]: [firebird-support] firebird multiple user on delphi 7
Hello, cedriz! Monday, March 25, 2013, 5:18:52 AM, you wrote: cm thanks for the answers i will look upon them. cm but is it possible to connect to a firebird server via lan and cm internet simultaneously? what is internet and what is lan? And how it differ? No difference. At least for Firebird and lot of other applications. cm also can i put restrictions on users via cm firebird or i just need to do it on my delphi app? you are too misty. What restrictions? There are lot of things can be made - by firewall - by connection encryption software like zebedee. - by delphi application - by SQL grants - by your logic in procedures and triggers, allowing to restrict users at record level. So, what are you asking about? -- Dmitry Kuzmenko, www.ib-aid.com
Re[4]: [firebird-support] firebird multiple user on delphi 7
Hello, cedriz! Monday, March 25, 2013, 10:37:24 AM, you wrote: cm im sorry for not elaborating my question and stating it clearly. cm restrictions means restrictions on accessing files, e.g. on my cm database i have 5 tables in which only 2 tables will be allowed on cm a account or 3 tables. read about SQL GRANT/REVOKE. When you create user, it does not have any permissions, you need to GRANT something (at least select) for user to be able read data from some table. cm i would like to know if it will be on cm delphi 7 that i put the restriction or will it be on the firebird cm server? GRANT/REVOKE is an SQL commands. They applied to the database, i.e. enforced by Firebird. But, as any SQL commands grant/revoke can be executed from Delphi, or any other tool. In Delphi you need to specify only user name at login to the database to get these restrictions. cm i want to set up a network in which i can connect on a cm local network and at the same time can access it remotely from cm another location. not included in the local network. This does not related to the questions above. TCP is TCP, local or remote, there is no difference in tcpv4 between LAN and internet. When user connects to the database, GRANTs applied with no care where this user came from. -- Dmitry Kuzmenko, www.ib-aid.com
Re[4]: [firebird-support] firebird multiple user on delphi 7
Hello, cedriz! Monday, March 25, 2013, 10:37:24 AM, you wrote: cm im sorry for not elaborating my question and stating it clearly. cm restrictions means restrictions on accessing files, e.g. on my cm database i have 5 tables in which only 2 tables will be allowed on cm a account or 3 tables. by the way, to be clear: - Firebird keeps all metadata (tables, indices, procedures, etc) in one file. So, tables here are not files. - user never have access to the database file. This is client-server, so, ony server (Firebird) acceses the database file. User can only ask (query) server about the data in the database. - database file access on the server - is another story. -- Dmitry Kuzmenko, www.ib-aid.com
Re: [firebird-support] firebird multiple user on delphi 7
Hello, dice_in_trouble! Sunday, March 24, 2013, 9:37:52 AM, you wrote: d hello can somebody help me im just new to using firebird, and got some questions: d first is that how to connect my firebird database to my delphi 7 app you have IBX components in Delphi 7, 2 component pages. Read about them in Hepl, for example, on TIBDatabase. There are also examples and demos in the Delphi 7 installation folder. If you don't see that components in the IDE, 2 things may happen: 1. you need to run insclient i -f gds32 from the BIN of Firebird installation to make gds32.dll from fbclient.dll 2. you have not checked IBX (IBExpress) components during Delphi 7 installation. d second is that how can i make a multiple user login with different d restrictions on the data that will be displayed. read about SQL commands - GRANT and REVOKE. And maybe you need to read about the whole concept about it, because you asked that question. This is common to (nearly) all SQL servers. d and lastly is it possible that i have a dialog in delphi 7 wherein d i can make accounts that can login to my firebird server? you can do this by several ways. One way is via IBX Admin components. -- Dmitry Kuzmenko, www.ib-aid.com
Re[2]: [firebird-support] Unsupported on-disk structure for file xxx.FDB; found 32779, support 15
Hello, All! You may add this to the FAQ: Server, native ODS, can work with ODS InterBase 4.0/4.1 8.0 ... InterBase 4.2 8.2 8.2 InterBase 5.0/5.1 9.0 8.2 InterBase 5.5 9.1 8.2 InterBase 5.6 9.1 8.2 InterBase 6.01 Firebird 1.0 Yaffil 10.09.0/9.1 Firebird 1.510.19.0/9.1, 10.0 InterBase 7.03 11.010.0 InterBase 7.1 11.110.0 InterBase 7.54 11.210.0 Firebird 2.011.010.x Firebird 2.111.110.x, 11.0 Firebird 2.511.210.x, 11.x InterBase 2007 12.011.x (InterBase 7.x) InterBase 2009 13.112.0 InterBase XE15.013.1 InterBase XE3 15.0 Example error messages by gstat: Wrong ODS version, expected 8, encountered 32779? InterBase 4.x tries to open Firebird 2.x database Wrong ODS version, expected 8, encountered 13? InterBase 4.x tries to open IntrBase 2009 database Wrong ODS version, expected 15, encountered 32779 InterBase XE/XE3 tries to open Firebird 2.x database Wrong ODS version, expected 11, encountered 15 Firebird 2.x tries to open InterBase XE/XE3 database Example error messages from server (client): unsupported on-disk stucture for file ...; found 32779, support 10 Firebird 1.0/1.5 or InterBase 6.0 tries to open Firebird 2.x database. Firebird 2.x, to make ODS distinguesable from InterBase 7.x and higher adds higher bit (x8000) to the ODS. In hex ODS 32779 will be 800B, that means Firebird's ODS 11. -- Dmitry Kuzmenko, www.ib-aid.com
Re[2]: [firebird-support] Delphi XE2. Migrating from IBX components
Hello, Precious! Monday, March 11, 2013, 7:54:34 PM, you wrote: PS Try to use the dbexpress. Its built in delphi xe2 and already has support PS in firebird. I do not suggest dbExpress for several reasons: - Embarcadero closes dbExpress (like Borland closed BDE in 2002), and replacement is FireDAC (former AnyDAC). Unfortunately, it is only available for Delphi XE3 Enterprise users, and can be bought by XE3 Professional users (also it can be installed for previous Delphi versions) - dbExpress, like BDE, have almost no transaction control. SQLConnection.BeginTransaction method is nearly useless, because a) it does not support special IB/FB transaction parameters b) it does not allow to start 2 transactions at the same time Thus, I suggest to stay with IBX, or, to buy FIBPlus, or, to upgrade to XE3 and get FireDAC. These are direct access components (instead dbExpress, BDE, ODBC, OLE DB, etc), and have full control over IB/FB API capabilities. Yes, IBX have some incompatibilities with FB, for example, in unicode support (in Delphi 2009/2010, XE-XE3), but it can be easily fixed in IBX sources (like at the end of the article, about Division by zero error in IBX Delphi 2009 http://www.ibase.ru/unicode_faq.html - it's in russian, but code is understandable, and for the text you can use google translate) -- Dmitry Kuzmenko, www.ib-aid.com
Re: [firebird-support] Delphi XE - Unicode
Hello, Tibi! Friday, June 22, 2012, 9:43:00 AM, you wrote: T I have a Delphi XE program then connects to a Firebird (2.5.1) database. T Everyting is fine using ASCII characters (doing this since Delphi 6) . I use T IBX components to access the database. One of my customers asked me to let T him input Ucrainean (cyrilic) characters into the program. Trying to T copypaste the T please help me implement this in TDBEdit and TDBGrid. there must not any problems with - db in UTF8 - Delphi XE - IBX, with patched IBSQL.TIBXSQLVAR.GetCharsetSize method, as shown here, near the end: http://www.ibase.ru/unicode_faq.html If you like, you can read the whole article, translated to any language by google, or in pure russian. -- Dmitry Kuzmenko, www.ib-aid.com
Re[2]: [firebird-support] Delphi XE - Unicode
Hello, Tomas! Friday, June 22, 2012, 10:44:32 AM, you wrote: TK I prefere dbExpress components for unicode support. TK Let you try them. dbExpress is awful, and now deprecated (fortunately). Why? read my answer, here, with subject Re: [firebird-support] Delphi XE2. Migrating from IBX components TK But if you use TDBEdit, TDBGrid, ... you need to use TK TDataSetProvider and TClientDataSet TK (and distribute midas.dll together with your application) this is related only to dbExpress, not to IBX. So, if you suggest him to switch to dbExpress, he will need to rewrite the whole application. -- Dmitry Kuzmenko, www.ib-aid.com
Re[4]: [firebird-support] Database backup test restore
Hello, Sean! Tuesday, March 5, 2013, 9:36:01 PM, you wrote: LS In the context he meant, that the backup was only to ensure that LS the backup was valid, no other use for the restore was intended -- so, it makes perfect LS sense that he would want to delete/destroy the backup once it had successfully completed. In the context he meant, that backup to tape is the key. -- Dmitry Kuzmenko, www.ib-aid.com
Re: [firebird-support] Firebird 2.5 - UTF8 - UDFs
Hello, castel! Monday, March 4, 2013, 7:08:52 PM, you wrote: cmtb Now I'm testing my application in Delphi 5 where my cmtb ib_connection.charset is now defined to WIN1252 and all seemed cmtb to work fine with the firebird 2.5 and utf8. cmtb Until i tried to create a record where the field value was cmtb containing € and a trigger was on that table and working this cmtb value with a UDF, i received the error message : malformed string at trigger cmtb The UDF was compiled with Lazarus on Windows 64-bits, because cmtb the firebird server is installed on windows 64-bits. You can find example in my uniciode faq http://www.ibase.ru/unicode_faq.html at the end. You may try to use google translate to understand the text, but the rules are simple: 1. to use unicode in udf it is better to have unicode compiler, like Delphi 2009-XE3. Moreover, XE2 and XE3 have 64bit compiler (but only for Windows, if you work on Linux). 2. if you use non-unicode udf, you must declare character set for the input/output udf params. 3. of course, you will not be able to pass characters, that don't exist in your non-unicode charset. -- Dmitry Kuzmenko, www.ib-aid.com
Re: [firebird-support] Database backup test restore
Hello, Rick! Monday, March 4, 2013, 11:07:12 PM, you wrote: RD After backup we do a test restore: RD gbak -v -se server_name:service_mgr -use_all_space -r backup.fbk RD test.fdb RD Normally we just delete the file after gbak completes, but I'd like to RD be nicer to the database server. I wonder that you - use -use_all_space for test database. You see real speedup? - delete restored database. Why you keep backup, that needed to be restored again at some reasons? Instead, better keep database, delete backup. RD Some coworkers have also been saying RD they think that Firebird is still using the database after gbak exits, RD and deleting the file at that point is logging errors. I also wonder why Firebird need to be interested in the database? It has been restored, so, if noone connects to it, FB does not care about this database anymore. The end of gbak is the final disconnect. RD After gbak completes, should I run an isql script and drop the database? RD Should I shutdown the database? Or just continue deleting the file RD after the restore completes? No, just delete it (if you still wish to do that). There is no reason to shutdown, run scripts, etc. -- Dmitry Kuzmenko, www.ib-aid.com
Re[2]: [firebird-support] Database backup test restore
Hello, Rick! Tuesday, March 5, 2013, 1:36:21 AM, you wrote: RD It requires less space for the test. How much less in your case? RD The backup is written to tape. well, I understood why you store backup, but not restored database. Also trying to have less space for backup. The end of gbak is the final disconnect. RD OK. If my coworkers can prove otherwise, I'll file a bug report. ok. -- Dmitry Kuzmenko, www.ib-aid.com
Re[2]: [firebird-support] Database backup test restore
Hello, Sean! Monday, March 4, 2013, 11:42:44 PM, you wrote: LS You missed a key point from Rick's post ... After backup we do a ***test restore*** LS Obviously, Rick is double checking that the backup is valid. missed nothing. Test restore does not differ from usual restore. LS As for -use_all_space option: I know what it does, thank you. LS 2 - I suspect that Rick believes that the restore will run LS faster, since more data will be on each page, thus less disk IO. (Personally, I have never LS tested the performance) I tested restore performance, but not with that flag. Yes, no_reserve may be faster and yes, the database has smaller size. But the gain depends of the size of the database. -- Dmitry Kuzmenko, www.ib-aid.com
Re: [firebird-support] Create index while database is in use?
Hello, Raith,Daniel! Saturday, March 2, 2013, 1:39:35 AM, you wrote: RD Is it safe? RD I want to create an index on a table but the database (fb1.5) has RD ~20 active users. Database usage is mostly reads, especially the table I want to RD index. RD I just don't want to take a chance creating an index with active RD connections could corrupt the database. With FB 1.5 - yes, if table is being modified when you create index on it, you may get an index that will have less keys than values (or versions) in the table. So, in terms of integrity index will not cover all records, and can be considered as corrupted, because search of an index may not include some records. But, you will not get DB corrupted in terms of corrupted DB. I mean, there will no be broken structures or data in DB, that can give errors like consistency check. Firebird 2.5 have protection for that, i.e. table modifying will be locked for the time of index creation (if I remember correctly what is locked - index creation or table data modification). -- Dmitry Kuzmenko, www.ib-aid.com
Re[2]: [firebird-support] Re: Handling large imports while system is in production
Hello, W! Wednesday, February 6, 2013, 11:03:13 PM, you wrote: WO THAT'S INCREDIBLE!!! WO Importing row by row 3.600.000 records takes about 14 hours. Importing WO 1.160.000 records using an external table takes seconds, less than a minute. WO Thanks very much to all for the advice. The most common solutions to speedup import 1. do not use cached datasets to read data. The more records you read, the more cache allocated in client program, the slower import will be. Source dataset must be able to cache only one row. 2. do not commit on each insert. That's the rule. The more commits, the slower performance. If you can't control transactions during import, you'll fail. 3. lot of indices on target table slowdowns inserts and updates. Turn that indices off (inactive) for import. Or, at least, turn of less selective indices. 4. do not use DataSets for target. Insert must be made by simple query insert ... and nothing else. 5. use parameters to insert data. plain text insert with data takes more time on prepare/execute cycles, than one prepare, and cycle with change_params/execute. -- Dmitry Kuzmenko, www.ib-aid.com
Re: [firebird-support] Firebird and RAID
Hello, garethm! Monday, February 4, 2013, 11:07:53 AM, you wrote: g We have a customer using a Firebird 2.0.6 Classic Server database g on a single disk connected to an IBM ServeRAID M5015 SAS/SATA Controller. The disk is g not part of any RAID array. The server is running Windows 2008 R2 64-bit. g Performance in this configuration is much slower than when the g disk is not connected to the RAID controller. Other applications and database services g running on the same hardware do not have any difference in g performance based on how the disk is connected to the computer. My opinion is simple. If Firebird DB at disk, attached not to RAID - fast and Firebird DB at dis, attached to the RAID - slow then problem is with raid controller configuration. Other applications can use disk less then Firebird, or only read, so, the IO of that applications can be much less than FB's. Have you tried on that configurations any common disk test utilities? From simple like CrystalDiskMark, HDTune, etc, to IOMeter? You have not seen any difference? -- Dmitry Kuzmenko, www.ib-aid.com
Re: [firebird-support] GBAK Scheduler won't install as service on Windows 2008 Server
Hello, ScottMoon! Wednesday, January 30, 2013, 10:45:28 PM, you wrote: S I'm not sure if this is the correct forum for this, so please let S me know if it should go somewhere else. S I am configuring a new Windows 2008 server (64-bit) to host our S Firebird installation (SuperServer v2.5.2). I have installed FB and it is running fine. S When I installed the latest version of GBAK Scheduler (1.0.10), S however, I ran into problems. The main application runs as expected, but I cannot get it If you mean this https://sites.google.com/site/gbakscheduler/ it is old, and yes, it can have access rights problem. I also, with one of my applications running as service, have an unknown and unresolved access rights problem even with starting service from an application on Windows 2003 Server and another server editions (do not have this problem on any desktop OS). The solution is only - run installation from Administrator - start service from Administrator If we were speaking about YOUR application, I would sent you to MSDN or MS knowledgebase, but Gbak Scheduler is a third party product, and you need to ask it's author about any problems with it. -- Dmitry Kuzmenko, www.ib-aid.com
Re: [firebird-support] How to keep fdb file small size
Hello, Nenhum! Monday, January 14, 2013, 6:17:09 PM, you wrote: NPe Does anyone knows how to keep the fdb file size small? I created NPe a small database and with 15.000 records of small data per record and i get 48Mb size NPe for the fdb file. NPe Thank you. Seems that you inserted some data and then deleted it. Database does not shrink, never. To see real size of data and indices in your database, make backup/restore. Also, if you use Firebird 2.5, you may check #DatabaseGrowthIncrement = 134217728 parameter in the firebird.conf. But anyway, increase goes by 1/16, and that means, the last increase was made by 6.6mb, and previous size was 41mb. btw, is 48mb now is a big database? NPe NPe ++ NPe Visit http://www.firebirdsql.org and click the Resources item NPe on the main (top) menu. Try Knowledgebase and FAQ links ! NPe Also search the knowledgebases at http://www.ibphoenix.com NPe ++ NPe Yahoo! Groups Links -- Dmitry Kuzmenko, www.ib-aid.com
Re[4]: [firebird-support] sweep start running
Hello, Nick! Monday, January 14, 2013, 6:39:33 PM, you wrote: NU I'm think of something like this: NU process A starts a transaction holds it open, holding the transaction NU counter NU (time transactions pass) NU process B starts a transaction, sees gap 2 initiates sweep ... No, completely. Process A starts a transaction, engine checks Next, OAT, OST, etc, and if there is a sweep gap greater than sweep interval, starts sweep. This happens at the start of transaction, and this operation is not parallel. So, when next transaction will start, auto-sweep already will be active, and will not start second time. And, transactions does not holding the transaction counter. Have you heard about windows function InterLockedIncrement? Transaction start is like this. NU On 14 January 2013 14:30, Dmitry Kuzmenko k...@ibase.ru wrote: ** Hello, Nick! Monday, January 14, 2013, 5:52:49 PM, you wrote: NU if 2 processes both see a gap 2 will that result in 2 sweeps running NU at the same time? transaction start is serialized, i.e. no 2 transactions can start at the same time. So, auto-sweep can not run 2 times at once. Only first transaction start, that will discover sweep interval, will initiate auto-sweep (if it is not turned off by sweep interval = 0). -- Dmitry Kuzmenko, www.ib-aid.com -- Dmitry Kuzmenko, www.ib-aid.com
Re: [firebird-support] Question about transactions
Hello, W! Monday, January 7, 2013, 2:33:30 AM, you wrote: WO Hello everybody, I have a question: WO - Transaction 510 inserts a row WO - Transaction 510 commit ok, data committed. WO - Transaction 526 start WO - Transaction 526 updates the row inserted by transaction 510 well, row locked by update made by transaction 526. WO - Transaction 535 start WO - Transaction 535 updates the row inserted by transaction 510 no, transaction 535 will get error deadlock, because version created by transaction 526 is not committed or rolled back yet. WO - Transaction 542 start WO - Transaction 542 updates the row inserted by transaction 510 NO. Same reason that I noted before. WO 1. Should be the order for the following commits: 526, 535, 542? NO. WO 2. Or in some cases it could be 542, 535, 526? NO. WO If the answer is 1. it means that if transaction 526 hangs up will avoid WO transactions 535 and 542 to finish. Am I right? No transaction can overwrite version, created by any transaction that modified some record and has not committed or rolled back changes. -- Dmitry Kuzmenko, www.ib-aid.com
Re: [firebird-support] Replacement of parameters within a stored procedure
Hello, SoftTech! Friday, December 14, 2012, 10:01:51 PM, you wrote: S I cannot do what I need to do on the client side as you suggest. S This must be handled on the server using firebird. A trigger is used to call a stored S procedure and then inside this stored procedure I need to replace parameters with actual values. Look at Execute Statement syntax. But what are you doing is road to hell - trigger, than SP, then parsing a then ES with THAT query. Be sure that performance will go to zero. -- Dmitry Kuzmenko, www.ib-aid.com
Re: [firebird-support] Replacement of parameters within a stored procedure
Hello, SoftTech! Saturday, December 15, 2012, 12:36:32 AM, you wrote: S I have used Execute Statement in the past. Using it to run SQL S statements built on the fly. What my problem is, is taking an existing SQL statement and S replacing the Param1 and Param2 with actual values. Again, I cannot build it on the fly. well, then, I can't understand the problem. You have statement in the varchar variable, and you can change it to anything. And, ES accepts parameters. Can you explain what you need to get (what you have we see)? -- Dmitry Kuzmenko, www.ib-aid.com
Re: [firebird-support] Changing Charset only Pumping ?
Hello, Gugui! Tuesday, November 13, 2012, 12:43:16 AM, you wrote: G I have a fdb with 400 Mb and Charset defined to NONE, default database charset is declarative only, and istructs server to use that charset for newly created character fields with no charset specified. So, you can create field with needed charset at any time in any database. G so I need to change it to UTF-8 (to work with Lazarus/Zeos) G what is the best way to do that ? G Is there a way to restoring it with utf-8 already ? no, only pump. unicode uses up to 4 bytes per character. Charset NONE uses only 1 byte per character. So, field char/varchar(20) can store 20 characters for NONE, and from 20 to 5 characters for UTF8, depending on how much bytes each character have. Thus, you may need to increase your character fields size. -- Dmitry Kuzmenko, www.ib-aid.com
Re: [firebird-support] Re: Is windows better than Linux for Firebird 2.5 64 bit?
Hello, trskopo! Wednesday, November 7, 2012, 9:29:05 AM, you wrote: t 1) What drivers in that should exist for better performance? My t guest is CPU and Harddisk, is this correct? yes. But even you buy brand hardware, drivers can be bad in performance. t 2) Assuming that Linux and windows has correct drivers, which one is better? It can be understood only by test. Linux is much specific, as I said. For example, on some hardware Linux (CentOS) was 30% slower than Windows (for FB Classic with ~400 active connections). 2. You can choose linux distro by your preferrence. If you don't know any Linux well, then, you will have troubles. t I am thinking using CentOS or Suse, what distro do you use for yours? I do not use Linux :-) but our clients - use latest CentOS and Suse. -- Dmitry Kuzmenko, www.ib-aid.com
Re: [firebird-support] Is windows better than Linux for Firebird 2.5 64 bit?
Hello, trskopo! Tuesday, November 6, 2012, 10:05:00 AM, you wrote: t Hi all, t I am considering moving from windows to linux, but not sure if it is a good idea. t Any one can give me a hints about this matter? t Is it Linux better than windows on the same hardware, regarding performance/security/etc? t And if it's better, what distro should I choose and what file system for database file? 1. Windows can be better or slower than Linux on same hardware, depends on hardware and existing drivers for hardware for Linux and Windows. Difference can be up to 30% of performance. 2. You can choose linux distro by your preferrence. If you don't know any Linux well, then, you will have troubles. 3. file system performance on linux, and performance of linux itsef depends on lot of factors - journaling, barrier, kernel, and hardware drivers. Thus, the only choice for you is to test it by yourself. -- Dmitry Kuzmenko, www.ib-aid.com
Re: [firebird-support] When does OIT = OAT?
Hello, ruan_h! Sunday, September 30, 2012, 8:09:27 AM, you wrote: r Oldest transaction 1 r Oldest active 2 r Oldest snapshot 2 r Next transaction3 this is the most normal transaction state. OIT+1 = OST = OAT = Next -1 r What does OIT = 1 mean here? r I then run a few transactions in one connection and commit them r all, but in the stats, OAT still = OIT + 1. read this article: http://www.ibase.ru/devinfo/summary_en.htm -- Dmitry Kuzmenko, www.ib-aid.com
Re: [firebird-support] Why not sweeping?
Hello, fdt4y! Tuesday, July 10, 2012, 1:31:23 PM, you wrote: f What are we overlooking? Our only current solution is to do a f full backup and restore at silly morning hours, otherwise the system grows terribly slow f due to garbage not being collected. f Oldest transaction 460 f Oldest active461 f Oldest snapshot 461 f Next transaction 50325450 you overlooking that some of your applications started transaction long time ago (50 million transactions, 7 days ago), and this transaction is still ACTIVE. That's why versions still not garbage, and that's why sweeping does not help. -- Dmitry Kuzmenko, www.ib-aid.com
Re: [firebird-support] Re: Why not sweeping?
Hello, fdt4y! Tuesday, July 10, 2012, 7:17:56 PM, you wrote: f After some more investigation I found that our main user interface f client app starts a single long running read transaction used to pull data for f display in its grids. All updates are executed in small/short f 'write' transactions. It is common for our client app to run 23 hours in a day (in a f warehouse running shifts). We use FIBPlus components which works f great, but now I'm faced with a catch-22. How can I commit/rollback the 'long read' f transaction without disconnecting the FIBDatasets which feed the f grids. If I do commit the read transaction, all the grids lose their content. read-only transaction must be real read-only, not logically read-only. So, you need to set this transaction parameters to read read_committed rec_version nowait -- Dmitry Kuzmenko, www.ib-aid.com
Re: [firebird-support] Delphi App won't Connect to FB2.5
Hello, Craig! Saturday, June 30, 2012, 11:25:26 PM, you wrote: CC locally. I get an IB Object error code 335544375 unavailable database. all known reasons of unavailable database: this error can happen when - client library is not same version as server. Local prococol can be incompatible even between minor versions of the same server - Firebird 1.5.0, 1.5.1, InterBase 7.0, 7.5. - you are using local protocol (path to database without server name) on Windows Vista or 7. To work with local protocol on those OSes you need - - InterBase 2007 SP1 and higher - - Firebird 1.5/2.x with IpcName=Global\FIREBIRD in firebird.conf - - if IB/FB is started as an application, it may need to be run with administrative privileges - client library does not support local protocol at all. For example, Firebird 1.5.1 for Windows, Classic. - server is running as a service. So, local protocol may work or may not work, depends on version of server and client library, and sometimes on OS version (Windows Vista, Windows terminal servers). - sometimes reasons why local protocol works on one computer, and not working on another (looks similar), can't be understood or found. Also one of the unavailable database reasons is specifying non-existing path to DB. In this case you will get this error even for the network connection. p.s. someone can put it into the FAQ, because this is copied from russian (ibase.ru) FAQ, that helps a lot of people for several years. :-) -- Dmitry Kuzmenko, www.ib-aid.com
Re: [firebird-support] Re: Unexpected behaviour when using union
Hello, roydamman! r Maybe I am wrong but I always thought the order by is applied on r the results of the two selects in both the second and third union query. And the order r by clause does work in the second query (also an union query) r order by 2 and does not work in the third query order by upper(2). The only difference r is the use of upper() which does work with the first query with only one select. I never heard about using upper(n) where n is the number of column. It may work, but, anyway, union eliminates duplicate rows, while union all - not. So, your thoughts are wrong. :-) -- Dmitry Kuzmenko, www.ib-aid.com
Re: [firebird-support] Manipulating BLOBs in a x64 UDF compiled with Delphi XE2
Hello, Josef! Monday, June 18, 2012, 10:18:57 AM, you wrote: JK function STRBLOB(Value: PAnsiChar; Dest: PBlob): PBlob; cdecl JK The blob record (probably wrong for x64): JKTISC_BlobGetSegment = function(BlobHandle: PInt; JK Buffer: PAnsiChar; JK BufferSize: LongInt; JK var ResultLength: LongInt): SmallInt; cdecl; no, buffersize: word; // two unsigned bytes, NOT longint. resultlength: word: // the same two unsigned bytes JKTISC_BlobPutSegment = procedure(BlobHandle: PInt; JK Buffer: PAnsiChar; JK BufferLength: SmallInt); cdecl; that's correct. JKTBlob = record that's correct too -- Dmitry Kuzmenko, www.ib-aid.com
Re: [firebird-support] Re: gbak to a non existing directory - Email found in subject
Hello, ma_golyo! Thursday, June 7, 2012, 5:32:10 PM, you wrote: m That is why I want/expect to create missing directory with m Firebird/gbak. It can create file but not a directory? It's strange to me. because directories for databases, etc, must be created by administator. It is not strange. Your wish - is strange. m why to separate customer's DBs by folders? Why not to place all of m them into one folder? m Namespace separation. Each customer has a fix named DB and any m number of (at least 1) custom named DB. you created that schema, don't want to change it, and want Firebird be changed for your needs? Really, as it was said, creating db anywhere at the filesystem is dangerous, and violates lot of administrative and security rules. m --- In firebird-support@yahoogroups.com, Dmitry Kuzmenko kdv@... wrote: Hello, ma_golyo! Thursday, June 7, 2012, 2:25:05 PM, you wrote: m To stat a new customer we have to m #1 : copy the app to new folder (win) m #2 : copy the initial DB to new folder (linux) m #3 : update inital DB metadata to current app version m I'm writing (Delphi) app which does the steps above. App runs on the terminal (win) server. what if Firebird on Linux is run under special account (as it usually made by linux administrators)? why to separate customer's DBs by folders? Why not to place all of them into one folder? Do you use aliases.conf? If not, and db and folder name somehow related to the customer (name, id, etc), some customer can think of hacking some other client's DB. So, I understand that you are trying to make process automatic, but the result looks insecure. m --- In firebird-support@yahoogroups.com, Michael Ludwig milu71@ wrote: ma_golyo schrieb am 07.06.2012 um 06:07 (-): Why do you not want something like that? What is te purpose of this restriction? Or it's not a restriction, just not implemeted? Guess most people back up their databases to known locations, be it locally or on the server. Willy-nilly creation of folders simply isn't necessary. I'd say it is even undesirable because it could create a folder by accident because you mistyped the path and then your important backup might end up in a folder that you don't have on your radar and so it would be effectively lost when you need it. Why would you want to create a new folder from another machine instead of just backing up to C:\Backups ? Explain your use case if you have a good one. Michael m m ++ m Visit http://www.firebirdsql.org and click the Resources item m on the main (top) menu. Try Knowledgebase and FAQ links ! m Also search the knowledgebases at http://www.ibphoenix.com m ++ m Yahoo! Groups Links -- Dmitry Kuzmenko, www.ib-aid.com m m ++ m Visit http://www.firebirdsql.org and click the Resources item m on the main (top) menu. Try Knowledgebase and FAQ links ! m Also search the knowledgebases at http://www.ibphoenix.com m ++ m Yahoo! Groups Links -- Dmitry Kuzmenko, www.ib-aid.com
Re: [firebird-support] Re: gbak to a non existing directory - Email found in subject
Hello, ma_golyo! Thursday, June 7, 2012, 2:25:05 PM, you wrote: m To stat a new customer we have to m #1 : copy the app to new folder (win) m #2 : copy the initial DB to new folder (linux) m #3 : update inital DB metadata to current app version m I'm writing (Delphi) app which does the steps above. App runs on the terminal (win) server. what if Firebird on Linux is run under special account (as it usually made by linux administrators)? why to separate customer's DBs by folders? Why not to place all of them into one folder? Do you use aliases.conf? If not, and db and folder name somehow related to the customer (name, id, etc), some customer can think of hacking some other client's DB. So, I understand that you are trying to make process automatic, but the result looks insecure. m --- In firebird-support@yahoogroups.com, Michael Ludwig milu71@... wrote: ma_golyo schrieb am 07.06.2012 um 06:07 (-): Why do you not want something like that? What is te purpose of this restriction? Or it's not a restriction, just not implemeted? Guess most people back up their databases to known locations, be it locally or on the server. Willy-nilly creation of folders simply isn't necessary. I'd say it is even undesirable because it could create a folder by accident because you mistyped the path and then your important backup might end up in a folder that you don't have on your radar and so it would be effectively lost when you need it. Why would you want to create a new folder from another machine instead of just backing up to C:\Backups ? Explain your use case if you have a good one. Michael m m ++ m Visit http://www.firebirdsql.org and click the Resources item m on the main (top) menu. Try Knowledgebase and FAQ links ! m Also search the knowledgebases at http://www.ibphoenix.com m ++ m Yahoo! Groups Links -- Dmitry Kuzmenko, www.ib-aid.com
Re[2]: [firebird-support] Re: Embedded Firebird leaves a lot of temp files behind
Hello, Alec! Sunday, June 3, 2012, 10:17:05 PM, you wrote: AS Actually, FB correctly deletes fb_sort file used by a transaction after the AS transaction completes. wrong. sort files being deleted when query that have plan sort fetched to EOF, i.e. to the last record. Same happens, for example, when group by query sort a huge resultset and result fits in memory. If sort file left in temp, then application was terminated or failed, or computer was powered off or reset. Nothing to the transactions. AS However, we have a lot of long-running concurrent AS transactions which generate a lot of large fb_sort files. long-running transactions is a different issue. Just fix your application. AS Moreover, when our customer rebuilt indexes they ran out of system disk space (since this AS is where %TEMP% points to) and left FB in corrupt state. change %TEMP%. -- Dmitry Kuzmenko, www.ib-aid.com
Re[4]: [firebird-support] Why PLAN token is not allowed after ORDER BY?
Hello, Alec! AS 2. Changed the query to A INNER JOIN B ORDER BY X PLAN P, where P is the AS plan generated by FB in #1. Received Unexpected token PLAN error. AS 3. Change the query to A INNER JOIN B PLAN P ORDER BY X - success! AS Why does #3 succeed but #2 fail? because inner join is not equal to left join. -- Dmitry Kuzmenko, www.ib-aid.com
Re: [firebird-support] Firebird hangs for a while blocking all DB operations periodically.
Hello, anthonyjang2012! Tuesday, May 29, 2012, 9:22:48 PM, you wrote: a Environment : Firebird 2.5.1 Super-Classic x64 on Windows 2008 R2 Server with 32 GB of RAM a We have a Firebird server installation that periodically blocks a all operations for a few minutes and then comes back alive on its own. This has happened a about once a month for the last few months. During this blocking a period, Firebird CPU usage is unusually low as this is a busy server. This server a normally has 200-300 client attachments. The blocking time has a varied from 2 minutes to over 10 minutes. During this time, no Firebird operations can be a performed i.e. New connections are blocked along with existing a connections. Upon recovery, Firebird continues processing without any other issues. Looks like storage hangs. Have you looked at - windows system log - Perfmon, at the physical disk counters (queue, disk read bytes/sec, disk writes bytes/sec) ? and, anything in firebird.log? do you use any UDFs? -- Dmitry Kuzmenko, www.ib-aid.com
