Fragments and Extents
A user reported tables growing fast and becoming fragmented (50+ extents, plus many sequential scans) on Informix 11.70 through 12.10.FC13, and asked how to detect and fix it. Ben Thompson replied that the old ~230-extent limit only applied to 11.50 and earlier; from 11.70 on, extents are far less of a concern, and 12.10 allocates new extents of at least 10% of table size, so consolidating 50 extents yields negligible gain. For reorganising or minimising space after loads, he suggested 'table repack shrink' (or just 'table shrink'), or sizing extents by calculating rows per page (noting the 255 rows/page limit). The sequential scans were flagged as a separate issue, left unaddressed. The poster said he would test the suggestions; no outcome reported.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi, I am facing some problems with some tables that are growing fast and getting fragmented. I have looked for some kind of procedure to identify and correct this. Can someone give me some help to solve this problem? Thanks in advance, SP
Please could you clarify this question: - Informix version. - what you mean by fragmented: too many extents? - what your concerns are (performance, hitting the extent limit, other). Ben Thompson.
Hi, I have the same problem over several versions since 11.70.xCX, now I am doing some tests over 12.10.FC13. I verify that exists several extents [more than 50] on some tables and also are too much sequential scans. I know this is one specific problem for development, but as they don't have time to look to the problem. I would like to have some performance improvement with some settings over informix if it is possible. Thanks, SP
If you're on 11.70 or later you need not worry very much about extents. With 11.50 and before there was a limit of about 230 extents per partition for 2 kB page sizes and it was possible to get close to this limit. Having 50 extents for a table is fine and with 12.10 the engine will allocate new extents that are at least 10% of the total table size. Presumably this is an upgraded system? Consolidating them into a small number or larger extents by itself will give a negligible performance gain. If you do want reorganise, a "table repack shrink" is probably the easiest way. The sequential scans are an entirely different problem... Ben.
How do we make frequent updates of the versions on the clients, with export and import. I would like to know if there is any formula that you can use to calculate these values in order to optimize their space. SP
Assuming I understood your question correctly, which I think is about how to load data into Informix while taking up the minimum space required. You'd have to work out how many rows you can fit inside a data page and thus how many pages you need. This would be based on the data types in your table, e.g. 4 bytes for an 'int' etc. Bear in mind the 255 rows per page limit. An alternative would be a 'table repack shrink' or just a 'table shrink' at the end of the load. Ben.
Thanks for your reply, I will test it!