Erik LaBianca wrote:

> I too have been having problems with insufficient performance due to
> lack of memory. I have a table of approximately 4.5 million records
> which I am working with. The .fdb file ends up using around 1GB of disk
> space, although adding a bunch of indexes push it up around 1.8GB.
>
> Among other things I need to be able to 'walk' the database in sorted
> order setting a 'sequence number'. I have a stored procedure to do this
> which works great, but takes several hours. It can take anywhere from 1
> to 6 to more than I was willing to wait, depending on platform and
> optimization settings. The disk is under very heavy I/O the entire time.
> I was able to improve performance a good bit but changing my procedure
> to work on 'subsets' of the table instead of trying to sort the entire
> table at once and then walking it, but it is still very slow.
>
> Reducing the record count to around 1million and database size to around
> 250MB results in the unoptimized procedure running in about 7 minutes.
>
> I'm using 8192 byte pages and 65535 buffers (aka I'm maxed out all
> around). My machine has 4GB of ram in it, so I'd like to be able to use
> that ram effectively, but since I can't increase the page cache any more
> and firebird doesn't seem to benefit much (any?) from the filesystem
> buffer cache I seem to be bottlenecked.
>
> I checked out the 2.0 release notes but I didn't see anything indicating
> that firebird's ability to use excess resources had improved, am I
> missing something, or is there some other tuning I could do to make
> better use of my machine?
>
> Thanks
>
> --erik
>
>

Erik,

I think you could use 16k page size, taking a max of 2GB of RAM.

The option for memory usage on sort operations on firebird.conf could help.

But I could bet that sorting 2 million records will always be slow.

see you !

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