Informix Partition Defragmentation

Abstract

From Informix Dynamic Server (IDS) 11.70, syntax exists to merge extents for any table or index, even system catalog tables for which there was previously no solution. This article examines why it matters and provides methods to defragment any number of them in one run.

Content

As an object (table or index) grows, its container (partition) initially has space within it to grow (allocated versus used pages). Allocating some storage in advance improves insert performance, and reading the object back will be quicker with less overhead if those pages are stored together. The amount of space pre-allocated for a table (an extent) can be specified as in the following example (or changed later with ALTER TABLE):

Copy to Clipboard

CREATE TABLE table-name
(
    column-list
)
    EXTENT SIZE 4098
    NEXT   SIZE 1024;

 
CREATE TABLE table-name
(
    column-list
)
    EXTENT SIZE 4098
    NEXT   SIZE 1024;

That reserves 4MB initially, with further extents of 1MB each time it needs to grow. These sizes are declared in KB, with the default being 16 for both values. A typical policy might be to set the next extent size to an eighth of the total table size in a mature database. Depending on the page size, type of object and IDS version, the maximum number of extents can be as little as 220. Should the extent size prove too small, the database engine compensates by employing an “Extent size doubling” algorithm. Extent sizes of indexes are based on those of the corresponding table.

The IBM documentation page “Partition defragmentation” states:

“You can improve performance by defragmenting partitions to merge non-contiguous extents. A frequently updated table can become fragmented over time, which degrades performance every time the table is accessed by the server. Defragmenting a table brings data rows closer together and avoids partition header page overflow problems. Defragmenting an index brings the entries closer together, which improves the speed at which the table information is accessed.”

In IDS 11.50 or earlier, a common intractable problem is the number of extents in system catalog table “sysprocbody”. If the application has many large procedures or functions written in the Informix Stored Procedure Language (SPL), that table often has around 90 extents, with no way to defragment the table due to ALTER not being permitted on any system catalog.

From IDS 11.70, syntax exists to “Dynamically defragment partition extents” in the SQL admin API, for example:

Copy to Clipboard

EXECUTE FUNCTION sysadmin:task('defragment','stores_demo:sysprocbody');

 
EXECUTE FUNCTION sysadmin:task('defragment','stores_demo:sysprocbody');

This does not seem to cause significant logical log turnover, so will not result in “Long transaction aborted” on large tables.

For indexes, the alternate syntax must be used to specify the object by part number (unique partition ID), for example:

Copy to Clipboard

EXECUTE FUNCTION sysadmin:task('defragment partnum','4199318');

 
EXECUTE FUNCTION sysadmin:task('defragment partnum','4199318');

InformixHQ provides an easy graphical interface to choose multiple objects to be defragmented in one request:

The following was written to the message log (actually requested in two sets):

Copy to Clipboard

17:52:17  SCHAPI Estimate Compression for stores_demo:informix.sysprocbody started
17:52:17  SCHAPI table estimate_compression  stores_demo:informix.sysprocbody succeeded
17:57:00  SCHAPI Estimate Compression for stores_demo:informix.sysprocauth started
17:57:00  SCHAPI table estimate_compression  stores_demo:informix.sysprocauth succeeded
17:57:00  SCHAPI Estimate Compression for stores_demo:informix.sysproccolumns started
17:57:00  SCHAPI table estimate_compression  stores_demo:informix.sysproccolumns succeeded
17:57:00  SCHAPI Estimate Compression for stores_demo:informix.sysprocedures started
17:57:00  SCHAPI table estimate_compression  stores_demo:informix.sysprocedures succeeded
17:57:00  SCHAPI Estimate Compression for stores_demo:informix.sysprocplan started
17:57:00  SCHAPI table estimate_compression  stores_demo:informix.sysprocplan succeeded

 
52:17  SCHAPI Estimate Compression for stores_demo:informix.sysprocbody started
52:17  SCHAPI table estimate_compression  stores_demo:informix.sysprocbody succeeded
57:00  SCHAPI Estimate Compression for stores_demo:informix.sysprocauth started
57:00  SCHAPI table estimate_compression  stores_demo:informix.sysprocauth succeeded
57:00  SCHAPI Estimate Compression for stores_demo:informix.sysproccolumns started
57:00  SCHAPI table estimate_compression  stores_demo:informix.sysproccolumns succeeded
57:00  SCHAPI Estimate Compression for stores_demo:informix.sysprocedures started
57:00  SCHAPI table estimate_compression  stores_demo:informix.sysprocedures succeeded
57:00  SCHAPI Estimate Compression for stores_demo:informix.sysprocplan started
57:00  SCHAPI table estimate_compression  stores_demo:informix.sysprocplan succeeded

InformixHQ must have run unnecessary steps that would make real jobs on larger tables take longer. Compression was not selected, and in any case is impossible on system catalogs or with IDS Editions other than Developer or Enterprise.

Note that, for large tables not in a dedicated dbspace, it is likely that the number of extents cannot be reduced below a certain number if a sufficiently large contiguous block of free pages does not exist. Conversely, if a large table is in a dedicated dbspace, it will always have just one extent, as IDS will be able to coalesce the next extent at the end of the current one each time it grows.

Another approach is this SQL:

Copy to Clipboard

DATABASE sysmaster;

DROP TABLE IF EXISTS tmp_data;

SELECT * FROM
(
SELECT
    TRIM(x0.dbsname) AS dbsname,
    TRIM(x2.tabname) AS tabname,
    CASE
        WHEN x0.tabname != x2.tabname
            THEN TRIM(x0.tabname)
    END AS idxname,
    x0.partnum,
    TRIM(DBINFO('dbspace',x2.partnum)) AS dbspace,
    (pagesize / 1024) :: SMALLINT AS pagesize,
    nextns,
    nrows,
    (nptotal * pagesize / 1024) :: INT AS kbtotal,
    (npused  * pagesize / 1024) :: INT AS kbused,
    (npdata  * pagesize / 1024) :: INT AS kbdata
FROM systabnames AS x0
JOIN sysptntab   AS x1 ON x1.partnum = x0.partnum
JOIN systabnames AS x2 ON x2.partnum = x1.tablock
JOIN sysptnhdr   AS x3 ON x3.partnum = x0.partnum
)
WHERE dbsname != 'sysmaster'
AND tabname MATCHES '[a-z]*'
AND nextns > 10
AND kbused < 102400
INTO TEMP tmp_data;

UNLOAD TO 'defragment_out.csv' DELIMITER ','
SELECT *
FROM tmp_data
ORDER BY dbsname, tabname, idxname;

UNLOAD TO 'defragment_out.sql' DELIMITER ';'
SELECT
    'EXECUTE FUNCTION task("defragment partnum",'
    || partnum || ') {'
    || dbsname || ' '
    || tabname
    || NVL(' ' || idxname, '') || '}'
FROM tmp_data
ORDER BY dbsname, tabname, idxname;

​x
 
DATABASE sysmaster;
​
DROP TABLE IF EXISTS tmp_data;
​
SELECT * FROM
(
SELECT
    TRIM(x0.dbsname) AS dbsname,
    TRIM(x2.tabname) AS tabname,
    CASE
        WHEN x0.tabname != x2.tabname
            THEN TRIM(x0.tabname)
    END AS idxname,
    x0.partnum,
    TRIM(DBINFO('dbspace',x2.partnum)) AS dbspace,
    (pagesize / 1024) :: SMALLINT AS pagesize,
    nextns,
    nrows,
    (nptotal * pagesize / 1024) :: INT AS kbtotal,
    (npused  * pagesize / 1024) :: INT AS kbused,
    (npdata  * pagesize / 1024) :: INT AS kbdata
FROM systabnames AS x0
JOIN sysptntab   AS x1 ON x1.partnum = x0.partnum
JOIN systabnames AS x2 ON x2.partnum = x1.tablock
JOIN sysptnhdr   AS x3 ON x3.partnum = x0.partnum
)
WHERE dbsname != 'sysmaster'
AND tabname MATCHES '[a-z]*'
AND nextns > 10
AND kbused < 102400
INTO TEMP tmp_data;
​
UNLOAD TO 'defragment_out.csv' DELIMITER ','
SELECT *
FROM tmp_data
ORDER BY dbsname, tabname, idxname;
​
UNLOAD TO 'defragment_out.sql' DELIMITER ';'
SELECT
    'EXECUTE FUNCTION task("defragment partnum",'
    || partnum || ') {'
    || dbsname || ' '
    || tabname
    || NVL(' ' || idxname, '') || '}'
FROM tmp_data
ORDER BY dbsname, tabname, idxname;

Tables and indexes are included above if they have:

more than 10 extents (less than that may not be possible);
less than 100MB used pages (larger objects may need special handling).

The above criterial can be changed in the SQL as preferred.

Two output files are produced:

a report in CSV format;
SQL to defragment the objects.

Examples from running on a test system follow, with column headings added:

dbsname	tabname	idxname	partnum	dbspace	pagesize	nextns	nrows	kbtotal	kbused	kbdata
hq	s_1_chunkwrites		5242981	hqdbs	2	19	3030	4608	4608	886
hq	s_1_dbspace_usage		5242953	hqdbs	2	21	21308	6144	5878	2878
hq	s_1_diskio		5242977	hqdbs	2	31	3031	18432	18432	6062
hq	s_1_fgwrites		5242985	hqdbs	2	17	3031	3584	3584	836
hq	s_1_lruwrites		5242979	hqdbs	2	17	3031	3584	3584	836
hq	s_1_memory_segments		5242960	hqdbs	2	12	11671	2048	2048	1296
hq	s_1_online_log		5242987	hqdbs	2	14	13498	8192	8080	2060
hq	s_1_os_diskio		5242992	hqdbs	2	36	93838	32768	32768	18396
hq	s_1_os_memory		5242967	hqdbs	2	13	3045	2304	2304	494
hq	s_1_os_networkio		5242989	hqdbs	2	14	9597	2560	2560	1518
hq	s_1_session_stats		5242947	hqdbs	2	11	3045	1792	1792	388
hq	s_1_thread_counts		5242962	hqdbs	2	16	3045	3072	3072	684
hq	s_1_vps		5242964	hqdbs	2	21	21308	5632	5382	2420
sysadmin	mon_page_usage		1048987	rootdbs	2	17	20462	4096	4096	1280
sysadmin	mon_page_usage	mon_page_usage_ix1	1048988	rootdbs	2	12	0	1792	1586	0
sysadmin	mon_prof		1048984	rootdbs	2	16	6968	3584	3432	154
sysadmin	mon_prof	mon_prof_idx1	1048985	rootdbs	2	12	0	2688	2688	0
sysadmin	mon_table_profile		1048799	rootdbs	2	13	4828	3864	3864	806
sysadmin	sysprocbody		1048710	rootdbs	2	12	7715	2560	2560	2208

Copy to Clipboard

EXECUTE FUNCTION task("defragment partnum",5242981) {hq s_1_chunkwrites};
EXECUTE FUNCTION task("defragment partnum",5242953) {hq s_1_dbspace_usage};
EXECUTE FUNCTION task("defragment partnum",5242977) {hq s_1_diskio};
EXECUTE FUNCTION task("defragment partnum",5242985) {hq s_1_fgwrites};
EXECUTE FUNCTION task("defragment partnum",5242979) {hq s_1_lruwrites};
EXECUTE FUNCTION task("defragment partnum",5242960) {hq s_1_memory_segments};
EXECUTE FUNCTION task("defragment partnum",5242987) {hq s_1_online_log};
EXECUTE FUNCTION task("defragment partnum",5242992) {hq s_1_os_diskio};
EXECUTE FUNCTION task("defragment partnum",5242967) {hq s_1_os_memory};
EXECUTE FUNCTION task("defragment partnum",5242989) {hq s_1_os_networkio};
EXECUTE FUNCTION task("defragment partnum",5242947) {hq s_1_session_stats};
EXECUTE FUNCTION task("defragment partnum",5242962) {hq s_1_thread_counts};
EXECUTE FUNCTION task("defragment partnum",5242964) {hq s_1_vps};
EXECUTE FUNCTION task("defragment partnum",1048987) {sysadmin mon_page_usage};
EXECUTE FUNCTION task("defragment partnum",1048988) {sysadmin mon_page_usage mon_page_usage_ix1};
EXECUTE FUNCTION task("defragment partnum",1048984) {sysadmin mon_prof};
EXECUTE FUNCTION task("defragment partnum",1048985) {sysadmin mon_prof mon_prof_idx1};
EXECUTE FUNCTION task("defragment partnum",1048799) {sysadmin mon_table_profile};
EXECUTE FUNCTION task("defragment partnum",1048710) {sysadmin sysprocbody};

 
EXECUTE FUNCTION task("defragment partnum",5242981) {hq s_1_chunkwrites};
EXECUTE FUNCTION task("defragment partnum",5242953) {hq s_1_dbspace_usage};
EXECUTE FUNCTION task("defragment partnum",5242977) {hq s_1_diskio};
EXECUTE FUNCTION task("defragment partnum",5242985) {hq s_1_fgwrites};
EXECUTE FUNCTION task("defragment partnum",5242979) {hq s_1_lruwrites};
EXECUTE FUNCTION task("defragment partnum",5242960) {hq s_1_memory_segments};
EXECUTE FUNCTION task("defragment partnum",5242987) {hq s_1_online_log};
EXECUTE FUNCTION task("defragment partnum",5242992) {hq s_1_os_diskio};
EXECUTE FUNCTION task("defragment partnum",5242967) {hq s_1_os_memory};
EXECUTE FUNCTION task("defragment partnum",5242989) {hq s_1_os_networkio};
EXECUTE FUNCTION task("defragment partnum",5242947) {hq s_1_session_stats};
EXECUTE FUNCTION task("defragment partnum",5242962) {hq s_1_thread_counts};
EXECUTE FUNCTION task("defragment partnum",5242964) {hq s_1_vps};
EXECUTE FUNCTION task("defragment partnum",1048987) {sysadmin mon_page_usage};
EXECUTE FUNCTION task("defragment partnum",1048988) {sysadmin mon_page_usage mon_page_usage_ix1};
EXECUTE FUNCTION task("defragment partnum",1048984) {sysadmin mon_prof};
EXECUTE FUNCTION task("defragment partnum",1048985) {sysadmin mon_prof mon_prof_idx1};
EXECUTE FUNCTION task("defragment partnum",1048799) {sysadmin mon_table_profile};
EXECUTE FUNCTION task("defragment partnum",1048710) {sysadmin sysprocbody};

Executing the above generated SQL should result in “OK” being returned by each statement. Nothing is written to the message log. As a guide to run time, running this recently on 551 tables in a real instance containing 400GB used pages in application dbspaces took 30 minutes.

Conclusion

Informix tables and indexes can become fragmented, adversely affecting performance and even preventing further growth, but that can easily be resolved using the “defragment” operation which can be driven in two ways across an entire instance while it remains fully available.

Disclaimer

Suggestions above are provided “as is” without warranty of any kind, either express or implied, including without limitation any implied warranties of condition, uninterrupted use, merchantability, fitness for a particular purpose, or non-infringement.

Author

Doug Lawry
Senior Informix Consultant

Informix Partition Defragmentation

Abstract

Content

Conclusion

Disclaimer

Author

Related Technical Articles:

Share this story: