Don't Understand Table's Space Requirement

IDS 12.10.FC3 Solaris 10 1/13 Consider a table with 11 columns. 10 of the columns are various "usual" datatypes, and those 10 columns take 39 bytes. The eleventh column is type LVARCHAR(10000). The table contains 300,000 rows. In 99.9% of the rows (299,700), the LVARCHAR column has LENGTH < 1,950, meaning that the entire row should fit on a single page (using default page size = 2K, and have MAX_FILL_DATA_PAGES = 1) the overwhelming majority of the time. Only 300 rows have the LVARCHAR column greater than 1,950, and only 3 rows exceed LENGTH 5,000. The SUM( LENGTH( <the_lvarchar_col> ) ) for the whole table is 30 MB (.03GB), and the remaining columns (39 bytes) should take up roughly .01MB. Yet, as shown in Server Studio, the table requires >1.5GB (approx 1,500MB). This seems like basic DBA material, but I don't understand why the table is taking up 50 times more space than it seems like it should. Might anyone offer an explanation? Thank you. DG

P.S. About 2% of the LVARCHAR values are between 1K and 5K, and 98% less than 1K.

Original post: IDS 12.10.FC3 Solaris 10 1/13 Consider a table with 11 columns. 10 of the columns are various "usual" datatypes, and those 10 columns take 39 bytes. The eleventh column is type LVARCHAR(10000). The table contains 300,000 rows. In 99.9% of the rows (299,700), the LVARCHAR column has LENGTH < 1,950, meaning that the entire row should fit on a single page (using default page size = 2K, and have MAX_FILL_DATA_PAGES = 1) the overwhelming majority of the time. Only 300 rows have the LVARCHAR column greater than 1,950, and only 3 rows exceed LENGTH 5,000. The SUM( LENGTH( <the_lvarchar_col> ) ) for the whole table is 30 MB (.03GB), and the remaining columns (39 bytes) should take up roughly .01MB. Yet, as shown in Server Studio, the table requires >1.5GB (approx 1,500MB). This seems like basic DBA material, but I don't understand why the table is taking up 50 times more space than it seems like it should. Might anyone offer an explanation? Thank you. DG Response: It sounds like a bug. I did a quick test on 12.10.FC11 using using a 2 column table, a char(39) and a lvarchar(10000) and I inserted like 15 rows, and according to oncheck -pT all my rows are still on a single page (as expected, I used small lvarchars). For your table to be 1.5Gb it almost seems like server studio is taking max row size (so like a bit over 10k) and just multiplying that by the number of rows. I don't know how server studio is getting it's size...but what does oncheck -pt say for the number of pages allocate/used/number of data pages? As I don't know if it is a server defect or something odd that server studio is doing. Jacques Renaut HCL Informix Advanced Support

Thank you, Mr. Renaut, for your helpful reply. I did an 'oncheck -pt'. Here is the output for the table, itself. (You'll note that my previous numbers were rounded to "nice" values for convenience.) I don't understand the huge difference between the "Number of pages used" and the "Number of data pages". TBLspace Report for acoms:informix.cnote Physical Address 1:3324981 Creation date 10/26/2016 16:28:07 TBLspace Flags 901 Page Locking TBLspace contains VARCHARS TBLspace use 4 bit bit-maps Maximum row size 10039 Number of special columns 1 Number of keys 0 Number of extents 1 Current serial value 1376126 Current SERIAL8 value 1 Current BIGSERIAL value 1 Current REFID value 1 Pagesize (k) 2 First extent size 614400 Next extent size 122880 Number of pages allocated 829440 Number of pages used 826911 Number of data pages 174019 Number of rows 316470 Partition partnum 1053310 Partition lockid 1053310 Extents Logical Page Physical Page Size Physical Pages 0 1:14554511 829440 829440

Original post: Thank you, Mr. Renaut, for your helpful reply. I did an 'oncheck -pt'. Here is the output for the table, itself. (You'll note that my previous numbers were rounded to "nice" values for convenience.) I don't understand the huge difference between the "Number of pages used" and the "Number of data pages". TBLspace Report for acoms:informix.cnote Physical Address 1:3324981 Creation date 10/26/2016 16:28:07 TBLspace Flags 901 Page Locking TBLspace contains VARCHARS TBLspace use 4 bit bit-maps Maximum row size 10039 Number of special columns 1 Number of keys 0 Number of extents 1 Current serial value 1376126 Current SERIAL8 value 1 Current BIGSERIAL value 1 Current REFID value 1 Pagesize (k) 2 First extent size 614400 Next extent size 122880 Number of pages allocated 829440 Number of pages used 826911 Number of data pages 174019 Number of rows 316470 ... some stuff cut out... Response: So the difference you are talking about would be cleared up more I believe if you ran oncheck -pT. It scans all the pages and counts up the types. It holds a 'S' lock on the partition while doing this so it can either run into locks (and consequently not run) or could impact users on the table so it's best to run at low usage times). So there's maybe 1 of 2 things happening. One would be the other used pages that aren't data pages are remainder pages (which would be where you would expect rows > 1 page to be split up upon). So if that was happening, that could be a problem with the server reserving too much space for the lvarchars. The second option would be the table was maybe much larger at some point and rows were deleted. I believe in that case, we do not decrement the number of pages used, so that's a record of how big the table once was and 1 way to tell how many pages are really being used is to examine the oncheck -pT output. Here's the portion of oncheck -pT output you would be interested in: TBLspace Usage Report for mydb:informix.systables Type Pages Empty Semi-Full Full Very-Full ---------------- ---------- ---------- ---------- ---------- ---------- Free 1 Bit-Map 1 Index 6 Data (Home) 8 Data (Remainder) 0 0 0 0 0 ---------- Total Pages 16 (eh bad formatting) So for option 1 I mentioned I think you would see a large number of the "Data (Remainder)" pages. For option 2, you would see a large number of "Free" pages. In option 2's case, those pages will be reused by the table, but it's a bit harder to tell how much more data this table could hold before it tried to extend, and it would seem like server studio is looking either at the number of pages used or the number of pages allocated to determine it's size. One way to reduce the number of pages used if they are "Free" would be to use the table re-org sysadmin tasks. I think repack would move the rows to the beginning of the table's extent and reduce the number of pages used. Jacques Renaut HCL Informix Advanced Support

Thank you, once again, Mr. Renaut. I just ran "oncheck -pT", and got the following... (I responded "n" to the questions because I didn't know whether answering "y" would be safe. I am consulting the documentation for further understanding. In the mean time, here is the output from 'oncheck -pT': informix@ifmx-prod-anc>oncheck -pT acoms:cnote TBLspace Report for acoms:informix.cnote Physical Address 1:3324981 Creation date 10/26/2016 16:28:07 TBLspace Flags 901 Page Locking TBLspace contains VARCHARS TBLspace use 4 bit bit-maps Maximum row size 10039 Number of special columns 1 Number of keys 0 Number of extents 1 Current serial value 1376229 Current SERIAL8 value 1 Current BIGSERIAL value 1 Current REFID value 1 Pagesize (k) 2 First extent size 614400 Next extent size 122880 Number of pages allocated 829440 Number of pages used 827426 Number of data pages 174122 Number of rows 316573 Partition partnum 1053310 Partition lockid 1053310 Extents Logical Page Physical Page Size Physical Pages 0 1:14554511 829440 829440 WARNING: data page 0x1 in tablespace 0x10127e appears to be more or less full than is indicated in the bitmap. Bitmap mode: 0xc, Calculated mode: 0x4. Reset the bitmap mode for this page?n WARNING: data page 0x2 in tablespace 0x10127e appears to be more or less full than is indicated in the bitmap. Bitmap mode: 0xc, Calculated mode: 0x4. Reset the bitmap mode for this page?n WARNING: data page 0x3 in tablespace 0x10127e appears to be more or less full than is indicated in the bitmap. Bitmap mode: 0xc, Calculated mode: 0x4. Reset the bitmap mode for this page?^CInterrupt received ... The check has been aborted. informix@ifmx-prod-anc> Also, just for additional info, I include the following additional information. We have another database, in another instance, on another machine, that is prediodicaly refreshed (via dbexport and then dbimport) from the database referenced above. When I just did an "oncheck -pT" on this "clone" table, I got the following: TBLspace Report for acoms_dev:informix.cnote Physical Address 1:8024342 Creation date 04/12/2018 16:02:55 TBLspace Flags 901 Page Locking TBLspace contains VARCHARS TBLspace use 4 bit bit-maps Maximum row size 10039 Number of special columns 1 Number of keys 0 Number of extents 2 Current serial value 1373346 Current SERIAL8 value 1 Current BIGSERIAL value 1 Current REFID value 1 Pagesize (k) 2 First extent size 614400 Next extent size 122880 Number of pages allocated 832256 Number of pages used 808043 Number of data pages 166129 Number of rows 313728 Partition partnum 1057758 Partition lockid 1057758 Extents Logical Page Physical Page Size Physical Pages 0 1:11287494 709376 709376 709376 10:9104216 122880 122880 ^L TBLspace Usage Report for acoms_dev:informix.cnote Type Pages Empty Semi-Full Full Very-Full ---------------- ---------- ---------- ---------- ---------- ---------- Free 24213 Bit-Map 201 Index 0 Data (Home) 166129 Data (Remainder) 641713 0 1 0 641712 ---------- Total Pages 832256 Unused Space Summary Unused data bytes in Home pages 421060 Unused data bytes in Remainder pages 4172698

Original Post: Thank you, once again, Mr. Renaut. I just ran "oncheck -pT", and got the following... (I responded "n" to the questions because I didn't know whether answering "y" would be safe. I am consulting the documentation for further understanding. In the mean time, here is the output from 'oncheck -pT': ...bunch of stuff cut.. TBLspace Usage Report for acoms_dev:informix.cnote Type Pages Empty Semi-Full Full Very-Full ---------------- ---------- ---------- ---------- ---------- ---------- Free 24213 Bit-Map 201 Index 0 Data (Home) 166129 Data (Remainder) 641713 0 1 0 641712 ---------- Total Pages 832256 Unused Space Summary Unused data bytes in Home pages 421060 Unused data bytes in Remainder pages 4172698 Response: So the errors you are getting in the oncheck look like a known defect (I didn't look super close but I think it has to do with using MAX_DATA_FILL_PAGES). But the fact that all your used pages seem to be the "Data (Remainder)" pages seems like the server is using a ton of space for rows that it thinks are larger then 1 page. I tried to see if I could find a defect with lvarchar and MAX_DATA_FILL_PAGES and I didn't turn anything up with a quick search, but it seems like it's certainly a possibility, if your lvarchar column data is as small as you say they are. It would seem like the server is potentially wasting space. You could open an official PMR to try and get the defect identified for sure, but my quick testing of 12.10.FC10 (or was it 12.10.FC11) seemed like the server was behaving properly, but you were on an older version (12.10.FC3). Jacques Renaut HCL Informix Advanced Support

Thank you, sir. It does also seem strange to me that I observe the "WARNINGs" in the production database, but not the same table in the dbexported-then-dbimported, alternate database. You have already been more than generous with your time & expertise, and I thank you. I will either open an official tech support case, or upgrade versions, so not necessarily expecting any additional replies. Still, it is curious to me, and, just for completeness, I include the following additional information. I ran "EXECUTE FUNCTION task("table repack shrink", "cnote", "<database_name>");" in both databases. Both ran to successful completion, but it didn't seem to change anything. I include the "after" results, below. Result from the first database: informix@ifmx-prod-anc>oncheck -pT acoms:cnote TBLspace Report for acoms:informix.cnote Physical Address 1:3324981 Creation date 10/26/2016 16:28:07 TBLspace Flags 901 Page Locking TBLspace contains VARCHARS TBLspace use 4 bit bit-maps Maximum row size 10039 Number of special columns 1 Number of keys 0 Number of extents 1 Current serial value 1376277 Current SERIAL8 value 1 Current BIGSERIAL value 1 Current REFID value 1 Pagesize (k) 2 First extent size 614400 Next extent size 122880 Number of pages allocated 828156 Number of pages used 828156 Number of data pages 174170 Number of rows 316621 Partition partnum 1053310 Partition lockid 1053310 Extents Logical Page Physical Page Size Physical Pages 0 1:14554511 828156 828156 WARNING: data page 0x1 in tablespace 0x10127e appears to be more or less full than is indicated in the bitmap. Bitmap mode: 0xc, Calculated mode: 0x4. Reset the bitmap mode for this page?n WARNING: data page 0x2 in tablespace 0x10127e appears to be more or less full than is indicated in the bitmap. Bitmap mode: 0xc, Calculated mode: 0x4. <snip> Result from the second database: TBLspace Report for acoms_dev:informix.cnote Physical Address 1:8024342 Creation date 04/12/2018 16:02:55 TBLspace Flags 901 Page Locking TBLspace contains VARCHARS TBLspace use 4 bit bit-maps Maximum row size 10039 Number of special columns 1 Number of keys 0 Number of extents 2 Current serial value 1373346 Current SERIAL8 value 1 Current BIGSERIAL value 1 Current REFID value 1 Pagesize (k) 2 First extent size 614400 Next extent size 122880 Number of pages allocated 808543 Number of pages used 808543 Number of data pages 166129 Number of rows 313728 Partition partnum 1057758 Partition lockid 1057758 Extents Logical Page Physical Page Size Physical Pages 0 1:11287494 709376 709376 709376 10:9104216 99167 99167 ^L TBLspace Usage Report for acoms_dev:informix.cnote Type Pages Empty Semi-Full Full Very-Full ---------------- ---------- ---------- ---------- ---------- ---------- Free 500 Bit-Map 201 Index 0 Data (Home) 166129 Data (Remainder) 641713 0 1 0 641712 ---------- Total Pages 808543 Unused Space Summary Unused data bytes in Home pages 421060 Unused data bytes in Remainder pages 4172698 Home Data Page Version Summary Version Count 0 (current) 166129 DG