SAP ECC6 memory and paging issues

[Former Member]

Dear Experts

I have recently upgraded my 4.6C systems to an ECC 6 system (DB2 LUW 9.5 on AIX 5.3 TL9 64 Bit OS)

I have been running the LPAR with 14 GB of memory and we are around 100-200+ users using the system, I was monitoring using nmon and found that Physical Memory was around 99.8% Used (14311.8MB and 22.6MB was free) also the paging space was around 37.2% in result causing the system at times to run slow which can have a very negative effect on the users.

After further investigation I found that after a system restart the Physical Memory would start around 50.9% and increased at a steady pace until it reached 99.8% that is when the system would start using the paging space which would steadily increase, I found that the only solution was a system restart at least once a week to reduce the memory consumption.

At first glance it looked like a database manger memory leak with the process db2sysc, so I searched the net with the search words u201Cdb2 memory leaku201D and found the following APARs and notes.

APAR JR30285 - Pervasive memory leak when compiling SQL statements that use SQL/XML functions

APAR IZ35230 - There is a pervasive unix-specific private memory leak in the security component

Note 1288341 - Memory leak in APPLHEAPSZ -> SQL0954C

Note 1352361 - Memory leak in shared memory area abrfci

Note 1147821 - DB6: Known Errors and available Fixes in DB2 9.5 LUW

After reading the notes and APARs I decided to updated DB2 to the latest fix pack (5SAP), but after the fix pack was implemented it did not solve the memory problem

I started look at different problems with SAP ECC6, db2 and AIX with paging/memory problems and I found the following notes to do with AIX memory and paging but none of them helped as all parameters and settings were set accordingly

789477 - Large extended memory on AIX (64-bit) as of Kernel 6.20

191801 - AIX 64-bit with very large amount of Extended Memory

973227 - AIX Virtual Memory Management: Tuning Recommendations

884393 - AIX saposcol consumes large amount of memory.

856848 u2013 AIX Extended Memory Disclaiming

1048686 u2013 Recommended AIX settings for SAP

1121904 u2013 SAP on AIX: Recommendations for Paging

1086130 u2013 DB6: DB2 Standard Parameter Settings

After even more investigation I found the following evidence suggesting AIX Virtual Memory Manager might have a problem

[Former Member]

Hi All

Just a quick update,

It looks like my problem is solved, I recently received the results from the SAP GoingLive functional upgrade check verification session, and SAP had suggested specific SAP and DB2 parameter changes (Listed Below) which were changed over the weekend and after monitoring the systems for the past 4 days it seems that the changes have fixed the memory and paging issue I have been experiencing.

I believe that the the setting the database memory, instance memory and the page cleaner trigger were ultimately responsible for the memory and paging issues. But I will be conducting some extra test to try and work out which parameters were directly responsible for the memory and paging problems

DB2 Database Configuration Changes:

Database Memory (database_memory) from: AUTOMATIC to: COMPUTED

Utilities heap size (util_heap_sz) from: 10000 to: 50000

Threshold for page cleaner trigger (chngpgs_thresh) from: 40 to: 20

DB2 Database Manager Configuration Changes:

Keep fenced process (keepfenced) from: NO to: YES

Number of fenced processes (fenced_pool Maximum) from: 5 to: 10

SAP Instance Parameter Changes:

Number of nametab entries administrated (rsdb/ntab/entrycount) from: 50000 to: 65000

Size of generic table buffer (zcsa/table_buffer_area) from: 129850000 to: 168805000

Size of short-term memory (em/short_area) from: 1600000 to: 2000000

Size of short-term memory (ztta/short_area) from: 1600000 to: 2000000

AIX: Segs Implementation Version (ES/SHM_SEGS_VERSION) from: N/A to: 2

Max. number of internal asynchronous messages (rdisp/max_arq) from: 500 to: 2000

Network Connection Keep Alive Timeout (icm/keep_alive_timeout) from: 60 to: 90

I will post another update after I have worked out which parameters a directly responsible

I hope this helps Regards

Simon Grzebieta

DB2 DBA Consultant

[markus_doehr2]

> I started look at different problems with SAP ECC6, db2 and AIX with paging/memory problems and I found the following notes to do with AIX memory and paging but none of them helped as all parameters and settings were set accordingly

Please post (as user <sid>adm) the output of

sappfpar check pf=<your-instances-profile>

Markus

[Former Member]

>

> Please post (as user <sid>adm) the output of 

===============================================================

Checking profile: ABC_DVEBMGS14_isapabc

===============================================================

***WARNING: Unexpected parameter: auth/auth_number_in_userbuffer =9000

***WARNING: Unexpected parameter: rsdb/reco_defer_msg =1

***WARNING: Unexpected parameter: system/type =ABAP

Shared memory disposition overview

===============================================================

Shared memory pools

Key: 10 Pool

Size configured.....: 588000000 ( 560.8 MB)

Size min. estimated.: 344538480 ( 328.6 MB)

Advised Size........: 348000000 ( 331.9 MB)

Key: 40 Pool for database buffers

Size configured.....: 500000000 ( 476.8 MB)

Size min. estimated.: 150296088 ( 143.3 MB)

Advised Size........: 154000000 ( 146.9 MB)

Shared memories inside of pool 10

Key: 1 Size: 2500 ( 0.0 MB) System administration

Key: 2 Size: 53434772 ( 51.0 MB) Disp. administration tables

Key: 11 Size: 500000 ( 0.5 MB) Factory calender buffer

Key: 12 Size: 6000000 ( 5.7 MB) TemSe Char-Code convert Buf.

Key: 13 Size: 40500000 ( 38.6 MB) Alert Area

Key: 14 Size: 20000000 ( 19.1 MB) Presentation buffer

Key: 16 Size: 22400 ( 0.0 MB) Semaphore activity monitoring

Key: 17 Size: 2741224 ( 2.6 MB) Roll administration

Key: 30 Size: 36864 ( 0.0 MB) Taskhandler runtime admin.

Key: 31 Size: 4206000 ( 4.0 MB) Dispatcher request queue

Key: 33 Size: 30720000 ( 29.3 MB) Table buffer, part.buffering

Key: 34 Size: 10240000 ( 9.8 MB) Enqueue table

Key: 51 Size: 3200000 ( 3.1 MB) Extended memory admin.

Key: 52 Size: 40000 ( 0.0 MB) Message Server buffer

Key: 54 Size: 35848192 ( 34.2 MB) Export/Import buffer

Key: 55 Size: 8192 ( 0.0 MB) Spool local printer+joblist

Key: 57 Size: 1048576 ( 1.0 MB) Profilparameter in shared mem

Key: 58 Size: 4096 ( 0.0 MB) Enqueue ID for reset

Key: 62 Size: 85983232 ( 82.0 MB) Memory pipes

[Former Member]

Shared memories inside of pool 40

Key: 42 Size: 17792992 ( 17.0 MB) DB TTAB buffer

Key: 43 Size: 53606392 ( 51.1 MB) DB FTAB buffer

Key: 44 Size: 8550392 ( 8.2 MB) DB IREC buffer

Key: 45 Size: 7014392 ( 6.7 MB) DB short nametab buffer

Key: 46 Size: 20480 ( 0.0 MB) DB sync table

Key: 47 Size: 10241024 ( 9.8 MB) DB CUA buffer

Key: 48 Size: 300000 ( 0.3 MB) Number range buffer

Key: 49 Size: 2769392 ( 2.6 MB) Spool admin (SpoolWP+DiaWP)

Shared memories outside of pools

Key: 3 Size: 114048000 ( 108.8 MB) Disp. communication areas

Key: 4 Size: 523048 ( 0.5 MB) statistic area

Key: 6 Size: 692224000 ( 660.2 MB) ABAP program buffer

Key: 7 Size: 14838 ( 0.0 MB) Update task administration

Key: 8 Size: 134217828 ( 128.0 MB) Paging buffer

Key: 9 Size: 134217828 ( 128.0 MB) Roll buffer

Key: 18 Size: 1835108 ( 1.7 MB) Paging adminitration

Key: 19 Size: 119850000 ( 114.3 MB) Table-buffer

Key: 41 Size: 25010000 ( 23.9 MB) DB statistics buffer

Key: 63 Size: 409600 ( 0.4 MB) ICMAN shared memory

Key: 64 Size: 4202496 ( 4.0 MB) Online Text Repository Buf.

Key: 65 Size: 4202496 ( 4.0 MB) Export/Import Shared Memory

Key: 1002 Size: 400000 ( 0.4 MB) Performance monitoring V01.0

Key: 58900114 Size: 4096 ( 0.0 MB) SCSA area

Nr of operating system shared memory segments: 16

Shared memory resource requirements estimated

================================================================

Total Nr of shared segments required.....: 16

System-imposed number of shared memories.: 1000

Shared memory segment size required min..: 692224000 ( 660.2 MB)

System-imposed maximum segment size......: 35184372088832 (33554432.0 MB)

Swap space requirements estimated

================================================

Shared memory....................: 1654.8 MB

..in pool 10 328.6 MB, 58% used

..in pool 40 143.3 MB, 30% used

..not in pool: 1174.1 MB

Processes........................: 413.4 MB

Extended Memory .................: 6144.0 MB

Total, minimum requirement.......: 8212.2 MB

Process local heaps, worst case..: 3814.7 MB

Total, worst case requirement....: 21882.9 MB

Errors detected..................: 0

Warnings detected................: 3

[Former Member]

I found reports from nmon analyzer that are reported on daily and are kept for over 6 months, so I have compiled a spread sheet with the relevant information about memory usage and it starts at a system restart then collected for 7 days, with before and after the upgrade, I did a comparison and below is what I found:

nmon analyzer results:

MEMNEW:

In the before spread sheet for the MEMNEW worksheets show that the average process usage is around 70%-100% and occasionally spiking but it always returning to around 70%, and in the after spread sheet for the MEMNEW worksheets its shown that the average process usage is around 90%-100%.

MEMUSE:

In the before spread sheet for the MEMUSE worksheets show that the %numperm is rarely below the %minperm and the %comp is never above the %maxperm and in the after spread sheet for the MEMUSE worksheets show that the %numperm is always below the %minperm and the %comp is always above the %maxperm thus the virtual memory manager is forced to page anything at will

If any one would like to view the spread sheets I can e-mail them the following: nmon_P1LPAR03_Before.xls and nmon_P1LPAR03_After.xls

At this point I have hit a wall and donu2019t know what I can do to fix the problem

If any one can please help me it would be greatly appreciated

Regards,

Simon Grzebieta

DB2 DBA Consultant

[Former Member]

Hi Simon, also running DB2 9.5 with FP5sap on AIX 5.3 TL11 and ECC6 (unicode), upgraded from 4.6c late last year and seem to have similar situation to what you describe -> memory in use by DB2 instance grows over time, pushing up swap utilisation, only way to resolve is to restart DB2 instance (& SAP)

Wondering if you use STMM? Do you have instance_memory as automatic or a fixed value?

Daniel Parker

[Former Member]

>

> Wondering if you use STMM? Do you have instance_memory as automatic or a fixed value?

STMM is turned off and db2 instance_memory is set to a fixed value of 1000000 4KB pages or 3.8 GB