> I'm probably not good enough, technically, to contribute too much > here, but nevertheless I would like to share my experiences. We are > running Classic (still 1.5! in the process of migrating to 2.5) for > many years now. Serving 6 databases concurrently used by some 50 users > and 3 different applications. > > Database sizes are not too large, up to 1 GB, biggest queries are > processing some 200.000 records, some StoredProcs are processing some > 4-5 million records. > > That's up to 6*50*3 == 900 concurrent processes on the CS (yes, plus > the mother process, plus 2 for gbak at times, plus a couple of > zombies). A realistic average is some 300 concurrent processes at any > time. > > Still using 1.5, my options of hunting down the regular slowdowns > is quite limited in comparison with the wonderful things possible > today. > > But I went through this often enough, using SysMon tools on the > server (Windows Server 2003). > > Almost always, the cause for a slowdown is when 1 or 2 users are > running complex reports. At this time, only 1 or 2 users are using up > to 80% of CPU (50% max per process), and are causing thousand times as > many disk writes as all the other 298 processes together. > > But while the queries of these 2 also run quite slow, the effect on > all the other connections is much more drastic. You might say that the > server is just not idle enough to serve 298 other connections in a > decent way, because of just those 1 or 2. > > The effects are worse if the database has not been backup/restored for > some time (2 months), and/or if "the GAP" is unusually big. > > I successfully managed to improve the situation over the years > - Revised all read-only queries to indeed only run read-only instead > of read/write > - Close connections as soon as possible > - avoid Rollbacks as often as possible > - rethinking and redesigning the transaction management in the > applications
Beside SQL tuning for short-term improvements, proper client transaction management is a MUST for every Firebird-based client application and the top-hitter for mid/long-term improvements. Thus, the Firebird 2013 tour dedicated to Firebird transaction is a pretty good move, IMHO: http://www.firebirdsql.org/en/firebird-conference-tour-2013/ > In addition, using new technologies for the applications made it > possible to reduce overall serverload dramatically (e.g. ADO.net). > > I changed one long-running report only recently and now it runs for > 40 seconds instead of 10 minutes. The reduced time is not only good > for the person running the report, but also for the server and thereby > for all other users. > > Somewhere along the way, I *also* tried to change architecture to > SuperServer, but I can assure you that this was only making things > much worse. > > Today, we only very rarely still have problems with slowdowns. And I > even have the database sweeping at normal intervals. > > What gives? > If you want to analyse what's going on and what you need to do to > improve it, don't just look at the queries which are slow, but most of > all at those which are running concurrently! Re-thinking transaction > management and application technology might bring much much more speed > than tweaking any of the server parameters. Concurrent statement execution can also mean concurrent (random) I/O from a single rotating disk. -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
