H> --- In firebird-support@yahoogroups.com, Helen Borrie
H> <helebor@t...> wrote:

> At 04:17 PM 23/09/2004 +0200, you wrote:

>>Hi all,
>>
>>trying to tune up the performance of FB 1.5, we have found
>>
>>SortMemBlockSize
>>
>>and
>>
>>SortMemUpperLimit
>>
>>parameters in the firebird.conf configuration file.
>>
>>They seem to be used for so called in-memory sorting (used belike by
>>commands GROUP BY, ORDER BY...)
>>
>>What we really want to know is how these two parameters interact
>>together and what is the ideal setting for them.

H> There is no "ideal" setting but the defaults (1 Mb block size, up to a
H> limit of 64 Mb) are determined to be "useful". If a sort needs more space
H> than 64Mb, it will create the sort files in your configured sort space on
H> disk. It will do that sooner if available RAM gets tight before it hits
H> the 64-block limit.

Helen, thanks for your comments.
Our thought was SortMemBlockSize sets block size which can be used for
one complete in-memory sort (for example for one "ORDER BY" command).


Lets suppose (pure theory!!!!!:) :
FB 1.5 SS
max. 4 simultaneous database users
max. 4 in-memory sorting commands in one SELECT statement
2GB RAM available on the server

It means "theoretically" max. 16 (4 SELECT * 4 users) concurrently
running sorting commands, so I can dedicate for example 1GB of memory
for sorting (set up SortMemUpperLimit to 1024 MB) and divide it to 16
blocks (set up SortMemBlockSize to 64 MB => 1024/64 = 16).


The second important thing is how SortMemUpperLimit interacts with the
database page size and number of pages settings?

Because I have got a lot of memory available, I would like database to
work in memory as much as possible. That is the reason why I am trying
to find reasonable upper limits of the server settings.


H> On SS, connections share sort space, so you can cheerfully allocate a
H> higher limit or a larger block size (or both) if you are currently
H> under-utilizing memory. If your sorts are currently using disk sort space,
H> you should notice the difference.

H> On Classic, each connection gets a static 8 Mb, because each has its own
H> sort space. It's going to take a smaller hike in block size or upper limit
H> to consume your spare resources.

>>We are running FB under 64bit Linux (AMD Athlon) with 2GB RAM.

H> Lucky server. :-) Experiment!!
PS: server is not lucky yet, we are trying to install Linux AMD-64
compilation right now, which is not rally easy (all necessary drivers
are not available:) But we will win the battle :)))) !!!!!

Thanks a lot for your comments.
Petr
H> ./heLen