on 03-25-2011 5:14 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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
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
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
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
...
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
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
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
User | Count |
---|---|
95 | |
11 | |
11 | |
10 | |
9 | |
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.