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.
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.
Be the first to comment