performance oncheck
Poster planning an IDS 9.30 to 9.40 upgrade asked how to speed up the pre-migration oncheck runs (oncheck -cI took ~7 hours), wondering about PDQ, PSORT_NPROC, CPU VPs or running several onchecks in parallel. No one offered any tuning parameters for oncheck. Suggested workarounds: skip the checks on the live system and instead test the whole migration plus onchecks on a restored copy/mirror, and use "unload to /dev/null" to detect data corruption quickly — though it does a table scan and so generally misses index corruption (ordering by index columns helps only partly), and gives only a lower-bound estimate of reorg time. No tuning resolution recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi,
we are planing a migration from IDS9.30FC3X3 to IDS9.40FC5.
According to the migration guide, we have to run 2times onchecks.
The oncheck duration is on the greatest database ( oncheck -cI ) about 7hours!
So my question is, is there a possibility to improve the performance/running
time of the onchecks?
for example Configuratin parameters ( PDQ, PSORT_NPROC, number of cpu-vps
)parallel excution of different onchecks ( oncheck -cD, oncheck -cI, oncheck
-cc ).
Thanks
Wolfgang
Hi,
I don't know about special performance improvements for oncheck commands.
But I think most people don't run any oncheck command before migrating
their servers - at least we don't run these checks before (sometimes
because of laziness and sometimes because of DB size).
If you fear to run into problems during the migration I suggest to
test the whole process on a recent database copy (restore / disk mirror).
There you can run the onchecks, too. If they don't report errors on
your database copy most likely your original won't report any errors, too.
Regards,
Andreas Kutsche
> -----Ursprüngliche Nachricht-----
> Von: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org]Im
> Auftrag von WOLFGANG NEUMAR
> Gesendet: Freitag, 15. April 2005 11:15
> An: ids@iiug.org
> Betreff: performance oncheck [4722]
>
>
> Hi,
>
> we are planing a migration from IDS9.30FC3X3 to IDS9.40FC5.
> According to the migration guide, we have to run 2times onchecks.
> The oncheck duration is on the greatest database ( oncheck
> -cI ) about 7hours!
> So my question is, is there a possibility to improve the
> performance/running time of the onchecks?
> for example Configuratin parameters ( PDQ, PSORT_NPROC,
> number of cpu-vps )parallel excution of different onchecks (
> oncheck -cD, oncheck -cI, oncheck -cc ).>
> Thanks
> Wolfgang
>
>
>
You
can also perform unloads to /dev/null of your choice of database tables; if
there is any data corruption, this will find it. Before performing
reorganizations, while planning them, I do this to test for corruption as well
as find out (somewhat) the maximum amount of time it may take to reorganize
the table.
Take care.
Clifton
"Andreas.KUT...." <andreas.kutsche@spar.at> wrote:
Hi,
I don't know about special performance improvements for oncheck commands.
But I think most people don't run any oncheck command before migrating
their servers - at least we don't run these checks before (sometimes
because of laziness and sometimes because of DB size).
If you fear to run into problems during the migration I suggest to
test the whole process on a recent database copy (restore / disk mirror).
There you can run the onchecks, too. If they don't report errors on
your database copy most likely your original won't report any errors, too.
Regards,
Andreas Kutsche
> -----Ursprüngliche Nachricht-----
> Von: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org]Im
> Auftrag von WOLFGANG NEUMAR
> Gesendet: Freitag, 15. April 2005 11:15
> An: ids@iiug.org
> Betreff: performance oncheck [4722]
>
>
> Hi,
>
> we are planing a migration from IDS9.30FC3X3 to IDS9.40FC5.
> According to the migration guide, we have to run 2times onchecks.
> The oncheck duration is on the greatest database ( oncheck
> -cI ) about 7hours!
> So my question is, is there a possibility to improve the
> performance/running time of the onchecks?
> for example Configuratin parameters ( PDQ, PSORT_NPROC,
> number of cpu-vps )parallel excution of different onchecks (
> oncheck -cD, oncheck -cI, oncheck -cc ).>
> Thanks
> Wolfgang
>
>
>
... to /dev/null ... very good trick !
But how you size (somewhat) the delay it may take to reorganize the table?
An unload to /dev/null will take a lot less time than to a file.
J.
-----Original Message-----
From: "Clifton M. Bean" <cmbean@sbcglobal.net>
You can also perform unloads to /dev/null of your choice of database tables;
if there is any data corruption, this will find it. Before performing
reorganizations, while planning them, I do this to test for corruption as well
as find out (somewhat) the maximum amount of time it may take to reorganize
the table.
Take care.
Clifton
"Andreas.KUT...." <andreas.kutsche@spar.at> wrote:
Hi,
I don't know about special performance improvements for oncheck commands.
But I think most people don't run any oncheck command before migrating
their servers - at least we don't run these checks before (sometimes
because of laziness and sometimes because of DB size).
If you fear to run into problems during the migration I suggest to
test the whole process on a recent database copy (restore / disk mirror).
There you can run the onchecks, too. If they don't report errors on
your database copy most likely your original won't report any errors, too.
Regards,
Andreas Kutsche
> -----Ursprüngliche Nachricht-----
> Von: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org]Im
> Auftrag von WOLFGANG NEUMAR
> Gesendet: Freitag, 15. April 2005 11:15
> An: ids@iiug.org
> Betreff: performance oncheck [4722]
>
>
> Hi,
>
> we are planing a migration from IDS9.30FC3X3 to IDS9.40FC5.
> According to the migration guide, we have to run 2times onchecks.
> The oncheck duration is on the greatest database ( oncheck
> -cI ) about 7hours!
> So my question is, is there a possibility to improve the
> performance/running time of the onchecks?
> for example Configuratin parameters ( PDQ, PSORT_NPROC,
> number of cpu-vps )parallel excution of different onchecks (
> oncheck -cD, oncheck -cI, oncheck -cc ).>
> Thanks
> Wolfgang
>
>
>
Jean Sagi
jeansagi@myrealbox.com
jeansagi@yahoo.com
The best
way to test the reorg is to duplicate the database on a similar system (QAS or
test system)
This is what I am doing to size the space needs and the elapsed time of the db
maintenance.
-----Original Message-----
From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org] On Behalf
Of Jean Sagi
Sent: Monday, April 18, 2005 1:27 PM
To: ids@iiug.org
Subject: Re: Re: AW: performance oncheck [4738]
.. to /dev/null ... very good trick !
But how you size (somewhat) the delay it may take to reorganize the table?
An unload to /dev/null will take a lot less time than to a file.
J.
-----Original Message-----
From: "Clifton M. Bean" <cmbean@sbcglobal.net>
You can also perform unloads to /dev/null of your choice of database tables;
if there is any data corruption, this will find it. Before performing
reorganizations, while planning them, I do this to test for corruption as well
as find out (somewhat) the maximum amount of time it may take to reorganize
the table.
Take care.
Clifton
"Andreas.KUT...." <andreas.kutsche@spar.at> wrote:
Hi,
I don't know about special performance improvements for oncheck commands.
But I think most people don't run any oncheck command before migrating their
servers - at least we don't run these checks before (sometimes because of
laziness and sometimes because of DB size).
If you fear to run into problems during the migration I suggest to test the
whole process on a recent database copy (restore / disk mirror).
There you can run the onchecks, too. If they don't report errors on your
database copy most likely your original won't report any errors, too.
Regards,
Andreas Kutsche
> -----Ursprüngliche Nachricht-----
> Von: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org]Im
> Auftrag von WOLFGANG NEUMAR
> Gesendet: Freitag, 15. April 2005 11:15
> An: ids@iiug.org
> Betreff: performance oncheck [4722]
>
>
> Hi,
>
> we are planing a migration from IDS9.30FC3X3 to IDS9.40FC5.
> According to the migration guide, we have to run 2times onchecks.
> The oncheck duration is on the greatest database ( oncheck -cI ) about
> 7hours!
> So my question is, is there a possibility to improve the
> performance/running time of the onchecks?
> for example Configuratin parameters ( PDQ, PSORT_NPROC, number of
> cpu-vps )parallel excution of different onchecks ( oncheck -cD,
> oncheck -cI, oncheck -cc ).>
> Thanks
> Wolfgang
>
>
>
Jean Sagi
jeansagi@myrealbox.com
jeansagi@yahoo.com
An unload to
/dev/null will detect data corruption, however it will
usually not detect
index corruption , as something like
unload to /dev/null
select * from <table>
will usually do a full table scan.
However, it is definitely valid appraoch to check data integrity.
If you want to include an Index check using this method, you may use an
order by
statment which matches the index columns. This on the other side will
then increase
the runtime of the unload statement.
Also, an indexed table read is not 100% fool proove to assess index
integrity, as this
approach will not detect rows taht are missing in the index.
(here an oncheck -cI may be the better approach, as it will detect such
corruption )
As for the reorg time, an unload to /dev/null (or NUL on Windows) does not
tell you much,
but at least it gives you a lower estimate, i.e. you know the reorg will
at least need
this amnount of time.
Regards
Tilman
--
Tilman Model-Bosch
IBM Data Managment Solutions, Informix Advanced Support
c\\\\o SAP AG
TECHDEV 05
Neurrotstr.16
69190 Walldorf
forum.subscriber@iiug.org wrote on 18/04/2005 19:27:00:
> .. to /dev/null ... very good trick !
>
> But how you size (somewhat) the delay it may take to reorganize the
table?
>
> An unload to /dev/null will take a lot less time than to a file.
>
> J.
>
> -----Original Message-----
> From: "Clifton M. Bean" <cmbean@sbcglobal.net>
>
> You can also perform unloads to /dev/null of your choice of database
> tables; if there is any data corruption, this will find it. Before
> performing reorganizations, while planning them, I do this to test
> for corruption as well as find out (somewhat) the maximum amount of
> time it may take to reorganize the table.
>
> Take care.
> Clifton
I agree. -----Original Message----- From: Tilman Model-Bosch <tilman.model-bosch@de.ibm.com> As for the reorg time, an unload to /dev/null (or NUL on Windows) does not tell you much, but at least it gives you a lower estimate, i.e. you know the reorg will at least need this amnount of time. Regards Tilman -- Tilman Model-Bosch IBM Data Managment Solutions, Informix Advanced Support c\\\\o SAP AG TECHDEV 05 Neurrotstr.16 69190 Walldorf forum.subscriber@iiug.org wrote on 18/04/2005 19:27:00: > .. to /dev/null ... very good trick ! > > But how you size (somewhat) the delay it may take to reorganize the table? > > An unload to /dev/null will take a lot less time than to a file. > > J. > > -----Original Message----- > From: "Clifton M. Bean" <cmbean@sbcglobal.net> > > You can also perform unloads to /dev/null of your choice of database > tables; if there is any data corruption, this will find it. Before > performing reorganizations, while planning them, I do this to test > for corruption as well as find out (somewhat) the maximum amount of > time it may take to reorganize the table. > > Take care. > Clifton Jean Sagi jeansagi@myrealbox.com jeansagi@yahoo.com