Set up DELTA (DB Endpoint Latency Testing Ammeter) to test the latency from a Windows system using venv to an oracle database

 What is the DELTA?

DELTA (DB Endpoint Latency Testing Ammeter) is a tool or framework designed to measure the latency of database endpoints. It is used to assess the performance of database connections, helping developers and database administrators understand the time it takes for a database query to travel from the client to the database server and back.

This measurement is crucial for optimizing database performance, diagnosing latency issues, and ensuring that applications relying on the database operate efficiently.

Usage Scenarios:

• Database Performance Tuning: Developers and DBAs can use DELTA to tune database performance by identifying and resolving latency issues.
• Infrastructure Upgrades: When upgrading infrastructure or changing network setups, DELTA can help measure the impact on database latency.
• Application Development: During the development of applications that heavily rely on database interactions, DELTA ensures that the database performance meets the application's requirements.
• SLAs and Monitoring: Organizations can use DELTA to ensure that database latency stays within agreed-upon Service Level Agreements (SLAs).

To use DELTA on windows we need to install python and use venv.

What is venv?

venv (short for virtual environment) is a crucial tool in Python development that is used to create isolated environments for your Python projects. Each virtual environment has its own Python interpreter and a unique set of libraries and dependencies, ensuring that projects remain independent from one another and avoiding conflicts between different project requirements.

Prerequisites:

1.    Python: Make sure Python is installed on your system. Python 3.7 or higher is recommended.

2.    delta.py script. You can see the details in db-endpoint-latency-testing-ammeter, 

NOTE: In db-endpoint-latency-testing-ammeter, and in its examples DELTA is just used in the Linux systems, I will explain how to use it in Windows.

Deploy DELTA on Windows:

1.1.    Install and configure Python on Windows: I used python-3.12.3-amd64 in this article.

Click Install Now

We should add the Python installation path to the windows PATH environment variable.

Go to View advanced system setting => Advanced => Environment Variables

Add the Python installation path to the windows PATH environment variable.

Check the PATH is set correctly

1.2.    Creating a Virtual Environment (venv): To create a virtual environment on Windows, first I created c:\path\to\myenv on Windows then I used the following commands:

To use DELTA, we need to install some packages including numpy, cryptography, requests, oracledb, psycopg2  in our venv.

Install numpy 

Install cryptography

Install requests

Install oracledb

Install psycopg2

1.3.    Configuring and using delta.py: Now you must configure delta.py to use tcp for on-premises deployments (change tcps to tcp). However, if your Oracle database is in OCI, you should use tcps.

I edited the delta.py script and added the following then copied it to C:\path\to\myenv\Scripts, my database service name is src.amir.net, and it is an Oracle database installed on a VirtualBox VM with the IP address 192.168.56.10.

# Oracle Database credentials

oracle_un = 'system'

oracle_pw = '******'

oracle_cs = '(description= (retry_count=20)(retry_delay=3)(address=(protocol=tcp)(port=1521)(host=<IP Adress>))(connect_data=(service_name=src.amir.net))(security=(ssl_server_dn_match=no)))'

Now, we execute run delta.py command as follows:

python delta.py --db oracle --interval 3 --period 5 --csvoutput oracle_latency.csv

Let's interpret the output:

P99 Latency: This refers to the 99th percentile latency, which means that 99% of the queries had latencies equal to or below this value. In this case, it's 0.66 milliseconds.

P90 Latency: Similarly, this is the 90th percentile latency, indicating that 90% of the queries had latencies equal to or below this value. Here, it's 0.63 milliseconds.

Standard Deviation Latency: This represents the variability or dispersion of the latency values around the mean latency. A lower standard deviation indicates that the latencies are closer to the mean. In this case, it's 0.16 milliseconds.

Average Latency: This is the arithmetic mean of all the latency values. It's calculated by summing up all the latency values and dividing by the total number of samples. Here, it's 0.49 milliseconds.

Mean Latency: This is another term for the average latency, so it's also 0.49 milliseconds. These metrics provide insights into the performance of the system in terms of query latency. Lower values indicate better performance, as queries are being executed more quickly.