Performance

To demonstrate the effectiveness of the Sqlcommenter Hyperf library, we will conduct two distinct tests. The measurement will be performed in a controlled Google Cloud Run environment with the following configurations:

• CPU always allocated
• Minimum number of instances: 1
• Maximum number of instances: 1
• Memory per instance: 1GB
• Number of vCPUs per instance: 1vCPU
• Maximum concurrent requests per instance: 1000

To create the Cloud Run, you can execute the command below:

gcloud run deploy sample-sqlcommenter-hyperf-poc \
--image=reinanhs/sample-sqlcommenter-hyperf-poc:benchmarking \
--allow-unauthenticated \
--memory=1Gi \
--concurrency=1000 \
--min-instances=1 \
--max-instances=1 \
--set-env-vars=DB_HOST=$DB_HOST \
--set-env-vars=DB_DATABASE=$DB_DATABASE \
--set-env-vars=DB_USERNAME=$DB_USERNAME \
--set-env-vars=DB_PASSWORD=$DB_PASSWORD \
--set-env-vars=SQLCOMMENTER_ENABLE=1 \
--set-env-vars=GCP_PROJECT_ID=$GCP_PROJECT_ID \
--no-cpu-throttling \
--region=us-central1 \
--network=default \
--subnet=default \
--vpc-egress=private-ranges-only \
--project=$GCP_PROJECT_ID

There is complete documentation on how to recreate the environment used for the tests. In it, you'll find a detailed step-by-step guide, from setting up the database to configuring Cloud Run. Following this documentation is fundamental to understanding how the application was configured and how it behaved during the tests.

• Project configuration on Google Cloud

Performance analysis of the code block in the library

In the context of our application, a critical performance point is the code segment responsible for adding SQL comments to queries before their execution. This process is essential for the traceability and performance analysis of SQL queries, allowing queries to be monitored and analyzed more effectively.

To measure the performance of this operation, we use the microtime(true) function to capture the start and end times of the process. The difference between these times, multiplied by 1000, gives us the execution time in milliseconds. Here is the code snippet used for this measurement:

$start = microtime(true);
$query = $proceedingJoinPoint->arguments['keys']['query'];

/** @var Connection $dbInstance */
$dbInstance = $proceedingJoinPoint->getInstance();

$proceedingJoinPoint->arguments['keys']['query'] = $this->appendSqlComments($query, $dbInstance->getDriverName());

$end = microtime(true);
$time_elapsed_ms = ($end - $start) * 1000;

$this->logger->debug(sprintf("Execution time Sqlcommenter code: %.2f ms", $time_elapsed_ms), [
    'execution_time' => $time_elapsed_ms,
]);

You can find the library with the version of the code above, specifically created for performance testing, through the following command:

composer require reinanhs/sqlcommenter-hyperf:dev-test/benchmarking

Note: This version is intended to be used for performance testing only.

Investigation results

After collecting 10,000 execution time records for this operation, we calculated the average execution time, which was approximately 0.103 milliseconds (ms). This value indicates that inserting SQL comments into queries is an extremely fast operation, adding negligible overhead to the total query execution time.

To obtain this value, we created a Cloud Run environment as mentioned in the initial topics, detailing the specifications of the test execution environment. We used the K6 tool to make multiple requests to our test code. After multiple calls, we analyzed the generated logs. Below is an example of the filter used to analyze this information within Google Cloud:

Using the Cloud Logging tool for analysis, we downloaded the last 10,000 records issued by the library related to execution time.

With this downloaded information, we could analyze and perform some operations to calculate the average execution time. The code below was used to calculate the average:

<?php

// Reads the content of the JSON log file
$logContent = file_get_contents('./downloaded-logs-20240712-220042.json');

// Decodes the JSON content into an associative array
$logArray = json_decode($logContent, true);

// Gets the total number of items in the log array
$totalLogs = count($logArray);

// Initializes the variable for the sum of execution times
$totalExecutionTime = 0;

// Iterates over each log to sum the execution times
foreach ($logArray as $logEntry) {
    // Gets the execution time of the current log and converts it to float
    $executionTime = (float) $logEntry['jsonPayload']['context']['execution_time'];
    // Adds the execution time to the total sum
    $totalExecutionTime += $executionTime;
}

// Calculates the average execution time
$averageExecutionTime = $totalExecutionTime / $totalLogs;

// Displays the average execution time
var_dump($averageExecutionTime); // float(0.10319230556488038)

?>

Conclusion

The analysis of the results demonstrates that the efficiency of the SQL comment insertion code is essential to ensure that our system maintains high performance even with the addition of detailed tracking. With an average of only 0.103 ms per operation, we can state that this code segment is optimized and does not negatively affect the overall system performance.

Request latency performance test

The goal of this test is to evaluate the impact of the Sqlcommenter Hyperf library on request response times in a real-world usage scenario. Unlike the isolated code block test, this test analyzes the overall system performance under load by measuring request latency with the library enabled and disabled.

For this test, we will use the same Cloud Run configurations specified earlier, and the K6 tool to simulate multiple virtual users (VUs).

K6 script to measure requests per second

The following script will be used to calculate the number of requests per second handled, varying the number of virtual users each minute. The test duration will be 1 minute per stage, and the number of virtual users will gradually increase:

import http from 'k6/http';
import { check } from 'k6';

export const options = {
    stages: [
        { duration: '1m', target: 50 },
        { duration: '1m', target: 100 },
        { duration: '1m', target: 200 },
        { duration: '1m', target: 300 },
        { duration: '1m', target: 400 },
        { duration: '1m', target: 500 },
    ],
    thresholds: {
        http_req_duration: ['p(95)<3000'], // 95% of requests must complete within 3 seconds
    },
};

export default function () {
    const result = http.get(`${__ENV.APP_HOSTNAME}/departments`);

    check(result, {
        'http response status code is 200': (r) => r.status === 200,
    });
}

• .k6/performance-script.js

Test scenarios

1. Scenario with the library enabled:

   • We run the K6 script with the environment variable SQLCOMMENTER_ENABLE set to true.
   • We monitor the number of requests per second handled at each load stage.

2. Scenario with the library disabled:

   • We redeploy the application with the environment variable SQLCOMMENTER_ENABLE set to false.
   • We run the same K6 script and monitor the results.

After running the tests, we generated the following comparative graph:

Analysis of results

Analyzing the graph above, we can see that initially, the response times are very similar for both configurations. However, as CPU consumption increases and we approach the 1 vCPU limit, the disabled configuration (False) starts to perform slightly better.

Examining the CPU utilization graphs, we observe that around 400 VUs, CPU usage was approximately 98% for both configurations. When the library is not intensely competing for CPU usage, it maintains very good performance, close to the disabled configuration. This suggests that under high-demand conditions, the disabled configuration can handle the load slightly better, resulting in a marginal increase in the number of requests served per second.

Creating the Graph

The graph was generated by comparing the K6 test results between the two scenarios using the requests per second metric. Here is an example of where this information was extracted:

After collecting and analyzing the data from each scenario, we used the following Python code to create the graph:

import matplotlib.pyplot as plt

vus = [50, 100, 200, 300, 400, 500]
true_values = [84.701, 159.357, 275.756, 361.425, 408.281, 435.863]
false_values = [84.986, 162.248, 279.058, 364.601, 415.128, 442.876]

plt.plot(vus, true_values, marker='o', label='True', color='blue')
plt.plot(vus, false_values, marker='o', label='False', color='red')

plt.title('Sqlcommenter Hyperf - SQLCOMMENTER_ENABLE')
plt.xlabel('Number of VUs')
plt.ylabel('Requests per second')
plt.legend(loc='upper left')

plt.show()

Conclusion

The performance test analysis revealed that the Sqlcommenter Hyperf library has a minimal impact on performance under moderate to high load. While the disabled configuration (SQLCOMMENTER_ENABLE=false) shows slightly better performance under high demand, the difference is marginal. Therefore, the benefits of detailed tracking provided by the library generally outweigh the small overhead introduced, making it a valuable addition for observability and debugging of applications.