Maxdb new features - Page Clustering and Prefetch

[Former Member]

Hi to all,

2 new features of maxdb are available with new patches: Page Clustering and Prefetch in OLTP-Systems.

Are there any experiences to this features?

READAHEAD_TABLE_THRESHOLD

"This parameter is used to specify, if read operations of tables

should be optimized, i.e. if during a table access more than the

specified value in pages is affected, servertasks will be used to

read ahead pages of this table."

I activated this parameter in November. The system now starts more quickly and some expensive sql-statements may be faster. But the average db-time didn't change.

Page Clustering can be activated with parameter: DATA_IO_BLOCK_COUNT

Then tables can be migrated with SQL-Statement: ALTER TABLE <table name> CLUSTER

I activated this only for one table. This table now is read much faster.

My questions.

Is there a documentation available for this features?

How can I "uncluster" clustered tables?

When this features are activated, expensive sql-statements are processed much faster. Because of the high workload on storagesystem, all other transaction may get slower?

regards

Franz

[lbreddemann]

> 2 new features of maxdb are available with new patches: Page Clustering and Prefetch in OLTP-Systems.

First of all: both features, although technically available, are not yet generally released for OLTP usage.

The current implementation of page prefetching is explicitely a proof-of-concept implementation which should be used by recommendation from MaxDB support only.

There are several limitations to the functionality at the moment, e.g. it does only work for table scans.

It does not work for joins, for index builds neither does it work on clustered tables.

Prefetching is available to 7.6 release only at the moment, so after an upgrade to 7.7 this feature would be gone...

> I activated this parameter in November. The system now starts more quickly and some expensive sql-statements may be faster. But the average db-time didn't change.

Well, nothing surprising here.

On SAP start there happen a lot of tablescans, mostly attributable to the fully buffered tables for the ABAP internals (security, ddic, etc.)

These statements will of course benefit if the pages are now read into the buffer in parallel.

For the avg. db-time: be happy!

This basically means, that most of the statements running in your system aren't processed by table scans, which is for MaxDB nearly always a very good thing.

> Page Clustering can be activated with parameter: DATA_IO_BLOCK_COUNT

> Then tables can be migrated with SQL-Statement: ALTER TABLE <table name> CLUSTER

Ok, page clustering is not documented yet (just as prefetching is not officially documented) and not released for OLTP.

> I activated this only for one table. This table now is read much faster.

>

> My questions.

> Is there a documentation available for this features?

Hmm.. you're using undocumented features - what do you expect?`

No, currently there is no official documentation.

There is work going on to provide official documentation but none of it is released yet (and it will take some more time!)

> How can I "uncluster" clustered tables?

ALTER TABLE <tablename> NOT CLUSTER

> When this features are activated, expensive sql-statements are processed much faster. Because of the high workload on storagesystem, all other transaction may get slower?

It's not that simple.

Both features address specific issues and are used only in very specific circumstances.

The clustering feature will not improve performance if the table is not accessed via the cluster key.

In fact, it may even decrease performance if this is done.

Also, at the moment clustered tables don't stay clustered on updates/inserts/deletes.

Therefore it is not necessarily a good idea to cluster tables like REPOLOAD pr REPOSRC on a development system.

regards,

Lars

[markus_doehr2]

I activated this parameter in November. The system now starts more quickly and some expensive sql-statements may be faster. But the average db-time didn't change.

The significant number for MaxDB is time of a single I/O. Ýou can check those times by doing

dbmcli -U c db_cons time enable

or by clicking on the "clock" in the task manager in DB50. The times will be written in the dbanalyzer logfile.

Page Clustering can be activated with parameter: DATA_IO_BLOCK_COUNT

Then tables can be migrated with SQL-Statement: ALTER TABLE <table name> CLUSTER

I activated this only for one table. This table now is read much faster.

Yes - because the MaxDB kernel will make sure, the pages are aligned at the "end" of the volumes so with one I/O there can be read more than one block.

Is there a documentation available for this features?

Clustering is part of the BW feature pack documented here.

https://wiki.sdn.sap.com/wiki/display/MaxDB/KernelPerformance-AcceleratingIO

When this features are activated, expensive sql-statements are processed much faster. Because of the high workload on storagesystem, all other transaction may get slower?

Let´s say "it depends". Prefetching uses one server task per volume to get the data faster. If you have a high number of volumes the avg. time for a single I/O can go down (see above) if the storage system is too heavily loaded. The best balance can be achieved by having as many volumes as you have physical disks.

Markus