1. What is the MaxDB Runtime Environment?
The runtime environment refers to the connection between a MaxDB and the particular operating system.
2. What MaxDB processes are contained in a MaxDB instance on Windows?
- dbanalyzer.exe: Database Analyzer for performance long-term analysis
- dbmcli.exe: Database Manager CLI (Command Line Interface) for database administration
- dbmevtdisp.exe: Event Dispatcher
- DBMGui3.exe: Database Manager GUI for database administration
- dbmsrv.exe: DBM Server
- dbmsrvscd.exe: Event Scheduler
- kernel.exe: Database system kernel. Each database instance has its own core process.
- loadercli.exe: Loader for unloading and loading data
- vserver.exe: MaxDB X server responsible for communication between remote applications and the database kernel.
- sqlcli.exe: SQLCLI (Command Line Interface) for SQL queries
- SQLSto.exe: SQL Studio
- dbstudio.exe: Database Studio as of Version 7.7
- wahttp.exe: MaxDB Web Server
3. What MaxDB processes are there in UNIX?
- dbanalyzer: Database Analyzer for performance long-term analysis
- dbmcli: Database Manager CLI (Command Line Interface) for database administration
- dbmevtdisp: Event Dispatcher
- dbmsrv: DBM Server
- dbmsrvscd: Event Scheduler
- kernel: Database system kernel. Each database instance has its own core processes (two).
- loadercli: Loader for unloading and loading data
- vserver: MaxDB X Server; a new child process is started for each database connection on UNIX/Linux.
- niserver: Part of the X server that is required for NI support for the MaxDB Support (SAPDB connection).
- sqlcli: SQLCLI (Command Line Interface) for SQL queries
- wahttp: MaxDB Web Server
4. What functions are performed by the database kernel?
The kernel performs all central database tasks for the database instance. These tasks include, for example, establishing connections, reading and writing data (I/O), monitoring the database instance and processing SQL queries (user tasks).
The database kernel runs as a single operating system process (service) on Microsoft Windows. In UNIX, the kernel has two operating system processes: When started, the database system generates a watchdog process that immediately branches and starts the actual kernel as a process.
5. Which task types are contained in a MaxDB instance?
a) Event task: The event task accepts events generated in the kernel and communicates them to the outside.
b) Log writer: Log writers write the log entries from the log queue to the log area, for example, in the case of a COMMIT or savepoint.
c) Pager (Data writer): Pagers write the data for each savepoint from the data cache to the data volumes. If the data cache is large, executing a savepoint may take a very long time. If necessary, pagers may also become active between the end of one savepoint and the start of the next savepoint. This allows the changed data to be written asynchronously to the data volumes.
d) Server task: The server task run database functions in parallel. They are used, for example, to save data and log entries, create indexes and finally delete tables. When the system is restarted, the server tasks upload log entries and simultaneously process transactions.
e) Timer task: The timer task is responsible for timeouts of SQL lock requests. It checks whether log entries can be saved.
f) Trace writer task: When the database trace is activated, the trace writer task writes the trace entries from the working memory to the trace volume (knltrace). If the database crashes, the trace writer task writes the contents of the internal data structures to the Knldump file.
g) User task: User tasks execute SQL statements.
h) Utility task (only versions lower than 7.7.): The utility task performs management tasks.
6. How is the number of user tasks determined?
When a database user opens a database session, the database system permanently assigns a user task to this database user. You can configure the number of user tasks available with the general database parameter MAXUSERTASKS.
7. How is the number of server tasks configured?
You can configure the number of server tasks available with the special database parameter MAXSERVERTASKS.
Server tasks may have a special name, depending on their use: for example, Savepoint, BackupMedium, BackupVolume, AutosaveCoordinator, AutosaveMedium.
8. Is it possible to configure the number of pagers?
When the system is restarted, the pagers import the converter pages from the data volumes to the converter cache. The number of pagers required is calculated by the system. This number depends primarily on the size of the data cache and the number of data volumes. If MaxDB specialists carry out a performance analysis and discover that the number of pagers is inadequate, you can override the calculated number of pagers with the extended parameter XP_MAXPAGER (as of MaxDB Version 7.6).
9. Is a new process started for every database task?
No. The database kernel generates operating system threads for tasks that should be executed in parallel.
On Linux systems without NPTL (Native Posix Thread Library), every thread is simulated by a clone process.
10. What is a UKT?
The database system differentiates between two types of threads:
UKTs and special threads.
UKTs contain a group of tasks (various task types). UKTs containing user tasks process the client requests. These are the threads that require the most processor time. Usually, a database instance has several UKTs.
Several tasks share a UKT. The database system determines the sequence in which the tasks in a UKT are to run, and can thus avoid conflicts when resources are accessed (task switch). For optimum distribution of the tasks across the UKTs, the database system performs internal load balancing.
11. What special threads are available?
Special threads have precisely one task. Some special threads provide services for the UKTs. Within special threads, the database system performs tasks whose duration cannot be forecast, such as operating system calls for reading and writing to volumes and connection requests from clients. The database system processes special threads asynchronously with the UKTs.
a) Coordinator: The coordinator thread monitors all threads belonging to the database instance. This is the first thread created by the database system when the database instance is started. It coordinates the start activities for subsequent threads. If an error occurs, the coordinator thread can stop other threads.
b) Requester: The requester thread accepts user logons on the database instance and assigns them to a user task within a user kernel thread.
c) Console: The console thread processes the requests from the database tool XCONS (database console).
d) Clock (Windows only): The clock thread sets a global time variable that guides internal time measurements.
e) dev<n>: Dev threads are responsible for executing read and write requests for the tasks to the volumes. The dev threads are numbered sequentially (dev<n>). Dev thread dev0 performs a special role: it coordinates and monitors the other dev threads.
f) asdev<i>: The database system activates temporary dev threads to carry out data backups.
g) Timer: Timer threads are responsible for time monitoring during timeouts, internal load balancing and for additional time-dependent internal control functions.
h) Worker: The worker thread is responsible for reporting completed asynchronous I/O operations.
12. Which factors determine how the MAXCPU parameter is set?
The MAXCPU parameter is used to notify the database kernel of the number of CPUs that can be used by the database. However, this configuration only affects threads that contain user tasks. Other threads access an unlimited number of CPUs, even if the value of MAXCPU is reduced. In general, the following applies:
MAXCPU specifies the number of CPUs subject to intensive, simultaneous use.
The MAXCPU value plays a major role in influencing how the database kernel tasks are distributed among the operating system threads (parameter _TASKCLUSTER). If the computer is used purely as a database server, MAXCPU should be equal to the number of CPUs belonging to the computer; otherwise, the value should be reduced to relieve some CPUs for other applications.
In the SAP central system, the ratio for CPUs from database/SAP system should be: 1/3 - 1/5 of the existing CPUs are configured
In the case of a database-only server with up to 7 CPUs: 100% of available CPUs are used for the database.
In the case of a database-only server with up to 7 CPUs: 100% of available CPUs -1
For more information, see the FAQ Note 820824 and MaxDB training materials for WB550 in the section on Parameters.
13. What determines the number of threads created and which tasks are assigned to which thread?
The MAXCPU and MAXUSERTAKS parameters control the number of threads, and thus how the task cluster is set. The parameters _TASKCLUSTER_01, _TASKCLUSTER_02 and _TASKCLUSTER_03 specify how the tasks from the database kernel are distributed among the threads.
The task cluster should not be changed by the customer.
14. How should I interpret the setting for the TASKCLUSTER parameter?
The following example explains interpretation of the task cluster setting:
MAXUSERTASKS = 40, MAXCPU = 2
In this example, 40 user tasks are configured. The database kernel can distribute these user tasks among 2 threads.
This results in the following task cluster:
TASKCLUSTER_01 = 'tw;al;ut;2000*sv;10*ev,10*gc;'
_TASKCLUSTER_02 = 'ti,100*dw;20*us;'
_TASKCLUSTER_03 = 'equalize'
Tasks between two semicolons are grouped together as one thread.
The trace writer, log writer and utility task each run separately in their own thread.
Up to 2,000 server tasks (practically all tasks) are grouped in one thread.
Garbage collectors and event tasks run together in one thread. Timers and up to 100 pager run in one thread.
The number of threads containing user tasks is limited to MAXUSERTASKS/2.
"equalize":_Usertasks are distributed as evenly as possible among various threads.
"compress":_The maximum number of user tasks is processed (20 in this case) in a single thread before a new thread is started.
#allinone#: All tasks run in one thread.
15. How can I change the number of dev threads?
The number of dev threads depends primarily on the number of volumes in the database instance. For each data volume and each log volume, the database system activates _IOPROCS_PER_DEV dev threads. The database system also activates a dev thread for the database trace volume.
16. What is a task switch?
Within a UKT, only one task can ever use the processor (have running status). All other tasks are either executable or they wait for an external event such as the end of an I/O operation and a relevant message before they are executable again.
When the current task no longer requires the processor, it initiates a task switch (cooperative task switch), and the next task can use the processor in the same user kernel thread.
More information about this topic is available is in the MaxDB documentation (Note 767598) under the keyword "Task" and in the MaxDB internal course WB550.
17. What is load balancing?
The operating system has no influence on how the database system distributes the tasks from a database instance among the available UKTs. Therefore it cannot intervene if, for example, a user kernel thread applies a 100% load on a processor, and the other three processors belonging to the computer are not used at all.
Within a user kernel thread, tasks operate in a cooperative manner. In other words, they decide when to suspend themselves and allow processing of the next task (see task switch). As a result of this behavior, a single task may block all other tasks in the same user kernel thread.
For optimum distribution of the tasks among the UKTs, the database system performs internal load balancing. In this way, the database system achieves more efficient resource utilization and short response times for the individual users, especially on computers using several processors.
For more information on load balancing, see the MaxDB documentation (Note 767598) under the keyword "load balancing". Refer also to the notes containing parameter recommendations on load balancing:
- liveCache instances: Notes 833216, 817884, 496318
- OLTP: Notes 814704, 767635
- BW: 901377
18. What database tool can I use to view how the database is using operating system resources?
The database tool XCONS (database console) is used to display the operating system resources used by the database system, the distribution of database sessions among threads and the status of active database sessions.
We recommend that you only use XCONS in productive systems for analysis if you have a thorough knowledge of database and operating systems.
XCONS is a command line tool. It can be used on all operating systems supported by the database system.
19. How can I display task states?
At the operating system level using the database console x_cons and the command show tasks: x_cons <DBNAME> show tasks
From the SAP system in transaction DB50/LC10 in the Task Manager using
current Status -> Kernel Threads -> Task Manager -> All Tasks.
For more details, see the MaxDB documentation.
20. Which task states are available and what do they mean?
The task state indicates whether a task is currently active and what action it is currently executing. The names of some task states were changed as of Version 7.7. The new names appear in parentheses after each task state.
AsynClose (Stream Close):
Task closes I/O ports after saving or restoring
Task determines parameter or initializes a backup device
Task executes asynchronous I/O operation (when saving or restoring)
Task opens I/O ports for saving or restoring
Task waits for the end of an I/O operation during a save or restore operation
Task transfers the result of a request to the application
Database session without request
free database session
Task executes a database procedure or a LCA object
Task initializes the trace file within the database
Task is in the initial status and has no resources (stack, for example)
Task generates an event
IO wait (W/R):
Task waits for the result of an I/O operation (W:write, R:read)
Task was locked when the kernel was shut down (to avoid rescheduling of this task)
short-term wait situation; task starts running again automatically after a specified period (microseconds).
Task is immediately executable, but was suppressed owing to long runtime or prioritizing of another task.
Task runs, uses CPU
short-term wait situation; task starts running again automatically after a specified period.
Task was suspended by kernel and waits before running again
Task or database session was terminated
Task state is unknown
Task opens I/O ports (volumes, normal operation)
Task waits to enter a protected region
Task closes I/O ports (volumes, normal operation)
Task determines the size of a volume or formats a volume
Vdualvectorio(Dual Vector IO):
Task executes a vector I/O operation (write or read) on two volumes in parallel
Vendexcl (Leave Region):
Task leaves a protected region
VenterRWRegion (Enter RWRegion):
Task waits to enter a protected region (reader/writer region)
Task closes a file
Vfopen (File Open):
Task opens a file
Vfwrite (File Write):
Task writes to a file
VleaveRWRegion (Leave RWRegion):
Task leaves a protected range (reader/writer region)
Vopmsg (Message Output):
Task writes a message to one of the following database files: knldiag, knldiag.err, opmsg[n]
Vrelease (Connect Close):
Task terminates the database session
Vshutdown (DB OFFLINE):
Task changes the operation status of the database instance from ONLINE (above offline) to ADMIN
Task is suspended and waits to be explicitly woken (Vresume) by another task. There are special Vsuspend wait statuses, each of which is displayed under its own name.
Vvectorio (Vector IO):
Task executes a vector I/O operation (write or read)
Task waits to be explicitly woken (Vsignal) by another task, for example, when the task waits for an SQL or object lock.
WaitForEvent (Wait For Event):
Task waits for event
21. What are the possible reasons for wait statuses on B* trees or page chains?
Wait statuses on B* trees should not appear for an extended period in the database console (x_cons) in a system that is performing efficiently. Wait statuses may appear on B* trees if lock requests on either the root page or a leaf page (at index or leaf level) are in conflict with a lock that has already been assigned.
a) Read access:
The following steps are carried out for a read access on a B* tree:
First, a read access is performed on the root of the B* tree. A shared lock is set on the root page.
If a DDL or DML command that is running simultaneously wants to lock the B* tree exclusively, it must request the root page exclusively, which causes a collision due to the existing shared lock. The task waiting on the exclusive lock is not woken until the shared lock on the root is released. If other tasks want to access the tree or root in the meantime, they are also suspended to prevent the exclusive lock request from having to wait 'for ever'.
Once the shared lock is approved, separators are used to determine the leaf where the searched record should reside. If the number (PageNo) of the page is fixed, a shared lock on the leaf is requested and a read access is performed for the lock in the data cache. This access may be delayed due to a collision with an exclusive leaf lock.
b) Change access:
In a similar procedure to the read operation described previously, the root of the B* tree is requested with a shared lock. The subsequent access on the relevant leaf is executed with an exclusive lock, which may collide with another shared lock at leaf level.
If you cannot carry out the change operation locally on the current leaf, a lock escalation takes place: in this case, the exclusive lock on the leaf and the shared lock on the root is released. An exclusive lock on the root is requested so that the previously identified cross-leaf operation can be executed. A new descent down the B* tree is then required as the structure of the B* tree may have changed and the path and neighboring nodes must be redefined for the target leaf.
c) Delete access:
Deletion of a B* tree requires an exclusive lock on the root. This lock should prevent another task from working on the B* tree. However, this lock does not result in synchronization with the savepoint to prevent any "hang operations" with the savepoint.
If the root page was already changed previously by another operation, it is written explicitly by the delete process to prevent the next savepoint from having to wait for completion of the delete operation.
22. Which wait statuses (VSUSPEND) exist on B* trees and what do they mean?
a) Wait statuses on B* trees that contain permanent data (tables):
A task tries to set a shared lock on the root page of a table and has to wait because of a lock conflict.
A task tries to set an exclusive lock on the root page of a table and has to wait because of a lock conflict.
A task tries to set a shared lock on a page (on index level greater than 1 or leaf level) of a table and has to wait because of a lock conflict.
A task tries to set an exclusive lock on a page (at index level greater than 1 or leaf level) of a table and has to wait because of a lock conflict.
A task tries to set a shared lock on a page (at index level greater than 1 or leaf level) of a table and must wait because of a lock conflict.
A task tries to set an exclusive lock on a page at index level = 1 of a table and has to wait because of a lock conflict.
b) Wait statuses on B* trees that contain temporary data
A task tries to set a shared lock on the root page of a temporary B* tree and has to wait because of a lock conflict.
A task tries to set an exclusive lock on the root page of a temporary B* tree and has to wait because of a lock conflict.
A task tries to set a shared lock on a page (index or leaf level) of a temporary B* tree and has to wait because of a lock conflict.
A task tries to set an exclusive lock on a page (index or leaf level) of a temporary B* tree and has to wait because of a lock conflict.
c) Wait statuses on B* trees of indexes
A task tries to set a shared lock on the root page of an index and has to wait because of a lock conflict.
A task tries to set an exclusive lock on the root page of a table and has to wait because of a lock conflict.
A task tries to set a shared lock on a page (index or leaf level) of an index and has to wait because of a lock conflict.
A task tries to set an exclusive lock on a page (index or leaf level) of an index and has to wait because of a lock conflict.
23. Which wait statuses (VSUSPEND) exist on page chains and what do they mean?
a) Wait statuses on page chains that contain object data:
A task tries to set a shared lock on the root page of a page chain of an object and has to wait because of a lock conflict.
A task tries to set an exclusive lock on the root page of a page chain of an object and has to wait because of a lock conflict.
A task tries to set a shared lock on a page of a page chain of an object and has to wait because of a lock conflict.
A task tries to set an exclusive lock on a page of a page chain of an object and has to wait because of a lock conflict.
a) Wait statuses on page chains that contain UNDO data:
The database system writes undo log entries (before images) for transactions that change data in the database to undo log files (page chains) in the data area. The undo log entries enables a transaction rollback or consistent views (for example, with liveCache database instances).
A task tries to set a shared lock on the root page of an UNDO page chain and has to wait because of a lock conflict.
A task tries to set an exclusive lock on the root page of an UNDO page chain and has to wait because of a lock conflict.
A task tries to set a shared lock on a page of an UNDO page chain and has to wait because of a lock conflict.
A task tries to set an exclusive lock on a page of an UNDO page chain and has to wait because of a lock conflict.
a) Wait statuses on page chains that contain history data:
History files contain references to the terminated transactions belonging to the undo log files.
At each savepoint, the database system writes the updated history files (page chains) to the data area.
For performance reasons, several history files are always open in parallel. The database system calculates the number of history files from the general database parameter MAXUSERTASKS. As of Version 7.6, the garbage collector check the history files at regular intervals of a few seconds. Once the garbage collectors have deleted the data that requires deletion, they also delete the relevant undo log files. This relieves the user task of the undo log files' delete operation. The database can send a faster confirmation of the commit/rollback to the application.
Collisions may occur on the history files between the user task and garbage collectors.
A task tries to set a shared lock on the root page of a history page chain and has to wait because of a lock conflict.
A task tries to set an exclusive lock on the root page of a history page chain and has to wait because of a lock conflict.
A task tries to set a shared lock on a page of a history page chain and has to wait because of a lock conflict.
A task tries to set an exclusive lock on a page of a history page chain and has to wait because of a lock conflict.
24. What do the task states 'DC OvFlow' and 'DB FULL' mean?
DC OvFlow: The data cache is used intensively at this time and cannot accept new data. The data cache can only start accepting data again when pages fromthe data cache have been written again to the volumes.
If you frequently observe DC OvFlow statuses:
- Check that your data cache has a configuration that is large enough
- Check whether the I/O system you are using is too slow
DB FULL: The database data area on the data volumes is full. The data area cannot accept any more pages. The database hangs. To resolve the problem, add a new data volume.
25. What do the task states 'LogQ FULL' and 'LOG FULL' mean?
LogQ FULL: This task state affects the LOG_IO_QUEUE, not the log area on the log volumes. The log queues (parameter LOG_IO_QUEUE) are currently used intensively and cannot accept any new redo log entries. The log queues can only start accepting redo log entries again when the log writer has written data from the log queues to the log area.
If you frequently observe the LogQ Full status, check the following:
- whether your log queues have a configuration that is too small (parameter LOG_IO_QUEUE)
- whether the I/O system you use that is located on the log volume is too slow.
LOG FULL: This task state indicates that the log area on the log volumes is full. Database operation stops because no more log entries can be written to the log volume. To solve this problem, you must save the log area. There is no point in using an ADD log volume in this situation.
Further information about the MaxDB log area is available in Note 869267 FAQ: MaxDB LOG area.
26. What are regions or critical sections in MaxDB?
A critical section is an area of the MaxDB code that must only be run by one instance (tasks/threads) at a particular time. Competing accesses are synchronized using special semaphores (regions, reader-writer locks).
27. Can I debug individual tasks?
Yes, you can use the console command 'debugtask' to write a Stack Back trace to knldiag. As of MaxDB Version 7.6.00 Build 10, this command is also available in UNIX.
x_cons <DBNAME> debugtask <TaskID>
For example: x_cons LCA debugtask 100
28. Where can I find more information on the subject of Runtime Environment?
For more information on the subject of MaxDB Runtime Environment, see the MaxDB documentation (Note 767598), the MaxDB Internals Workshop WB550 and the SDN via the following link: https://www.sdn.sap.com/irj/sdn/maxdb