[firebird-support] Monitoring and troubleshooting server with lots of db's

Hello all. Our data server is hosting about 100 databases for about as much as different clients. Usually the CPU load is very low, but we have some nasty spikes where the cpu gets to 100% for a long time. It's been very hard for us to find the culprit looking at the application side, since

[firebird-support] Foreign Key index being inactivated automatically

Hello. Is there any situation in which an index would be inactivated automatically by the system? We recently been debugging a database in which 2 indexes related to foreign keys mysteriously got inactivated. We are not sure if this might happen during a backup/restore, or any other

RE: [firebird-support] Question about delay in fetch operation

>Look about your where clause and also if your inner join catch whole index >segments (explained plan can >help here). >Also increase cache of this database. Thanks for the feedback Karol. Generally it’s very hard for us to optimize the bigger queries, we usually try to shift joins and

[firebird-support] Question about delay in fetch operation

Hello. Since the topic died down I would like to give it another go and explain all that transpired, in the hopes of trying to understand this behavior. I am unable to determine is this is normal/intended behavior, or something that might be worth being looked upon by the devs. -I have a

RE: [firebird-support] Question about delay in fetch operation

>Two last things come to mind... >- a memory issue on the server >What happens if you change page buffers? >- a disk issue on the server >What happens if you leave the original file on the server and try a copy of >the database >on a different disk location >on a different disk >on a

RE: [firebird-support] Question about delay in fetch operation

>Read Release Notes. Embedded mode is used automatically for local connections. Sorry. In that case, yes, I tried with embedded too.

RE: [firebird-support] Question about delay in fetch operation

>>Look at gstat output >>You probably have very long version this particular record >Hi Karol. >I checked gstat and made a manual sweep of the database, but the problem >persists. >There are some things I’m finding odd : >-The query brings 4791 records total >-Always on record 4000, it

RE: [firebird-support] Question about delay in fetch operation

>Look at gstat output >You probably have very long version this particular record Hi Karol. I checked gstat and made a manual sweep of the database, but the problem persists. There are some things I’m finding odd : -The query brings 4791 records total -Always on record 4000, it hangs and

[firebird-support] Question about delay in fetch operation

I have a query that returns about 5000 records, that “should” run without problems. However, when Im fetching the records in my applications loop, it always hangs for several seconds on an specific record. All the other records are fetched fast as expected, but when it gets to record 4096 it hangs

RE: [firebird-support] Firebird optmizer cop-out

>Join order can be forced by using outer join. >Using of indexes can be disabled by changing of simple field usage to an >expression. >Preferences between ORDER and SORT plans (more commonly between "first row" >and "all >rows" strategies) can be changed by using FIRST/ROWS. >Index statistics

[firebird-support] Firebird optmizer cop-out

>From the docs about the optmizer, there is a small section : Cop-Out The user can pick or influence the optimizer’s decision. The Firebird optimizer actually uses all three Could anyone explain me further how the user can go about to effectively pick or influence the optimizers decision?

[firebird-support] Informing the devs about optimizer issues / alternative plans

Hello. We have been trying to optimize one of our largest databases (firebird 3.0.4) , and in doing so, we managed to greatly improve the execution of one specific query by manually changing the PLAN. I am wondering if the firebird team is interested in receiving detailed feedback of such

[firebird-support] Diagnosing CPU usage

Hello I have a situation which is hard to debug. We are running about 50 databases (firebird 3.0.3) on a single server, all of them connected to different applications. CPU usage is normaly low, but sporadically it will peak at 100% and cause problems for all the connections. We have no way

[firebird-support] Fbtracemgr not working

Trying to run fbtracemgr is returning a windows message “this app can’t run on your pc”, and in the prompt it prints “Access is denied” Windows 8.1, firebird 3.0.3 Have tried elevated prompt, setting the .exe as “run as administrator”, and changed both firebird.conf and fbtrace.conf in

RE: [firebird-support] Trying to figure out if CORE-5615 is on fb 3.0.3

Hi Vlad Yes, I was forbidden by the company to update the live server with a snapshot release, and since the bug only happens on the live server I could not test it. I will update it with 3.0.3 and report back if it still crashes, thanks. Atenciosamente, Rudi Feijó Multidados

[firebird-support] Trying to figure out if CORE-5615 is on fb 3.0.3

Hello I’m trying to figure out if the changes made on core-5615 where pushed to the stable 3.0.3 version (its not listed in the changelog). If it were not, does the newest snapshot contain it? Thanks (im sorry for the duplicate topic) Atenciosamente, Rudi Feijó Multidados Informática

[firebird-support] CORE-5615 on fb 3.0.3?

Hello I’m trying to figure out if the changes made on core-5615 where pushed to the stable 3.0.3 version (its not listed in the changelog). If it were not, does the newest snapshot contain it? Thanks Atenciosamente, Rudi Feijó Multidados Informática Ltda. * (11) 2579-8794 / 2579-8795

RE: [firebird-support] CTE query works on fb 2.5 but not on fb 3.0

Thanks a lot Mark ! >Your problem has nothing to do with CTEs, you are combining implicit > (SQL-89) joins and explicit (SQL-92) joins, and referencing the >implicitly joined table from the explicitly joined tables. The rules for >these have changed in Firebird 3 to be compliant with the SQL

[firebird-support] CTE query works on fb 2.5 but not on fb 3.0

Good afternoon. I’ve been assigned to debug a query that was working on 2.5 but stopped workin on 3.0. As of now I have little information on the context of where this query is used, but I’m assuming it might be something simple. Apparently what’s causing the error is using this FROM

RE: [firebird-support] Firebird 3 crashes, unable to capture crash dumps ( terminated abnormally (4294967295) )

>but did you checked your DB against corruption? >gfix –validate –full >any errors (also in firebird.log)? The server have about 20 db’s, haven’t performed a validate full on each one of them. At the moment Im assuming theres no corruption because all of our clients are fully operational

[firebird-support] Firebird 3 crashes, unable to capture crash dumps ( terminated abnormally (4294967295) )

Hello It’s been a while since I gave na update on my situation here. To recap, firebird 3 is crashing randomly on my server. It may crash once, twice, or 10 times a day. Or not once during the day. Server load is always minimal during the crashes, and so is the memory usage. With the help

RE: [firebird-support] Re: Firebird 3 crashes ( terminated abnormally (4294967295) )

>How much virtual memory Firebird process is used before the crash ? >Any messages in the firebird.log near the crash time ? Vlad, not much memory, about 400k out of 12gb. There’s no other messages in the log before, just the crash msg “terminated abnormally (4294967295)” Currently I’m

RE: [firebird-support] UDF or/and maybe Charset problem

>You must use ib_util_malloc() to allocate memory in UDF and don't forget >FREE_IT in declaration to avoid memory leak. Dimitry, on a similar question, is there any problems if I just declare all UDF's with FREE IT? We use dozens of UDF's, but only half of them are declared as FREE IT, and I

[firebird-support] UDF not defined error but udf is defined

I’m having a weird error, sometimes the system crashes due to an UDF being not declared properly : “invalid request BLR at offset 877 function F_GFORMATD is not defined module name or entrypoint could not be found” But I can verify it is declared properly. The same query runs without

RE: [firebird-support] Firebird 3 crashes ( terminated abnormally (4294967295) )

>> Thank you for all the suggestions, I’ve changed the config as suggested, >> unfortunately the problem still happens. >> There seems to be little for me to investigate. >> The latest crashes all occurred when there was very little load on the >> server >> and on firebird. >> >> All access

RE: [firebird-support] Firebird 3 crashes ( terminated abnormally (4294967295) )

>> Thank you for all the suggestions, I’ve changed the config as suggested, >> unfortunately the problem still happens. >> There seems to be little for me to investigate. >> The latest crashes all occurred when there was very little load on the >> server >> and on firebird. >> >> All access

RE: [firebird-support] Firebird 3 crashes ( terminated abnormally (4294967295) )

Thank you for all the suggestions, I’ve changed the config as suggested, unfortunately the problem still happens. There seems to be little for me to investigate. The latest crashes all occurred when there was very little load on the server and on firebird. All access is done by php which

RE: [firebird-support] Firebird 3 crashes ( terminated abnormally (4294967295) )

About 20 databases Atenciosamente, Rudi Feijó Multidados Informática Ltda. * (11) 2579-8794 / 2579-8795 * rudi.fe...@multidadosti.com.br * www.multidadosti.com.br

RE: [firebird-support] Firebird 3 crashes ( terminated abnormally (4294967295) )

Thank you for the replies : >What is the size of the database pages? >Have you checked the DB level Page Cache setting (the DB can have a different >value from the config)? >How much RAM is available for the VM? Sean, theres 13gb of RAM, and no db with specific settings Page size is

[firebird-support] Firebird 3 crashes ( terminated abnormally (4294967295) )

I’ve been having quite a few crashes on a new server with not much databases or requests. terminated abnormally (4294967295) It seems to be very random, there are not many different operations going on. It’s a google cloud Windows vm, I’ve checked for hardware / driver problems but it seems

[firebird-support] firebird terminated abnormally (4294967295)

Our firebird installation have been terminating abnormally two or three times a day, for no apparent reason. There is hardly any usage of the servers yet (cpu/ram over 80% free/idle). There is no time patterns on when this occurs, it just does. How can I begin to diagnose this? Our server

[firebird-support] Idle connection timeout

Is there any way to configure idle connection timeout on silent connections? If its not configurable, can it be done by querying system tables and deleting idle registers from MON$ATTACHMENTS? I have a sub-optimal situation in my stack, with php and firebird. Php with fpm-fastcgi creates a

[firebird-support] FB persistent connections on php-fpm

As of late, we have been experiencing with persistent connections on our backend. This is justifiable, we have a very high number of connection requests (lots of requests from lots of users). We are using PHP-FPM connecting on firebird3 superserver. We quickly run into memory usage issues -

RE: [firebird-support] Firebird 3 superserver configuration for high load

Those are for 2.5 aren’t them? The only ones I found there are at https://ib-aid.com/br/optimized-firebird-configuration/ I was guessing there are different values for cache size on superserver? Atenciosamente, Rudi Feijó Multidados Informática Ltda. * (11) 2579-8794 / 2579-8795 *

[firebird-support] Firebird 3 superserver configuration for high load

Hello We have recently installed FB 3 on SuperServer mode. >From the articles, we deduced this is the best mode for our high concurrency scenario (about 100 databases, 10gb in size, each with 50 to 300 simultaneous users doing CRUD operations on the same tables). Is there any published guide

[firebird-support] Multiple firebird servers accessing the same database

Is this possible, even in some unorthodox way? We are moving our systems to google cloud engine which supports auto-scaling groups of VM’s (VM’s are automatically created if needed). It would be great if I could have a group of firebird vm’s in a load balancing scenario like that.

RE: [firebird-support] Firebird Backups on Google Compute Engine

>> We are considering moving our systems to GCE and trying to figure the best >> approach to backup our many firebird databases. >> Firebird is not VSS aware so we can't use shadow copying. > >Not completely true. > >1- Firebird uses careful write order, so a VSS snapshot should be a

RE: [firebird-support] 2 servers acessing one database

Thanks for all the replies – it is more or less as I suspected. The shared SAN drive is very fast and provide the locking/visibility guarantees, it is designed to do so. But we won’t be following that option anymore, it is too much of a risk for us. We have over 200 big databases with lots of

[firebird-support] 2 servers acessing one database

Hello. Is it possible to use 2 pc’s with firebird installed, accessing a single database that resides in a shared storage area network (which is acessible to both servers)? Atenciosamente, Rudi Feijó Multidados Informática Ltda. * (11) 2579-8794 / 2579-8795 *

RE: [firebird-support] 2 servers acessing one database

Thanks for the reply Karol What I mean is using 2 servers to connect to the same DB. This DB is in a shared disk that both servers can access. So I would have, for example, 100 clients connecting on server 1, another 100 clients on server 2, but both accessing the same shared database.

[firebird-support] FB lock files

I noticed our biggest data server have a lot of hard disk activity creating fb_lock files. Firebird 2.5.4, superclassic My changed lock configurations are : #LockMemSize = 1048576 LockMemSize = 10097152 #LockHashSlots = 1009 LockHashSlots = 60589 Should I try to set them higher? If

[firebird-support] FW: Scale-ability problems (PHP + firebird)

We host a php + firebird app for several years now, but as of late we are running into some scalability issues mainly with firebird. One of our new clients have about 120 users each which makes intensive use of the system and many requests to the database. The queries/indexes are all

RE: [firebird-support] Changing process priority

Thanks Fabiano. After Reading a bit on the basics of proccess priority, I found out this same information about realtime. Changing it to “above normal” would be sufficient for me – it would be above the priority of simple copying made by windows explorer. But I cant just test it out on the

[firebird-support] Changing process priority

Is it safe to change firebird’s process priority to “above normal” , “high” or “realtime” ? We have been having problems with it set to “normal”. Sometimes admins will copy files or do other process that are as prioritized and that ruins the servers.

[firebird-support] FW: FB 2.5.1 db's needing restore

I noticed that, when upgrading to 2.53, it is highly advisable to restore db’s that were on 2.5.1, as stated in the docs : Warning re Databases Created or Restored under Firebird 2.5.1 All users upgrading from Firebird 2.5.1 to a higher sub-release are strongly advised to migrate databases