Handling System Hang but Reachable Situations

SAP HANASitter is a tool developed by SAP Support that allows you to monitor SAP HANA and to automatically create dump files in certain scenarios. For more information, see SAP Note 2399979 - How-To: Configuring automatic SAP HANA Data Collection with SAP HANASitter

The SAP HANAsitter checks by default once an hour, if SAP HANA is online and primary. If so, it starts to track. Tracking includes regularly (by default, every minute) checking if SAP HANA is responsive. If it is not, it starts to record.

Recording can include writing call stacks of all active threads, recording run time dumps, index server gstacks, and/or kernel profiler traces. By default, nothing is recorded.

If SAP HANA is responsive, it checks many of the critical features of SAP HANA. As standard, the script checks if there are more than 30 active threads. If there are more than 30 active threads, the script starts to record.

When the script has finished recording, it exits. The script can be configured to restart using the command line.

When the script has finished all the tests successfully, it sleeps for one hour, before it starts all the checks again.

In a system hanging situation the execution of SQL statements is probably not possible anymore. If you still can log on to the operating system, you should try to perform the following steps on the OS of the master host.

In cases where logs cannot be written, all DML statements will fall into wait status. This can lead to a failure of opening new connections because the system internally executes DML statements during the process. Typically, a full log volume is the cause for this.

The root cause of the "log volume full" situation is either caused by disk being full or hitting the quota setting. To investigate more deeply, perform the following steps:

1. Check for the Internal Disk-Full Event (Alert 30) in the indexserver trace.
2. Check if the system is running out of disk space using the command df -h on the OS ssh shell.
3. Check if the system is running out of inodes (NFS) using the command df -i.
4. Check the quota setting in file system.
5. Check SAP Note 1679938 - Log Volume is full.
6. Check SAP Note 2083715 - Analyzing log volume full situations.

Once you have resolved the issue (for example, freed up disk space), you may need to manually mark the internal event as handled. You can do this on the Overview tab of the Administration editor in the SAP HANA studio, or by executing the following SQL statements:

12
ALTER SYSTEM SET EVENT ACKNOWLEDGED '<host>:<port>' <id>
ALTER SYSTEM SET EVENT HANDLED '<host>:<port>' <id>

For accurate root cause analysis, it is very helpful to have call-stack and runtime information available from the time of the hang. For this, you can use the approaches outlined in SAP Note 2313619. It is useful to capture one or several runtime dumps (SAP Note 1813020) so that an accurate root cause analysis can be done later.

If you consider the possibility of an SAP HANA internal deadlock, you can also run the deadlock detector functionality of hdbcons (SAP Note 2222218): hdbcons 'deadlockdetector wg -w -o <file_name>.dot'.

Use OS commands like top to identify the amount of CPU consumption, the main CPU consuming processes, and the main CPU utilization component. As SAP HANA is a multithreading system it's more useful to have an overview of the consumed CPU per thread. To display the CPU used per thread, use the top -H command. Its output shows the CPU consumption per thread.

Check /proc/interrupts to understand if certain interrupts happen very often. Ensure that the SAP HANA-related file systems contain sufficient free space (for example, using tools like df or mmdf (for GPFS)) and that there is no quota defined that could result in a file system becoming full. Check if the file systems are still reachable, for example, using df /usr/sap/<sid>. Inspect OS log files, such as /var/log/messages, for suspicious error messages that are issued at the time of the problem (but not at times of normal operation).

The Troubleshoot Unresponsive Systems function uses the SAP host agent. It can collect the most important diagnosis information from the SAP HANA database, even when the system is stopped or cannot be reached by SQL due to performance problems.

To troubleshoot a system in a hang state, there is a function in SAP HANA cockpit called Troubleshoot unresponsive systems. When using this function, information is collected through the SAP host agent. The communication between the web browser and the SAP host agent is always done over HTTPS, which requires that the SAP host agent has a Secure Sockets Layer (SSL) certificate (PSE) in its security directory.

The information is collected into a file named emergency_info_<SID>.zip by the Python script emergencyInfo.py. This script connects to the index server, using the hdbcons interface. The script tries to collect information about the open connections, running transactions, and threads. It also shows blocked transactions. If the index server is unavailable, no information is shown.

The information is collected by a Python script that connects to the index server, using the hdbcons interface. The script tries to collect information about the open connections, running transactions, and threads. It also shows blocked transactions. If the index server is unavailable, no information is shown.

The Troubleshoot Unresponsive System function organizes information about the system by tab. You can diagnose the following:

• Connections
• Transactions
• Blocked transactions
• Threads

Analyzing the sessions connected to your SAP HANA database helps you identify which applications, or which users, are currently connected to your system, as well as what they are doing in terms of SQL execution.

On the CONNECTIONS tab, you can see information about the current connections to the SAP HANA server. This information includes connection start time, ID, user name, and status. If there are many connections open to the server, it can lead to congestion and may result in the server becoming unresponsive.

On the CONNECTIONS tab, you can use the Cancel Connection button to stop a single connection. To do this, select the connection that you want to cancel, and choose Cancel Connection.

You can stop all the transactions that are currently running by choosing Cancel All Transactions.

On the TRANSACTIONS tab, you can see information about the current transactions in the SAP HANA system. This information includes connection and transaction ID, allocated memory, and user name. The information shown in the transactions table gives you a good insight into current activity on the system.

Via the connection ID and the primary connection tab, you can even link the transaction to the corresponding connection.

You can stop all the transactions that are currently running by choosing Cancel All Transactions.

On the BLOCKED TRANSACTIONS tab, you can investigate if there are blocked transactions in your system.

Blocked transactions are transactions that cannot be processed further because they need to acquire transactional locks (record or table locks) that are currently held by another transaction. Transactions can also be blocked while waiting for other resources, such as the network or disk access (database or metadata locks).

The type of lock held by the blocking transaction (record, table, or metadata) is indicated in the Lock Type column.

The lock mode is indicated in the Transactional Lock Type column.

Exclusive: Row-level locks prevent concurrent write operations on the same record. They are acquired implicitly by update and delete operations, or explicitly with the SELECT FOR UPDATE statement.

Table-level: Locks prevent operations on the content of a table from interfering with changes to the table definition (such as drop table or alter table). DML operations on the table content require an intentional exclusive lock, while changes to the table definition (DDL operations) require an exclusive table lock. There is also a LOCK TABLE statement for explicitly locking a table. Intentional exclusive locks can be acquired if no other transaction holds an exclusive lock for the same object. Exclusive locks require that no other transaction holds a lock for the same object (neither intentional exclusive nor exclusive).

For more detailed analysis of blocked transactions, information about low-level locks is available in the columns Lock Wait Name, Lock Wait Component, and Thread ID of Low-Level Lock Owner. Low-level locks are locks acquired at the thread level. They manage code-level access to a range of resources (for example, internal data structures, network, or disk). Lock wait components group low-level locks by engine component or resource.

By choosing Cancel All Transactions, you can stop all the current running transactions.

Because the Delta Table Merge needs to lock tables to proceed, it is a common cause of blocked transactions. Another job displayed by this monitor is the savepoint write, which needs to pull a global database lock in its critical phase. A common issue is a flaw in the application coding that does not commit a write transaction. Such a transaction will block any other transaction that needs to access the same database object. To remedy the situation, close the blocking transaction.

In the UI table, the blocked transactions are displayed directly beneath the blocking transaction.

First, you must determine whether there is only one, or a few transactions, blocking many other transactions. To do this, open the Blocked Transaction tab and check the amount of blocking transactions. If there are only a few blocking transactions, there is probably an issue on the application side. To resolve the problem, use the following techniques:

1. If only one transaction is blocked, contact the application user and the developer. Firstly, ask the user to close the application and secondly, to check if there is a general issue with the application code.

   If you are not able to contact the user, you can kill the transaction or kill the client process that opened the session. The transaction is rolled back. The session cancellation may take some time to succeed. If it takes longer than 30 seconds, consider this as a bug and contact development support.

   If the session cancellation takes too long or does not complete at all, you can kill the client process that opened the session. This also terminates the blocking transaction. As a prerequisite, you must have access to the client machine.

2. If a large amount of transactions are blocked, you must find out whether a specific access pattern is causing the issue. If multiple transactions are trying to access the same database objects with write operations, they block each other. To check if this is happening, open the Blocked Transaction Monitor and analyze the Waiting Schema Name, Waiting Object Name, and Waiting Record ID columns. If you find a lot of transactions that are blocking many other transactions, you must investigate whether you can do the following:

   1. Change the client application(s) to avoid the access pattern.
   2. If a background job that issues many write transactions (for example, a data load job) is running, reschedule it to a period with a low user load.
   3. Partition tables that are accessed frequently to avoid clashes. See the SAP HANA Administration Guide for more details on partitioning.
3. If you cannot identify specific transactions or specific database objects that lead to transactions being blocked, you can assume that there is a problem with the database itself or with its configuration, an issue with the delta merge (for example, mass write operation on a column table), or a long savepoint duration.

In the THREADS tab, you can identify which statements or procedures are being executed and at what stage they are, who else is connected to the system, and if there are any internal processes running as well. The information shown includes thread type, ID, and thread status. You can also find information of system user and waiting time. The information shown in the table helps you to identify transactions with high average wait times. With the user name, thread ID, and wait time columns, you can identify which thread is causing problems.

In this tab, you can identify long-running threads and those threads that are blocked for an inexplicable long period of time.

In the case of an emergency, choose Cancel All Transactions.