본문 바로가기
Database

[HANA] 2114710 - FAQ: SAP HANA Threads and Thread Samples

이반&핫버드 2019. 5. 29. 16:27

https://launchpad.support.sap.com/#/notes/2114710

 

 

Thread Overview

  • Threads description
    • The SAP HANA work, like executing of SQL statements or background tasks is performed by threads
    • Each SAP HANA process like indexserver or xsengine consists of a set of threads
  • Information about historic thread activityes are available
    • Monitoring view M_SERVICE_THREAD_SAMPLES
      • <service>.ini -> [resource_tracting] -> service_thread_sampling_monitor_enabled = true|false
    • History HOST_SERVICE_THREAD_SAMPLES
      • Only available if embedded statistics server is used
      • Should be historicized at least evenry 10 munutes (SAP Note 1999993)
      • Retention time = 1 day (default) ~ 42 days 
    • SQL: "HANA_Threads_ThreadSamples_FilterAndAggegation" (SAP Notes 1969700)
    • SQL: "HANA_Threads_ThreadSamples_AggregationPerTimeSlice" (SAP Note 1969700)
  • Activity related with threads
    • Activity responsible for CPU consumption (SAP Note 2100040)
    • Activity responsible for lock situations (SAP Note 1999998)
    • Activity responsible for bad performance (SAP Noe 2000000)
    • Expensive SQL statement (SAP Note 2000002)
  • Thread information
Information Column names Available Details
Host name
Port
Service name		 HOST
PORT
SERVICE_NAME		 generally Basic information to understand which service on which host owns the thread
Timestamp TIMESTAMP M_SERVICE_THREAD_SAMPLES
HOST_SERVICE_THREAD_SAMPLES		 Sample timestamp
Connection ID CONNECTION_ID generally Client connection identifier (-1 if thread has no assigned client connection)
Thread ID THREAD_ID generally Thread identifier
Statement hash STATEMENT_HASH

M_SERVICE_THREADS (SAP HANA SPS >= 10)
M_SERVICE_THREAD_SAMPLES
HOST_SERVICE_THREAD_SAMPLES

 Statement hash of active SQL statement, important anchor information for SQL optimization (SAP Note 2000002)
Statement ID STATEMENT_ID

M_SERVICE_THREADS (SAP HANA SPS >= 10)
M_SERVICE_THREAD_SAMPLES
HOST_SERVICE_THREAD_SAMPLES

 Identifier for individual statement executions
Active flag IS_ACTIVE M_SERVICE_THREADS

TRUE if thread is currently active, otherwise FALSE

History views only contain active threads, so flag is not required for M_SERVICE_THREAD_SAMPLES and HOST_SERVICE_THREAD_SAMPLES
Thread state THREAD_STATE generally Activity state of thread (e.g. running or waiting for a resource), for more details see "What are the main thread types, methods and details?" below.
Thread type THREAD_TYPE generally Thread type (e.g. SQLExecutor or JobWorker), for more details see "What are the main thread types, methods and details?" below.
Thread method THREAD_METHOD generally (but not consequently populated in thread sample views)

Thread method (e.g. ExecutePrepared or indexing), for more details see "What are the main thread types, methods and details?" below.

Partially (e.g. for SqlExecutor threads) only written to thread sample views if the following parameter is set to true:
Thread detail THREAD_DETAIL generally (but not populated per default in thread sample views)

Thread detail (e.g. SQL statement or column store plan step), for more details see "What are the main thread types, methods and details?" below

Only written to thread sample views if the following parameter is set to true:
Caller thread
Called thread		 CALLER
CALLING		 M_SERVICE_THREADS
M_SERVICE_THREAD_SAMPLES (>= SPS 09)		 Thread dependencies (caller: ID of thread which called the current thread; calling: ID of thread which is called by the current thread)
Database user name USER_NAME generally Name of database user executing the thread
Application user name APPLICATION_USER_NAME generally Name of application user (e.g. SAP end user in ABAP environments)
Application name APPLICATION_NAME M_SERVICE_THREAD_SAMPLES
HOST_SERVICE_THREAD_SAMPLES		 Name of client application
Application source APPLICATION_SOURCE M_SERVICE_THREAD_SAMPLES
HOST_SERVICE_THREAD_SAMPLES		 Application details (e.g. report name in case of SAP ABAP stacks)
CPU time CPU_TIME_SELF
CPU_TIME_CUMULATIVE		 M_SERVICE_THREADS

CPU time in microseconds consumed by the thread itself (CPU_TIME_SELF) or including the associated children (CPU_TIME_CUMULATIVE)

On SAP HANA <= SPS 11 this value is only populated if the following SAP HANA parameter is set (SAP Note 2100040):
Transaction ID
Update transaction ID		 TRANSACTION_ID
UPDATE_TRANSACTION_ID		 generally Identifier of related transaction (-1 if no transaction is associated to thread) and update transaction (-1 if no uncommitted modification is associated to thread)
Lock wait details LOCK_WAIT_COMPONENT
LOCK_WAIT_NAME
LOCK_OWNER_THREAD_ID		 generally Component, details of lock waits and ID of thread being responsible for lock wait

 

  • Thread State
Thread state Area Details SAP Note
Barrier Wait Lock  Barrier wait 1999998
ConditionalVariable Wait Lock  Mainly record and table lock waits 1999998
ExclusiveLock Enter Lock Exclusive read / write lock waits 1999998
Inactive Idle Idle thread  
IO Wait I/O Disk I/O wait (e.g. during column load or hybrid LOB access) 1999930
Job Exec Waiting Idle Waiting for a JobWorker thread executing the actual work  
Mutex Wait Lock Mutex lock waits 1999998
Network Connect Network Network I/O between SAP HANA nodes or services 2222200
Network Poll Network Network I/O between SAP HANA nodes or services 2222200
Network Read Network Network I/O read between SAP HANA nodes or services 2222200
Network Write Network Network I/O write between SAP HANA nodes or services 2222200
Resource Load Wait I/O Disk I/O wait (e.g. during undo file load, DataAccess::UndoFileAnchor::allocateFile) 1999930
Running SQL Request execution, typically CPU consumption 2000002
Semaphore Wait Lock Semaphore lock waits 1999998
SharedLock Enter Lock Shared read / write lock waits 1999998
Sleeping Lock Waiting for a lock 1999998
Speculative Lock Retry backoff Lock Transactional memory lock (back-off waiting on transaction retry) 1999998
Speculative Lock Wait for fallback Lock Transactional memory lock state (waiting on fallback lock) 1999998

 

  • Thread Type (Assign - method)
    • Assign
      • assign
        • open data+log volume <id> / local tables
        • SAP Notes 2185955
        • indexserver -> nameserver communication in a ndexserver -> nameserver communication in order to open disk volumes
        • nameserver에 이슈가 있는 경우 blocked됨
      • post-assign
        • open data+log volume <id> / preload tables
        • SAP Notes 227458
        • Reloading tables at the end of start and volume assignement
    • AsyncRSIndexPreloader
      • NA
        • SAP Notes 2160391 
        • Asynchronous recreation of row store indexs at startup
    • AsyncSende
      • send
        • working fo thread <waiting_thread>
        • Asynchronous thread / thread communication (sending thread)
    • AsyncWaiter 
      • waiting
        • working for thread <sending_thread>
        • Asynchronous thread / thread communication (waiting thread), used in cases where a thread needs to issue multiple network requests in parallel, every one of these parallel requests is processed by an own AsyncWaiter thread
    • BackupExe_LogWaitForBackintConn
      BackupExe_LogWaitForBackintConnectionThread
      • NA
        • SAP Notes 1642148
        • Used in context of backups taken via backint
    • BackupProgressWatchdog
      • NA
        • SAP Notes 1642148
        • Monitoring of SAP HANA backup progress
    • Caallstack
      • NA
        • <host>:<port>
        • SAP Notes 1213619
        • Generation of SAP HANA call stacks, thread detail indicates the host and service port of the processed thread
    • CatalogWatchdo
      • NA
        • Watchdog thread for SAP HANA catalog
    • ChildIOThreads::ErrorStream
      ChildIOThreads::OutputStream
      • NA
        • These threads are linked to SAP HANA streaming functionalities like streaming server (hdbstreamingserver) or streaming cluster utility (streamingclusterutil).
        • Usually they wait for incoming streaming requests in the following call stacks:
        • __read_nocancel+0x20
          System::UX::read
          (anonymous namespace)::writeOutput
          (anonymous namespace)::ChildIOThreads::ErrorStream::run
          (anonymous namespace)::ChildIOThreads::OutputStream::run
        • This is an idle state that can be considered as harmless. Starting with SAP HANA 1.00.122.24, 2.00.024.09 and 2.00.036 these threads are no longer displayed as active.
    • ClockMonitor
      • NA
        • SAP HANA internal clock time generation in order to be independent from system time (e.g. in cases when system time is manually adjusted)
    • ContinuousPageFlusher
      • flushing
        • SAP Notes 200009
        • Populates I/O flush queue with dirty pages, disjoint from actual savepoints
    • DirSize
      • fdir
        • <host>-><directory>
        • SAP Notes 1999930
        • Requests operating system directory information, e.g. in context of monitoring view calls like M_DISKS
        • Can be called by a JobWorker in context of NameServer::DirSizeJob::DirSizeJob
        • May spawn a Request thread working in thread method core/fdi
    • DiskInfo
      • remotediskinfo
        • Request of information for remote physical disk, typically required in context of accessing monitoring views with disk information,
        • e.g. M_SYSTEM_OVERVIEW, M_VOLUME_SIZES or M_DISKS
    • DPDistributor<remote_source_id>
      DPReceiver<remote_source_id>
      DPReceiverWriter<remote_source_id>
      • NA
        • SAP Notes 2400022
        • These threads are used for communicating with remote sources in smart data integration (SDI) environments.
    • DsoUpdate
      • ActivationQueuePackage
        • <schema>:<table>en: [<id>, <id>) / {Lhs table parts: [<schema>:<table> (<id>)] (not prunable: size 1 set 0), rhs table parts: [<schema>:<table> (<id>)]}
        • SAP Notes 2000002
        • DSO activation via DSO_ACTIVATE_PERSISTED
      • DSOPartActivatorLocal
        • <schema>:<table>en RequestIds <id> / {Lhs table parts: [<schema>:<table> (<id>)] (not prunable: size 1 set 0), rhs table parts: [<schema>:<table> (<id>)]}
        • SAP Notes 2000002
        • DSO activation via DSO_ACTIVATE_PERSISTED
      • RollbackPersistedPackage
        • SAP Notes 2000002
        • DSO rollback via DSO_ROLLBACK_PERSISTED
    • EmergencyJobForDeferredResource
      • NA
        • SAP Notes 2127458
        • Framework used during critical situations (e.g. for unloading columns during memory shortages)
    • FileCompletionThread-BACKUP-0
      FileCompletionThread-DATA-0
      FileCompletionThread-DATA_BACKUP-0
      FileCompletionThread-LOG-0
      FileCompletionThread-LOG_BACKUP
      • NA
        • SAP Notes 1999930
        • SAP HANA typically performs asynchronous I/O that is processed by the operating system.
        • Once a request if finished (successfully or unsuccessfully),
          the OS kernel reports it back to SAP HANA. This feedback is handled by the FileCompletionThreads.
    • FlushResourcesHelperThread
      FlushResourcesThread
      • NA
        • SAP Notes 1999930
        • Flushing of pages to disk (includes tasks like creation of shadow pages, checksumming, encryption and triggering of asynchronous I/O), helper threads are triggered by main flush thread
    • GarbageLobFileHandler
      • NA
        • SAP Notes 2169283
        • Lob gabage collection (row store)
    • Generic
      • NA
        • SAP Notes 1999880, 1787489
        • See THREAD_TYPE = 'Native' for more information.
      • LogSegmentFormatter[0]
        • SAP Notes 1999880
        • This thread is responsible for initializing a new redo log file. 
        • See THREAD_TYPE = 'LogSegmentFormatter[0]' for more information.
      • Search for trace files to compress
        • SAP Notes 2119087
        • Check if trace files (e.g. alert traces) exist that should be compressed
        • Peaks can be caused by I/O issues accessing the trace files (see SAP Note 1999930), example for an I/O related call stack:
        • __open_nocancel+0x7
          __opendir+0x1
          System::UX::opendir
          FileAccess::DirectoryEntry::findFirst
          FileAccess::DirectoryEntry::DirectoryEntry
          Diagnose::TraceSegmentCompressorThread::run
          __getdents+0x6
          __readdir_r+0x105 System::UX::readdir_r
          FileAccess::DirectoryEntry::findFirst
          FileAccess::DirectoryEntry::DirectoryEntry
          Diagnose::TraceSegmentCompressorThread::run
        • Also a high number of trace files can be responsible for increased activities. See SAP Note 2119087 for SAP HANA trace configuration.
        •  SQL: "HANA_TraceFiles_Overview" (SAP Note 1969700) can be used to display existing trace files.
        • Remove old and unnecessary trace files and make sure that the trace configuration is configured in a way that a high amount of trace files is avoided.
    • getLastDocids
      • byValue
        • $trexexternalkey$ from=<id> to=<id>
        • Row ID retrieval in column store
    • GroupMulticastExecutor(DMLMulticastAsync)
      GroupMulticastExecutor(MasterFirstPrepare)
      GroupMulticastExecutor(MasterPostCommit)
      GroupMulticastExecutor(MasterTimestampBroadcaster)
      GroupMulticastExecutor(SnapshotScopeManager)
      GroupMulticastExecutor(SlavePostCommit)
      • NA
        • SAP Notes 2222200
        • Fast handling of transaction manager requests, e.g. in context of 2-phase commit, timestamp synchronization, locking or transaction status retrieval.
    • HighResolutionLoadMon
      • ping
        • <host>:<port>
        • SAP Notes 2222110
        • Nameserver ping to other services in order to request load history information
  • .....

 

 

 

  • Thead sample과 연관된 parameter
    • M_SERVICE_THREAD_SAMPLES 의 contents를 control 하기 위함
    • global.ini -> [resource_tracking] -> service_thread_sampling_monitor_enabled
      • <= 1.00.122.13, < 2.00.010: true 
        >= 1.00.122.14, >= 2.00.010: false
저작자표시 비영리 변경금지

'Database' 카테고리의 다른 글

[HANA] NUMA Node를 user-specific하게 지정하는 방법  (0) 2019.06.25
[HANA] 2470289 - FAQ: SAP HANA Non-Uniform Memory Access (NUMA)  (0) 2019.06.23
[HANA] HSR 구성  (0) 2019.05.22
[HANA] Insert Only Operation  (0) 2019.05.21
[HANA] Delta merge-2 (internal)  (0) 2019.05.21

'Database' Related Articles

티스토리툴바