[firebird-support] Re: Issue with Database Cache Size on FB 3.0

[Dmitry Yemanov dim...@users.sourceforge.net [firebird-support]]

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

[Alexey Kovyazin (ak) a...@ib-aid.com [firebird-support]]

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

[Fabian Ernesto Chocron fabia...@itbizolutions.com.au [firebird-support]]

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

[Gabriel Frones grfro...@gmail.com [firebird-support]]

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?

[Alexey Kovyazin a...@ib-aid.com [firebird-support]]

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?

[Doychin Bondzhev doyc...@dsoft-bg.com [firebird-support]]

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

[Dalton Calford dcalf...@distributel.ca [firebird-support]]

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?

[Fabian Ernesto Chocron fabia...@itbizolutions.com.au [firebird-support]]

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