MaxDB consuming too much swap memory

[Former Member]

Hi,

in an installation I used MaxDB as the database for Pentaho BI, the data cache size is 2.093.856KB, but at linux top command i see a great consumption of memory, first all RAM then all SWAP, after MaxDB consumes all memory.

the version is 7.6.06.03 64 bits, with following non default parameters:

HASHED_RESULTSET = YES

LOAD_BALANCING_CHK = 30

MAX_SINGLE_HASHTABLE_SIZE = 120000

MAX_HASHTABLE_MEMORY = 240000

MAXCPU = 2

CACHE_SIZE = 262144

top outputt:

top - 14:07:15 up 161 days,  2:43,  1 user,  load average: 0.04, 0.05, 0.02
Tasks:  74 total,   1 running,  73 sleeping,   0 stopped,   0 zombie
Cpu(s):  0.2%us,  0.0%sy,  0.0%ni, 99.3%id,  0.5%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:   4048324k total,  4009312k used,    39012k free,    86752k buffers
Swap:  8385912k total,  3297828k used,  5088084k free,   960788k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                                      
16192 sdb       18   0 4669m 2.7g  14m S  0.0 70.0   1512:48 kernel                                                       
26046 sdb       16   0 28892 3876 2916 S  0.0  0.1   0:00.35 dbmsrv                                                       
26055 root      15   0 88948 3340 2596 S  0.0  0.1   0:00.01 sshd                                                         
26050 sdb       18   0 28804 3220 2476 S  0.0  0.1   0:00.03 dbmsrv                                                       
16187 sdb       18   0 57324 1840 1644 S  0.0  0.0   0:00.97 kernel                                                       
26057 root      15   0 66092 1592 1176 S  0.0  0.0   0:00.03 bash                                                         
16184 sdb       18   0 29300 1188  928 S  0.0  0.0   0:16.02 dbmsrv                                                       
26137 root      15   0 12604 1040  812 R  0.0  0.0   0:00.02 top                                                          
26102 sdb       15   0 26352  960  696 S  0.0  0.0   0:00.00 vserver                                                      
16182 sdb       16   0 25040  872  660 S  0.0  0.0   0:01.47 dbmevtdisp                                                   
26103 sdb       15   0 26352  736  584 S  0.0  0.0   0:00.01 vserver                                                      
26087 sdb       15   0 26352  720  576 S  0.0  0.0   0:00.00 vserver                                                      
26089 sdb       15   0 26352  720  576 S  0.0  0.0   0:00.00 vserver                                                      
26101 sdb       15   0 26352  720  576 S  0.0  0.0   0:00.00 vserver                                                      
26107 sdb       15   0 26352  720  576 S  0.0  0.0   0:00.00 vserver                                                      
26119 sdb       15   0 26352  720  576 S  0.0  0.0   0:00.00 vserver

I see in some documentation that I not remember now, that in old versions 7.5 the parameter HASHED_RESULTSET must be set to NO, is that the case for 7.6 too?

best regards

Clóvis

[former_member184473 · Accepted solution]

Hello Clóvis,

This parameter depends which MaxDB 7.6 version you have.

On note 814704:

o  Using Versions 7.6.02 to 7.6.06 lower than build 07
   There is enormous memory consumption during individual commands
   and HASHED_RESULTSET=YES (PTS: 1149231). If you are using Version
   7.6 as of Support Package 02, set the parameter HASHED_RESULTSET
   to NO.
   See also Note 1148494.

Regards,

Eduardo Rezende

[Former Member · Accepted solution]

Hi, Eduardo,

following your information I updated the MaxDB to 7.6.06.10, but database still consuming the swap memory, maybe exists another parameter that can cause a high use of memory?

top - 09:04:07 up 164 days, 21:40,  1 user,  load average: 0.01, 0.13, 0.23
Tasks:  65 total,   1 running,  64 sleeping,   0 stopped,   0 zombie
Cpu(s):  0.2%us,  0.0%sy,  0.0%ni, 99.8%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:   4048324k total,  4022900k used,    25424k free,    55840k buffers
Swap:  8385912k total,  1127992k used,  7257920k free,   681540k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                                      
27625 sdb       18   0 3718m 3.1g  18m S  0.0 79.8 348:30.34 kernel                                                       
27620 sdb       18   0 57492 7372 6044 S  0.0  0.2   0:00.82 kernel                                                       
27617 sdb       18   0 29304 3696 2760 S  0.0  0.1   0:12.28 dbmsrv                                                       
30735 root      15   0 88076 3328 2596 S  0.0  0.1   0:00.02 sshd                                                         
27615 sdb       16   0 25040 2516 1956 S  0.0  0.1   0:01.04 dbmevtdisp                                                   
30737 root      15   0 66092 1576 1164 S  0.0  0.0   0:00.02 bash                                                         
27608 sdb       15   0 22884 1036  872 S  0.0  0.0   0:00.50 vserver                                                      
30761 root      15   0 12604 1028  812 R  0.0  0.0   0:00.00 top                                                          
30598 sdb       15   0 26352  768  588 S  0.0  0.0   0:00.02 vserver                                                      
30678 sdb       15   0 26352  764  584 S  0.0  0.0   0:00.00 vserver                                                      
30679 sdb       15   0 26352  764  584 S  0.0  0.0   0:00.00 vserver                                                      
30716 sdb       15   0 26352  764  584 S  0.0  0.0   0:00.00 vserver                                                      
30732 sdb       16   0 26352  764  584 S  0.0  0.0   0:00.00 vserver                                                      
30733 sdb       16   0 26352  764  584 S  0.0  0.0   0:00.00 vserver                                                      
30734 sdb       15   0 26352  764  584 S  0.0  0.0   0:00.00 vserver

best regards

Clóvis

[former_member184473 · Accepted solution]

Hello Clóvis,

Do you have all the parameters set as per note 833216? You can use note 111426 to check it.

Note 833216 - Parameter values as of liveCache Versions 7.5, 7.6 and 7.7

Note 1111426 - Parameter check for liveCache/MaxDB instances

Regards,

Eduardo Rezende

[Former Member · Accepted solution]

Hi Eduardo,

I cant have access to SAP notes, I'm not a SAP customer, I'm using MaxDB as a Datawarehouse for Pentaho OpenSource BI. You have any other link for me to check that parameters?

best regards

Clóvis

[thorsten_zielke · Accepted solution]

Hello Clóvis,

you can download the Database Analyzer 'parameter check file' here:

Just scroll to the end of the download page.

We are currently working on getting a newer version delivered that also supports MaxDB 7.8, but since you are running 7.6, you should be fine.

Regarding memory consumption you might als enable the Database Analyzer tool, go to expert analysis and check the SYSHEAP column from the 'filling' view. This should list the MaxDB kernel memory usage for each time interval.

Best regards,

Thorsten

[Former Member · Accepted solution]

Hi Thorsten,

I executed dbanalyzer an get follow result:

===== #0          at 2011-04-11 11:01:48
*  I   
*  I  Database Analyzer configuration check version 1.08, December 29, 2008
*  I   
*  I  General information:
*  I  --------------------
*  I  Instance DBSERVER (dbserver.database) is up since 2011-03-31 09:11:42
*  I  Kernel version: Kernel    7.6.06   Build 010-123-229-880
*  I  Number of logical CPUs: 2, physical CPUs: 1, processor type: x86_64
*  I  Physical memory 3953 MB, virtual memory 8189 MB, memory allocated from instance: 2440.52 MB
*  I  Operating system: Linux 2.6.18-128.2.1.el5 #1 SMP Tue Jul 14 06:36:37 EDT 2009
*  I  Configuration of 'MAXCPU': 2, 'MAXUSERTASKS': 50
*  I  Size of data cache 261731 pages, size of converter cache 342 pages
*  I  Number of data volumes 1, usable size 1310718 pages, used size 446696 pages (34%)
*  I   
*  I  General checks:
*  I  ---------------
*  I  'MAXCPU' (2) exceeds number of physical CPUs (1)!
* W3  Log queue overflows: 14, configured log queue pages: 50 for each of 0 log queues
* W1  Lock list escalations: 4
* W2  Recommended value for parameter '_MBLOCK_STRAT_SIZE' is 32768, current value is 32767
*  I  Recommended value for parameter '_IOPROCS_FOR_READER' is 1, current value is 1
*  I  Number of clustered tables: 1
*  I   
*  I  If instance ABRACY is used for Data Warehouse applications, the following recommendations are of interest:
*  I  ----------------------------------------------------------------------------------------------------------
* W1  Recommended value for parameter 'MAX_HASHTABLE_MEMORY' is 24000, current value is 240000

===== #1          at 2011-04-11 11:01:53

This database is used for Data Warehouse, then maybe the MAX_HASHTABLE_MEMORY causing this memory usage?

I must worry about other warnings? any tip about those too?

best regards

Clóvis

[thorsten_zielke · Accepted solution]

Hello Clóvis,

please set MAX_HASHTABLE_MEMORY as recommended, that is to 24000 if you are running BW. That parameter is measured in KB (if I am not mistaken) and that memory is allocated on top of the configured CACHE_SIZE, but only if needed during certain join access. This sais I am not sure this will resolve your memory issue. All other parameters are fine.

To monitor the MaxDB memory usage I would also recommend using the Database Analyzer 'expert analysis'. Just open the file 'DBAN_FILLING' (located in the ANALYZER subdirectory of the database rundirectory) and look at the 'SYSHEAP' column. This shows the overall memory usage of the MaxDB kernel process at given time intervals (default = every 15 minutes).

Thorsten

[Former Member · Accepted solution]

Hi Thorsten,

I changed parameters MAX_HASHTABLE_MEMORY to 24000 and MAX_SINGLE_HASHTABLE_SIZE to 4000.

I dont use BW, but use Pentaho that is used for B.I., then I think that maybe the parameters are equals.

I activated DBAnalyser, to check what happens with memory now, after have some data i will post here.

best regards.

Clóvis

[Former Member · Accepted solution]

Hi, Thorsten,

follow is the DBAN_FILLING:

 1:  COUNT	DATE	TIME	DURATION	DELTA	SysHeap	DB_Size	PermUsed	TempUsed	HistoryUsed	DB_Filling	Log_Size	Log_Used	Log_Filling
 2:  COUNT	DATE	TIME	DURATION	DELTA	MB System heap used by the kernel process	Database size	Permantly used memory	Temporary used memory	history pages in use	% Database filling	Log size	Log used	% Log filling
 3:  P	D	T	P	P	P A	P A	P A	P A	P A	P A	P A	P A	P A
 4:  245	20110502	601	0	901	4378.43	1310718	456363	27	0	34.82	130908	22662	17.00
 5:  246	20110502	2103	0	902	4378.43	1310718	456363	31	0	34.82	130908	22662	17.00
 6:  247	20110502	3604	0	901	4378.43	1310718	456363	31	0	34.82	130908	22662	17.00
 7:  248	20110502	5104	1	900	4378.43	1310718	456363	27	0	34.82	130908	22662	17.00
 8:  249	20110502	10605	1	901	4378.68	1310718	456363	29	0	34.82	130908	22663	17.00
 9:  250	20110502	12107	0	902	4378.43	1310718	456363	27	0	34.82	130908	22663	17.00
...
35:  276	20110502	75125	0	901	4378.43	1310718	456363	27	0	34.82	130908	22663	17.00
36:  277	20110502	80626	0	901	4378.43	1310718	456363	27	0	34.82	130908	22663	17.00
37:  278	20110502	82128	0	902	4378.43	1310718	456363	33	0	34.82	130908	22663	17.00
38:  279	20110502	83634	5	906	4378.43	1310718	456363	35	0	34.82	130908	22663	17.00
39:  280	20110502	85142	0	908	4378.43	1310718	456363	31	0	34.82	130908	22663	17.00
40:  281	20110502	90648	12	906	4378.43	1310718	456363	38	0	34.82	130908	22663	17.00
41:  282	20110502	92201	8	913	4378.43	1310718	456363	32	0	34.82	130908	22664	17.00
42:  283	20110502	93708	0	907	4378.43	1310718	456363	32	0	34.82	130908	22665	17.00
43:  284	20110502	95209	1	901	4378.43	1310718	456363	33	0	34.82	130908	22665	17.00
44:  285	20110502	100713	0	904	4378.43	1310718	456363	31	0	34.82	130908	22670	17.00
45:  286	20110502	102215	61	902	4378.43	1310718	456363	41	0	34.82	130908	22671	17.00
46:  287	20110502	103823	27	968	4378.44	1310718	456363	34	0	34.82	130908	22671	17.00
47:  288	20110502	105353	1	930	4630.19	1310718	456363	42	0	34.82	130908	22673	17.00
48:  289	20110502	110854	56	901	4630.19	1310718	456363	40	0	34.82	130908	22674	17.00
49:  290	20110502	112452	16	958	4630.20	1310718	456363	41	0	34.82	130908	22674	17.00

and here is the output of linux top command:

top - 11:32:15 up 199 days, 8 min,  2 users,  load average: 1.45, 1.42, 1.35
Tasks:  77 total,   1 running,  76 sleeping,   0 stopped,   0 zombie
Cpu(s):  0.2%us,  0.2%sy,  0.0%ni, 99.7%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:   4048324k total,  4016956k used,    31368k free,    65092k buffers
Swap:  8385912k total,  2384624k used,  6001288k free,   213856k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                                      
20204 sdb       18   0 4698m 3.5g  14m S  0.3 89.4   1025:11 kernel                                                       
26105 sdb       15   0 55440  11m 1736 S  0.0  0.3   0:43.47 dbanalyzer                                                   
30179 sdb       16   0 28872 3696 2776 S  0.0  0.1   0:00.18 dbmsrv                                                       
30270 root      15   0 88816 3328 2596 S  0.0  0.1   0:00.00 sshd
...

[thorsten_zielke · Accepted solution]

Hi Clovis,

I agree that ithe system heap usage of 4,6 GB seems to be quite high in relation of only around 2 GB data cache.

If you are a SAP customer, I would recommend opening an OSS message for this, so that we could log on directly to the system and check who is using that memory.

But let me also give you some suggestions:

1. What is the 'maxusertasks' configured for? If more than 1000, you migh check lowering it. The more maxusertasks are configured, the more additional memory on top of the data cache is allocated.

2. Your current release 7.6.06.10 is a bit outdated. We will be making 7.7.07.33 and 7.8.02.21 available on SDN in the near future, so upgrading would be an option for you (in case the high memory usage was caused ba a bug).

3. You could also check for which allocator the most memory has been requested from with the following SQL:

select ALLOCATORNAME, USEDSIZE, MAXUSEDSIZE, ALLOCATEDSIZE from memoryallocatorstatistics order by maxusedsize

4. Is the memory usage still climbing or does it level out at some point which seems to be the maximum?

Regards,

Thorsten

[Former Member · Accepted solution]

HI Thorsten,

I'm not a SAP customer, I'm using community version of MaxDB and the Pentaho B.I. opensource.

1) MAXUSERTASKS = 150

2) about migration, today I cant do that, as somethings for Pentaho B.I. just works with 7.6, and today I dont have time to do that migration in parallel to check if that works or no.

3) result of top 40:

ALLOCATORNAME;USEDSIZE;MAXUSEDSIZE;ALLOCATEDSIZE;
SystemHeap;6542471168;6542471168;0
SystemPageCache;4617093120;5221703680;6273306624
RTEMem_BlockAllocator;4616880128;5221490688;4616880128
TransContext T127;240352;286891312;278528
StackSpace;214130688;214130688;215556096
Log_FrameAllocator;131088384;131153920;131088384
DataCache::ControlBlockPool;67059712;67059712;67239936
RTEMem_Allocator;48251600;51910576;49283072
DataCache;34185864;34185864;34185864
Join_HashAccessAllocator;0;12991976;20938752
LVCMem_BlockAllocator;11542528;11542528;11542528
CommandCacheAllocator;5200720;5200720;16777216
Log_Volume;3366592;3366592;3366592
FBM_Manager;2105352;2106056;2105352
RTEMem_RteAllocator;669384;1535776;1048576
TransContext T125;272472;1473864;540672
LVCMem_EmergencyAllocator;0;1048600;10485760
TransContext T100;288032;546472;409600
IOMan_Manager;0;524288;0
TransContext T159;237720;494856;278528
CatalogCache;395936;489112;417792
TransContext T141;236464;458536;278528
TransContext T176;237280;457904;278528
TransContext T203;238344;457904;278528
TransContext T118;235816;457904;278528
TransContext T139;236192;457904;278528
Log_Queue;448232;448232;448232
Log_Queue;448168;448168;448168
Log_Queue;447672;447672;447672
Log_Queue;445744;445744;445744
Log_Queue;429056;429056;429056
Log_Queue;396752;396752;396752
Log_Queue;374544;374544;374544
Log_Queue;370224;370224;370224
Converter_Manager;359968;365400;359968
RTE_RawAllocator;77200;77200;204800
TransContext T083;0;61808;278528
TransContext T096;0;61808;278528
FileDir_PointerCache;17680;59024;17680
LVCMem_Allocator_001;27552;27552;1048576

4) still climbing until system stop to update data, then we must stop/start maxdb, a big problem we see, when the maxdb eat all system memory, including swap, we can insert data, but any select we issue dont returns that inserted data.

past week, we increased the system RAM memory to 8GB and we allocated 4GB to data cache, as we though that 4GB was needed, then for surprise, we check that memory still increasing.

we are using some features of BW, like having a FACT table declared, maybe this can cause this?

best regards

Clóvis

[thorsten_zielke · Accepted solution]

Hi Clovis,

this is likely a bug (memory leak).

To proceed here, please check the following options:

1. As recommended before, try to upgrade to MaxDB 7.7.07.33. This patch should be available on SDN for download within the next 2 weeks.

2. Monitor the memory usage e.g. by checking the Database Analyzer 'sysheap' filling level. Standard interval is 15 minutes, but you might want to reduce it to every 60 seconds if needed. The idea is that you try to find out what action has been performed on SQL level when the memory usage increases. I would expect the memory usage to stay more or less constant, but during a certain SQL statement, it should spike and remain there (because the memory does not get deallocated).

With that statement identified, we could better assess where the problem comes from and how to help you.

3. As a guess, you might want to try disabling the parameters HASHED_RESULTSET and OPTIMIZE_OPERATOR_JOIN by setting them to NO (if not already done so), but please do not forget to reset them to their recommended value after the issue has been resolved or if they do not prove to resolve this, because disabling these parameters will cost performance.

4. Not a pretty solution, but as long as this is unresolved, you will likely have to schedule a periodic downtime to avoid OS paging due to too much memory usage (paging could lead to a drastic performance decrease).

Thorsten