How to Resolve 504 Gateway Timeout in Oracle for Bold BI Deployed in Linux Environments

Published:

When working with large datasets in Bold BI, users may experience a 504 Gateway Timeout error due to the query execution time exceeding the default timeout values. This article will guide you potential solutions.

Cause

The default timeout values in Bold BI and Nginx may not be sufficient for handling large datasets with an Oracle data source, leading to a 504 Gateway Timeout error.

Solution

1. Increase Timeout Values

To resolve the 504 Gateway Timeout error, consider increasing the timeout values in both Bold BI and Nginx.

Bold BI:

Enter the server details in the data source connectivity page.
Change the command time value to 600 from 300.

Nginx:

Edit the boldbi-nginx-config file from the following location: /etc/nginx/sites-enabled
Edit the timeout value to 600 and save it.
Run the following command to restart Nginx: sudo systemctl restart nginx

For more information on changing the Nginx timeout, refer to the Bold BI help document

2. Optimize Oracle SQL Queries

To further improve the performance of your dashboard when working with large datasets in Bold BI with an Oracle data source, consider optimizing your SQL queries. Some tips for optimizing Oracle SQL queries include:

Use the appropriate indexes to speed up query execution.
Limit the number of rows returned by using the ROWNUM or FETCH FIRST clauses.
Use the EXISTS clause instead of IN or NOT IN when checking for the existence of rows in a subquery.
Use the Oracle optimizer hints to guide the optimizer in choosing the best execution plan.
Analyze the execution plan of your query using the EXPLAIN PLAN statement to identify potential performance bottlenecks.

By following these steps, you can optimize the query execution time and improve the performance of your dashboard when working with large datasets in Bold BI with an Oracle datasource.