Fully managed cloud. 30-day FREE full featured trial. Start Now
cancel
Showing results for 
Search instead for 
Did you mean: 

Performance on premise dropping

ronaldvr
SQL-Fighter

Since a few weeks we see connection issues and performance issues on out on premise  Exasol (7.0.19) environment, we moved some people that develop queries to the production environment with 330GB Memory, but still we see slow queries, database performance degradation during the day (which incidentally can be solved -for a while- by restarting the database). Does anyone have an idea if there is anything we can do to solve or alleviate this? Moving from a DEV environment with 30GB memory to an environment with 10x more memory did not really help so throwing more hardware at the issue does not seem to be very helpful?EDIT:  month ago we experienced this:

exa1.png

Suddenly s spike in usage

exa2.png

And after  a restart of the Dtaabase back to normal again:

exa3.png

 

 

1 REPLY 1

PeterK
Xpert

Hi @ronaldvr 

Unfortunately there are so many factors that can influence performance that it's difficult to say what the root cause might be in your case.

Regarding the storage spike, it appears it's caused by the temporary volume increasing substantially in size up to 1.6TB. This limits the space available for the persistent volume and which then generates the error alert. Restarting an instance resets the size of the temporary volume which is why it resolves the alert.

1.6TB is very large for the temporary volume and suggests that you are running some queries that generate massive intermediate temporary datasets. When these intermediate datasets cannot fit in RAM they are swapped to the temporary storage volume and that cause significant slow-downs.  See "Reserve Space For Temporary Data" on this page: https://docs.exasol.com/db/latest/administration/on-premise/sizing.htm?#DatabaseDiskSize

I would suggest looking in your SQL log for statements that are using an excessive amount of TEMP_DB_RAM_PEAK and then profile the statements and try to rewrite them to avoid super-large intermediate result rows.

Regards,

Peter