Sybase IQ 16 Threads

[Former Member]

Hi

I have been running different queries on IQ, and i have been monitoring these queries with sp_iqcontext but noted that for all queries the IQTHREADS field always shows me that only use one thread per query.

The instance of IQ have 16 cores and  90 GB of memory RAM (-iqmc 30000  -iqtc 30000 -iqlm 30000 )

Anyone know if this is a normal behavior?  Because it always uses one thread for query.

[markmumy]

No, this is not normal.  Can you post a copy of your IQ config file?  Also, include an HTML query plan, captured post execution, as this will show us what decisions IQ made to run with just 1 thread.

Mark

[Former Member]

Hi Mark

I had a error with the values of iqmc, iqtc and iqlm, the correct values are (-iqmc 30000  -iqtc 50000 -iqlm 22000).

I attach the cfg file (server.txt) and query plan (Query_plan.txt can be open with Interactive SQL)

Thanks!

Query_plan.txt.zip

server.txt.zip

[markmumy]

A few things right away...

You have -gm defined as 300.  You also need -gn defined as 450 (1.5x the value of -gm).  Is there a reason that you have -gm so high?  Do you really expect 300 users?  This takes a huge hit on the threads from IQ and will reduce the threads that you have available for processing queries.

Have you done cache analysis to deviate from the recommended 30% for each cache (main, temp, lma)?  Each should be 30% of the RAM on the machine.

Your setting for catalog cache is just 64mb.  Make sure that it is much larger as you have a huge number of users.  Change -c to -c 1024m.

You are running IQ 16 SP08 PL01.  You should move to the latest patch level for IQ 16 SP08.  Somewhere around PL35 or later.

Can you please generate an HTML query plan on the server?  Make sure to execute the procedure.  In your current plan you have NOEXEC set to ON.

Use these options:

set temporary option query_plan = 'off'; -- text based plans

set temporary option query_plan_as_html='on'; -- HTML graphical plans

set temporary option query_plan_after_run='on'; -- post execution plans

set temporary option dml_options10 = 'on';

set temporary option query_detail = 'on';

set temporary option query_timing = 'on';

YOUR QUERY HERE;

Mark

[Former Member]

Mark

Yes i will expect a 300 users or maybe more.

I will set the values caches according your suggestion.

Do you know if exists any rule to set the catalog cache?

I attached the query plan in html.

I had to change the file extension because is not allowed html files.

thanks!

QUERY_PLAN_L_0__20151112-142556_16236_1.txt.zip