Re: [firebird-support] Software speed issue
On Wed, 17 Jul 2013 16:32:19 -0400, "Dom Kasony" wrote: > We are running Firebird 2.5.2 and the software we use that utilizes > firebird > is very slow in some areas such as compiling reports. Do to caching and > speed, they suggested that we increase the page size and percent of RAM > that > can be used. We did this and saw little to no increase in speed. Our > database is 120 GIG and we have 96 gig of RAM available which we are only > using 5 gig of that 96 gig. Can anyone suggest settings that can be > changed/activated (they originally had all of the commands in the > firebird.config file commented out so they were not active) and what values > we can try to increase the speed? We want their software to be much faster > than it is and they have no other ideas. Thank you Take a step back and ask yourself if you should looking at the low level caching options to solve performance options. These settings are not a panacea that will simply fix performance problems. Caching only helps if the data has been requested before and has not been evicted from the cache before it is needed again. You are talking about generating reports, which are usually on previous time periods, large aggregates than normal application use or data not (very recently) requested. I'd suspect the cache is not the bottleneck, so tweaking the cache won't help (much) with loading quicker. And if a report is very complex to query, sort and/or build, that can be a performance bottleneck on its own. I would seriously consider looking at other factors like the queries used to produce the reports (eg use the trace facility to get more information on the queries used, execution plans and their times and IO stats). Also check if the current indexes are sufficient for these queries. Another thing to look at is if the way the report is generated is actually efficient. For example at my current job an in-house app using SQL Server took 3 minutes to generate a report, simply because a new employee fresh out of college iterated over the result of a query to fire additional queries for each row in that query, replacing that query + loop with queries by a single (slightly) more complex query brought the run time down to 15 seconds (which is still relatively slow due to other inefficiencies and missing indexes, but it is fast enough for our in-house use). Mark
RE: [firebird-support] Software speed issue
1. Windows 2008 R2 Enterprise 64bit 2. Not sure on the version all they said was 2.5.2 when they upgraded us. 3. FileSystemCacheSize = 70. They put this in at the very end of the conf file with NO # signs around it. Every other line in the conf file starts with #. Example: # Number of cached database pages # This sets the number of pages from any one database that can be held # in cache at once. If you increase this value, the engine will # allocate more pages to the cache for every database. By default, the # SuperServer allocates 2048 pages for each database and the classic # allocates 75 pages per client connection per database. # # Type: integer # #DefaultDbCachePages = 2048 4. 31 users connected From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Leyne, Sean Sent: Wednesday, July 17, 2013 5:14 PM To: firebird-support@yahoogroups.com Subject: RE: [firebird-support] Software speed issue > We are running Firebird 2.5.2 and the software we use that utilizes firebird is > very slow in some areas such as compiling reports. Do to caching and speed, > they suggested that we increase the page size and percent of RAM that can > be used. We did this and saw little to no increase in speed. Our database is > 120 GIG and we have 96 gig of RAM available which we are only using 5 gig of > that 96 gig. Can anyone suggest settings that can be changed/activated (they > originally had all of the commands in the firebird.config file commented out > so they were not active) and what values we can try to increase the speed? > We want their software to be much faster than it is and they have no other > ideas. Thank you What OS/bitness are you running? What engine version (SuperClassic) are you running? What are the database page cache settings? How many active database connection are their? [Non-text portions of this message have been removed]
Re: [firebird-support] Software speed issue
At 08:32 a.m. 18/07/2013, Dom Kasony wrote: [..] >Can anyone suggest settings that can be >changed/activated (they originally had all of the commands in the >firebird.config file commented out so they were not active) Not true. If a setting is commented out then it is the default and thus active. The rider here would be that, if someone had activated a non-default setting at some point and then someone later came and commented that out, the configuration for that parameter would revert to the default. Helen Borrie, Support Consultant, IBPhoenix (Pacific) Author of "The Firebird Book" and "The Firebird Book Second Edition" http://www.firebird-books.net __
RE: [firebird-support] Software speed issue
> We are running Firebird 2.5.2 and the software we use that utilizes firebird > is > very slow in some areas such as compiling reports. Do to caching and speed, > they suggested that we increase the page size and percent of RAM that can > be used. We did this and saw little to no increase in speed. Our database is > 120 GIG and we have 96 gig of RAM available which we are only using 5 gig of > that 96 gig. Can anyone suggest settings that can be changed/activated (they > originally had all of the commands in the firebird.config file commented out > so they were not active) and what values we can try to increase the speed? > We want their software to be much faster than it is and they have no other > ideas. Thank you What OS/bitness are you running? What engine version (SuperClassic) are you running? What are the database page cache settings? How many active database connection are their?