IDS11.50.FC3 backup sequencing

Hello all.... We have just upgraded from IDS9.3 to IDS11.50FC3.... All appears fine apart from the backup sequence whic I would like to change.... I have googled the problem but can not fine a definative answer..so hopefully you guys will have the answer. Within the sysutils db table bar_instance does have the following column ins_backup_order integer no the bar_orederseq has the following... seqserial8 serial8 no and bar_oredertab dummy integer yes I guess the bar_orederseq should be changed from sequential. The question is what is the sql? thanks in advance. Howard

What do you think is wrong and what would you like to change it to? Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, May 13, 2009 at 7:24 AM, howie.lfc@googlemail.com < howie.lfc@googlemail.com> wrote: > Hello all.... > > We have just upgraded from IDS9.3 to IDS11.50FC3.... > > All appears fine apart from the backup sequence whic I would like to > change.... > > I have googled the problem but can not fine a definative answer..so > hopefully you guys will have the answer. > > Within the sysutils db table bar_instance does have the following > column > ins_backup_order integer no > > the bar_orederseq has the following... > seqserial8 serial8 no > > and bar_oredertab > dummy integer yes > > I guess the bar_orederseq should be changed from sequential. The > question is what is the sql? > > thanks in advance. > > Howard > > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >

On 13 May, 12:29, Art Kagel <art.ka...@gmail.com> wrote: > What do you think is wrong and what would you like to change it to? > > Art > > Art S. Kagel > Oninit (www.oninit.com) > IIUG Board of Directors (a...@iiug.org) > > Disclaimer: Please keep in mind that my own opinions are my own opinions and > do not reflect on my employer, Oninit, the IIUG, nor any other organization > with which I am associated either explicitly or implicitly.  Neither do > those opinions reflect those of other individuals affiliated with any entity > with which I am affiliated nor those of the entities themselves. > > On Wed, May 13, 2009 at 7:24 AM, howie....@googlemail.com < > > > > howie....@googlemail.com> wrote: > > Hello all.... > > > We have just upgraded from IDS9.3 to IDS11.50FC3.... > > > All appears fine apart from the backup sequence whic I would like to > > change.... > > > I have googled the problem but can not fine a definative answer..so > > hopefully you guys will have the answer. > > > Within the sysutils db table bar_instance does have the following > > column > > ins_backup_order     integer                                 no > > > the bar_orederseq has the following... > > seqserial8           serial8                                 no > > > and bar_oredertab > > dummy                integer                                 yes > > > I guess the bar_orederseq should be changed from sequential. The > > question is what is the sql? > > > thanks in advance. > > > Howard > > > _______________________________________________ > > Informix-list mailing list > > Informix-l...@iiug.org > >http://www.iiug.org/mailman/listinfo/informix-list- Hide quoted text - > > - Show quoted text - Sorry Art...the issue is we have a couple of very large dbspaces which we would like start backing up at the beginning of the sequence in parallel. At the moment they are only backed up at the end of the sequency and consequently adding a further 2 hours to the backup time. Cheers

howie.lfc@googlemail.com wrote: > On 13 May, 12:29, Art Kagel <art.ka...@gmail.com> wrote: >> What do you think is wrong and what would you like to change it to? <snip> > > Sorry Art...the issue is we have a couple of very large dbspaces which > we would like start backing up at the beginning of the sequence in > parallel. At the moment they are only backed up at the end of the > sequency and consequently adding a further 2 hours to the backup time. > > Cheers Hmmm ... I think you may be referring to : BAR_SORT_DBS Environment Variable For 10.00.xC4, the BAR_SORT_DBS environment variable was introduced to specify a more efficient sorting order for parallel backup and restore. Starting with version 11.10.xC1, this sorting order is now automatic and the BAR_SORT_DBS environment variable is no longer implemented. Can you confirm the actual onbar command you are using? Can you confirm what you have BAR_MAX_BACKUP set to? Can you confirm how many devices / streams the storage manager has? JJ (Yeah, well, whatever)

Oh, I was under the impression that the sequence of the dbspaces backed up could be configured. we are using onbar -b -L 0, BAR_MAX_BACKUP is set to 0 and there is 1 device with 4 concurrent streams to it.

howie.lfc@googlemail.com wrote: > Oh, I was under the impression that the sequence of the dbspaces > backed up could be configured. > > we are using onbar -b -L 0, BAR_MAX_BACKUP is set to 0 and there is 1 > device with 4 concurrent streams to it. > (extract from the 11.50 manual) =============================== Automatic Ordering of dbspaces during Backup and Restore IDS now makes intelligent decisions regarding the ordering of dbspaces during backup and restore to achieve maximum parallelism, thus reducing the backup and restore time necessary. For example, if the largest dbspace is backed up in parallel to other smaller dbspaces, the complete system backup will take less time. During a restore, dbspaces are restored in the same order in which they were backed up, reducing restore time. =============================== Hmmm, so ... can you provide an onstat -d? If you have loads of dbspaces, you are just going to kick all non-critical dbspace backups at the same time with BAR_MAX_BACKUP set to 0, if you have 4 *available* streams to backup, then set BAR_MAX_BACKUP to 4, and you will probably see the effect that you want.

On May 13, 6:24 am, "howie....@googlemail.com" <howie....@googlemail.com> wrote: > Hello all.... > > We have just upgraded from IDS9.3 to IDS11.50FC3.... > > All appears fine apart from the backup sequence whic I would like to > change.... > > I have googled the problem but can not fine a definative answer..so > hopefully you guys will have the answer. > > Within the sysutils db table bar_instance does have the following > column > ins_backup_order     integer                                 no > > the bar_orederseq has the following... > seqserial8           serial8                                 no > > and bar_oredertab > dummy                integer                                 yes > > I guess the bar_orederseq should be changed from sequential. The > question is what is the sql? > > thanks in advance. > > Howard As previously mentioned, in 11.50 it should be sorting the dbspaces automatically. If you set BAR_DEBUG to 8 and at least start up your archive (I believe you should be able to interrupt after it starts processing the actual backup) if you get the BAR_DEBUG_LOG file you should see the some output that shows the unsorted dbspace list and then the dbspace list after it gets sorted by the estimated size, and that output should also show the size estimate it's coming up with. You can look for "bar_sort_list: BEFORE list sort:" and "bar_sort_list: AFTER list sort:" to see the dbspace lists. Jacques Renaut IBM IDS APD team

On 13 May, 18:52, jpren...@yahoo.com wrote: > On May 13, 6:24 am, "howie....@googlemail.com" > > > > > > <howie....@googlemail.com> wrote: > > Hello all.... > > > We have just upgraded from IDS9.3 to IDS11.50FC3.... > > > All appears fine apart from the backup sequence whic I would like to > > change.... > > > I have googled the problem but can not fine a definative answer..so > > hopefully you guys will have the answer. > > > Within the sysutils db table bar_instance does have the following > > column > > ins_backup_order     integer                                 no > > > the bar_orederseq has the following... > > seqserial8           serial8                                 no > > > and bar_oredertab > > dummy                integer                                 yes > > > I guess the bar_orederseq should be changed from sequential. The > > question is what is the sql? > > > thanks in advance. > > > Howard > > As previously mentioned, in 11.50 it should be sorting the dbspaces > automatically.  If you set BAR_DEBUG to 8 and at least start up your > archive (I believe you should be able to interrupt after it starts > processing the actual backup) if you get the BAR_DEBUG_LOG file you > should see the some output that shows the unsorted dbspace list and > then the dbspace list after it gets sorted by the estimated size, and > that output should also show the size estimate it's coming up with. > You can look for "bar_sort_list: BEFORE list sort:" and > "bar_sort_list: AFTER list sort:" to see the dbspace lists. > > Jacques Renaut > IBM IDS APD team- Hide quoted text - > > - Show quoted text - thanks for that Jacques....quite confusing really as the largest dbspaces are only being backed up at the end of the process.

On May 13, 1:00 pm, "howie....@googlemail.com" <howie....@googlemail.com> wrote: > On 13 May, 18:52, jpren...@yahoo.com wrote: > > > > > On May 13, 6:24 am, "howie....@googlemail.com" > > > <howie....@googlemail.com> wrote: > > > Hello all.... > > > > We have just upgraded from IDS9.3 to IDS11.50FC3.... > > > > All appears fine apart from the backup sequence whic I would like to > > > change.... > > > > I have googled the problem but can not fine a definative answer..so > > > hopefully you guys will have the answer. > > > > Within the sysutils db table bar_instance does have the following > > > column > > > ins_backup_order     integer                                 no > > > > the bar_orederseq has the following... > > > seqserial8           serial8                                 no > > > > and bar_oredertab > > > dummy                integer                                 yes > > > > I guess the bar_orederseq should be changed from sequential. The > > > question is what is the sql? > > > > thanks in advance. > > > > Howard > > > As previously mentioned, in 11.50 it should be sorting the dbspaces > > automatically.  If you set BAR_DEBUG to 8 and at least start up your > > archive (I believe you should be able to interrupt after it starts > > processing the actual backup) if you get the BAR_DEBUG_LOG file you > > should see the some output that shows the unsorted dbspace list and > > then the dbspace list after it gets sorted by the estimated size, and > > that output should also show the size estimate it's coming up with. > > You can look for "bar_sort_list: BEFORE list sort:" and > > "bar_sort_list: AFTER list sort:" to see the dbspace lists. > > > Jacques Renaut > > IBM IDS APD team- Hide quoted text - > > > - Show quoted text - > > thanks for that Jacques....quite confusing really as the largest > dbspaces are only being backed up at the end of the process. Well, it could be that they are largest in size and space allocated, but aren't largest in actual used pages (like big table with big extent size, but the pages in the extent aren't actually used yet so onstat -d shows little free space but actual pages backed up is a much smaller amount).

Hi, the original problem (I think) is still that BAR_MAX_BACKUP is set to 0 (as was mentioned in an earlier e-mail). This causes ON-Bar after backup of the root dbspace (which is always done first and not in parallel with the other dbspaces) to start the backup of the remaining dbspaces all at once. BAR_MAX_BACKUP set to 0 means that there is no limit to parallelism of dbspace backup. Even though ON-Bar will have sorted the list of dbspaces correctly, this sort order will not matter as all the backups of individual dbspaces will be started at the same time. So there is no ordering (actually ON-Bar could do without creating the sort list in this special case, except for the fact that it may be of interest for a restore - if that is done with a BAR_MAX_BACKUP setting other than 0). Once ON-Bar has started all dbspace backup processes (almost at the same time), everything else is at the mercy of the OS and the process scheduling. I.e. not determined by ON-Bar. From ON-Bar side there should not be much of a time difference (a little is normal) for starting the backups. You should be able to verify that in your BAR Activity log file. But it also is natural that the processes with the most work to do will finish last. And depending on the OS process scheduling method in effect, it may even be that long running and I/O intensive processes are further "delayed" to let short running processes advance and finish earlier. Also, as said before, the criteria for the sort order of dbspaces for backup is not the size of the dbspace, but how many pages in a dbspace have been used (as these will be backed up, but empty pages are not). However, I guess that is not of a significance in your case as you reported that these dbspaces really take longer to backup ... so they are the ones with the most pages used. As described, you can try with BAR_DEBUG setting to see the list and sorted list of dbspaces in the trace file output. Regards, Martin -- Martin Fuerderer IBM Informix Development Munich, Germany Information Management IBM Deutschland Research & Development GmbH Chairman of the Supervisory Board: Martin Jetter Board of Management: Erich Baier Corporate Seat: Boeblingen, Germany Reg.-Gericht: Amtsgericht Stuttgart, HRB 243294 informix-list-bounces@iiug.org wrote on 13.05.2009 20:51:35: > On May 13, 1:00 pm, "howie....@googlemail.com" > <howie....@googlemail.com> wrote: > > On 13 May, 18:52, jpren...@yahoo.com wrote: > > > > > > > > > On May 13, 6:24 am, "howie....@googlemail.com" > > > > > <howie....@googlemail.com> wrote: > > > > Hello all.... > > > > > > We have just upgraded from IDS9.3 to IDS11.50FC3.... > > > > > > All appears fine apart from the backup sequence whic I would like to > > > > change.... > > > > > > I have googled the problem but can not fine a definative answer..so > > > > hopefully you guys will have the answer. > > > > > > Within the sysutils db table bar_instance does have the following > > > > column > > > > ins_backup_order integer no > > > > > > the bar_orederseq has the following... > > > > seqserial8 serial8 no > > > > > > and bar_oredertab > > > > dummy integer yes > > > > > > I guess the bar_orederseq should be changed from sequential. The > > > > question is what is the sql? > > > > > > thanks in advance. > > > > > > Howard > > > > > As previously mentioned, in 11.50 it should be sorting the dbspaces > > > automatically. If you set BAR_DEBUG to 8 and at least start up your > > > archive (I believe you should be able to interrupt after it starts > > > processing the actual backup) if you get the BAR_DEBUG_LOG file you > > > should see the some output that shows the unsorted dbspace list and > > > then the dbspace list after it gets sorted by the estimated size, and > > > that output should also show the size estimate it's coming up with. > > > You can look for "bar_sort_list: BEFORE list sort:" and > > > "bar_sort_list: AFTER list sort:" to see the dbspace lists. > > > > > Jacques Renaut > > > IBM IDS APD team- Hide quoted text - > > > > > - Show quoted text - > > > > thanks for that Jacques....quite confusing really as the largest > > dbspaces are only being backed up at the end of the process. > > Well, it could be that they are largest in size and space allocated, > but aren't largest in actual used pages (like big table with big > extent size, but the pages in the extent aren't actually used yet so > onstat -d shows little free space but actual pages backed up is a much > smaller amount). > _______________________________________________> Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list

right then.....thanks for all your input. Here is what I am going to try. On Sunday when the system is rebooted I will change the BAR_MAX_BACKUP from 0 to 4 and set the BAR_DEBUG to 8 and let you all know how we get on.

Hi Howard, one note: Not sure if you have tried using BAR_DEBUG before. But BAR_DEBUG set to 8 is a high level. Therefore it will produce a lot of tracing, and therefore it will slow down things _considerably_! The dbspace list generation and sorting is done fairly early - as far as I remember :) So you may run a backup attempt with BAR_DEBUG set to 8 only up to the point where the lists are generated and output to the trace file ... and after that cancel the whole backup operation, reset the BAR_DEBUG parameter to 0 and start it again for doing a real backup. Otherwise the backup with BAR_DEBUG set to 8 may not be able to complete over the weekend or your disk with the trace file may get full ... Not to be disappointed on Monday morning, they usually are bad enough without such surprises, no? ;-) TIA, Martin -- Martin Fuerderer IBM Informix Development Munich, Germany Information Management IBM Deutschland Research & Development GmbH Chairman of the Supervisory Board: Martin Jetter Board of Management: Erich Baier Corporate Seat: Boeblingen, Germany Reg.-Gericht: Amtsgericht Stuttgart, HRB 243294 informix-list-bounces@iiug.org wrote on 14.05.2009 10:55:24: > right then.....thanks for all your input. Here is what I am going to > try. On Sunday when the system is rebooted I will change the > BAR_MAX_BACKUP from 0 to 4 and set the BAR_DEBUG to 8 and let you all > know how we get on. > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list

On 14 May, 08:43, Martin Fuerderer <MARTI...@de.ibm.com> wrote: > Hi, > > the original problem (I think) is still that BAR_MAX_BACKUP > is set to 0 (as was mentioned in an earlier e-mail). > This causes ON-Bar after backup of the root dbspace (which > is always done first and not in parallel with the other > dbspaces) to start the backup of the remaining dbspaces > all at once. BAR_MAX_BACKUP set to 0 means that > there is no limit to parallelism of dbspace backup. > > Even though ON-Bar will have sorted the list of dbspaces > correctly, this sort order will not matter as all the backups > of individual dbspaces will be started at the same time. > So there is no ordering (actually ON-Bar could do without > creating the sort list in this special case, except for the fact > that it may be of interest for a restore - if that is done > with a BAR_MAX_BACKUP setting other than 0). > > Once ON-Bar has started all dbspace backup processes > (almost at the same time), everything else is at the mercy > of the OS and the process scheduling. I.e. not determined > by ON-Bar. From ON-Bar side there should not be much > of a time difference (a little is normal) for starting the backups. > You should be able to verify that in your BAR Activity log > file. But it also is natural that the processes with the > most work to do will finish last. And depending on the > OS process scheduling method in effect, it may even be that > long running and I/O intensive processes are further "delayed" > to let short running processes advance and finish earlier. > > Also, as said before, the criteria for the sort order of dbspaces > for backup is not the size of the dbspace, but how many > pages in a dbspace have been used (as these will be backed > up, but empty pages are not). However, I guess that is not > of a significance in your case as you reported that these > dbspaces really take longer to backup ... so they are the ones > with the most pages used. > As described, you can try with BAR_DEBUG setting to see > the list and sorted list of dbspaces in the trace file output. > > Regards, Martin > -- > Martin Fuerderer > IBM Informix Development Munich, Germany > Information Management > > IBM Deutschland Research & Development GmbH > Chairman of the Supervisory Board: Martin Jetter > Board of Management: Erich Baier > Corporate Seat: Boeblingen, Germany > Reg.-Gericht: Amtsgericht Stuttgart, HRB 243294 > > informix-list-boun...@iiug.org wrote on 13.05.2009 20:51:35: > > > > > > > On May 13, 1:00 pm, "howie....@googlemail.com" > > <howie....@googlemail.com> wrote: > > > On 13 May, 18:52, jpren...@yahoo.com wrote: > > > > > On May 13, 6:24 am, "howie....@googlemail.com" > > > > > <howie....@googlemail.com> wrote: > > > > > Hello all.... > > > > > > We have just upgraded from IDS9.3 to IDS11.50FC3.... > > > > > > All appears fine apart from the backup sequence whic I would like > to > > > > > change.... > > > > > > I have googled the problem but can not fine a definative > answer..so > > > > > hopefully you guys will have the answer. > > > > > > Within the sysutils db table bar_instance does have the following > > > > > column > > > > > ins_backup_order     integer                                 no > > > > > > the bar_orederseq has the following... > > > > > seqserial8           serial8                                 no > > > > > > and bar_oredertab > > > > > dummy                integer                                 yes > > > > > > I guess the bar_orederseq should be changed from sequential. The > > > > > question is what is the sql? > > > > > > thanks in advance. > > > > > > Howard > > > > > As previously mentioned, in 11.50 it should be sorting the dbspaces > > > > automatically.  If you set BAR_DEBUG to 8 and at least start up your > > > > archive (I believe you should be able to interrupt after it starts > > > > processing the actual backup) if you get the BAR_DEBUG_LOG file you > > > > should see the some output that shows the unsorted dbspace list and > > > > then the dbspace list after it gets sorted by the estimated size, > and > > > > that output should also show the size estimate it's coming up with. > > > > You can look for "bar_sort_list: BEFORE list sort:" and > > > > "bar_sort_list: AFTER list sort:" to see the dbspace lists. > > > > > Jacques Renaut > > > > IBM IDS APD team- Hide quoted text - > > > > > - Show quoted text - > > > > thanks for that Jacques....quite confusing really as the largest > > > dbspaces are only being backed up at the end of the process. > > > Well, it could be that they are largest in size and space allocated, > > but aren't largest in actual used pages (like big table with big > > extent size, but the pages in the extent aren't actually used yet so > > onstat -d shows little free space but actual pages backed up is a much > > smaller amount). > > _______________________________________________> > Informix-list mailing list > > Informix-l...@iiug.org > >http://www.iiug.org/mailman/listinfo/informix-list- Hide quoted text - > > - Show quoted text - I will stop the backup as soon as I have the info needed