Subject Re: Active transactions (benchmark results - updated)
Author k_smith_000
"Adam" <s3057043@y...> wrote:
> > It seems that sometimes firebird hangs and I don't know exactly what
> > the database is doing...
> You may want to volunteer a bit more information about what you mean by
> hang. Does it take a long time to establish a connection, or does the
> CPU stay pegged at 100%?
It was weird situation, which I'll try to present.
I've quite big database (2GB) which I decided to test:
1.I've made backup/restore (so all garbage were swept)
2.I've prepared some benchmark scripts:
-selecting data using indexes
-selecting data not using indexes (SELECT * FROM table WHERE field
LIKE 'B%')
(all queries were performed on table with few millions of records)
3.After that I've launched script from separate computer (to simulate
TCP/IP not pipe connection ).
At the beggining I started with 10 simultaneous connections.

I've run benchmark on the same database but on different servers.

2CPUs PIV HT2.4 512Mb RAM, SCSI drives
Gentoo Linux 2.4

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. 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, 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?

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

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.

> In SS, there is a dedicated thread that does garbage collection, in CS,
> it is whatever transaction happens to stumble across it. Due to the way
> Firebird 1.5 and earlier handle the index, large sequences of identical
> values in an index can really hamper garbage collection. Firebird 2
> will fix this to a great extent. For the moment, you could analyse the
> output of gstat which may help identify the problem.
> Running a nightly sweep (or even a nightly backup) may help too.
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.

If you have the same or similar experiences please answet this post. I
think that eventually some document tackling firebird and linux should
be written. Furthermore it would be nice if one could find documents
related to tuning firebird parameters. I've find quite interesting
document on IB Phoenix page that were related to garbage collector,
indexes. That was a great job but Firebird to be a fully competitive
database has to possess better documentation. The great job was done
already, but also a lot need to be performed!

Best regards,
Kevin Smith