Using Measure to monitor NonStop SQL performance

Introduction

Applications that use relational databases take advantage of the powerful Structured Query Language (SQL) that expresses only what data is required but not how this data needs to be accessed.  This takes away the effort to know the data structures and access methods of the data from a developer, however, on the other hand a performance analyst may have to spend the time to figure out how an application is accessing a database. Fortunately, the HPE MEASURE subsystem can collect data specifically from SQL operations. This allows for the correlation of SQL performance with system performance in a single tool.

This article describes which entities in MEASURE need to be included to provide a meaningful and correct application profile.

Measuring SQL applications

Three Measure entities are used to examine application performance related to the SQL activity of that application. Entities that are recorded for each process are:

  • SQLPROC, for reporting SQL compilation activity of that process.
  • FILE, reporting activity at the level of SQL tables and indexes.
  • SQLSTMT, reporting activity per SQL Statement

Per process CPU utilization of application processes is also important to measure, however, it depends on the type of application which processes are of interest. Some applications may even run off-platform.

A typical measurement should include the SQLPROC and FILE entities as will be explained.

In addition, when the measurement is started, “SQL” must be specified as an argument to the start command, which will add an SQL segment to the measure data file. The data in this SQL segment allows measure to correlate the ANSI SQL names to their names in the NonStop file system and this is crucial information for performance analysts.

The Measure SQLPROC entity

The SQLPROC entity provides information about SQL processes and how they interact with the SQL compiler. In typical OLTP applications, the SQLPROC entity should not show much activity because statements should be compiled or recompiled only once in the lifetime of a process to reduce system overhead.

Note: When cached database connections are repeatedly closed and re-opened, cached statements may not have to be recompiled. However, changing connection defaults (such as the auto-commit mode) may result in unexpected interactions with the compiler, increasing the SQL-Stmt-Compiles counter, and revealing increased compiler activity.

While the information that it records is useful for detecting excessive statement compilations, which can impact application performance negatively, this is not the only reason why you should consider adding SQLPROC to a measurement.

Figure 1: SQLPROC information

LIST SQLPROC *
SQL Proc 1,780     ($Y5QF)    Pri 148        OSSPID:  1560281418
Program  $SYSTEM.ZMXODBC.MXOSRVR
OSSPath: "/G/system/zmxodbc/mxosrvr"
Userid   255,255   Creatorid  255,255   Ancestor 2,632 ($MXOAS)
Format Version:  J06  Data Version:  J06  Subsystem Version:  2
Local System \TSEK    From  16 Jan 2024,  4:38:09   For    11 Minutes
----------------------------------------------------------------------
SQL-Obj-Recompiles
SQL-Obj-Recompile-Time
SQL-Stmt-Recompiles
SQL-Stmt-Recompile-Time
SQL-Stmt-Compiles                 32 #
SQL-Stmt-Compile-Time           1.33 sec
SQL-Newprocesses
SQL-Newprocess-Time
Opens                             42 #
Open-Time                       5.59 ms

During the test, the application issues 32 unique statements and they need to be compiled once as is shown in SQL-Stmt-Compiles in figure 3. They are executed thousands of times, but they are only compiled once during the lifetime of the application.

When SQLPROC is included, it also triggers the system to update the SQL information for optimized SQL statements to be recorded in the FILE entity for SQL files. Therefore, it is highly recommended to include SQLPROC when measuring SQL applications.

The Measure FILE entity

The FILE entity in Measure contains valuable information for a performance analyst.

Figure 2: FILE information

list file 'table frans.benchmarksql.new_order'
File Open $SD0104.ZSDFJ003.F2XTGD00:1316774467         Open Type SQLFILE
ANSISQL: 'TABLE FRANS.BENCHMARKSQL.NEW_ORDER'
Device Name $SD0104           Device Type  3  (Disk)
Opener 1,731    ($Y79T)       File Num 12      OSSPID:  1073742061
Program  $SYSTEM.ZMXODBC.MXOSRVR
OSSPath: "/G/system/zmxodbc/mxosrvr"
Opener Device Name $SYSTEM
Format Version:  J06  Data Version:  J06  Subsystem Version:  1
Local System \TSEK    From  17 Jan 2024,  1:27:39   For     5 Minutes
------------ Requests ------------------------------------------------
File-Busy-Time                         Disc-Reads
Reads                      18,440 #    Writes                 20,090 #
Deletes-or-Writereads      18,440 #    Updates-or-Replies
Timeouts-Or-Cancels                    Info-Calls
Misc-Calls
------------ Logical I/O ---------------------------------------------
Messages                   56,970 #    Message-Bytes      11,799,280 #
Read-Bytes                             Write-Bytes
DBIO-Reads                             DBIO-Writes
DBIO-Read-Bytes                        DBIO-Write-Bytes
Lock-Waits                             Escalations


------------ SQL -----------------------------------------------------
Records-Accessed           56,970 #    Records-Used           56,970 #

The information of interest for SQL performance are the fields Records-Accessed and Records-Used because they return information about plan quality for SQL operations. In the example, 20,090 inserts were done to this table, as indicated by the Writes field, in addition to 18,440 deletes and 18,440 reads. In this application, all operations provide the primary key of the table, and therefore, these statements are efficient, and no additional rows are read and match the selection criteria placed in the WHERE clause of the query.

IMPORTANT:

The SQLPROC or SQLSTMT entity must be measured to provide accurately filled entity data. If not, some fields are not maintained for OLTP “optimized lean” operations in SQL/MX execution plans.

These optimized operations are shown in a plan in the OPT column as “ol”. Operations that can be optimized this way are insert, file-scan-unique, unique-delete and unique-update.

Figure 3: “Optimized lean” operations

Statement: SQLMX_DEFAULT_STATEMENT_20

LC   RC   OP   OPERATOR              OPT       DESCRIPTION    CARD
---- ---- ---- --------------------  --------  ------------ ----------
2    .    3    root                  o         r             1.00E+000
1    .    2    partition_access      o                       1.00E+000
.    .    1    insert                ol        NEW_ORDER     1.00E+000

The Measure SQLSTMT entity

The SQLSTMT entity is used to record per-statement statistics. There is one SQLSTMT entry for each statement that is executed by a SQL process. This can lead to a lot of data being collected depending on how statements are used by programs. For example, dynamic queries create an entry for every invocation of the statement, whereas prepared queries will record multiple invocations in one SQLSTMT entry. If prepared queries are used, but statement caching is not enabled, each prepare (which invokes the SQL compiler) will create a SQLSTMT record in the Measure datafile.

Figure 4: SQLSTMT information

SQL Statement
Procedure '/home/hp/frans/jtpcc/modules/MFCABS1031FRANS.BENCHMARKSQL.190D6CB40EFED3A7B88152455C463A2B_121' Index #18
Process  1,731     ($Y79T)    Pri 148        OSSPID:  1073742061
Program  $SYSTEM.ZMXODBC.MXOSRVR
OSSPath: "/G/system/zmxodbc/mxosrvr"
Userid   211,96    Creatorid  211,96    Ancestor 2,632 ($MXOAS)
Format Version:  J06  Data Version:  J06  Subsystem Version:  0
Local System \TSEK    From  17 Jan 2024,  1:27:39   For     5 Minutes
----------------------------------------------------------------------
Elapsed-Busy-Time          652.38 ms   Calls                 20,090 #
Elapsed-Sort-Time                      Sorts
Elapsed-Recompile-Time                 Recompiles
Messages                   20,090 #    Message-Bytes      1,767,920 #
Lock-Waits                             Escalations
Timeouts                               Disc-Reads
Records-Accessed           20,090 #    Records-Used          20,090 #

The SQLSTMT entity provides detailed information about the statement. The example shows the information of a simple DELETE statement that was called 20,090 times during the 5-minute interval. Because the statement deletes unique rows based on the primary key, records-accessed and records-used are equal to the number of calls.

These operations are included in the FILE entity combined with operations on the same SQL table by other statements.

Note:

Because of the granularity of the SQLSTMT information, some performance impact may be experienced. As explained in the next section, how much impact will be application-specific? Measuring SQLSTMT for dynamic (ad-hoc) SQL applications that execute unique queries that include literals in the WHERE clause is not recommended.

When SQLSTMT is measured, SQLPROC entities are also created by Measure.

Performance impact SQL FILE information

To examine the performance impact to SQL-specific measurements I used a Java application that is modeled after the TPC-C order benchmark.

The tests were executed on a 4 processor 4 core NonStop NS7/X2 system with the L23.08 OS version and NonStop SQL/MX version 3.8.2. The Java version was OpenJDK 11.0.8.

The Java application runs in a virtual machine (JVM) and simulates one terminal that executes the TPCC transaction mix and runs in CPU 3. It uses the JDBC Type 4 driver to connect to the database on the same system; however, the mxosrvr processes run in CPU 1. This way, the Java application and the database processes do not compete for CPU resources.

Figure 5: Test database information

TABLE_NAME                               STATS_ROWCOUNT
---------------------------------------- --------------------
CUSTOMER                                              1500000
DISTRICT                                                  500
HISTORY                                               1500000
ITEM                                                   100000
NEW_ORDER                                                  81
OORDER                                                1500000
ORDER_LINE                                           15001962
STOCK                                                 5000000
WAREHOUSE                                                  50

The tables are hash-partitioned over just two volumes ($SD0103 and $SD0104). Both volumes have their primary disk processes in CPU 1. The total database size is approximately 6 GB.

Each test runs for 10 minutes and executes transactions without think-time on a single database connection. The TMF transaction rates, calculated over a 5-minute interval, vary between 135 and 160 TPS between tests. Measure data was captured for the CPU, PROCESS, TMF, FILE, DISCOPEN. DISKFILE, OSSCPU and OSSNS entities.

It is not well-known that the FILE entity data for SQL files is not complete unless SQLPROC or SQLSTMT entities are also measured. However, historically, the recommendation has been not to use SQLSTMT because of the perceived performance impact. However, for an application like JTPCC, which uses common practices like connection pooling and prepared statements, the performance impact is quite small.

To identify the exact impact requires a dedicated system that is just executing the specific application to be examined. In a less ideal situation, such as the lab system that was used for this JTPCC test, the following calculation was made:

Without SQL-specific measurement, the total system CPU utilization was calculated and divided by the number of business transactions, which gives a reasonable number of CPU seconds per transaction, and it includes some system overhead. Such overhead for other processes running in the system was limited to less than 5 %.

Thus calculated, the CPU cost of one business transaction, which access about 114 SQL rows, is 6.82 milliseconds.

When SQLPROC is added, the CPU cost of one business transaction increased to 7.07 milliseconds, an increase of 3.6%.

When SQLSTMT is added instead of SQLPROC, the CPU cost increases by 5.5% to 7.20 milliseconds.

Conclusion and recommendations

The performance of SQL applications depends also on the SQL database activity. Measure can collect detailed information on SQL activity at the level of the application processes, per SQL object, the tables, and indexes, and even at the statement level within the application processes.

The Measure FILE entity will not contain all SQL operations if SQL process (SQLPROC) or detailed statement (SQLSTMT) information is not measured. These entities are often not included due to the perceived performance impact. However, since SQLPROC only adds a few percent overhead it is recommended to include SQLPROC in measurements that include the FILE entity.

It is good practice to examine SQLPROC data to detect excessive SQL compilation activity.

The SQLSTMT entity provides detailed information at the statement level. However, if an application creates new statement records by dynamic, unique statements, or the application does not use statement caching, this will lead to large measure data files, and possibly Measure errors like “004024 Cid Table Overflow”.  However, when statements are prepared and cached, the SQLSTMT entity will record how many times the statement was executed and it provides performance information like rows accessed, lock waits and -escalations to partition locks. With an increase of about 5%, the performance impact of measuring SQLSTMT is a bit higher than that of SQLPROC. It does not add value to include both SQLSTMT and SQLPROC in the same measurement, but the performance impact is higher.

Author

  • Frans Jongma

    Frans Jongma is a Master Technologist for the NonStop Advanced Technology Center (ATC) and is based The Netherlands. Frans has worked in several consulting positions for the NonStop Enterprise Division since 1989. His main areas of expertise are: NonStop SQL application design, performance analysis and high availability. Prior to joining Tandem, Frans has worked on the design and implementation of database management systems and developer productivity tools for UNIX and proprietary systems. As part of the ATC Frans has been involved in proving the concept of SQL/MX as a Service which resulted in the SQL/MX DBS product and an HTML-based prototype of a web-based user interface to SQL/MX DBS. This HTML version is the basis of WebDBS, the GUI interface for administrators and users of NonStop SQL/MX DBS.

Be the first to comment

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.