Hi Lawrence, > We have a 5GB Database FB2.5 Win2008 Server 4 Core M/C, 143 tables, problem > with table INV 48K Records
Which Firebird architecture? > When everyone logs off and then log on, access to INV is slow for the last 7K > Records. If we fetch all, it takes forever, but eventually can log off and > log on again and all is ok. Same is OK if we set all indexes ACTIVE (takes 4 > hours for this table), or do a backup. Also if we sweep. this takes the same > or more time. > > Questions: > > If we backup and restore the database is like new? data exported and then > reloaded into a copied schema? This seems to work. > > Can sweep occur if the database has connections, but no activity? If it did, > then we would not get all the sweep operations stacked up. > > Why does sweep (or the slow backup) take so long and if so, why is there no > cpu load? I would have thought that the CPU would have been busy. > > In the app - one transaction and all datasets/queries/procedures are > commitretaining. Evil. ;-) > And, and why always this table - treated much the same as other tables. > > Are there special settings for the database connection? and how can we know > that this might happen (so we could backup and restore before the last user > logged out)? This all sounds like the usual problem with inproper client transaction management, possibly additionally with cooperative GC or sweeping taking place. - Run gstat -h when the database is slow - Use the MON$ tables to identifiy long-running transactions - Use the Trace API to identify problematic stuff. I won't do shameless product plugs, which would ease some work, here now. ;-) -- With regards, Thomas Steinmaurer (^TS^) Firebird Technology Evangelist http://www.upscene.com/ http://www.firebirdsql.org/en/firebird-foundation/
