Hi,

--- In firebird-support@yahoogroups.com, Marek Konitz <marekk@s...>
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 30000 records. It run extremely slow...

This numbers should be some chips to eat for FB.

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

database

> page size 8192

Format of disk? (cluster/RW-Block size)
if it is default then i think it is 4096, this means
when FB wants to read or write of any page to disk,
OS transparently read and write 2 different cluster from disk.
no one give you a guarantee they are on the same track and in wanted
order. Any active disk will be fragmanted in some minutes.

> 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

it is not enough, because it was allready designed by you, u should
show us your indexing strategy.

> - I don't think that users could lock each other - every client

works on

locks don't mean performance lost. lock posibility means more
sharing. if there is no lock, this means everybody works on different
scope of DBfile, this reduces help of cache.

> 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/

scoples counting will kill any MGA RDBMS system. count mean read all
wanted rows and count them.

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

hours

After restore there will be only one record versions and not
gragmanted DBfile (logically. Pyhsical fragmantaion depends on OS/HW)

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

check your querys or TEMP directory.
is there a SORT/file?
if so, then increase sortbuffersize in firebird.conf.
it will reduce disk usage and incrase RAM usage.

there is default 67 sort blocks, not enough for this days and
serious projects. even GSM phones has more RAM today. ;)

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

yes, if you read IB pdfs and make some search on ibphoenix.com
then you can understand what does any paramater mean.
there is also description top of any paramater.

> - Superserver is said to be more efficient in such

implementations, so

> why the classic is?

for support SMP with stoneage logic. better than nothing.
with vulcan, FB code will be monoblock.

> - 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'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.
>
> Best Regards,
>
> Marek Konitz
>

Regards.

Ali