proutil -C increaseto parameter

[Brown, Gordon]

I want to increase my -L and I have about 30+ users connected. I'm going to create a new memory segment during this operation and those 30+ users won't be connected to the new segment.

The devil on my one shoulder keeps telling me to increase the -L and just let the command sit until all 30+ users "do something" (as the Progress Knowledgebase unhelpfully puts it). The angel shoulder tells me I shouldn't run the command without a clear indication of when it will complete.

What risks might I be facing if it takes hours for every user to connect to the new memory segment?

Linux RHEL 6.7
OE 11.303

Gordon Brown
QAD DBA - IAC Group, NA

LEGAL DISCLAIMER

This E-mail message and any attachments may contain legally privileged, confidential or proprietary information. If you are not the intended recipient(s), or the employee or agent responsible for delivery of this message to the intended recipient(s), you are hereby notified that any dissemination, distribution or copying of this E-mail message is strictly prohibited. If you have received this message in error, please immediately notify the sender and delete this E-mail message from your computer.

[Gus Bjorklund]

risk is

unplanned database shutdown due to lock table overflow during transaction
backout.

vs

planned database shutdown to change -L


regards,
gus

3 Logicians walk into a bar.
The bartender asks if they'd all like a beer.
The first logician says "I don't know˛.
The second says "I donšt know˛.
Then the third exclaims "Yes!

[Brown, Gordon]

Yeah. I've got that. That's the calculus I've used for the past 18 years as we wandered through Progress versions 7-9.

Now that we've recently upgraded to 11.3 I suddenly find there's a new feature and I was kind of hoping it would be useful and practical.

My initial impression is that it is neither. If it can only be used "online" at a time when it would be just as easy to stop/restart the database, what is the point? This utility, however, is new to me and I could be missing something. I could be missing how it could be helpful or, and more importantly to me, I could be missing how it could be dangerous.

[Brandt, Carey]

In my world, the proutil -C increaseto parameter has been more helpful in non prod environments.

We have several hundred of these environments so they run with the minimum resources. If I need to bump up the locks from 8192 to 16384 I can usually do that without having to kick any shared memory users out. Also we have several application servers that connect to the databases. If a new shared memory segment needs to be created, it is faster for me to kick out the shared memory users, run the increaseto command and then let the processes reconnect on their own rather than shut everything down and then restart.

Jumping from 9.x to 11.x. You will be very happy the next time you go to rebuild an index in 11.




Carey Brandt
Database Services

[Gus Bjorklund]

well you asked about the risks. those are a wash so you have nothing to
lose.

the advantage is that often it it will just work, without downtime.

regards,
gus

3 Logicians walk into a bar.
The bartender asks if they'd all like a beer.
The first logician says "I don't know˛.
The second says "I donšt know˛.
Then the third exclaims "Yes!

>The first logician says "I don't know2.
>The second says "I don1t know2.

[Gus Bjorklund]

when you delete a record:

0) the record is locked in exclusive mode using one lock table entry.

1) we generate a before-image log record (aka łnote˛) that contains a copy
of the record.

2) the record is removed from the data block it occupies but is replaced
with a łrowid reservation˛ so that another transaction cannot reuse the
rowid just yet (because if that happens, then the record cannot be put
back in its former location and a rollback would be impossible). once the
transaction has committed, the rowid reservation no longer applies.

3) the rowid reservation serves also as a record lock so we can (and do)
delete the lock table entry in memory since it is no longer needed.

when the transaction is rolled back:

4) we read the transactionąs before-iamge log records from back to front

5a) for deleted records, we replace the rowid reservation with the record
copy from the before-image log.

5b) we restore the exclusive lock.

6) for other operations we reverse their effect.

7) repeat steps 4 through 6 for remaining before-image records until we
get to the transaction begin record.

as you can see, rollback could consume quite a few lock table entries.

regards,
gus

3 Logicians walk into a bar.
The bartender asks if they'd all like a beer.
The first logician says "I don't know˛.

The second says "I donąt know˛.

Then the third exclaims "Yes!

On 2/19/16, 6:30 AM, "Marcos Kirchner" <marcos....@totvs.com.br>
wrote:

>Hey Gus,
>
>How is it possible to overflow the lock table during backout? Everything
>the transaction touched is already locked I believe, right?
>Is it possible the a transaction will acquire more locks during rollback?
>
>
>
>Thanks,
>Marcos
>
>
>
>-----Mensagem original-----
>De: Gus Bjorklund [mailto:g...@progress.com]
>Enviada em: quinta-feira, 18 de fevereiro de 2016 20:03
>Para: Brown, Gordon <GBr...@iacgroup.com>; d...@peg.com
>Assunto: Re: proutil -C increaseto parameter

[George Potemkin (peg)]

BTW, a database will be crashed after a long agony (when the -Mxs memory
will be exhausted) if a lock table overflow will happen during transaction
undo.

Regards,
George Potemkin
Progress Technologies
S.-Petersburg, Russia
Phone: +7 (812) 438 2757
http://www.progress-tech.ru

--------------------------------------------------
From: "Gus Bjorklund" <g...@progress.com>
Sent: Friday, February 19, 2016 20:05
To: "Marcos Kirchner" <marcos....@totvs.com.br>; <d...@peg.com>
Subject: Re: RES: proutil -C increaseto parameter