Hi Alexey, Thanks for the answer. I'll look at our options regarding the professional services.
Regards, Zoltan PS: I am familiar with database concepts like MVCC, transactions, locking and query execution planning but unfortunately I don't know how to investigate these mechanisms within Firebird :-( On Tue, Feb 11, 2014 at 9:58 AM, Alexey Kovyazin <a...@ib-aid.com> wrote: > > > Hi Zoltan, > > this problem is often topic here. > Solving this problem is a long boring process, where community will try to > explain you basics about transactions and transaction markers meaning, ask > you to send gstat statistics, then explain about record versioning and > garbage and long-running transactions, about advanced monitoring and so on. > In the end you will land with big3 - long-running transactions, bad plans > and wrong configuration, each is another big topic . > > In short, your database size looks pretty serious to afford professional > services > http://www.firebirdsql.org/en/professional-support/ > > Regards, > Alexey Kovyazin > IBSurgeon > PS If somebody here wants to go through steps described above, please > don't hesitate to do this. > > > > > > > Hi, > > 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? > > Thanks, > Zoltan > > > > -- Zizi "...nálatok a cégnél múltból nagyon sok van..."
