[firebird-support] mapping destroyed by backup / restore

[FSPAPA INCA Team i...@foodstuffs-si.co.nz [firebird-support]]

Hi – I have another question re user mapping…

If I create a new database with a user->role mapping, I can back up and restore 
it and the mapping is still present.
But if I back up the new copy and restore that, the mapping is gone… is there 
an issue with the procedure I'm using or is this a bug?
(for context, we have Firebird databases here that have inherited a regular 
backup/restore cycle from when we were using InterBase 5 or 6… apparently they 
had a tendency to slow down if not refreshed this way, but that is from before 
my time!)

Here's a demonstration using Firebird 3.0.5:


1. Create database with mapping

PS C:\Users\PGMRSD1> c:\apps\firebird\isql.exe
Use CONNECT or CREATE DATABASE to specify a database
SQL> create role foo;
SQL> create mapping sjd_foo using plugin win_sspi from user "foodstuff\pgmrsd1" 
to role foo;
SQL> exit;


2. Check mapping

PS C:\Users\PGMRSD1> c:\apps\firebird\isql.exe 
C:\temp\mapping-backup-restore\FOO.FDB
Database: C:\temp\mapping-backup-restore\FOO.FDB, User: PGMRSD1
SQL> show users;
Users in the database
  1 #PGMRSD1
SQL> show roles;
   FOO
SQL> show mapping;
SJD_FOO USING PLUGIN WIN_SSPI FROM USER foodstuff\pgmrsd1 TO ROLE FOO
*** Global mapping ***
TRUSTED_AUTH USING PLUGIN WIN_SSPI FROM ANY USER TO USER
SQL> exit;


3. Back up database – verbose output shows mapping saved

PS C:\Users\PGMRSD1> c:\apps\firebird\gbak.exe -v -stat tdrw -b 
C:\temp\mapping-backup-restore\FOO.FDB 
C:\temp\mapping-backup-restore\FOO.FDB.bak
gbak: time delta  reads  writes
gbak:0.104  0.104 40  0 readied database 
C:\temp\mapping-backup-restore\FOO.FDB for backup
gbak:0.107  0.002  0  0 creating file 
C:\temp\mapping-backup-restore\FOO.FDB.bak
gbak:0.112  0.004  0  0 starting transaction
gbak:0.115  0.003 21  1 database 
C:\temp\mapping-backup-restore\FOO.FDB has a page size of 8192 bytes.
gbak:0.118  0.003  0  0 writing domains
gbak:0.122  0.003  6  0 writing shadow files
gbak:0.123  0.001  0  0 writing character sets
gbak:0.125  0.001  0  0 writing collations
gbak:0.127  0.001  1  0 writing tables
gbak:0.128  0.001  0  0 writing types
gbak:0.131  0.002  5  0 writing filters
gbak:0.132  0.001  2  0 writing id generators
gbak:0.134  0.001  3  0 writing exceptions
gbak:0.135  0.001  2  0 writing functions
gbak:0.137  0.001  2  0 writing stored procedures
gbak:0.139  0.002  2  0 writing packages
gbak:0.141  0.001  2  0 writing triggers
gbak:0.143  0.002  0  0 writing trigger messages
gbak:0.145  0.002 12  0 writing security classes
gbak:0.148  0.002  2  0 writing table constraints
gbak:0.150  0.002  0  0 writing referential constraints
gbak:0.152  0.002  1  0 writing check constraints
gbak:0.154  0.001  0  0 writing SQL roles
gbak:0.155  0.001  0  0 writing SQL role: FOO
gbak:0.157  0.002  0  0 writing names mapping
gbak:0.159  0.001  3  0 writing map for SJD_FOO
gbak:0.199  0.002104  1 total statistics


4. Restore database – verbose output shows mapping preserved

PS C:\Users\PGMRSD1> c:\apps\firebird\gbak.exe -v -stat tdrw -c 
C:\temp\mapping-backup-restore\FOO.FDB.bak 
C:\temp\mapping-backup-restore\BAR.FDB
gbak: time delta  reads  writes
gbak:0.004  0.004  0  0 opened file 
C:\temp\mapping-backup-restore\FOO.FDB.bak
gbak:0.219  0.214  0  0 transportable backup -- data in XDR format
gbak:0.222  0.002  0  0 backup file is compressed
gbak:0.224  0.001  0  0 backup version is 10
gbak:0.362  0.137  0666 created database 
C:\temp\mapping-backup-restore\BAR.FDB, page_size 8192 bytes
gbak:0.365  0.003  0  2 started transaction
gbak:0.366  0.001  0  0 restoring SQL role: FOO
gbak:0.368  0.001  0  0 restoring names mapping
gbak:0.370  0.002  0  0 restoring map for SJD_FOO
gbak:0.375  0.004  0 13 creating indexes
gbak:0.402  0.027  0 34 committing metadata
gbak:0.404  0.002  0 10 fixing views dbkey length
gbak:0.407  0.002  0  0 updating ownership of packages, procedures 
and tables
gbak:0.409  0.002  0  0 adding missing privileges
gbak:0.411  0.002  0  0 adjusting system generators
gbak:0.418  0.006  0  3 finishing, closing, and going home
gbak:0.421  0.002  0728 total statistics
gbak:adjusting the ONLINE and FORCED WRITES flags


5. Confirm mapping present in restored database

PS C:\Users\PGMRSD1> c:\apps\firebird\isql.exe 
C:\temp\mapping-backup-restore\BAR.FDB
Database: C:\temp\mapping-backup-restore\BAR.FDB, User: PGMRSD1
SQL> show users;
Users in the database
  1 #PGMRSD1
SQL> show roles;
   FOO
SQL> show mapping;
SJD_FOO 

Re: [firebird-support] Page buffers & cache threshold

[Kjell Rilbe kjell.ri...@marknadsinformation.se [firebird-support]]

Den 2020-03-08 kl. 10:45, skrev liviuslivius liviusliv...@poczta.onet.pl 
[firebird-support]:
> About 3
> "DefaultDbCachePages" in firebird.conf is used only at server start. 
> Then restart is needed. But you have 2 more options and you should 
> check it maybe you override default. Buffers you can set also in 
> databases.conf and in db file itself by gfix buffers.
>
> And it is used in priority.
> 1. buffers set in db by gfix if 0 then go to point 2.
> 2. Setting in databases.conf if not present go to point 3.
> 3. Setting from firebird.conf

Ah, that explains the limited firebird.exe process commit size at least. 
Apparently I have 131072 set in the database, so it overrides the 
setting n databases.conf. I wasn't aware of that priority order. Will 
change DB setting to 0 to be able to control it from databases.conf.

Thanks!

Other questions remain...

Regards,
Kjell



[Non-text portions of this message have been removed]

RE: [firebird-support] Page buffers & cache threshold

[liviuslivius liviusliv...@poczta.onet.pl [firebird-support]]

About 3"DefaultDbCachePages" in firebird.conf is used only at server start. 
Then restart is needed. But you have 2 more options and you should check it 
maybe you override default. Buffers you can set also in databases.conf and in 
db file itself by gfix buffers.And it is used in priority.1. buffers set in db 
by gfix if 0 then go to point 2.2. Setting in databases.conf if not present go 
to point 3.3. Setting from firebird.confRegards,Karol Bieniaszewski
 Oryginalna wiadomość Od: "Kjell Rilbe 
kjell.ri...@marknadsinformation.se [firebird-support]" 
 Data: 08.03.2020  10:10  (GMT+01:00) Do: 
firebird-support@yahoogroups.com Temat: [firebird-support] Page buffers & cache 
threshold 
 



  



  
  
  Hi,

Trying to figure out the best settings for my database and app usage. 
Firebird 3.0.4 SuperServer on virtualized Windows Server 2016 with 40 
Gbyte RAM and fast SSD disks (separate disks for OS and DB). Database 
almost 200 Gbyte. Page size 16k.

We can assume that no other heavy work is running on the server. The app 
itself uses a few Gbyte RAM.

I have set in databases.conf:
DefaultDbCachePages = 1310720
FileSystemCacheThreshold = 640K

And in firebird.conf:

FileSystemCacheSize = 50%
TempCacheLimit = 4G
MaxUnflushedWrites = 100
MaxUnflushedWriteTime = 60

All other settings (except some security ones) are set to default.

1. Would you recommend any changes to these settings, and why?

2. I see that the firebird.exe process has commit size about 2.2 Gbyte, 
which seems small considering the DefaultDbCachePages = 1310720 setting. 
Shouldn't this result in a page cache of 20 Gbyte?

3. I did change the DefaultDbCachePages setting without restarting the 
superserver process, but I did make sure all connections to the database 
in questions were closed. Does this setting take effect only when the 
server process is restarted?

4. It's not clear to me how the FileSystemCacheThreshold setting 
actually works. I have read the release notes for FB 2.5 as well as the 
comments inside firebird.conf and a couple of support list messages. But 
I still find it a bit muddy... I can see that the database file is 
cached, using RAMMap.exe. It reports "Standby" as "28733844 K" for that 
file.

It seems that the file system cache works and keeps large parts of the 
database file in the cache, but I do realize that the batch will for the 
most part need to read "new" data. The important thing ought to be to 
keep the indexes in cache. Right?

Reason/background: The use case is a batch operation that will do mostly 
(only?) reads and will read large parts of the database. The batch does 
its job in "chunks", and I can see that after a while the time to 
process each chunk goes up from about half a minute to about a full 
minute, or even worse. Restarting the app makes it go back to about half 
a minute per chunk, but just stopping the batch operation (without 
restarting the app) doesn't seem to help. I will investigate this more, 
of course. It's possible that the app has some garbage piling up that 
might slow things down.

Mvh,
Kjell

Kjell Rilbe
Telefon: 0733-44 24 64

Marknadsinformation i Sverige AB
Sundbybergs Torg 1 H 7 tr
172 67 Sundbyberg, Sweden
www.marknadsinformation.se 
08-514 905 90

Företagskontakt.se  
Personkontakt.se  DI Gasell 2018DI Gasell 
2019

[Non-text portions of this message have been removed]




 

[firebird-support] Page buffers & cache threshold

[Kjell Rilbe kjell.ri...@marknadsinformation.se [firebird-support]]

Hi,

Trying to figure out the best settings for my database and app usage. 
Firebird 3.0.4 SuperServer on virtualized Windows Server 2016 with 40 
Gbyte RAM and fast SSD disks (separate disks for OS and DB). Database 
almost 200 Gbyte. Page size 16k.

We can assume that no other heavy work is running on the server. The app 
itself uses a few Gbyte RAM.

I have set in databases.conf:
DefaultDbCachePages = 1310720
FileSystemCacheThreshold = 640K

And in firebird.conf:

FileSystemCacheSize = 50%
TempCacheLimit = 4G
MaxUnflushedWrites = 100
MaxUnflushedWriteTime = 60

All other settings (except some security ones) are set to default.

1. Would you recommend any changes to these settings, and why?

2. I see that the firebird.exe process has commit size about 2.2 Gbyte, 
which seems small considering the DefaultDbCachePages = 1310720 setting. 
Shouldn't this result in a page cache of 20 Gbyte?

3. I did change the DefaultDbCachePages setting without restarting the 
superserver process, but I did make sure all connections to the database 
in questions were closed. Does this setting take effect only when the 
server process is restarted?

4. It's not clear to me how the FileSystemCacheThreshold setting 
actually works. I have read the release notes for FB 2.5 as well as the 
comments inside firebird.conf and a couple of support list messages. But 
I still find it a bit muddy... I can see that the database file is 
cached, using RAMMap.exe. It reports "Standby" as "28733844 K" for that 
file.

It seems that the file system cache works and keeps large parts of the 
database file in the cache, but I do realize that the batch will for the 
most part need to read "new" data. The important thing ought to be to 
keep the indexes in cache. Right?

Reason/background: The use case is a batch operation that will do mostly 
(only?) reads and will read large parts of the database. The batch does 
its job in "chunks", and I can see that after a while the time to 
process each chunk goes up from about half a minute to about a full 
minute, or even worse. Restarting the app makes it go back to about half 
a minute per chunk, but just stopping the batch operation (without 
restarting the app) doesn't seem to help. I will investigate this more, 
of course. It's possible that the app has some garbage piling up that 
might slow things down.

Mvh,
Kjell



Kjell Rilbe
Telefon: 0733-44 24 64

Marknadsinformation i Sverige AB
Sundbybergs Torg 1 H 7 tr
172 67 Sundbyberg, Sweden
www.marknadsinformation.se 
08-514 905 90

Företagskontakt.se  
Personkontakt.se  DI Gasell 2018DI Gasell 
2019


[Non-text portions of this message have been removed]