Defragment questions ?

Hi everybody, I read the technical documentation on defragment feature. I did several tests and all works well with the defragment. But, I would like to be sure : Do you know if there is a risk when we defragment a table or a partition when we have a peak of activity (by example : numberous transactional query to update, delete or insert into the table) ? In your experience, did you meet a problem when we run a defragment on a environment production ? Regards, -- Franck Thomas ConsultiX franck.thomas@consult-ix.fr http://www.consult-ix.fr Téléphone : 33 (0) 1 39 12 18 00 Mobile : 33 (0) 6 78 81 09 33 Fax : 33 (0) 1 39 12 18 18

Hello, Franck. As a good practice, you should always run data movement operations (compress, repack, shrink, defrag) in non-peak production times. Mainly because they generate a lot of IO operations, that could impact in your environment, degrading other engine processes. Also, take care that most of these operations can be run by partition, instead of the entire object. It is always a good practice to run them in smaller jobs, that a huge one of the entire object.... Regards. Alexandre Marini IBM Informix Certified Professional v10 / v11.50 / v11.70 IBM Information Management Informix Technical Professional IBM Infosphere DataStage Technical Professional Informix Senior DBA - Orizon Brasil BRIUG website administrator Informix independent consultant > To: ids@iiug.org > From: franck.thomas@consult-ix.fr > Subject: Defragment questions ? [30129] > Date: Wed, 24 Apr 2013 07:59:19 -0400 > > Hi everybody, > > I read the technical documentation on defragment feature. > I did several tests and all works well with the defragment. > But, I would like to be sure : > Do you know if there is a risk when we defragment a table or a > partition when we have a peak of activity (by example : numberous > transactional query to update, delete or insert into the table) ? > In your experience, did you meet a problem when we run a defragment on a > environment production ? > > Regards, > > -- > Franck Thomas > ConsultiX > franck.thomas@consult-ix.fr > http://www.consult-ix.fr > Téléphone : 33 (0) 1 39 12 18 00 > Mobile : 33 (0) 6 78 81 09 33 > Fax : 33 (0) 1 39 12 18 18 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

The DEFRAGMENT feature is VERY light weight. It moves a small number of pages at a time in a small transaction. There is no more impact on other users than there would be if a user were updating a few dozen to a few hundred rows in a single transaction. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Apr 24, 2013 at 7:59 AM, Franck THOMAS <franck.thomas@consult-ix.fr>wrote: > Hi everybody, > > I read the technical documentation on defragment feature. > I did several tests and all works well with the defragment. > But, I would like to be sure : > Do you know if there is a risk when we defragment a table or a > partition when we have a peak of activity (by example : numberous > transactional query to update, delete or insert into the table) ? > In your experience, did you meet a problem when we run a defragment on a > environment production ? > > Regards, > > -- > Franck Thomas > ConsultiX > franck.thomas@consult-ix.fr > http://www.consult-ix.fr > Téléphone : 33 (0) 1 39 12 18 00 > Mobile : 33 (0) 6 78 81 09 33 > Fax : 33 (0) 1 39 12 18 18 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11c20c9ae2c45d04db1b57c8

Hi Art, Alexandre, Thank you for your answers. Art, you tell me that there is no risk with defragment feature in the environment production. In the article "Understand the Informix Server V11.7 defragmenter" (http://www.ibm.com/developerworks/data/library/techarticle/dm-1011informixdefra gmenter/), I read "The defragmenter uses the buffer pool for this movement in order to keep the data in sync with other uses. *If buffer pool usage is a concern, then running the defragmenter at a non-peak time is recommended.**"** * When do you think ? Another question : "The defragment moves a small number of pages at a time." Do you know how many pages (maximum) can be moved by the defragment ? This small number can be changed by us with a Informix parameter ? Alexandre, your tips of good pratice are interesting. I did some tests, I defragmented a table with 50 million of rows. During the defragment task, I run several queries : Delete 10 000 rows or Update 10 000 rows or Insert new 10 000 rows. No problem. All work. But, I was not in an environment production. Regards, Le 24/04/2013 15:33, Art Kagel a écrit : > The DEFRAGMENT feature is VERY light weight. It moves a small number of > pages at a time in a small transaction. There is no more impact on other > users than there would be if a user were updating a few dozen to a few > hundred rows in a single transaction. > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on my employer, Advanced DataTools, the IIUG, nor any > other organization with which I am associated either explicitly, > implicitly, or by inference. Neither do those opinions reflect those of > other individuals affiliated with any entity with which I am affiliated nor > those of the entities themselves. > > On Wed, Apr 24, 2013 at 7:59 AM, Franck THOMAS > <franck.thomas@consult-ix.fr>wrote: > >> Hi everybody, >> >> I read the technical documentation on defragment feature. >> I did several tests and all works well with the defragment. >> But, I would like to be sure : >> Do you know if there is a risk when we defragment a table or a >> partition when we have a peak of activity (by example : numberous >> transactional query to update, delete or insert into the table) ? >> In your experience, did you meet a problem when we run a defragment on a >> environment production ? >> >> Regards, >> >> -- >> Franck Thomas >> ConsultiX >> franck.thomas@consult-ix.fr >> http://www.consult-ix.fr >> Téléphone : 33 (0) 1 39 12 18 00 >> Mobile : 33 (0) 6 78 81 09 33 >> Fax : 33 (0) 1 39 12 18 18 >> >> >> >> > ******************************************************************************* >> Forum Note: Use "Reply" to post a response in the discussion forum. >> >> > --001a11c20c9ae2c45d04db1b57c8 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Franck Thomas ConsultiX franck.thomas@consult-ix.fr http://www.consult-ix.fr Téléphone : 33 (0) 1 39 12 18 00 Mobile : 33 (0) 6 78 81 09 33 Fax : 33 (0) 1 39 12 18 18

Alexandre's concern's about the effects of the IOs and the manual's notes about buffer usage possibly affecting performance during peak loads are valid. I took the question as applying to safety and lock contention which, as you saw when you tested the deletes, updates, and inserts is not an issue. I do not know how many pages are used, no, and it is not configurable. I'm here at the IIUG Conference in San Diego. There was a session on this and one gotcha came out of it: Be careful not to interrupt the session performing the defragment or to bounce the instance until it completes. The official word is that this is safe, but some users have had problems from long recovery after shutdown to a server crash after killing the defragment session with onmode -z, so caviat emptor. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Apr 25, 2013 at 2:18 AM, Franck THOMAS <franck.thomas@consult-ix.fr>wrote: > Hi Art, Alexandre, > > Thank you for your answers. > > Art, you tell me that there is no risk with defragment feature in the > environment production. > > In the article "Understand the Informix Server V11.7 defragmenter" > > ( > http://www.ibm.com/developerworks/data/library/techarticle/dm-1011informixdefrag menter/ > ), > I read "The defragmenter uses the buffer pool for this movement in order > to keep the data in sync with other uses. *If buffer pool usage is a > concern, then running the defragmenter at a non-peak time is > recommended.**"** > * > When do you think ? > Another question : "The defragment moves a small number of pages at a > time." > Do you know how many pages (maximum) can be moved by the defragment ? > This small number can be changed by us with a Informix parameter ? > > Alexandre, your tips of good pratice are interesting. > > I did some tests, I defragmented a table with 50 million of rows. > During the defragment task, I run several queries : Delete 10 000 rows > or Update 10 000 rows or Insert new 10 000 rows. > No problem. All work. > But, I was not in an environment production. > > Regards, > > Le 24/04/2013 15:33, Art Kagel a écrit : > > The DEFRAGMENT feature is VERY light weight. It moves a small number of > > pages at a time in a small transaction. There is no more impact on other > > users than there would be if a user were updating a few dozen to a few > > hundred rows in a single transaction. > > > > Art > > > > Art S. Kagel > > Advanced DataTools (www.advancedatatools.com) > > Blog: http://informix-myview.blogspot.com/ > > > > Disclaimer: Please keep in mind that my own opinions are my own opinions > > and do not reflect on my employer, Advanced DataTools, the IIUG, nor any > > other organization with which I am associated either explicitly, > > implicitly, or by inference. Neither do those opinions reflect those of > > other individuals affiliated with any entity with which I am affiliated > nor > > those of the entities themselves. > > > > On Wed, Apr 24, 2013 at 7:59 AM, Franck THOMAS > > <franck.thomas@consult-ix.fr>wrote: > > > >> Hi everybody, > >> > >> I read the technical documentation on defragment feature. > >> I did several tests and all works well with the defragment. > >> But, I would like to be sure : > >> Do you know if there is a risk when we defragment a table or a > >> partition when we have a peak of activity (by example : numberous > >> transactional query to update, delete or insert into the table) ? > >> In your experience, did you meet a problem when we run a defragment on a > >> environment production ? > >> > >> Regards, > >> > >> -- > >> Franck Thomas > >> ConsultiX > >> franck.thomas@consult-ix.fr > >> http://www.consult-ix.fr > >> Téléphone : 33 (0) 1 39 12 18 00 > >> Mobile : 33 (0) 6 78 81 09 33 > >> Fax : 33 (0) 1 39 12 18 18 > >> > >> > >> > >> > > > > ******************************************************************************* > >> Forum Note: Use "Reply" to post a response in the discussion forum. > >> > >> > > --001a11c20c9ae2c45d04db1b57c8 > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > Franck Thomas > ConsultiX > franck.thomas@consult-ix.fr > http://www.consult-ix.fr > Téléphone : 33 (0) 1 39 12 18 00 > Mobile : 33 (0) 6 78 81 09 33 > Fax : 33 (0) 1 39 12 18 18 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11c20c9a14411604db2ef4f4