cancel
Showing results for 
Search instead for 
Did you mean: 

Create more than one "Garbage Collector" Task

Former Member
0 Kudos

I'm a "cleaning up" a rather big system (> 1.5 GB) by deleting no more used clients. The deletion itself is pretty fast, however, I see since a few days the following:

T70    5     -1 GarbCol      -1 IO Wait (R)           0 0      4        63521510(s)
T243   7     -1 User       8148 IO Wait (R)           0 0     21        145391381(s)
T270   9     -1 User       8057 Running               0 722             128781825(r)
T298  10     -1 User       8128 Running               0 1751 137        179800729(r)
T300  10     -1 User       8237 IO Wait (R)           0 0     10        179800729(r)

Is there a possibility to enable more that one GC task to make deletion quicker?

Regards,

Markus

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

No, you shouldn't need to change anything else. The number of servertasks is set by the kernel, based on the number of datavolumes and parallel media.

markus_doehr2
Active Contributor
0 Kudos

Ok.

I set MAXGARBAGECOLL to 32 (since I have > 60 volumes) but the system uses only a few of them in parallel:

T70    5     -1 GarbCol      -1 IO Wait (R)           0 0     28        1861530(s)
T75    5     -1 GarbCol      -1 IO Wait (R)           0 0      8        1861530(s)
T79    5     -1 GarbCol      -1 IO Wait (R)           0 0     13        1861530(s)
T80   15     -1 GarbCol      -1 IO Wait (R)           0 0      9        3197191(s)
T81   15     -1 GarbCol      -1 IO Wait (R)           0 0      4        3197191(s)
T84   15     -1 GarbCol      -1 IO Wait (R)           0 0      3        3197191(s)
T86   15     -1 GarbCol      -1 IO Wait (R)           0 0     31        3197191(s)
T88   15     -1 GarbCol      -1 IO Wait (R)           0 0      2        3197191(s)
T90   16     -1 GarbCol      -1 IO Wait (R)           0 0     28        1223326(s)
T91   16     -1 GarbCol      -1 IO Wait (R)           0 0      9        1223326(s)

Is that "one Garbcol per delete table"?

--

Markus

former_member229109
Active Contributor
0 Kudos

Hello Marcus,

-> What are the values of the database parameters: MAXCPU < 2 ? >, MAXSERVERTASKS, MAXUSERTASKS, USE_OPEN_DIRECT < if you run on Linux, see SAP note 993848 >

How many CPU do you have on the database server?

Do you have the user activities at that time in the Database?

Do you have the estimations, like number of the history

pages pro sec deleted by Garbage collectors?

-> "param_getexplain MAXGARBAGECOLL"

OK

The lower and upper limits are:

1 <= MAXGARBAGECOLL <= 64

-> This could take time, because the garbage collector task has to read the history pages to remove from the data volumes (physical disk I/O).

Did you do Measurement of I/O times < SAP Note No. 748225 >?

Please update with output of the param_getvolsall && dbm_version

-> Please review SAP Note No. 804657.

-> Please update with output of

x_cons <SID> show io

x_cons <SID> show active

x_cons <SID> show rte

< run them twice with 30sec period >

Thank you & best regards, Natalia Khlopina

markus_doehr2
Active Contributor
0 Kudos

Hi Natalia,

-> What are the values of the database parameters:

MAXCPU < 2 ? >, MAXSERVERTASKS, MAXUSERTASKS,

USE_OPEN_DIRECT < if you run on Linux, see SAP note

993848 >

MAXCPU is set to 8 (this is an 8-way Itanium-2 box)

MAXSERVERTASK is set to 159

MAXUSERTASK is set to 79 (23 % sessions free)

USE_OPEN_DIRECT doesn´t matter in our case since we use RAW devices

How many CPU do you have on the database server?

8 x Itanium-2

Do you have the user activities at that time in the

Database?

I am logged in alone.

The database analyzer doesn´s start (for whatever reason). I started it using DB50 several times but it doesn´t stay running.

I can see, that the system is deleting ~ 4 - 5 GB in 10 minutes (which is pretty fast I think). I monitored that using ST04 - Detail - Database filling statistics.

OK
LOG_MIRRORED                     NO
MAXLOGVOLUMES                    2
MAXDATAVOLUMES                   75
LOG_VOLUME_NAME_001              1048320    L  /sapdb/KFV/saplog/DISKL001
DATA_VOLUME_NAME_0001            3051136    L  /sapdb/KFV/sapdata/DISKD0001
DATA_VOLUME_NAME_0002            3051136    L  /sapdb/KFV/sapdata/DISKD0002
DATA_VOLUME_NAME_0003            3051136    L  /sapdb/KFV/sapdata/DISKD0003
DATA_VOLUME_NAME_0004            3051136    L  /sapdb/KFV/sapdata/DISKD0004
DATA_VOLUME_NAME_0005            3051136    L  /sapdb/KFV/sapdata/DISKD0005
DATA_VOLUME_NAME_0006            3051136    L  /sapdb/KFV/sapdata/DISKD0006
DATA_VOLUME_NAME_0007            3051136    L  /sapdb/KFV/sapdata/DISKD0007
DATA_VOLUME_NAME_0008            3051136    L  /sapdb/KFV/sapdata/DISKD0008
DATA_VOLUME_NAME_0009            3051136    L  /sapdb/KFV/sapdata/DISKD0009
DATA_VOLUME_NAME_0010            3051136    L  /sapdb/KFV/sapdata/DISKD0010
DATA_VOLUME_NAME_0011            3051136    L  /sapdb/KFV/sapdata/DISKD0011
DATA_VOLUME_NAME_0012            3051136    L  /sapdb/KFV/sapdata/DISKD0012
DATA_VOLUME_NAME_0013            2704640    L  /sapdb/KFV/sapdata/DISKD0013
DATA_VOLUME_NAME_0014            3051136    L  /sapdb/KFV/sapdata/DISKD0014
DATA_VOLUME_NAME_0015            3051136    L  /sapdb/KFV/sapdata/DISKD0015
DATA_VOLUME_NAME_0016            3051136    L  /sapdb/KFV/sapdata/DISKD0016
DATA_VOLUME_NAME_0017            3051136    L  /sapdb/KFV/sapdata/DISKD0017
DATA_VOLUME_NAME_0018            3051136    L  /sapdb/KFV/sapdata/DISKD0018
DATA_VOLUME_NAME_0019            3051136    L  /sapdb/KFV/sapdata/DISKD0019
DATA_VOLUME_NAME_0020            3051136    L  /sapdb/KFV/sapdata/DISKD0020
DATA_VOLUME_NAME_0021            3051136    L  /sapdb/KFV/sapdata/DISKD0021
DATA_VOLUME_NAME_0022            3051136    L  /sapdb/KFV/sapdata/DISKD0022
DATA_VOLUME_NAME_0023            3051136    L  /sapdb/KFV/sapdata/DISKD0023
DATA_VOLUME_NAME_0024            3051136    L  /sapdb/KFV/sapdata/DISKD0024
DATA_VOLUME_NAME_0025            3051136    L  /sapdb/KFV/sapdata/DISKD0025
DATA_VOLUME_NAME_0026            2704640    L  /sapdb/KFV/sapdata/DISKD0026
DATA_VOLUME_NAME_0027            3051136    L  /sapdb/KFV/sapdata/DISKD0027
DATA_VOLUME_NAME_0028            3051136    L  /sapdb/KFV/sapdata/DISKD0028
DATA_VOLUME_NAME_0029            3051136    L  /sapdb/KFV/sapdata/DISKD0029
DATA_VOLUME_NAME_0030            3051136    L  /sapdb/KFV/sapdata/DISKD0030
DATA_VOLUME_NAME_0031            3051136    L  /sapdb/KFV/sapdata/DISKD0031
DATA_VOLUME_NAME_0032            3051136    L  /sapdb/KFV/sapdata/DISKD0032
DATA_VOLUME_NAME_0033            3051136    L  /sapdb/KFV/sapdata/DISKD0033
DATA_VOLUME_NAME_0034            3051136    L  /sapdb/KFV/sapdata/DISKD0034
DATA_VOLUME_NAME_0035            3051136    L  /sapdb/KFV/sapdata/DISKD0035
DATA_VOLUME_NAME_0036            3051136    L  /sapdb/KFV/sapdata/DISKD0036
DATA_VOLUME_NAME_0037            3051136    L  /sapdb/KFV/sapdata/DISKD0037
DATA_VOLUME_NAME_0038            3051136    L  /sapdb/KFV/sapdata/DISKD0038
DATA_VOLUME_NAME_0039            2704640    L  /sapdb/KFV/sapdata/DISKD0039
DATA_VOLUME_NAME_0040            3051136    L  /sapdb/KFV/sapdata/DISKD0040
DATA_VOLUME_NAME_0041            3051136    L  /sapdb/KFV/sapdata/DISKD0041
DATA_VOLUME_NAME_0042            3051136    L  /sapdb/KFV/sapdata/DISKD0042
DATA_VOLUME_NAME_0043            3051136    L  /sapdb/KFV/sapdata/DISKD0043
DATA_VOLUME_NAME_0044            3051136    L  /sapdb/KFV/sapdata/DISKD0044
DATA_VOLUME_NAME_0045            3051136    L  /sapdb/KFV/sapdata/DISKD0045
DATA_VOLUME_NAME_0046            3051136    L  /sapdb/KFV/sapdata/DISKD0046
DATA_VOLUME_NAME_0047            3051136    L  /sapdb/KFV/sapdata/DISKD0047
DATA_VOLUME_NAME_0048            3051136    L  /sapdb/KFV/sapdata/DISKD0048
DATA_VOLUME_NAME_0049            3051136    L  /sapdb/KFV/sapdata/DISKD0049
DATA_VOLUME_NAME_0050            3051136    L  /sapdb/KFV/sapdata/DISKD0050
DATA_VOLUME_NAME_0051            3051136    L  /sapdb/KFV/sapdata/DISKD0051
DATA_VOLUME_NAME_0052            2704640    L  /sapdb/KFV/sapdata/DISKD0052
DATA_VOLUME_NAME_0053            3051136    L  /sapdb/KFV/sapdata/DISKD0053
DATA_VOLUME_NAME_0054            3051136    L  /sapdb/KFV/sapdata/DISKD0054
DATA_VOLUME_NAME_0055            3051136    L  /sapdb/KFV/sapdata/DISKD0055
DATA_VOLUME_NAME_0056            3051136    L  /sapdb/KFV/sapdata/DISKD0056
DATA_VOLUME_NAME_0057            3051136    L  /sapdb/KFV/sapdata/DISKD0057
DATA_VOLUME_NAME_0058            3051136    L  /sapdb/KFV/sapdata/DISKD0058
DATA_VOLUME_NAME_0059            3051136    L  /sapdb/KFV/sapdata/DISKD0059
DATA_VOLUME_NAME_0060            3051136    L  /sapdb/KFV/sapdata/DISKD0060
DATA_VOLUME_NAME_0061            3051136    L  /sapdb/KFV/sapdata/DISKD0061
DATA_VOLUME_NAME_0062            3051136    L  /sapdb/KFV/sapdata/DISKD0062
DATA_VOLUME_NAME_0063            3051136    L  /sapdb/KFV/sapdata/DISKD0063
DATA_VOLUME_NAME_0064            3051136    L  /sapdb/KFV/sapdata/DISKD0064
DATA_VOLUME_NAME_0065            2704640    L  /sapdb/KFV/sapdata/DISKD0065

siau-kfv:kfvadm 111> dbmcli -U c dbm_version
OK
VERSION    = 7.6.02
BUILD      = DBMServer 7.6.02   Build 015-123-153-230
OS         = UNIX
INSTROOT   = /sapdb/KFV/db
LOGON      = True
CODE       = UTF8
SWAP       = full
UNICODE    = YES
INSTANCE   = OLTP
SYSNAME    = Linux

The database is now up for > 4 hours (and still deleting)

Regards,

Markus

former_member229109
Active Contributor
0 Kudos

Hello Marcus,

1)"The database analyzer doesn't start (for whatever reason)."

You are SAP customer, please create the ticket to 'BC-DB-SDB'

Therefore we could logon & review the problem on your system.

You could check the errors in DBAN.err < located in

<Rundirectory>/analyzer/DBAN.err > & dbm.prt files.

What errors did you get when you run: dbmcli -U c dban_state ?

2)As I understood you have 15624797 history pages => 119.2GB

You wrote, that "I can see, that the system is deleting ~ 4 - 5 GB in 10

minutes (which is pretty fast I think)." If I'm correct,

You will wait ~300min or less < ~ 5h > for the history pages to be deleted.

Please run the SQL statement 'select sum(pagecount) from historyinfo' in interval 10min to review how many history pages left & estimate the number of history pages deleted in 10 min.

What are the values of the database parameters:

TASKCLUSTER01,_TASKCLUSTER_02, and TASKCLUSTER03

<The TASKCLUSTER is defined by the parameters TASKCLUSTER01,

TASKCLUSTER02, and TASKCLUSTER03>

IOPROCSPER_DEV

< See more info about this parameter in SAP notes 820824 & 936058 >

Please update with output of commands running on the database server

x_cons KFV show io 30 2

x_cons KFV show active 30 2

x_cons KFV show rte 30 2

< Each command will run for 30sec >

The Garbage collectors started every 30sec automatically & checked if you have history pages in the database to be deleted.

One garbage collector picked up the one history-file to be deleted.

Did you have the long running transactions in the past?

***

You are SAP customer, please create the ticket to 'BC-DB-SDB' therefore we could logon & review the problem on your system, if you still have problems to delete the history Pages in your database by Garbage collectors.

If you still have too many history pages the garbage collectors could be distributed in more CPUs. I saw from above that garbage collectors used only 3 UKTs. Also it need to be checked why you have too many history pages in the database.

Thank you & best regards, Natalia Khlopina

markus_doehr2
Active Contributor
0 Kudos

Hi Natalia,

> 1)"The database analyzer doesn't start (for whatever

> reason)."

The error is

2007-08-09 23:24:44
ERROR 5: Cannot execute SQL statement.
[SAP AG][LIBSQLOD SO][MaxDB] Numeric value out of range;-811 Numeric output parameter overflow
SELECT * FROM SYSINFO.LOGSTATISTICS

> 2)As I understood you have 15624797 history pages =>

> 119.2GB

> You wrote, that "I can see, that the system is

> deleting ~ 4 - 5 GB in 10

> minutes (which is pretty fast I think)." If I'm

> correct,

> You will wait ~300min or less < ~ 5h > for the

> history pages to be deleted.

It is still running

>

> Please run the SQL statement 'select sum(pagecount)

> from historyinfo' in interval 10min to review how

> many history pages left & estimate the number of

> history pages deleted in 10 min.

Initial value:

7.140291E6

after 10 minutes

7.054757E6

> What are the values of the database parameters:

> TASKCLUSTER01,_TASKCLUSTER_02, and TASKCLUSTER03

> <The TASKCLUSTER is defined by the parameters

> TASKCLUSTER01,

> TASKCLUSTER02, and TASKCLUSTER03>

_TASKCLUSTER_01                         tw;al;ut;2000*sv,100*bup;10*ev,10*gc;
_TASKCLUSTER_02                         ti,100*dw;18*us;
_TASKCLUSTER_03                         equalize

> IOPROCSPER_DEV

> < See more info about this parameter in SAP notes

> 820824 & 936058 >


_IOPROCS_PER_DEV                        2

>

>

> Please update with output of commands running on the

> database server

> x_cons KFV show io 30 2

> x_cons KFV show active 30 2

> x_cons KFV show rte 30 2

After 30 seconds you won't see more than you see when executing with 1 second. It's running and running and running - and nicely dispatching.

> You are SAP customer, please create the ticket to

> 'BC-DB-SDB' therefore we could logon & review the

> problem on your system, if you still have problems to

> delete the history Pages in your database by Garbage

> collectors.

> If you still have too many history pages the garbage

> collectors could be distributed in more CPUs. I saw

> from above that garbage collectors used only 3 UKTs.

> Also it need to be checked why you have too many

> history pages in the database.

Natalia, those many history pages are expected.

Following scenario: We use TDMS (Test Data MIgration Server) to build up a reduced system with data of only e. g. one year.

After we did a system copy of the production, the TDMS did delete all the data, that is not valid for the given period. We have > 10 years of data in the original system, so we expect to have a HIGH deletion of data in the system and thus expecting to have a high history count.

What I was trying to find out is a way to speed that process up, nobody is working in the system actually (because it deleted the data). Since yesterday ~ 450 GB of data was deleted - which is correct.

I wanted just to speed up that process so that I can start this evening with the "refill" of the data from the production system. I know, I can start this although the system is still "deleting" but I want to do a backup of the "empty" system.

It's not a big issue, I'm just trying to find out, how to configure the system for that specific scenario.

Thank you for all your suggestions.

Regards,

Markus

former_member229109
Active Contributor
0 Kudos

Hello Markus,

1) Please update with output of the following commands:

dbmcli -U c dban_state

/sapdb/programs/bin/dbanalyzer -d KFV -u control,<control> -V

sdbregview -l

to check the version of the DB analyzer.

Please update the ticket with output of the SQL statement

SELECT * FROM SYSINFO.LOGSTATISTICS

< Could be known problem reported in PTS 1142294, you defined the

log area of the size 1048320 pages. >

2) Could you please decrease the number of MAXCPU to 3 & increase the number of GCs to 45. Please change the database parameters::

MAXCPU -> 3

MAXGARBAGECOLL -> 45

RESTARTTIME -> 1200

Please restart the database to activate the values.

Please run the SQL statement 'select sum(pagecount) from historyinfo' in interval 10min to review how many history pages left & estimate the number of history pages deleted in 10 min.

After the history pages will be deleted, please go back to the values :

MAXCPU -> 8

MAXGARBAGECOLL -> 10

RESTARTTIME -> 600 < ! >

3) Please update with output of three commands running on the database server :

x_cons KFV show io 30 2

x_cons KFV show active 30 2

x_cons KFV show rte 30 2

"After 30 seconds you won't see more than you see when executing with 1 second. It's running and running and running - and nicely dispatching"

usage: x_cons [<serverdb>] <Command> [<Interval> [<Repeat>]]

=> I would like to see the output of the x_cons commands repeated twice with Interval 30 < sec> !!

Thank you and best regards, Natalia Khlopina

markus_doehr2
Active Contributor
0 Kudos

Hi Natalia,

after some days now the deletion is finished - thank for all your help, I appreciate.

Regards,

Markus

Answers (2)

Answers (2)

Former Member
0 Kudos

Hi Markus,

I recommend that you increase the number of GCs to 10 and restart the instance. This should speed up garbage collection.

Thanks,

Ashwath

markus_doehr2
Active Contributor
0 Kudos

Thanx, that seems to work

T70 5 -1 GarbCol -1 IO Wait (R) 0 0 3 14357(s)

T71 5 -1 GarbCol -1 IO Wait (R) 0 0 30 14357(s)

T75 5 -1 GarbCol -1 IO Wait (R) 0 0 18 14357(s)

T78 5 -1 GarbCol -1 IO Wait (R) 0 0 3 14357(s)

T79 5 -1 GarbCol -1 IO Wait (R) 0 0 39 14357(s)

T80 15 -1 GarbCol -1 IO Wait (R) 0 0 42 14640(s)

T81 15 -1 GarbCol -1 IO Wait (R) 0 0 26 14640(s)

T84 15 -1 GarbCol -1 IO Wait (R) 0 0 37 14640(s)

T86 15 -1 GarbCol -1 IO Wait (R) 0 0 21 14640(s)

T89 15 -1 GarbCol -1 IO Wait (R) 0 0 21 14640(s)

T90 16 -1 GarbCol -1 IO Wait (R) 0 0 38 5620(s)

T91 16 -1 GarbCol -1 IO Wait (R) 0 0 6 5620(s)

T101 17 -1 GarbCol -1 IO Wait (R) 0 0 6 2630(s)

T220 7 -1 User 5819 IO Wait (R) 0 0 5 1341(s)

Any implications of doing so (increasing number of servertasks or the like)?

Regards,

Markus

former_member229109
Active Contributor
0 Kudos

Hello Markus,

-> What is the value of the database prameter MAXGARBAGECOLL?

-> What is the version of the database? What is the type of your database?

-> Please update with output of the SQL statement:

select sum(pagecount) from historyinfo

-> Please review SAP Note No. 748225. This note describes how you can use SAPGUI to identify poor I/O times in liveCache and in the OLTP database SAP DB/MaxDB.

Thank you and beswt regards, Natalia Khlopina

markus_doehr2
Active Contributor
0 Kudos

Hi Natalia,

> -> What is the value of the database prameter

> MAXGARBAGECOLL?

siau-kfv:kfvadm 83> dbmcli -U c param_directget _MAXGARBAGE_COLL
OK
_MAXGARBAGE_COLL        1

> -> What is the version of the database? What is the

> type of your database?

siau-kfv:kfvadm 84> dbmcli db_enum
OK
KFV     /sapdb/KFV/db                           7.6.02.15       fast    running
KFV     /sapdb/KFV/db                           7.6.02.15       quick   offline
KFV     /sapdb/KFV/db                           7.6.02.15       slow    offline
KFV     /sapdb/KFV/db                           7.6.02.15       test    offline

type is OLTP

> -> Please update with output of the SQL statement:

> select sum(pagecount) from historyinfo

siau-kfv:kfvadm 85> sqlcli -U w

Welcome to the MaxDB interactive terminal.
                                           
Type:  h for help with commands          
       q to quit                         

sqlcli=> select sum(pagecount) from historyinfo
| EXPRESSION1                                    |
| ---------------------------------------------- |
|                                    1.5624797E7 |

> ase review SAP Note No. 748225. This note describes

> how you can use SAPGUI to identify poor I/O times in

> liveCache and in the OLTP database SAP DB/MaxDB.

>

The problem is not, that the I/O subsystem is "slow", it's just the pure mass of the data to be deleted.

That system is about 1.5 TB and I want to have finally a "clean" system, so I've done quite a few client deletions. I expect that about only 200 GB will stay, everything else should be deleted so the logical deletion using a client deletion will take some time, sure, but I wonder, if it would be faster, if more GCs are there that can do deletions in parallel.

--

Markus