Marek Konitz wrote:

>Hi,
>
>I've database with about 10 000 000 records in main table, max 30
>concurrent connections. Every client updates one record in that table
>every 1-2 minutes and from time to time selects a set of data with ca 30
>000 records. It run extremely slow...
>
>

What is extremely slow ? Do you really need the 30 000 records ?
What your query and the plan used ?

>The server is: p4 xeon 1,5GB RAM, Firebird 1.5.2, win 2003, database
>page size 8192
>
>

Disk configuration ? it's very important piece in databases...

>When fb was installed as superserver it used about 70MB of memory, now
>it's classic server and each connection consumes ca 5MB. Classic goes a
>bit faster than super, but still cpu usage is about 90-100% all the time.
>
>- I've already reviewed indices
>- I don't think that users could lock each other - every client works on
>separate set of records
>- I know what cpu affinity is - was set to 2 (second processor) with
>superserver
>- select count(*) from table takes several minutes(!) /tested both in
>ibexpert and application/
>
>

select count(*) from what table ? how much records it counted ???

>- client application uses ibx controls
>- after backup/restore works slightly better, but only for several hours
>
>

what is slightly ?

What are the database statistcs when the system is slow ?

do you commit frequently ? (not commit retain, I mean hard commits)

>Questions:
>- How can I increase amount of memory used by fb server?
>
>

use 16kb pages, use 8000 to 10000 page buffers, see what fits better for
you, do not use more then 10000 pages with FB 1.5, FB 2.0 has it corrected

for CS use a much lower number of page buffers !!!!

>- What parameters should I change in firebird.conf?
>
>

You don;t need to tweak much in the firebird.conf to get good performance.

>- Are there any 'hidden' (not mentioned in comments) parameters in
>firebird.conf? Any documentation about this file?
>
>

from waht I know only what is documented on the file, and you should not
be a "problem" with defaule configuration.

Using classic you could increase de lock table size, look for Ann's
message on the topic or read the excelent article she wrote to
Firebird/Interbase Developer Magazine

>- Superserver is said to be more efficient in such implementations, so
>why the classic is?
>
>

SS use a sahred cache and should use only one CPU to be efficient, in
SMP systems it it is allowed to usemore than 1 CPU it will slown down,
CS use a separated cache per connection, and could bennefit from SMP boxes

>- Have ibx controls negative influence on database server? I'm using
>mostly TIBSQL controls, rarely TIBQuery. Any filtering is done on the
>server side and result datasets minimized as much as possible.
>
>
>

I think the answer is more related to hwo you manage your transactions
then to the component suite you use to access FB

>I've asked already about firebird performance on this forum and you were
>surprised, why it's slow. I don't know if it might be bad application
>design/db design/server configuration? The application isn't very
>complicated, nor db structure is. Hope you can help - the number of
>records will increase and number of clients double in a short time... If
>you need more configuration details, just tell me.
>
>
>

Show us the problem queries, the used plan, indices and database statistics.

>Best Regards,
>
>Marek Konitz
>
>

see you !

--
Alexandre Benson Smith
Development
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br