firebird-devel

[Firebird-devel] non-transportable backup using without "-se service_mgr" results in an backup file ~50% larger than the source database

[Brenden W. <BKW...@dr...>]

I originally posted this to firebird-support and didn't get any feedback, figured I'd post here before entering an issue.

I performed most of these tests with 2.1.3.18185, however I just tested again with 2.5.0.26074 and the results were the same.

In short, source database is roughly 21gig, freshly restored from and .fbk

If I backup with the below command:
gbak.exe -y d:\temp\i21426\backup.log -se service_mgr -v -nt -user sysdba -password masterkey d:\temp\i21426\SW.fdb d:\temp\i21426\SW.fbk

Results in 13,257,345KB file, took approximately 31 minutes.  Likely what one would expect.

However dropping "-se service_mgr" from the above results in a huge FBK file 32,220,963KB file.

Backing up the same database in transportable format results in an even smaller file (11,205,556KB) with the file size being exactly the same whether I specify "-se service_mgr" or not, so this appears to be specific to the non transportable format.

I searched the tracker and wasn't able to find an existing issue for this.  I'll add one if that's the correct next step, I just wanted to run this by someone before doing so.

We could likely provide the source database on HD if needed, although I'd probably want to remove more sensitive customer information and retest prior to doing so (for obvious reasons).

Re: [Firebird-devel] non-transportable backup using without "-se service_mgr" results in an backup file ~50% larger than the source database

[Alex P. <pes...@ma...>]

 On 10/13/10 23:24, Brenden Walker wrote:
> I originally posted this to firebird-support and didn't get any feedback, figured I'd post here before entering an issue.
>
> I performed most of these tests with 2.1.3.18185, however I just tested again with 2.5.0.26074 and the results were the same.
>
> In short, source database is roughly 21gig, freshly restored from and .fbk
>
> If I backup with the below command:
> gbak.exe -y d:\temp\i21426\backup.log -se service_mgr -v -nt -user sysdba -password masterkey d:\temp\i21426\SW.fdb d:\temp\i21426\SW.fbk
>
> Results in 13,257,345KB file, took approximately 31 minutes.  Likely what one would expect.
>
> However dropping "-se service_mgr" from the above results in a huge FBK file 32,220,963KB file.
>
> Backing up the same database in transportable format results in an even smaller file (11,205,556KB) with the file size being exactly the same whether I specify "-se service_mgr" or not, so this appears to be specific to the non transportable format.
>
> I searched the tracker and wasn't able to find an existing issue for this.  I'll add one if that's the correct next step, I just wanted to run this by someone before doing so.
>
> We could likely provide the source database on HD if needed, although I'd probably want to remove more sensitive customer information and retest prior to doing so (for obvious reasons).
Looks like a bug. 2 questions:
Can you reproduce an issue with database of smaller size?
Are both non-transportable backups (big and small) restorable?

Re: [Firebird-devel] non-transportable backup using without "-seservice_mgr" results in an backup file ~50% larger than the source database

[Brenden W. <BKW...@dr...>]

> -----Original Message-----
> From: Alex Peshkoff [mailto:pes...@ma...]
> Sent: Thursday, October 14, 2010 2:35 AM
> To: fir...@li...
> Subject: Re: [Firebird-devel] non-transportable backup using without "-
> seservice_mgr" results in an backup file ~50% larger than the source
> database
> 
>  On 10/13/10 23:24, Brenden Walker wrote:
> > I originally posted this to firebird-support and didn't get any
> feedback, figured I'd post here before entering an issue.
> >
> > I performed most of these tests with 2.1.3.18185, however I just
> tested again with 2.5.0.26074 and the results were the same.
> >
> > In short, source database is roughly 21gig, freshly restored from and
> .fbk
> >
> > If I backup with the below command:
> > gbak.exe -y d:\temp\i21426\backup.log -se service_mgr -v -nt -user
> sysdba -password masterkey d:\temp\i21426\SW.fdb d:\temp\i21426\SW.fbk
> >
> > Results in 13,257,345KB file, took approximately 31 minutes.  Likely
> what one would expect.
> >
> > However dropping "-se service_mgr" from the above results in a huge
> FBK file 32,220,963KB file.
> >
> > Backing up the same database in transportable format results in an
> even smaller file (11,205,556KB) with the file size being exactly the
> same whether I specify "-se service_mgr" or not, so this appears to be
> specific to the non transportable format.
> >
> > I searched the tracker and wasn't able to find an existing issue for
> this.  I'll add one if that's the correct next step, I just wanted to
> run this by someone before doing so.
> >
> > We could likely provide the source database on HD if needed, although
> I'd probably want to remove more sensitive customer information and
> retest prior to doing so (for obvious reasons).
> Looks like a bug. 2 questions:
> Can you reproduce an issue with database of smaller size?
> Are both non-transportable backups (big and small) restorable?

I tried reproducing with smaller DB's (6gig as well as 12gig) with no luck.  All -nt backups where the same size, and transportables where the same size (although not the same size as the non-trans backups, expected I believe).

I was able to restore all of the backups without any problem, resulting database was roughly the same size in all cases.  I even did a database table data compare using IbExpert and the DB's were exactly the same in structure and content.

I also tried restoring the transportable format and then backing that up into non-transportable with and without -se service_mgr.  That exhibited the same behaviour so whatever it is 'survives' a backup/restore.

The above tests were only done on 2.1.3 though, as I'm sure you can imagine these tests take a long time.

I'll take a shot today paring this database down to a smaller size (drop a few of our large tables) and see if the problem persists. Perhaps I can get a database that's small enough to download.  I'll post a follow up as soon as that's complete.

Re: [Firebird-devel] non-transportable backup using without "-seservice_mgr" results in an backup file ~50% larger than the source database

[Dimitry S. <sd...@ib...>]

14.10.2010 14:21, Brenden Walker wrote:
> I was able to restore all of the backups without any problem, resulting database was roughly the same size in all cases.  I even did a database table data compare using IbExpert and the DB's were exactly the same in structure and content.

   Take IBBackupSurgeon and look what is so big in the backup.

-- 
   SY, SD.

Re: [Firebird-devel] non-transportable backup using without "-se service_mgr" results in an backup file ~50% larger than the source database

[Brenden W. <BKW...@dr...>]

> -----Original Message-----
> From: Alex Peshkoff [mailto:pes...@ma...]
> Sent: Thursday, October 14, 2010 2:35 AM
> To: fir...@li...
> Subject: Re: [Firebird-devel] non-transportable backup using without "-
> seservice_mgr" results in an backup file ~50% larger than the source
> database
> 
>  On 10/13/10 23:24, Brenden Walker wrote:
> > I originally posted this to firebird-support and didn't get any
> feedback, figured I'd post here before entering an issue.
> >
<..snip>
> same whether I specify "-se service_mgr" or not, so this appears to be
> specific to the non transportable format.
> >
> > I searched the tracker and wasn't able to find an existing issue for
> this.  I'll add one if that's the correct next step, I just wanted to
> run this by someone before doing so.
> >
> > We could likely provide the source database on HD if needed, although
> I'd probably want to remove more sensitive customer information and
> retest prior to doing so (for obvious reasons).
> Looks like a bug. 2 questions:
> Can you reproduce an issue with database of smaller size?
> Are both non-transportable backups (big and small) restorable?

I was able to shrink the database by dropping tables.  The non-service manager backup is no longer larger than the FDB, *however* the .fbk file size is larger than when using the service manager.  I think this is still exhibiting the problem, just not as pronounced.

The database is now 359,280K.

Here are the two test backup command lines I used.

c:gbak.exe -se service_mgr -v -nt -user sysdba -password masterkey d:\temp\i21426\2.5\smalltest\sw_even_smaller.fdb d:\temp\i21426\2.5\smalltest\service_mgr.fbk

c:gbak.exe -v -nt -user sysdba -password masterkey d:\temp\i21426\2.5\smalltest\sw_even_smaller.fdb d:\temp\i21426\2.5\smalltest\no_service_mgr.fbk


Here's the directory contents:

Directory of D:\temp\i21426\2.5\smalltest

10/14/2010  11:06 AM    <DIR>          .
10/14/2010  11:06 AM    <DIR>          ..
10/14/2010  11:04 AM       175,974,400 no_service_mgr.fbk
10/14/2010  11:07 AM       167,070,720 service_mgr.fbk
10/14/2010  11:07 AM       367,902,720 SW_EVEN_SMALLER.FDB


The difference in file size isn't as large, however I would think that whether using -se service_mgr or not the resulting FBK should be the same size.

Re: [Firebird-devel] non-transportable backup using without "-se service_mgr" results in an backup file ~50% larger than the source database

[Alex P. <pes...@ma...>]

 On 10/14/10 19:13, Brenden Walker wrote:
>> -----Original Message-----
>> From: Alex Peshkoff [mailto:pes...@ma...]
>> Sent: Thursday, October 14, 2010 2:35 AM
>> To: fir...@li...
>> Subject: Re: [Firebird-devel] non-transportable backup using without "-
>> seservice_mgr" results in an backup file ~50% larger than the source
>> database
>>
>>  On 10/13/10 23:24, Brenden Walker wrote:
>>> I originally posted this to firebird-support and didn't get any
>> feedback, figured I'd post here before entering an issue.
> <..snip>
>> same whether I specify "-se service_mgr" or not, so this appears to be
>> specific to the non transportable format.
>>> I searched the tracker and wasn't able to find an existing issue for
>> this.  I'll add one if that's the correct next step, I just wanted to
>> run this by someone before doing so.
>>> We could likely provide the source database on HD if needed, although
>> I'd probably want to remove more sensitive customer information and
>> retest prior to doing so (for obvious reasons).
>> Looks like a bug. 2 questions:
>> Can you reproduce an issue with database of smaller size?
>> Are both non-transportable backups (big and small) restorable?
> I was able to shrink the database by dropping tables.  The non-service manager backup is no longer larger than the FDB, *however* the .fbk file size is larger than when using the service manager.  I think this is still exhibiting the problem, just not as pronounced.
>
> The database is now 359,280K.
>
> Here are the two test backup command lines I used.
>
> c:gbak.exe -se service_mgr -v -nt -user sysdba -password masterkey d:\temp\i21426\2.5\smalltest\sw_even_smaller.fdb d:\temp\i21426\2.5\smalltest\service_mgr.fbk
>
> c:gbak.exe -v -nt -user sysdba -password masterkey d:\temp\i21426\2.5\smalltest\sw_even_smaller.fdb d:\temp\i21426\2.5\smalltest\no_service_mgr.fbk
>
>
> Here's the directory contents:
>
> Directory of D:\temp\i21426\2.5\smalltest
>
> 10/14/2010  11:06 AM    <DIR>          .
> 10/14/2010  11:06 AM    <DIR>          ..
> 10/14/2010  11:04 AM       175,974,400 no_service_mgr.fbk
> 10/14/2010  11:07 AM       167,070,720 service_mgr.fbk
> 10/14/2010  11:07 AM       367,902,720 SW_EVEN_SMALLER.FDB
>
>
> The difference in file size isn't as large, however I would think that whether using -se service_mgr or not the resulting FBK should be the same size.
Yes, certainly.
Can you compress that fdb and make it available for download?

Re: [Firebird-devel] non-transportable backup using without "-seservice_mgr" results in an backup file ~50% larger than the source database

[Brenden W. <BKW...@dr...>]

> -----Original Message-----
> From: Alex Peshkoff [mailto:pes...@ma...]
> Sent: Friday, October 15, 2010 2:44 AM
> To: fir...@li...
> Subject: Re: [Firebird-devel] non-transportable backup using without "-
> seservice_mgr" results in an backup file ~50% larger than the source
> database
> 
>  On 10/14/10 19:13, Brenden Walker wrote:
> >> -----Original Message-----
> >> From: Alex Peshkoff [mailto:pes...@ma...]
> >> Sent: Thursday, October 14, 2010 2:35 AM
<snip>
> > Here are the two test backup command lines I used.
> >
> > c:gbak.exe -se service_mgr -v -nt -user sysdba -password masterkey
> d:\temp\i21426\2.5\smalltest\sw_even_smaller.fdb
> d:\temp\i21426\2.5\smalltest\service_mgr.fbk
> >
> > c:gbak.exe -v -nt -user sysdba -password masterkey
> d:\temp\i21426\2.5\smalltest\sw_even_smaller.fdb
> d:\temp\i21426\2.5\smalltest\no_service_mgr.fbk
> >
> >
> > Here's the directory contents:
> >
> > Directory of D:\temp\i21426\2.5\smalltest
> >
> > 10/14/2010  11:06 AM    <DIR>          .
> > 10/14/2010  11:06 AM    <DIR>          ..
> > 10/14/2010  11:04 AM       175,974,400 no_service_mgr.fbk
> > 10/14/2010  11:07 AM       167,070,720 service_mgr.fbk
> > 10/14/2010  11:07 AM       367,902,720 SW_EVEN_SMALLER.FDB
> >
> >
> > The difference in file size isn't as large, however I would think
> that whether using -se service_mgr or not the resulting FBK should be
> the same size.
> Yes, certainly.
> Can you compress that fdb and make it available for download?

The backups will be a tad smaller than noted above (dropped a table with employee information), however the disparity of backup file size is still present.


http://www.diablops.com/dlfiles/BackupTestDB.zip


Thanks for looking into this.

Re: [Firebird-devel] non-transportable backup using without "-seservice_mgr" results in an backup file ~50% larger than the source database

[Alex P. <pes...@ma...>]

 On 10/15/10 16:52, Brenden Walker wrote:
> http://www.diablops.com/dlfiles/BackupTestDB.zip
Downloaded and unzipped OK.

Re: [Firebird-devel] non-transportable backup using without "-seservice_mgr" results in an backup file ~50% larger than the source database

[Alex P. <pes...@ma...>]

 On 10/15/10 16:52, Brenden Walker wrote:
>>> The difference in file size isn't as large, however I would think
>> that whether using -se service_mgr or not the resulting FBK should be
>> the same size.

Confirm an issue. Files are exactly same up to the end of one created
using service_mgr, later something like data from tables (starting at
something looking like random place) follows. I.e. the issue is not to
dangerous, but certainly worth fixing. Ticket
(http://tracker.firebirdsql.org/browse/CORE-3232) added.

Re: [Firebird-devel] non-transportable backup using without "-seservice_mgr" results in an backup file ~50% larger than the source database

[Alex P. <pes...@ma...>]

 After more careful analysis I've found the following.

My first impression that files differ only in the end was wrong.
Actually, I've overwritten file with backup done over network by backup,
done using embedded access. Size did not change - but this is due ti the
fact that gbak does not trancate backup files. And if you backup
something small overwriting old big  file, it will anyway remain big.
This is a bug itself and should be fixed.

Embedded access produces file with exactly same size as service_mgr
does. This is as expected. But I was working with overwritten by
embedded access and not truncated file, therefore it seemed to me that
files differ in the end.

Actually the reason is as follows. gbak when used in none-transportable
mode compresses message as it was received by isc_receive(). And
sometimes in varchar fields with zero length there arrives some garbage
- values of fields transfered some times before. Length is OK - it's
zero - but some non-null data is present after it. In transportable mode
length of varchar is taken into an account and therefore this data does
not affect something. In none-transportable - it makes RLE compression
work worse, causing therefore bigger backup file.

The question is - are there any requirements in our API to what is
placed in the message buffer as a data for varchar field with zero
length? Should it be NULLs or may it be garbage?

Re: [Firebird-devel] non-transportable backup using without "-seservice_mgr" results in an backup file ~50% larger than the source database

[Dmitry Y. <fir...@ya...>]

Alex,

> The question is - are there any requirements in our API to what is
> placed in the message buffer as a data for varchar field with zero
> length? Should it be NULLs or may it be garbage?

I believe garbage is acceptable, not only for zero-length strings but 
also for NULLs (as we have an indicator stored separately). Although I 
remember a hack we have regarding blobs: NULL blob must have zero blob 
id, otherwise some applications started to work unpredictably, so we may 
be sure that some applications ignore our API rules :-)

But nothing prevents us from choosing the way which is better in 
practice. You know that any record buffer is initially zapped with 
zeroes to allow better compression later. So we may do the same for the 
message buffers, unless it's proven to be too much expensive (as the 
message processing is mostly CPU bound).


Dmitry

Re: [Firebird-devel] non-transportable backup using without "-seservice_mgr" results in an backup file ~50% larger than the source database

[Alex P. <pes...@ma...>]

 On 11/19/10 12:02, Dmitry Yemanov wrote:
> Alex,
>
>> The question is - are there any requirements in our API to what is
>> placed in the message buffer as a data for varchar field with zero
>> length? Should it be NULLs or may it be garbage?
> I believe garbage is acceptable, not only for zero-length strings but 
> also for NULLs (as we have an indicator stored separately). Although I 
> remember a hack we have regarding blobs: NULL blob must have zero blob 
> id, otherwise some applications started to work unpredictably, so we may 
> be sure that some applications ignore our API rules :-)
>
> But nothing prevents us from choosing the way which is better in 
> practice. You know that any record buffer is initially zapped with 
> zeroes to allow better compression later. So we may do the same for the 
> message buffers, unless it's proven to be too much expensive (as the 
> message processing is mostly CPU bound).
>
Taking into an account that some other application may also ignore
length, I will first try with zeroes initializing message buffers in
remote and see how does it affect performance. gbak's backup appears to
be very good test for it.

Re: [Firebird-devel] non-transportable backup using without "-seservice_mgr" results in an backup file ~50% larger than the source database

[Alex P. <pes...@ma...>]

 On 11/19/10 12:31, Alex Peshkoff wrote:
>  On 11/19/10 12:02, Dmitry Yemanov wrote:
>> Alex,
>>
>>> The question is - are there any requirements in our API to what is
>>> placed in the message buffer as a data for varchar field with zero
>>> length? Should it be NULLs or may it be garbage?
>> I believe garbage is acceptable, not only for zero-length strings but 
>> also for NULLs (as we have an indicator stored separately). Although I 
>> remember a hack we have regarding blobs: NULL blob must have zero blob 
>> id, otherwise some applications started to work unpredictably, so we may 
>> be sure that some applications ignore our API rules :-)
>>
>> But nothing prevents us from choosing the way which is better in 
>> practice. You know that any record buffer is initially zapped with 
>> zeroes to allow better compression later. So we may do the same for the 
>> message buffers, unless it's proven to be too much expensive (as the 
>> message processing is mostly CPU bound).
>>
> Taking into an account that some other application may also ignore
> length, I will first try with zeroes initializing message buffers in
> remote and see how does it affect performance. gbak's backup appears to
> be very good test for it.

No performance problems detected after zeroing varchars in messages.
Will commit in 2.5 and trunk.