Thanks Alexey!! As you have read, setting pagebuffers to 0 on the database solved the problem and now it works as I expected.
2015-09-05 19:06 GMT+02:00 Alexey Kovyazin [email protected] [firebird-support] <[email protected]>: > > > 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]> > [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]>[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> >> 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
