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 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. >>>>we sometimes get queries, that normally complete in reasonable time, taking >>>>many times as long >>> >>> This sounds like you are running into the automation database sweep, >>> what is the sweep interval for the database? >> >>Turned off. > Hmm, do the queries suddenly or gradually drop performancewise? If > gradually, it could be a problem with one or more long-running > transactions. If suddenly (and repeatedly) it could be > a) that Firebird (incorrectly) thinks another plan would be better > b) running under a WAIT transaction if you're trying to update > something that (an)other person(s) has changed but not committed yet > These are at least a few possibilities, there could be others. > Set > ------------------------------------ > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ > Visit http://www.firebirdsql.org and click the Resources item > on the main (top) menu. Try Knowledgebase and FAQ links ! > Also search the knowledgebases at http://www.ibphoenix.com > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ > Yahoo! Groups Links mit freundlichen Grüßen, André Knappstein EDV und Controlling ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ beta Eigenheim- und Grundstücksverwertungsgesellschaft mbH Hafenweg 4 59192 Bergkamen-Rünthe Telefon: +49 2389 9240 140 Telefax: +49 2389 9240 150 e-mail: [email protected] Amtsgericht Hamm Nr. B 420 Geschäftsführer: Achim Krähling, Dirk Salewski und Matthias Steinhaus USt-IDNr.: DE 125215402
