Questions[firebird](server)

I've been going crazy for 2 days now and I'm asking for help.

I have a program developed in delphi (early 2000s about) that accesses a firebird v3 database, currently installed on the same machine (windows server 2016 x64 - db and program are x86).

The machine is a VM configured on vmware esxi, I come to the point: if I configure the VM with only 1 vCPU the program runs very well, if I also configure it with 2 vCPUs (1 socket and 2 cores) or more, the performance is halved.

The problem is that by leaving only one vCPU, the cpu is perpetually at 100% even just for windows server jobs (eg search for updates and other stuff).

Do you have any advice?

PS: I can't switch to firebird x64 because many libraries are x86.

PPS: The OS on the VM is Windows Server 2016 Standard ed

I have a strange slowdown problem with a Firebird database. During the daily use of the database the clients experience significant slowdowns while the system still have lots of resources available. Some information about the environment:

• 64 bit Firebird 2.5.2 server running in SuperServer mode
• the database is running on a 64 bit Windows 2008 R2 server OS
• the server OS is running in a VMware 4.1 VM with 4 CPU cores and 16 GB of RAM
• the database size is around 37 GB and the number of concurrent connections to the database is around 150.

While observing the slowdowns:

• the CPU usage on the machine is between 40-60% without higher spikes and the load is nicely distributed among all 4 cores
• the memory usage of the server is around 4-6 GB and the rest of the memory is used as OS cache
• the disc queue lenghts almost never go over 0.3 with around 2-5 ms latency
• there is almost no network activity on the server.

Still, the slowdown seems to be linked to the general load on the server. During the night when no users are connected to the database/no background jobs are running a test query used for reference executes in 4-5 seconds while during the day when all the users are connected to the database executing the same reference query requires 60+ seconds to finish. It should be added though that the slowdown is general in nature, there are no specific queries that are slower while the server is under load, everything gets slower within the specific Firebird database. The server has other databases with a very low number of transactions executed daily and these other databases show no sign of slowdowns. I even created a copy of the live database experiencing slowdowns and executed the same query against both the originaly and the duplicate database - the original did execute the query slow and hte duplicate fast. The only difference between the original and the duplicate I know is the number of connected users/concurrent transactions.

As I found no evident reasons of all these in the available OS resources so I tried to fetch statistics from Firebird.

The observations:

• at peak times the databse has 30-40 transactions running in parallel according to mon$statements (where mon$state == 1 which according to the archives means the transactions are running or are waiting for a lock)
• fb_lock_print displays the following about the database:

LOCK_HEADER BLOCK

    Version: 145, Active owner:      0, Length: 2097152, Used: 1335440
    Flags: 0x0001
    Enqs: 9993237, Converts:  93191, Rejects: 1417230, Blocks:      2
    Deadlock scans:      0, Deadlocks:      0, Scan interval:  10
    Acquires: 19972846, Acquire blocks:      0, Spin count:   0
    Mutex wait: 0.0%
    Hash slots: 1009, Hash lengths (min/avg/max):    0/   2/   7
    Remove node:      0, Insert queue:      0, Insert prior:      0
    Owners (38):    forward:  20824, backward: 872088
    Free owners (126):      forward: 973360, backward: 728016
    Free locks (370):       forward: 852200, backward: 195936
    Free requests (12425):  forward: 614608, backward: 1230536
    Lock Ordering: Enabled

Here I noted that the "rejects" field accounts for ~14% of "enqs" field but unfortunately I don't know the exact meaning of these values. I guess about 14% of the lock requests are rejected for some reason but I might be completely wrong.

So the questions:

• How should the output of fb_lock_print interpreted in this case? Are these numbers "wrong" in some sense? Can they be improved by some parameter tuning?
• What additional steps should be taken to pinpoint what causes the slowdowns?

I have a server with 16 cores. What do I set the CpuAffinityMask to?

1111 1111 1111 1111 is 65535, should I put 65535?

Also, how to I configure the SuperClassic server?

This is for Firebird 2.5.

TIA

We've got a setup that we're using for different clients : a program connecting to a Firebird server on a local network.

So far we mostly used 32bit processors running Ubuntu LTS (recently upgraded to 10.04).

Now we introduced servers running on 64bit processors, running Ubuntu 10.04 64bit.

Suddenly some queries run slower than they used to.

In short: running the query locally works fine on both 64bit and 32bit servers, but when running the same queries over the network the 64bit server is suddenly much slower.

We did a few checks with both local and remote connections to both 64bit and 32bit servers, using identical databases and identical queries, running in Flamerobin.

Running the query locally takes a negligible amount of time: 0.008s on the 64bit server, 0.014s on the 32bit servers. So the servers themselves are running fine.

Running the queries over the network, the 64bit server suddenly needs up to 0.160s to respond, while the 32bit server responds in 0.055s.

So the older servers are twice as fast over the network, in spite of the newer servers being twice as fast if run locally.

Apart from that the setup is identical. All servers are running the same installation of Ubuntu 10.04, same version of Firebird and so on, the only difference is that some are 64 and some 32bit.

Any idea??

I tried to google it, but I couldn't find any complains that Firebird 64bit is slower than Firebird 32bit, except that the Firebird 2.1 change log mentions that there's a new network API which is twice as fast, as soon as the drivers are updated to use it.

So I could imagine that the 64bit driver is still using the old API, but that's a bit of a stretch, I guess.

Thanx in advance for any replies! :)

Does anybody know on how to install Firebird 2.0 on FreeBSD 7.2? I have updated the ports and the kernels, but when I try to do a "make install", I get the error:

Please do not build firebird as 'root' because this may cause conflicts with SysV semaphores of running services.

Any ideas?