Summary
This article deals with an issue that can cause S3 related queries to fails due to the connection pool being busy
Reported Issue
The server.log returns the following error:
ConnectionPoolTimeoutException: Timeout waiting for connection from pool
Relevant Versions Tools and Integrations
This can affect all Dremio releases.
Steps to Resolve
Generally here are a couple of settings used to tune (https://hadoop.apache.org/docs/stable/hadoop-aws/tools/hadoop-aws/performance.html) this connection:
fs.s3a.connection.maximum - Maximum number of HTTP connections (default 1000).
fs.s3a.threads.max - Threads in the AWS transfer manager (default 1000).
Increasing the number of threads means that more memory will be used.
To test increased values for these properties, one can create a new source and promote underlying PDS and then VDS and apply the fs.s3a.connection.maximum connection setting on this new source so that your current source is not affected. This way you can test the setting safely.
To increase the parameters for existing sources, one can use the Advanced Options for the source or the core-site.xml file.
If one uses Advanced Options, one should also use support key store.plugin.keep_metadata_on_replace to edit Dremio sources without losing metadata (including permissions). The steps are:
1. Under "Settings" -> "Support" add the support key: store.plugin.keep_metadata_on_replace Enable it then click "Save";
2. Go to your S3 source and add the following Connection Property:
Name: fs.s3a.connection.maximum
Value: 2000
3. Then save the changes on the S3 source (you will still see a popup message that this is a metadata impacting change, but it will not make any changes to the datasets in that source);
4. Go back to "Settings" -> "Support" and disable/reset the key store.plugin.keep_metadata_on_replace and save this change.
Remember to check the core-site.xml file and make sure the same properties are not defined there.