Informix and thin provisioning

Hi all: We have several huge sbspaces in one of our instances (12.10FC7). After deleting almost all the blob rows contained in it Informix is showing the sbspaces as almost empty. But, from the storage point of view, no changes have been shown. As per aour tests, the problem is that Informix is not zeroing the free space of the sbspaces, so our storage array is not being able to use the "thin provisioning" capability. Our chunks are based on raw devices con SUSE linux, so we cannot interact with them directly from the OS. Anybody knows if there is any method to release this free space in the storage array? Thank you in advance and best regards.

Hi Luis, not sure I fully understand what you mean/want: You've logically emptied these sbspaces, i.e. deleted their sblob content using SQL means, so now ... you want some of the chunks' space returned to the OS? Or would you want to have all those freed sbspace pages zeroed so some storage magic would be able to 'thin provision', i.e. re-use, the space underneath? For obvious performance reasons, freeing space in Informix always was a logical-only operation, so the space only gets 'unassigned' from an object, but otherwise is left alone and its content untouched. So are you asking for getting FREE space zeroed out? So storage layer can re-purpose such unused areas to other users while Informix' chunks remain fully configured and available to Informix? Sounds interesting. I'd say one for our RFE list, please provide more details on this 'think provisioning'. Still having to find again in how far 'sparse files' had hurt us (we don't want them and make sure we don't have them) and in how far similar problems could arise with such thin provisioning. BR, Andreas From: "LUIS VENTURA" <luisventurap@gmail.com> To: ids@iiug.org Date: 12/13/2017 04:07 PM Subject: Informix and thin provisioning [40366] Sent by: ids-bounces@iiug.org Hi all: We have several huge sbspaces in one of our instances (12.10FC7). After deleting almost all the blob rows contained in it Informix is showing the sbspaces as almost empty. But, from the storage point of view, no changes have been shown. As per aour tests, the problem is that Informix is not zeroing the free space of the sbspaces, so our storage array is not being able to use the "thin provisioning" capability. Our chunks are based on raw devices con SUSE linux, so we cannot interact with them directly from the OS. Anybody knows if there is any method to release this free space in the storage array? Thank you in advance and best regards. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Need to test this, not sure if it would work... Informix 12.10.FC10 has the ability to swap primary and mirror chunks. What if you used Informix mirroring to mirror the chunks to new raw devices, swap primary/mirror chunks then drop the original chunks? Presumably when Informix adds mirror chunks on raw devices it would only copy the used space(?). I will play tonight and see if it works...if so I can add to the Informix FAQ! Regards, David. > On 13 December 2017 at 15:06 LUIS VENTURA <luisventurap@gmail.com> wrote: > > > Hi all: > > We have several huge sbspaces in one of our instances (12.10FC7). After > deleting almost all the blob rows contained in it Informix is showing the > sbspaces as almost empty. But, from the storage point of view, no changes have > been shown. > > As per aour tests, the problem is that Informix is not zeroing the free space > of the sbspaces, so our storage array is not being able to use the "thin > provisioning" capability. > > Our chunks are based on raw devices con SUSE linux, so we cannot interact with > them directly from the OS. > > Anybody knows if there is any method to release this free space in the storage > array? > > Thank you in advance and best regards. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

Keep an eye on the sys master entry for the chunks - see if they swap. j. > On Dec 13, 2017, at 11:28 AM, david@smooth1.co.uk wrote: > > Need to test this, not sure if it would work... > > Informix 12.10.FC10 has the ability to swap primary and mirror chunks. > > What if you used Informix mirroring to mirror the chunks to new raw devices, > swap primary/mirror chunks then drop the original chunks? > > Presumably when Informix adds mirror chunks on raw devices it would only copy > the used space(?). > > I will play tonight and see if it works...if so I can add to the Informix FAQ! > > Regards, > David. > >> On 13 December 2017 at 15:06 LUIS VENTURA <luisventurap@gmail.com> wrote: >> >> >> Hi all: >> >> We have several huge sbspaces in one of our instances (12.10FC7). After >> deleting almost all the blob rows contained in it Informix is showing the >> sbspaces as almost empty. But, from the storage point of view, no changes > have >> been shown. >> >> As per aour tests, the problem is that Informix is not zeroing the free > space >> of the sbspaces, so our storage array is not being able to use the "thin >> provisioning" capability. >> >> Our chunks are based on raw devices con SUSE linux, so we cannot interact > with >> them directly from the OS. >> >> Anybody knows if there is any method to release this free space in the > storage >> array? >> >> Thank you in advance and best regards. >> >> >> > ******************************************************************************* >> Forum Note: Use "Reply" to post a response in the discussion forum. >> > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

Hi Andreas , The major issue with thin provisioning would be that when raw devices are added Informix just checks it can access the 'end' of the chunk and unlike cooked files does not try and write to all of the chunk. If a raw device is thinly provisioned the obvious issue would be if the underlying pool runs out of space. You then get writing errors, not sure how these would be reported (ENOSPACE?). The question then is what does Informix do if it gets a write error on a raw device which is ENOSPACE? Regards, David. > On 13 December 2017 at 16:21 Andreas Legner1 <Andreas.Legner1@de.ibm.com> wrote: > > > Hi Luis, > > not sure I fully understand what you mean/want: > > You've logically emptied these sbspaces, i.e. deleted their sblob content > using SQL means, so now ... you want some of the chunks' space returned to > the OS? > Or would you want to have all those freed sbspace pages zeroed so some > storage magic would be able to 'thin provision', i.e. re-use, the space > underneath? > > For obvious performance reasons, freeing space in Informix always was a > logical-only operation, so the space only gets 'unassigned' from an object, > but otherwise is left alone and its content untouched. > So are you asking for getting FREE space zeroed out? So storage layer can > re-purpose such unused areas to other users while Informix' chunks remain > fully configured and available to Informix? > > Sounds interesting. I'd say one for our RFE list, please provide more > details on this 'think provisioning'. > > Still having to find again in how far 'sparse files' had hurt us (we don't > want them and make sure we don't have them) and in how far similar problems > could arise with such thin provisioning. > > BR, > Andreas > > From: "LUIS VENTURA" <luisventurap@gmail.com> > To: ids@iiug.org > Date: 12/13/2017 04:07 PM > Subject: Informix and thin provisioning [40366] > Sent by: ids-bounces@iiug.org > > Hi all: > > We have several huge sbspaces in one of our instances (12.10FC7). After > deleting almost all the blob rows contained in it Informix is showing the > sbspaces as almost empty. But, from the storage point of view, no changes > have > been shown. > > As per aour tests, the problem is that Informix is not zeroing the free > space > of the sbspaces, so our storage array is not being able to use the "thin > provisioning" capability. > > Our chunks are based on raw devices con SUSE linux, so we cannot interact > with > them directly from the OS. > > Anybody knows if there is any method to release this free space in the > storage > array? > > Thank you in advance and best regards. > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

Hi Andreas: Exactly, what we need is zeroing the free part of the chunks to make this space available from the storage array point of view. I know it could sound weird, but I know that this space won't be needed by Informix. I'll post the full picture to help you understand the exact situation: - This is a test environment used to do full restores of our production database (which actually has 9Tb of blobs) - Once restored, we run a stored procedure which updates almost all of these blobs to the same binary file. Due to the smart blobs way of work this creates million of pointers to one file, and free the most of the sbspace area. So, after running this procedure, we have nine sbspaces with 12Tb reserved space but only 300Gb of used space. At the storage array level, I can see 9Tb occupied, as it has been used during the restore. - At this point, I would like to return all this free space to the storage array, as I know that this space won't be used again. As this is a test environment, we have tried to move all the blobs to the first sbspace to get the rest dropped and recreated after doing a dd if=/dev/zero of=/dev/anydev, but for some reason we are not able to completely move all the objects (I've opened a case with Tech Support for this, as it seems that we have some kind of orphaned blobs...) So, as I cannot drop the chunks, I'd need a way to free this space without dropping the chunks...

It seems an excelent idea. It would be great if you could post the reaults. Best regards.

The trial downloads still do not include FC10 so I cannot try it. Can someone chase IBM? Regards, David. > On 13 December 2017 at 16:37 Jack Parker <jack.parker4@verizon.net> wrote: > > > Keep an eye on the sys master entry for the chunks - see if they swap. > > j. > > > On Dec 13, 2017, at 11:28 AM, david@smooth1.co.uk wrote: > > > > Need to test this, not sure if it would work... > > > > Informix 12.10.FC10 has the ability to swap primary and mirror chunks. > > > > What if you used Informix mirroring to mirror the chunks to new raw devices, > > swap primary/mirror chunks then drop the original chunks? > > > > Presumably when Informix adds mirror chunks on raw devices it would only > copy > > the used space(?). > > > > I will play tonight and see if it works...if so I can add to the Informix > FAQ! > > > > Regards, > > David. > > > >> On 13 December 2017 at 15:06 LUIS VENTURA <luisventurap@gmail.com> wrote: > >> > >> > >> Hi all: > >> > >> We have several huge sbspaces in one of our instances (12.10FC7). After > >> deleting almost all the blob rows contained in it Informix is showing the > >> sbspaces as almost empty. But, from the storage point of view, no changes > > have > >> been shown. > >> > >> As per aour tests, the problem is that Informix is not zeroing the free > > space > >> of the sbspaces, so our storage array is not being able to use the "thin > >> provisioning" capability. > >> > >> Our chunks are based on raw devices con SUSE linux, so we cannot interact > > with > >> them directly from the OS. > >> > >> Anybody knows if there is any method to release this free space in the > > storage > >> array? > >> > >> Thank you in advance and best regards. > >> > >> > >> > > > ******************************************************************************* > >> Forum Note: Use "Reply" to post a response in the discussion forum. > >> > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

Well, there might be an easier solution for this: before running those big updates, on sblob columns, create a new small sbspace, modify those sblob columns to use this new sbspace, then run the update. Your dummy sblob(s) now should reside in new sbspace and old ones should be completely unused so you can drop them entirely. Maybe too simple a thought ? From: "LUIS VENTURA" <luisventurap@gmail.com> To: ids@iiug.org Date: 12/13/2017 06:01 PM Subject: Re: Informix and thin provisioning [40372] Sent by: ids-bounces@iiug.org Hi Andreas: Exactly, what we need is zeroing the free part of the chunks to make this space available from the storage array point of view. I know it could sound weird, but I know that this space won't be needed by Informix. I'll post the full picture to help you understand the exact situation: - This is a test environment used to do full restores of our production database (which actually has 9Tb of blobs) - Once restored, we run a stored procedure which updates almost all of these blobs to the same binary file. Due to the smart blobs way of work this creates million of pointers to one file, and free the most of the sbspace area. So, after running this procedure, we have nine sbspaces with 12Tb reserved space but only 300Gb of used space. At the storage array level, I can see 9Tb occupied, as it has been used during the restore. - At this point, I would like to return all this free space to the storage array, as I know that this space won't be used again. As this is a test environment, we have tried to move all the blobs to the first sbspace to get the rest dropped and recreated after doing a dd if=/dev/zero of=/dev/anydev, but for some reason we are not able to completely move all the objects (I've opened a case with Tech Support for this, as it seems that we have some kind of orphaned blobs...) So, as I cannot drop the chunks, I'd need a way to free this space without dropping the chunks... ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

In fact this is what we try when doing these massive updates to get all of our blobs (around 100 million rows) pointing to some dummy blobs placed in the first sbspace. The idea was to completely free the rest of the sbspaces to get these dropped, but we have seen that, although we have updated all of our blobs, there are around 200 blobs placed in the "empty" dbspaces. We're trying to find out where come these blobs from, as per the info we see in our tables no one is referencing them (although they have a ref count = 1). We have opened a ticket for this issue, as we think that they are orphaned objects.

I'm replying without thinking (almost) after a tiring day.... so take this lightly... But I think you can restore without those chunks.... it should give you an error but should not stop. I know we have a customer doing that with normal dbspaces..... Luis Marques.... are you there?.... On Dec 13, 2017 5:28 PM, "LUIS VENTURA" <luisventurap@gmail.com> wrote: > In fact this is what we try when doing these massive updates to get all of > our > blobs (around 100 million rows) pointing to some dummy blobs placed in the > first sbspace. The idea was to completely free the rest of the sbspaces to > get > these dropped, but we have seen that, although we have updated all of our > blobs, there are around 200 blobs placed in the "empty" dbspaces. We're > trying > to find out where come these blobs from, as per the info we see in our > tables > no one is referencing them (although they have a ref count = 1). We have > opened a ticket for this issue, as we think that they are orphaned objects. > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >

Luis, Can you get downtime to run oncheck -cS on just the affected sbspace? [1]https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ib m.adref.doc/ids_adr_0381.htm Luis/Andreas, The documentation above seems wrong, surely it should read as follows " The -cs option checks sbspaces. The -ps option checks sbspaces and extents. The -cS option validates and displays metadata for an sbspace checks sbspaces and extents. If you do not specify the sbspace name, these options check all sbspaces. The -ps option validates and displays metadata for an sbspace. If you do not specify the sbspace name, these options check all sbspaces. The -pS option validates and displays metadata for an sbspace and also lists extents and header information for smart large objects. " Feedback sent to IBM Knowledge Centre. Regards, David. On 13 December 2017 at 17:27 LUIS VENTURA <luisventurap@gmail.com> wrote: In fact this is what we try when doing these massive updates to get all of our blobs (around 100 million rows) pointing to some dummy blobs placed in the first sbspace. The idea was to completely free the rest of the sbspaces to get these dropped, but we have seen that, although we have updated all of our blobs, there are around 200 blobs placed in the "empty" dbspaces. We're trying to find out where come these blobs from, as per the info we see in our tables no one is referencing them (although they have a ref count = 1). We have opened a ticket for this issue, as we think that they are orphaned objects. ********************************************************************** ********* Forum Note: Use "Reply" to post a response in the discussion forum. References 1. https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.adref.doc/i ds_adr_0381.htm

Hi Fernando. Sure I can, but this is not a valid solution for us. This environment, after the restore, becames into an important test environment for our developers. If I exclude the sbspaces in the restore, although I can use the instance, every time I try to use a blob field I'll receive an error (-9810 If I'm not wrong...). Anyway, than you for your suggestion.

Sure I can! In fact I've already run oncheck -pS and -cS for all sbspaces. Tomorrow I'll post the results for one of them. Best regards.

this all hinges on whether or not the engine does a size check on the chunk(s) its restoring before it does any writing back of the data: you *may* be able to : After deleting all the blobs and getting the "actual" used space down to 300 GB, take a level 0 archive. On the storage array drop the raw volume and reconfigure it again at a size somewhat larger than the 300GB needed. Restore the sblob dbspace. If I am not mistaken Informix only complains when it runs out of space writing the data during the restore (in other words it will not do a size check before restoring the chunk). The con to this is that the dbspace will still report a dbspace/chunk of 9TB but in reality ....

Hi david: This is the oncheck -cS for the last (and one of the "should be free") sbspace: Validating space 'imgsdbs10' ... sbspace Metadata Partition Partnum Used Free imgsdbs10:'informix'.TBLSpace 0x2100001 14 36 imgsdbs10:'informix'.sbspace_desc 0x2100002 2 2 imgsdbs10:'informix'.chunk_adjunc 0x2100003 6 2 imgsdbs10:'informix'.LO_ud_free 0x2100004 95 326491 imgsdbs10:'informix'.LO_hdr_partn 0x2100005 919007 867381 imgsdbs10:'informix'.LO_ud_free 0x2100006 588 325998 imgsdbs10:'informix'.LO_hdr_partn 0x2100007 919132 867265 imgsdbs10:'informix'.LO_ud_free 0x2100008 3686 322900 imgsdbs10:'informix'.LO_hdr_partn 0x2100009 367715 1489268 imgsdbs10:'informix'.LO_ud_free 0x210000a 1957 271865 imgsdbs10:'informix'.LO_hdr_partn 0x210000b 231523 1314764 imgsdbs10:'informix'.LO_ud_free 0x210000c 3213 416218 imgsdbs10:'informix'.LO_hdr_partn 0x210000d 109733 1987419 Large Objects ID Ref Size Allocced Creat Last Sbs# Chk# Seq# Cnt (Bytes) Pages Extns Flags Modified ---- ---- ----- ---- ---------- -------- ----- ----- ------------------------ 33 92 2656790 1 179552 109 1 N-N-H Wed Nov 11 07:07:56 2015 33 92 2959768 1 181785 110 1 N-N-H Wed Jun 1 10:19:20 2016 Large Objects ID Ref Size Allocced Creat Last Sbs# Chk# Seq# Cnt (Bytes) Pages Extns Flags Modified ---- ---- ----- ---- ---------- -------- ----- ----- ------------------------ 33 93 2223249 1 183179 115 2 N-N-H Wed Nov 11 07:05:34 2015 33 93 2223250 1 181093 110 1 N-N-H Wed Nov 11 07:02:37 2015 33 93 2223251 1 178783 109 1 N-N-H Wed Nov 11 07:02:39 2015 33 93 2223252 1 181649 101 1 N-N-H Wed Nov 11 07:02:41 2015 33 93 2223267 1 178760 112 2 N-N-H Mon Jan 11 09:41:54 2016 33 93 2223302 1 179048 104 2 N-N-H Wed Nov 11 07:04:50 2015 33 93 2223304 1 181061 110 1 N-N-H Wed Nov 11 07:04:56 2015 33 93 2223308 1 181892 110 1 N-N-H Wed Nov 11 07:05:06 2015 33 93 2223319 1 179189 109 1 N-N-H Wed Nov 11 07:05:36 2015 33 93 2223322 1 180004 110 1 N-N-H Wed Nov 11 07:05:41 2015 33 93 2223326 1 179197 109 1 N-N-H Mon Jan 11 09:41:08 2016 33 93 2223327 1 180402 93 1 N-N-H Wed Nov 11 07:05:52 2015 33 93 2223328 1 179570 109 1 N-N-H Wed Nov 11 07:05:55 2015 33 93 2223329 1 179874 91 1 N-N-H Wed Nov 11 07:05:57 2015 33 93 2223330 1 180382 112 2 N-N-H Mon Jan 11 09:41:11 2016 33 93 2223341 1 183900 113 3 N-N-H Wed Nov 11 07:06:31 2015 33 93 2223364 1 180322 94 3 N-N-H Mon Jan 11 09:41:51 2016 33 93 2223373 1 179598 109 1 N-N-H Wed Nov 11 07:08:05 2015 33 93 2223375 1 181981 100 1 N-N-H Wed Nov 11 07:08:09 2015 33 93 2223376 1 191589 114 3 N-N-H Wed Nov 11 07:08:11 2015 33 93 2223377 1 181004 114 2 N-N-H Wed Nov 11 07:08:13 2015 33 93 2223379 1 227404 122 1 N-N-H Wed Nov 11 07:08:16 2015 33 93 2223380 1 180579 105 2 N-N-H Wed Nov 11 07:08:20 2015 33 93 2223387 1 181992 110 2 N-N-H Wed Nov 11 07:06:29 2015 33 93 2800073 1 179131 89 1 N-N-H Fri Feb 12 13:14:05 2016 33 93 3450480 1 30778 16 1 N-N-H Mon May 29 14:35:18 2017 33 93 3456636 1 30894 16 1 N-N-H Mon May 29 14:35:09 2017 Large Objects ID Ref Size Allocced Creat Last Sbs# Chk# Seq# Cnt (Bytes) Pages Extns Flags Modified ---- ---- ----- ---- ---------- -------- ----- ----- ------------------------ 33 105 103854 1 179404 112 2 N-N-H Wed Nov 11 07:06:34 2015 33 105 536833 1 180159 90 2 N-N-H Mon Jan 11 09:40:50 2016 Large Objects ID Ref Size Allocced Creat Last Sbs# Chk# Seq# Cnt (Bytes) Pages Extns Flags Modified ---- ---- ----- ---- ---------- -------- ----- ----- ------------------------ 33 113 249891 1 225216 112 1 N-N-H Wed Jun 29 10:51:38 2016 33 113 340729 1 184724 92 1 N-N-H Thu Sep 8 12:46:34 2016