Performance Tuning
The following describes how to maximize the throughput using sorts and process cache.
To maximize the throughput for a number of the Basic I/O operations that operate on server tables, you must carefully plan the design of the table. The client process creates generic MariaDB tables. It makes no assumption about the data to be contained in the record other than what it determines from the Dictionary-IV Format. This is sufficient to describe the table to MariaDB, but under some conditions, sorting, for example this may not be enough.
MariaDB creates result sets that it generates from statements that specify ordering whether or not an index exists for the columns listed in the ORDER BY clause. Without an index this operation is very time consuming for large tables. Within the Basic environment this is multiplied since each access to the table may require an in-line sort. Basic saves the result set from a particular query and uses it if the subsequent query matches the one that generated the result. If it does not match, the result set must be regenerated the next time the query is executed.
The solution is to create an index for the columns to sort and define at least one column as NOT NULL (a mandatory field in Dictionary-IV). This must be done or the index is ignored for ORDER BY processing.
The create process translates mandatory fields and the first column of the primary key into columns with the NOT NULL constraint if PRM CREATWDBATTR is configured. If these columns are part of indices, you need no further changes.
Degradation due to indices being ignored becomes evident in:
Basic
using LKY( ), PKY( ), and I/O with sorts
Dictionary-IV File access with sorts (multi-record maintenance, view maintenance, sort order change) and backward reads in files (multi-record maintenance up arrow and page up)
Script-IV
READ PREVIOUS, LAST TO FIRST, sorts, etc.
Logon Cache
Section titled “Logon Cache”All access to the MariaDB database occurs through one login to the server using multiple cursors. When a LOCK, EXTRACT, or WRITE is performed in Basic a new login is executed to preserve key pointers to the other tables opened on the original login (LOCKs, EXTRACTs, and WRITE will perform commits and will clear all record locks). Once the LOCK, EXTRACT, or WRITE is complete, this login is terminated. The logon cache count parameter in the IPLINPUT file is used to specify the number of login processes to retain.
For example, if the logon cache count is set to 5, a pool of up to 5 SQL login processes will remain open. The next LOCK, EXTRACT, or WRITE executed by Basic will attach itself to one of these 5 processes bypassing the overhead to login and establish a new process.
By configuring this value properly, a balanced maximum can be maintained between excessive login processing and the number of active logins. A maximum of 20 logins can be cached.
From the TS DataServer for MariaDB Reference, version 8.9.1. Open this page in the PDF