Hi.

Our largest client have a SA16 database, about 25 GB in size. Primary use is a client/server application, but we also have som webservices and some use CLR (.Net code).

As of lately, we are experiencing some issues. Yesterday, one of the webservices used over 30 seconds to connect. This usually takes milliseconds, due to the use of connection pool cache. At that same time, one of the CLR-routines reported "All threads are blocked" in the Windows event log.

The database server is a new HP Proliant server, with lots of RAM and a gnarly CPU.

I had a look at the service, and it is set up like this:

-n <servername> -x tcpip(serverport=2638) -gnl 200 -gnh 400 -ca 0 -c 32G <databasefile.db>

Also, the option max_query_tasks option is set to 1 (a tech at SAP recommended it).

Is there any point in adjusting the gnl and gnh switches? Or could we remove them all together?

It would be great to hear some of your experiences if you have any. Perhaps someone from SAP has anything to contribute?

Regards,

Bjarne Anker Maritech Systems AS Norway

asked 24 Jan, 16:22

Bjarne%20Anker's gravatar image

Bjarne Anker
455181931
accept rate: 0%

1

As option -gnh restricts the maximum number of tasks and thereby worker threads - is there a need for you to specify that? (So my implicit suggestion is to try to omit that limitation...) - As your -gnl option is set to a really high minimum MPL value, I guess that seems to have a reason, however, it would be a further test to omit that, as well, and check what default MPL settings the database server will calculate (listed in the console when starting the process...).

(25 Jan, 05:44) Volker Barth

I'm not sure why this option is specified actually. I think this is an old setup, before I was involved and before we added several more services and CLR. I guess it's no big problem in omitting set option all together?

Thanks.

(25 Jan, 06:07) Bjarne Anker

FWIW, thread deadlock may also result from problems with external environments or suboptimal locking schemes (in which case increasing the maximum MPL value may just delay the problem to a later time, when the new maximum value is reached)...

There are quite a few FAQs here dealing with such problems, cf:

http://sqlanywhere-forum.sap.com/search/?q=All+threads+are+blocked&Submit=search&t=question

(25 Jan, 07:40) Volker Barth

We are using CLR External Environment, and that is rather new. I think the first "all threads are blocked" came around not long after we started the CLR routines. I think there is a connection here. I will look into some of the other similar problems and see if I can find anything useful.

Thanks.

(25 Jan, 07:47) Bjarne Anker

You could use sa_server_option with MaxMultiProgrammingLevel to increase the gnh value online, to check if that helps.

You can use the profiler to check if you really have so many simultaneous requests that 400 tasks are not enough to handle them.

You can check the blocked connections tab to check if more or less all your requests are accessing the same data and therefore have to wait for each other

permanent link

answered 25 Jan, 05:51

Martin's gravatar image

Martin
8.6k118151237
accept rate: 14%

In addition to all the other suggestions, you may find Foxhound to be helpful when dealing with runaway connections; see the white paper here.

permanent link

answered 25 Jan, 07:50

Breck%20Carter's gravatar image

Breck Carter
26.9k438609883
accept rate: 21%

edited 25 Jan, 07:51

2

The exercise would be to identify the reason all your worker threads are busy. {as others have correctly identified} Intra-query parallelism is just one possible factor.

The list of possibilities include:

  • Execution of Parallel access plans,
  • Remote accesses (via proxy tables for example),
  • " " adds extra workers normally but can become new additional requests/tasks and will consume more workers when the remote dbs are on the same server,
  • External Environment uses (your CLR routines are an example),
  • Web requests (both those into and those from the database server),
  • Active sending of emails,
  • The execution of Events (including maintenance plans),
  • Backup tasks,
  • {I believe TDS result sets can also hold worker threads longer than other connections)
  • in addition to the normal Active (scheduled) requests,
  • ... and I may have missed a couple more.

It is worth noting one request from 1 client connection can consume many workers when it combines any number of the above features and parallelism can magnify that a couple of different ways.

It is also worth noting that Tasks can also become blocked and hold their worker threads while locks need to be acquired. If you have a large impact due to contention, you will want to monitor your contention levels as well.

Just some thoughts that may help you in your investigations ...

(25 Jan, 08:54) Nick Elson S...

Ok, it looks like we have a lot to learn regarding this. But stupid question: is it an idea to omit both -gnl and -gnh switches? I suppose that the server would dynamically handle this itself when the limitations are gone?

Regards,

Bjarne

(25 Jan, 08:58) Bjarne Anker
Replies hidden

I would think so, but note that your -gnl value is probably quite high, so I'll check what the defaults are... (see my first comment).

(25 Jan, 09:48) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×19
×15
×12
×7

question asked: 24 Jan, 16:22

question was seen: 360 times

last updated: 25 Jan, 09:49