MaxDB 7.x and its reorganization + SAN cloning/snapshotting

[Former Member]

We are currently in the process of evaluating a new storage solution for all our SAP (= MaxDB) systems.

Given the case we'd produce a snapshot from our production system (3,1 TB) and present the results of that to another server to create a test system. Observations turned out that we move between 300 - 600 GB of data daily on the production - not only but also due to the internal MaxDB page reorganization. This would eventually result in a test system that will be as big as the source system in a few days.

We are currently discussing whether and if yes how it can be possible to reduce the amount of daily block changes, I'm of the opinion that this is not really possible.

Am I right?

Markus

[lbreddemann]

Hi Markus,

you cannot change the copy-on-write mechanism, so data changed by SQL will always lead to page "movements".

However, the internal data volume equalization can be configured or switched off completely (starting with 7.7).

Parameter

EnableDataVolumeBalancing        YES

switches on the active movement of pages by server tasks (VolBlc) to even out volume usage.

Another feature that might lead to a higher amount of 'moved' pages is maybe the table clustering. (I haven't checked this, but I assume this!).

With 7.6 (or below) there is no active movement of pages.

Hope this gives you a start.

regards,

Lars

[markus_doehr2]

Hi Lars,

> you cannot change the copy-on-write mechanism, so data changed by SQL will always lead to page "movements".

That's how I understood how the engine is balancing the B*trees, yes.

> EnableDataVolumeBalancing YES

> Another feature that might lead to a higher amount of 'moved' pages is maybe the table clustering. (I haven't checked this, but I assume this!).

Those two come together, no rebalancing on Volumes --> no more free clusters - fragementation - slower system. There are other parameters influencing the "search for free clusters", yes, but eventually this will lead to a slower system.

So this SAN feature is not really usable for MaxDB

Another discussion was/is the deduplication of backups, we tested this earlier and there is only one solution supporting the MaxDB backup deduplication (Exagrid), others storage vendors are not really aware of MaxDB backups being a "problem" as per todays knowledge. They all think in "Oracle files".

Markus

[lbreddemann]

> > you cannot change the copy-on-write mechanism, so data changed by SQL will always lead to page "movements".

>

> That's how I understood how the engine is balancing the B*trees, yes.

Hmm... close but not correct.

B*Tree rebalancing may require page splits etc. but it's not always required.

Plus, this is working on logical pages!

Copy-on-Write in turn is a feature of the physical page management (happening in the converter).

This does happen with every change of the page - even if it's an in-place operation seen from the logical-page-angle.

> > EnableDataVolumeBalancing YES

> > Another feature that might lead to a higher amount of 'moved' pages is maybe the table clustering. (I haven't checked this, but I assume this!).

>

> Those two come together, no rebalancing on Volumes --> no more free clusters - fragementation - slower system. There are other parameters influencing the "search for free clusters", yes, but eventually this will lead to a slower system.

Nope again.

Rebalancing surely helps to free up space for clusters, but you can use the clustering without it.

See - 7.6 databases do use clustering and don't even have that active rebalancing!

For the clustered pages a certain area of each data volume is kind of reserved and not considered for non-clustered pages.

> So this SAN feature is not really usable for MaxDB

Likely not - at least not with the same efficiency that can be reached when all data stays where it is...

> Another discussion was/is the deduplication of backups, we tested this earlier and there is only one solution supporting the MaxDB backup deduplication (Exagrid), others storage vendors are not really aware of MaxDB backups being a "problem" as per todays knowledge. They all think in "Oracle files".

I wouldn't call this 'thinking' really.

In fact this feature is not that clever and as you see it's also not very flexible.

As soon as the data 'moves' around it does not work properly anymore.

regards,

Lars

[markus_doehr2]

> Copy-on-Write in turn is a feature of the physical page management (happening in the converter).

> This does happen with every change of the page - even if it's an in-place operation seen from the logical-page-angle.

Ok - got it.

> > > EnableDataVolumeBalancing YES

> > > Another feature that might lead to a higher amount of 'moved' pages is maybe the table clustering. (I haven't checked this, but I assume this!).

> >

> > Those two come together, no rebalancing on Volumes --> no more free clusters - fragementation - slower system. There are other parameters influencing the "search for free clusters", yes, but eventually this will lead to a slower system.

>

> Nope again.

> Rebalancing surely helps to free up space for clusters, but you can use the clustering without it.

Sure - I did not exclude them

> For the clustered pages a certain area of each data volume is kind of reserved and not considered for non-clustered pages.

I thought this "static" mode is no more supported but now it's "mixed up" (with 7.7.x) with other non-clustered pages.

> > Another discussion was/is the deduplication of backups, we tested this earlier and there is only one solution supporting the MaxDB backup deduplication (Exagrid), others storage vendors are not really aware of MaxDB backups being a "problem" as per todays knowledge. They all think in "Oracle files".

>

> I wouldn't call this 'thinking' really.

> In fact this feature is not that clever and as you see it's also not very flexible.

Well, reducing a daily "full backup" of 3,1 TB to a daily backup of a few hundred GB is clever enough to save time and backup space, no matter how intelligent it is in fact. We are doing this with other (non-SAP) databases (SQL Server) and it works pretty well saving LOTS of storage and tapes.

> As soon as the data 'moves' around it does not work properly anymore.

That's again the reorganization thing, interestingly enough is Exagrid clever enough to detect the changes and stores only one block and not the two (as in COW) - which is a significant reduction.

Backing up a 3 TB database is a challenge if you have no real SAN features to assist you.

Markus

[lbreddemann]

>

> > For the clustered pages a certain area of each data volume is kind of reserved and not considered for non-clustered pages.

> I thought this "static" mode is no more supported but now it's "mixed up" (with 7.7.x) with other non-clustered pages.

Honestly: I don't know this right now - would have to check, but no time for that...

> > I wouldn't call this 'thinking' really.

> > In fact this feature is not that clever and as you see it's also not very flexible.

>

> Well, reducing a daily "full backup" of 3,1 TB to a daily backup of a few hundred GB is clever enough to save time and backup space, no matter how intelligent it is in fact. We are doing this with other (non-SAP) databases (SQL Server) and it works pretty well saving LOTS of storage and tapes.

Yes, the result is impressive and quite useful - no question about that!

> > As soon as the data 'moves' around it does not work properly anymore.

>

> That's again the reorganization thing, interestingly enough is Exagrid clever enough to detect the changes and stores only one block and not the two (as in COW) - which is a significant reduction.

>

> Backing up a 3 TB database is a challenge if you have no real SAN features to assist you.

Ok, we had been here before and you know my answer to this.

Use incremental backups - this will at least reduce the amount of data for most backups.

And since the size of an incremental backup is a function of the data change since the last full backup, you may get some quite good results with taking a full backup once a week and incremental backups in-between.

This could also reduce the recovery time, but is of course, one step more complex during the restore.

regards,

Lars

[markus_doehr2]

> > Backing up a 3 TB database is a challenge if you have no real SAN features to assist you.

>

> Ok, we had been here before and you know my answer to this.

>

> Use incremental backups - this will at least reduce the amount of data for most backups.

Well - I tried that before and the recover time of a roughly 200 GB incremental backup takes about 4 hours, even with an increased number of parallel threads on the target system.

I don't know why it takes so long, may be some long running transaction but this is not a good choice (for us), we will look more for mirroring on SAN level.

Markus

[Former Member]

Hi Markus,

Have you ever looked at DataDomain dedup for backing up MaxDB? I've heard good things from the vendor, but haven't seen any real world results.

Cheers

Shaun

[simon_matter]

> However, the internal data volume equalization can be configured or switched off completely (starting with 7.7).

> Parameter

>

> EnableDataVolumeBalancing        YES
> 

> switches on the active movement of pages by server tasks (VolBlc) to even out volume usage.

While we are at it, I've just checked our 7.7.06.09 production instance and it has EnableDataVolumeBalancing set to NO.

How safe is it to turn this on on our version?

And, does it really change anything if all DATA volumes are on the same physical disks (despite all vols being filled at the same percantage)?

Thanks,

Simon

[markus_doehr2]

> Have you ever looked at DataDomain dedup for backing up MaxDB? I've heard good things from the vendor, but haven't seen any real world results.

We check that, yes, but AFAIK you can either enable the algorithm for deduplication MaxDB or for other stuff (like files). We want to use one backup solution for the whole IT, not separate ones so this is out of scope for now...

Markus

[markus_doehr2]

> And, does it really change anything if all DATA volumes are on the same physical disks (despite all vols being filled at the same percantage)?

Yes, it moves pages around in the files.

Meanwhile we have a NetApp and we were doing some tests.

Our tests were showing a 40 - 100 GB data movments per day if we clone production so latest after 4 weeks all blocks from the database were touched at least once. This is with or witohut VolumeBalancing, the parameter is not that significant.

Conclusively: cloining a huge MaxDB on Netapp is not something very fancy if you don't have the space.

Basically all the nice features the SAN brings are not leveraged and even lead to counterproductive results:

- dedup doesn't do anything (there is nothing to dedup in a running MaxDB despite UCS2 making half of the database 'empty')

- compression doesn't do anything (same as above)

- the cache (we have 2 x 256 GB on the SAN) is not really used due to the nature of the random reads of the database. The chance that a block is no more in the database cache (CacheSize 110 GB on our main ERP system) but still in the SAN cache is < 1 % according to our tests

- the maximum read throughput is never reached/leveraged by the database due to the random (unparallel) reads

- snapshotting/cloning leads in our case to a system where size of source = destination in about 4 weeks. On a BW system that time was 11 days.

Going from the old system with 36 GB CacheSize of the database on an EVA6000 with 16 GB Cache on the SAN to the new system with 110 GB CacheSize of the database and 256 GB Cache on the SAN gave us "just" an increase of database speed (according to ST03N) of roughly 90 ms in avg.

Markus