Re: [firebird-support] Re: order by takes too long
> Hello, > > ok, I guess I need to create a DESC index on TIMESTAMP of MY_TABLE. > I could verify this speeds it up from 1 minute to 1 second. > This works as long as MY_TABLE is a real table. > But now MY_TABLE is actually a View. > > Can I created a index on a view with firebird? No, but an index on the underlying table should be used by the optimizer. Check the execution plan of your query accessing the view. -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: [firebird-support] Unique Constraints and NULLs
Hello, > Are NULLs considered values for unique constraints on a table? No. NULL is not evaluated as part of a unique constraint. What you could do is to declare your column as NOT NULL and provide a DEFAULT value/clause for your column, using a value which your application interprets as unknown / unset etc ... -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc. > > e.g. > > CREATE TABLE blah ( >col1 INT NOT NULL, >col2 INT, > >CONSTRAINT unq_blah UNIQUE(col1, col2) > ); > > INSERT INTO blah (col1, col2) (123, 321); > INSERT INTO blah (col1, col2) (123, 999); /* okay */ > INSERT INTO blah (col1, col2) (123, 321); /* error */ > > INSERT INTO blah (col1, col2) (123, NULL); > INSERT INTO blah (col1, col2) (123, NULL); /* okay? error? */ > > I'd like that NULL example to be an error, does the UNIQUE constraint do > that, or do I have to use a CHECK constraint? If it does require the > CHECK, are there any performance tips for that setup? > > Scott > > > > > Posted by: Scott Morgan > > > ++ > > Visit http://www.firebirdsql.org and click the Documentation item > on the main (top) menu. Try FAQ and other links from the left-side menu > there. > > Also search the knowledgebases at > http://www.ibphoenix.com/resources/documents/ > > ++ > > > Yahoo Groups Links > > >
Re: [firebird-support] Page cache size in Firebird 3.0.4
> I am running SS FB 3.0.4 x64 in Windows 10 x64 with 16GB RAM. > > Database properties > > ODS Version 12 > Page size 16384 > Pages 437728 > Size on disk 6.68GB > Page buffers 10240 > Read only false > > Settings > > Dialect 3 > Default character set NONE > Sweep interval 2 > Forced writes > > Database alias in databases.conf file > = > my_dba = C:\Dba\my_dba.fdb > { > FileSystemCacheThreshold = 2M > LockMemSize = 16M > LockHashSlots = 30011 > TempCacheLimit = 2048M > TempBlockSize = 2M > DefaultDbCachePages = 65536 > } > > I ran a query with flamerobin, this is the statistic result : > = > 46220441 fetches, 17965 marks, 172471 reads, 82 writes. > 8847 inserts, 0 updates, 0 deletes, 15730299 index, 8847 seq. > Delta memory: 4206064 bytes. > G_TMP: 8847 inserts. > Total execution time: 52.434s > Script execution finished. > > On Windows Task Manager, Firebird Server took 236MB RAM, max. > > If not mistaken, Page Cache Size should be > Page Size x DefaultDbCachePages = 16384 x 65536 = ~ 1GB > > Why Firebird server only took 236 MB RAM ? Is this normal or something > missed here ? DefaultDbCachePages (65536 in your case) is only used if Page buffers at database level is set to 0. Yours is set to 10240. Hope this helps. -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: [firebird-support] Re: Issue with large table in FB 1.5 and 2.5
Hi Mark, > On 30-5-2019 16:14, Thomas Steinmaurer t...@iblogmanager.com > [firebird-support] wrote: >>> Also one further question Do later versions of Firebird (ie. 3 or >>> 4) have any performance increase for cooperative garbage collection at >>> all? Would I expect to see any performance improvement by any newer >>> version, or different server implementation here? >> >> Likely TRUNCATE TABLE ... in Firebird 4.0 comes to rescue here: > > Truncate table (CORE-2479) isn't implemented yet, so it is unsure this > will land in Firebird 4. Oh, right. Thought it is in Firebird 4 Alpha already. Sorry. -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: [firebird-support] Re: Issue with large table in FB 1.5 and 2.5
> Also one further question Do later versions of Firebird (ie. 3 or > 4) have any performance increase for cooperative garbage collection at > all? Would I expect to see any performance improvement by any newer > version, or different server implementation here? Likely TRUNCATE TABLE ... in Firebird 4.0 comes to rescue here: a) No need to drop/create dependent objects like SPs etc. in the DROP TABLE use case b) Won't suffer from garbage collection as with deleted data from the table. -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: [firebird-support] Drop table safe when other users are active?
Hello Kjell, > I see noone replied. OK. > > > > 1. Alexey Kovyazin at IBSurgeon confirms that the problem still exists. > > He's the one who fixed my corruption before. He should know... > > > > 2. He also says that there's a "rule" that DDL should always be executed > > in exclusive connection. Where is this rule documented, if it exists? > > > > It would seem odd to ignore the drop table problem and also not document > > that it exists and that DDL should be executed in exclusive connection. > > In fact, if DDL is supposed to be executed only in exclusive connection, > > the engine should refuse to execute DDL in any non-exclusive connection > > and return a suitable error. > > > > If there's already an issue in the tracker about this, I'd appreciate a > > pointer to it so I at least would be able to follow it. I fully agree. If DDL statements on an active database may result in corruption, then the Firebird engine should reject such requests or fix bugs in that area. -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: [firebird-support] Recursive Relationship on Dictionary Table
Hi, > Hi, > > > Is this list allowed to post SQL queries ou links? Anyway if someone is > interested and can help me, I did a recent post on SO which has been a > great challenge to me, although it seems simple at first sight: > > > https://stackoverflow.com/questions/56220744/recursive-relationship-on-dictionary-table If your question is, if Firebird supports recursive queries via Common Table Expressions (CTE), then the answer is yes. Since Firebird 2.1. https://firebirdsql.org/refdocs/langrefupd21-select.html#langrefupd21-select-cte -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: [firebird-support] FB 2.1 transaction question
> I have a program that uses Firebird 2.1. Most installation use the > embedded version so I prefer to stick with 2.1 since it does all I need. > > I have a question about transactions: if I start a transaction, I add or > modify some data, then the program gets an exception and it crashes, > what happens to the transaction? > > I was believing that if the program terminates without calling commit or > rollback, then the transaction is automatically rolled back. > > Now a customer had a problem that can be explained if the transaction is > not rolled back, but the data changes remained in the database even if > commit was not called because the program crashed before calling it. > > Can anybody explain if changed data in this situation remains in the > database of is it rolled back? Data changes, which did not get acknowledged by either COMMIT or COMMIT RETAINING should never get permanently persisted. Any chance that you are using your client access components in auto commit mode? -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc. ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ 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: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] Get client-IP-Adress
Hello Josef, > Hi, > > > with our client software installed on a terminal server we like to get the > IP-Adress of the "real" client-computer connected to the database (the one > the > user sits in front of). > > > With Mon$Remote_Address from Mon$Attachments (using FB 2.5.8) we get the > IP-Adress of the Citrix-machine. > > > Is there a way to get the IP-Adress of the client-machine. > Is there a difference/improvement using FB 3? One work-around that comes to mind is using context variables. E.g. upon client application startup, set the client IP address as a USER_SESSION context variable, which then is available in context of the client connection / user session in e.g. triggers etc. -- With regards, Thomas Steinmaurer http://www.upscene.com Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: [firebird-support] Trailing spaces behaviour
Alex, > Hi All, > > I have found strange behaviour concerning trailing spaces - following > strings are equal: > > 'ABC ' = 'ABC' > > You can try the following query: > > /select/ > / case when 'ABC ' = 'ABC' then 1 else 0 end,/ > / case when cast('ABC ' as varchar(10)) = cast('ABC' as varchar(10)) > then 1 else 0 end,/ > / char_length('ABC '),/ > / char_length('ABC')/ > /from/ > / rdb$database/ > > This query was tested on FB v.1.5.6, 2.5.8 & 3.0.3 - while char length > (or strlen on 1.5) is different, the strings are always equal. > > Is this by design :) Additionally to Dimitry's reply. If you need a reliable way for comparison and want to treat trailing spaces as different, check out the IS [NOT] DISTINCT FROM clause. https://firebirdsql.org/refdocs/langrefupd21-distinct.html -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: AW: [firebird-support] Re: Trace not working
>> Hmm... what do you mean ? > >> There should be almost instant message about trace started with trace ID, >> but then messages will appear only when some events happens in database. > > Well, I started the trace and the system prompted the message about trace > start > its ID. But there was no output. Now I realized, that there is a difference > between the alias and the absolute database path in config. > > I used the alias name in config and the absolute path in my application. When > I > switched the application to the alias name I was able to get the correct trace > outputs. Is there a documentation about that? I couldn’t find anything about > it. 'database' in your trace configuration is basically a regex, thus without any wildcards, this results in an exact match being necessary to be picked up then by the database filter in the trace component on the server side. -- With regards, Thomas Steinmaurer http://www.upscene.com Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: [firebird-support] Moving DB from 32 bits to 64 bits
> I am only using some functions from the ib_udf.dll which is installed in > the \Firebird\Firebird_3_0\UDF and have built a little functions > calling functions of that dll there is no problem I suppose ? If you are talking about a PSQL-based stored function (new feature in 3.0), then you are right, this won't be a problem. -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: [firebird-support] Moving DB from 32 bits to 64 bits
> I am already under FB3 and I am only planning in case I will move to 64 > bits server If you are using your own or third-party UDFs, be aware that you will need UDF libraries compiled with 64 bit then. Other than that, a 64-bit server won't care about if a 32-bit or 64-bit client connects. -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: [firebird-support] Firing event when deleting in MON$ATTACHMENTS
Hi, > Yes in my delphi application I registered the event name > I tried POST_EVENT in a stored procedure but without DELETE FROM > MON$ATTACHMENTS statement > sometimes it is fired sometimes not. I didn't understand why > > > When I write the event name like this : > > 1-POST_EVENT 'DISCONNECT_USER'; > It is fired > > 2-POST_EVENT 'DISCONNECT_USER]'||:USR; (I splitt the event name using > "]" in the client in order to retrieve the event name part and the > username part) > It is not fired > > And when I back to 1 when it worked it is not fired at all Did you commit the transaction executing POST_EVENT in all scenarios? Events are delivered to the listening client upon transaction commit. -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: [firebird-support] Firing event when deleting in MON$ATTACHMENTS
> Hello, > > I am deleting user connected with: > DELETE FROM MON$ATTACHMENTS WHERE MON$ATTACHMENT_ID = :ID_CONNECTION; > > I need to use POST_EVENT in order to notify user that he has been disconnected > but don't found a trigger for MON$ATTACHMENTS to do it. Can you please help > me > ? > Thanks As Sean has mentioned, when you "delete" the attachment, your "communication channel" back to the user is basically gone. What you could do is to: * Run a stored procedure with the POST_EVENT * Wait X minutes * Execute the DELETE FROM MON$ATTACHMENTS statement The main problem here is that your "administration connection" need to post an event isolated to the targeted user, e.g. by including something unique for the user in the event name and the client application has proper events registered. -- With regards, Thomas Steinmaurer http://www.upscene.com Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: [firebird-support] Re: Cannot create sequence
> Solved. > I was missing to do GRANT USAGE ON SEQUENCE RDB$GEN_REPLICADOR TO [user] > WITH GRANT OPTION > (Sequence seems to be considered system one and dont get listed) Third-party tools may consider everything starting with 'RDB$' a system object. Possibly you may switch to a different name for being able to work with the object in your tool without any side-effects. -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: [firebird-support] Background auditing of Firebird database transactions
Hi, > Hi fellow Firebird users, > > Background: > > * Current Firebird version is 2.5 > > * Windows OS could be anything... > > * We are using C# with FirebirdClient ADO.NET Data Provider > > * The software we write extracts and monitors the Firebird database of > a third party application. > > * We do a major extract of data from the Firebird database, then > attempt to monitor for changes. > > Comment: > It appears to be very difficult for us to monitor the Firebird database for > changes. We have started to look into the Trace functionality introduced in > Firebird 2.5 to monitor for changes. This has helped, as we can relatively > easily detect what tables have changed from the Trace output. But this > involves > a lots of string parsing, which is really not nice. > > We have just started to look into other software like FbTraceManager to help > us, which appears to be a great tool for debugging issue, but I can't work out > if there is a way to integrate FbTraceManager into an existing application to > programmatically access the structured trace data. > > I've come across some slideshows on Auditing (different from Trace?) that make > it sound like there is other functionality there, but maybe not documented? > > Question: > Is there any way to detect table and row level changes, that could be > integrated into an existing C# application? Identifying the changed row IDs in > a table that has changed would be ideal... If you are interested in what e.g. other DBMS products name "Change Data Capture", to audit/monitor data changes, then a trigger-based solution might be an appropriate mechanism. This might be a bit hard to do with the Trace functionality, which is great for debugging, getting the TOP X slowest queries etc. IBLogManager is a third-party product for the trigger-based approach: http://www.upscene.com/database_auditing/firebird_and_interbase/ Of course, it is not too difficult to roll your own ... -- With regards, Thomas Steinmaurer http://www.upscene.com Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: [firebird-support] Restore performance (gbak service_mgr) whilst activating indices
> Hello Group, > > Sorry if this double posts, I received an error when Sending. > > I was provided with a largish (~27GB) Firebird 2.5 backup file (gbak) > yesterday. I have been restoring this on Windows 2012R2 running 2.5.6 Classic > using gbak with the -service_mgr switch. This particular VM only has a single > CPU core but it is connected to the SAN and isn't doing anything else. I am > remote desktop'd into the machine. > > I have been tracking the restore through several mechanisms: > * -v switch of gbak > * How large the file is on disk > * CPU utilisation (Resource Monitor) > * Disk I/O (Resource Monitor) > > The first 40GB or so of the restored FDB took about an hour or so. The I/O for > fb_inet_server.exe was consistently in the 30MB/s range (usually about 10 read > from the fbk and 20 write to the fdb file) > The next 10 GB took 10 hours. Now obviously when it gets to "activating and > creating deferred index xyz" it slows down, but I cannot see where the > bottleneck is. I have seen extended periods of about 1MB/s reads corresponding > with 5% CPU utilisation whilst activating some of those indices. I copied an > unrelated 20GB file in the same folder and it happily copied at 50MB/s so > there > is definitely capacity that isn't being used. > > I can see that when it activates indices on larger tables it is creating temp > files and these are at least being written to at 8+ MB/s. > > Although I get that activating the indices is going to be slower than > restoring > the data, I was expecting to see it either CPU and/or disk bound at any moment > in time. (Plenty of headroom for memory and network utilisation is very low). The restore process is bound to a single physical core. I've seen something similar in the past, where basically no resource (CPU, disk I/O - throughput + IOPS) being exhausted, thus not bound to available hardware. Do you have an Antivirus solution running affecting the restored Firebird database and/or temporary created files during restore? You may also vote for: http://tracker.firebirdsql.org/browse/CORE-2992 -- With regards, Thomas Steinmaurer http://www.upscene.com Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: [firebird-support] FreeAdhocUDF and Firebird 3 not working
Hi, > I have been using Firebird 2.5.7 64 bit on Windows 7 for sometime, also > using FreeAdhocUDF. I just installed Firebird 3.0.2.32703 64 bit and now > FreeAdhocUDF is no longer loading. > > I see that there is a support ticket in place for this exact bug, but so > far no resolution. This is a major problem as I will have to roll back > to 2.5 unless I find a work around. > > Has any one found a resolution for this problem? I mainly use the F_DVL > function to get around null values, so I guess I can use a case > statement (a lot of work though). It would be nice to get FreeAdhocUDF > working again, of course. Have you tried to contact the creators of FreeAdhocUDF? This is basically a third-party UDF library not being maintained by the Firebird project. -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: [firebird-support] Re: Sort error No free space found in temporary...
Hello Alexey, > Hi, > > I remember I answered incorrectly that time :) > > TempCacheLimit is always per server instance. Makes sense. Thanks for the clarification. :-) -- With regards, Thomas Steinmaurer http://www.upscene.com Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc. > For Classic it means it is allocated per each connection (since each > connection has own server process), for SuperClassic and SuperServer > temp space will be shared between connections, since there is the single > server process. > > Regards, > Alexey Kovyazin > IBSurgeon > www.ib-aid.com > > > > > On 02.09.2017 20:46, Thomas Steinmaurer t...@iblogmanager.com > [firebird-support] wrote: >> >> Hi Thomas, >> >> > From what i can read on the internet TempCacheLimit, is per server >> > process, and not per attachment. >> >> In SuperServer architecture, right, but if I remember correctly, you >> have mentioned in one of your first posts that you are using >> SuperClassic, right? >> >> > http://firebird.1100200.n4.nabble.com/TempCacheLimit-td4309475.html >> >> Talking about SuperServer. >> >> > >> https://www.mail-archive.com/firebird-support@yahoogroups.com/msg10199.html >> >> Talking about SuperClassic with answers that it is per connection. >> >> > >> > >> > I believe I have found the statements that caused the problem, first >> one >> > was executede about 400/second and contained a union, that was replaced >> > by union all in the hope that would eliminate the need for temp cache. >> >> UNION vs. UNION ALL. UNION is removing duplicates, thus some sort of >> applying DISTINCT with first sorting to remove duplicates is applied. >> >> DISTINCT, sorting, group by (internally first doing a sort) will >> internally produce temporary data. >> >> > The second statement is a really nasty dynamically build common table >> > expression containing upwards of 10 union.. Here we are in the process >> > of moving this search to Elastisearch. >> > >> > We just purchased FB TraceManager at our company to weed performance >> > issues - Thanks for a great product : ) >> >> Great to hear. Thanks. :-) >> >> -- >> With regards, >> Thomas Steinmaurer >> http://www.upscene.com/ >> >> Professional Tools and Services for Firebird >> FB TraceManager, IB LogManager, Database Health Check, Tuning etc. >> >> > >
Re: [firebird-support] Re: Sort error No free space found in temporary...
Hi Thomas, > From what i can read on the internet TempCacheLimit, is per server > process, and not per attachment. In SuperServer architecture, right, but if I remember correctly, you have mentioned in one of your first posts that you are using SuperClassic, right? > http://firebird.1100200.n4.nabble.com/TempCacheLimit-td4309475.html Talking about SuperServer. > https://www.mail-archive.com/firebird-support@yahoogroups.com/msg10199.html Talking about SuperClassic with answers that it is per connection. > > > I believe I have found the statements that caused the problem, first one > was executede about 400/second and contained a union, that was replaced > by union all in the hope that would eliminate the need for temp cache. UNION vs. UNION ALL. UNION is removing duplicates, thus some sort of applying DISTINCT with first sorting to remove duplicates is applied. DISTINCT, sorting, group by (internally first doing a sort) will internally produce temporary data. > The second statement is a really nasty dynamically build common table > expression containing upwards of 10 union.. Here we are in the process > of moving this search to Elastisearch. > > We just purchased FB TraceManager at our company to weed performance > issues - Thanks for a great product : ) Great to hear. Thanks. :-) -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: [firebird-support] Re: Sort error No free space found in temporary...
Hi, > Thanks for the advise Thomas I will increase the value. > After we changed TempCacheLimit to 567108864, we saw the problem once > under heavy load - for a very sourt perio this time. Increased TempCacheLimit value as a side-effect of serving more temp stuff through RAM instead of spilling onto disk, thus somehow makes sense to see the too many open files less frequent then. > Is it correct > understood that once TempCacheLimit was reached Firebird used /tmp/ > until the proccess reached the max open files? Sounds reasonable, yes. > Regarding TempBlockSize and TempCacheLimit do you or any other know of a > way to monitor if the configured value is too low or the limit is about > to be reached? No, but I became a bit rusty regarding Firebird lately. :-) > Since the server has plenty of RAM i was thinking about configuring > TempCacheLimit to 2, 4 or 6 Gb, if this has no negative effect? As you have mentioned that you are using SuperClassic, mind that TempCacheLimit is maximum memory for temp stuff PER client connections in this architecture, thus if configured too high with a high number of concurrent connections, you may end up in exhausting host RAM, resulting in swapping to disk if hitting boundaries. > Likewise if there is no negative effect, is there anything to gain from > increasing TempBlockSize from 2 megabytes to perhaps 4 or 6? I doubt you will see a real difference. Beside that, perhaps you are able to pin the SQL statement producing the temp data load e.g. by using the Trace API. -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: [firebird-support] Re: Sort error No free space found in temporary...
> Hey Thomas > Thanks for the answer. The command output is this: > Max cpu time unlimited unlimited seconds > Max file size unlimited unlimited bytes > Max data size unlimited unlimited bytes > Max stack size 8388608 unlimited bytes > Max core file size 0 unlimited bytes > Max resident set unlimited unlimited bytes > Max processes 514866 514866 > processes > Max open files 4096 4096 files > Max locked memory 65536 65536 bytes > Max address space unlimited unlimited bytes > Max file locks unlimited unlimited locks > Max pending signals 514866 514866 signals > Max msgqueue size 819200 819200 bytes > Max nice priority 0 0 > Max realtime priority 0 0 > Max realtime timeout unlimited unlimited us > > Max Open Files seems a bit low, is that the reason? Very likely, so try to increase the Max open files limit and restart the Firebird process. Increasing the limits might be a bit tricky, cause it may differ across Linux distros (to have new limits being persistent) and even more interestingly, how processes are started (e.g. via user based SSH session vs. started as systemd service etc.) Try to Google for your Linux distro. So, best check if new limits have been applied is always done via: sudo cat /proc//limits Hope this helps. -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: [firebird-support] Sort error No free space found in temporary...
Hello Thomas, > Hey > The other day we had a major slowdown on a database - one server with a single > database. Some of our queries received this error: > Sort error No free space found in temporary directories operating system > directive open failed > All queries that received this error used union in some way. > > > The Firebird log is shows a lot of errors at the same time. > Cannot dump the monitoring data operating system directive open failed Too > many > open files > And > Cannot initialize the shared memory region operating system directive open > failed Too many open files "Too many open files" usually means that the Firebird process isn't allowed to acquire more file handles from the OS. What is the output of? sudo cat /proc//limits > > > After this the following changes was made to the config > TempBlockSize = 2048576(changed from default value) > TempCacheLimit = 567108864(changed from default value) With TempCacheLimit, you basically tell Firebird how much RAM it is allowed to use for temporary data structure caused by sorting, group by etc. before spilling temporary stuff out onto disk. > (I took the values from the firebird_25_superclassic_64bit.conf from ib-aid) > > > However today same thing happened once again. Does anyone know the solution to > this? > > > TempDirectories in config is set to default, so I guess /tmp/firebird i used, > this disk has about 12Gb free space at the time of writing. Could all this > space have been consumed and causing the errors? If TempDirectories is commented out (thus not explicitly set), Firebird will use whatever the OS returns as disk location for temporary data. Do you monitor disk usage somehow to correlate e.g. increased disk usage with error message above? > > > A few weeks back we moved the database from a Windows Server 2008R2 to CentOs > to accommodate an increased load, on the database, before this we hand never > seen this kind of error. The windows server used a default firebird config > file > except for DefaultDbCachePages that was configured to 1024. > > > Firebird: SuperClassic 2.5.7 > Server: Virtual server, 16 cores, 128Gb of Ram, Multi disk SSD SAN. -- With regards, Thomas Steinmaurer http://www.upscene.com Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: [firebird-support] Foreign key different field type
> Hi, > > Can someone show me example when it is usefull to have different field > type in relation > > Sample > Create table test1 (id smallint not null primary key) > > Create table test2 (id1 integer) > > Alter table test2 Add foreign key (id1) references test1(id) > > Why this is not forbidden? Perhaps cause the referenced column in table test1 can only store a subset (SMALLINT) of the value range of the INTEGER used in test2? Even if this works at DDL time, I wonder how strict the optimizer then is in JOIN statements using an index. Haven't tried. At DDL time: What happens if you flip the used data types for ID/ID1? -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: [firebird-support] nbackup
Hello, > Hello, > > Is there a way to ask a database if someone is performing a nbackup? > > If it's in "the physical backup mode". > > I'm always paranoid if there is a power failure during a backup and it > gets stuck in that mode and I would like it to recover from such situation. > We have many hundreds of installations and it's difficult to monitor all. > > Firebird 2.5 on Windows Perhaps MON$DATABASE is delivering with the MON$BACKUP_STATE field? https://firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref-appx05-mondb.html -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: [firebird-support] Table Size
Ed, FB TraceManager parses gstat output and give you table/index size (MB), percentage of total database size etc. but actually does not have charting in that area. Check out screens here: http://www.upscene.com/fb_tracemanager/media * Table Stats Monitoring, and * Index Stats Monitoring -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc. > I have a number of growing databases on my server and would like to > occasionally see how much space each table is taking in the > database--there may be some log files that could be cleaned up now and > then. Many of my tables include blob fields, so this becomes different > than just doing record count times the amount of data in a record. > > Does anyone have any tools that they can recommend that could help me > easily manage this? > > much appreciated, > > Ed Dressel > > >
Re: [firebird-support] reset sysdba password without loose existing users
> Hi all, > > I need more help to archive this task. > > > I just copied the file flembed.dll and others in my directory(bin) > now i can connect to my database, with gsec or isql > > > but when i try to change de sysdba password in commande line, i got this > message : > > > SQL> alter user sysdba password 'mypassword' > Statement failed, SQLSTATE = 0A000 > feature is not supported > > > Maybe i missed somthing, thanks for your help Are you sure that you are connecting via the embedded DLL and not a regular Firebird server process? I have seen several environments where there have been Firebird Windows Services running in parallel and a faulty connect string pretty much resulted in using the wrong Firebird server. So, for your case, make sure that no other Firebird server is running, e.g. explicitly stopping them in the Windows services panel, in case you are on Windows. -- With regards, Thomas Steinmaurer http://www.upscene.com Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: [firebird-support] stale statements in MON$STATEMENTS
> On 02/12/16 20:59, Dimitry Sibiryakov s...@ibphoenix.com > [firebird-support] wrote: >> 02.12.2016 8:56, Hamish Moffatt ham...@risingsoftware.com [firebird-support] >> wrote: >>> I understand that the idle statement >>> means they have been prepared but not executed, but this shouldn't be >>> possible in my application source (prepare has never failed, and I >>> always execute). >> Yes, but after execution they are idle again until unprepared or freed. >> >> > > OK, thanks. I'm doing all my Firebird interaction with Qt and its IBASE > driver, so this should all be managed for me. It should free the > statements when they go out of scope. > > It's a bit suspicious that 100 of these statements were the same one, > "merge into...", so I'll have to follow that up. > > I also see a ton of connections on MON$ATTACHMENTS from the same thread > in my application (MON$REMOTE_PID, MON$REMOTE_ADDRESS both the same) > which also makes no sense. Closing a connection implicitly will free the underlaying statements, thus due to your explanations, it seems you are leaking (not closing) connections. -- With regards, Thomas Steinmaurer http://www.upscene.com Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: [firebird-support] How non-SYSDBA user can see connections by other users?
> SYSDBA users can see other connected users using monitoring tables but is it > possible to implement such feature for non-SYSDBA users? Are there > event/triggers that act uppon connecting and disconnecting and which can > insert > usual database records. Triggers no MON$ tables are not suitable because MON$ > tables are populated only during query time. The following users get the full picture when querying monitoring tables: * Database owner * SYSDBA * Users as a member of the special per database RDB$ADMIN role. Role needs to be specified at connect time. -- With regards, Thomas Steinmaurer http://www.upscene.com Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: [firebird-support] change /tmp/firebird lock dir default value
> Hi, > > > I’am running multiples instances of Firebird CS 2.5 x64. But all instances > uses the same “lock dir”. How I can change the "lock dir” default value > "/tmp/firebird”? > I will appreciate any help. To my understanding, this can get dangerous when you start to separate shared resources (e.g. lock tables) into different directories per instance. Once you start to access a database from several SC/CS instances, you might ask for corruption. See also: http://tracker.firebirdsql.org/browse/CORE-3268 Destination for the the temporary data (sort files etc.) can be configured per instance though. Check out firebird.conf. -- With regards, Thomas Steinmaurer http://www.upscene.com Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: AW: [firebird-support] High number of NULL attachments
> fbclient.dll is bundled with the client application. If I remember correctly, you need a client library 2.1+ to get that information popping up in MON$ATTACHMENTS -- With regards, Thomas Steinmaurer http://www.upscene.com Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc. > > -Tom > > On Tue, October 4, 2016 10:14 am, 'Gregor Kobler' g...@gksoft.ch > [firebird-support] wrote: >> Does the Client uses the right fbclient.dll or GDS32.dll? >> >> >> >> >> >> Best Regards >> >> >> Gregor >> >> >> >> >> >> Von: firebird-support@yahoogroups.com >> [mailto:firebird-support@yahoogroups.com] >> Gesendet: Dienstag, 4. Oktober 2016 08:41 >> An: firebird-support@yahoogroups.com >> Betreff: [firebird-support] High number of NULL attachments >> >> >> >> >> >> >> >> >> Hi all. >> >> >> I am running a FB 2.5.3 server to which a delphi application connects. We >> usually have 20-30 concurrent connections. >> I have inspected the MON$ATTACHMENTS table and noticed many of the records >> have NULL values in MON$REMOTE_PID and MON$REMOTE_PROCESS but not in >> MON$REMOTE_ADDRESS. >> >> >> Does that mean that the connection to the server is still open even if the >> application isn't? How can I mitigate that? >> Thanks. >> >> >> -Tom >> >> >> >> >> >> >> > > > > > > Posted by: "Tom" > > > ++ > > Visit http://www.firebirdsql.org and click the Documentation item > on the main (top) menu. Try FAQ and other links from the left-side menu > there. > > Also search the knowledgebases at > http://www.ibphoenix.com/resources/documents/ > > > ++ > > > Yahoo Groups Links > > >
Re: [firebird-support] High number of NULL attachments
Hi, > Hi all. > > > I am running a FB 2.5.3 server to which a delphi application connects. We > usually have 20-30 concurrent connections. > I have inspected the MON$ATTACHMENTS table and noticed many of the records > have NULL values in MON$REMOTE_PID and MON$REMOTE_PROCESS but not in > MON$REMOTE_ADDRESS. > > > Does that mean that the connection to the server is still open even if the > application isn't? How can I mitigate that? Are you using connections through the client library fbclient.dll or perhaps Jaybird/.NET? -- With regards, Thomas Steinmaurer http://www.upscene.com Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: [firebird-support] Can merge return AffecterRows?
> hi,I am Nobuo Watanabe. > I use FireBird 2.5.6 and FireBird2.5.3. > > Firebird2.5Language Reference about 'merge command' says: > Currently, the ROW_COUNT variable returns the value 1, even if more than one > record is modified or inserted. For details and progress, refer to Tracker > ticket CORE-4400 <http://tracker.firebirdsql.org/browse/CORE-4400>. > > I use ISQL,and set count on; > When I use 'merge' on ISQL2.5.3,then return 1 always. > but when I use 'merge' on ISQL2.5.6,then affected row count return. > > Is this right count?or is there any condition to get right row_count? Sounds a bit like http://tracker.firebirdsql.org/browse/CORE-4817 fixed in 2.5.6 and 3.0 -- With regards, Thomas Steinmaurer http://www.upscene.com Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: [firebird-support] Firebird 3 performances
> Hello, > > > I am testing Firebird 3 in order to upgrade an application from Firebird 2.5 > and I would like to know if there are known cases of Firebird 3 performances > that are worse than Firebird 2.5. During my tests, it seems that bulk > insertions take longer in Firebird 3. http://tracker.firebirdsql.org/browse/CORE-5302 -- With regards, Thomas Steinmaurer http://www.upscene.com Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: [firebird-support] After update trigger: the same values for new.somefiled and old.somefield
Hello Sean, >>> For some reason they both have the new value and I can't figure out why. >>> If I didn't misread all the articles, I've found, completely, then >>> they should be different, shouldn't they? >>> >>> >>> I'm using Firebird 2.5.2.26539. >> >> Any chance that another trigger is changing the value? > > I was thinking that myself, but Patrick said that it was the "old" value > which was being changed to the "new" value. Right. Perhaps NEW is changed to OLD? > > Another trigger (a BEFORE UPDATE trigger), in a single update > operation/chain, can only change the "new" value, nothing should be able to > change the "old" value. -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: [firebird-support] After update trigger: the same values for new.somefiled and old.somefield
Hi, > shouldn't the values of new.somefield and old.somefield be different in > an after update trigger, when the value of that field has definitely > changed? > > > For some reason they both have the new value and I can't figure out why. > If I didn't misread all the articles, I've found, completely, then they > should be different, shouldn't they? > > > I'm using Firebird 2.5.2.26539. Any chance that another trigger is changing the value? You might use the Trace API to find out what's going on ... -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: [firebird-support] Transaction in limbo
> Hi > > I have a database, which have had to instances where there have been > Transactions in limbo. > I know how to fix this. > > I am on the other hand unsure why they appear. > What can cause this? > What should/could I check, so I can eliminate this from occuring. Something went wrong in a distributed transaction spanning multiple databases. Prominent examples are e.g. replication tools. -- With regards, Thomas Steinmaurer http://www.upscene.com Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: [firebird-support] FB 3 - Adding anew User
Hello Fabian, > Sorted after reading "Initializing the Security Database" from > http://www.firebirdsql.org/file/documentation/release_notes/Firebird-3.0.0-ReleaseNotes.pdf > at page 116. I think there is a small error on the documentation, where it > says > " isql -user sysdba employee", the sentence works probably on Linux but on Ms > WIn 2012 Server R2 it requires the password to run. You need to shutdown every running regular Firebird server process, otherwise you won't end up in an Firebird 3.0 Embedded connection, which is needed if you want to initialize your 3.0 security database in SRP mode. -- With regards, Thomas Steinmaurer http://www.upscene.com Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc. > > Cheers, > Fabian > > - Original Message - > From: fabia...@itbizolutions.com.au [firebird-support] > To: firebird-support@yahoogroups.com > Sent: Monday, June 13, 2016 4:34 AM > Subject: [firebird-support] FB 3 - Adding anew User > > > > > > Hi All, > > I have configured the FB 3 server to only accept SRP connections and need to > add a new User to the security DB, I read GSEC is not the way, and I have > tried with isql but for some reason I am not having luck, I am trying the > following: > > > > I created a script into a file as follows: > > SET SQL DIALECT 3; > SET NAMES UTF8; > SET CLIENTLIB 'C:\Temp\FB32Bits\fbclient.dll'; (this dll is 32 bits dll, > but > the server is 64 bits, and I use the 32 bits dll because IBexpert is 32 bits) > > CONNECT '127.0.0.1/3050:My_New_DB_Configured_Properly_in_Databases.conf' USER > 'SYSDBA' PASSWORD 'NewPassNotMasterkey'; > > CREATE USER NUCLEUSCRM PASSWORD 'WhateverPassword' ; > > COMMIT work ; > > > The above fails when executed from IBExpert script executive, the error is: > > "Unsuccessful execution caused by a system error that precludes successful > execution of subsequent statements. > Your user name and password are not defined. Ask your database administrator > to set up a Firebird login." > > Its funny because IBexpert is connecting fine to the DB, and the following > config is in place: > > AuthServer = Srp > AuthClient = Srp > UserManager = Srp > > > So the only way to connect is with the new security model, and IBExpert is > connecting when restoring the DB, but when running script into the DB > something does not work. Any ideas? > > > > Cheers, > Fabian > > > > > > >
Re: [firebird-support] Re: Max RAM for Database Cache in SS FB 3.0 64bit is to small?
> 'Thomas Steinmaurer' t...@iblogmanager.com [firebird-support] a écrit : >> trsk...@yahoo.com [firebird-support] schrieb am 02.06.2016 06:44: >> >>> Where I can download v3.0.1 ? >> >> 3.0.1 hasn't been released yet, but snapshot builds are available here: >> http://www.firebirdsql.org/en/snapshot-builds/ >> > > the link is dead Works here. I get the entry page regarding snapshot builds for all branches and Windows/Linux. Anyway ... ;-) > http://web.firebirdsql.org/download/snapshot_builds/win/3.0 Thomas
Re: [firebird-support] Re: Max RAM for Database Cache in SS FB 3.0 64bit is to small?
trsk...@yahoo.com [firebird-support] schrieb am 02.06.2016 06:44: > Where I can download v3.0.1 ? 3.0.1 hasn't been released yet, but snapshot builds are available here: http://www.firebirdsql.org/en/snapshot-builds/ -- With regards, Thomas Steinmaurer http://www.upscene.com Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: [firebird-support] Re: FB 3.0, got problem when add field to Table with existing data
>> What value must it have? Add a default clause to this statement. Or add > > the field as nullable, populate with data and then alter to NOT NULL. > > I have tried to add field as nullable, then tried to alter to not null. > > UPDATE TMP_RPT SET ID1 = '5'; Commit; > (This statement is OK) > > > UPDATE RDB$RELATION_FIELDS SET RDB$NULL_FLAG = 1 > WHERE RDB$FIELD_NAME = 'ID1' AND RDB$RELATION_NAME = 'TMP_RPT'; > (This statement raised an error, FB 3.0 doesn't allow SysDBA user to change > System table) Firebird 3 rejects direct system tables modification. Use the supported ALTER DDL statement for managing NULLability of existing columns/domains. ALTER TABLE ALTER { DROP | SET } [NOT] NULL See Firebird 3 release notes p.68. -- With regards, Thomas Steinmaurer http://www.upscene.com Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: [firebird-support] download link for fb 3 embedded?
> 25.05.2016 12:23, korkl...@yahoo.it [firebird-support] wrote: >> where are the embedded packages for fb3? > > Not exists anymore. Read Release Notes about changes in server architecture. While this is true, that people should first read the release notes, IMHO we should make the transition for Embedded users more clear/prominent/present on the website, e.g. in the download area for 3.0 packages. Due to a missing downloadable Embedded package, a clear statement on what is needed for an Embedded deployment out of the regular server distribution might help as well. Perhaps in a dedicated chapter of the release notes (possibly I haven't read the release notes carefully myself *g*). I know Carlos' migration guide deals with that, but IMHO the release notes should make that clear as well. The current situation is a bit confusing for Embedded users. Just my thoughts. -- With regards, Thomas Steinmaurer http://www.upscene.com Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: [firebird-support] database unavailable
Hello Bill, > Used IB 10 years ago. Porting an SQL Anywhere app to FB 2.5. Going well > until I try to connect a client to the database - "database unavailable." I > have shared the DB folder on my Windows 7 machine, can browse to it from a > client machine (Win 10) and see Full Control permissions. Don't put your database into a shared folder. The clients must not have direct physical access to the database file, because they can play around taking physical copies then, worst case corrupting the database. Only the Firebird process needs physical access to the database file locally. Clients need to use a TCP/IP connect string then, e.g. myhost:c:\data\database.fdb, where c:\data\database.fdb is the location on the machine running the Firebird process. Or even better, use database aliases, configurable in a Firebird configuration file called 'aliases.conf', which allows you to define a mapping from a custom name to a physical location. E.g.: mydb = c:\data\database.fdb The custom name 'mydb' should then be used in the connect string, e.g. with myhost:mydb Hope this helps. -- With regards, Thomas Steinmaurer http://www.upscene.com Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc. I have disabled > my Trend AV on the Win7 server as well as put incoming and outgoing rules in > the firewall for FBserver.exe. > > > > I am using the ODBC client to connect via ADO which works fine on the Win7 > server. I've run out of things to try. Any suggestion would be greatly > appreciated. > > > > Thanks, > > > > Bill Skelton > > > > Landmark Data Systems, Inc. > > 2 Old River Place, Ste L > > Jackson, MS 39202 > > 601-362-0303 > > > > > > [Non-text portions of this message have been removed] > > > > > Posted by: "Bill Skelton" > > > ++ > > Visit http://www.firebirdsql.org and click the Documentation item > on the main (top) menu. Try FAQ and other links from the left-side menu > there. > > Also search the knowledgebases at > http://www.ibphoenix.com/resources/documents/ > > > ++ > > > Yahoo Groups Links > > >
Re: [firebird-support] IN OUT PARAMETERS
> In firebird is there an option to declare a parameter in a stored procedure > as an IN/OUT. (like ORACLE server has) > http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/parameter_declaration. > htm > > > > > > I want to send a parameter to a procedure, do some operation with it, and > return his new value. No, not with a single parameter. PSQL requires an explicit list of return parameters, of course with different parameter names as the input parameters. The language reference is available here: http://www.firebirdsql.org/refdocs/langrefupd25-ddl-procedure.html -- With regards, Thomas Steinmaurer http://www.upscene.com Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: [firebird-support] Why does "IF (NOT(EXISTS(SELECT 1..." not work as expected?
> Hello 'stwizard', > > Thursday, May 12, 2016, 12:46:25 AM, you wrote: > >> Here is my simple Stored Procedure. It simply looks for any >> address in the ADDRESS table that starts with ‘0 ‘ as in “0 SE >> ADAMS ST” and COUNT(*) how many time it might have been used in >> PER_ADDRESS and if COUNT() > 0 delete the links from the PER_ADDRESS table. > >> > >> The next line verifies that there is no remaining links in the >> PER_ADDRESS table and then deletes the record from the ADDRESS table. > >> > >> My problem is that even though ADDR_ID 347006 does not exist in the >> PER_ADDRESS table, the “IF (NOT(EXISTS(SELECT 1..” line thinks there >> is and skips the deletion of the record f orm the ADDRESS table. > > >> What might I be doing wrong? > > First of all, this is an executable SP, not intended to return a > result set, so get rid of the RETURNS parameters and declare variables > instead. While executable SPs *can* return a single-row result set, > with your for-loop, the only result you would get back would be the > values from the final iteration of the loop. But that's not the > reason for your unexpected results. > > In your INTO clause, you are missing the colon (:) markers > that are needed when variables or parameters are referred to in a DSQL > statement. I seem to recall that they are optional in v.2.5 (not > sure) but that would not be a reason for me to omit them. > > Your problem is your variables. With both local variables and > parameters that you are using like variables, you need to > > (1) Initialise your variables before starting the loop (they start out > as NULL) > and > (2) Re-initialise them at the end of the loop (otherwise, until the > next time the loop gets a "hit", the variables retain the values that > existed after the last "hit"). > > Also, once you fix those problems, I see no point in adding extra cost > by revisiting the PER_ADDRESS table to verify the non-existence of > the records you just deleted. If the delete had failed, you would > already be in an exception condition and would have jumped past that > block to the last END statement. > > You don't actually need ADDRESS1 in your working set, either, since > you don't do anything with it. > > With the correct initialisation/re-initialisation, you already have > the value of ADDR_ID at that point: either a genuine ID (if the > current ADDRESS format matches the condition) or the dummy value from the > initialisation (if no invalid addresses were found for that ADDR_ID in > PER_ADDRESS). > > CREATE PROCEDURE P_CLEAN_ADDR > AS > DECLARE ADDR_ID Integer = -1; > /* DECLARE ADDRESS VarChar(50) = ''; not needed */ > DECLARE PER_ADDR_CNT SmallInt = -1; > > begin > FOR SELECT A.ADDR_ID, > /* A.ADDRESS1, not needed */ > (SELECT COUNT(*) FROM PER_ADDRESS PA > WHERE PA.ADDR_ID = A.ADDR_ID) AS PER_ADDR_CNT > > FROM ADDRESS A > WHERE ADDRESS1 STARTING WITH '0 ' > INTO :ADDR_ID, /* :ADDRESS, */ :PER_ADDR_CNT > DO > BEGIN > IF (PER_ADDR_CNT > 0) THEN > begin > DELETE FROM PER_ADDRESS WHERE ADDR_ID = :ADDR_ID; > DELETE FROM ADDRESS WHERE ADDR_ID = :ADDR_ID; > end > -- re-initialise variables > ADDR_ID = -1; > /* ADDRESS = ''; */ > PER_ADDR_CNT = -1; > END > end ^^ Very exhaustive and a lot of useful advices ... ;-) He also could simply have a cascading delete foreign key constraint on PER_ADDRESS referencing ADDRESS. -- With regards, Thomas Steinmaurer http://www.upscene.com Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: [firebird-support] Simply bad, new is not always better. FB3 and ODBC
[snip] >> Also the post_event behaves different than the old firebird, the >> never changed mfc (c++) application gets it sometimes, in many cases >> not. Only the version of firebird is changed. > > Post_event was not changed in Firebird 3. Are you sure that your > customer's applications are loading the correct version of > fbclient.dll? This question is applicable to your other problems, > too. Looks a bit like: http://tracker.firebirdsql.org/browse/CORE-5210 -- With regards, Thomas Steinmaurer http://www.upscene.com Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: [firebird-support] EF6 - Why extra index created?
> I try the ordinary EF CodeFirst test with latest packages for new FB3. > > > >targetFramework="net452" /> >targetFramework="net452" /> > > > - > > public class Blog > { > public int id{ get; set; } > public string name { get; set; } > public DateTime created { get; set; } > public string comment { get; set; } > > public virtual List posts { get; set; } > } > > public class Post > { > public int id { get; set; } > public string content { get; set; } > > public int blogId { get; set; } > > public virtual Blog blog { get; set; } > } > > > > public TestContext() > : base(connection, true) > { > > } > > public DbSet Blog { get; set; } > public DbSet Posts { get; set; } > } > > ... > > Database.SetInitializer(new MigrateDatabaseToLatestVersion Configuration>()); > > - > > This code creates this table definition: > > CREATE TABLE "Posts" > ( > "id" Integer NOT NULL, > "content" Blob sub_type 1, > "blogId" Integer NOT NULL, > CONSTRAINT "PK_Posts" PRIMARY KEY ("id") > ); > ALTER TABLE "Posts" ADD CONSTRAINT "FK_Posts_Blogs_blogId" > FOREIGN KEY ("blogId") REFERENCES "Blogs" ("id") ON DELETE CASCADE; > CREATE INDEX "IX_Posts_blogId" ON "Posts" ("blogId"); > GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE > ON "Posts" TO SYSDBA WITH GRANT OPTION; > > > > BUT: why is created a "IX_Posts_blogId" index when the > "FK_Posts_Blogs_blogId" index created for FK? There are databases out there, e.g. MS SQL Server, which do not create an index for FK constraints behind the scene. I'm not really into the .NET world or EF in particular, but I wonder if the code responsible for creating the DDL is something in the Firebird .NET provider or EF in general. -- With regards, Thomas Steinmaurer http://www.upscene.com Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: [firebird-support] Fast Slow Stored procedure
Hi Jack, > Hi Thomas > > I did a trace as you suggested. > The slow SP shows these 3 consecutive statements showing a 20 min gap of > no activity > > 2016-05-04T14:25:30.5020 (3268:061B51F8) EXECUTE_PROCEDURE_START > C:\MAGUSDB\FBDB\SAMPLE1.FDB (ATT_45, SYSDBA:NONE, NONE, TCPv4:127.0.0.1) > C:\Magus\Magus C++\Latest Developement IBO Test\MagusManager.exe:8304 > (TRA_382, READ_COMMITTED | REC_VERSION | NOWAIT | READ_WRITE) > > Procedure CLEAR_BUILD_DXS: > param0 = integer, "0" > > > 2016-05-04T14:45:39.7880 (3268:061B51F8) PREPARE_STATEMENT > C:\MAGUSDB\FBDB\SAMPLE1.FDB (ATT_45, SYSDBA:NONE, NONE, TCPv4:127.0.0.1) > C:\Magus\Magus C++\Latest Developement IBO Test\MagusManager.exe:8304 > (TRA_382, READ_COMMITTED | REC_VERSION | NOWAIT | READ_WRITE) > > Statement 605: > --- > Insert into TEMP_IMPORT_DXS (DXS_NO, ABS_NO, DB, DW, VALUE1, USED, > SPACE, UOP, EOL) > select DXS_NO, ABS_NO, DB, DW, VALUE1, USED, SPACE, UOP, EOL > from IMPORT_DXS > ^^^ > > The fast SP there is a only a small gap > > 2016-05-05T07:24:05.3290 (3268:061B51F8) EXECUTE_PROCEDURE_START > C:\MAGUSDB\FBDB\SAMPLE1.FDB (ATT_52, SYSDBA:NONE, NONE, TCPv4:127.0.0.1) > C:\Magus\Magus C++\Latest Developement IBO Test\MagusManager.exe:5636 > (TRA_417, READ_COMMITTED | REC_VERSION | NOWAIT | READ_WRITE) > > Procedure CLEAR_BUILD_DXS: > param0 = integer, "0" > > > 2016-05-05T07:24:13.4540 (3268:061B51F8) PREPARE_STATEMENT > C:\MAGUSDB\FBDB\SAMPLE1.FDB (ATT_52, SYSDBA:NONE, NONE, TCPv4:127.0.0.1) > C:\Magus\Magus C++\Latest Developement IBO Test\MagusManager.exe:5636 > (TRA_417, READ_COMMITTED | REC_VERSION | NOWAIT | READ_WRITE) > > Statement 605: > --- > Insert into TEMP_IMPORT_DXS (DXS_NO, ABS_NO, DB, DW, VALUE1, USED, > SPACE, UOP, EOL) > select DXS_NO, ABS_NO, DB, DW, VALUE1, USED, SPACE, UOP, EOL > from IMPORT_DXS > ^^^ > > Can you make any suggestion as to what happened for 20 min No, sorry. You either have removed parts of the trace or you need a different trace configuration, particular the various *_FINISH entries, which usually print out the execution time. -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: [firebird-support] Fast Slow Stored procedure
> What are the possible reason for a SP that normally runs in approx 20 seconds > then Occasionally takes 15 minutes, (or a method of trying to find the cause > of > the problem). > > > > Firebird Details > Server Version: WI-V2.5.5.26952 Firebird 2.5 32 bit Superserver, > CpuAffinityMask = 1 in firebird.conf A few ideas: * Some sort of lock contention at transaction level caused by concurrent read/write requests on the same (range) of records running transaction in WAIT and/or NO RECORD_VERSION mode * Firebird 2.5 Superserver is basically single CPU/core bound for a single database environment, but IMHO not an explanation for that large difference * Watch out typical response time influencers like execution plan, I/O fetches, records fetched etc ... * Query the monitoring tables when your SP is stalled and/or run a continuous trace through the Trace API -- With regards, Thomas Steinmaurer http://www.upscene.com Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: [firebird-support] Firebird 3.0 and GDS32.DLL
Hello Michael, > Hi > > I have succesfully installed Firebird 3.0 on my home computer. > I can use our program. We have 3 main programs. Two are using IBX component > and > the last uses IBObjects. > On my local mashine it runs smoothly. > > I have then installed Firebird 3.0 alongside Firebird 2.5 and 2.1 on a > server. > Using Database Workbench I can connect to all 3 types of databases. No > problem. > > > But the moment I try to start any program, I get this instantly: > > Exception EIBClientError in module EasyPOSSalg.EXE at 001ED40. > Interbase library gds32.dll not found in the path. PLease install Interbase to > use this functionality. > > I have located gds32.dll in my Windows\System32 folder. > I have tried taking FBClient.DLL from FB3.0 and copy and rename to same > folder. > > I have tried taking the gds32.dll from my local machine. > All ends with the same. > > The gds32.dll has a size of 1.739.776. Same as FBClient.DLL > > What am I missing here? Possibly a bit-ness mismatch e.g. Firebird 64-bit client library vs. your 32-bit client application? -- With regards, Thomas Steinmaurer http://www.upscene.com Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: [firebird-support] Re: Migrating from Super Classic FB 2.5.2 to SuperServer FB 3.0 could have a problems
Hi, > This is the Query plan from SS FB 3.0 > > PLAN (GET_STCK NATURAL) > > and here is the Query plan from SC FB 2.5.2 > > PLAN (G_T3 NATURAL)(M_TT INDEX (RDB$PRIMARY2))(A INDEX (IDX_M_GD3))(A > INDEX (IDX_M_GD3))SORT (JOIN (B INDEX (TM_D), A INDEX (T2_T1), C INDEX > (RDB$PRIMARY25), D INDEX (RDB$PRIMARY21), E INDEX (RDB$PRIMARY24), F > INDEX (RDB$PRIMARY23), G INDEX (RDB$PRIMARY22)))(M_TT INDEX > (RDB$PRIMARY2))(A INDEX (IDX_M_GD3))(A INDEX (IDX_M_GD3))SORT (JOIN (B > INDEX (T1_M_DL), A INDEX (T2_T1), C INDEX (RDB$PRIMARY25), D INDEX > (RDB$PRIMARY21), E INDEX (RDB$PRIMARY24), F INDEX (RDB$PRIMARY23), G > INDEX (RDB$PRIMARY22)))(M_TT INDEX (RDB$PRIMARY2))(A INDEX > (IDX_M_GD3))(A INDEX (IDX_M_GD3))SORT (JOIN (B INDEX (T1_M_DL), A INDEX > (T2_T1), C INDEX (RDB$PRIMARY25), D INDEX (RDB$PRIMARY21), E INDEX > (RDB$PRIMARY24), F INDEX (RDB$PRIMARY23), G INDEX (RDB$PRIMARY22)))SORT > ((G_T3 NATURAL)) > > This could be a big problem, all queries/stored procedures/triggers must > be test again for its performance. Are you sure that you didn't deactivate indexes during the restore? -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: [firebird-support] SELECT WITH LOCK, WAIT transaction, deadlock
Hi, > 2016. 04. 28. 21:22 keltezéssel, Ann Harrison aharri...@ibphoenix.com > [firebird-support] írta: >> Okay... What's the requirement that keeps you from using generators? > > I need number generators which give back sequence numbers without > skipping and repeating. But don't know how many needed. The number of > needed generators depend on the customers(X Ltd., Y Ltd, ...). This is > why I want to store the generators in a table. > >> Do you intend to grab the number in a transaction that does other work? > > The transaction do other work before grab the number and after grab the > number. The whole thing is in a (one) stored procedure. (Works like a > charm except deadlocks.) > >> What is the concurrency requirement? > > If I understand your question correctly... I don't think will be high > load but I test is because not want to see deadlock in production. > >> What you're thinking about doing >> will serialize the generation of identifiers and all other actions of >> those transactions. I'm not totally convinced that using >> ReadCommitted/wait will create an auditable series of numbers in the >> case where a transaction fails. > > For testing purposes the test app insert the generated numbers into a > one field table which have a unique constraint. I not see any error > except the deadlocks. I don't see deadlocks with the following test case. 1) Create some tables and a stored procedure doing the access + increment CREATE TABLE GENERATOR_TABLE ( ID BIGINT NOT NULL ); CREATE TABLE GENERATOR_TABLE_LOG ( IDBIGINT NOT NULL, ID_NEWBIGINT NOT NULL, ID_DIFF BIGINT NOT NULL ); ALTER TABLE GENERATOR_TABLE ADD CONSTRAINT PK_GENERATOR_TABLE PRIMARY KEY (ID); ALTER TABLE GENERATOR_TABLE_LOG ADD CONSTRAINT PK_GENERATOR_TABLE_LOG PRIMARY KEY (ID); SET TERM ^^ ; CREATE PROCEDURE P_INCREMENT returns ( ID BigInt) AS BEGIN SUSPEND; END ^^ SET TERM ; ^^ SET TERM ^^ ; ALTER PROCEDURE P_INCREMENT returns ( ID BigInt) AS declare i bigint; declare id_new bigint; begin i = 1; while (i <= 1) do begin in autonomous transaction do begin select id from generator_table with lock into :id; id_new = id + 1; insert into generator_table_log (id, id_new, id_diff) values (:id, :id_new, :id_new - :id); update GENERATOR_TABLE set id = :id_new where id = :id; i = i + 1; end end suspend; end ^^ SET TERM ; ^^ COMMIT; INSERT INTO GENERATOR_TABLE (ID) VALUES (0); COMMIT; The autonomous transaction stuff is used to split up the look into smaller chunks from a perspective of the other isql sessions, otherwise conccurent transactions will wait, until the entire SP is finished from the isql session. 2) Fire up e.g. 5 isql sessions and execute the following concurrently: SET TRANSACTION READ WRITE ISOLATION LEVEL READ COMMITTED RECORD_VERSION WAIT; EXECUTE PROCEDURE P_INCREMENT; 3) Result SQL> select count(*), min(id_new), max(id_new) from generator_table_log; COUNT MIN MAX = = = 5 1 5 No gaps, no deadlock messages, no pk/unique violation in the log table etc ... It is crucial that accessing the central generator table is tunneled through a single access point. The SP in our case. Any chance that anything else is accessing the table in your scenario resulting in the deadlock / write update conflict? -- With regards, Thomas Steinmaurer http://www.upscene.com Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: [firebird-support] SELECT WITH LOCK, WAIT transaction, deadlock
> 2016. 04. 28. 14:55 keltezéssel, Dimitry Sibiryakov s...@ibphoenix.com > [firebird-support] írta: >> 28.04.2016 13:41, Gabor Boros gaborbo...@yahoo.com [firebird-support] wrote: >>> The message is: >>> deadlock >>> update conflicts with concurrent update >>> concurrent transaction number is ... >> >> That's an expected behavior. > > Are you sure? > > In the language reference for "isc_tpb_read_committed + isc_tpb_wait" I > see this: "Update conflict exceptions can never be raised by an explicit > lock statement in this TPB mode." > > http://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-dml-select.html#fblangref25-dml-with-lock You have mentioned that you are using Read Committed without record version. What happens if you are running Read Committed WITH record version? -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc. ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ 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: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] FB 3.0, how to get IDENTITY of last inserted row
Hello Zoran, > > In FB 2.5 I am using > > insert into MyTable (col1, col2) values(val1, val2) returning Id > > where Id is autoinc column (sequence generator + before insert trigger). > > How do I do it in FB 3.0? It doesn’t like “returning Id”. It displays > this error: SQLDA error. Wrong number of parameters (expected 1, got 0) > > I have Id defined as IDENTITY. Can I get INDENTITY for the last inserted > row without using SELECT GEN_ID(generator-name, 0) AS ID FROM RDB$DATABASE ? > > I am trying this with Firebird Maestro 15.11.0.1 (supports FB 3) on Win > Server 2012 R2. With FB 2.5 all works as expected. The following works fine for me with FB3 (server + client library): create table t1 ( id integer generated by default as identity primary key , fname varchar(15) ); commit; In isql: C:\Firebird\Firebird_300_4050_x86>isql -user sysdba -password masterkey fb3test.fdb Database: fb3test.fdb, User: SYSDBA SQL> insert into t1 (fname) values ('hugo') returning id; ID 15 So, in general it works. ;-) -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc. ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ 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: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] conect flamerobin with Firebird3
> Create a firebird 3 bd in another user who is not sysdba > and create a role called sysdba without permission > and when I try to connect from flamerobin you give me the following error > message > > *** IBPP::SQLException *** > Context: Database::Connect > Message: isc_attach_database failed > > SQL Message : -902 > Unsuccessful execution caused by a system error that precludes successful > execution of subsequent statements > > Engine Code: 335544745 > Engine Message : > Your login SYSDBA is same as one of the SQL role name. Ask your database > administrator to set up a valid Firebird login. > unknown ISC error 335545081 > > As I can overcome this error? Are you sure that FlameRobin picks up the FB 3 client library? -- With regards, Thomas Steinmaurer http://www.upscene.com Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: [firebird-support] How to get FB 3.0 x64 work as embeded version?
Hello James, > After FB 3.0 released, I am trying to immigrate my DB application to this new > version. > > > My DB app is running with embeded version before, so now I am still trying to > get it work under embeded mode. > I tried FB 3.0 x32, it works as embedded version well after I created a new > user SYSDBA (as in FB 2.5). > But for x64 package, I got error like this: > > > Error message: > [FireDAC][Phys][FB]-314. Cannot load vendor library [fbclient.dll or > fbembed.dll]. fbclient.dll has unsupported architecture [x64]. Required [x86]. This looks to me like you are running/compiling your client application as 32-bit trying to load fbclient.dll 64-bit, which obviously does not work. The bitness of your client application and the Firebird client/embedded DLL must match. -- With regards, Thomas Steinmaurer http://www.upscene.com Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc. > The specified module could not be found. > Hint: check it is in the PATH or application EXE directories, and has x86 > bitness. > > > What I have done (same as x32 package): > 1. Unzip FB3.0 x64 zip file to a folder. > 2. Run isql to create a new user SYSDBA with password masterkey. > 3. Copy my DB application to this folder. > 4. Run this application and got error. > > > OS: Windows 7 x64 Ultimate English > FB: No firebird server was installed as service. > DB: FDB file is upgraded to new format v12.0. > > > > > Best regards, > > > James
Re: [firebird-support] Backup with admin rights is prohibited?
> 2016. 04. 22. 15:57 keltezéssel, Gabor Boros gaborbo...@yahoo.com > [firebird-support] írta: >> 2016. 04. 22. 15:37 keltezéssel, Dimitry Sibiryakov s...@ibphoenix.com >> [firebird-support] írta: >>> 22.04.2016 15:34, Gabor Boros gaborbo...@yahoo.com [firebird-support] wrote: >>>> Now tried to do a backup (gbak -B -T >>>> -USER MYADMIN MYDB /home/user/MYDB.bak) and gbak say "You must be either >>>> SYSDBA or owner of the database". >>> >>>You forgot to mention role ADMIN in the command line. >> >> With "gbak -B -T -USER MYADMIN -ROLE RDB$ADMIN MYDB /home/user/MYDB.bak" >> got the same error. > > Other idea? Honestly, IMHO, if the user MYADMIN is a member of the RDB$ADMIN role in MYDB, then your gbak call should work. -- With regards, Thomas Steinmaurer http://www.upscene.com Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: [firebird-support] Database performance slowdown
Hi Neil, > We are explicitly starting and committing our own transactions rather than > relying on any auto-commit behaviour. > > We are running Classic (sorry I should have told you that already). > > I'll get our devs to checkout the MON$ tables and see what additional info > can be gleaned from them. > > A current gstat -h output is this below, we experienced the slowdown a > little while ago tonight. > > Database header page information: > Flags 0 > Checksum12345 > Generation 2240486 > Page size 8192 > ODS version 11.2 > Oldest transaction 1616642 > Oldest active 2143185 > Oldest snapshot 2143185 > Next transaction2168081 > Bumped transaction 1 > Sequence number 0 > Next attachment ID 72398 > Implementation ID 26 > Shadow count0 > Page buffers0 > Next header page0 > Database dialect3 > Creation date Mar 26, 2016 10:35:08 > Attributes force write > > Variable header data: > Sweep interval: 0 > *END* The oldest transaction (limbo or a larger transaction got rolled back) is way behind compared to oldest active and oldest snapshot. This can cause performance issues, because upon starting a new transaction, the transaction holds a private copy of the so-called TIP (transaction inventory page), which gets bigger and bigger with an increasing gap of the transaction counters and out-dated record versions won't get garbage collected as well. Your sweep task in the night does not seem to be able to do its job, because otherwise the oldest transaction should move forward. Ideally you schedule the sweep in a maintenance window ideally with zero connections or at least low load. Another reason why the oldest transaction can get stuck is a (failed) distributed transaction (aka 2PC). This needs manual intervention through gfix to resolve such a transaction either as committed or rolled back. Any chance to you are using something which spans a single transaction across several databases? E.g. replication comes to my mind. -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc. ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ 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: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] Installing Firebird 3.0 alongside Firebird 2.5 and 2.1
> Michael, > >> I have a testserver currently running both Firebird 2.1 and 2.5. >> I need this to support and test various situations with different firebird >> versions. >> >> Now I would like to install Firebird 3.0 manually as well, so I can have 2.1, >> 2.5 and 3.0 on the same server. >> 2.1 listens on port 3050, 2.5 listens on 3060 and I have set 3.0 to listen on >> 3070. >> >> So far so good. >> I then executed the install_service.bat Firebird_3_0 >> I can now see its running in process manager. >> >> But I'm having problems connecting to the server. >> Using Database Workbench to create a server entry I instantly get >> >> connection rejected by remote interface >> >> Sinse security is handled quite differently in Firebird 3.0 I suspect I am >> missing something here. >> >> Could someone either give me a step-by-step instructions to install Firebird >> 3.0 manually or point me to places where I can read some about it. >> >> >> On a difference machine I did run the installer, and I did get Firebird 3.0 >> up >> and running. But I need it on this server alongside the other firebird >> installations > > For test purposes I tend to use Firebird 32-bit, because most/all of my client > applications are 32-bit, thus I can easily point the client application to the > targeted 32-bit fbclient.dll. > > Step-by-step: > > * Extract the FB 3 ZIP distribution > * Change the tcp port to 3070 in firebird.conf > * The security database is not initialized when installing from ZIP, thus we > need a few additional steps > - We need an embedded connection with Firebird 3, thus stop ALL Firebird > processes including 2.1, 2.5 etc. > - Go to FB 3 install directory, and type: > > isql -u user sysdba employee Of course: isql -u sysdba employee Sorry. Thomas
Re: [firebird-support] Installing Firebird 3.0 alongside Firebird 2.5 and 2.1
Michael, > I have a testserver currently running both Firebird 2.1 and 2.5. > I need this to support and test various situations with different firebird > versions. > > Now I would like to install Firebird 3.0 manually as well, so I can have 2.1, > 2.5 and 3.0 on the same server. > 2.1 listens on port 3050, 2.5 listens on 3060 and I have set 3.0 to listen on > 3070. > > So far so good. > I then executed the install_service.bat Firebird_3_0 > I can now see its running in process manager. > > But I'm having problems connecting to the server. > Using Database Workbench to create a server entry I instantly get > > connection rejected by remote interface > > Sinse security is handled quite differently in Firebird 3.0 I suspect I am > missing something here. > > Could someone either give me a step-by-step instructions to install Firebird > 3.0 manually or point me to places where I can read some about it. > > > On a difference machine I did run the installer, and I did get Firebird 3.0 up > and running. But I need it on this server alongside the other firebird > installations For test purposes I tend to use Firebird 32-bit, because most/all of my client applications are 32-bit, thus I can easily point the client application to the targeted 32-bit fbclient.dll. Step-by-step: * Extract the FB 3 ZIP distribution * Change the tcp port to 3070 in firebird.conf * The security database is not initialized when installing from ZIP, thus we need a few additional steps - We need an embedded connection with Firebird 3, thus stop ALL Firebird processes including 2.1, 2.5 etc. - Go to FB 3 install directory, and type: isql -u user sysdba employee In isql: create user sysdba password 'yoursecretpwd'; commit; exit; Your security database has now 1 user SYSDBA with the new SRP security/authentication in place * Being still in FB 3 install directory, now install the FB 3 service (adapt service name properly. I tend to include the version, port and bitness in the service name): install_service.bat Firebird_300_3070_x86 * Start all other Firebird processes (2.1, 2.5 etc.) again * The new SRP authentication makes the usage of the 3.0 fbclient.dll mandatory! I guess in your case, DBW was simply using an older FB client library. So, in Database Workbench, upon server registration, explicitely provide the path to 32-bit fbclient.dll of Firebird 3. You could also run the Firebird 3.0 server in some sort of legacy authentication mode where you can use an older fbclient.dll, but personally, for test purposes, I would like to try out the new stuff. ;-) Hope this helps. -- With regards, Thomas Steinmaurer http://www.upscene.com Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc. > > Thank you > Michael >
Re: [firebird-support] Database performance slowdown
Neil, > We are investigating an issue with one of our systems where it gradually > slows down during the course of a day. > > We have our epos system that we write ourselves using Delphi XE2 and > the IBX components. How does your transaction model/layer look like in your client application with IBX? If you are not starting an explicit transaction yourself, IBX is running in "auto commit" mode, wrapping each statement into it's own transaction context and commit the statement with a commit retaining, which basically preserves the physical transaction context and makes your transaction counters (OIT/OAT/OST) stuck. Any chance to run gstat -h when your experience such a response time drop? > We have a group of tables that are updated during the end of sale save > process. > > Typically this whole save process takes milliseconds, but on one > particular site we see it taking several seconds and occasionally taking > up to 35 seconds. > > We have done some digging into the behaviour of Firebird, we are > currently using v2.5, and it appears that Firebird needs to check the > entire chain of back versions when reading a record from the database. > Is this the cooperative Garbage Collection process running? If you are running Classic or SuperClassic architecture, then yes. In these architectures, each statement is also doing garbage collection in its execution context. > It seems like it would be logical for a lookup to only search back > records until it finds the record that is supposed to be visible to it, > but it appears to go all the way back. Visible from a transaction context. Beside the transaction ID, also the used isolation level needs to be taken into account. With using commit retaining behind the scenes, e.g. due to auto commit, you basically end up in accumulating back record versions. > This causes us a slowdown if there are a lot of back versions still > active, and flags up data as corrupted if something untoward has > happened to them, even if the current committed version of the record > seems to be valid. > > Can anyone confirm this behaviour and any way to work around it? To be sure, if the slowdown for a single statement is driven by cooperative garbage collection, you can use the Trace API and its detailed statistics output (something like "backouts", "purges", "expunges") per statement. The MON$RECORD_STATS monitoring table is another source for that information, if your statement is still available through MON$ tables. -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc. ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ 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: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] Re: Connecting to Firebird database from two or more pc with same username
> On Mon, Apr 18, 2016 at 10:03 AM, 'Thomas Steinmaurer' t...@iblogmanager.com > [firebird-support] wrote: > >> > On Thu, 14 Apr 2016 12:28:41 +0200 (CEST), 'Thomas Steinmaurer' >> > t...@iblogmanager.com [firebird-support] > > >> > wrote: >> >> >> >> >> >> Yep. If one want to have some sort of client login/user available on >> >> the server-side, which might be totally different to the Firebird >> >> user used upon connect time, a common approach is to put that login >> >> name into a context variable via RDB$SET_CONTEXT(...) and query that >> >> in context of the connection with RDB$GET_CONTEXT(...). Needless to >> > > >> > Virgo Pärna wrote: > >> >> > Unless I'm misunderstanding it, it is not possible to read >> > context variable from another connection? >> >> No. There are two "namespaces" USER_SESSION and USER_TRANSACTION which >> also define the visibility of context variable values. >> >> Check out: >> http://www.firebirdsql.org/refdocs/langrefupd25-intfunc-set_context.html > > > Maybe I'm confused, but I think Virgo is asking about two different > connections using the same > user credentials, not a an application server with a single connection to > the database and multiple > connections from the client to the application. Different connections have > different USER_SESSION > namespaces even if they use the same user credentials, I think. Context variables values are separated by physical connection/transaction and not credentials used at connect time. Regards, Thomas
Re: [firebird-support] Re: Connecting to Firebird database from two or more pc with same username
> On Thu, 14 Apr 2016 12:28:41 +0200 (CEST), 'Thomas Steinmaurer' > t...@iblogmanager.com [firebird-support] > wrote: >> >> >> Yep. If one want to have some sort of client login/user available on >> the server-side, which might be totally different to the Firebird >> user used upon connect time, a common approach is to put that login >> name into a context variable via RDB$SET_CONTEXT(...) and query that >> in context of the connection with RDB$GET_CONTEXT(...). Needless to > > Unless I'm misunderstanding it, it is not possible to read > context variable from another connection? No. There are two "namespaces" USER_SESSION and USER_TRANSACTION which also define the visibility of context variable values. Check out: http://www.firebirdsql.org/refdocs/langrefupd25-intfunc-set_context.html -- With regards, Thomas Steinmaurer http://www.upscene.com Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc. > > -- > Virgo Pärna > virgo.pa...@mail.ee > > > > > Posted by: Virgo =?UTF-8?Q?P=C3=A4rna?= > > > ++ > > Visit http://www.firebirdsql.org and click the Documentation item > on the main (top) menu. Try FAQ and other links from the left-side menu > there. > > Also search the knowledgebases at > http://www.ibphoenix.com/resources/documents/ > > > ++ > > > Yahoo Groups Links > > >
Re: AW: [firebird-support] How to interprete gstat-results
Hi Olaf, > Database "D:\INTERBASE\IFLS.GDB" > > Database header page information: > >Flags 0 > >Generation 2939162 > >System Change Number0 > >Page size 4096 You might consider increasing to 8K, especially when the tree depth of an index starts to become > 3. To be checked with gstat -i. >ODS version 12.0 Indeed, the database was created with Firebird 3. ;-) >Oldest transaction 2893677 The "oldest interesting" transaction (OIT) in a state other than committed, e.g. also rolled back. There are no record versions for transactions < 2893677 available (cause they have been garbage collected), thus one could say why the marker is called "interesting". >Oldest active 2929833 The oldest transaction currently active/running. This is the lowest transaction ID which should be visible in MON$TRANSACTIONS. >Oldest snapshot 2878316 This is the oldest transaction started in snapshot mode. Hope I remembered that correctly. >Next transaction2938824 Transaction id for the next new transaction. >Sequence number 0 > >Next attachment ID 87910 > >Implementation HW=Intel/i386 little-endian OS=Windows > CC=MSVC > >Shadow count0 > >Page buffers0 The default value in firebird.conf is used for the size of the page cache of the database. Might be too small, depending on your use case. >Next header page0 > >Database dialect3 > >Creation date Mar 7, 2016 18:33:37 > >Attributes force write > > > >Variable header data: > >Sweep interval: 5000 That's not a good idea. Because if the difference of OIT and OST (oldest snapshot) reaches that threshold, a sweep will kick in, visits all records in the database and removes out-dated record versions which are not interesting for other transactions anymore. Regarding OIT/OAT movement/gaps etc., commit retaining is one of your #1 enemies. Usually used behind the scene by drivers with auto commit = ON. -- With regards, Thomas Steinmaurer http://www.upscene.com Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc. > >*END* > > > > > > > > Von: firebird-support@yahoogroups.com > [mailto:firebird-support@yahoogroups.com] > Gesendet: Donnerstag, 14. April 2016 14:03 > An: firebird-support@yahoogroups.com > Betreff: Re: [firebird-support] How to interprete gstat-results > > > > > >> Hello @ll, >> >> >> >> we are using firebird 3.0 RC2 and have some questions about open >> transactions. (Clients connect over odbc) The gstat output gives 7 >> transactions between the oldest and the current transaction, if we look > into >> the monitoring table, we see only 10 transactions. The "oldest one" we >> cannot find in this table. What is the oldest transaction and which roll >> does the garbage collector play? If we have many open transactions, the >> performance is bad. What role plays the attachments? What is the best way > to >> check the performance bottleneck in such cases? > > Please post an entire gstat -h output. > > -- > With regards, > Thomas Steinmaurer > <http://www.upscene.com> http://www.upscene.com > > Professional Tools and Services for Firebird > FB TraceManager, IB LogManager, Database Health Check, Tuning etc. > > > >
Re: [firebird-support] How to interprete gstat-results
> Hello @ll, > > > > we are using firebird 3.0 RC2 and have some questions about open > transactions. (Clients connect over odbc) The gstat output gives 7 > transactions between the oldest and the current transaction, if we look into > the monitoring table, we see only 10 transactions. The "oldest one" we > cannot find in this table. What is the oldest transaction and which roll > does the garbage collector play? If we have many open transactions, the > performance is bad. What role plays the attachments? What is the best way to > check the performance bottleneck in such cases? Please post an entire gstat -h output. -- With regards, Thomas Steinmaurer http://www.upscene.com Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: [firebird-support] Connecting to Firebird database from two or more pc with same username
> On 14/04/2016 08:26, 'Thomas Steinmaurer' t...@iblogmanager.com > [firebird-support] wrote: >> >> With a single user you basically can't distinguish people on the >> server side through CURRENT_USER etc. >> > We use one Firebird user for the web server, and then one each for each > of several batch processes, so if we poke around in the database we can > see at least which part of the system any problematic (eg very long > running) user sessions or transactions or queries belong to. Yep. If one want to have some sort of client login/user available on the server-side, which might be totally different to the Firebird user used upon connect time, a common approach is to put that login name into a context variable via RDB$SET_CONTEXT(...) and query that in context of the connection with RDB$GET_CONTEXT(...). Needless to say, for pooled connections in a middleware, one needs to take care of updating the context variable value which each connection aquisition. -- With regards, Thomas Steinmaurer http://www.upscene.com Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: [firebird-support] Connecting to Firebird database from two or more pc with same username
> Hello, > > Il 14/04/2016 09.26, 'Thomas Steinmaurer' t...@iblogmanager.com > [firebird-support] ha scritto: >> >> I guess you mean with the same Firebird user name? Or are you using >> Windows trusted authentication? >> > > Oops, sorry. I mean the same Firebird username. > I notice that in an LAN with three users, all users was authenticated > with the same Firebird username at the same time! > I did not notice any malfunction, but I doubt that it is not safe and is > the bearer of impending problems on the data. > >> In general this is fine of course, even many people are using SYSDBA >> for regular connects, although this is not a good idea from a security >> perspective. >> > > Ok but, as you say, and if I understand correctly, it is safe and > Firebird is imperishable its data, even if it gets the data from > different PCs at the same time and with the same FB user name. It's right? >From a pure Firebird POV, yes. -- With regards, Thomas Steinmaurer http://www.upscene.com Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: [firebird-support] Connecting to Firebird database from two or more pc with same username
> Hello, > If a user try to connect from two or more PC with same username, > Firebird accepts the connection but is safe without problems or is best > to avoid like the pest? I guess you mean with the same Firebird user name? Or are you using Windows trusted authentication? In general this is fine of course, even many people are using SYSDBA for regular connects, although this is not a good idea from a security perspective. With a single user you basically can't distinguish people on the server side through CURRENT_USER etc. -- With regards, Thomas Steinmaurer http://www.upscene.com Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc. > > Thanks. > -- > > Luigi Siciliano > -- > > > > > > > > ++ > > Visit http://www.firebirdsql.org and click the Documentation item > on the main (top) menu. Try FAQ and other links from the left-side menu > there. > > Also search the knowledgebases at > http://www.ibphoenix.com/resources/documents/ > > > ++ > > > Yahoo Groups Links > > >
Re: [firebird-support] monitoring Table
Hello Olaf, 'Checkmail' check_m...@satron.de [firebird-support] schrieb am 13.04.2016 14:38: > Hello, > > > > I've a problem with our database, firebird 3.0 RC2, some Clients (C++, > Microsoft Access over Firebird ODBC). There are 7 open transactions. But > in the monitoring Table, I cannot see the one who is the oldest was shown. > Why? How/where do you identify the oldest transaction? -- With regards, Thomas Steinmaurer http://www.upscene.com Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc. > > > > INSERT INTO MON$TRANSACTIONS (MON$TRANSACTION_ID, MON$ATTACHMENT_ID, > MON$STATE, MON$TIMESTAMP, MON$TOP_TRANSACTION, MON$OLDEST_TRANSACTION, > MON$OLDEST_ACTIVE, MON$ISOLATION_MODE, MON$LOCK_TIMEOUT, MON$READ_ONLY, > MON$AUTO_COMMIT, MON$AUTO_UNDO, MON$STAT_ID) > > VALUES (2834893, 85000, 1, '2016-04-13 14:34:31', > 2834893, 2774214, 2771000, 2, 0, 0, 0, 1, 201); > > INSERT INTO MON$TRANSACTIONS (MON$TRANSACTION_ID, MON$ATTACHMENT_ID, > MON$STATE, MON$TIMESTAMP, MON$TOP_TRANSACTION, MON$OLDEST_TRANSACTION, > MON$OLDEST_ACTIVE, MON$ISOLATION_MODE, MON$LOCK_TIMEOUT, MON$READ_ONLY, > MON$AUTO_COMMIT, MON$AUTO_UNDO, MON$STAT_ID) > > VALUES (2208191, 68114, 0, '2016-04-07 05:03:44', > 2208191, 2768721, 2771000, 2, -1, 1, 0, 1, 319); > > INSERT INTO MON$TRANSACTIONS (MON$TRANSACTION_ID, MON$ATTACHMENT_ID, > MON$STATE, MON$TIMESTAMP, MON$TOP_TRANSACTION, MON$OLDEST_TRANSACTION, > MON$OLDEST_ACTIVE, MON$ISOLATION_MODE, MON$LOCK_TIMEOUT, MON$READ_ONLY, > MON$AUTO_COMMIT, MON$AUTO_UNDO, MON$STAT_ID) > > VALUES (2833276, 83617, 1, '2016-04-13 07:00:29', > 2771000, 2768721, 2769594, 2, -1, 0, 0, 1, 1267); > > INSERT INTO MON$TRANSACTIONS (MON$TRANSACTION_ID, MON$ATTACHMENT_ID, > MON$STATE, MON$TIMESTAMP, MON$TOP_TRANSACTION, MON$OLDEST_TRANSACTION, > MON$OLDEST_ACTIVE, MON$ISOLATION_MODE, MON$LOCK_TIMEOUT, MON$READ_ONLY, > MON$AUTO_COMMIT, MON$AUTO_UNDO, MON$STAT_ID) > > VALUES (2834878, 84239, 1, '2016-04-13 10:38:25', > 2792941, 2768721, 2771000, 2, -1, 0, 0, 1, 1677); > > INSERT INTO MON$TRANSACTIONS (MON$TRANSACTION_ID, MON$ATTACHMENT_ID, > MON$STATE, MON$TIMESTAMP, MON$TOP_TRANSACTION, MON$OLDEST_TRANSACTION, > MON$OLDEST_ACTIVE, MON$ISOLATION_MODE, MON$LOCK_TIMEOUT, MON$READ_ONLY, > MON$AUTO_COMMIT, MON$AUTO_UNDO, MON$STAT_ID) > > VALUES (2834800, 84681, 1, '2016-04-13 14:21:39', > 2832348, 2768721, 2771000, 2, -1, 0, 0, 1, 1933); > > INSERT INTO MON$TRANSACTIONS (MON$TRANSACTION_ID, MON$ATTACHMENT_ID, > MON$STATE, MON$TIMESTAMP, MON$TOP_TRANSACTION, MON$OLDEST_TRANSACTION, > MON$OLDEST_ACTIVE, MON$ISOLATION_MODE, MON$LOCK_TIMEOUT, MON$READ_ONLY, > MON$AUTO_COMMIT, MON$AUTO_UNDO, MON$STAT_ID) > > VALUES (2833230, 84857, 1, '2016-04-13 14:25:23', > 2833010, 2768721, 2771000, 2, -1, 0, 0, 1, 2196); > > INSERT INTO MON$TRANSACTIONS (MON$TRANSACTION_ID, MON$ATTACHMENT_ID, > MON$STATE, MON$TIMESTAMP, MON$TOP_TRANSACTION, MON$OLDEST_TRANSACTION, > MON$OLDEST_ACTIVE, MON$ISOLATION_MODE, MON$LOCK_TIMEOUT, MON$READ_ONLY, > MON$AUTO_COMMIT, MON$AUTO_UNDO, MON$STAT_ID) > > VALUES (2829258, 84866, 1, '2016-04-13 14:06:50', > 2829258, 2768721, 2771000, 2, -1, 0, 0, 1, 2293); > > INSERT INTO MON$TRANSACTIONS (MON$TRANSACTION_ID, MON$ATTACHMENT_ID, > MON$STATE, MON$TIMESTAMP, MON$TOP_TRANSACTION, MON$OLDEST_TRANSACTION, > MON$OLDEST_ACTIVE, MON$ISOLATION_MODE, MON$LOCK_TIMEOUT, MON$READ_ONLY, > MON$AUTO_COMMIT, MON$AUTO_UNDO, MON$STAT_ID) > > VALUES (2834822, 84891, 1, '2016-04-13 14:13:14', > 2829911, 2768721, 2771000, 2, -1, 0, 0, 1, 2358); > > INSERT INTO MON$TRANSACTIONS (MON$TRANSACTION_ID, MON$ATTACHMENT_ID, > MON$STATE, MON$TIMESTAMP, MON$TOP_TRANSACTION, MON$OLDEST_TRANSACTION, > MON$OLDEST_ACTIVE, MON$ISOLATION_MODE, MON$LOCK_TIMEOUT, MON$READ_ONLY, > MON$AUTO_COMMIT, MON$AUTO_UNDO, MON$STAT_ID) > > VALUES (2833355, 84977, 1, '2016-04-13 14:27:03', > 2833355, 2768721, 2771000, 2, -1, 0, 0, 1, 2450); > > INSERT INTO MON$TRANSACTIONS (MON$TRANSACTION_ID, MON$ATTACHMENT_ID, > MON$STATE, MON$TIMESTAMP, MON$TOP_TRANSACTION, MON$OLDEST_TRANSACTION, > MON$OLDEST_ACTIVE, MON$ISOLATION_MODE, MON$LOCK_TIMEOUT, MON$READ_ONLY, > MON$AUTO_COMMIT, MON$AUTO_UNDO, MON$STAT_ID) > > VALUES (2833491, 84980, 1, '2016-04-13 14:28:18', > 2833491, 2768721, 2771000, 2, -1, 0, 0, 1, 2527); > > INSERT INTO MON$TRANSACTIONS (MON$TRANSACTION_ID, MON$ATTACH
Re: [firebird-support] Cannot restore 32GB DB - fails at end, unusable. Any ideas please.
> There is 77GB free out of 148GB at the minute and it has restored 9GB so > far, final size ~33GB so on the surface looks adequate unless very large > temp files are created when the indexes are reactivated? Yes. This may happen, depending on the index size. Are you sure that temp space is actually pointing to the disk with 77G free space? * What is the value for the TEMP/TMP environment variable (in case you are on Windows)? * Any manual changes for TempDirectories in firebird.conf? -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: [firebird-support] Cannot restore 32GB DB - fails at end, unusable. Any ideas please.
Hi! > gbak:activating and creating deferred index K_MERCURY_EMAIL > > gbak:cannot commit index K_MERCURY_EMAIL > > gbak: ERROR:operating system directive CreateFile failed Any chance that you are running out of disk where your TEMP directory is located? -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc. > gbak: ERROR:The system cannot find the path specified. > > Database restore canceled 21:17:19 due to IBPP exception: > > > *** IBPP::SQLException *** > > Context: ServiceImpl::Wait > > Message: isc_service_query failed > > > SQL Message : -901 > > Unsuccessful execution caused by system error that does not preclude > successful execution of subsequent statements > > > Engine Code: 335544342 > > Engine Message : > > action cancelled by trigger (3) to preserve data integrity > > Cannot deactivate index used by a PRIMARY/UNIQUE constraint > > > > >
Re: [firebird-support] [FB 2.1] Firebird engine seems to slow down on high load without utilizing hardware
Hi Patrick, > Hi Thomas, nice to get a response from you. We already met in ~2010 in Linz at > your office :) > (ex. SEM GmbH, later Playmonitor GmbH) I know. XING (Big Brother) is watching you. Nice to see that you are still running with Firebird. ;-) > First, sorry for posting a mixed state of informations. The config settings i > postet are the current settings. > But the Lock-Table-Header was from last saturday (day of total system crash) - > we changed Hash Slot Value since than, but it didn't work. New Table looks > like: > > > LOCK_HEADER BLOCK > Version: 16, Active owner: 0, Length: 134247728, Used: 55790260 > Semmask: 0x0, Flags: 0x0001 > Enqs: 1806423519, Converts: 4553851, Rejects: 5134185, Blocks: 56585419 > Deadlock scans: 82, Deadlocks: 0, Scan interval: 10 > Acquires: 2058846891, Acquire blocks: 321584126, Spin count: 0 > Mutex wait: 15.6% > Hash slots: 20011, Hash lengths (min/avg/max):0/ 7/ 18 > Remove node: 0, Insert queue: 0, Insert prior: 0 > Owners (297): forward: 385160, backward: 38086352 > Free owners (43): forward: 52978748, backward: 20505128 > Free locks (41802): forward: 180712, backward: 3620136 > Free requests (-1097572396): forward: 46948676, backward: 13681252 > Lock Ordering: Enabled > > > The Min/Avg/Max hash lengths look better now, but as you mentioned the Mutex > wait is worring us too. > We have 2 direct questions about that. > > > 1) What are the negative effects of increasing Hash-Slots (too high)? It somehow defines the initial size of a hash table which is used for lock(ed) object lookup by a key (= hash value), ideally with constant O(1) run-time complexity. If the hash table is too small, due to a too small value for hash slots, it starts to degenerate into a linked/linear list per hash slot. Worst case resulting in O(n) complexity for lookups. The above 20011 setting shows an AVG hash length which looks fine. As you might know, Classic having a dedicated process per connection model somehow needs a (global) mechanism to synchronize/protect shared data structures across these processes via IPC. This is what the lock manager and the lock table is used for. > 2) As far as we know, we can't influence Mutex wait directly (it's just > informational). But do you think that's the reason the underlying hardware is > not utilized? I don't think you are disk IO bound. Means, I'm not convinced that faster IO will help. Somehow backed by the high mutex wait. Under normal operations you see 100-500 IOPS with some room for further increase as shown in the 1700 IOPS backup use case. Don't know how random disk IO is in this two scenarios. Any chance to run some sort of disk IO benchmarks or do you already know your upper limits for your SAN IOPS wise? > > > We do consider to upgrade to 2.5, but had our eyes on FB 3 over the last year, > waiting for it to get ready. > With 2.5.x we tested around a long time now, but never found a real reason to > upgrade - since it's a reasonable amount of work for us. When you say it > improves the lock contention, this sound pretty good. But again the question, > do you think lock contention is limiting our system? Dmitry, Vlad etc. will correct me (in case he is following the thread), but I recall that in 2.5, especially in SuperClassic being multi-threaded per worker process compared to Classic, now also allows specific(?) lock manager operations in parallel to regular request processing. In general I remember a mentioned improvement of ~25% in a TPC-C style workload with SuperClassic compared to Classic. > > > First and foremost, we would really like to find the bottleneck. We just don't > have the know-how to imagine something like "Fb 2.1 Engine is limiting us > because of ..." and without that knowledge it's hard to take actions like > upgrading to 2.5. > > > We'll try to collect information about the garbage we create :) We do run > "Sinatica Monitoring" on the server, which shows us "Awaiting Gargabe > Collection" Transactions. Is that the information you'r looking for? I'm not familiar with Sinatica. Perhaps the periodic MON$ queries (how frequent are they executed by Sinatica?) also produce some sort of overhead, cause each MON$ table query in context of a new physical transaction results in a stable view of current activity. Possibly not neglectable with > 400 connections. The most easiest way to get insights on your record garbage is, e.g.: * Run gstat -r * Run a tool from IBSurgeon (can't recall the name, Alexey?) * Run a tool from Upscene (FB TraceManager) > > Maybe to avoid confusion, we don't have normal "Spikes" .. the system just > starts to slow down and this state remains until the server-load is gone > (after > midnight, when software is not used anymore). -- With regards, Thomas Steinmaurer http://www.upscene.com Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: [firebird-support] [FB 2.1] Firebird engine seems to slow down on high load without utilizing hardware
> Thomas, >> >> I doubt, Firebird is IO-bound (limited by disk IO). >> > > Sorry, I don't understand your comment, can you please clarify what you > mean? I think, disk IO isn't the limiting factor in that environment. -- With regards, Thomas Steinmaurer http://www.upscene.com Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: [firebird-support] [FB 2.1] Firebird engine seems to slow down on high load without utilizing hardware
> Hi Patrick, > > If you say that problem occurred recently, I would suggest you to check > SAN disks health. > > However, these values > >>Average system IOPS under load read: 100 > >Average system IOPS under load write: 550 > >Backup Restore IOPS read: 1700 > >Backup Restore IOPS write: 250 > > are really, really low. > 1700 IOPS for the database with 4k page means 6.8Mb/sec (in case of > random reads). > > I suggest to install a single SSD drive and check how it will work. > SSD IOPS looks like > Random Read 4KB (QD=32) : 283.050 MB/s [ 69104.0 IOPS] > Random Write 4KB (QD=32) : 213.837 MB/s [ 52206.2 IOPS] > > > From our optimization practice we found that if you need to optimize > only the single instance of the database, the most cost effective way is > to upgrade to SSD first, and only then fix other problems. I doubt, Firebird is IO-bound (limited by disk IO). -- With regards, Thomas Steinmaurer http://www.upscene.com Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc. >> hi, >> >> recently we had some strange performance issues with our Firebird DB >> server. >> >> On high load, our server started to slow down. Select and update SQL >> query times did go up by more than 500% on average, >> >> but reaching unreasonable high execution times at worst case. (several >> minutes instead of < 1sec) >> >> >> OIT/OAT/Next Transaction statistics was within 1000 the hole time >> >> We were not able to messure any hardware limiting factor. Indeed, this >> system was running with only 8 cores at about 70% CPU usage on max. load. >> >> We decided that this may be our problem since we experienced a similar >> problem at about 80% CPU load in the past. >> >> So we upgraded the hardware. As expected, the CPU-load dropped to ~35% >> usage on max. load scenario. >> >> But this did not solve the problem. >> >> Same story for the harddisk system. The usage is not even near it's >> max capacity. >> >> >> We also can't see any impact on the harddisk. >> >> We'r kind of stuck with our ideas, because we have no idea what could >> be a potential bottleneck to the system. >> >> Since the hardware doesn't show a limit, there have to be anything >> else - most likely firebird engine related that's limiting our system. >> >> We would be very grateful if anyone can give us hints where we can >> search further. >> >> Or someone has similar experiences to share with us. >> >> >> >> Operating System: Windows Server 2003 >> >> Firebird: 2.1.5 Classic >> >> Dedicated database server (VMWare) >> >> >> CPU: 16 cores, each 2.4 GHz >> >> RAM: 32 GB >> >> About 14GB are used from OS and firebird processes under max load. >> >> HDD: SAN Storage System >> >> >> Average system IOPS under load read: 100 >> >> Average system IOPS under load write: 550 >> >> Backup Restore IOPS read: 1700 >> >> Backup Restore IOPS write: 250 >> >> SAN IPOS Limit (max): 3000 >> >> >> Firebird Config Settings, based on defaults >> >> DefaultDbCachePages = 1024 >> >> LockMemSize = 134247728 >> >> LockHashSlots = 20011 >> >> Database >> >> size: about 45 GB >> >> 450 to 550 concurrent connections >> >> Daily average of 65 transactions / second (peak should be higher) >> >> >> FB_LOCK_PRINT (without any params) while system was slowing down (~4 >> days uptime). >> >> I have to note, Firebird was not able to print the complete output >> (stats was not cropped by me) >> >> >> LOCK_HEADER BLOCK >> >> Version: 16, Active owner: 0, Length: 134247728, Used: 82169316 >> >> Semmask: 0x0, Flags: 0x0001 >> >> Enqs: 4211018659, Converts: 10050437, Rejects: 9115488, Blocks: 105409192 >> >> Deadlock scans: 1049, Deadlocks: 0, Scan interval: 10 >> >> Acquires: 4723416170, Acquire blocks: 640857597, Spin count: 0 >> >> Mutex wait: 13.6% >> >> Hash slots: 15077, Hash lengths (min/avg/max):3/ 12/ 25 >> >> Remove node: 0, Insert queue: 36, Insert prior: 74815332 >> >> Owners (456):forward: 131316, backward: 14899392 >> >> Free owners (9):forward: 39711576, backward: 49867232 >> >> Free locks (42409):forward: 65924212, backward: 23319052 >> >> >> With best Regards, >> >> >> Patrick Friessnegg >> >> Synesc GmbH >> >> > >
Re: [firebird-support] [FB 2.1] Firebird engine seems to slow down on high load without utilizing hardware
Patrick, > recently we had some strange performance issues with our Firebird DB server. > > On high load, our server started to slow down. Select and update SQL > query times did go up by more than 500% on average, > > but reaching unreasonable high execution times at worst case. (several > minutes instead of < 1sec) > > > OIT/OAT/Next Transaction statistics was within 1000 the hole time > > We were not able to messure any hardware limiting factor. Indeed, this > system was running with only 8 cores at about 70% CPU usage on max. load. > > We decided that this may be our problem since we experienced a similar > problem at about 80% CPU load in the past. > > So we upgraded the hardware. As expected, the CPU-load dropped to ~35% > usage on max. load scenario. > > But this did not solve the problem. > > Same story for the harddisk system. The usage is not even near it's max > capacity. > > > We also can't see any impact on the harddisk. > > We'r kind of stuck with our ideas, because we have no idea what could be > a potential bottleneck to the system. > > Since the hardware doesn't show a limit, there have to be anything else > - most likely firebird engine related that's limiting our system. > > We would be very grateful if anyone can give us hints where we can > search further. > > Or someone has similar experiences to share with us. > > > > Operating System: Windows Server 2003 > > Firebird: 2.1.5 Classic > > Dedicated database server (VMWare) > > > CPU: 16 cores, each 2.4 GHz > > RAM: 32 GB > > About 14GB are used from OS and firebird processes under max load. > > HDD: SAN Storage System > > > Average system IOPS under load read: 100 > > Average system IOPS under load write: 550 > > Backup Restore IOPS read: 1700 > > Backup Restore IOPS write: 250 > > SAN IPOS Limit (max): 3000 > > > Firebird Config Settings, based on defaults > > DefaultDbCachePages = 1024 > > LockMemSize = 134247728 > > LockHashSlots = 20011 > > Database > > size: about 45 GB > > 450 to 550 concurrent connections > > Daily average of 65 transactions / second (peak should be higher) > > > FB_LOCK_PRINT (without any params) while system was slowing down (~4 > days uptime). > > I have to note, Firebird was not able to print the complete output > (stats was not cropped by me) > > > LOCK_HEADER BLOCK > > Version: 16, Active owner: 0, Length: 134247728, Used: 82169316 > > Semmask: 0x0, Flags: 0x0001 > > Enqs: 4211018659, Converts: 10050437, Rejects: 9115488, Blocks: 105409192 > > Deadlock scans: 1049, Deadlocks: 0, Scan interval: 10 > > Acquires: 4723416170, Acquire blocks: 640857597, Spin count: 0 > > Mutex wait: 13.6% > > Hash slots: 15077, Hash lengths (min/avg/max):3/ 12/ 25 > > Remove node: 0, Insert queue: 36, Insert prior: 74815332 > > Owners (456):forward: 131316, backward: 14899392 > > Free owners (9):forward: 39711576, backward: 49867232 > > Free locks (42409):forward: 65924212, backward: 23319052 fb_lock_print is reporting a hash slots value of 15077 but you show a setting of 20011. Mutex wait looks high to me. Some ideas: * Increase the hash slots value to 30011 * Get a picture on how many garbage (record versions) you create. AFAIR it is the -r switch of gstat which gives you that information. Sudden spikes in the statement response time could be related to co-operative garbage collection in Classic/SuperClassic, where basically the statement synchronously removes garbage of out-dated record versions * Consider upgrading to 2.5. 2.1.7 is end-of-life and 2.5 improved in the area of lock contention in Classic/SuperClassic substantially. -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: [firebird-support] How can I prevent that my database can be opened with another security.fdb.
> Dear all, > If blocking SYSDBA from connecting the database is the goal, than ones can > create a role with the name SYSDBA.Is there any minus about this? AFAIR, this needs to be done with a direct system table insertion in RDB$ROLES with 2.5 and earlier. I don't know if this has changed in Firebird 3, but for sure, Firebird 3 will reject direct system table modifications at all. -- With regards, Thomas Steinmaurer http://www.upscene.com Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: [firebird-support] How can I prevent that my database can be opened with another security.fdb.
Hi, > Dear all, > Though I don't have the same question as Roland, information below by Virna > Constantin (costel...@yahoo.com) is very useful.Thanks every one. > Regards,Tjioe > > Suggestion > event studies "on connect" > 1. an application with minimum rights connects to the database > 1 '. in table "mon$attachmnts" field "MON$REMOTE_PROCESS" will record the > application name > 2. in the trigger event "on connect" if the user connects SysAdmin, verifies > the application name in "MON$REMOTE_PROCESS" Just FYI: In practice it is possible to suppress database triggers (DPB: isc_dpb_no_db_triggers) when connecting as the database owner or as SYSDBA. E.g. isql has a special command-line option "-nodbtriggers" to bypass database triggers at connect time to e.g. modify/drop the trigger afterwards. -- With regards, Thomas Steinmaurer http://www.upscene.com Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: !!***SPAM***!! Re: [firebird-support] Firebird V3RC2 Primary Key question
> Thanks for instructions. It does work. > > > > Is there a way to use created case insensitive collation as database wide > default? It would be easier for me not to specify collation for each and > every index definition. http://www.firebirdsql.org/refdocs/langrefupd25-ddl-charset.html This does not affect existing character fields though. -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc. > > > > Thanks. > > > > From: firebird-support@yahoogroups.com > [mailto:firebird-support@yahoogroups.com] > Sent: Friday, April 8, 2016 10:36 AM > To: firebird-support@yahoogroups.com > Subject: !!***SPAM***!! Re: [firebird-support] Firebird V3RC2 Primary Key > question > > > > > > On 7-4-2016 23:01, Ertan Küçükoğlu ertan.kucuko...@1nar.com.tr > [firebird-support] wrote: >> Would you be so kind to provide an example SQL which shows how to do >> primary key with a case insensitive collation? > > 1. Register the WIN1254_UNICODE collation (it is defined in fbintl.conf, > but not included by default in Firebird): > > create collation WIN1254_UNICODE for win1254 > > 2. Derive a case insensitive collation from WIN1254_UNICODE: > > create collation win1254_ci for win1254 from win1254_unicode case > insensitive > > 3. Create table > > create table win1254test ( > stringkey varchar(30) character set win1254 primary key collate > win1254_ci > ) > > 4. Test it: > > INSERT INTO win1254test(stringkey) VALUES('EK'); -- succeeds > INSERT INTO win1254test(stringkey) VALUES('ek'); -- fails > INSERT INTO win1254test(stringkey) VALUES('Ek'); -- fails > INSERT INTO win1254test(stringkey) VALUES('eK'); -- fails > > I hope this helps, > Mark >
Re: [firebird-support] Find rarely used or unused indices
Hi, > What is the best way to go about to find out about this? > > I have several projects that are using the FB database and I would like > to cleanup and remove all the indices that are just taking up space and > thus indirectly reduces performance? It is not that easy to clearly define the usefulness of an index. Usually indexes with a low selectivity (high number of duplicates) is a questionable candidate for a good index, but it depends from a query perspective. Imagine you have an index with 1 million nodes pointing to records but with only 2 unique values (e.g. BOOLEAN), one row only with a value of 0 and the rest with a value of 1. If you query the one row with value 0, the index will be definitely useful, but for querying 99 records with value 1, it is better to scan the entire table instead of the additional "hop" via the index to locate the row. Histograms (distribution among unique values) will help the optimizer there to choose a better execution plan. AFAIK histograms are planned for Firebird 4. To get an overview across all your indices in your database from various perspectives (size, index depth, selectivity, duplicates etc.), you can run the gstat command-line tool and process and interpret the raw output. But as Alexey has pointed out, there is tool support out there. HQbird is one. Upscene's FB TraceManager (http://www.upscene.com/fb_tracemanager/) is another product which can help you in that area. To get a first impression how this may look lie, have a look on that screen: http://www.upscene.com/fb_tracemanager/images/products/fbtm/fbtm_ss_databasestatistics_index_monitoring.png In combination with the live tracing functionality including execution plans of executed statements etc., you may find suspicious things. -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: [firebird-support] How can I prevent that my database can be opened with another security.fdb.
Roland, the new Firebird 3 per-database security database capability/area is a bit confusing or perhaps leads to wrong expectations, unfortunately. When a user database acts itself also as a security database, this does not mean that you get some sort of embedded user authentication as in InterBase, that said, the expectation that you can move around the database and you are not able to connect without knowing user credentials stored in the database itself is wrong. I hope I'm wrong and stand corrected if I am, but I guess this is how it works. Although, the alternate security database approach makes a lot of sense e.g. for (web) hosting companies, running several customer databases in an isolated way from a user database perspective, without sharing the e.g. the SYSDBA user across several customers. ;-) -- With regards, Thomas Steinmaurer http://www.upscene.com Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc. 'Mueller, Roland (GE87)' roland.muel...@honeywell.com [firebird-support] schrieb am 08.04.2016 10:12: > Thanks for your suggestions. > > I am actually searching for another way. This database is part of an > application that is deployed at our customers. > In the new firebird release it is possible to choose security authentication > via external .fbd file or programmed into the database itself. > > We want to use the authentication in our own database and prevent any use of > .fdb files for access. Now if a customer has our database, he simply can set > security3.fdb and gain access to the DB. > Is there any way to prevent this and only use our authentication ? > > regards Roland > >
Re: [firebird-support] How to add previous column value to current value?
With pre V3, PSQL (stored procedure or EXECUTE BLOCK) comes to my mind. -- With regards, Thomas Steinmaurer http://www.upscene.com Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc. 'Piotr Olszewski' piotr.olszew...@eskrzynka.pl [firebird-support] schrieb am 29.03.2016 11:53: > let's assume that I have 2.5.3 > > Pozdrawiam > > Piotr Olszewski > > -Original Message- > From: firebird-support@yahoogroups.com > [mailto:firebird-support@yahoogroups.com] > Sent: Tuesday, March 29, 2016 11:45 AM > To: firebird-support@yahoogroups.com > Subject: Re: [firebird-support] How to add previous column value to current > value? > > 29.03.2016 11:40, 'Piotr Olszewski' piotr.olszew...@eskrzynka.pl > [firebird-support] wrote: >> I need to get a date difference (days between) between rows. > > With Firebird 3.0 you can use window function LAG. > > -- > WBR, SD. > > > > > > > ++ > > Visit http://www.firebirdsql.org and click the Documentation item on the > main (top) menu. Try FAQ and other links from the left-side menu there. > > Also search the knowledgebases at > http://www.ibphoenix.com/resources/documents/ > > ++ > > > Yahoo Groups Links > > > > > > > Posted by: "Piotr Olszewski" > > > ++ > > Visit http://www.firebirdsql.org and click the Documentation item > on the main (top) menu. Try FAQ and other links from the left-side menu > there. > > Also search the knowledgebases at > http://www.ibphoenix.com/resources/documents/ > > > ++ > > > Yahoo Groups Links > > >
Re: [firebird-support] Disable cache at all - future question
> Hi, > is this possible to disable cache at all? > I mean that all go from and to “HDD” without RAM between. Checkout the FileSystemCacheThreshold configuration parameter in firebird.conf. Set it to 0 and also use a small page buffers value. Another area of RAM usage is for temporary data used by grouping, sorting etc. If you also want to (almost) disable that area, watch out the TempCacheLimit configuration parameter in firebird.conf. -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: [firebird-support] Temporarily disable constraints
Hi, > Is it possible to temporarily disable a FK constraint? I have found a > couple of requests for this feature dated 2009 but these still show as > open. Did this functionality ever get implemented ? No. As Alan said, currently the only way is to dropping and re-creating. -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: [firebird-support] Ascending and Descending Index
> Hi. > > Is it possible to create an index like > > CREATE INDEX MYINDEX_IDX1 > ON MYTABLE (COLUMN1 ASCENDING, COLUMN2 DESCENDING ) No. You can't define ordering per index segment. -- With regards, Thomas Steinmaurer http://www.upscene.com Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc. > > > > > > ++ > > Visit http://www.firebirdsql.org and click the Documentation item > on the main (top) menu. Try FAQ and other links from the left-side menu > there. > > Also search the knowledgebases at > http://www.ibphoenix.com/resources/documents/ > > > ++ > > > Yahoo Groups Links > > >
Re: [firebird-support] How write a query with a progressive sum field
On 07.03.2016 17:40, Dimitry Sibiryakov s...@ibphoenix.com [firebird-support] wrote: > 07.03.2016 17:21, Thomas Steinmaurer t...@iblogmanager.com [firebird-support] > wrote: >>>> Nothing prevents him from installing of an additional server for >>>> report purposes and >>>> setting up replication to it from production one. >> >> Yeah. Sure. ;-) >> > > Well, if setting up standby server for reporting purposes with > IBLogManager is too had > task, he always can try IBPhoenix Replicator which is easy to install and > configure. Oh, product flame ware. Sorry to disappoint you, but I haven't joined the thread to place product plugs. I'm getting older, hopefully more wisely and luckily, something else is feeding my family. ;-) As you started the product plug stuff, I guess it is different at your side. I'm sure, IBReplicator does its job very well. I simply noted that V3 RC should be treated as not production-ready (like any other RC in Firebird's history), but perhaps the Firebird project philosophy changed. > If he is able to write queries to the database, he knows for sure which > tables he > needs. Extract definitions of these tables in FlameRobin or IBExpert is easy. > Create a new > database with these tables inside and copy data for these tables is also > easy. After that > setting up replication will take less than an hour, including reading of > manual. Na, replication is an additional (complex) layer, which needs to be maintained, monitored etc ... -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc. ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ 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: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] How write a query with a progressive sum field
> Il 07/03/2016 11.32, Dimitry Sibiryakov s...@ibphoenix.com > [firebird-support] ha scritto: >> sum(saldo) over (order by data) > > Thank You for your response but I can't do: sum(SALDO) because SALDO is > not a field of my table. > > I badly explained myself: I have a table with only this fields: > > DATA DOCUMENTO_ID NUMERO SERIE CARICO SCARICO > > > I need add SALDO fields in the result of the query to get a progressive > sum of CARICO - SCARICO. Although not that flexible as an ad-hoc query, but perhaps your particular use case can be achieved with a selectable stored procedure ... I'm not good in writing PSQL out of my mind at the moment, but execute your SQL (without the SUM aggregate field) in a stored procedure, iterate over the result set, remember the computation for the previous row in a local variable and use that for the addition with the current row and call SUSPEND for each result set iteration. -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc. ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ 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: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] How write a query with a progressive sum field
> 07.03.2016 12:55, 'Thomas Steinmaurer' t...@iblogmanager.com > [firebird-support] wrote: >> Needless to say, 3.0 is in release candidate state. Not sure if Luigi wants >> to run 3.0 in >> production yet. ;-) > > Nothing prevents him from installing of an additional server for report > purposes and > setting up replication to it from production one. Yeah. Sure. ;-) -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc. ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ 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: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] How write a query with a progressive sum field
> 07.03.2016 12:29, Luigi Siciliano luigi...@tiscalinet.it [firebird-support] > wrote: >> Ok, I understand but, "over" is unknow in firebird 2.5.5. > > Upgrade to 3.0. Needless to say, 3.0 is in release candidate state. Not sure if Luigi wants to run 3.0 in production yet. ;-) -- With regards, Thomas Steinmaurer http://www.upscene.com Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc. > -- > WBR, SD. > > > > > > > ++ > > Visit http://www.firebirdsql.org and click the Documentation item > on the main (top) menu. Try FAQ and other links from the left-side menu > there. > > Also search the knowledgebases at > http://www.ibphoenix.com/resources/documents/ > > > ++ > > > Yahoo Groups Links > > >
Re: [firebird-support] How to optimize queries on field state
> I have a state machine (workflow) implemented on a table a couple of > million records and growing. > I have crated an index on the "state" field. The possible states are > very few (5 to 10). Obviously selectivity index is terrible. As I can > optimize the query? Hard to say without seeing an actual query ... Is the state field the only one used to filter the result set? -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: [firebird-support] CPU Affinity
Hi! > Dear Friends. > > FirebirdSQL: 2.5.5 64bits > Operating System: Windows 2008 R2 Enterprise 64bits > Server: Dell Poweredge R620 > 32GB > 2 processors E5-2609 2.40 GHz > 8 Cores > > To define CPU Affinity in firebird.conf I set CPUAffinity to 255, in a > way to use all the 8 cores. Which Firebird server architecture are you using? CPU affinity is only for SuperServer and the question is why do you bother with SuperServer on a multi-core machine when there is Classic or SuperClassic available? -- With regards, Thomas Steinmaurer http://www.upscene.com Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc. > Does it make sense or could be better to use less cores, like core > 5,6,7,8 or 3,4,5,6? > > -- > > > Atenciosamente, > > Hugo Eyng > > > > > > > > ++ > > Visit http://www.firebirdsql.org and click the Documentation item > on the main (top) menu. Try FAQ and other links from the left-side menu > there. > > Also search the knowledgebases at > http://www.ibphoenix.com/resources/documents/ > > > ++ > > > Yahoo Groups Links > > >
Re: [firebird-support] When to use Firebird 3 Classic
> On 17-1-2016 00:07, Köditz, Martin martin.koed...@it-syn.de > [firebird-support] wrote: >> is there still a reason to use the Classic mode with Firebird 3? Since >> there is real SMP support with SuperServer I don’t think there can be >> advantages with ClassicServer. > > The main advantage of course is that if one connection does something > (hit a bug in the engine or in a UDF) that crashes the process, that it > doesn't bring down all connections, but just that one connection. The number one reason why Classic hasn't been vanished yet, cause big customers with critical environments are still opting for Classic. -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
Re: [firebird-support] Small Database with very very bad performance on LAN
> Hallo, > > Il 08/01/2016 16.22, Tim Ward t...@telensa.com [firebird-support] ha scritto: >> What are your queries, query plans, query statistics? > > The queries are similar to: SELECT * FROM TABLE. > > Investigating for query plans, statistics, Buffers as suggested by > Thomas, I discovered the origin of the problem: > FB is innocent :) the guilty is me because I use ZEOSLib to connect to > FB and I used the property "SortedFields" instead of using the clause > "ORDER BY" in SQL query thinking that is more flexible on a Delphi RAD > development like Delphi :( A great use case for the Firebird Trace API to investigate what's up behind the scene of ZEOS. ;-) -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc. ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ 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: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: AW: AW: AW: [firebird-support] Architecture
> Yes, Thomas, > > it woks with 2.1. In the release notes of 2.5.X is written: > > Adding a value to a timestamp earlier than '16.11.1858 00:00:01' would throw > the error "value exceeds the range for valid timestamp". > > But where can I search 200 Tables... Read Paul's blog on using -v for the restore etc ... -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc. > -Ursprüngliche Nachricht- > Von: firebird-support@yahoogroups.com > [mailto:firebird-support@yahoogroups.com] > Gesendet: Freitag, 8. Januar 2016 17:07 > An: firebird-support@yahoogroups.com > Betreff: Re: AW: AW: [firebird-support] Architecture > >> now, the switches works fine, I had forget the Charset -fix_fss_d >> ISO8859_1 >> >> It works fine, but there is another problem to fix. Now I get the >> error, that value exceeds the range for valid timestamps. How can I fix > this issue? > > * When does that happen during the restore? > * Does a backup *AND* restore with Firebird 2.1 works? > > > -- > With regards, > Thomas Steinmaurer > http://www.upscene.com/ > > Professional Tools and Services for Firebird FB TraceManager, IB LogManager, > Database Health Check, Tuning etc. > >> I have installed fb as cs without superclassic under 32 Bit Win7 the >> 32 Bit Version >> >> -Ursprüngliche Nachricht- >> Von: firebird-support@yahoogroups.com >> [mailto:firebird-support@yahoogroups.com] >> Gesendet: Freitag, 8. Januar 2016 16:51 >> An: firebird-support@yahoogroups.com >> Betreff: Re: AW: [firebird-support] Architecture >> >>> Thanks. For some Years, we have had in use the superserver, but since >>> we have a quadcore CPU, we are using cs. >>> >>> UDFs can I replace with build in functions >> >> I guess you can imagine that you first need to do that before backing >> up and restoring? >> >>> Now, I have restored the database with 2.5, but.. Errors :( Now I >>> have seen the new switches -fix_fss_d and m, but now I get another >>> error, no character set were found. >> >> * What is the character set of your database used when you have >> created the database in the past? >> * What is the exact error message? >> * How did you install Firebird 2.5? >> >> Is there another way to migrate? With the scripts while >>> running 2.1? >> >> backup/restore via gbak is the preferred solution, because it also >> moves data. Of course, you still can create an empty Firebird 2.5 >> based database with a DDL script and then pump your data with a tool >> from your source into the destination database. >> >> >> -- >> With regards, >> Thomas Steinmaurer >> http://www.upscene.com/ >> >> Professional Tools and Services for Firebird FB TraceManager, IB >> LogManager, Database Health Check, Tuning etc. >> >> >> >>> >>> thanks >>> >>> -Ursprüngliche Nachricht- >>> Von: firebird-support@yahoogroups.com >>> [mailto:firebird-support@yahoogroups.com] >>> Gesendet: Freitag, 8. Januar 2016 16:37 >>> An: firebird-support@yahoogroups.com >>> Betreff: Re: [firebird-support] Architecture >>> >>> Hello Olaf, >>> >>>> Hello, >>>> >>>> I plan an update of firebird 2.1 cs 32 Bit with 32 Bit Server OS to >>>> Firebird 2.5. Is there a 64 Bit OS recommend and a 64 Bit >>>> installation of Firebird too? >>> >>> From an operational POV, a main factor with moving from 32-bit to >>> 64-bit is if you are using (third-party) UDF libraries. You need them >>> to be compiled with a 64-bit compiler if you want to run Firebird 64-bit. >>> >>> Other than that, I guess it depends on your planned load, especially >>> addressable RAM by the Firebird server process. If still intend to >>> run with ClassicServer, then each connection spawns its own process, >>> thus you probably won't hit the addressable RAM limit with Firebird > 32-bit. >>> That might be different with Firebird SuperClassic (32-bit), because >>> this is a single process architecture. >>> >>> I connect with odbc. Known Issues while migration, >>>> metadata etc. ? >>> >>> The us
Re: AW: AW: [firebird-support] Architecture
Olaf, > now, the switches works fine, I had forget the Charset -fix_fss_d ISO8859_1 > > It works fine, but there is another problem to fix. Now I get the error, > that value exceeds the range for valid timestamps. How can I fix this issue? Funny, you have been using 2.5 in 2013 already: https://www.mail-archive.com/firebird-support@yahoogroups.com/msg05861.html and ran into the same problem. ;-) Check out Paul's blog: http://paulbeachsblog.blogspot.co.at/2011/07/firebird-v21-error-value-exceeds-range.html -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc. > I have installed fb as cs without superclassic under 32 Bit Win7 the 32 Bit > Version > > -Ursprüngliche Nachricht- > Von: firebird-support@yahoogroups.com > [mailto:firebird-support@yahoogroups.com] > Gesendet: Freitag, 8. Januar 2016 16:51 > An: firebird-support@yahoogroups.com > Betreff: Re: AW: [firebird-support] Architecture > >> Thanks. For some Years, we have had in use the superserver, but since >> we have a quadcore CPU, we are using cs. >> >> UDFs can I replace with build in functions > > I guess you can imagine that you first need to do that before backing up and > restoring? > >> Now, I have restored the database with 2.5, but.. Errors :( Now I have >> seen the new switches -fix_fss_d and m, but now I get another error, >> no character set were found. > > * What is the character set of your database used when you have created the > database in the past? > * What is the exact error message? > * How did you install Firebird 2.5? > > Is there another way to migrate? With the scripts while >> running 2.1? > > backup/restore via gbak is the preferred solution, because it also moves > data. Of course, you still can create an empty Firebird 2.5 based database > with a DDL script and then pump your data with a tool from your source into > the destination database. > > > -- > With regards, > Thomas Steinmaurer > http://www.upscene.com/ > > Professional Tools and Services for Firebird FB TraceManager, IB LogManager, > Database Health Check, Tuning etc. > > > >> >> thanks >> >> -Ursprüngliche Nachricht- >> Von: firebird-support@yahoogroups.com >> [mailto:firebird-support@yahoogroups.com] >> Gesendet: Freitag, 8. Januar 2016 16:37 >> An: firebird-support@yahoogroups.com >> Betreff: Re: [firebird-support] Architecture >> >> Hello Olaf, >> >>> Hello, >>> >>> I plan an update of firebird 2.1 cs 32 Bit with 32 Bit Server OS to >>> Firebird 2.5. Is there a 64 Bit OS recommend and a 64 Bit >>> installation of Firebird too? >> >>From an operational POV, a main factor with moving from 32-bit to >> 64-bit is if you are using (third-party) UDF libraries. You need them >> to be compiled with a 64-bit compiler if you want to run Firebird 64-bit. >> >> Other than that, I guess it depends on your planned load, especially >> addressable RAM by the Firebird server process. If still intend to run >> with ClassicServer, then each connection spawns its own process, thus >> you probably won't hit the addressable RAM limit with Firebird 32-bit. >> That might be different with Firebird SuperClassic (32-bit), because >> this is a single process architecture. >> >> I connect with odbc. Known Issues while migration, >>> metadata etc. ? >> >> The usual path is creating a gbak-based backup with Firebird 2.1 and >> restore it under Firebird 2.5. The resulting ODS will be 11.2 (you can >> check with gstat -h afterwards). >> >> On the client side, make sure that you are using a version of the >> Firebird client library matching the server version. Bitness (32-bit vs. >> 64-bit) of the client library is driven by your application and not by >> the Firebird server. So, if you are application is 32-bit (Delphi >> 32-bit?) use a Firebird 32-bit client library and ODBC driver. >> >> Hope this helps. >> >> -- >> With regards, >> Thomas Steinmaurer >> http://www.upscene.com/ >> >> Professional Tools and Services for Firebird FB TraceManager, IB >> LogManager, Database Health Check, Tuning etc. >> >> >> >> >> >> >> ++ >> >> Visit http://www.firebirdsql.org and click the Documentation item on >> the main (top) menu. Try FAQ and other links from
Re: AW: AW: [firebird-support] Architecture
> now, the switches works fine, I had forget the Charset -fix_fss_d ISO8859_1 > > It works fine, but there is another problem to fix. Now I get the error, > that value exceeds the range for valid timestamps. How can I fix this issue? * When does that happen during the restore? * Does a backup *AND* restore with Firebird 2.1 works? -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc. > I have installed fb as cs without superclassic under 32 Bit Win7 the 32 Bit > Version > > -Ursprüngliche Nachricht- > Von: firebird-support@yahoogroups.com > [mailto:firebird-support@yahoogroups.com] > Gesendet: Freitag, 8. Januar 2016 16:51 > An: firebird-support@yahoogroups.com > Betreff: Re: AW: [firebird-support] Architecture > >> Thanks. For some Years, we have had in use the superserver, but since >> we have a quadcore CPU, we are using cs. >> >> UDFs can I replace with build in functions > > I guess you can imagine that you first need to do that before backing up and > restoring? > >> Now, I have restored the database with 2.5, but.. Errors :( Now I have >> seen the new switches -fix_fss_d and m, but now I get another error, >> no character set were found. > > * What is the character set of your database used when you have created the > database in the past? > * What is the exact error message? > * How did you install Firebird 2.5? > > Is there another way to migrate? With the scripts while >> running 2.1? > > backup/restore via gbak is the preferred solution, because it also moves > data. Of course, you still can create an empty Firebird 2.5 based database > with a DDL script and then pump your data with a tool from your source into > the destination database. > > > -- > With regards, > Thomas Steinmaurer > http://www.upscene.com/ > > Professional Tools and Services for Firebird FB TraceManager, IB LogManager, > Database Health Check, Tuning etc. > > > >> >> thanks >> >> -Ursprüngliche Nachricht- >> Von: firebird-support@yahoogroups.com >> [mailto:firebird-support@yahoogroups.com] >> Gesendet: Freitag, 8. Januar 2016 16:37 >> An: firebird-support@yahoogroups.com >> Betreff: Re: [firebird-support] Architecture >> >> Hello Olaf, >> >>> Hello, >>> >>> I plan an update of firebird 2.1 cs 32 Bit with 32 Bit Server OS to >>> Firebird 2.5. Is there a 64 Bit OS recommend and a 64 Bit >>> installation of Firebird too? >> >>From an operational POV, a main factor with moving from 32-bit to >> 64-bit is if you are using (third-party) UDF libraries. You need them >> to be compiled with a 64-bit compiler if you want to run Firebird 64-bit. >> >> Other than that, I guess it depends on your planned load, especially >> addressable RAM by the Firebird server process. If still intend to run >> with ClassicServer, then each connection spawns its own process, thus >> you probably won't hit the addressable RAM limit with Firebird 32-bit. >> That might be different with Firebird SuperClassic (32-bit), because >> this is a single process architecture. >> >> I connect with odbc. Known Issues while migration, >>> metadata etc. ? >> >> The usual path is creating a gbak-based backup with Firebird 2.1 and >> restore it under Firebird 2.5. The resulting ODS will be 11.2 (you can >> check with gstat -h afterwards). >> >> On the client side, make sure that you are using a version of the >> Firebird client library matching the server version. Bitness (32-bit vs. >> 64-bit) of the client library is driven by your application and not by >> the Firebird server. So, if you are application is 32-bit (Delphi >> 32-bit?) use a Firebird 32-bit client library and ODBC driver. >> >> Hope this helps. >> >> -- >> With regards, >> Thomas Steinmaurer >> http://www.upscene.com/ >> >> Professional Tools and Services for Firebird FB TraceManager, IB >> LogManager, Database Health Check, Tuning etc. >> >> >> >> >> >> >> ++ >> >> Visit http://www.firebirdsql.org and click the Documentation item on >> the main (top) menu. Try FAQ and other links from the left-side menu > there. >> >> Also search the knowledgebases at >> http://www.ibphoenix.com/resources/docu
Re: AW: [firebird-support] Architecture
> Thanks. For some Years, we have had in use the superserver, but since we > have a quadcore CPU, we are using cs. > > UDFs can I replace with build in functions I guess you can imagine that you first need to do that before backing up and restoring? > Now, I have restored the database with 2.5, but.. Errors :( Now I have seen > the new switches -fix_fss_d and m, but now I get another error, no character > set were found. * What is the character set of your database used when you have created the database in the past? * What is the exact error message? * How did you install Firebird 2.5? Is there another way to migrate? With the scripts while > running 2.1? backup/restore via gbak is the preferred solution, because it also moves data. Of course, you still can create an empty Firebird 2.5 based database with a DDL script and then pump your data with a tool from your source into the destination database. -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc. > > thanks > > -Ursprüngliche Nachricht- > Von: firebird-support@yahoogroups.com > [mailto:firebird-support@yahoogroups.com] > Gesendet: Freitag, 8. Januar 2016 16:37 > An: firebird-support@yahoogroups.com > Betreff: Re: [firebird-support] Architecture > > Hello Olaf, > >> Hello, >> >> I plan an update of firebird 2.1 cs 32 Bit with 32 Bit Server OS to >> Firebird 2.5. Is there a 64 Bit OS recommend and a 64 Bit installation >> of Firebird too? > > From an operational POV, a main factor with moving from 32-bit to 64-bit is > if you are using (third-party) UDF libraries. You need them to be compiled > with a 64-bit compiler if you want to run Firebird 64-bit. > > Other than that, I guess it depends on your planned load, especially > addressable RAM by the Firebird server process. If still intend to run with > ClassicServer, then each connection spawns its own process, thus you > probably won't hit the addressable RAM limit with Firebird 32-bit. > That might be different with Firebird SuperClassic (32-bit), because this is > a single process architecture. > > I connect with odbc. Known Issues while migration, >> metadata etc. ? > > The usual path is creating a gbak-based backup with Firebird 2.1 and restore > it under Firebird 2.5. The resulting ODS will be 11.2 (you can check with > gstat -h afterwards). > > On the client side, make sure that you are using a version of the Firebird > client library matching the server version. Bitness (32-bit vs. > 64-bit) of the client library is driven by your application and not by the > Firebird server. So, if you are application is 32-bit (Delphi > 32-bit?) use a Firebird 32-bit client library and ODBC driver. > > Hope this helps. > > -- > With regards, > Thomas Steinmaurer > http://www.upscene.com/ > > Professional Tools and Services for Firebird FB TraceManager, IB LogManager, > Database Health Check, Tuning etc. > > > > > > > ++ > > Visit http://www.firebirdsql.org and click the Documentation item on the > main (top) menu. Try FAQ and other links from the left-side menu there. > > Also search the knowledgebases at > http://www.ibphoenix.com/resources/documents/ > > ++ > > > Yahoo Groups Links > > > > > > > Posted by: "checkmail" > > > ++ > > Visit http://www.firebirdsql.org and click the Documentation item > on the main (top) menu. Try FAQ and other links from the left-side menu > there. > > Also search the knowledgebases at > http://www.ibphoenix.com/resources/documents/ > > ++ > > > Yahoo Groups Links > > > ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://group
Re: [firebird-support] Architecture
Hello Olaf, > Hello, > > I plan an update of firebird 2.1 cs 32 Bit with 32 Bit Server OS to > Firebird 2.5. Is there a 64 Bit OS recommend and a 64 Bit installation > of Firebird too? From an operational POV, a main factor with moving from 32-bit to 64-bit is if you are using (third-party) UDF libraries. You need them to be compiled with a 64-bit compiler if you want to run Firebird 64-bit. Other than that, I guess it depends on your planned load, especially addressable RAM by the Firebird server process. If still intend to run with ClassicServer, then each connection spawns its own process, thus you probably won't hit the addressable RAM limit with Firebird 32-bit. That might be different with Firebird SuperClassic (32-bit), because this is a single process architecture. I connect with odbc. Known Issues while migration, > metadata etc. ? The usual path is creating a gbak-based backup with Firebird 2.1 and restore it under Firebird 2.5. The resulting ODS will be 11.2 (you can check with gstat -h afterwards). On the client side, make sure that you are using a version of the Firebird client library matching the server version. Bitness (32-bit vs. 64-bit) of the client library is driven by your application and not by the Firebird server. So, if you are application is 32-bit (Delphi 32-bit?) use a Firebird 32-bit client library and ODBC driver. Hope this helps. -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc. ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ 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: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] Small Database with very very bad performance on LAN
Hello, > Hallo, > I have a small database (less 100MB) that works on Firebird 2.5.5 > SS64bit over a machine with Win7 64bit with 4GB Ram, CPU Pentium G620. > > I seems to work very well in local but with the 3 clients on LAN works > very bad. It is very very slow to open and navigate a simple table with > less of 5.000 rows! > > What i can do? You have to sort out where your current bottleneck is. Can you pin-point it to a particular query or is performance in general bad? With Firebird 2.5, you have quite some monitoring amory, e.g. monitoring tables (tables starting with MON$...) or the Trace API. Try to get a big picture with that tooling to see what's going, statement execution plans etc..., I/O statistics, e.g. is data being fetched from disk or served from the Firebird cache, which I doubt with your page buffers value below. > > I very novice in Firebird. > > The result of gstat is: > Database header page information: > Flags 0 > Checksum12345 > Generation 26613 > Page size 4096 > ODS version 11.2 > Oldest transaction 26603 > Oldest active 26604 > Oldest snapshot 26604 > Next transaction26605 > Bumped transaction 1 > Sequence number 0 > Next attachment ID 139 > Implementation ID 26 > Shadow count0 > Page buffers0 > Next header page0 > Database dialect3 > Creation date Jan 4, 2016 15:44:10 > Attributes force write > > Variable header data: > Sweep interval: 2 > *END* With SuperServer and a page size of 4096 and a page buffers set of 0, which gets overriden with a default value of 2048 from firebird.conf (if not specified otherwise), you end up with a Firebird page cache of 8MB (!) only for your database. Increase "Page buffers" to e.g. 1 (e.g. with gfix command-line tool), then re-connect your client application and see if this helps. There is other tuning stuff, but as a first step increasing the Firebird page cache + getting a big picture on your slow statements, their execution plan probably pointing to missing indexes ... is a good start. Good luck. -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc. ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ 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: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] Firebird Download Malware
>> I downloaded FB 2.5.5 from firebirdsql and my Microsoft Essentials >> detected it as a malware. > > I am facing the same problem. > The following file is affected: > http://sourceforge.net/projects/firebird/files/firebird-win64/2.5.5-Release/Firebird-2.5.5.26952_0_x64.exe/download > > This seems to be a false alarm though. > According to Virustotal, 54 different scan engines say the file is > clean: > https://www.virustotal.com/de/file/e4ce70fd1be17b07fd26554ef322ee14f65fef8d398aaa707df115530aa0f410/analysis/ > > Funnily, the Microsoft scan engine at Virustotal also considers it as > clean. Avira Antivir locally does not report anything suspicious. -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc. ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ 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: https://info.yahoo.com/legal/us/yahoo/utos/terms/