Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Define a new option for DROP SHADOW command to leave the shadow file in the filesystem [CORE4955] #5246

Closed
firebird-automations opened this issue Oct 9, 2015 · 16 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Holger Klemt (klemmo)

Attachments:
core4955-ex_shadow-file-remains-in-shutdown.zip

We have very good experience with using shadows as a much faster backup alternative.
Up to fb254 we can do some changes on RDB$FILES to avoid the file delete, that is done
automaticaly when using DROP SHADOW.

It would be a very good feature for Firebird 3, when we can use the DROP SHADOW command with an additional
new option, that does not drop the shadow file from the filesystem.

We use this way especially in combination with RAM Disk to create and drop a shadow. Since the shadow file can
be used as a database directly, we save a lot of time compared to a full backup/restore process. Our
current way with changes in RDB$FILES will not work in FB3, but we and our customers would really like to have
such a new option to be able to use the same technology on fb3.

And yes, we know, that a shadow does no transaction reset, garbage collection etc., but here are some numbers i
showed a customer yesterday on my laptop (all operations were done on a 240 GB enterprise SSD in a i7 Laptop
with 8GB RAM, Customer database was 1.6 GB, almost no blobs, but a lot of records and a lot of indices)

Backup using gbak fb254 win64: 65 seconds
Restore using gbak fb254 win64: 410 seconds

overall around 8 minutes

and now using a script in ibexpert using a ramdisk and a shadow

-create shadow
-commit;
-update rdb$files
-commit
-drop Shadow
-commit

overall around 8 seconds!

Really, the whole script used 8 seconds! The customer was extremly impressed, especially since they
control very large machines using their software and firebird

We use the same way with database up to 10 GB and there we have a typical time of 30 seconds

This makes it possible for the customer to start the shadow based backup/restore replacement job every 30
minutes, since it only uses 30 seconds of high load. After creating the shadow on the ramdisk and dropping it,
it is copied to their backup server.

I think such an additional option for drop shadow statement should not be a bug deal for the core developers

Commits: 42b98af bcf2c0e FirebirdSQL/fbt-repository@3d7fb8b FirebirdSQL/fbt-repository@56dfac4

====== Test Details ======

Wait for reply on letter 11-oct-2015 16:53. Got different results for SS vs CS (and "sometimes" SC).

@firebird-automations
Copy link
Collaborator Author

Modified by: Holger Klemt (klemmo)

summary: Define a new option for DROP SHADOW command to leasve the shadow file in the filesystem => Define a new option for DROP SHADOW command to leave the shadow file in the filesystem

@firebird-automations
Copy link
Collaborator Author

Modified by: Holger Klemt (klemmo)

description: We have very good experience with using shadows as as much faster backup alternative.
Up to fb25 we can do some changes on RDB$FILES to avoid the filedelete, that is doen automaticaly when using DROP SHADOW.

It would be a very good feature for Firebird 2.x but at least for fb3, when we can use the DROP SHADOW command with an additional
new option, that does not drop the shadow file from the filesystem.

We use this way esepcially in combination with RAM Disk to create and drop a shadow. Since the shadow file can
be used as a database directly, we save a lot of time compared to a full backup/restore process and based on our
current way with changes in RDB$FILES, which will not work in FB3, we and our customers would really like to have
such a new option.

And yes, we know, that a shadow does no reorg etc., but here are some numbers i showed a customer yesterday
on my laptop (all operations were done on a 240 GB enterprise SSD in a i7 Laptop with 8GB RAM, Customer database
was 1.6 GB, almost no blobs, but a lot of records and a lot of indices)

Backup using gbak fb254 win64: 65 seconds
Restore using gbak fb254 win64: 410 seconds

overall around 8 minutes

and now using a script in ibexpert using a ramdisk and a shadow

-create shadow
-commit;
-update rdb$files
-commit
-drop Shadow
-commit

overall around 8 seconds!

Really, the whole script used 8 seconds! The customer was extremly impressed, especially since they
control very large machines using their software and firebird

We use the same way with database up to 10 GB and there we have a typical time of 30 seconds

This makes it possible for the customer to start the shadow based backup/restore replacement job every 30
minutes, since it only uses 30 seconds of high load. After creating the shadow on the ramdisk and dropping it,
it is copied to their backup server.

I think such an additional option for drop shadow statement should not be a bug deal for the core developers

=>

We have very good experience with using shadows as a much faster backup alternative.
Up to fb254 we can do some changes on RDB$FILES to avoid the file delete, that is done
automaticaly when using DROP SHADOW.

It would be a very good feature for Firebird 3, when we can use the DROP SHADOW command with an additional
new option, that does not drop the shadow file from the filesystem.

We use this way especially in combination with RAM Disk to create and drop a shadow. Since the shadow file can
be used as a database directly, we save a lot of time compared to a full backup/restore process. Our
current way with changes in RDB$FILES will not work in FB3, but we and our customers would really like to have
such a new option to be able to use the same technology on fb3.

And yes, we know, that a shadow does no transaction reset, garbage collection etc., but here are some numbers i
showed a customer yesterday on my laptop (all operations were done on a 240 GB enterprise SSD in a i7 Laptop
with 8GB RAM, Customer database was 1.6 GB, almost no blobs, but a lot of records and a lot of indices)

Backup using gbak fb254 win64: 65 seconds
Restore using gbak fb254 win64: 410 seconds

overall around 8 minutes

and now using a script in ibexpert using a ramdisk and a shadow

-create shadow
-commit;
-update rdb$files
-commit
-drop Shadow
-commit

overall around 8 seconds!

Really, the whole script used 8 seconds! The customer was extremly impressed, especially since they
control very large machines using their software and firebird

We use the same way with database up to 10 GB and there we have a typical time of 30 seconds

This makes it possible for the customer to start the shadow based backup/restore replacement job every 30
minutes, since it only uses 30 seconds of high load. After creating the shadow on the ramdisk and dropping it,
it is copied to their backup server.

I think such an additional option for drop shadow statement should not be a bug deal for the core developers

@firebird-automations
Copy link
Collaborator Author

Modified by: @AlexPeshkoff

assignee: Alexander Peshkov [ alexpeshkoff ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @AlexPeshkoff

Syntax is:
drop shadow <N> no file delete;
or
drop shadow <N> no delete file;
being not sure which form is better I've decided to have both.

In 2.5 it's definitely unreal.
Must say that it was a matter of days for such interesting suggestion to get or not to get into v.3 - after RC1 which is coming very soon nobody will add even simple features. Please try not to be so late in the future.

@firebird-automations
Copy link
Collaborator Author

Modified by: @AlexPeshkoff

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 3.0 RC 1 [ 10584 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

From the syntax POV, I'd rather suggest [PRESERVE FILE] or [{PRESERVE | DELETE} FILE].

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

+1 for Dmitry syntax.

@firebird-automations
Copy link
Collaborator Author

Commented by: @AlexPeshkoff

[{PRESERVE | DELETE} FILE] is great
will do

@firebird-automations
Copy link
Collaborator Author

Commented by: @pavel-zotov

I've used many times following trivial .sh for making "hot-copy" of database with size moire than 30 Gb at 20-30 seconds:

fbhome=/opt/fb30sc
dbn=oltp30
dbc=./$dbn.copy_$(date +'%Y%m%d_%H%M%S').fdb
$fbhome/bin/nbackup -L ./$dbn.fdb
cp ./$dbn.fdb $dbc
$fbhome/bin/nbackup -N ./$dbn.fdb
$fbhome/bin/nbackup -F $dbc
$fbhome/bin/gstat -h $dbc

No shadow required, no intermediate storage as ram-disk ('scp' can be used instead 'cp' to copy on remote host).
So, what's the profit of create shadow & dropping it then ?

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: No test => Deferred

Test Details: Wait for reply on letter 11-oct-2015 16:53. Got different results for SS vs CS (and "sometimes" SC).

@firebird-automations
Copy link
Collaborator Author

Commented by: @livius2

Do you think that will be good to have system option like
CREATE DATABASE DUMP FILE_NAME;

and this create this shadow and drop it immediately without deleting file
This is simpler and show the "new way" to backup database

@firebird-automations
Copy link
Collaborator Author

Commented by: @AlexPeshkoff

Karol, that's possibly good, but you've selected a bad place for discussing new feature - too few people read resolved tickets.
Devel list would be much better.

@firebird-automations
Copy link
Collaborator Author

Commented by: @pavel-zotov

I've found strange "persistent link" from ex-shadow file to its ex-root database, even when .sql script contain DROP SHADOW + COMMIT + RE-CONNECT.

Test files are in attached "core4955-ex_shadow-file-remains-in-shutdown.zip".
Scenario:

Window-1. Creates database and table with varchar indexed field and starts to fill it, thus doing "heavy DML"

Window-2.

1) Launches .sql script which creates shadow file and immediatelly drops it with PRESERVE FILE clause. Then makes COMMIT.
2) After returning from .sql on step "1" - runs batch which:
2.1) obtains header attributes from shadow file; this is marked as "point 0"
2.2) moves .fdb to OFFLINE (am I right that at this moment .fdb should NOT affect in any was to the SHADOW theat was created before but then DROPPED ?)
2.3) checks that .fdb is really in shutdown mode (again reading attributes from database header)
2.4) again obtains header attributes from .SHD-file; this is marked as "point 1"
2.5) returns .fdb to ONLINE
2.6) checks that .fdb was really returned to ONLINE mode
2.7) attempts to activate shadow (or maybe properly now to say: "EX-shadow" ?) file. It should remove 'active shadow' from attributes.
2.8) obtains header attributes from shadow file; this is marked as "point 2"

The first surprise is waiting on the "2.4", when we already moved .FDB to offline and now are verifying attributes of .SHD file: it appeared also in OFFLINE state. Why this ex-shadow "watches" for our handles with its ex-root ?

Second surprise is on "2.7": ex-shadow file will be remained in OFFLINE state and we'll get error

database C:\MIX\FIREBIRD\QA\FBT-REPO\TMP\C4955.SHD shutdown
-Some database(s) were shutdown when trying to read mapping data

To reproduce unpack attached .zip and:

1) open "c4955-1.sql" and change path and name of database and shadow files:

shell del C:\MIX\firebird\QA\fbt-repo\tmp\c4955.fdb 2>nul;
shell del C:\MIX\firebird\QA\fbt-repo\tmp\c4955.shd 2>nul;
create database '/3333:C:\MIX\firebird\QA\fbt-repo\tmp\c4955.fdb';

2) open "c4955-1.sql" and change path and file of .db file:

. . .
drop shadow 1 preserve file;
commit;
connect '/3333:C:\MIX\firebird\QA\fbt-repo\tmp\c4955.fdb' user 'SYSDBA' password 'masterke';
. . .

3) open "c4955-2.bat" and change these settings:

set usr=sysdba
set pwd=masterke
set host=localhost
set port=3333
set dbnm=c4955

4) launch in 1st winfow: isql -q -i c4955-1.sql
It will issue smth like this:

ISQL Version: WI-V3.0.0.32272 Firebird 3.0 Release Candidate 2
Server version:
Firebird/Windows/Intel/i386 (access method), version "WI-V3.0.0.32272 Fi
. . .
Database: /3333:C:\MIX\firebird\QA\fbt-repo\tmp\c4955.fdb

MSG Attach_1. Starting "heavy DML"...

5) After 4-5 seconds open one else window and run there c4955-2.bat

Check content of c4955-2.log, it should be like this:
#⁠#⁠#⁠#⁠#⁠

Starting ISQL with create and drop shadow...

MSG Attach_2. Check state of Attach_1: OK. It works, gen_id=20203

MSG Attach-2. RDB$FILES before dropping shadow
FILE_NAME <null>
FILE_SEQ <null>
. . .

drop shadow 1 preserve file;
commit;
connect '/3333:C:\MIX\firebird\QA\fbt-repo\tmp\c4955.fdb' user 'SYSDBA' password 'masterke';
set echo off;

MSG Attach_2. RDB$FILES after dropping shadow
FILE_NAME <null>
FILE_SEQ <null>
. . .

Obtain header from .SHD file, point 0.
Database "C:\MIX\FIREBIRD\QA\FBT-REPO\TMP\C4955.SHD"
Attributes force write, active shadow
Root file name: C:\MIX\FIREBIRD\QA\FBT-REPO\TMP\C4955.FDB

Move .FDB to SHUTDOWN...

Check header of.FDB file: whether it's in SHUTDOWN ?
Database "C:\MIX\FIREBIRD\QA\FBT-REPO\TMP\C4955.FDB"
Attributes force write, full shutdown

Obtain header from .SHD file, point 1.
Database "C:\MIX\FIREBIRD\QA\FBT-REPO\TMP\C4955.SHD"
Attributes force write, active shadow, full shutdown
Root file name: C:\MIX\FIREBIRD\QA\FBT-REPO\TMP\C4955.FDB

Return .FDB to ONLINE...

Check header of.FDB file: whether it's in ONLINE ?
Attributes force write

Activate SHADOW file. This should remove 'active shadow' from attributes.
STDERR: database C:\MIX\FIREBIRD\QA\FBT-REPO\TMP\C4955.SHD shutdown
STDERR: -Some database(s) were shutdown when trying to read mapping data

Obtain header from .SHD file, point 2.
Database "C:\MIX\FIREBIRD\QA\FBT-REPO\TMP\C4955.SHD"
Attributes force write, full shutdown
Root file name: C:\MIX\FIREBIRD\QA\FBT-REPO\TMP\C4955.FDB

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

Attachment: core4955-ex_shadow-file-remains-in-shutdown.zip [ 12870 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: Sean Leyne (seanleyne)

Pavel, Are you suggesting/saying that this case should be re-opened?

@firebird-automations
Copy link
Collaborator Author

Commented by: @pavel-zotov

No. Because command 'drop shadow preserve file' does work: it really allows to create shadow file and use it.

But this shadow is still 'watched' by its ex-root, I don't know why :-)
May be something like 'postfix' one need to be implemented for this ticket ?..

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants