== 현황 조회
hdbsql TMP=> select schema_name, table_name, column_name, NUMA_NODE_INDEXES from table_columns where table_name = 'CUSTOMER1TST' and column_name = 'C_ZIP';
| SCHEM | TABLE_NAME | COLUM | N |
| ----- | ------------ | ----- | - |
| BENCH | CUSTOMER1TST | C_ZIP | ? |
1 row selected (overall time 103.392 msec; server time 803 usec)
hdbsql TMP=> select schema_name, table_name, column_name, numa_node_index from m_cs_all_columns where table_name = 'CUSTOMER1TST' and column_name = 'C_ZIP';
| SCHEM | TABLE_NAME | COLUM | NUMA_NODE_INDEX |
| ----- | ------------ | ----- | ---------------- |
| BENCH | CUSTOMER1TST | C_ZIP | 1 |
1 row selected (overall time 59.715 msec; server time 17.871 msec)
== 기본 설정으로 Load 수행 (해당 컬럼만 로드 함)
hdbsql TMP=> load bench.customer1tst (c_zip);
0 rows affected (overall time 2762.742 msec; server time 2758.535 msec)
== Load된 현황 확인
hdbsql TMP=> select memory_size_in_total from m_cs_all_columns where table_name = 'CUSTOMER1TST' and column_name = 'C_ZIP';
| MEMORY_SIZE_IN_TOT |
| --------------------- |
| 735026564 | <<-- Load된 Size
1 row selected (overall time 59.714 msec; server time 17.687 msec)
tmpadm@server01:/usr/sap/TMP/HDB00> hdbcons "numa locate BENCH CUSTOMER1TST"
SAP HANA DB Management Client Console (type '\?' to get help for client commands)
Try to open connection to server process 'hdbindexserver' on system 'TMP', instance '00'
SAP HANA DB Management Server Console (type 'help' to get help for server commands)
Executable: hdbindexserver (PID: 355543)
[OK]
--
## Start command at: 2019-06-25 14:29:32.530
*******************************************************************************************
NUMA page location statistics of Table (Main Store)
*******************************************************************************************
Total number of NUMA nodes = 8
Table name = CUSTOMER1TST
Expected NUMA locations: [1]
Attribute IDs involved: [7, 217]
*******************************************************************************************
------------------------------------------------------------------------------------------
Node Id DataPageBytes DictionaryPageBytes IndexPageBytes MiscPageBytes
------------------------------------------------------------------------------------------
0 0 0 0 0
1 738281264 84248 0 1040 <<< 해당 Size만큼 Memory 할당 됨 (NUMA Node 1)
2 0 0 0 0
3 0 0 0 0
4 0 0 0 0
5 0 0 0 0
6 0 0 0 0
7 0 0 0 0
*******************************************************************************************
Total memory page bytes with unaccounted NUMA location = 0
*******************************************************************************************
------------------------------------------------------------------------------------------
DataPageBytes DictionaryPageBytes IndexPageBytes MiscPageBytes
------------------------------------------------------------------------------------------
0 0 0 0
*******************************************************************************************
NUMA page location statistics of Table (Delta Store)
*******************************************************************************************
Total number of NUMA nodes = 8
Table name = CUSTOMER1TST
Expected NUMA locations: [1]
Attribute IDs involved: [5, 7, 201, 202, 203, 204, 205, 206, 207, 208, 209, 210, 211, 212, 213, 214, 215, 216, 217, 218, 219, 220, 221, 222]
*******************************************************************************************
------------------------------------------------------------------------------------------
Node Id DataPageBytes DictionaryPageBytes IndexPageBytes MiscPageBytes
------------------------------------------------------------------------------------------
0 8192 0 0 0
1 0 768 0 0
2 0 0 0 0
3 0 0 0 0
4 0 0 0 0
5 0 0 0 0
6 0 0 0 0
7 0 0 0 0
*******************************************************************************************
Total memory page bytes with unaccounted NUMA location = 0
*******************************************************************************************
------------------------------------------------------------------------------------------
DataPageBytes DictionaryPageBytes IndexPageBytes MiscPageBytes
------------------------------------------------------------------------------------------
0 0 0 0
[OK]
## Finish command at: 2019-06-25 14:29:32.576 command took: 46.494 msec
--
[EXIT]
--
[BYE]
== unload 후 설정 변경
. 명령 : alter table bench.customer1tst alter (c_zip char(9) numa node ('3'));
. 적용 : Unload 후 Load 수행 후 적용 됨
hdbsql TMP=> unload bench.customer1tst;
0 rows affected (overall time 282.956 msec; server time 278.662 msec)
hdbsql TMP=> alter table bench.customer1tst alter (c_zip char(9) numa node ('3'));
0 rows affected (overall time 11.592 msec; server time 6300 usec)
hdbsql TMP=> load bench.customer1tst (c_zip);
0 rows affected (overall time 1044.345 msec; server time 1039.639 msec). 테이블단위 설정 : alter table bench.customer1tst numa node ('2');
alter table bench.customer1tst null;
== 변경 사항 확인
hdbsql TMP=> select memory_size_in_total from m_cs_all_columns where table_name = 'CUSTOMER1TST' and column_name = 'C_ZIP';
| MEMORY_SIZE_IN_TOT |
| --------------------- |
| 735026564 |
1 row selected (overall time 28.240 msec; server time 18.632 msec)
hdbsql TMP=> select schema_name, table_name, column_name, numa_node_index from m_cs_all_columns where table_name = 'CUSTOMER1TST' and column_name = 'C_ZIP';
| SCHEM | TABLE_NAME | COLUM | NUMA_NODE_INDEX |
| ----- | ------------ | ----- | ---------------- |
| BENCH | CUSTOMER1TST | C_ZIP | 3 | <<<< unload -> load 해야 반영됨 / alter 후에 바로 조회해 보면 값의 변화가 없음
1 row selected (overall time 28.126 msec; server time 18.828 msec)
hdbsql TMP=> select schema_name, table_name, column_name, NUMA_NODE_INDEXES from table_columns where table_name = 'CUSTOMER1TST' and column_name = 'C_ZIP';
| SCHEM | TABLE_NAME | COLUM | N |
| ----- | ------------ | ----- | - |
| BENCH | CUSTOMER1TST | C_ZIP | 3 |
1 row selected (overall time 9887 usec; server time 855 usec)
tmpadm@server01:/usr/sap/TMP/HDB00> hdbcons "numa locate BENCH CUSTOMER1TST"
SAP HANA DB Management Client Console (type '\?' to get help for client commands)
Try to open connection to server process 'hdbindexserver' on system 'TMP', instance '00'
SAP HANA DB Management Server Console (type 'help' to get help for server commands)
Executable: hdbindexserver (PID: 355543)
[OK]
--
## Start command at: 2019-06-25 14:36:00.220
*******************************************************************************************
NUMA page location statistics of Table (Main Store)
*******************************************************************************************
Total number of NUMA nodes = 8
Table name = CUSTOMER1TST
Expected NUMA locations: [1, 3]
Attribute IDs involved: [7, 217]
*******************************************************************************************
------------------------------------------------------------------------------------------
Node Id DataPageBytes DictionaryPageBytes IndexPageBytes MiscPageBytes
------------------------------------------------------------------------------------------
0 0 0 0 0
1 3281256 0 0 0
2 0 0 0 0
3 735000008 52344 0 3296 <<<------ NUMA Node 3으로 변경됨을 확인
4 0 0 0 0
5 0 0 0 0
6 0 0 0 0
7 0 0 0 0
*******************************************************************************************
Total memory page bytes with unaccounted NUMA location = 0
*******************************************************************************************
------------------------------------------------------------------------------------------
DataPageBytes DictionaryPageBytes IndexPageBytes MiscPageBytes
------------------------------------------------------------------------------------------
0 0 0 0
*******************************************************************************************
NUMA page location statistics of Table (Delta Store)
*******************************************************************************************
Total number of NUMA nodes = 8
Table name = CUSTOMER1TST
Expected NUMA locations: [1]
Attribute IDs involved: [5, 7, 201, 202, 203, 204, 205, 206, 207, 208, 209, 210, 211, 212, 213, 214, 215, 216, 217, 218, 219, 220, 221, 222]
*******************************************************************************************
------------------------------------------------------------------------------------------
Node Id DataPageBytes DictionaryPageBytes IndexPageBytes MiscPageBytes
------------------------------------------------------------------------------------------
0 10192 0 0 0
1 0 2688 0 0
2 0 0 0 0
3 0 0 0 0
4 0 0 0 0
5 0 0 0 0
6 0 0 0 0
7 0 0 0 0
*******************************************************************************************
Total memory page bytes with unaccounted NUMA location = 0
*******************************************************************************************
------------------------------------------------------------------------------------------
DataPageBytes DictionaryPageBytes IndexPageBytes MiscPageBytes
------------------------------------------------------------------------------------------
0 0 0 0
[OK]
## Finish command at: 2019-06-25 14:36:00.272 command took: 51.404 msec
--
[EXIT]
--
[BYE]'Database' 카테고리의 다른 글
| [HANA] Database trace설정 (savepoint) (0) | 2019.07.12 |
|---|---|
| [HANA] Memory GC Trigger 관련 파라미터 (0) | 2019.06.25 |
| [HANA] 2470289 - FAQ: SAP HANA Non-Uniform Memory Access (NUMA) (0) | 2019.06.23 |
| [HANA] 2114710 - FAQ: SAP HANA Threads and Thread Samples (0) | 2019.05.29 |
| [HANA] HSR 구성 (0) | 2019.05.22 |