Hello Hector, what version of firebird are you running, super server, super classic or classic server?
Jesus Angel Garcia Zarco Cointec > El 5/9/2015, a las 18:00, Hector Sánchez [email protected] > [firebird-support] <[email protected]> escribió: > > 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]>: >>>> >>>> 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 >
