Symptom
You are experiencing slow database performance during distributed
statements or are unable to distribute statements within your SAP HANA
cluster appropriately.
Other Terms
HANA, DB, BW on HANA, Suite on HANA, statement routing, default route, hostnames, distributed statements
Reason and Prerequisites
You are running SAP HANA in any flavor on SLES 11 SP2 or SLES 11 for
SAP SP2 on a Scale-out appliance on SAP HANA SPS06. During the execution
of SQL statements which do access tables, partitions, or data being not
located on the SAP HANA master node, you are experiencing performance
degradations which you do not experience when executing the same SQL
statement with the HANA studio being directly connected against the
master node of the SAP HANA cluster.
With SAP HANA SP6 (starting with revision 60) the default behavior for internal/external hostname and IP address identification changed. Prior to SAP HANA SP6 the database client received a list of hostnames from the database corresponding to the different HANA nodes within the scale-out cluster. The exact value of the delivered host names depends on the choosen configuration during installation: In case internal virtual host names have been assigned during the installation (which can differ from real physical host names and which is required for DT scenarios), those host names are being delivered. In case no dedicated internal virtual host names have been assigned during the installation, the real physical host names (without domoin) are being delivered (which are by the way then identical to the internal virtual host names). Depending on the overall setup, those hostnames might have caused issues with hostbased routing and workarounds were needed to provide a working solution. The new identification method relies on the default route and IP addresses configured on the nodes within the SAP HANA cluster. Therefore a correct configuration of the network setup is required.
With SAP HANA SP6 (starting with revision 60) the default behavior for internal/external hostname and IP address identification changed. Prior to SAP HANA SP6 the database client received a list of hostnames from the database corresponding to the different HANA nodes within the scale-out cluster. The exact value of the delivered host names depends on the choosen configuration during installation: In case internal virtual host names have been assigned during the installation (which can differ from real physical host names and which is required for DT scenarios), those host names are being delivered. In case no dedicated internal virtual host names have been assigned during the installation, the real physical host names (without domoin) are being delivered (which are by the way then identical to the internal virtual host names). Depending on the overall setup, those hostnames might have caused issues with hostbased routing and workarounds were needed to provide a working solution. The new identification method relies on the default route and IP addresses configured on the nodes within the SAP HANA cluster. Therefore a correct configuration of the network setup is required.
Solution
The following sections provides you with a detailed specification of how to check the setup or configure it appropriately:
To verify your setting is effectively working, you can check the nameserver trace files of the respective HANA DB cluster for entries such as:
public hostname resolution: use default route as ip -> 10.136.215.4
in the case use_default_route = 'IP'
or
public hostname resolution: use internal hostname -> hana01int
when for example the setting use_default_route = 'no' is used.
The entry in the trace file actually shows the configured mechanism as well as the configured value. So there is acutally no additional lookup of effective values required as with be below mentioned SQL statement in the next section, which is primarily there to give the complete picture.
For the setting use_default_route = 'no', the propagated SAP HANA hostnames can individually be checked in the nameserver trace files (see section above) close to the display of the utilized mechanism. In addition to that, a complete overview of all propagated hostnames of the SAP HANA cluster can be retrieved with the following SQL statement:
select host, value from m_host_information where key='net_publicname'
For each host in the HANA cluster the propagated hostname can be found in the column value.
For the setting use_default_route = 'ip', the same as above holds true for propagated IP addresses. The nameserver trace contains the individual ip address - the provided SQL statement delivers ip addresses for each individual host.
What is more, for the setting use_default_route = 'ip' the IP address set on operating system level for the default route is propagated. The entire routing information plus the finally used ip address of the default route can be found in the daemon trace (entries with prefix network):
[60722]{-1}[-1/-1] 2013-09-03 06:58:27.710657 i Network TrexDaemon.cpp(02210) : routing table:
[60722]{-1}[-1/-1] 2013-09-03 06:58:27.710661 i Network TrexDaemon.cpp(02210) : bond2 0.0.0.0 10.136.215.1 0.0.0.0/0 0 ipv4 up default
[60722]{-1}[-1/-1] 2013-09-03 06:58:27.710665 i Network TrexDaemon.cpp(02210) : bond3 10.136.214.0 0.0.0.0 255.255.255.0/24 0 ipv4 up
[60722]{-1}[-1/-1] 2013-09-03 06:58:27.710667 i Network TrexDaemon.cpp(02210) : bond2 10.136.215.0 0.0.0.0 255.255.255.0/24 0 ipv4 up
[60722]{-1}[-1/-1] 2013-09-03 06:58:27.710669 i Network TrexDaemon.cpp(02210) : bond1 10.136.216.0 0.0.0.0 255.255.254.0/23 0 ipv4 up
[60722]{-1}[-1/-1] 2013-09-03 06:58:27.710671 i Network TrexDaemon.cpp(02210) : bond3 10.136.226.0 10.136.214.1 255.255.255.0/24 0 ipv4 up
[60722]{-1}[-1/-1] 2013-09-03 06:58:27.710673 i Network TrexDaemon.cpp(02210) : bond3 10.136.231.0 10.136.214.1 255.255.255.0/24 0 ipv4 up
[60722]{-1}[-1/-1] 2013-09-03 06:58:27.710675 i Network TrexDaemon.cpp(02210) : bond3 10.127.1.115 10.136.214.1 255.255.255.255/32 0 ipv4 up
[60722]{-1}[-1/-1] 2013-09-03 06:58:27.710677 i Network TrexDaemon.cpp(02210) : bond3 10.127.3.33 10.136.214.1 255.255.255.255/32 0 ipv4 up
[60722]{-1}[-1/-1] 2013-09-03 06:58:27.710679 i Network TrexDaemon.cpp(02210) : bond3 10.127.20.119 10.136.214.1 255.255.255.255/32 0 ipv4 up
[60722]{-1}[-1/-1] 2013-09-03 06:58:27.710681 i Network TrexDaemon.cpp(02210) : bond3 10.127.20.120 10.136.214.1 255.255.255.255/32 0 ipv4 up
[60722]{-1}[-1/-1] 2013-09-03 06:58:27.710683 i Network TrexDaemon.cpp(02210) : lo 127.0.0.0 0.0.0.0 255.0.0.0/8 0 ipv4 up
[60722]{-1}[-1/-1] 2013-09-03 06:58:27.710684 i Network TrexDaemon.cpp(02210) : bond3 192.0.0.0 10.136.214.1 255.0.0.0/8 0 ipv4 up
[60722]{-1}[-1/-1] 2013-09-03 06:58:27.710686 i Network TrexDaemon.cpp(02210) : bond4 192.168.6.0 0.0.0.0 255.255.255.0/24 0 ipv4 up
[60722]{-1}[-1/-1] 2013-09-03 06:58:27.710688 i Network TrexDaemon.cpp(02210) : bond5 192.168.7.0 0.0.0.0 255.255.255.0/24 0 ipv4 up
[60722]{-1}[-1/-1] 2013-09-03 06:58:27.710919 i Network TrexDaemon.cpp(02223) : default IPv4 interface: bond2 10.136.215.4 (hana01u, hana01u.fqdn) ipv4 up broadcast running multicast
[60722]{-1}[-1/-1] 2013-09-03 06:58:27.711147 i Daemon TrexDaemon.cpp(09985) : handling programs
If this needs to be checked on the operating system level (per host), this can easily be done via the following two commands:
gw_if=`ip route list match 0.0.0.0 | awk '{print $5}'`
ip addr show dev ${gw_if}
Here an Example output where the default route is over bond2 with the ip 10.136.215.4:
Bond2: <BROADCAST,MULTICAST,MASTER,UP,LOWER_UP> mtu 1500 qdisc noqueue state UP
link/ether XX:XX:XX:XX:XX:XX brd ff:ff:ff:ff:ff:ff
inet 10.136.215.4/XX brd XX.XX.XXX.XXX scope global bond2
inet6 XXX::XXX:XXXX:XXXX:XXXX/64 scope link
valid_lft forever preferred_lft forever
Be aware that in a SAP HANA scale-out cluster this has to be done on each and every server within the cluster. Please also check if the interface / utilized network subnet is consistent on all nodes with the SAP HANA cluster.
In order to have the communication from the SAP HANA client working properly (including SQL statement routing / distribution to dedicated SAP HANA host names / ip addresses) you need to ensure that the hostnames / ip addresses can be resolved and reached from the respective SAP HANA client.
Generally SAP recommends the default SAP HANA SPS06 setting - having use_default_route = 'ip' in combination with having the proper default route configured on operating system level side. In case you have the requirement of a more fine grain network configuration please check the SAP HANA Administration guide available at http://help.sap.com/hana/SAP_HANA_Administration_Guide_en.pdf (section "Configuring the network for multiple hosts" and "Mapping host names for database client access").
- 1. Identify which mechanism is currently set within your SAP HANA cluster:
To verify your setting is effectively working, you can check the nameserver trace files of the respective HANA DB cluster for entries such as:
public hostname resolution: use default route as ip -> 10.136.215.4
in the case use_default_route = 'IP'
or
public hostname resolution: use internal hostname -> hana01int
when for example the setting use_default_route = 'no' is used.
The entry in the trace file actually shows the configured mechanism as well as the configured value. So there is acutally no additional lookup of effective values required as with be below mentioned SQL statement in the next section, which is primarily there to give the complete picture.
- 2. Identify which hana hostname, ip address is propagated to the client and utilized for back-communication to the HANA database:
For the setting use_default_route = 'no', the propagated SAP HANA hostnames can individually be checked in the nameserver trace files (see section above) close to the display of the utilized mechanism. In addition to that, a complete overview of all propagated hostnames of the SAP HANA cluster can be retrieved with the following SQL statement:
select host, value from m_host_information where key='net_publicname'
For each host in the HANA cluster the propagated hostname can be found in the column value.
For the setting use_default_route = 'ip', the same as above holds true for propagated IP addresses. The nameserver trace contains the individual ip address - the provided SQL statement delivers ip addresses for each individual host.
What is more, for the setting use_default_route = 'ip' the IP address set on operating system level for the default route is propagated. The entire routing information plus the finally used ip address of the default route can be found in the daemon trace (entries with prefix network):
[60722]{-1}[-1/-1] 2013-09-03 06:58:27.710657 i Network TrexDaemon.cpp(02210) : routing table:
[60722]{-1}[-1/-1] 2013-09-03 06:58:27.710661 i Network TrexDaemon.cpp(02210) : bond2 0.0.0.0 10.136.215.1 0.0.0.0/0 0 ipv4 up default
[60722]{-1}[-1/-1] 2013-09-03 06:58:27.710665 i Network TrexDaemon.cpp(02210) : bond3 10.136.214.0 0.0.0.0 255.255.255.0/24 0 ipv4 up
[60722]{-1}[-1/-1] 2013-09-03 06:58:27.710667 i Network TrexDaemon.cpp(02210) : bond2 10.136.215.0 0.0.0.0 255.255.255.0/24 0 ipv4 up
[60722]{-1}[-1/-1] 2013-09-03 06:58:27.710669 i Network TrexDaemon.cpp(02210) : bond1 10.136.216.0 0.0.0.0 255.255.254.0/23 0 ipv4 up
[60722]{-1}[-1/-1] 2013-09-03 06:58:27.710671 i Network TrexDaemon.cpp(02210) : bond3 10.136.226.0 10.136.214.1 255.255.255.0/24 0 ipv4 up
[60722]{-1}[-1/-1] 2013-09-03 06:58:27.710673 i Network TrexDaemon.cpp(02210) : bond3 10.136.231.0 10.136.214.1 255.255.255.0/24 0 ipv4 up
[60722]{-1}[-1/-1] 2013-09-03 06:58:27.710675 i Network TrexDaemon.cpp(02210) : bond3 10.127.1.115 10.136.214.1 255.255.255.255/32 0 ipv4 up
[60722]{-1}[-1/-1] 2013-09-03 06:58:27.710677 i Network TrexDaemon.cpp(02210) : bond3 10.127.3.33 10.136.214.1 255.255.255.255/32 0 ipv4 up
[60722]{-1}[-1/-1] 2013-09-03 06:58:27.710679 i Network TrexDaemon.cpp(02210) : bond3 10.127.20.119 10.136.214.1 255.255.255.255/32 0 ipv4 up
[60722]{-1}[-1/-1] 2013-09-03 06:58:27.710681 i Network TrexDaemon.cpp(02210) : bond3 10.127.20.120 10.136.214.1 255.255.255.255/32 0 ipv4 up
[60722]{-1}[-1/-1] 2013-09-03 06:58:27.710683 i Network TrexDaemon.cpp(02210) : lo 127.0.0.0 0.0.0.0 255.0.0.0/8 0 ipv4 up
[60722]{-1}[-1/-1] 2013-09-03 06:58:27.710684 i Network TrexDaemon.cpp(02210) : bond3 192.0.0.0 10.136.214.1 255.0.0.0/8 0 ipv4 up
[60722]{-1}[-1/-1] 2013-09-03 06:58:27.710686 i Network TrexDaemon.cpp(02210) : bond4 192.168.6.0 0.0.0.0 255.255.255.0/24 0 ipv4 up
[60722]{-1}[-1/-1] 2013-09-03 06:58:27.710688 i Network TrexDaemon.cpp(02210) : bond5 192.168.7.0 0.0.0.0 255.255.255.0/24 0 ipv4 up
[60722]{-1}[-1/-1] 2013-09-03 06:58:27.710919 i Network TrexDaemon.cpp(02223) : default IPv4 interface: bond2 10.136.215.4 (hana01u, hana01u.fqdn) ipv4 up broadcast running multicast
[60722]{-1}[-1/-1] 2013-09-03 06:58:27.711147 i Daemon TrexDaemon.cpp(09985) : handling programs
If this needs to be checked on the operating system level (per host), this can easily be done via the following two commands:
gw_if=`ip route list match 0.0.0.0 | awk '{print $5}'`
ip addr show dev ${gw_if}
Here an Example output where the default route is over bond2 with the ip 10.136.215.4:
Bond2: <BROADCAST,MULTICAST,MASTER,UP,LOWER_UP> mtu 1500 qdisc noqueue state UP
link/ether XX:XX:XX:XX:XX:XX brd ff:ff:ff:ff:ff:ff
inet 10.136.215.4/XX brd XX.XX.XXX.XXX scope global bond2
inet6 XXX::XXX:XXXX:XXXX:XXXX/64 scope link
valid_lft forever preferred_lft forever
Be aware that in a SAP HANA scale-out cluster this has to be done on each and every server within the cluster. Please also check if the interface / utilized network subnet is consistent on all nodes with the SAP HANA cluster.
- 3. Verify that the hostnames or ip addresses can be reached from the SAP HANA client to ensure proper communication:
In order to have the communication from the SAP HANA client working properly (including SQL statement routing / distribution to dedicated SAP HANA host names / ip addresses) you need to ensure that the hostnames / ip addresses can be resolved and reached from the respective SAP HANA client.
Generally SAP recommends the default SAP HANA SPS06 setting - having use_default_route = 'ip' in combination with having the proper default route configured on operating system level side. In case you have the requirement of a more fine grain network configuration please check the SAP HANA Administration guide available at http://help.sap.com/hana/SAP_HANA_Administration_Guide_en.pdf (section "Configuring the network for multiple hosts" and "Mapping host names for database client access").
Header Data
| Released On | 16.09.2013 13:32:29 |
| Release Status | Released for Customer |
| Component | HAN-DB SAP HANA Database |
| Priority | Recommendations / Additional Info |
| Category | Customizing |
No comments:
Post a Comment