| Subject | Re: [firebird-support] Classic vs Superserver |
|---|---|
| Author | André Knappstein |
| Post date | 2013-08-30T10:01:13Z |
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.
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: knappstein@...
Amtsgericht Hamm Nr. B 420
Geschäftsführer: Achim Krähling, Dirk Salewski und Matthias Steinhaus
USt-IDNr.: DE 125215402
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 longmit freundlichen Grüßen,
>>>
>>> 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
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: knappstein@...
Amtsgericht Hamm Nr. B 420
Geschäftsführer: Achim Krähling, Dirk Salewski und Matthias Steinhaus
USt-IDNr.: DE 125215402