Informix

 View Only
Expand all | Collapse all

Repack/Shrink With In-Table BYTE Columns

  • 1.  Repack/Shrink With In-Table BYTE Columns

    IBM Champion
    Posted Tue March 08, 2022 03:05 PM

    All:

    I have a couple of tables that have millions of records and each of which has one BYTE column stored in-table. I've deleted over a million records from each of them (probably accounting for about 35% of the total records in the table) and then did 'table repack shrink,' which claims to have succeeded. However, no space was actually reclaimed. The records were inserted years ago and we back up logical logs on an ongoing basis, so I can't imagine the must-also-back-up-the-log thing is an issue. I've done the same thing with several other tables that do not contain BYTE or TEXT types and the space reduction was immediately apparent for those. EXTENT and NEXT sizes are sufficiently small that we should see plenty of space freed up. I ran the repack shrink multiple times and still nothing.

    Has anyone else encountered anything like this? Am I looking at a bug? Missing something obvious?

    IDS 14.10.FC7W1 on Linux x86_64

    TIA,

    - TJG



    ------------------------------
    TOM GIRSCH
    ------------------------------

    #Informix


  • 2.  RE: Repack/Shrink With In-Table BYTE Columns

    IBM Champion
    Posted Tue March 08, 2022 03:12 PM
    Tom:

    What does the oncheck -pT report show?

    Art

    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------



  • 3.  RE: Repack/Shrink With In-Table BYTE Columns

    IBM Champion
    Posted Tue March 08, 2022 03:25 PM
    Preliminary output (waiting for the detailed stuff to complete):

        Number of pages allocated      12281265  
        Number of pages used           12281265  
        Number of data pages           812482    
        Number of rows                 27955341  

    Looks like it isn't freeing up the blobpages.

    Tom Girsch

    Lead System Architect
    Auto Europe Group
    tgirsch@autoeurope.com
    Office #207-842-2139


    " If you think there is something more important than a Client ... think again "





  • 4.  RE: Repack/Shrink With In-Table BYTE Columns

    IBM Champion
    Posted Tue March 08, 2022 03:40 PM
    Tom:

    I think I know why the blob pages are not being freed. Unlike BLOBSPACE blobs, for TABLESPACE blobs, if the blob entry is smaller than a page multiple blobs are stored on a page. If when you deleted the old rows their blobs were on pages that still have other blobs on them, those pages couldn't be freed. It may be that the REPACK code doesn't move the blobs to consolidate them onto fewer pages. If so, when the details from the oncheck come in, they will show lots of free space on blob pages.

    Anyone at HCL want to comment?

    Art

    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------



  • 5.  RE: Repack/Shrink With In-Table BYTE Columns

    IBM Champion
    Posted Tue March 08, 2022 03:45 PM
    I'm guessing you're looking for this from the oncheck -pT. Note over 5.5 million "Free" pages, but they ain't freeing.

        Type                  Pages      Empty  Semi-Full       Full  Very-Full
        ---------------- ---------- ---------- ---------- ---------- ----------
        Free                5522396
        Bit-Map                3046
        Index                     0
        Data (Home)          812483
        Data (Remainder)          1          0          1          0          0
        TBLspace BLOBs      5943339          0    1247033    3847314     848992
                         ----------
        Total Pages        12281265

        Unused Space Summary

            Unused data bytes in Home pages           172743481
            Unused data bytes in Remainder pages           1631
            Unused bytes in TBLspace Blob pages      -167521571

        Home Data Page Version Summary

                     Version                                 Count

                           0 (current)                     812482

    I'm in the process of building a clean copy of the table to see how much space that takes up (and what its oncheck -pT looks like)

    Tom Girsch

    Lead System Architect
    Auto Europe Group
    tgirsch@autoeurope.com
    Office #207-842-2139


    " If you think there is something more important than a Client ... think again "





  • 6.  RE: Repack/Shrink With In-Table BYTE Columns

    IBM Champion
    Posted Tue March 08, 2022 04:56 PM
    Tom:

    Yup, this many free blobpage bytes: 11,137,123,527. Most of that is on partially full blobpages.

    What's concerning are the ~84,000 free data pages that weren't compacted out. Yea, JC or Vlad has to figure this one out.

    Art



    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------



  • 7.  RE: Repack/Shrink With In-Table BYTE Columns

    IBM Champion
    Posted Tue March 08, 2022 05:03 PM
    I'm more concerned with the 5.5 million pages that are simply listed as "free."

    Tom Girsch

    Lead System Architect
    Auto Europe Group
    tgirsch@autoeurope.com
    Office #207-842-2139


    " If you think there is something more important than a Client ... think again "





  • 8.  RE: Repack/Shrink With In-Table BYTE Columns

    IBM Champion
    Posted Tue March 08, 2022 06:39 PM
    I'll have to open a ticket here I guess. I created an uncompressed full copy of the table with all the same data, and that version is 70% of the size of the existing, compressed one.





  • 9.  RE: Repack/Shrink With In-Table BYTE Columns

    IBM Champion
    Posted Wed March 09, 2022 08:49 AM
    TS008671041 opened

    Tom Girsch

    Lead System Architect
    Auto Europe Group
    tgirsch@autoeurope.com
    Office #207-842-2139


    " If you think there is something more important than a Client ... think again "





  • 10.  RE: Repack/Shrink With In-Table BYTE Columns

    IBM Champion
    Posted Tue March 08, 2022 03:47 PM
    Additional tidbit of relevant info:

    extent size 57344 next size 14336  compressed lock mode row

    Just tried the repack/shrink again:

    EXECUTE FUNCTION task('table repack shrink','vouch_tag','euroreserve');


    (expression)  Succeeded: table repack shrink  euroreserve:informix.vouch_tag

    1 row(s) retrieved.

    Table size didn't budge.





  • 11.  RE: Repack/Shrink With In-Table BYTE Columns

    IBM Champion
    Posted Wed March 09, 2022 10:28 AM
    I did a little bit of additional testing and it seems the problem is limited to the BYTE data type. I tested with a table that has the TEXT type and the repack/shrink worked as expected.

    ------------------------------
    TOM GIRSCH
    ------------------------------



  • 12.  RE: Repack/Shrink With In-Table BYTE Columns

    IBM Champion
    Posted Wed April 13, 2022 12:15 PM

    Support has identified this as a bug:

    idsdb00110607 ( IT40623 ) : REPACK & SHRINK IS NOT SUCCESSFUL IF TABLE CONTAINS PARTITION BLOBS WITH LARGE ADDRESSES

    No ETA yet on a fix.



    ------------------------------
    TOM GIRSCH
    ------------------------------



  • 13.  RE: Repack/Shrink With In-Table BYTE Columns

    Posted Tue January 16, 2024 05:13 PM

    Hi all, 

    I have got exactly the same problem in very heavy core system on big partitioned table (700GB) in version 12.10.FC15. Repack, shrink doesn't

    work properly on table with blobs.

    Could you tell me if is there a fix on this or number of Case?

    Regards

    Piotr Wolski



    ------------------------------
    Piotr Wolski
    ------------------------------



  • 14.  RE: Repack/Shrink With In-Table BYTE Columns

    IBM Champion
    Posted Tue January 16, 2024 07:17 PM
    They gave us a patch for 14.10 so they could probably provide one for 12.10 pretty easily. But it's not perfect. Works for most but not all of our tables with BYTE.






  • 15.  RE: Repack/Shrink With In-Table BYTE Columns

    Posted Wed January 17, 2024 01:57 AM

    Thanks a lot

    I will open CASE.



    ------------------------------
    Piotr Wolski
    ------------------------------



  • 16.  RE: Repack/Shrink With In-Table BYTE Columns

    IBM Champion
    Posted Wed January 17, 2024 06:09 AM
    You might reference our case, TS008671041