on 08-08-2007 11:33 AM
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
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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
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
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
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
User | Count |
---|---|
81 | |
24 | |
11 | |
9 | |
7 | |
5 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.