Hi Svein,

Svein Erling Tysvær wrote:

> Hi Ferda!
>
> Does this slowdown appear after a massive delete or update (regardless
> of which transaction deleted/updated the records), maybe on a table
> with low selectivity indexes?

No, this is not the case I beleive. It happens also with rarely updated
tables and explicit garbage collection does not help. It can affect
such queries like "SELECT * FROM small_table WHERE primary_key_column=value".

> I just seem to remember reading something about it being a good idea
> to run a select count(*) on tables after doing such deletes, since
> garbage collection otherwise could be left to a more or less random
> transaction.

Count query is being run more often then we would like although it
typically takes form "SELECT COUNT(1) ... {JOINs} ... WHERE ...".
Does it have the same effect?

We have got automatic sweeping disabled because it imposes unacceptable
load and takes days to process. So it is less harmful for us to drop 24/7
service and go offline after massive updates and make backup and restore
which does the job mauch faster.

> If this is your problem, then others on this list will know lots about
> it (it should be a pretty common "problem"), it just happens to be one
> of the things that very rarely affects me since I hardly ever do
> massive updates/deletes.
>
> Since no-one have answered you, I guess it is more likely that this
> only was a problem some versions ago and that it is completely
> irrelevant for your problem. But it's better to receive possibly wrong
> advice than no advice, so I'll send it to the list just in case,

We have got the most recent public release of FB and I beleive that
somebody must met the problem if it is not somewhere between heaven
and earth.

> HTH,
> Set

Thanks much for your response.
Ferda

>
> --- In firebird-support@yahoogroups.com, Ferda Brabenec wrote:
>
>>Hi,
>>we are running Firebird Superserver 1.5[1-2] on a Linux server. It
>>serves data to a web application. We are accessing FB from a
>>multithreaded CORBA backend server through ODBC (UnixODBC driver
>>manager + Easysoft ODBC FB/IB driver).
>>
>>Everything works fine until moderate load appears. Then randomly
>>some of SQL* calls are processed 100-1000x slower then usually. We
>>started to log timestamps of some execution points when the
>>processing gets too slow. It seems that the slowdown may happen
>>with any arbitrary ODBC call such as SQLNumResulCols,
>>SQLDescribeCol, SQLBindCol, SQLExecute. Sorrounding ODBC calls from
>>a given thread are regularly fast.
>>
>>This slowdown appears even if a low number of db connections (such
>>as 5) is being used. Our proprietary connection pool allocates
>>additional connections when needed but this is not the case.
>>
>>We have tried 2 Xeon-based servers with various setup combinations
>>of HW : single/double cpu, , HT on/off
>>kernels : 2.4/2.6, smp/non-smp, pthread/nptl
>>
>>and also we tried to collocate/dislocate application modules on 1-3
>>hosts and nothing seems to affect these slowdowns.
>>
>>Classic server perfomance was noticably weaker so we did not
>>experiment with that build at all.
>>
>>So I want to ask if anybody has/had similar problem or if anybody
>>has a clue how to cope with this.
>>
>>TIA, Ferda
>
>
>
>
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://firebird.sourceforge.net 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
>
>
>
>
>
>
>