Hi Steve and others,

I took some time to carefully inspect what is going on in there on the
server using sysstat package tools.

I found only one thing: When some of our ODBC calls are processed
very slowly the CPU load is getting very near to 1 at the same time.
And the load comes from fbserver process. All other activities seem
negligible to me.

It looks like thread scheduling gets thick when the db engine is loaded.

Lets say that I have 5 parallel queries. 5 connections serially
feeding queries to the server. Then I would expect something
like 10 times longer processing time for any query than in single
connection case, but not 100x - 1000x longer. Of course there will
always be fluctuations in processing times, but 2+ seconds for
a simple ODBC call seems to me more like kind of temporary deadlock.
FB engine gurus: am I wrong?

Thank you, Ferda

Steve Wiser wrote:

> Hi Ferda,
>
> First of all which distro are you using? You could use iostat to
> monitor the drive io (see:
> http://www.redhat.com/docs/manuals/linux/RHL-9-Manual/admin-primer/s1-bandwidth-rhlspec.html ). We only use classic server as it seems to handle smp better (from what I have read). We ended up using the following configuration for most of our db servers:
>
> multiple cpu's with HT turned on
> lots of memory
> 2 raid partitions -- 1 for the os, temp, and interbase, the other
> partition for the db directories
>
> Before we added enough memory we could watch (using iostat) io requests
> queue up and wait until they could be served. By splitting the
> partitions and adding more memory we no longer have to wait for disk io.
> We seem to be constrained by cpu speed which is fine for us at the
> moment. Does this help at all?
>
> -steve
>
> On Mon, 2005-06-13 at 18:14 +0200, Ferda Brabenec wrote:
>
>
>>Hi Steve,
>>
>>no I am not sure. Our HW environment is quite fine I guess. We've got
>>6GB
>>of RAM and >4GB is cached. 20GB free disk space seems reasonable for a
>>4GB
>>database, at least I think so. There are no other files on the volume
>>than
>>this database and its backups so I hope it is not trashed too much.
>>
>>We are monitoring cpu load, memory does not seem to be a problem.
>>What would you suggest for I/O usage monitoring?
>>
>>Thanks for your response,
>>
>>FERDA
>>
>>Steve Wiser wrote:
>>
>>>Hi,
>>>
>>> Are you sure the slowdown is Firebird related? We use Interbase
>>
>>5.6
>>
>>>on linux and when we were getting slowdowns like this it was traced
>>
>>back
>>
>>>to severe disk thrashing. Upping the memory from 1 GB to 8 GB
>>>completely fixed our problem as it seems that the CPUs can now
>>
>>access
>>
>>>the data they need without having to move stuff out of memory so
>>
>>often.
>>
>>>Just wondering if you have profiled the server while under this load
>>
>>and
>>
>>>checked out the cpu, memory, and io usage.
>>>
>>>-steve
>>>
>>>On Mon, 2005-06-13 at 13:45 +0200, Ferda Brabenec wrote:
>>>
>>>
>>>
>>>>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
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>>
>>>>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>>>>
>>>>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
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>>
>>>[Non-text portions of this message have been removed]
>>>
>>>
>>>
>>>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>>>
>>>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
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>
>>
>>
>>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>>
>>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
>>
>>
>>
>>
>>
>>
>>
>
>
>
> [Non-text portions of this message have been removed]
>
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> 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
>
>
>
>
>
>
>
>