Hi,
For superserver this should be as high as possible. Why you recommended 10000
value? I always set it to highest possible value in current environment
compared to size of database. And i always disable system cache
Regards,Karol Bieniaszewski
-------- Oryginalna wiadomość --------
Od: "Alexey Kovyazin [email protected] [firebird-support]"
<[email protected]>
Data: 05.09.2015 19:06 (GMT+01:00)
Do: [email protected]
Temat: Re: [firebird-support] About firebird performance
Hi Hector,
I discovered something else quite
interesting:
I realized both IB and FB dbs have pagebuffers set to
100.000. I changed FB to 0 and now it works like I
expected!!..Same query now completes on FB on 50 sec. It
seems this is the problem although I don't understand
why.
100 000 is too high for Firebird SuperServer, it should be set to
10000.
You can use optimized Firebird configuration files
http://ib-aid.com/en/optimized-firebird-configuration/
Regards,
Alexey Kovyazin
IBSurgeon
Nevertheless, I'll try on a physical machine as you
and Carlos suggest and see how it behaves.
2015-09-05 15:47 GMT+02:00 Alexey
Kovyazin [email protected]
[firebird-support] <[email protected]>:
Hi Hector,
Thanks for statistics, it clarifies
situation.
There is 304880 data pages, each is 4kb, so
Firebird need to read 1.1Gb of data to count
records.
Count uses NATURAL scan, so it's a simple
set of disk reads, CPU is not involved.
Regular SATA3 disk should read 30-70Mb per
second (I assume database is not fragmented,
since you told it was freshly restored), it
gives us estimation from 15 to 30 seconds.
So, I think there is a disk IO problem.
Try it on another computer (better not
virtual).
Regards,
Alexey
He again Alexey,
As I suspected, my problem is not
caused by GC:
gstat over the table reported this:
TABLE1 (503)
Primary pointer page: 9374,
Index root page: 9375
Average record length:
317.63, total records: 3261775
Average version length: 0.00,
total versions: 0, max versions: 0
Data pages: 304880, data page
slots: 304880, average fill: 90%
Also, as you suggested ran the
query twice a got exaclty the same
times: 51minutes to complete!!!
I must have something obvious which
is killing firebird performance, but
cannot guess it.
Any help would be appretiated.
2015-09-05
11:59 GMT+02:00 Alexey Kovyazin
[email protected]
[firebird-support]
<[email protected]>:
Hi Hector,
So you finally decided
to try Firebird - that's
good.
I think that you are
facing garbage
collection problem - if
there are a lot of
record versions in the
TABLE1 which are not
interested to any
transaction, your query
will force garbage
collection, and it can
take some time.
How to check? run SELECT
count(*) twice and
compare execution times,
and other query
statistics
(reads/writes).
Also, run gstat -a -r
<database> and
check information for
TABLE1 - VERSIONS and
MAX VERSIONS, if there
are big numbers there,
problem is certainly
related with record
versions, with the
initial cause in wrong
transactions management
- i.e., long-running
write transactions or
forced rollbacks.
Regards,
Alexey Kovyazin
IBSurgeon
I'm doing some
basic tests with
Firebird and I'm
facing something I
cannot understand
(I'm a newbie to
Firebird which has
worked with
IB7.5 for years, so
I apologize if I ask
something obvious):
- I have a linux
server with firebird
2.5.4 Superserver:
multi-core (4), 3GB
RAM
- I placed a "huge"
database: 1000
tables, 9GB
- When I issue a
query like this
"select count(*)
from TABLE1" it
takes very long time
to complete (>30
min).
- TABLE 1 has 3
million records
As I told you, I'm
quite used to IB7.5,
but I guess this is
not normal
at all...probably
I'm missing
something but I
couldn't find any
document which gives
me an idea of what
I'm doing wrong.
With same hardware
an IB7.5 same query
took 40 sec to
complete (first
time issued, no
cache).
Could anyone help?
--
--
Planatec Software
S.L. **
<http://www.planatec.es>
telf: +34
964 340 560 **
fax: +34
961 130 921
--
Planatec Software S.L. ** <http://www.planatec.es>
telf: +34
964 340 560 ** fax: +34
961 130 921
--
Planatec Software S.L. ** <http://www.planatec.es>
telf: +34 964 340 560 ** fax: +34 961 130 921
