IDS Forum

Re: transation logs problem

Posted By: Fernando Nunes
Date: Wednesday, 21 March 2018, at 8:14 a.m.

In Response To: Re: transation logs problem (SERGIO PERES)

Sérgio,

As transações longas não se podem evitar por descartar os logs. O que
define uma transacção longa é se entre o início e o ponto actual dos logs
passou em percentagem o valor to LTXHWM.
Por exemplo se o valor do LTXHWM estiver a 70, tivermos 100 logs e não
houver mais actividade no motor, e a transação de iniciar no log "1",
quando preencher o log 70, será dada como long transaction e inicia-se o
rollback.
Se o LTXEHWM (o outro parâmetro) estiver a 80, quando o rollback preencher
o log 80 pára toda a actividade do motor e continua o rollback. E neste
cenário chegaria ao log 100 sem completar o rollback e ou aloca mais logs
(DYNAMIC_LOGS = 2) ou o motor ficará parado. Sendo que já terá parado toda
a actividade extra no log 80.

Portanto, estes parâmetros devem estar em valores mais baixos (30/70 por
exemplo). A consequência disto é que pode entrar em long transaction mais
cedo, mas a probabilidade de bloquear ou ficar bloqueado é menor.

A solução para o problema será não fazer actividades que consumam logs.
Como? Pode ser complicado... Em vez de ALTER TABLE que reconstrói a tabela
(se for um SLOW ALTER) pode ser melhor fazer uma tabela nova (RAW) e copiar
o conteudo. Mas isto é apenas um exemplo. No limite poderá ser necessário
ter mais logs no motor.

Por último, sempre que se mudar o LTAPEDEV de ou para /dev/null a instância
tem de ser parada. Mas isto como refiro, não evita o problema.

=====================

Long transactions can't be avoided by discarding the logs. What defines if
a transaction is "long" is the log consumption between the start of the
transaction and the current log position. If the difference in percentage
is higher than LTXHWM, then a transaction is considered a long one.
As an example, if we have 100 logs, LTXHWM=80, there is no other activity
in the database, and we start a transaction in log "1", when it finishes
log 70, it will trigger a long transaction and the rollback is started. If
the LTXEHWM (the other parameter) is set to 80, when the rollback fills log
"80" is will block all other transactions and continues the rollback. In
this scenario it will probably reach log 100 before completing the rollback
and either it allocates more (DYNAMIC_LOGS = 2) or the engine will stop.
Meanwhile all other activity has stopped in log "80".

That's why these parameters should be lower (30/70 for example). The
consequence of this, is that it will probably consider a transaction as a
long one, sooner. But we lower the probability of blocking other
transactions or get into a blocked state.

The solution for the issue it to avoid doing activities that consume logs.
How? Can be tricky.... Instead of an ALTER TABLE that rebuilds the whole
table (if it's a SLOW ALTER) it may be better to create an new table (RAW)
and copy the original table's content. But this is just a simple
example.... In the end it may be needed to add more logs to the engine

Last, whenever you change the LTAPEDEV from or to "/dev/null" you must
restart the instance. But this, as explained, doesn't solve the issue.

Regards.

On Wed, Mar 21, 2018 at 11:54 AM, SERGIO PERES <sergio.peres@airc.pt> wrote:

> Hi Marcus,
>
> Thanks for your reply.
> As we don't use transaction logs I'm trying to avoid hangs, because before
> the
> changes we do a full backup.
> I can change LTAPEDEV by onmode but I'm looking if there is any different
> option.
>
> Best regards,
>
> SP
>
>
> ************************************************************
> *******************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--
Fernando Nunes
Portugal

http://informix-technology.blogspot.com
My email works... but I don't check it frequently...

Messages In This Thread

• transation logs problem
  SERGIO PERES -- Wednesday, 21 March 2018, at 6:20 a.m.
  • Re: transation logs problem
    Marcus Haarmann -- Wednesday, 21 March 2018, at 6:39 a.m.
    • Re: transation logs problem
      SERGIO PERES -- Wednesday, 21 March 2018, at 6:54 a.m.
      • RE: transation logs problem
        Alexandre Marini -- Wednesday, 21 March 2018, at 7:35 a.m.
        • Re: RE: transation logs problem
          SERGIO PERES -- Wednesday, 21 March 2018, at 8:09 a.m.
          • Re: RE: transation logs problem
            Fernando Nunes -- Wednesday, 21 March 2018, at 8:18 a.m.
          • RE: RE: transation logs problem
            Alexandre Marini -- Wednesday, 21 March 2018, at 8:48 a.m.
      • Re: transation logs problem
        Fernando Nunes -- Wednesday, 21 March 2018, at 8:14 a.m.
        • Re: transation logs problem
          SERGIO PERES -- Thursday, 22 March 2018, at 7:04 a.m.
          • Re: transation logs problem
            Fernando Nunes -- Thursday, 22 March 2018, at 7:23 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.