Database Performance Monitoring Job

This page demonstrates codebeamer's database performance measuring function, describing the related predefined queries and their correlation.

Database Performance Monitoring Job

System Administrators can use this tool to evaluate the stability and performance between codebeamer and codebeamer's database.

Available at System Admin ► Database Performance job page menu.

This function enables periodic and manual execution of predefined and custom steps which measure response times of the database in various scenarios. During the process, both the internal library and the external CLI JAR are executed.

Results

The results can be seen in multiple views:

• Main results table -shows the aggregated view.
  • This consists of rows for each step and columns for multiple metrics: the last execution time, minimum, maximum and average values for two intervals (3 days and 30 days). Deviation percentages are also shown between last run and average values.
  • External rows are tagged with the source where it was run. (Default tag: External)
  • Each row is also highlighted if the measured values exceed the reference value for the query.
  • The system also annotates the Last run and Avg (ms) Last 3 days values with colors to show if they are within the reference values: green = the value is normal; red = the execution took longer than expected, this can indicate a possible database, system or network issue.
    
    
• Results per query - similar to the main table, but shows each step separately with the corresponding execution history. The executed query is also displayed here.

Predefined Queries

• Get Connection creation in Java:
  • Description - measuring the time elapsed during retrieving a database connection from pool (only relevant when the tool is used as library, not relevant with the external run because in that case connections are created).
  • Affected by - application server to database network speed.
  • 
    
• New Connection creation in Java:
  • Description - measuring the time elapsed during creating a NEW database connection.
  • Affected by - application server to database network speed.
    
    
• CPU heavy query:
  • Description - executes a prime number calculation on the database server which creates heavy load on the server processors.
  • Affected by - database server speed (CPU performance).
    
    
• Insert CLOB 500 times:
  • Description - tests the CLOB (character large object) write speed of the database engine. Test data is transferred through the network between the application server and the database.
  • Number of elements - 500 pieces of long text values (each 20,000 characters long).
  • Affected by - application server to database network speed, database server speed (I/O).
• Insert CLOB 500 times into table with primary key:
  • Description - tests the int - CLOB (character large object) pair write speed of the database engine. Test data is transferred through the network between the application server and the database.
  • Number of elements - 500 pairs of numbers (from 0 to 499) and long text values (each 20,000 characters long).
  • Affected by - application server to database network speed, database server speed (I/O).
    
    
• Read CLOB:
  • Description - tests the CLOB (character large object) read speed of the database engine. Test data is transferred through the network between the application server and the database.
  • Number of elements - 500 pieces of long text values (each 20,000 characters long).
  • Affected by - application server to database network speed, database server speed (I/O).
    
    
• Insert CLOB 500 times from stored procedure:
  • Description - similar to Insert CLOB 500 times query, but this does NOT transfer any data between the application and database servers. A stored procedure generates values and persists them purely on database server side. This test can be used in conjunction with the Insert CLOB tests to reveal network issues.
  • Number of elements - 500 pieces of long text values (each 20,000 characters long).
  • Affected by - database server speed (I/O).
    
    
• Read CLOBs from stored procedure:
  • Description - similar to Read CLOB queries, but this does NOT transfer any data between the application and database servers. A stored procedure reads back all of them purely on database server side. This test can be used in conjunction with the Read CLOB tests to reveal network issues.
  • Number of elements - 500 pieces of long text values (each 20,000 characters long).
  • Affected by - database server speed (I/O).
    
    
• Insert CLOB 500 times from stored procedure {Measured execution inside the procedure}:
  • Description - same logic as Insert CLOB 500 times from stored procedure query, this is the value which is measured inside the application and will be used to calculate the latency in the Latency of Insert CLOB 500 times from stored procedure {Measured execution inside the procedure}.
  • Affected by - database server speed (I/O), application server to database network speed.
    
    
• Latency of Insert CLOB 500 times from stored procedure {Measured execution inside the procedure}:
  • Description - same logic as Insert CLOB 500 times from stored procedure query, but another execution time is also measured inside the procedure and subtracted from the value measured in the application to show the latency between the application and the execution of the actual procedure logic.
  • Affected by - database server speed (I/O), application server to database network speed.
    
    
• Insert CLOB 500 times from stored procedure into table with primary key:
  • Description - similar to Insert CLOB 500 times into table with primary key query, but this does NOT transfer any data between the application and database servers. A stored procedure generates values and persists them purely on database server side. This test can be used in conjunction with the Insert CLOB tests to reveal network issues.
  • Number of elements - 500 pairs of numbers and long text values (each 20,000 characters long).
  • Affected by - database server speed (I/O).
    
    
• Insert BLOB 500 times:
  • Description - tests the BLOB (binary large object) write speed of the database engine. BLOB type is used with non-textual data, like images and other file attachments. Test data is transferred through the network between the application server and the database.
  • Number of elements - 500 pieces of binary objects (each 20,000 bytes in length).
  • Affected by - application server to database network speed, database server speed (I/O).
    
    
• Read BLOB:
  • Description - tests the BLOB (binary large object) read speed of the database engine. BLOB type is used with non-textual data, like images and other file attachments. Test data is transferred through the network between the application server and the database.
  • Number of elements - 500 pieces of binary objects (each 20,000 bytes in length).
  • Affected by - application server to database network speed, database server speed (I/O).
    
    
• Insert one row, count rows table then clear the table:
  • Description - this is a basic test which executes a set of minimal cost operations on the database. The speed of this step reflects the network layer latency time between the application and database server.
  • Number of elements - single row.
  • Affected by - application server to database network speed.
    
    
• Write 'varchar(255)' 500 times:
  • Description - tests insertion of varchar data type.
  • Number of elements - 500 varchar elements.
  • Affected by - database server speed (I/O).
    
    
• Read 'varchar(255)' 500 times:
  • Description - reading back of varchar data types.
  • Number of elements - 500 varchar elements.
  • Affected by - database server speed (I/O).
    
    
• Write 'integer' 500 times:
  • Description - tests insertion of integer data type.
  • Number of elements - 500 integer elements.
  • Affected by - database server speed (I/O).
    
    
• Read 'integer' 500 times:
  • Description - reading back of integer data types.
  • Number of elements - 500 integer elements.
  • Affected by - database server speed (I/O).
    
    
• Write 'datetime' 500 times:
  • Description - tests insertion of datetime data type.
  • Number of elements - 500 datetime elements.
  • Affected by - database server speed (I/O).
    
    
• Read 'datetime' 500 times:
  • Description - reading back of datetime data type.
  • Number of elements - 500 datetime elements.
  • Affected by - database server speed (I/O).
    
    
• Write 'integer' 500 times with primary key:
  • Description - tests insertion of integer data type.
  • Number of elements - 500 integer elements.
  • Affected by - database server speed (I/O).
    
    
• Write 'integer with index' 1000 times:
  • Description - tests insertion of integer data type.
  • Number of elements - 500 integer elements.
  • Affected by - speed, database server speed (I/O).

Correlation

1. Queries that are affected only by database server speed:
   • CPU heavy query
   • Insert CLOB 500 times from stored procedure
   • Read CLOBs from stored procedure
   • Insert CLOB 500 times from stored procedure into table with primary key
   • Write 'varchar(255)' 500 times
   • Read 'varchar(255)' 500 times
   • Write 'integer' 500 times
   • Write 'datetime' 500 times
   • Read 'datetime' 500 times
   • Write 'integer' 500 times with primary key
   • Write 'integer with index' 1000 times
     
     
2. Queries that are affected only by application server to database network speed:
   • Get Connection creation in Java
   • New Connection creation in Java
   • Insert one row, count rows table then clear the table
     
     
3. Queries that are affected by database server speed and by application server to database network speed:
   • Insert CLOB 500 times
   • Insert CLOB 500 times into table with primary key
   • Read CLOB
   • Insert CLOB 500 times from stored procedure {Measured execution inside the procedure}
   • Latency of Insert CLOB 500 times from stored procedure {Measured execution inside the procedure}
   • Insert BLOB 500 times
   • Read BLOB.

• If every query is slow, the problem will be with the application server to database network speed, and with the database server speed.

Performance CLI Tool

For more information, see: Performance CLI tool