- SAP Community
- Products and Technology
- Technology
- Technology Q&A
- MaxDB Read Performance
- Subscribe to RSS Feed
- Mark Question as New
- Mark Question as Read
- Bookmark
- Subscribe
- Printer Friendly Page
- Report Inappropriate Content
MaxDB Read Performance
- Subscribe to RSS Feed
- Mark Question as New
- Mark Question as Read
- Bookmark
- Subscribe
- Printer Friendly Page
- Report Inappropriate Content
on 02-03-2009 7:04 AM
Hi,
I am having several old box of 7.6.00.16 as I previously posted on the board, and I recently start to explore the ways to improve its boot performance again.
I am using Linux and managed to do 300K+ block read in raw file system, but in maxdb I only managed to get 2K+ block read per second with IOWAIT according to database analyzer
This box is a 4 CPU 8GB RAM 8 HDD (RAID 10) box with a good hardware raid card .... so I wondering if it's only the parameter that caused the trouble (actually our production server has 8 CPU, 32GB RAM and 14 HDD (RAID 10) but am too worry to touch it)
parameter as below
KERNELVERSION KERNEL 7.6.00 BUILD 016-123-109-428
INSTANCE_TYPE OLTP
MCOD NO
_SERVERDB_FOR_SAP NO
_UNICODE YES
DEFAULT_CODE UNICODE
DATE_TIME_FORMAT INTERNAL
CONTROLUSERID DBM
CONTROLPASSWORD
MAXLOGVOLUMES 8
MAXDATAVOLUMES 32
LOG_BACKUP_TO_PIPE NO
MAXBACKUPDEVS 16
BACKUP_BLOCK_CNT 8
LOG_MIRRORED NO
MAXVOLUMES 41
_MULT_IO_BLOCK_CNT 8
_DELAY_LOGWRITER 0
LOG_IO_QUEUE 4000
_RESTART_TIME 600
MAXCPU 8
MAX_LOG_QUEUE_COUNT 0
USED_MAX_LOG_QUEUE_COUNT 8
LOG_QUEUE_COUNT 4
MAXUSERTASKS 400
_TRANS_RGNS 64
_TAB_RGNS 64
_OMS_REGIONS 0
_OMS_RGNS 7
OMS_HEAP_LIMIT 0
OMS_HEAP_COUNT 8
OMS_HEAP_BLOCKSIZE 10000
OMS_HEAP_THRESHOLD 100
OMS_VERS_THRESHOLD 2097152
HEAP_CHECK_LEVEL 0
_ROW_RGNS 64
MINSERVERTASKS 458
MAXSERVERTASKS 458
_MAXGARBAGE_COLL 1
_MAXTRANS 14752
MAXLOCKS 147520
_LOCK_SUPPLY_BLOCK 100
DEADLOCK_DETECTION 4
SESSION_TIMEOUT 900
OMS_STREAM_TIMEOUT 30
REQUEST_TIMEOUT 5000
_IOPROCS_PER_DEV 2
_IOPROCS_FOR_PRIO 0
_USE_IOPROCS_ONLY NO
_IOPROCS_SWITCH 2
LRU_FOR_SCAN NO
_PAGE_SIZE 8192
_PACKET_SIZE 131072
_MINREPLY_SIZE 4096
_MBLOCK_DATA_SIZE 32768
_MBLOCK_QUAL_SIZE 16384
_MBLOCK_STACK_SIZE 32768
_MBLOCK_STRAT_SIZE 8192
_WORKSTACK_SIZE 8192
_WORKDATA_SIZE 8192
_CAT_CACHE_MINSIZE 262144
CAT_CACHE_SUPPLY 314572
INIT_ALLOCATORSIZE 245760
ALLOW_MULTIPLE_SERVERTASK_UKTS NO
_TASKCLUSTER_01 tw;al;ut;2000*sv,100*bup;10*ev,10*gc;
_TASKCLUSTER_02 ti,100*dw;50*us;
_TASKCLUSTER_03 equalize
_DYN_TASK_STACK NO
_MP_RGN_QUEUE YES
_MP_RGN_DIRTY_READ YES
_MP_RGN_BUSY_WAIT YES
_MP_DISP_LOOPS 2
_MP_DISP_PRIO YES
MP_RGN_LOOP 100
_MP_RGN_PRIO YES
MAXRGN_REQUEST 3000
_PRIO_BASE_U2U 100
_PRIO_BASE_IOC 80
_PRIO_BASE_RAV 80
_PRIO_BASE_REX 40
_PRIO_BASE_COM 10
_PRIO_FACTOR 80
_DELAY_COMMIT NO
_MAXTASK_STACK 512
MAX_SERVERTASK_STACK 150
MAX_SPECIALTASK_STACK 200
_DW_IO_AREA_SIZE 50
_DW_IO_AREA_FLUSH 50
FBM_VOLUME_COMPRESSION 50
FBM_VOLUME_BALANCE 10
_FBM_LOW_IO_RATE 10
CACHE_SIZE 3145728
_DW_LRU_TAIL_FLUSH 25
XP_DATA_CACHE_RGNS 0
_DATA_CACHE_RGNS 64
XP_CONVERTER_REGIONS 0
CONVERTER_REGIONS 32
XP_MAXPAGER 0
MAXPAGER 64
SEQUENCE_CACHE 1
_IDXFILE_LIST_SIZE 8192
VOLUMENO_BIT_COUNT 8
OPTIM_MAX_MERGE 500
OPTIM_INV_ONLY YES
OPTIM_CACHE YES
OPTIM_JOIN_FETCH 0
JOIN_SEARCH_LEVEL 0
JOIN_MAXTAB_LEVEL4 16
JOIN_MAXTAB_LEVEL9 5
_READAHEAD_BLOBS 32
RUNDIRECTORY /var/opt/sdb/data/wrk/M4N
OPMSG1 /dev/console
OPMSG2 /dev/null
_KERNELDIAGFILE knldiag
KERNELDIAGSIZE 800
_EVENTFILE knldiag.evt
_EVENTSIZE 0
_MAXEVENTTASKS 2
_MAXEVENTS 100
_KERNELTRACEFILE knltrace
TRACE_PAGES_TI 2
TRACE_PAGES_GC 20
TRACE_PAGES_LW 5
TRACE_PAGES_PG 3
TRACE_PAGES_US 10
TRACE_PAGES_UT 5
TRACE_PAGES_SV 5
TRACE_PAGES_EV 2
TRACE_PAGES_BUP 0
KERNELTRACESIZE 6519
EXTERNAL_DUMP_REQUEST NO
_AK_DUMP_ALLOWED YES
_KERNELDUMPFILE knldump
_RTEDUMPFILE rtedump
_UTILITY_PROTFILE dbm.utl
UTILITY_PROTSIZE 100
_BACKUP_HISTFILE dbm.knl
_BACKUP_MED_DEF dbm.mdf
_MAX_MESSAGE_FILES 0
_SHMKERNEL 56052
__PARAM_CHANGED___ 0
__PARAM_VERIFIED__ 2008-11-07 12:18:31
DIAG_HISTORY_NUM 2
DIAG_HISTORY_PATH /var/opt/sdb/data/wrk/M4N/DIAGHISTORY
_DIAG_SEM 1
SHOW_MAX_STACK_USE NO
SHOW_MAX_KB_STACK_USE NO
LOG_SEGMENT_SIZE 262144
_COMMENT
SUPPRESS_CORE YES
FORMATTING_MODE PARALLEL
FORMAT_DATAVOLUME YES
CLUSTERED_IO NO
OFFICIAL_NODE
LOAD_BALANCING_CHK 0
LOAD_BALANCING_DIF 10
LOAD_BALANCING_EQ 5
HS_STORAGE_DLL libhsscopy
HS_SYNC_INTERVAL 50
USE_OPEN_DIRECT NO
SYMBOL_DEMANGLING NO
EXPAND_COM_TRACE NO
JOIN_TABLEBUFFER 2048
SET_VOLUME_LOCK YES
SHAREDSQL YES
SHAREDSQL_EXPECTEDSTATEMENTCOUNT 1500
SHAREDSQL_COMMANDCACHESIZE 32768
MEMORY_ALLOCATION_LIMIT 0
USE_SYSTEM_PAGE_CACHE YES
USE_COROUTINES YES
FORBID_LOAD_BALANCING YES
USE_STACK_ON_STACK YES
USE_UCONTEXT YES
MIN_RETENTION_TIME 60
MAX_RETENTION_TIME 480
MAX_SINGLE_HASHTABLE_SIZE 512
MAX_HASHTABLE_MEMORY 5120
ENABLE_CHECK_INSTANCE YES
RTE_TEST_REGIONS 0
HASHED_RESULTSET NO
HASHED_RESULTSET_CACHESIZE 262144
AUTO_RECREATE_BAD_INDEXES NO
AUTHENTICATION_ALLOW
AUTHENTICATION_DENY
TRACE_AK NO
TRACE_DEFAULT NO
TRACE_DELETE NO
TRACE_INDEX NO
TRACE_INSERT NO
TRACE_LOCK NO
TRACE_LONG NO
TRACE_OBJECT NO
TRACE_OBJECT_ADD NO
TRACE_OBJECT_ALTER NO
TRACE_OBJECT_FREE NO
TRACE_OBJECT_GET NO
TRACE_OPTIMIZE NO
TRACE_ORDER NO
TRACE_ORDER_STANDARD NO
TRACE_PAGES NO
TRACE_PRIMARY_TREE NO
TRACE_SELECT NO
TRACE_TIME NO
TRACE_UPDATE NO
TRACE_STOP_ERRORCODE 0
TRACE_ALLOCATOR 0
TRACE_CATALOG 0
TRACE_CLIENTKERNELCOM 0
TRACE_COMMON 0
TRACE_COMMUNICATION 0
TRACE_CONVERTER 0
TRACE_DATACHAIN 0
TRACE_DATAPAM 0
TRACE_DATATREE 0
TRACE_DBPROC 0
TRACE_FBM 0
TRACE_FILEDIR 0
TRACE_FRAMECTRL 0
TRACE_IOMAN 0
TRACE_IPC 0
TRACE_JOIN 0
TRACE_KSQL 0
TRACE_LOGACTION 0
TRACE_LOGHISTORY 0
TRACE_LOGPAGE 0
TRACE_LOGTRANS 0
TRACE_LOGVOLUME 0
TRACE_MEMORY 0
TRACE_MESSAGES 0
TRACE_OBJECTCONTAINER 0
TRACE_OMS_CONTAINERDIR 0
TRACE_OMS_CONTEXT 0
TRACE_OMS_ERROR 0
TRACE_OMS_FLUSHCACHE 0
TRACE_OMS_INTERFACE 0
TRACE_OMS_KEY 0
TRACE_OMS_KEYRANGE 0
TRACE_OMS_LOCK 0
TRACE_OMS_MEMORY 0
TRACE_OMS_NEWOBJ 0
TRACE_OMS_SESSION 0
TRACE_OMS_STREAM 0
TRACE_OMS_VAROBJECT 0
TRACE_OMS_VERSION 0
TRACE_PAGER 0
TRACE_RUNTIME 0
TRACE_SHAREDSQL 0
TRACE_SQLMANAGER 0
TRACE_SRVTASKS 0
TRACE_SYNCHRONISATION 0
TRACE_SYSVIEW 0
TRACE_TABLE 0
TRACE_VOLUME 0
CHECK_BACKUP NO
CHECK_DATACACHE NO
CHECK_KB_REGIONS NO
CHECK_LOCK NO
CHECK_LOCK_SUPPLY NO
CHECK_REGIONS NO
CHECK_TASK_SPECIFIC_CATALOGCACHE NO
CHECK_TRANSLIST NO
CHECK_TREE NO
CHECK_TREE_LOCKS NO
CHECK_COMMON 0
CHECK_CONVERTER 0
CHECK_DATAPAGELOG 0
CHECK_FBM 0
CHECK_IOMAN 0
CHECK_LOGHISTORY 0
CHECK_LOGPAGE 0
CHECK_LOGTRANS 0
CHECK_LOGVOLUME 0
CHECK_SRVTASKS 0
OPTIMIZE_AGGREGATION YES
OPTIMIZE_FETCH_REVERSE YES
OPTIMIZE_JOIN_ONEPHASE YES
OPTIMIZE_JOIN_OUTER YES
OPTIMIZE_MIN_MAX YES
OPTIMIZE_FIRST_ROWS YES
OPTIMIZE_OPERATOR_JOIN YES
OPTIMIZE_JOIN_HASHTABLE YES
OPTIMIZE_JOIN_HASH_MINIMAL_RATIO 1
OPTIMIZE_OPERATOR_JOIN_COSTFUNC YES
OPTIMIZE_JOIN_PARALLEL_MINSIZE 1000000
OPTIMIZE_JOIN_PARALLEL_SERVERS 0
OPTIMIZE_JOIN_OPERATOR_SORT YES
OPTIMIZE_QUAL_ON_INDEX YES
DDLTRIGGER YES
SUBTREE_LOCKS NO
MONITOR_READ 2147483647Edited by: Chris Li on Feb 3, 2009 8:05 AM
- SAP Managed Tags:
- SAP MaxDB
Accepted Solutions (0)
Answers (2)
Answers (2)
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
The significant time for MaxDB is the time the I/O needs for a single 8 kb block. You can find out the speed by doing
dbmcli -u <dbmuser>,<dbmpassword> -d <DB-NAME> dban_start -t -60
dbmcli -u <dbmuser>,<dbmpassword> -d <DB-NAME> db_cons time enableWith those commands the dbanalyzer starts and measures I/O throughput all 60 seconds and logs it in <workdirectory>/analyzer/DBM.prt
What you see is typical for MaxDB, however, there are improvements to speed reading up (READAHEAD etc.)
Markus
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
> I am having several old box of 7.6.00.16 as I previously posted on the board, and I recently start to explore the ways to improve its boot performance again.
So you know it: "use the current patch instead!", do you?
> I am using Linux and managed to do 300K+ block read in raw file system, but in maxdb I only managed to get 2K+ block read per second with IOWAIT according to database analyzer
Hmm... MaxDB reads/writes pages of 8K with each I/O. Sometimes it tries to do a vector I/O to bundle several single block (8K) I/Os to one big I/O.
Anyhow, how exactly did you check the read performance with and without MaxDB?
> This box is a 4 CPU 8GB RAM 8 HDD (RAID 10) box with a good hardware raid card .... so I wondering if it's only the parameter that caused the trouble (actually our production server has 8 CPU, 32GB RAM and 14 HDD (RAID 10) but am too worry to touch it)
It's very likely not any MaxDB setting - because there isn't much to be setup for raw devices at all.
> parameter as below
>
> PAGESIZE 8192
So even on your system, the page size is 8K (it's *always' 8K, the parameter cannot be changed)
> LOG_QUEUE_COUNT 4
You surely don't want this setting as there had been a nasty bug, that would prevent logs that have been written with this setting to be recoverable. Set this parameter to 1 in any case (until of course you start using the current patch...)
regards Lars
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
So you know it: "use the current patch instead!", do you?
Ya I know it ... and actually I tried it and the same performance bottleneck happens on 7.6.05
Hmm... MaxDB reads/writes pages of 8K with each I/O. Sometimes it tries to do a vector I/O to bundle several single block (8K) I/Os to one big I/O.
Anyhow, how exactly did you check the read performance with and without MaxDB?
Fairly simple ....
cat <any big file> > /dev/null and at the same time watch the statistics using command iostat
for maxdb, I have one of the worse SQL I can have which do almost a table scan with 2 tables
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Report Inappropriate Content
> Ya I know it ... and actually I tried it and the same performance bottleneck happens on 7.6.05
Yepp - because this very likely not a MaxDB issue at all.
BTW: there are tons of other fixes in 7.6.05 which alone should be a reason to install the current patch.
>
Anyhow, how exactly did you check the read performance with and without MaxDB?
> Fairly simple ....
>
> cat <any big file> > /dev/null and at the same time watch the statistics using command iostat
>
> for maxdb, I have one of the worse SQL I can have which do almost a table scan with 2 tables
Sorry, but that's too simple. Reading/Writing to /dev/null will never cause real I/O.
It's just a system call pretenting to do I/O.
If you want to compare the performance, read the data from your Raw devices.
In 8K blocks, random access pattern, one block at a time (so a single dd-call will probably not do here).
regards,
Lars
- Start page of SAP Signavio Process Insights, discovery edition, the 4 pillars and documentation in Technology Blogs by SAP
- Analyze Expensive ABAP Workload in the Cloud with Work Process Sampling in Technology Blogs by SAP
- Introducing Blog Series of SAP Signavio Process Insights, discovery edition – An in-depth exploratio in Technology Blogs by SAP
- Kyma Integration with SAP Cloud Logging. Part 2: Let's ship some traces in Technology Blogs by SAP
- SAP HANA Cloud Vector Engine: Quick FAQ Reference in Technology Blogs by SAP
| User | Count |
|---|---|
| 87 | |
| 10 | |
| 10 | |
| 9 | |
| 7 | |
| 7 | |
| 6 | |
| 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.