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 a...@ib-aid.com
<mailto:a...@ib-aid.com> [firebird-support]
<firebird-support@yahoogroups.com
<mailto:firebird-support@yahoogroups.com>>:
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 a...@ib-aid.com
<mailto:a...@ib-aid.com> [firebird-support]
<firebird-support@yahoogroups.com
<mailto:firebird-support@yahoogroups.com>>:
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>
<http://www.planatec.es>
telf: +34 964 340 560 <tel:%2B34%20964%20340%20560> ** fax:
+34 961 130 921 <tel:%2B34%20961%20130%20921>
--
Planatec Software S.L. ** <http://www.planatec.es>
telf: +34 964 340 560 <tel:%2B34%20964%20340%20560> ** fax: +34
961 130 921 <tel:%2B34%20961%20130%20921>
--
Planatec Software S.L. ** <http://www.planatec.es>
telf: +34 964 340 560 ** fax: +34 961 130 921
