Update statistics filling up too much logical logs

Hi Forum guys, some time ago, I already posted this topic but didn't get a clear answer, yesterday the topic was actual again. OS : Solaris 8, Informix 9.40 UC3 On a new database (in "unbuffered logging") I performed the update statistics. Method based on performance guide and white paper of John Miller. The database has only a total size of 2,8 Gb, update statistics filled up 50 logfiles of 25 Mb (so almost half of the size of the DB) In the past I 've seen that on a DB in "buffered logging" this only fills up about 5 logfiles. What is happening ? Can anyone clear this out ? I can change the mode to buffered logging of course but that's less secure. Most consuming steps : The basic part contains 4765 statements : update medium , distributions only, fills up 19 logs The part on the first column of an index contains 21356 statements : update high, distributions only, fills up 8 logs The part on small tables contains 4282 statements : update high and fills up 19 logs Any advice is appreciated. Jacques Lapeire

UNBUFFERED log databases will use significantly more logical log space if and only if the logical log buffers are very (and unnecessarily) large and your transactions are very small. This is because the main difference between buffered and unbuffered logging is that under unbuffered logging the logical log buffer is flushed if it contains a COMMIT WORK record. Singleton transactions automatically post a BEGIN WORK and COMMIT work statement in any non-ANSI mode database. An update statistics statement that's not preceded by an explicit BEGIN WORK statement is such a singletin transaction. For UPDATE STATISTICS you can reduce the effect by making all of the statements for a single table (or even for the entire database) run under a single transaction. However, the most important step will be to reduce the size of the logical log buffers (LOGBUFF) in the ONCONFIG file as what is flushed to disk whenever a transaction is completed is the current logical log buffer. Art On Tue, Dec 16, 2008 at 3:54 AM, JACQUES LAPEIRE < jacques.lapeire@fujitsu-siemens.com> wrote: > Hi Forum guys, > > some time ago, I already posted this topic but didn't get a clear answer, > yesterday the topic was actual again. > > OS : Solaris 8, Informix 9.40 UC3 > > On a new database (in "unbuffered logging") I performed the update > statistics. > Method based on performance guide and white paper of John Miller. > > The database has only a total size of 2,8 Gb, update statistics filled up > 50 > logfiles of 25 Mb (so almost half of the size of the DB) > In the past I 've seen that on a DB in "buffered logging" this only fills > up > about 5 logfiles. > > What is happening ? Can anyone clear this out ? > I can change the mode to buffered logging of course but that's less secure. > > Most consuming steps : > > The basic part contains 4765 statements : update medium , distributions > only, > fills up 19 logs > The part on the first column of an index contains 21356 statements : update > high, distributions only, fills up 8 logs > The part on small tables contains 4282 statements : update high and fills > up > 19 logs > > Any advice is appreciated. > > Jacques Lapeire > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.

Thanks for the info Art, LOGBUFF is set to 32 (as the default) should I reduce that ???? PHYSBUFF is also set to 32 and BUFFERS (which has nothing to do with this in my opinion, is set to 50000) Am I right if I conclude like this : The logical log buffer (always 3 buffers, in my case of 32 Kb is 96 Kb in total) Unbuffered logging flushes the logical log buffer (also partially filled pages) at commit. As you indicate update statistics as singleton transactions, after every statement the buffer is flushed (can be 32 * 3 = 96 Kb) and as I have 4765 statements in my base-part , this can result in 457440 Kb of logging. Which can be compared with the 19 logical logs of 25 Mb filled , being 475 Mb That seems to match (other activities included) If I don't need or can't reduce the size of LOGBUFF, do you think I can put more than 4000 statements between 1 Begin work and commit; or should I do it every 500 statements for instance ? Thx again Best regards Jacques -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art Kagel Sent: Tuesday, December 16, 2008 12:36 PM To: ids@iiug.org Subject: Re: Update statistics filling up too much logi.... [14321] UNBUFFERED log databases will use significantly more logical log space if and only if the logical log buffers are very (and unnecessarily) large and your transactions are very small. This is because the main difference between buffered and unbuffered logging is that under unbuffered logging the logical log buffer is flushed if it contains a COMMIT WORK record. Singleton transactions automatically post a BEGIN WORK and COMMIT work statement in any non-ANSI mode database. An update statistics statement that's not preceded by an explicit BEGIN WORK statement is such a singletin transaction. For UPDATE STATISTICS you can reduce the effect by making all of the statements for a single table (or even for the entire database) run under a single transaction. However, the most important step will be to reduce the size of the logical log buffers (LOGBUFF) in the ONCONFIG file as what is flushed to disk whenever a transaction is completed is the current logical log buffer. Art On Tue, Dec 16, 2008 at 3:54 AM, JACQUES LAPEIRE < jacques.lapeire@fujitsu-siemens.com> wrote: > Hi Forum guys, > > some time ago, I already posted this topic but didn't get a clear answer, > yesterday the topic was actual again. > > OS : Solaris 8, Informix 9.40 UC3 > > On a new database (in "unbuffered logging") I performed the update > statistics. > Method based on performance guide and white paper of John Miller. > > The database has only a total size of 2,8 Gb, update statistics filled up > 50 > logfiles of 25 Mb (so almost half of the size of the DB) > In the past I 've seen that on a DB in "buffered logging" this only fills > up > about 5 logfiles. > > What is happening ? Can anyone clear this out ? > I can change the mode to buffered logging of course but that's less secure. > > Most consuming steps : > > The basic part contains 4765 statements : update medium , distributions > only, > fills up 19 logs > The part on the first column of an index contains 21356 statements : update > high, distributions only, fills up 8 logs > The part on small tables contains 4282 statements : update high and fills > up > 19 logs > > Any advice is appreciated. > > Jacques Lapeire > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Note that only the 'current' logical log buffer is flushed not all three. There are three so that you can always be writing to one while one or both of the remaining buffers are being flushed to disk. A logbuff of 32(K) is not unreasonable however, a single UPDATE STATISTICS command is likely to be smaller than 3K which is why you seem to be wasting 90% of each log buffer and so your logical log disk space. One aside, make certain that you have DBTEMPSPACE set to include one or more 'temp' dbspaces so that sorting does not default to using the logged ROOT dbspace. I would commit and begin anew after processing each table. Also, following John's recommendations, minimize the number of separate statements - this will also go a ways to reduce logical log waste. So: DO SELECT table info; BEGIN WORK; U...S... MEDIUM (list all columns for which you are not doing HIGH); U...S... HIGH (list all columns that require HIGH stats) DISTRIBUTIONS ONLY; for each index: U...S...LOW (on the full key of the index); COMMIT WORK; LOOP Dostats doesn't perform the BEGIN WORK and COMMIT WORK statements unless it's processing an ANSI mode database. Sounds like I need to add an option for that in there as well. Art On Tue, Dec 16, 2008 at 9:30 AM, Lapeire, Jacques < Jacques.Lapeire@fujitsu-siemens.com> wrote: > Thanks for the info Art, > > LOGBUFF is set to 32 (as the default) should I reduce that ???? > PHYSBUFF is also set to 32 and BUFFERS (which has nothing to do with this > in > my opinion, is set to 50000) > > Am I right if I conclude like this : > > The logical log buffer (always 3 buffers, in my case of 32 Kb is 96 Kb in > total) > Unbuffered logging flushes the logical log buffer (also partially filled > pages) at commit. > As you indicate update statistics as singleton transactions, after every > statement the buffer is flushed (can be 32 * 3 = 96 Kb) > and as I have 4765 statements in my base-part , this can result in 457440 > Kb > of logging. > > Which can be compared with the 19 logical logs of 25 Mb filled , being 475 > Mb > > That seems to match (other activities included) > > If I don't need or can't reduce the size of LOGBUFF, do you think I can put > more than 4000 statements between 1 Begin work and commit; > or should I do it every 500 statements for instance ? > > Thx again > > Best regards > Jacques > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art > Kagel > Sent: Tuesday, December 16, 2008 12:36 PM > To: ids@iiug.org > Subject: Re: Update statistics filling up too much logi.... [14321] > > UNBUFFERED log databases will use significantly more logical log space if > and > only if the logical log buffers are very (and unnecessarily) large and your > transactions are very small. This is because the main difference between > buffered and unbuffered logging is that under unbuffered logging the > logical > log buffer is flushed if it contains a COMMIT WORK record. > Singleton transactions automatically post a BEGIN WORK and COMMIT work > statement in any non-ANSI mode database. An update statistics statement > that's > not preceded by an explicit BEGIN WORK statement is such a singletin > transaction. > > For UPDATE STATISTICS you can reduce the effect by making all of the > statements for a single table (or even for the entire database) run under a > single transaction. However, the most important step will be to reduce the > size of the logical log buffers (LOGBUFF) in the ONCONFIG file as what is > flushed to disk whenever a transaction is completed is the current logical > log > buffer. > > Art > > On Tue, Dec 16, 2008 at 3:54 AM, JACQUES LAPEIRE < > jacques.lapeire@fujitsu-siemens.com> wrote: > > > Hi Forum guys, > > > > some time ago, I already posted this topic but didn't get a clear answer, > > yesterday the topic was actual again. > > > > OS : Solaris 8, Informix 9.40 UC3 > > > > On a new database (in "unbuffered logging") I performed the update > > statistics. > > Method based on performance guide and white paper of John Miller. > > > > The database has only a total size of 2,8 Gb, update statistics filled up > > 50 > > logfiles of 25 Mb (so almost half of the size of the DB) > > In the past I 've seen that on a DB in "buffered logging" this only fills > > up > > about 5 logfiles. > > > > What is happening ? Can anyone clear this out ? > > I can change the mode to buffered logging of course but that's less > secure. > > > > Most consuming steps : > > > > The basic part contains 4765 statements : update medium , distributions > > only, > > fills up 19 logs > > The part on the first column of an index contains 21356 statements : > update > > high, distributions only, fills up 8 logs > > The part on small tables contains 4282 statements : update high and fills > > up > > 19 logs > > > > Any advice is appreciated. > > > > Jacques Lapeire > > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > Art S. Kagel > Oninit (www.oninit.com) > IIUG Board of Directors (art@iiug.org) > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and > do not reflect on my employer, Oninit, the IIUG, nor any other organization > with which I am associated either explicitly or implicitly. Neither do > those opinions reflect those of other individuals affiliated with any > entity > with which I am affiliated nor those of the entities themselves. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.

Art, here's some feedback. I ran a couple of tests on my existing data (4765 update statements) using the begin-commit work statements. Without begin-commit (as it originally was) : runs 34 minutes using 19 logs With 1 begin-commit (including all satements) : runs 22 minutes using 10 logs ==> faster and less logs With 1 begin-commit for every 100 statements : runs 7,5 minutes using 10 logs ==> fastest and also less logs When 1 put 1 begin-commit for every 100, 200 or 400 statements there 's no difference anymore. Can't get below the 10 logs Thx, Jacques -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art Kagel Sent: Tuesday, December 16, 2008 3:50 PM To: ids@iiug.org Subject: Re: Update statistics filling up too much logi.... [14323] Note that only the 'current' logical log buffer is flushed not all three. There are three so that you can always be writing to one while one or both of the remaining buffers are being flushed to disk. A logbuff of 32(K) is not unreasonable however, a single UPDATE STATISTICS command is likely to be smaller than 3K which is why you seem to be wasting 90% of each log buffer and so your logical log disk space. One aside, make certain that you have DBTEMPSPACE set to include one or more 'temp' dbspaces so that sorting does not default to using the logged ROOT dbspace. I would commit and begin anew after processing each table. Also, following John's recommendations, minimize the number of separate statements - this will also go a ways to reduce logical log waste. So: DO SELECT table info; BEGIN WORK; U...S... MEDIUM (list all columns for which you are not doing HIGH); U...S... HIGH (list all columns that require HIGH stats) DISTRIBUTIONS ONLY; for each index: U...S...LOW (on the full key of the index); COMMIT WORK; LOOP Dostats doesn't perform the BEGIN WORK and COMMIT WORK statements unless it's processing an ANSI mode database. Sounds like I need to add an option for that in there as well. Art On Tue, Dec 16, 2008 at 9:30 AM, Lapeire, Jacques < Jacques.Lapeire@fujitsu-siemens.com> wrote: > Thanks for the info Art, > > LOGBUFF is set to 32 (as the default) should I reduce that ???? > PHYSBUFF is also set to 32 and BUFFERS (which has nothing to do with > this in my opinion, is set to 50000) > > Am I right if I conclude like this : > > The logical log buffer (always 3 buffers, in my case of 32 Kb is 96 Kb > in > total) > Unbuffered logging flushes the logical log buffer (also partially > filled > pages) at commit. > As you indicate update statistics as singleton transactions, after > every statement the buffer is flushed (can be 32 * 3 = 96 Kb) and as I > have 4765 statements in my base-part , this can result in 457440 Kb of > logging. > > Which can be compared with the 19 logical logs of 25 Mb filled , being > 475 Mb > > That seems to match (other activities included) > > If I don't need or can't reduce the size of LOGBUFF, do you think I > can put more than 4000 statements between 1 Begin work and commit; or > should I do it every 500 statements for instance ? > > Thx again > > Best regards > Jacques > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Art Kagel > Sent: Tuesday, December 16, 2008 12:36 PM > To: ids@iiug.org > Subject: Re: Update statistics filling up too much logi.... [14321] > > UNBUFFERED log databases will use significantly more logical log space > if and only if the logical log buffers are very (and unnecessarily) > large and your transactions are very small. This is because the main > difference between buffered and unbuffered logging is that under > unbuffered logging the logical log buffer is flushed if it contains a > COMMIT WORK record. > Singleton transactions automatically post a BEGIN WORK and COMMIT work > statement in any non-ANSI mode database. An update statistics > statement that's not preceded by an explicit BEGIN WORK statement is > such a singletin transaction. > > For UPDATE STATISTICS you can reduce the effect by making all of the > statements for a single table (or even for the entire database) run > under a single transaction. However, the most important step will be > to reduce the size of the logical log buffers (LOGBUFF) in the > ONCONFIG file as what is flushed to disk whenever a transaction is > completed is the current logical log buffer. > > Art > > On Tue, Dec 16, 2008 at 3:54 AM, JACQUES LAPEIRE < > jacques.lapeire@fujitsu-siemens.com> wrote: > > > Hi Forum guys, > > > > some time ago, I already posted this topic but didn't get a clear > > answer, yesterday the topic was actual again. > > > > OS : Solaris 8, Informix 9.40 UC3 > > > > On a new database (in "unbuffered logging") I performed the update > > statistics. > > Method based on performance guide and white paper of John Miller. > > > > The database has only a total size of 2,8 Gb, update statistics > > filled up 50 logfiles of 25 Mb (so almost half of the size of the > > DB) In the past I 've seen that on a DB in "buffered logging" this > > only fills up about 5 logfiles. > > > > What is happening ? Can anyone clear this out ? > > I can change the mode to buffered logging of course but that's less > secure. > > > > Most consuming steps : > > > > The basic part contains 4765 statements : update medium , > > distributions only, fills up 19 logs The part on the first column of > > an index contains 21356 statements : > update > > high, distributions only, fills up 8 logs The part on small tables > > contains 4282 statements : update high and fills up > > 19 logs > > > > Any advice is appreciated. > > > > Jacques Lapeire > > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > Art S. Kagel > Oninit (www.oninit.com) > IIUG Board of Directors (art@iiug.org) > > Disclaimer: Please keep in mind that my own opinions are my own > opinions and do not reflect on my employer, Oninit, the IIUG, nor any > other organization with which I am associated either explicitly or > implicitly. Neither do those opinions reflect those of other > individuals affiliated with any entity with which I am affiliated nor > those of the entities themselves. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer

If there is no other update activity on the server at the time then I'm out of ideas. You've made a significant impact on the process however, and made it clear to me that I have to add transaction handling to dostats ;-) Art On Wed, Dec 17, 2008 at 4:58 AM, Lapeire, Jacques < Jacques.Lapeire@fujitsu-siemens.com> wrote: > Art, > > here's some feedback. > > I ran a couple of tests on my existing data (4765 update statements) using > the > begin-commit work statements. > > Without begin-commit (as it originally was) : runs 34 minutes using 19 logs > With 1 begin-commit (including all satements) : runs 22 minutes using 10 > logs > ==> faster and less logs > With 1 begin-commit for every 100 statements : runs 7,5 minutes using 10 > logs > ==> fastest and also less logs > When 1 put 1 begin-commit for every 100, 200 or 400 statements there 's no > difference anymore. > > Can't get below the 10 logs > > Thx, > Jacques > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art > Kagel > Sent: Tuesday, December 16, 2008 3:50 PM > To: ids@iiug.org > Subject: Re: Update statistics filling up too much logi.... [14323] > > Note that only the 'current' logical log buffer is flushed not all three. > There are three so that you can always be writing to one while one or both > of > the remaining buffers are being flushed to disk. A logbuff of 32(K) is not > unreasonable however, a single UPDATE STATISTICS command is likely to be > smaller than 3K which is why you seem to be wasting 90% of each log buffer > and > so your logical log disk space. One aside, make certain that you have > DBTEMPSPACE set to include one or more 'temp' dbspaces so that sorting does > not default to using the logged ROOT dbspace. > > I would commit and begin anew after processing each table. Also, following > John's recommendations, minimize the number of separate statements - this > will > also go a ways to reduce logical log waste. So: > DO > > SELECT table info; > > BEGIN WORK; > > U...S... MEDIUM (list all columns for which you are not doing HIGH); > > U...S... HIGH (list all columns that require HIGH stats) DISTRIBUTIONS > ONLY; > > for each index: > > U...S...LOW (on the full key of the index); > > COMMIT WORK; > LOOP > > Dostats doesn't perform the BEGIN WORK and COMMIT WORK statements unless > it's > processing an ANSI mode database. Sounds like I need to add an option for > that > in there as well. > > Art > > On Tue, Dec 16, 2008 at 9:30 AM, Lapeire, Jacques < > Jacques.Lapeire@fujitsu-siemens.com> wrote: > > > Thanks for the info Art, > > > > LOGBUFF is set to 32 (as the default) should I reduce that ???? > > PHYSBUFF is also set to 32 and BUFFERS (which has nothing to do with > > this in my opinion, is set to 50000) > > > > Am I right if I conclude like this : > > > > The logical log buffer (always 3 buffers, in my case of 32 Kb is 96 Kb > > in > > total) > > Unbuffered logging flushes the logical log buffer (also partially > > filled > > pages) at commit. > > As you indicate update statistics as singleton transactions, after > > every statement the buffer is flushed (can be 32 * 3 = 96 Kb) and as I > > have 4765 statements in my base-part , this can result in 457440 Kb of > > logging. > > > > Which can be compared with the 19 logical logs of 25 Mb filled , being > > 475 Mb > > > > That seems to match (other activities included) > > > > If I don't need or can't reduce the size of LOGBUFF, do you think I > > can put more than 4000 statements between 1 Begin work and commit; or > > should I do it every 500 statements for instance ? > > > > Thx again > > > > Best regards > > Jacques > > > > -----Original Message----- > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > > Art Kagel > > Sent: Tuesday, December 16, 2008 12:36 PM > > To: ids@iiug.org > > Subject: Re: Update statistics filling up too much logi.... [14321] > > > > UNBUFFERED log databases will use significantly more logical log space > > if and only if the logical log buffers are very (and unnecessarily) > > large and your transactions are very small. This is because the main > > difference between buffered and unbuffered logging is that under > > unbuffered logging the logical log buffer is flushed if it contains a > > COMMIT WORK record. > > Singleton transactions automatically post a BEGIN WORK and COMMIT work > > statement in any non-ANSI mode database. An update statistics > > statement that's not preceded by an explicit BEGIN WORK statement is > > such a singletin transaction. > > > > For UPDATE STATISTICS you can reduce the effect by making all of the > > statements for a single table (or even for the entire database) run > > under a single transaction. However, the most important step will be > > to reduce the size of the logical log buffers (LOGBUFF) in the > > ONCONFIG file as what is flushed to disk whenever a transaction is > > completed is the current logical log buffer. > > > > Art > > > > On Tue, Dec 16, 2008 at 3:54 AM, JACQUES LAPEIRE < > > jacques.lapeire@fujitsu-siemens.com> wrote: > > > > > Hi Forum guys, > > > > > > some time ago, I already posted this topic but didn't get a clear > > > answer, yesterday the topic was actual again. > > > > > > OS : Solaris 8, Informix 9.40 UC3 > > > > > > On a new database (in "unbuffered logging") I performed the update > > > statistics. > > > Method based on performance guide and white paper of John Miller. > > > > > > The database has only a total size of 2,8 Gb, update statistics > > > filled up 50 logfiles of 25 Mb (so almost half of the size of the > > > DB) In the past I 've seen that on a DB in "buffered logging" this > > > only fills up about 5 logfiles. > > > > > > What is happening ? Can anyone clear this out ? > > > I can change the mode to buffered logging of course but that's less > > secure. > > > > > > Most consuming steps : > > > > > > The basic part contains 4765 statements : update medium , > > > distributions only, fills up 19 logs The part on the first column of > > > an index contains 21356 statements : > > update > > > high, distributions only, fills up 8 logs The part on small tables > > > contains 4282 statements : update high and fills up > > > 19 logs > > > > > > Any advice is appreciated. > > > > > > Jacques Lapeire > > > > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > -- > > Art S. Kagel > > Oninit (www.oninit.com) > > IIUG Board of Directors (art@iiug.org) > > > > Disclaimer: Please keep in mind that my own opinions are my own > > opinions and do not reflect on my employer, Oninit, the IIUG, nor any > > other organization with which I am associated either explicitly or > > implicitly. Ne