What is the maximum possible value of -io for a probkup incr

mb_ibseq (incremental backup sequence number since last full backup) in master block and the field that stores the overlap factor (-io) in backup header are both 4 byte integers.

While what George wrote is true, the value of the -io parameter is stored in a 16-bit signed integer in the code which parses the parameters so its maximum value is 32,767.

Many thanks to both of you, George and Matt

intersting idea, sebastian. but i imagine the implementation can be quite complex given the fact that failures can occur now and then (e.g. disk space exhausted on backup volume, etc).

keeping track of which incremental should be restored might be hard.

the incrementals will become larger and larger. i would expect that eventually they would stabilize at some size and not grow larger until some event like year-end closing when lots of stuff is changed.

What is the hoped for advantage to this scheme over an alternative like after-imaging?

Also, as a Techsupport Engineer for a long time, Would make me nervous replying on one Full backup instead of a weekly one or something like that. Also, well cleaver schemas are great, remember that others after you leave might be incharge of restoring from your clever system and they would need to be as clever and knowledgeable as you would be to restore from a disaster. People are often panicked in a disaster situation.

Side thought I never had to look at: Wouldn't the time to restore a full backup and an ever increasing incremental backup grow and grow over say, weekly backups and incremental backup/s?

In case you are planning to take those backups online, keep in mind there is a max counter as per knowledgebase.progress.com/.../How-many-online-probkups-000068748... Once you hit it "A dump and load of the database is needed to re-initialise the counters on all database blocks."

There is also a performance hit to the db, in our case any other process that runs at that time slows down significantly, the database record pool goes out a wack, etc...

> the database record pool goes out a wack

With the recent Progress versions?

Many thanks you for your point Slegian,

The KBase link you shared seems to be broken.  Here is an attempt to share it again (based on the KBase ID):

knowledgebase.progress.com/.../How-many-online-probkups-000068748

So the limit is 65535 and not 32767, and we should definitely avoid overflowing that limit.  Fine.  Actually, the original point of my question was to avoid this kind of issue.  Yes I intent to use probkup with the online option.  Now I want to perform a full backup from time to time (every year or so), so this should reset the block counter.

My main idea is to perform an incremental online backups every hour, as a start.

Then roughly...   every day at some point (say around midnight) do another one with -io 25 so it includes a full day,

I will keep track of the daily files until I reach 10 days

Then every 10 days, do another one with 260 (I have to check the number, but including a few extra hours is not a big problem)

I will keep track of the 10 days files until I reach 100

Every 100 days I do another with -io around 2610

Every 1000 days a big one with around 26100

The idea is that 243 days after a full backup, I would be able to restore a database somewhere with a reasonable set of files like :

full + inc200 + inc100 + inc40 + inc30 + inc20 + inc10 + inc4 + inc3 + inc2 + inc1

Yes we should issue a few warnings before reaching 65000, so it should roughly be able to go up to about 2500 days, so about 7 years, but as said above; I'd like to retrigger a full backup every year or so.

The peculiar issue to find cleaver naming for the incremental backup files so it shows when each file is produced and the period of data that is included with the the -io overlap factor.

[quote user="slacroixak"]

The idea is that 243 days after a full backup, I would be able to restore a database somewhere with a reasonable set of files like :

full + inc200 + inc100 + inc40 + inc30 + inc20 + inc10 + inc4 + inc3 + inc2 + inc1

[/quote]

But is that really going to be a reasonable set of files compared to, let's say, a weekly full backup and daily incrementals ?

It's not just the number of files involved. I would not be surprised if the total filesize of incremental backups will start exceeding the full backups as well.There's probably a tipping point depending on exactly what activity takes place in the database, but because of that you may want to do full backups more frequently anyway, and with fewer incrementals between fulls, those become easier to manage too. 

I'm also kind of curious what the exact use case behind this scenario is. Meaning, how important is the time to restore from the backups, and how accurately do you want to specify the point in time to restore to ?

I still do not see what the advantage of this scheme is over after-image files.

This really seems like an awful lot of complexity with no benefits.

@Tom: Some people believe that they cannot afford the alleged degree in performance when turning AI on.

(don't kill the messenger).

Yes, "some people" occasionally have that misconception.  But is that the case here?   And if it is my follow-up question is obviously going to be: "what evidence is there to support that proposition?"

I'm also inclined to wonder, if performance is such a concern, why introduce all the additional read and write I/O of incremental backups?  

Is AI not in use?  What are the RPO and RTO for such a system?

Until Sebastian clarifies what the hoped for benefit of the incremental backup scheme is we are just speculating that there is a perception of an after-imaging performance problem.  He could be wanting to do this for some other reason.

I second Tom's comment: you are trying to replace a very simple, tested and trusted solution (after-imaging) with an artisanal, unproven one. The effect on performance of your solution will undoubtedly be much higher than AI, since every hour you will read the entire database, looking for the relatively few blocks that have been modified. How long does it take today to do a full or incremental backup? If it isn't one 2 or 3 minutes, then you will be spending most of the day sucking up a CPU and fairly high disk IO bandwidth running the incrementals, and in the end you will have a less trustworthy and less maintainable solution.

Unless there is pertinent information that you are not sharing with us, I don't see how your solution serves your customer better than AI.

Regards,

[mention:ae2ea2f6412743fc8be36c522f414ef0:e9ed411860ed4f2ba0265705b8793d05] we're using OE 11.5. I'll need to check the more recent versions.

[mention:5e61cead20354196ab65fe67462c6163:e9ed411860ed4f2ba0265705b8793d05] The way I read that KP the counter is not reset by a full backup... 65535 is relatively high, depending on how many backups you take. About 7 years worth of hourly backups.

Regarding all the other questions I see here on incremental vs AI, one of our apps has this "scratch & reload" feature, meaning the clients are loading say a month's worth of data, process and spit some results. They can do this over and over again, and each time they expect the old data to be removed and replaced with the new one. We started with AI and found the log files to grow very fast as their data was not as clean as expected so there was a lot of scratching and reloading going on, which the AI duly recorded. An incremental backup on the other hand, would only record the last state of the data, at the time is invoked, skipping their failed load attempts in between.

Having said that, it all depends on the frequency at which you take the incremental backups. As we are now asked to increase that frequency we are currently exploring AI all over again :)

> we're using OE 11.5. I'll need to check the more recent versions.

The issue # 20041203-030 is fixed in 9.1E01/10.0B02:
"Online backup performance problem has been fixed. The fix requires that the -Bp parameteris used. The minimal of -Bp 10 will suffice."

The same fix also eliminated the eviction of the buffers when probkup is not use -Bp.

Tests with 11.7:
"Used buffers" when probkup is waiting on proquiet'ed sports database is 33.
"Used buffers" when backup complete is changed to:
45 -Bp 0
43 -Bp 10
107 -Bp 100

After dbanalys:
Used buffers: 1057

Danalys: 1073 total blocks found in the database.

"scratch and reload" makes sense as one of those very unusual reasons why after imaging might not be the best answer.  thank-you.

I'd be tempted to do full backups.  But I suppose that there might be large relatively static portions of the db as well as the "scratch and reload" bits?

Would it make sense to move the "Scratch and reload" part of the system to another db and run AI on the one that is not "scratch and reload"ed?

This really seems like a fun solution, but not a pragmatic one.  AI overhead shouldn't be a real issue anymore on modern hardware.  That said if you have a DB with volatile data that modifies 30% to 40% of your DB daily, I'm curious how something like that is practical in the real world even with incrementals.  IMO Probkup is great for small DBs, once you move into 200GB+ you want to be using PROQUIET + SAN level snap backups ("mirror split") and AI for point in time recovery.  Also a replicated DB is by far your best bet for RTO (assuming no one FOR EACH ... DELETED your DB).

Prorest is really only worth the time if you need a RPO in the past. Probkup/Prorest requires hours of downtime assuming a sizable db.

This thread has lead to many questions.  I am going to try to explain in one single post why I am interested in this pattern

First, please note I know AI.  I've used it in a previous life, when I was at a direct end user.  It is great for what it was designed : DB recovery until the very last committed transaction in case of a crash, or synchronize a cold database on another machine over a LAN.  I know AIW and so forth

My needs are different now : I work for an editor with many different customers, most of them with a small or medium database

1)  We do NOT wish to configure and administrate AI on all our customer sites.  Guys in our IT teams know how to backup and restore backups also with incremental backups. They will be happy with the ability to restore a DB with an easily identified backup point.

2) On our development side, from time to time, we want to reproduce an issue of a given customer with fresh data.  If we already have a full backup of theirs, then getting a fair set of incremental files (as described earlier) with be great for us.

Next, in our App** the life cycle of our data is that people focus on a rather small amount of data every day, or every month.  Older data remain unchanged most of the time.  But one important aspect is that the same records are updated many times during the same day.  Regarding this aspect, with AI you would end up with a much higher IO overhead for every single transaction.  On the other hand, a daily incremental backup file (with -io 25 as I will do one per hour) will only hold the DB blocks that have changed for one day, and these blocks will appear only once in the backup file even if they have been updated multiple times during that day. Our test have shown that "probkup online incremental" is very fast and very smart, and it does not slow down operations in the DB while it is running***

  ** like in many apps I suppose.

 ***but the resulting backup is a snapshot of the DB as it was when the probkup was launched, because the DB engine is smart enough to make probkup jump at a block to be updated so it can back it up before letting the update happen.

3) Hence this performance point : for our needs, I bet we will will leverage IO and cpu with this pattern compared to the constant overhead of AI

4)  One dilemma I want to avoid about hardware : in order to do good AI, I would really prefer to do deal with an additional independent storage, but with same performance as the main one, for the sake of IO.  But nowadays, our customers deal with too many different configs, some with SAN, some with SSD, many with VM in blades that are also used by other Apps..  If I do not use AI, then I just do not have to bother with the performance aspect.  The only requirement is to hold the backup on a separate drive with little performance requirements.  Costly, this is just much better

At last, as some may have guessed...  

5) this pattern allows synchronization of a DB over the internet. Indeed, the incremental file can be zipped then transferred daily, with an optimized amount of data (see point #3), so we might be able to offer some BI**** reporting on a hosted site somewhere.

**** Business Inteligence, not Before Imaging ;)

Thanks Sebastian, that explains where you are coming from nicely.

FWIW -- unlike your "**" note, I doubt that there are many apps that behave like this ;)