Forum

Discussions specific to version 8.1
Contributor
Offline
User avatar
Posts: 25
Joined: Fri Nov 29, 2013 9:31 pm

Query "hangs", never completes - out of memory?

by quant123 » Wed Dec 11, 2013 3:42 am

Hi,

I am having a problem when trying to run complex queries (on either a table or a view) that have multiple windowing (ranking, AVG, LEAD, LAG) functions.
This is with 14+ windowing functions querying a table with 10 million records.

It looks to me that the program is running out of memory - if I remove enough windowing functions from the query or if I "drop RAM images" from other tables, or if I remove enough records from the table being queried I can get the query to run. When it runs, it completes the query in under 15 seconds.

The server has 64Gb of RAM, and the wxadmin does recognize all 64Gb, however Linux system monitor is showing that only 19Gb is being used overall.
The RAM usage shown on the dashboard never goes over 35%
I have plenty of HDD space

So, it seems to me that the program does not have enough memory to finish the query, however, it is not using all available RAM.

Am I missing something in the settings? Any suggestions?

Thanks.
Reply with quote Top
Contributor
Offline
User avatar
Posts: 386
Joined: Thu May 23, 2013 4:48 pm

Re: Query "hangs", never completes - out of memory?

by markc » Wed Dec 11, 2013 9:23 am

The Kognitio software should use all the available RAM (it reserves a small percentage for Linux etc). It can cope with queries which will not fit into RAM using a streaming mechanism introduced in version 6 - this allows accesses in a query to be repeated if all the data required at a later stage cannot be held in RAM. Unsurprisingly, attempting to image an object which is too big for RAM (e.g. a table or view image) will result in an out-of-memory error for the query, but temporary workspace requirements that exceed RAM should not generate errors, due to the streaming mechanism mentioned above.

The fact that the Console dashboard only shows 35% usage is unexpected here, although there are some steps that we take to prevent individual queries from grabbing all RAM, as otherwise starting up a number of other queries shortly afterwards can lead to issues.

To understand better what is going on, can you attach the following to this topic:
1) when the system is in this state, the results of the following SQL queries run as SYS:
a) "SELECT * FROM SYS.IPE_PROCESS". This will let us see how much memory is allocated to each Ram Store, and how much of it is in use.
b) "SELECT * FROM SYS.IPE_ALLRAM_ACCESS". This lets us see the state of all RAM accesses on the system.
c) "SELECT * FROM SYS.IPE_DISK_ACCESS". This lets us see the state of any disk accesses on the system (we would not expect there to be any if all the objects being queried have RAM images, but it never hurts to check).
d) "SELECT * FROM SYS.IPE_ALLCURSESSIONS". This shows all current sessions, the queries they are running, what state they are in, etc.

2) Also with the system in this state, can you run "wxtop" from the Linux command line and capture that output. This lets us see which database processes, if any, across the whole system are busy. That is a good first step in differentiating between a hang and e.g. something which is struggling due to lack of resource.

3) the result of running "ls -ltr /dev/shm" as root on each node in the system (from earlier comments I think this is a single node system).

4) tarred up log files under /var/log/wx2 - these are normally a good thing to attach to help with any investigation.

5) output from the SQL "DIAGNOSE <insert your problem query here>". Make sure you do not have a row number limit in the Console when doing this, as diagnose output can be quite lengthy. This will let us review the query plan to see if it shows any issues.

6) a diagnose as in (5) above, but this time for the query with the minimal number of changes you need to make it run in under 15 seconds. This will show if there is a significant difference between this query plan and the one from (5).

Regards,
Mark.
Reply with quote Top
Contributor
Offline
User avatar
Posts: 25
Joined: Fri Nov 29, 2013 9:31 pm

Re: Query "hangs", never completes - out of memory?

by quant123 » Thu Dec 12, 2013 1:00 am

Mark,

Thanks for the very informative reply, as always.

I just emailed all the files that you requested to helpdesk at kognitio as I can't share some of the content on the forum.

Couple of things that I tried with no success:
1. Tried disabling NUMA awareness - no difference
2. Tried setting rs_core_pma_size to 50 and 0 - no difference (although, when I set it to 0, kognitio reserves all available system memory)
3. Tried different combinations of windowing functions - no difference, just noted that it takes more of the silmplier functions and less of the more complex functions to produce the error. So it appears to me that the problem is not due to a SQL error.

Let me know when you think.
Thank you.
Reply with quote Top
Contributor
Offline
User avatar
Posts: 386
Joined: Thu May 23, 2013 4:48 pm

Re: Query "hangs", never completes - out of memory?

by markc » Thu Dec 12, 2013 9:13 am

Thanks - I've got the email, so will work through the information in that and post when we have something.

Regards,
Mark.
Reply with quote Top

Who is online

Users browsing this forum: No registered users and 1 guest

cron