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.
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]> > [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> >> <http://www.planatec.es> <http://www.planatec.es> >> telf: +34 964 340 560 <%2B34%20964%20340%20560> ** fax: +34 961 130 921 >> <%2B34%20961%20130%20921> >> >> >> > > -- > 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
