cancel
Showing results for 
Search instead for 
Did you mean: 

All table and their sizes in a one node in distributed environment

Former Member
0 Kudos

Hi All,

We have a strange issue.

We have 7+1 nodes setup. With each node having 1 TB of memory.

Now when we backup HANA master node is about 561GB. whereas when i check the memory allocation we get the below statistics.

row store tables : 181 GB

SYSTEM : 132 GB

Column store : 25 GB

caches : 10 GB

Monitoring and statistical data : 8 GB

Statement Execution and intermediate results : 3 GB

Where is the missing data.

How do we identify as to why we have 561 GB in the backup file size but whereas we can see only 360 GB.

How can we troubleshoot further?

Can anyone please help.

Accepted Solutions (0)

Answers (3)

Answers (3)

Former Member
0 Kudos

Sorry,

could you please clarify where the discrepancy is?

What are your sizes for in-memory part, data volume, and backup files?

The reason I am asking that my first thought was that your data volume grew at some point but then it was reduced and the free space was not reclaimed (unlike with log area, you have to reclaim it manually). So in that case we would have in-memory<data volume size (which now has the payload plus the free unused space). But your backup file should theoretically contain ONLY the payload of the data volume.

Former Member
0 Kudos

Liz,

Thats not what happened actually.

The thing is that master node indexserver data volume size is around 600 GB.

In memory part if we calculate with the RS and CS tables and indexes around 250 GB.

Nobody knew where the rest of the part is going into the data volumes. When checked in the data volumes there were no free pages so even the part of the free space was ruled out.

Then SAP development came back saying that one of the table LOB was in master node even though HANA studio table runtime definition said that it is on the slave node.

So we now know what is the cause for the increment in the data volume size.

Hope this helps.

Thanks,

Former Member
0 Kudos

Can you run SQL: "HANA_Memory_Overview" (SAP Note 1969700) and provide results?

Former Member
0 Kudos

Hi Martin,

Please find the results. I just dont understand the missing part.

License memory
  limit
8192
License usage46362642 (2015/05/01-2015/05/31)
4636 (2015/06/01-2015/06/30)
2593 (2015/07/01-2015/07/02)
Physical memory80781010 (master1)
1010 (slave1)
1010 (slave2)
1010 (slave3)
1010 (slave4)
1010 (slave5)
1010 (slave6)
1010 (standby)
Global allocation limit7798975 (master1)
975 (slave1)
975 (slave2)
975 (slave3)
975 (slave4)
975 (slave5)
975 (slave6)
975 (standby)
HANA instance memory (allocated)2607565 (master1)
326 (slave1)
238 (slave2)
392 (slave3)
367 (slave4)
328 (slave5)
339 (slave6)
51 (standby)
HANA instance memory (peak used)2528680 (master1)
367 (slave1)
210 (slave2)
381 (slave3)
287 (slave4)
449 (slave5)
396 (slave6)
9 (standby)
HANA instance memory (used)1692338 (master1)
230 (slave1)
150 (slave2)
270 (slave3)
231 (slave4)
244 (slave5)
221 (slave6)
9 (standby)
HANA shared memory134130 (master1)
1 (slave1)
1 (slave2)
1 (slave3)
1 (slave4)
1 (slave5)
1 (slave6)
0 (standby)
HANA heap memory (used)1489198 (master1)49 (Pool/PersistenceManager/PersistentSpace(0)/DefaultLPA/Page)
47 (Pool/RowEngine/CpbTree)
13 (Pool/PersistenceManager/ContainerFileIDMapping)
11 (Pool/NameIdMapping/RoDict)
11 (Pool/RowEngine/SQLPlan)
10 (Pool/RowEngine/QueryExecution)
8 (Pool/Statistics)
5 (Pool/PersistenceManager/PersistentSpace(0)/RowStoreLPA)
3 (Pool/AttributeEngine-IndexVector-Single)
3
  (AllocateOnlyAllocator-unlimited/FLA-UL<3145728,1>/MemoryMapLevel2Blocks)
221 (slave1)69 (Pool/AttributeEngine-IndexVector-Single)
37 (Pool/malloc/libhdbcs.so)
24 (Pool/NameIdMapping/RoDict)
19 (Pool/AttributeEngine-IndexVector-Sp-Indirect)
18 (Pool/PersistenceManager/PersistentSpace(0)/DefaultLPA/Page)
12 (Pool/AttributeEngine/idattribute)
6 (Pool/AttributeEngine)
4 (Pool/RowEngine/SQLPlan)
4 (Pool/AttributeEngine-IndexVector-SingleIndex)
3 (Pool/AttributeEngine-IndexVector-Sp-Sparse)
141 (slave2)52 (Pool/AttributeEngine-IndexVector-Single)
23 (Pool/malloc/libhdbcs.so)
13 (Pool/NameIdMapping/RoDict)
10 (Pool/AttributeEngine-IndexVector-Sp-Indirect)
10 (Pool/PersistenceManager/PersistentSpace(0)/DefaultLPA/Page)
8 (Pool/AttributeEngine/idattribute)
5 (Pool/AttributeEngine)
3 (Pool/AttributeEngine-IndexVector-Sp-Sparse)
2 (Pool/RowEngine/SQLPlan)
2 (Pool/AttributeEngine-IndexVector-SingleIndex)
261 (slave3)77 (Pool/AttributeEngine-IndexVector-Single)
43 (Pool/malloc/libhdbcs.so)
31 (Pool/NameIdMapping/RoDict)
25 (Pool/AttributeEngine-IndexVector-Sp-Indirect)
21 (Pool/PersistenceManager/PersistentSpace(0)/DefaultLPA/Page)
15 (Pool/AttributeEngine/idattribute)
8 (Pool/AttributeEngine)
5 (Pool/AttributeEngine-IndexVector-SingleIndex)
4 (Pool/AttributeEngine-IndexVector-Sp-Rle)
4 (Pool/RowEngine/SQLPlan)
222 (slave4)66 (Pool/AttributeEngine-IndexVector-Single)
40 (Pool/malloc/libhdbcs.so)
21 (Pool/AttributeEngine-IndexVector-Sp-Indirect)
20 (Pool/NameIdMapping/RoDict)
16 (Pool/PersistenceManager/PersistentSpace(0)/DefaultLPA/Page)
14 (Pool/AttributeEngine/idattribute)
6 (Pool/AttributeEngine)
5 (Pool/RowEngine/Session)
4 (Pool/AttributeEngine-IndexVector-Sp-Sparse)
4 (Pool/RowEngine/SQLPlan)
235 (slave5)86 (Pool/AttributeEngine-IndexVector-Single)
39 (Pool/malloc/libhdbcs.so)
22 (Pool/NameIdMapping/RoDict)
20 (Pool/PersistenceManager/PersistentSpace(0)/DefaultLPA/Page)
16 (Pool/AttributeEngine-IndexVector-Sp-Indirect)
11 (Pool/AttributeEngine/idattribute)
6 (Pool/AttributeEngine)
4 (Pool/RowEngine/SQLPlan)
3 (Pool/AttributeEngine-IndexVector-SingleIndex)
3 (Pool/AttributeEngine-IndexVector-Sp-Sparse)
212 (slave6)71 (Pool/AttributeEngine-IndexVector-Single)
33 (Pool/malloc/libhdbcs.so)
22 (Pool/NameIdMapping/RoDict)
18 (Pool/PersistenceManager/PersistentSpace(0)/DefaultLPA/Page)
16 (Pool/AttributeEngine-IndexVector-Sp-Indirect)
15 (Pool/AttributeEngine/idattribute)
7 (Pool/AttributeEngine)
5 (Pool/AttributeEngine-IndexVector-Sp-Sparse)
4 (Pool/AttributeEngine-IndexVector-SingleIndex)
3 (Pool/RowEngine/SQLPlan)
Column store size108824 (master1)4 (BALDAT)
2 (RSSTATMANREQMAP)
2 (RSTHIERNODE)
2 (RSREQMAP_SAVE2)
1 (WBCROSSGT)
1 (UJU_AUDACTDET)
1 (RSDDSTATDTP)
1 (/1CPMB/TORYZAD)
1 (REPOSRC)
182 (slave1)20 (/BIC/FZGL_C101)
16 (/BIC/AZRE_M10200)
7 (/BIC/FZGL_C161)
4 (/BIC/FZGLM0103)
4 (/BIC/FZGLM0104)
3 (/BIC/AZAG_D30300)
3 (/BIC/AZAG_D30500)
3 (/BIC/AZAG_D30600)
3 (/BIC/FZGUD0308)
3 (/BIC/AZFI_D6000)
119 (slave2)20 (/BIC/FZGL_C101)
16 (/BIC/AZRE_M10200)
7 (/BIC/FZGL_C161)
5 (/BIC/FZGUD0307)
4 (/BIC/FZGLM0103)
4 (/BIC/FZGLM0104)
3 (/BIC/AZAG_D30300)
3 (/BIC/AZAG_D30500)
3 (/BIC/AZAG_D30600)
3 (/BIC/FZGUD0308)
218 (slave3)20 (/BIC/FZGL_C101)
16 (/BIC/AZRE_M10200)
7 (/BIC/FZGL_C161)
4 (/BIC/FZGLM0103)
4 (/BIC/FZGLM0104)
3 (/BIC/AZAG_D30300)
3 (/BIC/AZAG_D30500)
3 (/BIC/AZAG_D30600)
3 (/BI0/IPROFIT_CTR)
3 (/BIC/FZGUD0407)
181 (slave4)20 (/BIC/FZGL_C101)
16 (/BIC/AZRE_M10200)
15 (HOST_SQL_PLAN_CACHE_BASE)
7 (/BIC/FZGL_C161)
5 (/BIC/FZGUD0307)
4 (/BIC/FZGLM0103)
4 (/BIC/FZGLM0104)
3 (/BIC/AZAG_D30300)
3 (/BIC/AZAG_D30500)
3 (/BIC/AZAG_D30600)
184 (slave5)20 (/BIC/FZGL_C101)
17 (/BIC/AZRE_M10200)
7 (/BIC/FZGL_C161)
4 (/BIC/FZGLM0103)
4 (/BIC/FZGLM0104)
3 (/BIC/AZAG_D30300)
3 (/BIC/AZAG_D30500)
3 (/BIC/AZAG_D30600)
3 (/BIC/FZGUD0308)
3 (/BIC/AZFI_D6000)
179 (slave6)20 (/BIC/FZGL_C101)
16 (/BIC/AZRE_M10200)
7 (/BIC/FZGL_C161)
5 (/BIC/FZGUD0307)
4 (/BIC/FZGLM0103)
4 (/BI0/STCTSTEPUID)
4 (/BIC/FZGLM0104)
3 (/BIC/AZAG_D30300)
3 (/BIC/AZAG_D30500)
3 (/BIC/AZAG_D30600)
Row store size177163 (master1)33 (RSBMREQ_DTP)
32 (RSBATCHDATA)
18 (RSBMLOGPAR_DTP)
14 (RSBMONMESS_DTP)
11 (RSSTATMANREQMDEL)
7 (RSBATCHCTRL)
6 (RSMONMESS)
3 (RSBKDATAPAKID)
2 (RSDDSTATEVDATA)
2 (D010TAB)
Disk size14421442 (global)170 (HOST_SQL_PLAN_CACHE_BASE)
111 (/BIC/FZGL_C101)
98 (/BIC/AZRE_M10200)
35 (/BIC/FZGL_C161)
26 (RSBATCHDATA)
25 (RSBMREQ_DTP)
23 (UJF_DOC_CLUSTER)
22 (/BIC/FZGLM0104)
22 (/BIC/FZGLM0103)
21 (/BIC/AZAG_D30300)
Former Member
0 Kudos

This output confirms that the disk size is larger. There can be multiple reasons like hybrid LOBs, delayed persistence garbage collection or snapshots. You can check these aspects based on SAP Note 1999997 ("What are typical reasons for significant size differences in memory vs. on disk?").

Former Member
0 Kudos

Thanks so much Martin.

Danke Martin.

Thats a lot of points to check martin .


 

Reason


 

 

Symptom


 

 

Details


 

 

No logging tables


 

 

Memory > disk


 

 

Tables created with the NO LOGGING option are not
  persisted to disk. See SAP Note 2000003
  ("What kind of temporary and non-persisted tables can be created with
  SAP HANA?") for more information.


 

 

Temporary tables


 

 

Memory > disk


 

 

Tables created with the TEMPORARY option are not persisted
  to disk. See SAP Note 2000003
  ("What kind of temporary and non-persisted tables can be created with
  SAP HANA?") for more information.


 

 

Single column and row store indexes


 

 

Memory > disk


 

 

Single column indexes and row store indexes aren't
  persisted to disk. See SAP Note 2160391
  ("Are indexes persisted to disk?") for more information.


 

 

Logically moved tables


 

 

Memory > disk


 

 

If tables are moved logically, their disk footprint can be
  larger than the size in memory. See SAP Note 1994408
  for more information.


 

 

Hybrid LOBs


 

 

Disk > memory


 

 

Large hybrid LOB values (SAP Note 1994962)
  are not loaded into memory, so the disk size of tables is larger than the
  memory size.


 

 

Partially loaded tables


 

 

Disk > memory


 

 

If columns of a table are only partially loaded into the
  memory, the disk size is larger than the current memory size. You can use SQL:
  "HANA_Tables_LargestTables"
(SAP Note 1969700)
  to check disk size, potential maximum memory size and current memory size.


 

 

Data fragmentation


 

 

Disk > memory


 

 

A fragmented data area can significantly increase the disk
  requirements. You can use SQL:
  "HANA_Disks_Fragmentation"
(SAP Note 1969700)
  to check for the amount of fragmentation on disk side.


 

 

Database snapshots


 

 

Disk > memory


 

 

Database snapshots can result in significantly increased
  disk space requirements, because the before image of modified blocks needs to
  be stored in addition to the normal data blocks. Therefore you should make
  sure that old snapshots are deleted. SQL:
  "HANA_IO_Snapshots"
(SAP Note 1969700)
  can be used to check for old snapshots. See SAP Note 2100009
  for more information related to savepoints and snapshots.


 

 

Gargabe collection blocked


 

 

Disk > memory


 

 

Blocked persistence garbage collection can result in a
  significant increase of disk space. SAP Note 2169283
  describes how to analyze issues with garbage collection.


 

 

Large DELETE / TRUNCATE


 

 

Disk > memory


 

 

As described in SAP Note 2014987
  the disk size can remain at a high level after having performed a large
  DELETE or TRUNCATE operation. Proceed as described in SAP Note 2014987
  in order to reduce the allocated disk size.


 
Former Member
0 Kudos

Martin,

There are no discrepancies between the table sizes in memory and sizes on the disk.

What is that extra space that is being occupied is what we wanted to know.

Former Member
0 Kudos

Martin,

How can we get to know this?

Suppose we have 600 GB allocated on the disk for the master indexserver. What is that 600 GB being stored on the disk.

Want to know the details.

Former Member
0 Kudos

If the table sizes are identical, then it is usually fragmentation. Can you run the Mini Checks (SAP Note 1999993) and provide the results?

Former Member
0 Kudos

Hi Martin please find the results below.

   ;CHID ;DESCRIPTION                                  
  ;HOST       ;VALUE                               
  ;EXPECTED_VALUE;C;SAP_NOTE
1
  ;**** ;GENERAL                                      
  ;           ;                                      ;              ; ;       
2
  ;     ;                                             
  ;           ;                                      ;              ; ;       
3
  ;   10;Analysis date                                  ;           ;2015/07/03 11:10:23 (EDT)             ;              ; ;       
4
  ;   11;Database name                                  ;           ;UP1                                   ;              ; ;       
5
  ;   12;Revision level                                 ;           ;82.00                                 ;>=
  82.00      ; ; 2021789
6
  ;  110;Everything started                             ;           ;yes                                   ;yes           ; ;       
7
  ;  111;Host startup time
  variation (s)                ;           ;1                                     ;<=
  600        ; ;       
8
  ;  115;Service startup time
  variation (s)           
  ;master;50                                    ;<=
  600        ; ;       
9
  ;     ;                                             
  ;           ;                                      ;              ; ;       
10 ;**** ;OPERATING SYSTEM                               ;           ;                                      ;              ; ;       
11 ;   
  ;                                             
  ;           ;                                      ;              ; ;       
12 ;
  210;Minimum CPU rate (MHz)                         ;master;2500                                  ;>= 2000       ; ; 1890444
13 ;
  211;Hosts with varying CPU rates                   ;           ;no                                    ;no            ; ; 1890444
14 ;
  220;Current CPU utilization (%)                    ;master;9                                     ;<=
  80         ; ; 2100040
15 ;
  221;Time since CPU utilization > 95 % (h)          ;           ;never                                 ;>=
  12.00      ; ; 2100040
16 ;
  230;Current memory utilization (%)                 ;master;40                                    ;<= 90         ; ; 1999997
17 ;
  231;Time since memory utilization > 95 % (h)       ;           ;never                                 ;>=
  12.00      ; ; 1999997
18 ;
  235;Hosts with varying physical memory size        ;           ;no                                    ;no            ; ; 1999997
19 ;
  240;Current swap utilization (GB)                  ;master;0                                     ;0             ; ; 1999997
20 ;
  241;Time since swap utilization > 1 GB (h)         ;           ;never                                 ;>=
  480        ; ; 1999997
21 ;
  245;Swap space size (GB)                         
  ;master;255.98                                ;<= 25.00      ;X; 1999997
22 ;
  250;Max. used disk size (%)                        ;           ;84                                    ;<=
  90         ; ; 1870858
23 ;   
  ;                                             
  ;master;84                                    ;<= 90         ; ; 1870858
24 ;
  260;Open files limit (OS)                        
  ;master;1048576                               ;>= 100000     ; ; 1771873
25 ;   
  ;                                             
  ;           ;                                      ;              ; ;       
26 ;**** ;DISK I/O                                       ;           ;                                      ;              ; ;       
27 ;   
  ;                                             
  ;           ;                                      ;              ; ;       
28 ;
  310;I/O read throughput data min. (MB/s, last day) ;master;59                                    ;>=
  20         ; ; 1999930
29 ;
  311;I/O read throughput data avg. (MB/s, last day) ;master;295                                   ;>=
  100        ; ; 1999930
30 ;
  315;I/O write throughput data min. (MB/s, last day);master;294                                   ;>=
  20         ; ; 1999930
31 ;
  316;I/O write throughput data avg. (MB/s, last day);master;348                                   ;>=
  100        ; ; 1999930
32 ;
  320;I/O read throughput log min. (MB/s, last day)  ;master;1060                                  ;>= 30         ; ; 1999930
33 ;
  321;I/O read throughput log avg. (MB/s, last day)  ;master;1218                                  ;>= 100        ; ; 1999930
34 ;
  325;I/O write throughput log min. (MB/s, last day) ;master;46                                    ;>=
  10         ; ; 1999930
35 ;
  326;I/O write throughput log avg. (MB/s, last day) ;master;147                                   ;>=
  20         ; ; 1999930
36 ;
  330;Max. trigger read ratio (data)                 ;master;0.24                                  ;<= 0.50       ; ; 1930979
37 ;
  331;Max. trigger write ratio (data, log)           ;master;0.07                                  ;<= 0.50       ; ; 1930979
38 ;
  340;Log switch wait count ratio (%)                ;master;0                                     ;<= 1          ; ;       
39 ;
  341;Log switch race count ratio (%)                ;master;0                                     ;<= 1          ; ;       
40 ;
  350;Crit. savepoint phases > 10 s (last day)       ;master;0                                     ;0             ; ; 2100009
41 ;
  351;Crit. savepoint phase avg. (s, last day)       ;master;0.11                                  ;<= 2.00       ; ; 2100009
42 ;
  352;Crit. savepoint phase max. (s, last day)       ;master;0.95                                  ;<=
  10.00      ; ; 2100009
43 ;
  355;Time since last savepoint (s)                  ;master;145                                   ;<= 900        ; ; 2100009
44 ;
  357;Savepoint write throughput (MB/s)              ;master;150                                   ;>= 100        ; ; 2100009
45 ;
  358;Savepoints taking longer than 900 s (last day) ;           ;0                                     ;0             ; ; 2100009
46 ;
  360;Number of failed I/O reads                     ;master;0                                     ;0             ; ;       
47 ;
  361;Number of failed I/O writes                    ;master;0                                     ;0             ; ;       
48 ;
  370;Unused space in data files (%)                 ;master;6                                     ;<=
  50         ; ; 1870858
49 ;
  380;Age of oldest backup snapshot (days)           ;           ;n/a                                   ;<=
  30.00      ; ; 2100009
50 ;   
  ;                                             
  ;           ;                                      ;              ; ;       
51 ;**** ;MEMORY                                       
  ;           ;                                      ;              ; ;       
52 ;   
  ;                                             
  ;           ;                                      ;              ; ;       
53 ;
  410;Current allocation limit used (%)              ;master;36                                    ;<= 80         ; ; 1999997
54 ;
  411;Current allocation limit used by tables (%)    ;master;21                                    ;<= 50         ; ; 1999997
55 ;
  413;Time since allocation limit used > 80 % (h)    ;           ;never                                 ;>=
  24         ; ; 1999997
56 ;
  415;Curr. max. service allocation limit used (%)   ;master;36                                    ;<= 80         ; ; 1999997
57 ;
  417;Time since service alloc. limit used > 80 % (h);           ;never                                 ;>= 24         ; ; 1999997
58 ;
  420;Heap areas currently larger than 50 GB         ;           ;none                                  ;none          ; ; 1999997
59 ;
  421;Heap areas larger than 100 GB (last day)       ;           ;none                                  ;none          ; ; 1999997
60 ;
  422;Heap areas larger than 200 GB (history)        ;           ;none                                  ;none          ; ; 1999997
61 ;
  425;Pool/RowEngine/CpbTree leak size (GB)          ;           ;2.96                                  ;<=
  5.00       ; ; 1999997
62 ;
  430;Number of column store unloads (last day)      ;           ;0                                     ;0             ; ; 1977207
63 ;
  431;Time since last column store unload (days)     ;           ;never                                 ;>=
  5.00       ; ; 1977207
64 ;
  440;Shared memory utilization of nameserver (%)    ;master;52                                    ;<= 70         ; ; 1977101
65 ;
  450;Tables with memory LOBs > 2 GB                 ;           ;0                                     ;0             ; ; 1994962
66 ;   
  ;                                             
  ;           ;                                      ;              ; ;       
67 ;**** ;TABLES                                       
  ;           ;                                      ;              ; ;       
68 ;   
  ;                                             
  ;           ;                                      ;              ; ;       
69 ;
  510;Tables with > 100 partitions                   ;           ;0                                     ;0             ; ; 2044468
70 ;
  520;Tables / partitions > 1 billion rows           ;           ;none                                  ;none          ; ; 1921694
71 ;
  521;Table histories > 1 billion rows               ;           ;none                                  ;none          ; ; 1921694
72 ;
  530;Row store size (GB)                            ;master;179                                   ;<= 300        ; ; 2050579
73 ;
  535;Row store (> 10 GB) fragmentation (%)          ;master;5                                     ;<=
  30         ; ; 1813245
74 ;
  538;Delta merges > 200 s (last day)                ;master;0                                     ;0             ; ; 2057046
75 ;
  540;Failing delta merges (info messages, last day) ;master;0                                     ;<=
  1000       ; ; 2057046
76 ;
  541;Failing delta merges (error messages, last day);master;0                                     ;0             ; ; 2057046
77 ;
  542;Tables with delta storage > 5 GB               ;           ;none                                  ;none          ; ; 1977314
78 ;
  543;Tables with > 1 Mio. and > 90 % of delta rows  ;         
  ;/B28/HXGDNJJU (1088416 rows, 93 %)  
  ;none          ;X; 1909742
79 ;   
  ;                                             
  ;           ;/B28/TXGD15MS
  (1864908 rows, 99 %)    ;none          ;X; 1909742
80 ;   
  ;                                             
  ;           ;/BIC/B0000185001
  (3106632 rows, 100 %);none          ;X;
  1909742
81 ;
  544;Sum of delta size of small tables (GB)         ;           ;2.85                                  ;<=
  1.00       ;X; 2057046
82 ;
  545;Non BW tables with disabled auto merge         ;           ;0                                     ;0             ; ; 1977314
83 ;
  546;Tables with disabled persistent merge          ;           ;0                                     ;0             ; ; 2057046
84 ;
  550;Row store tables with ST_POINT columns         ;           ;0                                     ;0             ; ; 2038897
85 ;
  555;Tables / partitions moved only logically       ;           ;0                                     ;0             ; ; 2066313
86 ;
  560;Tables > 10 Mio. rows not compressed           ;           ;30                                    ;0             ;X; 2105761
87 ;
  563;Indexes on large SPARSE / PREFIXED columns     ;           ;2                                     ;0             ;X; 2112604
88 ;
  565;Tables > 10 Mio. rows and > 200 % UDIV rows    ;           ;10                                    ;0             ;X; 2112604
89 ;
  570;Number of temporary tables                     ;           ;35371                                 ;<=
  100000     ; ;       
90 ;
  580;Tables assigned to wrong service               ;           ;0                                     ;0             ; ;       
91 ;   
  ;                                             
  ;           ;                                      ;              ; ;       
92 ;**** ;TRACES, DUMPS AND LOGS                         ;           ;                                      ;              ; ;       
93 ;   
  ;                                             
  ;           ;                                      ;              ; ;       
94 ;
  610;Kernel profiler active                         ;           ;no                                    ;no            ; ; 1804811
95 ;
  612;Performance trace enabled                      ;           ;no                                    ;no            ; ; 1787489
96 ;
  614;SQL trace enabled                              ;           ;no                                    ;no            ; ; 2031647
97 ;
  616;Indexserver python trace enabled               ;           ;no                                    ;no            ; ; 1935113
98 ;
  617;Executor python trace enabled                  ;           ;no                                    ;no            ; ; 2020877
99 ;
  618;SQL client network trace enabled               ;           ;no                                    ;no            ; ; 2081065
100;
  620;Minimum expensive SQL trace threshold (ms)     ;           ;1000                                  ;>=
  1000       ; ; 1982207
101;
  630;Existence of database trace settings           ;           ;no                                    ;no            ; ; 2119087
102;
  635;Existence of user specific trace settings      ;           ;no                                    ;no            ; ; 2119087
103;
  637;Existence of end-to-end trace settings         ;           ;no                                    ;no            ; ; 2119087
104;
  640;Number of tracefiles (total)                   ;master;70                                    ;<=
  150        ; ; 1977162
105;
  641;Number of tracefiles (last day)                ;master;22                                    ;<= 30         ; ; 1977162
106;
  642;Size of tracefiles (GB, total)                 ;master;0.27                                  ;<= 6.00       ; ; 1977162
107;
  643;Size of tracefiles (GB, last day)              ;master;0.22                                  ;<= 1.00       ; ; 1977162
108;
  644;Size of largest trace file (MB)                ;master;185.63                                ;<= 50.00      ;X; 1977162
109;
  645;Number of OOM tracefiles (last day)            ;master;0                                     ;0             ; ; 1999997
110;
  646;Number of statement OOM tracefiles (last day)  ;master;0                                     ;0             ; ; 1999997
111;
  650;Number of crash dumps (last day)               ;master;0                                     ;0             ; ; 1977218
112;
  652;Number of page dumps (last day)                ;master;0                                     ;0             ; ; 1977242
113;
  655;Number of RTE dumps (last day)                 ;master;0                                     ;0             ; ;       
114;
  658;Time since last dump (days)                    ;           ;1.28                                  ;>=
  7.00       ;X; 2119087
115;
  660;CPU time measurement enabled                   ;           ;no                                    ;no            ; ; 2100040
116;   
  ;                                             
  ;           ;                                      ;              ; ;       
117;**** ;STATISTICS SERVER                              ;           ;                                      ;              ; ;       
118;   
  ;                                             
  ;           ;                                      ;              ; ;       
119;
  710;Open alerts (high priority)                    ;           ;0                                     ;0             ; ; 2053330
120;
  711;Open alerts (error state)                      ;           ;0                                     ;0             ; ; 2053330
121;
  715;Number of actions not executed as expected     ;           ;0                                     ;0             ; ; 2147247
122;
  717;Number of disabled actions                     ;           ;1                                     ;0             ;X; 2113228
123;
  718;Number of relevant inactive actions            ;           ;1                                     ;0             ;X; 2147247
124;
  720;Events not acknowledged since >= 1800 s        ;master;849                                   ;0             ;X; 2126236
125;
  730;Pending e-mails older than 3 days              ;           ;699877                                ;0             ;X; 2133799
126;
  740;Time since statistics server run (s)           ;           ;6                                     ;<=
  3600       ; ; 2147247
127;
  745;Total size of statistics server tables (GB)    ;           ;19.27                                 ;<=
  10.00      ;X; 2147247
128;
  746;History tables with primary key                ;           ;33                                    ;0             ;X; 2143679
129;
  747;Number of zero entries in HOST_SQL_PLAN_CACHE  ;         
  ;17702155                            
  ;<= 1000000    ;X; 2084747
130;
  748;History of M_CS_UNLOADS collected              ;           ;yes                                   ;no            ;X; 2147247
131;
  749;Alerts older than 42 days                      ;           ;991                                   ;<=
  10000      ; ; 2170779
132;
  750;Stat. server tables with retention < 42 days   ;         
  ;5                                   
  ;0             ;X; 2147247
133;
  755;Embedded statistics server used                ;           ;yes                                   ;yes           ; ; 2092033
134;
  760;Status of embedded statistics server migration ;           ;done (okay)                           ;done (okay)   ; ; 2092033
135;
  770;Number of stat. server tables not on master    ;           ;50                                    ;0             ;X; 2091256
136;   
  ;                                             
  ;           ;                                      ;              ; ;       
137;**** ;TRANSACTIONS AND THREADS                       ;           ;                                      ;              ; ;       
138;   
  ;                                             
  ;           ;                                      ;              ; ;       
139;
  810;MVCC versions in row store                     ;master;4135405                               ;<= 3000000    ;X; 2169283
140;
  812;Max. versions per record in row store          ;master;398                                   ;<=
  30000      ; ; 2169283
141;
  815;Age of transaction blocking row store MVCC (s) ;           ;175                                   ;<=
  10800      ; ; 2169283
142;
  830;Active commit ID range                         ;master;38399                                 ;<=
  3000000    ; ; 2169283
143;
  840;Active update transaction ID range             ;master;7271                                  ;<=
  3000000    ; ; 2169283
144;
  841;Oldest active update transaction (s)           ;master;3                                     ;<=
  10800      ; ; 2169283
145;
  850;Persistence garbage collection history count   ;master;1427                                  ;<=
  3000000    ; ; 2169283
146;   
  ;                                             
  ;           ;                                      ;              ; ;       
147;**** ;BACKUP                                       
  ;           ;                                      ;              ; ;       
148;   
  ;                                             
  ;           ;                                      ;              ; ;       
149;
  910;Age of last data backup (days)                 ;           ;0.73                                  ;<=
  1.20       ; ; 2091951
150;
  911;Age of last data backup error (days)           ;           ;24.72                                 ;>=
  1.20       ; ; 2091951
151;
  915;Min. data backup throughput (GB/h, last week)  ;         
  ;1185.93                             
  ;>= 200.00     ; ; 1999930
152;
  916;Avg. data backup throughput (GB/h, last week)  ;         
  ;1359.32                             
  ;>= 300.00     ; ; 1999930
153;
  918;Maximum data backup duration (h, last week)    ;           ;1.38                                  ;<=
  4.00       ; ; 1999930
154;
  920;Age of last log backup (hours)                 ;           ;0.02                                  ;<=
  1.00       ; ; 2091951
155;
  921;Age of last log backup error (days)            ;           ;53.06                                 ;>=
  1.00       ; ; 2091951
156;
  922;Maximum log backup duration (s, last day)      ;           ;6                                     ;<=
  300        ; ; 2063454
157;
  930;Automatic log backup                           ;           ;yes                                   ;yes           ; ; 1645183
158;
  935;Database log mode                              ;           ;normal                                ;normal        ; ; 1645183
159;
  940;Size of backup catalog (MB)                    ;           ;40.16                                 ;<=
  50.00      ; ;       
160;
  942;Catalog size share (last day, %)               ;           ;2.27                                  ;<=
  3.00       ; ;       
161;
  945;Age of oldest backup in catalog (days)         ;           ;110                                   ;<=
  100        ;X;       
162;
  950;Log segments not free for reuse                ;           ;11                                    ;<=
  100        ; ;       
163;   
  ;                                             
  ;           ;                                      ;              ; ;       
164;**** ;LOCKS                                        
  ;           ;                                      ;              ; ;       
165;   
  ;                                             
  ;           ;                                      ;              ; ;       
166; 1010;Age of oldest active trans.
  lock wait (s)      ;           ;0                                     ;<=
  60         ; ; 1999998
167; 1011;Trans. lock wait durations
  > 600 s (last day)  ;           ;0                                     ;0             ; ; 1999998
168; 1020;Threads currently waiting for
  locks            ;           ;0                                     ;<=
  10         ; ; 1999998
169; 1021;Maximum threads waiting for
  locks (last day)   ;           ;16                                    ;<=
  100        ; ; 1999998
170;   
  ;                                             
  ;           ;                                      ;              ; ;       
171;**** ;SQL                                          
  ;           ;                                      ;              ; ;       
172;   
  ;                                             
  ;           ;                                      ;              ; ;       
173; 1110;SQL using in average > 1
  connection (last day) ;         
  ;none                                
  ;none          ; ; 2000002
174; 1112;SQL using in average > 1
  thread (last hour)    ;           ;none                                  ;none          ; ; 2000002
175; 1113;SQL using in average > 1
  thread (last day)     ;           ;none                                  ;none          ; ; 2000002
176; 1115;Longest running current SQL
  statement (s)      ;           ;0                                     ;<=
  600        ; ; 2000002
177; 1120;Exp. stmt. trace: SQL running
  > 1 h (last day) ;           ;0                                     ;0             ; ; 2000002
178; 1130;SQL cache evictions / h                        ;master;675                                   ;<=
  1000       ; ; 2124112
179; 1131;SQL cache hit ratio of
  indexserver (%)       
  ;master;99.44                                 ;>=
  90.00      ; ; 2124112
180; 1132;SQL cache size per SQL
  connection (MB)       
  ;master;12.54                                 ;>=
  15.00      ;X; 2124112
181; 1140;SQL preparation runtime share
  (%)              ;master;0.46                                  ;<=
  5.00       ; ; 2124112
182; 1142;Table(s) using > 10 % of
  SQL cache             ;master;1                                     ;0             ;X; 2124112
183; 1145;SQL cache used by IN LISTs
  >= 100 elements (%) ;         
  ;13.23                               
  ;<= 10.00      ;X; 2124112
184;   
  ;                                             
  ;           ;                                      ;              ; ;       
185;**** ;APPLICATION                                    ;           ;                                      ;              ; ;       
186;   
  ;                                             
  ;           ;                                      ;              ; ;       
187; 1220;QCM conversion tables                          ;           ;0                                     ;0             ; ;    9385
188; 1250;Physical BPC tables                            ;           ;0                                     ;0             ; ; 1835219
189; 1260;QIWKTAB update rate (updates /
  h)              ;           ;0                                     ;<=
  10000      ; ; 2000002
190;   
  ;                                             
  ;           ;                                      ;              ; ;       
191;**** ;SECURITY                                       ;           ;                                      ;              ; ;       
192;   
  ;                                             
  ;           ;                                      ;              ; ;       
193; 1310;Secure store (SSFS)
  status                   
  ;master;available                             ;available     ; ; 1977221
194; 1330;Number of users with
  expiration date           ;           ;0                                     ;0             ; ;       
195; 1335;Number of SAP users with
  password expiration   ;           ;0                                     ;0             ; ; 1702224
196; 1340;CATALOG READ privilege granted
  to current user ;           ;yes                                   ;yes           ; ; 1640741
197; 1342;Users with
  SAP_INTERNAL_HANA_SUPPORT role    
  ;           ;0                                     ;0             ; ; 2081857
198; 1345;DATA ADMIN privilege granted
  to users or roles ;           ;no                                    ;no            ; ;  863362
199; 1350;SQL trace including results
  configured         ;           ;no                                    ;no            ; ;  863362
200; 1360;Size of audit log table
  (GB)                   ;           ;0.07                                  ;<=
  10.00      ; ;  706478
201;   
  ;                                             
  ;           ;                                      ;              ; ;       
202;**** ;LICENSE                                      
  ;           ;                                      ;              ; ;       
203;   
  ;                                             
  ;           ;                                      ;              ; ;       
204; 1410;License usage (%)                              ;           ;57                                    ;<= 95         ; ; 1704499
205; 1415;License expiration (days)                      ;           ;never                                 ;>= 100        ; ; 1644792
206; 1420;Permanent license                              ;           ;yes                                   ;yes           ; ; 1644792
207;   
  ;                                             
  ;           ;                                      ;              ; ;       
208;**** ;NETWORK                                      
  ;           ;                                      ;              ; ;       
209;   
  ;                                             
  ;           ;                                      ;              ; ;       
210; 1510;Avg. intra node send
  throughput (MB/s)       
  ;master;555                                   ;>=
  120        ; ; 2081065
211; 1512;Avg. inter node send
  throughput (MB/s)       
  ;master;611                                   ;>=
  80         ; ; 2081065
212;   
  ;                                             
  ;           ;                                      ;              ; ;       
213;**** ;XS ENGINE                                      ;           ;                                      ;              ; ;       
214;   
  ;                                             
  ;           ;                                      ;              ; ;       
215; 1610;Log segment size of xsengine
  (MB)              ;master;200                                   ;>=
  64         ; ; 2062080
216;   
  ;                                             
  ;           ;                                      ;              ; ;       
217;**** ;SYSTEM REPLICATION                             ;           ;                                      ;              ; ;       
218;   
  ;                                             
  ;           ;                                      ;              ; ;       
219; 1810;Services with replication
  error                ;           ;0                                     ;0             ; ; 1999880
220; 1811;Services with unknown
  replication state        ;           ;0                                     ;0             ; ; 1999880
221; 1815;Current log shipping delay
  (s)                 ;           ;0                                     ;<=
  60         ; ; 1999880
222; 1820;Parameter deviations primary
  vs. secondary site;           ;0                                     ;0             ; ; 1999880
223; 1830;Age of oldest replication
  snapshot (h)         ;master;0.11                                  ;<=
  5.00       ; ; 1999880
224;   
  ;                                             
  ;           ;                                      ;              ; ;       
225;**** ;OBJECTS                                      
  ;           ;                                      ;              ; ;       
226;   
  ;                                             
  ;           ;                                      ;              ; ;       
227; 1910;Maximum object ID                              ;           ;2349475                               ;<=
  1000000000 ; ; 2155663
228;   
  ;                                             
  ;           ;                                      ;              ; ;       
229;**** ;BW                                           
  ;           ;                                      ;              ; ;       
230;   
  ;                                             
  ;           ;                                      ;              ; ;       
231; 2010;Empty TABLE_PLACEMENT table in
  BW scale-out    ;           ;no                                    ;no            ; ; 1908075
232; 2020;Number of partitioned SID
  tables               ;           ;0                                     ;0             ; ; 2044468
Former Member
0 Kudos

Hi Martin,

Good Day!

Have a question, how can i identify what all data is inside data file for the indexserver.

Former Member
0 Kudos

The result is incomplete.

Former Member
0 Kudos

Just use a filter for the indexserver port in your query or in the command from the SQL statement collection (SAP Note 1969700) that you are about to use.

Former Member
0 Kudos

Martin,

I did use

                                               /* Modification section */


'master' HOST,


' ' ONLY_POTENTIALLY_CRITICAL_RESULTS,



'CHECK' ORDER_BY                            /* HOST, CHECK */

Former Member
0 Kudos

Also in the query it is searching for the indexserver results only right.

SELECT


'STAT_SERVER_WRONG_HOST',


'',


COUNT(*))


FROM



WHERE


'_SYS_STATISTICS' AND



SELECT


':' || PORT


FROM



WHERE


'indexserver' AND


'master'

Former Member
0 Kudos

Martin,

The issue is like this:

select * from M_DATA_VOLUME_PAGE_STATISTICS  where host='master' and DATA_VOLUME_NAME like '%hdb%4%'

Here the page size class 4K is affecting the sizes.

As soon as the 4K blocks increase we have seen in the indexserver size.

Former Member
0 Kudos

I don't think that drawing conclusions from block sizes is a good start point. I would really at first look at overview information like the Mini Checks.

Former Member
0 Kudos

Ok Martin, any particular direction or properties to look for ?

Former Member
0 Kudos

The one's already mentioned like fragmentation or garbage collection issues.

Former Member
0 Kudos

Will check thoroughly stuff related to fragmentation and garbage collection.

Former Member
0 Kudos

Martin,

SAP suggested to check the output from some commands, which i dont want to paste it here since people will try them .

Former Member
0 Kudos

Martin, Finally we identified the table which was causing the problem.

The strange thing what SAP support told us is that the LOB columns are being stored on the master node thats the reason we couldnt identify.

From hana studio it shows that the table belongs to a different node. But the LOB columns were residing on the master node since the table got created on the master node but moved to a different node.

Former Member
0 Kudos

Hi,

Can you please share any queries that SAP Support may have shared to identify this?

Sunil

Former Member
0 Kudos

Sunil,

I wish i could share. But the thing is that they executed majority using hdbcons. Which they told us not to use without their supervision. So i think we shouldnt use it either. Some of them very delicate commands any wrong usage could actually damage the data volumes.

TomCenens
Active Contributor
0 Kudos

Check your estimated backup size by performing the the SQL statement in point 17 in 1642148 - FAQ: SAP HANA Database Backup & Recovery

Former Member
0 Kudos

Tom that gives the backup size estimator.

But the calculations dont match

RS tables + CS tables + indexex + System tables  is less than the backup size.

I dont understand the missing part .