k_smith_000 wrote:

>
> I've quite big database (2GB) which I decided to test:
> 1.I've made backup/restore (so all garbage were swept)

If you're starting with a newly restored database and running the same
scripts on both systems, then I think you can eliminate garbage
collection as the source of the problem.

> 2.I've prepared some benchmark scripts:
> -selecting data using indexes
> -selecting data not using indexes (SELECT * FROM table WHERE field
> LIKE 'B%')

Err, that does use an index. The LIKE operator special cases a fixed
argument that starts with a constant. LIKE '%B%' does not use an index.

>...
>
> I've run benchmark on the same database but on different servers.
>
> Computer1:
> 2CPUs PIV HT2.4 512Mb RAM, SCSI drives
> Gentoo Linux 2.4
>
> Computer2:
> 2CPUs PIV HT3.2 4GB RAM, SCSI drives
> Fedora Core 1 Linux 2.4 (with NPTL)
>
> Oddly the first computer performed task in 3 minutes while it took 30
> minutes to accomplish jobs for 2nd server.

Hunh. 10 to 1 is really odd. What's HT? Sorry to be an ignoramus.
Interesting that the fast computer has 1/8 the memory of the slow
computer. What happens if they both run the same version of Linux?

> What is interesting when I
> run top utility to monitor CPU/mem usage during benchmark first
> computer was showing almost 100% CPU usage and all firebird processes
> running,

That's pretty much what you want, especially if the processes are
actually getting work done, which it appears they are. I wish Firebird
were more disk bound, but that's a different issue.

> while 2nd server has cpu usage at almost 0% level, with only
> 1 firebird process running (the rest firebird processes were running
> but were in sleep state). Do you thinks it may have connection with
> process communication and NPTL kernel running od 2nd server?

There's something grossly wrong there.

>
> All servers were running with the same Firebird 1.5.1 CS and
> firebird.conf (with slightly increased DbCache)

Are they both the NPTL build? (Sorry, I can't keep in my head which
Linuxes are NTPL and which aren't. And a failure in threading could
cause the behavior you're seeing.)

>
> In a few days I'm going to install other release of Fedora on second
> computer to test whether Linux distribution has any impact on firebird
> performance. I'll send a post to inform you with the results.

Please do.

>
> This is why I recently decided to turn off sweep interval and perform
> sweep every night.
> Moreover I decided to automatically set index statistics every night.

Those are both good decisions.

>
> ... it would be nice if one could find documents
> related to tuning firebird parameters.

A significant project goal is to minimize the number of tuning
parameters by thinking about the problems they're meant to address and
designing the system to handle those problems rather than dropping them
on the unfortunate dba. And we should increase the default lock table
size and the default hash table width - those were designed for
computers of the 1980's, where every byte of memory counted. Sigh.

Regards,


Ann