Development » aiaxio

API Performance Optimization¶

Recommendation API¶

Description:

This issue is created to track the performance improvements made to the Recommendation API's response times after adjusting the PostgreSQL connection pool configuration.

Problem:

Before optimization, the response time for the Recommendation API under load was significantly high, with a p(95) latency of 1086.48 ms. After experimenting with various configurations, especially with the connection pool, a significant improvement was observed in the response times.

Optimization Strategy:

• Parallel Query Execution using Promise.all():

  • In the Recommendation Controller, both the main query (for fetching recommendations) and the count query (for total record count) are executed in parallel using Promise.all(). This ensures that both queries are running concurrently, which reduces the overall time spent waiting for the database to return results.

• Setting max_parallel_workers_per_gather to 1:

  • By setting max_parallel_workers_per_gather to 1, we limited the number of parallel workers that could be used for queries, reducing CPU contention and overhead from parallel execution, which helped improve overall response time and stability under load.

• Pool Configuration was the key area of focus. Initially, we didn't use any pooling strategy. Each time a database call was made, a new connection was created. This caused increased latency on api response time.

• Previous Pool Config (before optimization):

  • No configuration

• Optimized Pool Config:

  • Max connections: 10
  • Idle timeout: 30 seconds
  • Connection timeout: 2 seconds

Performance Improvement (before and after optimization):

Detailed Explanation of Performance Gains:

1. Reduced Max Connections:

   • Reducing the max connections in the pool to 10 helped in reducing contention over the database connections and allowed the application to process requests without waiting for unused connections to be freed.

2. Lower Latency:

   • HTTP Request Duration saw a significant reduction, with a 31.2% improvement in average latency and 26.8% in p95 response time. This improvement was due to better database connection management, preventing connection saturation.

3. Improved Throughput:

   • The Total Requests increased by 10.5%, indicating that the application could handle more requests concurrently due to the optimized pool size and better resource management.

4. Efficient Resource Utilization:

   • By reducing the max connections, the backend resources (CPU, memory) were used more efficiently, leading to faster processing of requests and reduced load on the server.

Additional Observations:

• Blocked Time (HTTP_Request_Blocked) remained very low, indicating that there was no significant blocking in processing requests, even as the load increased.
• Connection Handling: With the optimized connection pool, the time spent on establishing connections (HTTP_Request_Connecting) was very minimal, ensuring smooth and efficient database communication.