cancel
Showing results for 
Search instead for 
Did you mean: 

Howto increase log recovery speed?

Former Member
0 Kudos

We created a copy of our production MaxDB (2,8 TB) to have a shadow database.

When I apply the logs (via script) I find out, that the shadow database is not able to "keep up" with the number of logs produced in the production. I figured out so far this is caused by the fact that the initial recover_start with the last but one log needs between 9 and 11 minutes until the first log recover is actually started; only 64 pager are used during this process despite having 100 datafiles (with 30 GB each) connected to a SAN with 248 disks.

I first recovered only one log (256 MB/32000 pages) by using a script like

db_admin
db_connect
recover_start logsich log 1000
recover_replace logsich /archivelog/log 1001
recover_cancel

This took roughly 10 - 12 minutes for each of them in average which is too long.

I switched then the script to recover more than one log at a time like

db_admin
db_connect
recover_start logsich log 1000
recover_replace logsich /archivelog/log 1001
recover_replace logsich /archivelog/log 1002
recover_replace logsich /archivelog/log 1003
recover_replace logsich /archivelog/log 1004
recover_cancel

using 10 logs. This process takes in avg. about 90 minutes which is too long.

The following parameters were changed already:

MaxServerTasks 1500

MaxUserTasks 300

MaxDataVolumes 130

However, I'm not able to keep up with the number of logs produced in the production, the backlog is always 10 - 15 logs. If we now wanted to stop the log recover to do a full database backup of the shadow database instead of the production we will never be able to make that backup current.

It seems to me that two things are mainly responsible for this:

- the long startup between "recover_start" and "recover_replace" (from db_offline to db_online it takes even without crashrecovery 12 - 15 minutes)

- the fact that there's not really a full parallelism used to apply the log (whatever that means)

Any hints on how to speed up that process are appreciated.

Markus

Accepted Solutions (0)

Answers (1)

Answers (1)

Former Member
0 Kudos

Hello Mr. Markus Doehr,

we ar also actually working on this topic. Our Database slightly smaller than yours. On the 11. of April I asked a similar question, because we also want to maintain a shadow database, and we saw the similar performance problems.

After first experience with a smaller databases we also started with a script, that began with recover_start, continued with recvover_replace and ended then, later on it started again with recover_start...

On our bigger DB we also saw, that the recover_start takes some minutes we wanted to save. So our next script worked in another way. It starts a dbmcli session, then a recover_start, after that it goes into an "endless recover_replace" loop, that means that the script never stops this dbmcli session, it checks, wether a new Log-Files exists, then it uses sendkey methods to tell the running dbmcli session to make the next_recover replace, so if everything is ok, we don´t waste time on the repeated recover_start.

We are still experimenting on that. In my thread of 11.04. Lars Bredemann proposed to increase the CacheMemorySize. It really increased the speed, and I marked the thread as solved. But after having it run for more than a week, we still often see, that our shadow system is running behind especially through the night hours (we have a lot of Batch-traffic), but it keeps up during day time (Perhaps we simply have to put even more memory into the shadow system, we will try that). So we are still interested in a discussion on how we can increase the speed an we like to share our experiences here in this forum.

Fank Sievering

markus_doehr2
Active Contributor
0 Kudos

I don't believe the memory thing (CacheMemorySize) is really an issue at this point.

If I check the memory usage vs. CacheSize I see that even after the application of 8 logs not all memory is used:

PID USERNAME LWP PRI NICE  SIZE   RES STATE    TIME    CPU COMMAND
 23963 maxdb    143  53    2   11G 7320M sleep   21:36  7.38% /sapdb/SHA/db/pgm/kernel SHA -diagpipe 18

I also notice, that despite having 1500 potential threads to read and write the data the I/O subsystem is not really leveraged to its fullest:

capacity     operations    bandwidth
pool        alloc   free   read  write   read  write
----------  -----  -----  -----  -----  -----  -----
dbdataSHA   2.71T   507G    374      0  46.5M      0
dbdataSHA   2.71T   507G     75      0  9.41M      0
dbdataSHA   2.71T   507G    135      0  16.6M      0
dbdataSHA   2.71T   507G    154      0  18.8M      0
dbdataSHA   2.71T   507G    139      0  16.9M      0
dbdataSHA   2.71T   507G    141      0  17.5M      0
dbdataSHA   2.71T   507G    164      0  19.8M      0
dbdataSHA   2.71T   507G    122      0  14.9M      0
dbdataSHA   2.71T   507G     74      0  9.35M      0

So we read roughly 15 - 20 MB/sec though the I/O subsystem can deliver and write up to 180 - 200 MB/sec (e. g. during a normal savepoint operation).

I guess this is yet-another-MaxDB-random-8kb-I/O-thing that can't be speed up

sigh

Markus

Former Member
0 Kudos

Hello Markus,

here is the top result of our machine during recover_replace:

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND

15632 sdb 16 0 7375m 6.2g 13m S 7 80.1 15:39.49 kernel

We use a virtual machine with 8GB memory, the cache is set to about 6.4GB, so my interpretation is that maxdb (the kernel process) is using 80.1% of the machines memory.

We also observe that our disk system and our cpu is not stressed at all, so we also think, it could be faster...

Sorry, I didn´t understand your last sentence with the 8kb-I/O thing, what do you mean by that?

double sigh

Frank

markus_doehr2
Active Contributor
0 Kudos

We also observe that our disk system and our cpu is not stressed at all, so we also think, it could be faster...

True because...

Sorry, I didn´t understand your last sentence with the 8kb-I/O thing, what do you mean by that?

the only significant key number for MaxDB is "how fast is a single random 8 kb I/O?". That number - and solely that one - defines the actual "speed"/performance of the database.

There are things like Prefetching/Readahead and Clusters but using those is just feasible for specific application scenarios and not generally. So if you have a single I/O time of let's say 6 ms at the moment and at night this increases to 9 or 10 ms because other systems are stressing the SAN, then the same process that took 20 minutes with 6 ms will take 30 minutes with 9 ms, no matter whether your SAN is on maximum load or not.

So even if you have a system with a CacheSize of 128 GB (which our production has) and a SAN system that can deliver up to 300 MB/sec random I/O read and save roughly 250 MB/sec random I/O the database kernel is still not leveraging that speed, not with configured 64 pagers and > 1000 ServerTasks and 1024 DataStripes.

I believe that this approach will just not work as expected and we have to find a differen solution (if there is any).

Marus