gfix -sweep -clean

[FabianoKureck]

Hello!

I would suggest that gfix -sweep have a feature to clean a database page. Nowadays a backup folowed by a gfix sweep only marks the data page as FREE but the data itself remains in the database page. If I ZIP the database file it became bigger than necessary due to this. I would like to mark the data page as FREE and ZERO (0x00) the content.

Why:
We have a handfull of customers where the Firebird database File have more than 500GB of size (our biggest customer have 666GB today and grows 40GB/month).
We have some problems related to this size databases. For example a simple gbak backup runs for over 6 hours to complete (on a Database server with 128GB RAM, 50+ processors). A database restore thakes more than 24 hours to complete.

I would like make a FAST backup, where I do a "alter database begin backup", copy the database file itself, then "alter dabase end backup". With the database copy I remove all Indexes (PK and FK, etc) do a gfix -sweep zeroing the freed database pages. This way when I ZIP the database file I can schrink it a lot.

[mrotteveel]

Why have you created this as a discussion and not as an issue?

[dyemanov]

GitHub allows to create an issue based on discussion, so I don't see a big problem to have it discussed here.

[alexeykovyazin]

Hello Fabiano,

As you probably know, all requested features in 99% cases appear in the next major version of Firebird, in this case, I guess, it will be 6.
However, you already can start preparations to migrate to v5 (its release is scheduled for upcoming months), which has multithread backup, restore and sweep, and runs x4-10 times faster than single thread backup (on the powerful servers, of course).
In this way you will approach desired goal (faster backup restore, as I understood) approximately 2-3 years faster than in the case it will be included in v6 (and I guess, it will not).
Multithread features are from HQbird, where they were used in production with versions 2.5, 3 and 4, so there is no worries about stability and performance.
Regarding the compressing of the backup file - take a look on -ZIP switch in v4, also tested from v3 HQBird, which transparently compresses the large backups.

Regards,
Alexey Kovyazin

[FabianoKureck]

Vlad, what you said contradicts what Mark said. "Chapter 10. Monitoring & Command-line Utilities" under "gbak". The documentation is not clear, its confusing as Mark tough the backup/restore is parallel as "documentation" but you said otherwise. It is what I said first about "documentation it is clear that the sweep and parallel index creation is available BUT is not clear at all that gbak backup/restore operation is also parallel!"

[mrotteveel]

It doesn't contradict it. What is parallelized in gbak - as far as I'm aware - is sweep (on backup) and index creation (on restore).

[hvlad]

I'm sorry, but I see no contradiction. What and where I "said otherwise" ? Could you explain ?

Also, documentation is explicitly state about parallel backup and restore.

[hvlad]

What is parallelized in gbak - as far as I'm aware - is sweep (on backup) and index creation (on restore).

Oh, no. Please, re-read docs. In particular, doc\README.gbak, section "gbak enhancements in Firebird v5."

[mrotteveel]

OK, in that case I think the release notes need some revision in this area.

[aafemt]

Rephrase this suggestion as "security measure: zero freed database pages" and it will get higher chance for implementation.

[AlexPeshkoff]

Agree that as security measure this may be useful. But what about your backup process...

There is much faster way to perform regular backup using multilevel nbackup. Backups with level > 0 are executed much faster (nbackup saves only modified pages, moreover starting with FB3 it reads only them). I doubt your customers change all 500+ GB of data everyday.

[FabianoKureck]

Agree. The problem with nBackup for backup purpose is that I can't guarantee database integrity. The only way is by doent a gbak backup/restore. On Windows the Shadow Copy and Data Deduplication Service are Firebird database killers, you can lose your database on a sigle Shadow Copy backup - the corruption is beyong repair. Our system refuses to work when it detects this situation to prevent data loss but there are cases when It does not detects (as a thirt part software using Shadow Copy). So, this is why we use gbak.

[omachtandras]

If it's security... Wouldn't it be more effective and efficient if the garbage collector automatically (without gfix -sweep) set the deleted/unused parts to 0x00? (If it don't already does this?) It could also reset entire pages.
This would also reduce the possibility of data leakage when someone using nbackup to copy the database...

[FabianoKureck]

It is a concernem too. In Brazil there is LGPD law where the software developers can't share user data. To comply we "anonimize" data, changing people names, ID numbers, etc to null before sending this to us (software developers) for debugging purposes. The problem is that when I do a File copy of the database in use (alter database begin backup) then anonimize database the users data can be seen on database file because of old/released/no longer used transaction - before anonimization.

[dyemanov]

Basically, I agree it makes more sense to have this feature at the engine level -- e.g. every page is zero-zapped when it's marked as empty. And this should be controllable by configuration (disabled by default). But I doubt it could be counted as a proper security measure -- I'd rather expect a requirement that every record must be zero-zapped when being garbage-collected.

[hvlad]

I also not see much better security with zeroed empty pages.
For working database I see no value to clear empty pages.
What I really see is huge performance penalty when writting such pages.
Also, empty page could be reused in next ms after releasing.
It is a waste of resources for nothing, imho.

As for backup purposes - nbackup might be tuned to not copy content of empty pages into backup of level 0.
Such pages anyway will be zeroed by OS when backup file is growing.

As for LGPD and "anonimize users data" - AFAIK, database under such conditions should not store any sensitive data in open/complete form, so this is completely another task with very different solutions.

[FabianoKureck]

IMHO, this can be implemented on gfix. Why not? If there is a page marked as free then zero it. It will allow smaller nBackup copies, database copies after "alter database begin backup", when I need do ZIP a database file to send it to some place and so on. These small things are useful on day/day Firebird life.

[aafemt]

If all you need is a smaller backup - a little utility which zero free pages during feeding the file to ZIP is the simplest solution.

[mrotteveel]

Why would a user need to write custom tools, when this could be implemented in gfix?

[aafemt]

Because sweeper works at record level, not page level. It is not in position to clean them. To clean pages one would need to implement fully new functionality for gfix similar to validation. As a standalone utility it is much simpler.

[hvlad]

IMHO, this can be implemented on gfix.

Why would a user need to write custom tools, when this could be implemented in gfix?

First, gfix doesn't work with database file, engine does.
Next, please, don't touch a sweep, it does its job and there is no need to overcomplicate it with something else.
Next, could someone explain me how live and working database could benefit from zeroing empty pages ?
I speak about database, not about its backup here.
If one need to have zero's in backup (on purpose!) - this is not the same as have zero's in database (all the time!).