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

Exasol 6.2.4 Perfomance

Moritz
SQL-Fighter

Hi guys,

We are having performance issues with our Exasol - query times are extraordinary long and volatile. 
The database has 16 GB RAM and the underlying Server 32 GB RAM and 16 CPUs.

Here you can see basic information provided by the UI indicating that the RAM usage is pretty high:

Moritz_0-1598596375002.jpeg

However, I wonder why this is the case.
Do you have any ideas how to improve perfomance?
Any help would be much appreciated!

Best

Moritz

1 ACCEPTED SOLUTION

Accepted Solutions

mwellbro
Xpert

Hi Simon,

just so I think in the right direction: "have been running smooth a few weeks ago" => also under V.6.2.4 , right ?

"It appears unrealistic, that all SQLs and joins, that have been working before, are now affected by these issues." => I´m not sure what to make of this, I´ll try to be more specific: the things I enumerated ( which were meant to be neither exhaustive nor directly applicable in your setup ) were just examples.

From what I can guess given the original info there are 2 phenomenons here: 

1) Queries that are being considered too slow with exec times <10 sec. ( the example profile was given ), where I´d think it could be worthwhile checking COMPILE times.
2) Queries that suddenly run "open end" or at least 10min. , which can happen for a lot of reasons and without the DDL or SQL code changing ( the properties of the data could have changed in terms of per value distribution, what was previously an index could now be an index wrapper, etc. , etc. ).

When checking your SQLs avoid 2 little pitfalls I went into on occasion:

1) If your measure runtime across a session with multiple statements for your result ( well hello there, MicroStrategy-Multi-Pass-Execution ) it does pay off to concatenate all SQL_TEXTs , regex away all "unique to the execution timey wimey-bits" and create a HASH_TIGER over the resulting VARCHAR, that way you´ll see rather soon if your SQL_TEXT changed along the way ( it´s a bit like a sql_id in oracle ).

2) Make use of the recursive_dependency view in exa to resolve or rather detect changes in sub elements along your views, i.e. SELECT * FROM ACCESSLAYER.FACT_PROJECTS_BUDGET_MONEY_MONTH might be the exact query text for the last couple of weeks, but if the DDL of that view has been changed you could hardly call it the same SQL when analyzing for issues.

Hope this helps, looking forward to what you dig up 🙂

Cheers,
Malte

 

View solution in original post

18 REPLIES 18

Charlie
Xpert

In Exasol you want to use all the memory your infrastructure can provide.

That's mainly the use case for an in memory database.

Can you post output of 

select * from EXA_STATISTICS.EXA_DB_SIZE_LAST_DAY   order by MEASURE_TIME  desc;
select * from EXA_STATISTICS.EXA_MONITOR_LAST_DAY   order by  MEASURE_TIME desc;

Exasol will not perform good if you don't have enough memory to keep at least the indexes in memory.

So when you see volatile response times it may be due to the fact that objects and indexes have to be cast out of memory and re-read often.

 

Moritz
SQL-Fighter

Hi Charlie,

thank you very much for your quick reply. This is much appreciated!
Please find the output of the queries below.

select * from EXA_STATISTICS.EXA_DB_SIZE_LAST_DAY   order by MEASURE_TIME  desc;

Moritz_13-1598599953197.png

select * from EXA_STATISTICS.EXA_MONITOR_LAST_DAY   order by  MEASURE_TIME desc;

Moritz_14-1598599953208.png

Does this information help you?

Best

Moritz

 

Charlie
Xpert

Basically what I checked for was the recommended memory size and the auxiliary size (index size) but the database is tiny.

Further no heavy HDD reads/writes can be seen.

From the low workload from the entries you have posted I would guess the database is idle and the database size is really small so it would fit completely into memory.

I have to  say that I read over the fact that only 16 GB of the memory are assigned to the database and the server has 32 GB of memory.

We once ran into an issue where bucketfs consuming all the available memory but that should be fixed in your version (EXASOL-2523) 

Can you execute a slow query by using the explain feature of DBeaver and post the result?

 

 

 

 

Moritz
SQL-Fighter

This query took approximately 6 seconds (way too long).

Moritz_0-1598605999328.png

Do you need further information?
Best

Moritz

exa-Carsten
Moderator
Moderator

Hi Moritz,

I assume, you had no other query running between 9:24 and 9:29. Memory is definitely not an issue. 

As you are using DBeaver, you could take one of your problematic queries and execute it with Ctrl+Shift+E. This will return the execution plan of the query and might give you some more hints on what's going on. Please also see https://docs.exasol.com/database_concepts/profiling.htm for some further explanations on this profiling. Feel free to post the result here.

Moritz
SQL-Fighter

Hi Carsten,

Thank you very much for your reply.

Interestingly, the most problematic query does not even execute the Crtl+Shift+E command properly since it is running for 10 minutes now.

Moritz_1-1598606859081.png

 

Please find a result of another query below. It takes approximately 6 seconds which is way too long given the fact that it is a small table.

Do you need further information?

Best

Moritz

 

 

Moritz
SQL-Fighter

*

Moritz_2-1598606945161.png

 

Charlie
Xpert

I can't see the duration of the parts in the screenshot and I'm unable to see all parts.

 

You could export the info by doing 

 

export (select * from EXA_STATISTICS.EXA_DBA_PROFILE_LAST_DAY where session_id = current_session and stmt_id = 28) into local csv file '<path>\<file>.csv.gz';

and attach the file.

 

Otherwise you would have to attach quite a lot of screenshots to see all information 

Moritz
SQL-Fighter

Please find the file attached.