Registration

Dear SAP Community Member,
In order to fully benefit from what the SAP Community has to offer, please register at:
http://scn.sap.com
Thank you,
The SAP Community team.
Skip to end of metadata
Go to start of metadata

Attention

The usage of Multiple Log Partitions is not recommended!

This section provides information about concept and configuration of multiple log partitions.


 


Preface

Former concepts of writing log entries into the log area were:

As a first approach database log entries were written into one log queue and were processed by one log writer task. Using buffers for each transaction should reduce region collisions when writing concurrently into the log queue.

A second approach was to use different log queues but still one log writer task. The log entries are now allocated to the different queues but a potential bottleneck remains - only one log writer task.

The newest approach as of MaxDB version 7.8 provides a separate log writer task for every log queue. Every log writer task writes its log entries to a defined log partition. A log partition consists of one or more log volumes. The log volumes get an additional parameter which indicates the partition ID. This ID is used to group the log volumes by the partition they belong to.

The main advantage is that writing to these log partitions can be done in parallel. In addition the region synchronization effort is reduced. Every UserKernelThread (UKT) can contain a log writer task which writes its log entries to a specific log partition. This means as many log writer tasks can be used as CPUs are on hand to utilize by the database (parameter MaxCPUs).

back to top

Configuration of multiple log partitions

The activation of using multiple log partitions is controlled by the setting of parameter MaxLogWriterTasks. The default value of this parameter is 0. This means that log partitioning is not active. Real partitioning starts with the setting of 2 and above. A parameter change will be active after next restart of the database only. There are as much logwriter tasks available afterwards as set via the parameter. Each log writer task is located in a separate UserKernelThread (UKT). It makes no sense to change this parameter to a value which exceeds the number of CPUs used by the database (parameter MaxCPUs). It would create UKTs which cannot be used.

Please note: If parameter MaxLogWriterTasks is set to 2 or above both parameters MaxLogQueues and LogQueues are locked and set unchangeably to 1.

Once parameter MaxLogWriterTasks has been changed to a value greater than 1 and the database has been restarted log partitioning is active. But still only one log partition exists and the existing log volumes still belong to this partition.

New log volumes can be added now (according to parameter MaxLogVolumes) and they can be assigned to different log partitions. So the existing log volumes belong to log partition 1 and newly added log volumes can be assigned to a partition with the maximum number according to parameter MaxLogWriterTasks.

Whenever a new log volume is added and assigned to a new log partition simultaneously the database state has to be switched to ADMIN before. If the log partition already exists (containing at least one log volume) new log volumes can be added and assigned to this partition in state ONLINE, too.

Configuration using DBMCLI

Preconditions for the following example are:

A: Database version is at least 7.8.01.09
B: Full data backup is available and was created within current backup history
C: Two log volumes are configured
D: Parameter MaxCPUs is set to >= 3

At first a DBMCLI session has to be opened to the corresponding database instance. In this example database name is DB1.

dbmcli on DB1>

First step is to switch to state ADMIN and to create a log backup (log backup template is called LOGSAVE here):

dbmcli on DB1> db_admin
dbmcli on DB1> backup_start LOGSAVE

Now the value of parameter MaxLogWriterTasks is checked:

dbmcli on DB1> param_getvalue MaxLogWriterTasks
OK
0

The intention is to configure 3 log partitions at a max. Therefore parameter MaxLogWriterTasks is set to this value:

dbmcli on DB1> param_put MaxLogWriterTasks 3

As this parameter change gets active only after switching to state OFFLINE, this is done now. Afterwards state ADMIN is required again:

dbmcli on DB1> db_offline
dbmcli on DB1> db_admin

Again the value of parameter MaxLogWriterTasks is checked:

dbmcli on DB1> param_getvalue MaxLogWriterTasks
OK
3

Now three log writer tasks can be found within task overview:

dbmcli on DB1> db_cons show tasks

ID   UKT  Win   TASK    APPL Current        Timeout/ Region     Wait
T9     8 0x16D0 Logwr        No-Work  (255)  2       0           313(s)
T10    9  0x804 Logwr        No-Work  (255)  2       0            15(s)
T11   10  0xE4C Logwr        No-Work  (255)          0             1(s)

Now the log volume configuration is checked:

dbmcli on DB1> param_getvolsall log
OK
UseMirroredLog         NO
MaxLogVolumes          5
LogVolumeName001       1280       F  C:\DB1\log\DISKL001  1

The last figure in lines starting with "LogVolumeName..." represents the log partition ID (in this case 1).

Adding a new log partition is a log configuration change which is possible only when a log backup was created before. It is not allowed at this point to have log entries which are not saved yet. Creating a log backup had been done already at the beginning of the example here (see above). Now a second log volume is added and assigned to new log partition 2 :

dbmcli on DB1> db_addvolume log "C:\DB1\log\DISKL002" F 1280 partition 2
OK

And a third one:

dbmcli on DB1> db_addvolume log "C:\DB1\log\DISKL003" F 1280 partition 3
OK

Again a check of the log volume configuration:

dbmcli on DB1> param_getvolsall log
OK
UseMirroredLog                   NO
MaxLogVolumes                    5
LogVolumeName001                 1280       F  C:\DB1\log\DISKL001  1
LogVolumeName002                 1280       F  C:\DB1\log\DISKL002  2
LogVolumeName003                 1280       F  C:\DB1\log\DISKL003  3

Log volume 2 is assigned to log partition 2 and log volume 3 to log partition 3 as displayed above. Database can be restarted now to state ONLINE. Further volumes can be added to log partition 1, 2 or 3 in database state ONLINE.

back to top

Configuration using Database Studio

Currently there is no guided tool support (wizard) for activating multiple log partitions within the released versions of Database Studio. This means the steps described above have to be done in Database Studio manually as well (backup, parameter changes, adding log volumes).

back to top

Mirrored log and multiple log partitions

Mirrored log together with multiple log partitions can be used as of MaxDB version 7.8.02.02. For the example below the following initial situation is present:

dbmcli on DB1> param_getvolsall log
OK
UseMirroredLog                   YES
MaxLogVolumes                    5
LogVolumeName001                 1280       F  C:\DB1\log\DISKL001  1
MirroredLogVolumeName001                    F  C:\DB1\log\DISKL_M_001

dbmcli on DB1> param_getvalue MaxLogWriterTasks
OK
0

So one log volume is configured, mirrored log is active and log partitioning is deactivated. At first two log partitions are configured via parameter MaxLogWriterTasks:

dbmcli on DB1> db_admin
OK

dbmcli on DB1> param_put MaxLogWriterTasks 2
OK

Now database state is switched to state OFFLINE and ADMIN afterwards to get the parameter changes active.

dbmcli on DB1> db_offline
OK

dbmcli on DB1> db_admin
OK

Afterwards a second log volume is added and directed to log partition 2. As mirrored log is active it is necessary to include name and type of the mirrored log volume into the command:

dbmcli on DB1> db_addvolume log "C:\DB1\log\DISKL002" F 1280 "C:\DB1\log\DISKL_M_002" F partition 2
OK

If this command fails most probably log entries exist which were not saved yet (no log backup has been done at the beginning of the procedure). Database is ready to be restarted now:

dbmcli on DB1>db_online
OK

When checking the log volume configuration both log volumes are visible, each in one of the two log partitions. In addition the mirrored volumes are shown. The mirrored log volumes are assigned to the same log partition as the corresponding original log volume:

dbmcli on DB1>param_getvolsall log
OK
UseMirroredLog                   YES
MaxLogVolumes                    5
LogVolumeName001                 1280       F  C:\DB1\log\DISKL001  1
MirroredLogVolumeName001                    F  C:\DB1\log\DISKL_M_001
LogVolumeName002                 1280       F  C:\DB1\log\DISKL002  2
MirroredLogVolumeName002                    F  C:\DB1\log\DISKL_M_002

back to top

Reconfiguration of multiple log partitions

As described above adding log volumes to existing log partitions is quite simple. To add an additional log partition (within the limits of parameter MaxLogWriterTasks) it has to be considered that this is only possible in database state ADMIN. But how to proceed when log partitioning should be switched off? Or if existing log volumes should be assigned to a different existing log partition or even the number of log volumes should be reduced?

Before starting with the approaches described below please perform a complete data and log backup.

Reverting to one single log partition

Referring to the previous example without mirrored log there is the following initial situation:

dbmcli on DB1> param_getvolsall log
OK
UseMirroredLog                   NO
MaxLogVolumes                    5
LogVolumeName001                 1280       F  C:\DB1\log\DISKL001  1
LogVolumeName002                 1280       F  C:\DB1\log\DISKL002  2
LogVolumeName003                 1280       F  C:\DB1\log\DISKL003  3

dbmcli on DB1> param_getvalue MaxLogWriterTasks
OK
3

At first the database has to be switched to state ADMIN:

dbmcli on DB1> db_admin

Now the required parameter changes are performed, starting with parameter MaxLogWriterTasks. Value 0 is the default value when creating a new database. It means that log partitioning is not active, i.e. one single log partition for all log volumes is used. This setting is equal to value 1.

dbmcli on DB1> param_put MaxLogWriterTasks 0
OK

For each log volume a parameter exists which indicates the assignment to the corresponding log partition. The parameter name starts with "LogVolumePartition" followed by the number of the log volume. In our example we had three log volumes. So there are three of those parameters. Each parameter points to the log partition ID the log volume belongs to:

dbmcli on DB1> param_getvalue LogVolumePartition001
OK
1

dbmcli on DB1> param_getvalue LogVolumePartition002
OK
2

dbmcli on DB1> param_getvalue LogVolumePartition003
OK
3

As every log volume is assigned to a log partition via these parameters they have to be changed so that only one partition is used by all log volumes:

dbmcli on DB1> param_put LogVolumePartition002 1
OK

dbmcli on DB1> param_put LogVolumePartition003 1
OK

Parameter LogVolumePartition001 remains unchanged as it is set to value 1 already. Now the database is switched to status OFFLINE and again to ADMIN to get the parameter changes active:

dbmcli on DB1> db_offline
OK

dbmcli on DB1> db_admin
OK

The parameter values can be verified now:

dbmcli on DB1> param_getvalue MaxLogWriterTasks
OK
0

dbmcli on DB1> param_getvalue LogVolumePartition001
OK
1

dbmcli on DB1> param_getvalue LogVolumePartition002
OK
1

dbmcli on DB1> param_getvalue LogVolumePartition003
OK
1

Now it is required to reset the log volume concatenation. The parameter changes don't do this. Currently the only option to achieve this is to reformat the log area. All entries on the log area get lost. But as we created a full data backup and a log backup at the beginning of the process there is no danger of data loss.

If the reset of the log volume concatenation is not done switching to database state ONLINE will fail because the log area can not be opened with the new configuration.

dbmcli on DB1> db_execute clear log
OK

Please be aware that executing this command starts a new backup history. This means a full data backup has to be done as precondition for subsequent log backups.

Now the database is ready to be switched to state ONLINE:

dbmcli on DB1> db_online
OK

Now all log volumes belong to a single log partition:

dbmcli on DB1> param_getvolsall log
OK
UseMirroredLog                   NO
MaxLogVolumes                    5
LogVolumeName001                 1280       F  C:\DB1\log\DISKL001  1
LogVolumeName002                 1280       F  C:\DB1\log\DISKL002  1
LogVolumeName003                 1280       F  C:\DB1\log\DISKL003  1

Assigning log volumes to different log partitions

The approach is similar to the previous one. Again the initial situation is the following:

dbmcli on DB1> param_getvolsall log
OK
UseMirroredLog                   NO
MaxLogVolumes                    5
LogVolumeName001                 1280       F  C:\DB1\log\DISKL001  1
LogVolumeName002                 1280       F  C:\DB1\log\DISKL002  2
LogVolumeName003                 1280       F  C:\DB1\log\DISKL003  3

dbmcli on DB1> param_getvalue MaxLogWriterTasks
OK
3

The intention is to reduce the number of log partitions to 2 and to assign log volume 2 to log partition 1 and log volume 3 to log partition 2. At first the database has to be switched to state ADMIN:

dbmcli on DB1> db_admin
OK

Now the parameter changes are done. Firstly the number of log partitions is changed to 2:

dbmcli on DB1> param_put MaxLogWriterTasks 2
OK

Afterwards the assigment of the log volumes to the log partitions is adjusted as requested:

dbmcli on DB1> param_put LogVolumePartition002 1
OK

dbmcli on DB1> param_put LogVolumePartition003 2
OK

Once this has been done the database is switched to state OFFLINE and ADMIN afterwards to get the parameter changes active.

dbmcli on DB1> db_offline
OK

dbmcli on DB1> db_admin
OK

Now the log area has to be reformatted. The reason is the same as described above regarding the log volume concatenation:

dbmcli on DB1> db_execute clear log
OK

Please be aware that executing this command starts a new backup history. This means a full data backup has to be done as precondition for subsequent log backups.

Finally the database is restarted to state ONLINE and afterwards the log configuration is checked:

dbmcli on DB1> db_online
OK

dbmcli on DB1> param_getvolsall log
OK
UseMirroredLog                   NO
MaxLogVolumes                    5
LogVolumeName001                 1280       F  C:\DB1\log\DISKL001  1
LogVolumeName002                 1280       F  C:\DB1\log\DISKL002  1
LogVolumeName003                 1280       F  C:\DB1\log\DISKL003  2

dbmcli on DB1> param_getvalue MaxLogWriterTasks
OK
2

Reducing the number of log volumes

As an example to show this the last final configuration is used:

dbmcli on DB1> param_getvolsall log
OK
UseMirroredLog                   NO
MaxLogVolumes                    5
LogVolumeName001                 1280       F  C:\DB1\log\DISKL001  1
LogVolumeName002                 1280       F  C:\DB1\log\DISKL002  1
LogVolumeName003                 1280       F  C:\DB1\log\DISKL003  2

dbmcli on DB1> param_getvalue MaxLogWriterTasks
OK
2

The current intention is to reduce the number of log volumes to 2 and to assign log volume 2 to log partition 2. At first the database has to be switched to state ADMIN:

dbmcli on DB1> db_admin
OK

Now the third log volume is deleted:

dbmcli on DB1> param_delvolume 3 log
OK

This command removes the log volume from the parameter configuration only. Currently there is no command to delete the volume physically. So this has to be done manually. Command db_deletevolume can only be used for data volumes. If the log volume won't be deleted physically it doesn't matter. But it is not used anymore and consumes file system space. It could be added later again to the parameter configuration for reuse. But the contained log entries are never usable again. To reintegrate it the entire log area has to be reformatted.

As a next step log volume 2 is assigned to log partition 2:

dbmcli on DB1> param_put LogVolumePartition002 2
OK

Once this has been done the database is switched to state OFFLINE and ADMIN afterwards to get the parameter changes active.

dbmcli on DB1> db_offline
OK

dbmcli on DB1> db_admin
OK

Now the log area has to be reformatted. The reason is the same as described above regarding the log volume concatenation:

dbmcli on DB1> db_execute clear log
OK

Please be aware that executing this command starts a new backup history. This means a full data backup has to be done as precondition for subsequent log backups.

Finally the database is restarted to state ONLINE and afterwards the log configuration is checked:.

dbmcli on DB1> db_online
OK

dbmcli on DB1> param_getvolsall log
OK
UseMirroredLog                   NO
MaxLogVolumes                    5
LogVolumeName001                 1280       F  C:\DB1\log\DISKL001  1
LogVolumeName002                 1280       F  C:\DB1\log\DISKL002  2

dbmcli on DB1> param_getvalue MaxLogWritertasks
OK
2

Closing remarks

Changing parameter MaxLogWriterTasks takes affect only after a shutdown of the database. Reason is the change of the number of logwriter tasks. This change can be done only in OFFLINE state. This means that it is not possible to have the database in state ADMIN, increase parameter MaxLogWriterTasks and add a log volume to a new log partition at one go. This will cause an error saying that not enough logwriter tasks are available.

The log volumes have not to be evenly distributed to log partitions. But if automatic log backup is activated the number of log pages specified via parameter AutoLogBackupSize will be totalized regarding all log partitions. That means it is possible that one log partition gets full whereas the other partitions still have free space. But no log backup will start as the required number of log pages (according to parameter AutoLogBackupSize) has not been reached yet. So it is recommended to keep the divergence from uniform distribution low.

There is a system table called LOGPARTITIONSTATISTICS which allows for each log partition to review values for the usable size, the used size as well as the not saved size.

The order of the log partition IDs currently doesn't have to be gapless. So it is possible to have log partition ID 1 followed by log partition ID 3:

dbmcli on DB1> param_getvolsall log
OK
UseMirroredLog                   NO
MaxLogVolumes                    5
LogVolumeName001                 1280       F  C:\DB1\log\DISKL001  1
LogVolumeName002                 1280       F  C:\DB1\log\DISKL002  3

The recommendation is to avoid this as it could cause confusion. Technically it is no issue but when having a lot of log volumes and a mess of log partition IDs it might be difficult to keep track of the log configuration.

back to top

Relevant SAP Notes

869267 "FAQ: SAP MaxDB LOG area" (SMP login required)

  • No labels