Online Defragmentation
Do you have large tables that contain too many extents but haven’t been able to schedule a maintenance window to reorganize? With the release of Informix 11.70, IBM introduced a new defragmentation utility that lets you reorganize a table or index online while the table is in use.
In a blog post that I wrote a couple of years ago, “Informix Extents: Sizing and Performance“, I explained the importance of managing the number of extents allocated to tables and indexes. Prior to Informix 11.70, reorganizing a table required exclusive access and meant a disruption to service. This often results in tables getting out of hand because there is no convenient time to reorganize. There is now a sysadmin function that allows you to defragment a table or index in the same way you might defrag a disk in Windows.
You can use the following query to determine the number of extents for your tables and indexes:
database sysmaster;
select dbsname,
tabname,
count(*) num_of_extents,
sum( pe_size ) total_size
from systabnames, sysptnext
where partnum = pe_partnum
group by 1, 2
order by 3 desc, 4 desc;Alternatively, to see the fragments for a given table, you can run oncheck –pt database:table, as shown below.
Prior to defragment:
informix@Linux02:~/xtivia-dir/bjs$ oncheck -pt bjsdb:data
TBLspace Report for bjsdb:informix.data
Physical Address 4:7457
Creation date 01/13/2016 15:56:21
TBLspace Flags 802 Row Locking
TBLspace use 4 bit bit-maps
Maximum row size 125
Number of special columns 0
Number of keys 0
Number of extents 8
Current serial value 48469
Current SERIAL8 value 1
Current BIGSERIAL value 1
Current REFID value 1
Pagesize (k) 2
First extent size 8
Next extent size 512
Number of pages allocated 3584
Number of pages used 3233
Number of data pages 3232
Number of rows 48468
Partition partnum 4194461
Partition lockid 4194461
Extents
Logical Page Physical Page Size Physical Pages
0 4:9040 32 32
32 4:9076 32 32
64 4:9116 192 192
256 4:9324 128 128
384 4:9484 256 256
640 4:9804 768 768
1408 4:10700 1408 1408
2816 4:12236 768 768
Index 100_1 fragment partition datadbs1 in DBspace datadbs1
Physical Address 4:7458
Creation date 01/13/2016 15:56:21
TBLspace Flags 802 Row Locking
TBLspace use 4 bit bit-maps
Maximum row size 125
Number of special columns 0
Number of keys 1
Number of extents 8
Current serial value 1
Current SERIAL8 value 1
Current BIGSERIAL value 1
Current REFID value 1
Pagesize (k) 2
First extent size 4
Next extent size 128
Number of pages allocated 384
Number of pages used 320
Number of data pages 0
Number of rows 0
Partition partnum 4194462
Partition lockid 4194461
Extents
Logical Page Physical Page Size Physical Pages
0 4:9036 4 4
4 4:9072 4 4
8 4:9108 8 8
16 4:9308 16 16
32 4:9452 32 32
64 4:9740 64 64
128 4:10572 128 128
256 4:12108 128 128A good starting place for reorganization would be any object with 50 or more extents. To defragment the table, you can run “execute function task” in the sysadmin database, specifying “defragment” with the database and table name or the partition number. For example, to defragment the data table in the bjsdb database, you would type the following:
execute function task(“defragment”,”bjsdb:informix.data”);
The results of the defragment can be seen in the next output from oncheck.
After table defragment:
informix@Linux02:~/xtivia-dir/bjs$ oncheck -pt bjsdb:data
TBLspace Report for bjsdb:informix.data
Physical Address 4:7457
Creation date 01/13/2016 15:56:21
TBLspace Flags 802 Row Locking
TBLspace use 4 bit bit-maps
Maximum row size 125
Number of special columns 0
Number of keys 0
Number of extents 1
Current serial value 48469
Current SERIAL8 value 1
Current BIGSERIAL value 1
Current REFID value 1
Pagesize (k) 2
First extent size 8
Next extent size 512
Number of pages allocated 3584
Number of pages used 3233
Number of data pages 3232
Number of rows 48468
Partition partnum 4194461
Partition lockid 4194461
Extents
Logical Page Physical Page Size Physical Pages
0 4:13004 3584 3584
Index 100_1 fragment partition datadbs1 in DBspace datadbs1
Physical Address 4:7458
Creation date 01/13/2016 15:56:21
TBLspace Flags 802 Row Locking
TBLspace use 4 bit bit-maps
Maximum row size 125
Number of special columns 0
Number of keys 1
Number of extents 8
Current serial value 1
Current SERIAL8 value 1
Current BIGSERIAL value 1
Current REFID value 1
Pagesize (k) 2
First extent size 4
Next extent size 128
Number of pages allocated 384
Number of pages used 320
Number of data pages 0
Number of rows 0
Partition partnum 4194462
Partition lockid 4194461
Extents
Logical Page Physical Page Size Physical Pages
0 4:9036 4 4
4 4:9072 4 4
8 4:9108 8 8
16 4:9308 16 16
32 4:9452 32 32
64 4:9740 64 64
128 4:10572 128 128
256 4:12108 128 128In order to defragment an index using this function, you need to use the index name or partition number, which can be found in the “oncheck –pt” output above.
execute function task(“defragment partnum”,”4194462″);
After index defragment:
informix@Linux02:~/xtivia-dir/bjs$ oncheck -pt bjsdb:data
TBLspace Report for bjsdb:informix.data
Physical Address 4:7457
Creation date 01/13/2016 15:56:21
TBLspace Flags 802 Row Locking
TBLspace use 4 bit bit-maps
Maximum row size 125
Number of special columns 0
Number of keys 0
Number of extents 1
Current serial value 48469
Current SERIAL8 value 1
Current BIGSERIAL value 1
Current REFID value 1
Pagesize (k) 2
First extent size 8
Next extent size 512
Number of pages allocated 3584
Number of pages used 3233
Number of data pages 3232
Number of rows 48468
Partition partnum 4194461
Partition lockid 4194461
Extents
Logical Page Physical Page Size Physical Pages
0 4:13004 3584 3584
Index 100_1 fragment partition datadbs1 in DBspace datadbs1
Physical Address 4:7458
Creation date 01/13/2016 15:56:21
TBLspace Flags 802 Row Locking
TBLspace use 4 bit bit-maps
Maximum row size 125
Number of special columns 0
Number of keys 1
Number of extents 1
Current serial value 1
Current SERIAL8 value 1
Current BIGSERIAL value 1
Current REFID value 1
Pagesize (k) 2
First extent size 4
Next extent size 128
Number of pages allocated 384
Number of pages used 320
Number of data pages 0
Number of rows 0
Partition partnum 4194462
Partition lockid 4194461
Extents
Logical Page Physical Page Size Physical Pages
0 4:9804 384 384The “execute function” commands can be put in a sql file and then run from the command line, or typed directly into dbaccess. I recommend the former as it could take some time to execute against a large table.
Example:
vi defrag_data.sql:
execute function task(“defragment”,”bjsdb:informix.data”);
Then to run the command in background:
dbaccess sysadmin defrag_data > defrag.out 2>&1 &
You can watch the progress of the defragment statement by running “onstat –g defragment” from the command line. You will need to run update statistics against the table after defragmentation to update distributions for the optimizer.
I have found this function to be very useful in defragmenting tables that otherwise could not be reorganized without a maintenance window. It can also be scheduled to run automatically using the Informix scheduler, but that’s a topic for another blog post. Stay tuned!
Version 11.50 has almost the same feature.
EXECUTE FUNCTION task(‘fragment repack shrink’, ”);
Did they just alias this to defragment or does it do something different?
Defragment is different. Repack and shrink can be used to reclaim free space after rows are deleted from a table. Repack consolidates the free space by moving the rows to the beginning of the table or fragment, then shrink releases any resulting unused pages at the end of the table. The number of extents is reduced only if sufficient data has been removed. On the other hand, defragment is used to reduce the number of extents regardless of whether the amount of data has been reduced. In my next blog post, I will explain “repack shrink” in more detail as well as how they differ from defragment.