Question DB Locks - _Lock VST vs promon

[ron]

Hi,

OE 10.2B - RH Linux.

I have a process that gets database lock details using the output from promon. I used to do it with the _Lock VST, but found that too slow.

Recently an associate said there was a risk of "freezing" a database by using promon to do this on "large" databases. That seemed pretty strange to me - but I thought I'd better make sure!

Does anyone know of any risks involved in using promon to extract details about locks out of large databases?

Ron.

 

[TomBascom]

I think that your associate is confused. He's probably got things backwards and is thinking of what happens when you try to use _LOCK instead of PROMON.

Having said that... the PROMON technique is limited to the first 9999 locks because you cannot set the page size any larger.

 

[ron]

Thanks Tom. I was pretty sure that was the case - but I thought I'd better ask .... just to be sure.

(Pity about the 9999 limit.)

 

[cj_brandt]

on 10.2B06 we use _Lock to collect the info. In prod, we check to make sure the current locks are below a set amount - like 10,000 and then go through the table.

 

[ron]

How do you check how many locks are in the table before scanning it?

 

[Rob Fitzpatrick]

The size of the lock table (-L) is given by _Startup._Startup-LockTable.

 

[ron]

I would think that gives you the size of the lock table - not the number of locks actually in it.

 

[Rob Fitzpatrick]

Yes, I think that's what CJ was alluding to: permit the _Lock scan if the size of the lock table (-L) is small enough.

 

[TomBascom]

There is also a current # of locks field.

_DbStatus._DbStatus-NumLocks

And the "high water mark":

_DbStatus._DbStatus-MostLocks

But - read the other thread. This kind of code is a very, very bad idea. Time to scan the lock table is not just related to the number of locks that you're going to scan it is *also* related to -L. So even scanning just a few records with a big -L is horrible.

This may sound crazy but that behavior is saying that the engine scans the entire -L table to find each and every _LOCK.

Code that messes with _LOCK works sort of "ok" if -L is small and there are not many locks -- like the default of 8192 in a test environment. It goes to hell in a handbasket when -L is large. 100,000 is a very common production setting. I frequently see 1,000,000. And I have seen it in excess of 1 billion.

 

[ron]

That's my experience too. On a "big" system under heavy load - ONE scan of the lock table with _Lock can take more than one hour (!!) - and at the same time it creates a terrible impact of system performance.

That's exactly why I've moved to using promon. It provide a full copy of everything "in a flash".

Parsing the output, catering for different versions of Progress is pesky - but certainly worth the trouble.

 

[cj_brandt]

Sorry - I meant the _DbStatus._DbStatus-NumLocks. I should have said if the current number of locks is below ....

On 10.2B06 in non production environments we have scanned _Lock tables with 500,000 locks or so and the results are back in a minute or 2.

 

[TomBascom]

What platform?

 

[Rob Fitzpatrick]

Sorry - I meant the _DbStatus._DbStatus-NumLocks. I should have said if the current number of locks is below ....

On 10.2B06 in non production environments we have scanned _Lock tables with 500,000 locks or so and the results are back in a minute or 2.

But by the time you have read the value of NumLocks and acted on it, it could have changed considerably.

 

[ron]

Does anyone know what promon does if there are more than 9999 locks? I expect that it will just give you the first 9999 - and that's it.

Also - is anyone aware of any plans from Progress to increase the number of locks promon can report?

 

[TomBascom]

PROMON doesn't care how many locks you have. It cares how many lines you have set the page size to...

If you set your page size to 9999, which is the maximum, and try to list more than 9999 locks (actually some lines get wasted with headers and prompts so it is more like 9990) then it depends on what your script does. Mine makes no effort to ask for another page. I just exit it that point. In theory I could look for the prompt that says that there are more ("Enter <return> for more...") and then provide extra line feeds until it runs out.

Personally I'm too lazy right now. If there are that many locks there is a bigger problem.

 

[cj_brandt]

We use Red Hat Linux, OE 10.2B06 and it has worked well. I'm sure Progress has a KB that says not to do this though.

Like Rob suggests, we occasionally have a monitor that begins to read the _Lock table, only to have it increase to a large value. We don't have anything in place that would stop it from continuing to read until the ? value is found. 4 or 5 times a year, that monitor gets hung up and we kill it and start again.

It will chew up CPU time while processing, but that is the only negative we have experienced from using the monitor over 4 or 5 years.

In the early years we were HP and 10.1B and it was not as reliable, we only used it on a handful of databases and had a lot more problems with the monitor hanging.

In a couple months we'll have 11.5 in enough test systems that we'll be able to see what happens in 11. Maybe in time for the PUG Challenge.

 

[Rob Fitzpatrick]

In a couple months we'll have 11.5 in enough test systems that we'll be able to see what happens in 11. Maybe in time for the PUG Challenge.

It will be interesting to see how this evolves. 11.4 is supposed to have implemented a new method of turning the lock hash chains into _Lock records much more efficiently than in the past.

However right now there is an issue, which I believe is not the intended design, where it is no longer the case that all of the meaningful records are at the beginning of the table, followed by the records with unknown values. So that can break code that assumes there is no valid data after the first unknown. By "break" I mean semantically, not syntactically.

 

[Rob Fitzpatrick]

The KB for this issue is: http://knowledgebase.progress.com/articles/Article/000056304
Defect PSC00322250

 

[ron]

Tom - you are quite correct (of course!) - promon will continue providing as many pages as you ask for. Therefore, one can use _DbStatus._DbStatus-NumLocks to determine how many pages to request.

Thanks everyone - I believe my problem is now solved.

 

[Rob Fitzpatrick]

Tom - you are quite correct (of course!) - promon will continue providing as many pages as you ask for. Therefore, one can use _DbStatus._DbStatus-NumLocks to determine how many pages to request.

Locks can come and go faster than the VST or promon can be updated or you can act on the information you read.

Be aware that by the time you calculate that you need to read two promon lock pages and you display their contents, there now could be one page's worth of locks. Or ten. Or none.