transation logs problem

Hi, I have todo some frequent changes to database structure, on some customers the operation hangs because the transaction logs became full. I am using /dev/null for LTAPEDEV and configured alarmprogram script to do backups, is anything missing? There is any option to disable it from SQL? Thanks for any help, SP

Hi, if you set LTAPEDEV to /dev/null, you need to bounce the instance to make it effective. That way, the log files are thrown away immediately. If you do not do that, you would need to execute ontape -a / ontape -c to perform a pseudo logbackup to /dev/null The special situation /dev/null is only detected at boot time. A point to mention here is that you cannot cover long transactions by just setting LTAPEDEV to /dev/null, since a transaction needs to fit in the active logs. For a long transaction you need to have enough log space. Marcus Haarmann Von: "Sérgio Peres" <sergio.peres@airc.pt> An: "ids" <ids@iiug.org> Gesendet: Mittwoch, 21. März 2018 11:20:16 Betreff: transation logs problem [40882] Hi, I have todo some frequent changes to database structure, on some customers the operation hangs because the transaction logs became full. I am using /dev/null for LTAPEDEV and configured alarmprogram script to do backups, is anything missing? There is any option to disable it from SQL? Thanks for any help, SP ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

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

Hi Sergio. Let me explain and try to help you further on this. Just the fact that you are putting LTAPEDEV to /dev/null and alarmprogram to (backuplogs=Y) does not mean that your engine will not consume logical logs. They are just not being backed up, ok? Any data change (DML), structure change (DDL) will be recorded into your logs, since you are using logged mode databases on your Informix instance. Ok. So as you did not mention, what is your engine version and architecture? What is your engine status at blocking moment? Blocked Llog or LongTX? If so, we can try to help you more knowing about what is the sql command causing that high log consumption. Also suggest you to check the total number and sizes of the logs, the DYNAMIC_LOGS parameter (which should be set to the default value = 2. Please explain us how are these parameters, what was running exactly, and then we might help more. HTH Regards Alexandre Marini IBM Informix Certified Professional v10 / v11.50 / v11.70 / v12.10 IBM Informix on Cloud - Database Administrator - 2017 IBM dashDB Managed Service for Analytics and Transactions - 2017 DB2 Advanced DBA - v10.5 for LUW IBM Information Management Informix Technical Professional IBM Certified Developer - Informix Genero Informix independent consultant ________________________________ De: ids-bounces@iiug.org <ids-bounces@iiug.org> em nome de SERGIO PERES <sergio.peres@airc.pt> Enviado: quarta-feira, 21 de março de 2018 07:54 Para: ids@iiug.org Assunto: Re: transation logs problem [40884] 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.

Hi Alexandre, Thanks for your reply. I understand that informix needs logs for recovery, the use of BACKUPLOGS=Y was suggested by someone from IBM support time ago. We are using now 12.10.FC10 version over CentOS 7.4, in the past I just change LTAPEDEV to something and run regular ontape -a from scheduler. But, sometimes I have Long Transactions and sometimes the engines hanged. As I have refered, we do with some regularity DDL changes and on bigger tables we are facing the problem. For this specific case I have chenged LTAPEDEV to one fake file and run ontape -c. I would like to know if there is any way to avoid this type of problems, we have on customers several versions installed from 11.50.x to 12.10.FC10. Best regards, SP

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...

As explained in a previous message, the long transaction has nothing to do with backing up the logs. Regards. On Wed, Mar 21, 2018 at 1:09 PM, SERGIO PERES <sergio.peres@airc.pt> wrote: > Hi Alexandre, > > Thanks for your reply. > I understand that informix needs logs for recovery, the use of BACKUPLOGS=Y > was suggested by someone from IBM support time ago. > We are using now 12.10.FC10 version over CentOS 7.4, in the past I just > change > LTAPEDEV to something and run regular ontape -a from scheduler. But, > sometimes > I have Long Transactions and sometimes the engines hanged. > As I have refered, we do with some regularity DDL changes and on bigger > tables > we are facing the problem. > For this specific case I have chenged LTAPEDEV to one fake file and run > ontape > -c.> I would like to know if there is any way to avoid this type of problems, we > have on customers several versions installed from 11.50.x to 12.10.FC10. > > 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...

Ok Sergio, as I said before, any situation could recommend a specific turnaround, to avoid longtx/llog issues. As Fernando said, if you are adding/modifying a column in a huge table, you can follow his suggestions. Recreate the table is much easier, and there are several benefits (you can even redistribute data/indexes using fragmentation for better performance). HTH Regards Alexandre Marini IBM Informix Certified Professional v10 / v11.50 / v11.70 / v12.10 IBM Informix on Cloud - Database Administrator - 2017 IBM dashDB Managed Service for Analytics and Transactions - 2017 DB2 Advanced DBA - v10.5 for LUW IBM Information Management Informix Technical Professional IBM Certified Developer - Informix Genero Informix independent consultant (cel) +55 11 97603-0358 ________________________________ De: ids-bounces@iiug.org <ids-bounces@iiug.org> em nome de SERGIO PERES <sergio.peres@airc.pt> Enviado: quarta-feira, 21 de março de 2018 09:09 Para: ids@iiug.org Assunto: Re: RE: transation logs problem [40886] Hi Alexandre, Thanks for your reply. I understand that informix needs logs for recovery, the use of BACKUPLOGS=Y was suggested by someone from IBM support time ago. We are using now 12.10.FC10 version over CentOS 7.4, in the past I just change LTAPEDEV to something and run regular ontape -a from scheduler. But, sometimes I have Long Transactions and sometimes the engines hanged. As I have refered, we do with some regularity DDL changes and on bigger tables we are facing the problem. For this specific case I have chenged LTAPEDEV to one fake file and run ontape -c. I would like to know if there is any way to avoid this type of problems, we have on customers several versions installed from 11.50.x to 12.10.FC10. Best regards, SP ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Hi Fernando, Thanks for the information; I´ll try your suggestion to lower the values for LTXHWM, because on some servers to get bigger space for them is quite difficult. There is any formula/rule to estimate that size? Best regards, SP

There is no formula. But note that by changing the parameter to a lower value you will probably get more long transactions (or sooner). It will not solve the issue where the logs are not enough for the DDL changes you're doing. The good thing is that by lowering LTXHWM and creating more space between LTXHWM and LTXEHWM you reduce the chance of locking other transactions while it rolls back a long TX. By creating more space between LTXWHWM and 100 you reduce the chance of getting into an engine locked state, or having to add additional logs to complete the rollback. That's why I usually suggest 30/70. If you start a long transaction at 30, it's likely it will complete rollback before 70. Regards. On Thu, Mar 22, 2018 at 12:04 PM, SERGIO PERES <sergio.peres@airc.pt> wrote: > Hi Fernando, > > Thanks for the information; > I´ll try your suggestion to lower the values for LTXHWM, because on some > servers to get bigger space for them is quite difficult. > There is any formula/rule to estimate that size? > > 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...