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

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

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

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

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

Tables and indexes are included above if they have:

  1. more than 10 extents (less than that may not be possible);
  2. 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:

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

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

dbsnametabnameidxnamepartnumdbspacepagesizenextnsnrowskbtotalkbusedkbdata
hqs_1_chunkwrites5242981hqdbs219303046084608886
hqs_1_dbspace_usage5242953hqdbs22121308614458782878
hqs_1_diskio5242977hqdbs231303118432184326062
hqs_1_fgwrites5242985hqdbs217303135843584836
hqs_1_lruwrites5242979hqdbs217303135843584836
hqs_1_memory_segments5242960hqdbs21211671204820481296
hqs_1_online_log5242987hqdbs21413498819280802060
hqs_1_os_diskio5242992hqdbs23693838327683276818396
hqs_1_os_memory5242967hqdbs213304523042304494
hqs_1_os_networkio5242989hqdbs2149597256025601518
hqs_1_session_stats5242947hqdbs211304517921792388
hqs_1_thread_counts5242962hqdbs216304530723072684
hqs_1_vps5242964hqdbs22121308563253822420
sysadminmon_page_usage1048987rootdbs21720462409640961280
sysadminmon_page_usagemon_page_usage_ix11048988rootdbs2120179215860
sysadminmon_prof1048984rootdbs216696835843432154
sysadminmon_profmon_prof_idx11048985rootdbs2120268826880
sysadminmon_table_profile1048799rootdbs213482838643864806
sysadminsysprocbody1048710rootdbs2127715256025602208
Copy to Clipboard

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