Hi @fernando-b .
Could you tell me where I should set these environment variables, please? I tried to do it into docker.env but this didn't solve my problem. Also could you tell me where did you find such environment variables? Unfortunately, I didn't find them in documentation: Environment variables reference | Retool Docs
Hi @irinavyshnikova,
The SEQUELIZE_ environment variables will modify how connections to the Retool Storage DB are handled by Retool. Is that the issue you're looking to solve? What is the behavior that you're seeing? Did you restart containers after setting the env vars?
1 Like
Hi Tess,
I appreciate your response. Yes, I restarted the containers after adding these environment variables, but this didn't help. I am using an Oracle database in my Retool application, not Retool Storage DB. I often encounter a situation where SQL queries in Retool run slower and slower and then stop working altogether. Only rebooting the instance helps in such situations. Do you know what could be the reason for this behavior? Could you tell me if there is a solution for this issue when using an Oracle database, please?
Hi @irinavyshnikova Can you send us the container logs from when you're running into this issue?
Is there any pattern such as it always happens when the app has a lot of end users, or it always happens on Monday mornings, etc?
Another thing that could be helpful to see is the query performance popover data (Monitor query performance | Retool Docs)
Lastly, is this happening with Read queries? If so, how many records are you typically querying?
Hi @Tess,
It's difficult to pinpoint exactly when the issue occurs. Even if the application is just opened and no work is being done, the queries gradually become slower and slower. I've noticed something in the logs. We have a large database, and I've never seen the schema fully downloaded; the icon always shows that the schema is downloading.
Maybe turning off schema fetching in Retool can help improve performance. Can I do this? What steps should I follow to achieve it?
Thank you.
Logs:
"{"endpoint":{"path":"/api/resources/:resourceName/schema","team":"@tryretool/connect"},"level":"warn","msg":"Error fetching schema: Request timed out","organization":{"id":1,"name":"nick@organisation.com"},"pid":84,"requestId":"31a18fbf-600d-4e4f-9bd7-d7045a514664","stack":"Error: Error fetching schema: Request timed out\n at /retool_backend/bundle/main.js:3217:60449\n at process.processTicksAndRejections (node:internal/process/task_queues:95:5)","timestamp":"2024-05-23T13:48:46.510Z","type":"CAUGHT_ERROR","user":{"email":"nickname@org.com","sid":"user_8578b8c7d9a6431c888e7ff905733b71"}}
{"endpoint":{"path":"/api/resources/:resourceName/schema","team":"@tryretool/connect"},"error":{"level":"warn","msg":"Error fetching schema: Request timed out","stack":"Error: Error fetching schema: Request timed out\n at /retool_backend/bundle/main.js:3217:60449\n at process.processTicksAndRejections (node:internal/process/task_queues:95:5)","type":"CAUGHT_ERROR"},"level":"info","message":{"http":{"method":"GET","request":{"time":603.470426182},"status_code":400,"url_base":"http://apps-main-23linux:3006","url_path":"/api/resources/e02e93e5-6a74-4d7b-b054-0608336c1450/schema?environment=production&action=force"},"type":"REQUEST_FINISH"},"organization":{"id":1,"name":"nick@organisation.com"},"pid":84,"requestId":"31a18fbf-600d-4e4f-9bd7-d7045a514664","timestamp":"2024-05-23T13:48:46.511Z","user":{"email":"nickname@org.com","sid":"user_8578b8c7d9a6431c888e7ff905733b71"}}
"
Hi @Irinavyshnikova,
For what it's worth, I haven't seen similar reports of this issue from other orgs yet, but definitely feel free to dm me if you have any other logs you'd like us to look at. Unfortunately, for fetching the schema, I don't see a way to make it optional or a way to disable it. I will share this feedback as a feature request & can let you know if our team ends up shipping it
Hi @Tess,
Thank you for your message! I will wait for your response regarding managing the schema fetching.
On the topic of slow Oracle database queries, I investigated a small application in Retool. After running ALTER SESSION SET CURRENT_SCHEMA = schema_name, I can easily run queries like SELECT * FROM table_name (without adding the schema name).
However, this last query is only available for approximately 1 minute. After that, I get an error that the table or view doesn't exist. If I run ALTER SESSION SET CURRENT_SCHEMA = schema_name again, the query SELECT * FROM table_name works. If I try to run a select query before 1 minute has passed, I get results. But if I try to run the query after 1 minute, I get an error stating 'table or view doesn't exist'. When running the ALTER SESSION query directly in Oracle, I can use the select query for a long time without any error.
It seems to me that the issue might be with Retool. Could you tell me, please, what might be the issue? Does Retool create a new session every minute? Does it close the previous one or not? Every time I run ALTER SESSION SET CURRENT_SCHEMA = schema_name followed by SELECT * FROM table_name, the queries get slower and slower, and the execution time becomes very long.
Can I somehow close the session/connection in Retool, or maybe set variables like MAX_CONNECTIONS_SIZE? Currently, I need to reboot the instance where Retool is installed every time this issue occurs.
I'm concerned that even always including the schema name or running ALTER SESSION before each query will not solve the issue because, as I noticed from previous described investigation, if 10 sessions are created, the execution time of subsequent queries increases indefinitely.
Hi @Tess!
I would like to revisit the issue of slow queries on our Oracle database. Our analysis has shown that the following query runs multiple times across several sessions, consuming a significant amount of time and system resources:
SELECT
OWNER as TABLE_SCHEMA,
COLUMN_NAME,
DATA_TYPE,
TABLE_NAME
FROM
ALL_TAB_COLUMNS
WHERE (
OWNER != 'SYS'
AND OWNER != 'SYSTEM'
AND OWNER != 'APPQOSSYS'
AND OWNER != 'AUDSYS'
AND OWNER != 'DBSNMP'
AND OWNER != 'GSMADMIN_INTERNAL'
AND OWNER != 'OUTLN'
AND OWNER != 'DBSFWUSER'
AND OWNER != 'XDB'
AND OWNER != 'WMSYS'
AND OWNER != 'CTXSYS'
AND OWNER != 'OJVMSYS'
AND OWNER != 'ORDSYS'
AND OWNER != 'ORDDATA'
AND OWNER != 'MDSYS'
AND OWNER != 'OLAPSYS'
AND OWNER != 'LBACSYS'
AND OWNER != 'DVSYS'
) OR (
OWNER = :ownerName
)
Our analysis indicates that this query attempts to retrieve all columns for all owners, resulting in approximately 900,000 rows being fetched over 5 hours. This query takes up substantial Ubuntu resources and has never completed successfully, as it runs for nearly a day in Retool due to the specifics of our database(in SQL*Plus it takes 2 hours).
Our database has a large number of schemas (1.7 million), and it seems Retool is not optimized for handling this volume of schemas. Consequently, the application periodically sends this query to retrieve all columns from all schemas, causing severe slowdowns.
Here are some key points from our analysis:
- It consumes significant system resources, impacting overall performance.
- Fetching 1.7 million schemas results in extensive delays, with one session fetching only 158,900 rows in over 2 hours, while the total result set contains 1,749,511 rows.
- Frequent restarts of the Docker container retoolonpremise_db-connector are required to mitigate the issue.
This issue seems to stem from Retool’s periodic schema fetching. We are currently on the Free plan, but we are considering switching to the Team or Business plan if it can help resolve this issue.
Is there a way to make schema fetching optional or to disable it entirely? If not, are there any other solutions you can suggest to improve performance and avoid these extensive delays?
Thank you for your assistance.
Hi @irinavyshnikova Thanks for all of these details!
Where is that schema query coming from? Is it in the Oracle logs? 
It looks different than the schema query I'd expect. How do you know it's coming from Retool? And what is the pattern of when it is triggered (does it align with any usage on the Retool side)?
You could try to spam the refresh button on the schema to see if that request is causing issues:
The schema fetching query is fairly intensive, so we have a limit on how much it can fetch (I believe the max is 5000 tables) & we also cache the results.
Are any other products or services also using your Oracle resource or is it limited to your Retool instance? If so, what is the performance like in comparison?
It would be helpful to see the telemetry on your Retool containers. If it's a memory leak, we should be seeing the container run out of memory. If you can share the api/backend logs, that would be helpful: Deployment logs | Retool Docs
Hi @Tess.
Thank you a lot for your response!
I can't spam as you suggested because, as you can see from this screenshot, the schema hasn't downloaded and I always see constant state of downloading the schema. I've never seen any ready schema in this part of the Retool UI. So, Retool is trying to get the schema, therefore I don't even see the text input and Refresh button:
Previous logs with sql-query were made using the SQL Monitor. I also noticed this query using the command docker logs retool-onpremise-api-1 | grep 'fetching schema':
{"err":"Request timed out","level":"error","message":"Error fetching schema","organization":{"id":1,"name":"nick@gmail.com"},"pid":84,"requestId":"19acc3a7-575f-488d-8494-85241feab2a4","resourceName":"e02e93e5-6a74-4d7b-b054-0608336c1450","timestamp":"2024-06-12T10:36:00.203Z","user":{"email":"nick@gmail.com","sid":"user_8578b8c7d9a6431c888e7ff905733b71"}}
{"endpoint":{"path":"/api/resources/:resourceName/schema","team":"@tryretool/connect"},"level":"warn","msg":"Error fetching schema: Request timed out","organization":{"id":1,"name":"nick@gmail.com"},"pid":84,"requestId":"19acc3a7-575f-488d-8494-85241feab2a4","stack":"Error: Error fetching schema: Request timed out\n at /retool_backend/bundle/main.js:3217:60449\n at process.processTicksAndRejections (node:internal/process/task_queues:95:5)","timestamp":"2024-06-12T10:36:00.206Z","type":"CAUGHT_ERROR","user":{"email":"nick@gmail.com","sid":"user_8578b8c7d9a6431c888e7ff905733b71"}}
{"endpoint":{"path":"/api/resources/:resourceName/schema","team":"@tryretool/connect"},"error":{"level":"warn","msg":"Error fetching schema: Request timed out","stack":"Error: Error fetching schema: Request timed out\n at /retool_backend/bundle/main.js:3217:60449\n at process.processTicksAndRejections (node:internal/process/task_queues:95:5)","type":"CAUGHT_ERROR"},"level":"info","message":{"http":{"method":"GET","request":{"time":603.0522511869967},"status_code":400,"url_base":"http://apps-main-23linux:3006","url_path":"/api/resources/e02e93e5-6a74-4d7b-b054-0608336c1450/schema?environment=production"},"type":"REQUEST_FINISH"},"organization":{"id":1,"name":"nick@gmail.com"},"pid":84,"requestId":"19acc3a7-575f-488d-8494-85241feab2a4","timestamp":"2024-06-12T10:36:00.207Z","user":{"email":"nick@gmail.com","sid":"user_8578b8c7d9a6431c888e7ff905733b71"}}
{"err":"Request timed out","level":"error","message":"Error fetching schema","organization":{"id":1,"name":"nick@gmail.com"},"pid":84,"requestId":"57b18c0c-babf-4f4a-b453-47c4d60ade1f","resourceName":"9b369a49-6939-4c1f-9217-48a09d1ad9a1","timestamp":"2024-06-12T10:37:09.087Z","user":{"email":"nick@gmail.com","sid":"user_8578b8c7d9a6431c888e7ff905733b71"}}
{"endpoint":{"path":"/api/resources/:resourceName/schema","team":"@tryretool/connect"},"level":"warn","msg":"Error fetching schema: Request timed out","organization":{"id":1,"name":"nick@gmail.com"},"pid":84,"requestId":"57b18c0c-babf-4f4a-b453-47c4d60ade1f","stack":"Error: Error fetching schema: Request timed out\n at /retool_backend/bundle/main.js:3217:60449\n at process.processTicksAndRejections (node:internal/process/task_queues:95:5)","timestamp":"2024-06-12T10:37:09.089Z","type":"CAUGHT_ERROR","user":{"email":"nick@gmail.com","sid":"user_8578b8c7d9a6431c888e7ff905733b71"}}
{"endpoint":{"path":"/api/resources/:resourceName/schema","team":"@tryretool/connect"},"error":{"level":"warn","msg":"Error fetching schema: Request timed out","stack":"Error: Error fetching schema: Request timed out\n at /retool_backend/bundle/main.js:3217:60449\n at process.processTicksAndRejections (node:internal/process/task_queues:95:5)","type":"CAUGHT_ERROR"},"level":"info","message":{"http":{"method":"GET","request":{"time":604.469621652007},"status_code":400,"url_base":"http://apps-main-23linux:3006","url_path":"/api/resources/9b369a49-6939-4c1f-9217-48a09d1ad9a1/schema?environment=production&action=force"},"type":"REQUEST_FINISH"},"organization":{"id":1,"name":"nick@gmail.com"},"pid":84,"requestId":"57b18c0c-babf-4f4a-b453-47c4d60ade1f","timestamp":"2024-06-12T10:37:09.091Z","user":{"email":"nick@gmail.com","sid":"user_8578b8c7d9a6431c888e7ff905733b71"}}
{"err":"Request timed out","level":"error","message":"Error fetching schema","organization":{"id":1,"name":"nick@gmail.com"},"pid":53,"requestId":"b2ead43c-16f4-4fe2-90e7-9a260fb9a1de","resourceName":"9b369a49-6939-4c1f-9217-48a09d1ad9a1","timestamp":"2024-06-12T10:47:03.187Z","user":{"email":"nick@gmail.com","sid":"user_8578b8c7d9a6431c888e7ff905733b71"}}
{"endpoint":{"path":"/api/resources/:resourceName/schema","team":"@tryretool/connect"},"level":"warn","msg":"Error fetching schema: Request timed out","organization":{"id":1,"name":"nick@gmail.com"},"pid":53,"requestId":"b2ead43c-16f4-4fe2-90e7-9a260fb9a1de","stack":"Error: Error fetching schema: Request timed out\n at /retool_backend/bundle/main.js:3217:60449\n at process.processTicksAndRejections (node:internal/process/task_queues:95:5)","timestamp":"2024-06-12T10:47:03.278Z","type":"CAUGHT_ERROR","user":{"email":"nick@gmail.com","sid":"user_8578b8c7d9a6431c888e7ff905733b71"}}
{"endpoint":{"path":"/api/resources/:resourceName/schema","team":"@tryretool/connect"},"error":{"level":"warn","msg":"Error fetching schema: Request timed out","stack":"Error: Error fetching schema: Request timed out\n at /retool_backend/bundle/main.js:3217:60449\n at process.processTicksAndRejections (node:internal/process/task_queues:95:5)","type":"CAUGHT_ERROR"},"level":"info","message":{"http":{"method":"GET","request":{"time":603.0069984270036},"status_code":400,"url_base":"http://apps-main-23linux:3006","url_path":"/api/resources/9b369a49-6939-4c1f-9217-48a09d1ad9a1/schema?environment=production&action=force"},"type":"REQUEST_FINISH"},"organization":{"id":1,"name":"nick@gmail.com"},"pid":53,"requestId":"b2ead43c-16f4-4fe2-90e7-9a260fb9a1de","timestamp":"2024-06-12T10:47:03.282Z","user":{"email":"nick@gmail.com","sid":"user_8578b8c7d9a6431c888e7ff905733b71"}}
{"err":"Request timed out","level":"error","message":"Error fetching schema","organization":{"id":1,"name":"nick@gmail.com"},"pid":84,"requestId":"fcf8caea-c8cf-4f43-9b06-104fee7952ae","resourceName":"e02e93e5-6a74-4d7b-b054-0608336c1450","timestamp":"2024-06-12T11:07:28.452Z","user":{"email":"nick@gmail.com","sid":"user_8578b8c7d9a6431c888e7ff905733b71"}}
{"endpoint":{"path":"/api/resources/:resourceName/schema","team":"@tryretool/connect"},"level":"warn","msg":"Error fetching schema: Request timed out","organization":{"id":1,"name":"nick@gmail.com"},"pid":84,"requestId":"fcf8caea-c8cf-4f43-9b06-104fee7952ae","stack":"Error: Error fetching schema: Request timed out\n at /retool_backend/bundle/main.js:3217:60449\n at process.processTicksAndRejections (node:internal/process/task_queues:95:5)","timestamp":"2024-06-12T11:07:28.454Z","type":"CAUGHT_ERROR","user":{"email":"nick@gmail.com","sid":"user_8578b8c7d9a6431c888e7ff905733b71"}}
{"endpoint":{"path":"/api/resources/:resourceName/schema","team":"@tryretool/connect"},"error":{"level":"warn","msg":"Error fetching schema: Request timed out","stack":"Error: Error fetching schema: Request timed out\n at /retool_backend/bundle/main.js:3217:60449\n at process.processTicksAndRejections (node:internal/process/task_queues:95:5)","type":"CAUGHT_ERROR"},"level":"info","message":{"http":{"method":"GET","request":{"time":605.9652613990008},"status_code":400,"url_base":"http://apps-main-23linux:3006","url_path":"/api/resources/e02e93e5-6a74-4d7b-b054-0608336c1450/schema?environment=production"},"type":"REQUEST_FINISH"},"organization":{"id":1,"name":"nick@gmail.com"},"pid":84,"requestId":"fcf8caea-c8cf-4f43-9b06-104fee7952ae","timestamp":"2024-06-12T11:07:28.456Z","user":{"email":"nick@gmail.com","sid":"user_8578b8c7d9a6431c888e7ff905733b71"}}
And what is the pattern of when it is triggered (does it align with any usage on the Retool side)? - Well, when I start running the Docker containers and interact with the application, which involves interacting with the database and sending queries, this query is running in the background:
SELECT
OWNER as TABLE_SCHEMA,
COLUMN_NAME,
DATA_TYPE,
TABLE_NAME
FROM
ALL_TAB_COLUMNS
WHERE (
OWNER != 'SYS'
AND OWNER != 'SYSTEM'
AND OWNER != 'APPQOSSYS'
AND OWNER != 'AUDSYS'
AND OWNER != 'DBSNMP'
AND OWNER != 'GSMADMIN_INTERNAL'
AND OWNER != 'OUTLN'
AND OWNER != 'DBSFWUSER'
AND OWNER != 'XDB'
AND OWNER != 'WMSYS'
AND OWNER != 'CTXSYS'
AND OWNER != 'OJVMSYS'
AND OWNER != 'ORDSYS'
AND OWNER != 'ORDDATA'
AND OWNER != 'MDSYS'
AND OWNER != 'OLAPSYS'
AND OWNER != 'LBACSYS'
AND OWNER != 'DVSYS'
) OR (
OWNER = :ownerName
)
The schema fetching query is fairly intensive, so we have a limit on how much it can fetch (I believe the max is 5000 tables) & we also cache the results. - Could you tell me why you need this, and can we somehow make this feature optional, please?
Are any other products or services also using your Oracle resource or is it limited to your Retool instance? If so, what is the performance like in comparison? - Of course, and after analysis, we noticed that queries running from Retool and from SQL*Plus are as fast as necessary, but this schema fetching eats many resources, and the system can't do anything else while running this schema-fetching query.
@Tess, I'll send you additional logs by email but can you send me your email, please?
Hi!
This session fetched only 158900 rows at 2+ hours, but result set has 1749511 rows.
ср, 12 июн. 2024 г. в 02:55, Tess via Retool Forum <community@retool.com>:
(Attachment 1076.txt is missing)
(Attachment oradev_ora_1076_RETOOL.trc is missing)
Hi @Tess,
We have temporarily resolved this issue by creating a test database with one schema, which has improved query performance and eliminated the need to restart Docker.
However, could I ask if you could add functionality to enable/disable schema fetching to avoid this problem in the future? Alternatively, could you please look into optimizing the query I posted earlier in this ticket that Retool is sending to retrieve all columns from all schemas?
Thank you for your assistance.
Best regards, Irina.
Thanks for the update! (and thanks for your patience as we work through this!) Glad you hear you have a temporary solution.
It looks like the limit that we set for optimizing schema calls isn't getting applied to the Oracle schema call 
I will let you know when I get an update on a fix
1 Like