I recently resumed my work on optimizing firebird performance after system
hibernation.

Helen, thanks for your tips. I increased the database page size to 16K. The
current production database size is 30MB.

When the system hibernates it writes its memory state to disk. When it comes
back from hibernation the memory state is restored from disk. So, my guess
was that the slow-down after hibernation was caused by reading memory state
from disk. Moreover, the slow-down was only observed on regular hard drives
and not on solid-state drives.

Since Embedded server is configured similar to SuperServer, it uses
2048-page DB cache by default (right?). I reduced DefaultDbCachePages to 500
and noticed a 3-fold improvement in the first query performance after
hibernation. This change, however, somewhat slowed down average query
performance, which is an acceptable trade-off.

I do not completely understand which firebird processes use memory cache and
I wonder if there is a way to reduce the memory cache without giving up too
much performance?

Thanks,

Alec

On Thu, Nov 19, 2009 at 4:57 PM, Helen Borrie <helebor@...> wrote:

>
>
> At 10:15 AM 20/11/2009, you wrote:
> >Sean,
> >
> >The database is on the local hard drive.
> >The query returns 4,000 rows each of which is pretty wide, so there is a
> lot
> >of I/O involved.
> >The query itself is pretty simple and just selects from a
> >single table while filtering on an indexed field.
>
> Look at the selectivity of the index on the filtered field. It smells like
> a case where an index has such poor selectivity that it actually makes the
> indexed read less efficient than the natural one, e.g.
>
> select * from atable where yes_no = 'Y' would be pretty horrible. Try
> either removing that index, or making it more selective by adding the
> primary key to it (at the right), or introducing some hint that will
> suppress use of that index, such as
> WHERE
> (INDEXED_FIELD = :AVALUE AND 0=0)
>
> If your search index is multi-key then run SET STATISTICS on that index. If
> you find that helps, make it something you do so often as is needed.
>
>
> >What setting should I consider changing in firebird.conf in order to make
> a
> >query like this faster?
>
> If these are sorted queries (ORDER BY, GROUP BY, DISTINCT) and plenty of
> RAM is available then you could consider tinkering about with the Sort*
> parameters. Use typical data for testing it, though, and test it on the
> actual machine.
>
> If, as you say, the rows are pretty wide, e.g., wider than 4Kb, you might
> like to backup the database and restore it with an 8Kb page size. Take the
> size of the database cache into account, though, since doubling the page
> size will double the size of the cache, which won't be helpful if the laptop
> is short of memory.
>
> None of these things will help speed up the wake-up time from hibernation,
> of course, since Firebird's memory structures are as much victim to the
> system's strategies as any others, including how it deals with the FS cache
> (which a well-configured Firebird server usually relies on for good
> performance). And laptops often have very slow disks...which matters for
> recovery time from hibernation if the machine went into hib with any
> significant degree of RAM utilisation.
>
> ./heLen
>
>
>


[Non-text portions of this message have been removed]