How is the -gm parameter effecting query runtime in detail?

[Former Member]

During test I found out, that the parameter -gm seems to have some dramatic impact onto runtime of queries. In Detail I found a something like factor 0.5 to 0.6 inside my tests for a smaller system:

-iqtc 12000
-iqmc 12000
-iqlm 1000
-gm 100
-m
-iqrlvmem 6000

When setting -gm 1001, runtimes for a single query goes up from about 10 to about 40s.

So how is the -gm parameter effecting IQ in detail?

[Former Member · Accepted solution]

On startup IQ keeps aside memory for the number of users specified in -gm config.

Setting -gm high also requires tuning of -iqmt.

Regards,

Abhi

[markmumy]

When IQ starts it allocates threads based on the -iqmt parameter.  If you don't set it, then it is computed as something like:

     -- 60 threads for each core up to 4

     -- 50 threads for each core after 4

     -- number of connections (-gm option)

     -- add 6 more just for good measure

Let's assume that your IQ instance has 16 cores and you have -gm set to 100 .  IQ would allocate 946 threads (60*4+50*12+100+6).  We have to add 1 more thread to this, too.  Before we can allocate threads, we have 1 thread that is running to start the server.

On my 16 core system, here are the threads allocated at startup for various values of -gm:

     -gn 10: 857 threads

     -gn 100: 947 threads (notice the +1 from the above algorithm?)

     -gn 1000: 1847 total threads

There are two major deductions from the thread total: IO threads and SA/user threads.

Of the threads, 40% are supposed to be held in reserve to handle IO tasks (sweeper and prefetch threads are set to 10% each and apply to main and temp cache) via the Prefetch_Threads_Percent and Sweeper_Threads_Percent options.  I am double checking this area just to make sure.  There was some work done on this in the v16 timeline.

For user threads, I don't see you having set -gn.  Assuming that -gn is not set, then IQ will reserve 5 + -gm threads (105).  Not a problem.  IQ will grab 5 more threads than are allocated via the above algorithm, but that's OK.

Things go a bit wrong on you when your -gn is set to the recommended value of 1.5 * -gm.  What happens is that we are computing the number of threads needed based on -gm (100 or 1000).  However, we are actually allocating threads based on -gm (105, 1005, 150, or 1500 depending on the default or best practices used).

Imagine that -gn is set to 1.5x -gm.  Of the 1847 threads allocated, 1500 are held in reserve for user connections.  Read that as threads for the catalog only.  There would be just 347 threads left over.  But don't forget the IO threads either.  Now all of a sudden there are no threads left for any work in IQ.  We are thread starved.  When IQ gets into a thread starvation mode, we have little or no threads left over to do work in IQ.  At that time, we use the one guaranteed thread that each user connection has to do all work.  This holds true for worker thread starvation as well as IO thread starvation.

One of two things is happening in your case.  You are either thread starved and forced to run everything single threaded or your system has so many threads that it must spend too much time maintaining threads that are not being used.

If you can run sp_iqsysmon in '-debug' mode you can look at the "Thread Manager" section to check out the thread usage and allocations.

Mark