[firebird-support] Re: Issue with Database Cache Size on FB 3.0
01.06.2016 04:16, Fabian Ernesto Chocron wrote: > > We are having trouble setting up the database cache size on FB 3.0 > running on Windows 2008 R2 64 bits with 32 GB ram. > > The problem we have is we cannot get the server to allocate the ram for > the cache as we intend. With FB 2.54 we had the DB cache set very high, > close to 1 GB per database, all running in RAM memory. With FB 3.0 we > read it can allocate much more RAM to the cache, but it appears the > server is allocating very small amount of Ram when the first user > connects to the DB, and as we connect more users to the DB the ram > consumption increases slowly. What memory counters do you look at? Virtual (reserved) memory usage should grow high with the first connect, physical (committed) memory usage should grow together with your load, while pages are being read from disk. > On firebird.conf > > FileSystemCacheThreshold = 0 > FileSystemCacheSize = 17179869184 (this is 16 GB - the server has 32 GB > ram.) These changes conflict with each other. You disable filesystem cache with the first line and still want to use 16GB with the second line. > On databases.conf > > MyTestDB = c:\Temp\MyDb.fdb > { > DefaultDbCachePages = 458752 > } First of all, I'd suggest to reset FileSystemCacheSize back to zero and set FileSystemCacheThreshold to something higher than 458752 pages (e.g. 50). Then re-test your memory usage and only then start tweaking the filesystem cache. BTW, don't you occasionally have page cache override at the database level? What is gstat -h output? Dmitry ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] Issue with Database Cache Size on FB 3.0
Hi Fabian, Firebird caches only actually used pages. The small cache means that your application touches the small part of the database. -- Regards, Alexey Kovyazin IBSurgeon PS It is difficult and wrong to give you any direct advice without all details in hands - it can lead to worse performance than with default parameters. среда, 01 июня 2016г., 04:16 +03:00 от Fabian Ernesto Chocron fabia...@itbizolutions.com.au [firebird-support] firebird-support@yahoogroups.com : > >Hi All > >We are having trouble setting up the database cache size on FB 3.0 >running on Windows 2008 R2 64 bits with 32 GB ram. > >The problem we have is we cannot get the server to allocate the ram for >the cache as we intend. With FB 2.54 we had the DB cache set very high, >close to 1 GB per database, all running in RAM memory. With FB 3.0 we >read it can allocate much more RAM to the cache, but it appears the >server is allocating very small amount of Ram when the first user >connects to the DB, and as we connect more users to the DB the ram >consumption increases slowly. The setting we are playing with are: > >On firebird.conf > >FileSystemCacheThreshold = 0 >FileSystemCacheSize = 17179869184 (this is 16 GB - the server has 32 GB >ram.) > >On databases.conf > >MyTestDB = c:\Temp\MyDb.fdb >{ >DefaultDbCachePages = 458752 >} > >Any ideas what could be wrong? Or what settings would give us maximum >RAM usage for the DB cache (we dont want file system cache meaning HDD >cache, we want to have the DB in RAM for the purpose of reading the DB) > >Cheers, >Fabian > >
[firebird-support] Issue with Database Cache Size on FB 3.0
Hi All We are having trouble setting up the database cache size on FB 3.0 running on Windows 2008 R2 64 bits with 32 GB ram. The problem we have is we cannot get the server to allocate the ram for the cache as we intend. With FB 2.54 we had the DB cache set very high, close to 1 GB per database, all running in RAM memory. With FB 3.0 we read it can allocate much more RAM to the cache, but it appears the server is allocating very small amount of Ram when the first user connects to the DB, and as we connect more users to the DB the ram consumption increases slowly. The setting we are playing with are: On firebird.conf FileSystemCacheThreshold = 0 FileSystemCacheSize = 17179869184 (this is 16 GB - the server has 32 GB ram.) On databases.conf MyTestDB = c:\Temp\MyDb.fdb { DefaultDbCachePages = 458752 } Any ideas what could be wrong? Or what settings would give us maximum RAM usage for the DB cache (we dont want file system cache meaning HDD cache, we want to have the DB in RAM for the purpose of reading the DB) Cheers, Fabian
Re: [firebird-support] Stored Procedures Execution Plan
It was sent and received... but this is working for me out of the box in FB 2.5.4. I just write the select * from SP and ask for plan and it gives me the plan for the inside statements. Haven't tested on non-selectable SPs. Em qua, 25 de mai de 2016 às 08:20, João Paulo Carvalho joao_paul...@yahoo.com [firebird-support]escreveu: > > > Hi. > > > Since this was my first post in this list, I'm not sure if it was sent. > > If you already get it, or this is not the right place to discuss this kind > of topic, I would be very grateful if you tell me where is. > > Att. > João Paulo > > > -- > *DE:* "joao_paul...@yahoo.com [firebird-support]" < > firebird-support@yahoogroups.com> > *Para:* firebird-support@yahoogroups.com > *Enviadas:* Sexta-feira, 20, Maio 2016 15:54:07 > *Assunto:* [firebird-support] Stored Procedures Execution Plan > > > > Hi. > > > I would like to know if it is possible to display the plan of stored > procedure statements. > > I'm interested in the plan of the statements inside the stored procedure > (SP), not the plan just informing that I'm executing an SP. > > We are running also selectable SP's. > > Att. > João Paulo > > > > >
Re: [firebird-support] how to keep history of statements per transaction?
Hi, use Trace API - the basic variant with text output is included into Firebird. If you need GUI and retrospective analysis, you can try HQbird (trial version of Standard is 14 days), it contains PerfMon tool with TraceAPI support More details in HQbird Guide, page 66 http://ib-aid.com/download/docs/hqbird2016r1userguide.pdf Regards, Alexey Kovyazin IBSurgeon www.ib-aid.com Hi, I'm looking for easy to use solution that can help me trace statements executed inside every transaction and keep them until transaction is active. Once it is closed(commit or rollback) statements are discarded. I need this to be able to run on a production system in order to track which code path leaves open transaction or keeps transaction open for long time. Any ideas how to make this? -- Doychin Bondzhev dSoft-Bulgaria Ltd. PowerPro - billing & provisioning solution for Service providers PowerStor - Warehouse & POS http://www.dsoft-bg.com/ Mobile: +359888243116 [Non-text portions of this message have been removed]
[firebird-support] how to keep history of statements per transaction?
Hi, I'm looking for easy to use solution that can help me trace statements executed inside every transaction and keep them until transaction is active. Once it is closed(commit or rollback) statements are discarded. I need this to be able to run on a production system in order to track which code path leaves open transaction or keeps transaction open for long time. Any ideas how to make this? -- Doychin Bondzhev dSoft-Bulgaria Ltd. PowerPro - billing & provisioning solution for Service providers PowerStor - Warehouse & POS http://www.dsoft-bg.com/ Mobile: +359888243116 [Non-text portions of this message have been removed]
[firebird-support] Firebird 3 on Kubuntu 16.04
Hey All, Does anyone have the latest install guides for Firebird 3 on systemd based Ubuntu systems? I installed firebird, gave it the password but the install had output as follows Created default security3.fdb Failed to start firebird3.0-server.service: Unit firebird3.0-server.service not found. Firebird 3.0 server not enabled or unable to start Not setting SYSDBA password Please run 'dpkg-reconfigure firebird3.0-server' Tried dpkg-reconfigure, still no startup, so I tried systemctl status firebird3.0.service and I got the following in return ● firebird3.0.service - Firebird Database Server ( SuperServer ) Loaded: loaded (/lib/systemd/system/firebird3.0.service; disabled; vendor preset: enabled) Active: failed (Result: resources) since Tue 2016-05-31 09:49:39 EDT; 24s ago Process: 11352 ExecStart=/usr/sbin/fbguard -pidfile /var/run/firebird/3.0default.pid -daemon -forever (code=exited, status=0/SUCCESS) systemd[1]: Starting Firebird Database Server ( SuperServer )... firebird[11355]: Security database error systemd[1]: firebird3.0.service: PID file /var/run/firebird/3.0default.pid not readable (yet?) after start: No such file or directory systemd[1]: firebird3.0.service: Daemon never wrote its PID file. Failing. systemd[1]: Failed to start Firebird Database Server ( SuperServer ). systemd[1]: firebird3.0.service: Unit entered failed state. systemd[1]: firebird3.0.service: Failed with result 'resources'. >From what I am reading, it appears firebird will not startup until the sysdba >password is set, yet, the configuration tools won’t set the sysdba password >until the server starts? Does anyone have any indication about what I am doing wrong here? Best regards Dalton Calford / Senior Systems Database Administrator, Information Systems DISTRIBUTEL 56 Aberfoyle Crescent Toronto(Ontario) M8X 2W4 T 416.969.8154 www.distributel.ca
[firebird-support] Risks - Experience and doubts regarding upgrading to FB 3.0 from 2.54?
Hi Again We are about to upgrade from Windows 2008 FB 2.54 64 bits into Windows 2012 R2 FB 3.0 64 bits, and would appreciate comments from experienced DB administrators. We use FB as the main DB, and we access it via 32 bit native ODBC (the latest version of Firebird ODBC). I would appreciate if someone can give us a list of risks associated with the upgrade upfront, I have read other posts where some issues were experienced such as having to convert Stored Procedures and triggers and views, but I am not sure why would that be, in our case we did a test conversion to 3.0 and all SPs and Triggers and Views recompiled with no issues, so I am not really sure if we are missing something or in our case the problem does not apply because we have always used standard SQL 92 when writting SPs and Triggers. Also any upfront advise would be highly appreciated. >From our point of view what we are after with the upgrade is: 1) Get parallel requests to be assigned to different CPUs, improving performance when a big process is being executed by another user. 2) Be able to execute backups excluding some tables ( we read this is a new feature) 3) Be able to use Nbackup very often, like every 20 minutes, without killing the performance of the database, as it would run on an isolated processor, we understand the HDD would still be under the pump, but we estimate other system users will not be as affected as on FB 2.54 because of the SMP feature. 4) DB encryption, we are planning to use this feature 5) Communications encryption, we are also planning to use this feature. 6) More DB pages into ram cache under 64 bit server, we would like to dedicate 96 GB ram to the DB cache, under FB 2.54 the limit was 131071 DB pages at 16KB each page, meaning 2GB ram per database. Any comments are highly appreciated! Cheers Fabian