Compress Repack Slow Performance
Posted in 2016
Dear All,
Have an instance of Informix 11.7.FC4 that runs on AIX PowerHA Cluster, IBM
POWER machines, with 64 logical cores, 160GB or RAM memory and FC storage.
Have a total of 600GB of chunks, containing few big tables within 1 DB (100GB
(100mil records), 70GB, 50GB) fragmented based on serial id (i.e. trough the
time) on portions of up to 10.000.000 rows. (Additional there are 2 RSS
Servers connected to it, for DR/BC and read only purposes). I would say quite
strong hardware for this type of load.
Since most of the data in those "older" fragments is rarely if ever read and
practically never updated, I would like to compress it, further more, the
machine is quite IO intensive (the sw behind is not an option to change) i.e.
3-400MBps read + 100MBps writes in peak periods. The problem is that the
machine needs to be online 24*7.
The compression of one fragment of more than few million rows,(does not have
to be 10mil) takes from few hours up to 12hours based on the size of the
fragment even in windows with little or no activities on the server (both IO,
CPU and memory idle). If run online while there are ~ 100 active sessions on
the machine, the users are struggling bad response times. Repack operation
(which I usually run after the compression is finished) takes even longer and
practically blocks user activities, and takes much much time (sometimes
estimation on -g dsk is >24hours) . SHRINK at the end (once all this finishes)
works like a charm.
De-fragment is something that I do not even try to do since it takes forever
and there are not much extents within table (10-20max)
All commands are run either trough OAT or dbaccess(cmd line).
I was wondering if there is something wrong that I do or there are some
settings within the onconfig that I do not take in to consideration.(since I
do not have any special setting or change within it for this purpose)
Another question, since I have killed one or two compress/repack operations
due to server slowdown, is there any inconsistency within the chunks right now
that I have to check/repair and if so, please suggest how this can be realized
and achieved?
Any advise or benchmark of some kind will be highly appreciated,
Thank you
Aleksandar