Issue Details (XML | Word | Printable)

Key: CORE-4955
Type: Improvement Improvement
Status: Resolved Resolved
Resolution: Fixed
Priority: Major Major
Assignee: Alexander Peshkov
Reporter: Holger Klemt
Votes: 0
Watchers: 3
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Define a new option for DROP SHADOW command to leave the shadow file in the filesystem

Created: 08/Oct/15 09:53 PM   Updated: 14/Jan/16 03:42 PM
Component/s: None
Affects Version/s: 2.5.4, 3.0 Beta 2
Fix Version/s: 3.0 RC1

File Attachments: 1. Zip Archive core4955-ex_shadow-file-remains-in-shutdown.zip (2 kB)

Environment: all

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


 Description  « Hide
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




 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Alexander Peshkov added a comment - 09/Oct/15 01:34 PM
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.

Dmitry Yemanov added a comment - 09/Oct/15 01:40 PM
From the syntax POV, I'd rather suggest [PRESERVE FILE] or [{PRESERVE | DELETE} FILE].

Adriano dos Santos Fernandes added a comment - 09/Oct/15 01:47 PM
+1 for Dmitry syntax.

Alexander Peshkov added a comment - 09/Oct/15 01:47 PM
[{PRESERVE | DELETE} FILE] is great
will do

Pavel Zotov added a comment - 09/Oct/15 02:25 PM
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 ?

Karol Bieniaszewski added a comment - 14/Oct/15 12:50 PM
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

Alexander Peshkov added a comment - 14/Oct/15 02:01 PM
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.

Pavel Zotov added a comment - 14/Jan/16 02:04 PM
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

Sean Leyne added a comment - 14/Jan/16 03:29 PM
Pavel, Are you suggesting/saying that this case should be re-opened?

Pavel Zotov added a comment - 14/Jan/16 03:42 PM

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 ?..