| Subject | Re: [firebird-support] Strange User connected to Database "(SQL Server)"; big slowdown |
|---|---|
| Author | Helen Borrie |
| Post date | 2004-12-10T00:49:14Z |
At 02:33 PM 9/12/2004 -0800, you wrote:
garbage collector thread. Under normal conditions, its presence is
undetectable because it does what it has to do and logs out. If it's
working long enough for you to detect it with IBExpert, then you are
looking at an extraordinarily high amount of GC activity.
This may or may not indicate a problem in your application. If GC has been
held up for a very long time because of a stuck transaction, then GC will
be a long process once the stuck transaction is released (committed or
rolled back). However, a huge delete operation inside one transaction will
also create a lot of garbage, quite naturally. From the GC point of view,
it's best to moderate such an operation by breaking it into chunks of
around 8000 rows (or less, if the rows being deleted are very large).
struggling to cope with the level of garbage that is being generated by the
apps. If you disable sweep, the "slowdown" will gravitate to a total hang
or crash when the server reaches the state where the garbage overwhelms it.
If you have this level of garbage persistently accumulating in the system,
you don't want to eliminate sweep. You can disable automatic sweeping by
setting the sweep interval to zero; but, if you do that, you must take
explicit control of it and take care to run sweep manually on a
sufficiently regular basis. How frequently depends on whether your
applications can be fixed to reduce or eliminate the buildup of garbage
that the GC thread either can't touch, or can't cope with in a timely fashion.
If it seems that this situation has developed because of some frequent huge
delete or update operation, then make a point of following that operation
with a sweep. You can sweep from a script or batchfile. If you are
frequently deleting all of the records in a table, consider deleting and
recreating the table instead.
You should also look at the index statistics, to see whether a regular
rebuild of certain indexes is indicated. For example, a regular large
batch insert of data where all or most of the records have the same value
in an indexed column, will tend to unbalance the index and cause it to
inhibit performance when it is used in searches and sorts.
./heLen
>Hello all,Nope. But the server connects this user: as Martijn explained, it is the
>
>In the past few weeks one of our clients (FB 1.0.3) has started experiencing
>severe (database operation, queries mostly) slowdown of our application that
>seem random. It will occur for an hour and then disappear.
>
>One of the other symptoms, seems to be (so far) a "(SQL Server)" user showing
>in IBExpert. Yes, the parentheses are included. We do not have any such
>user in
>the database user list.
garbage collector thread. Under normal conditions, its presence is
undetectable because it does what it has to do and logs out. If it's
working long enough for you to detect it with IBExpert, then you are
looking at an extraordinarily high amount of GC activity.
This may or may not indicate a problem in your application. If GC has been
held up for a very long time because of a stuck transaction, then GC will
be a long process once the stuck transaction is released (committed or
rolled back). However, a huge delete operation inside one transaction will
also create a lot of garbage, quite naturally. From the GC point of view,
it's best to moderate such an operation by breaking it into chunks of
around 8000 rows (or less, if the rows being deleted are very large).
>There are many other things wrong with this customer's implementation, butSweep also makes its own connection to the database.
>this
>is so odd, I needed some feedback. I've been using Interbase and Firebird for
>9+ years and have never seen this before. At first, I thought it might be the
>Sweep or something, but that's inconsistent with my experience.
>My questions:Could be either sweep or GC, one or the other at different times.
>
>1) What is this "(SQL Server)" user that IBExpert shows intermittently?
>2) What could be the cause of the slowdowns? Sweep?
>3) What can I do to eliminate both? I know how to eliminate sweep, but seemsThe slowdown is a strong sign that the applications need surgery. GC is
>like that's a problem, too. So I'd have to turn it on and off in batch process
>at night or something?
struggling to cope with the level of garbage that is being generated by the
apps. If you disable sweep, the "slowdown" will gravitate to a total hang
or crash when the server reaches the state where the garbage overwhelms it.
If you have this level of garbage persistently accumulating in the system,
you don't want to eliminate sweep. You can disable automatic sweeping by
setting the sweep interval to zero; but, if you do that, you must take
explicit control of it and take care to run sweep manually on a
sufficiently regular basis. How frequently depends on whether your
applications can be fixed to reduce or eliminate the buildup of garbage
that the GC thread either can't touch, or can't cope with in a timely fashion.
If it seems that this situation has developed because of some frequent huge
delete or update operation, then make a point of following that operation
with a sweep. You can sweep from a script or batchfile. If you are
frequently deleting all of the records in a table, consider deleting and
recreating the table instead.
You should also look at the index statistics, to see whether a regular
rebuild of certain indexes is indicated. For example, a regular large
batch insert of data where all or most of the records have the same value
in an indexed column, will tend to unbalance the index and cause it to
inhibit performance when it is used in searches and sorts.
./heLen