Re: Thoughts on Logical Log use requested

Obnoxio The Clown wrote: > Colin Dawson said: >> Logical Logging >> Buffer bufused bufsize numrecs numpages numwrits recs/pages pages/io >> L-2 0 64 32440954 3833640 3636418 8.5 1.1 > > Decrease the size of your logical log buffer to the minimum value allowed, > or just above it. > :P 1.1 Pages used per I/O and an I/O is 32 Pages, so about 30.9 pages are "empty" :0 So ... shutting down LOGBUFF to 16k will reduce your log usage by a remarkable 75%. However, before doing this I would try and establish how much your average pages/io is during your busy period. You do not want to fill up your LOGBUFF. So, onstat -z, busy busy busy, check onstat -l pages/io and then set your LOGBUFF to a nice value which is bigger than your average. Mash it up Harry!!!

Like Colin I have been absent from the office and having email/gateway problems. It seems the gateway between iiug.org and cdi has not been functioning correctly for a few days. I saw no emails from Monday am to Tuesday late pm. As to the thoughts on log usage - I too have been seeing this effect on a number of systems. Unfortunately I can't do anything about the application. In my case I can get as many as 8 or 10 logs fill up in about 4 minutes and then no logs fill up fro a few hours. It's a tuning challenge. However I am trying to find out what the application is doing at the time. I'm even tempted to use onlog to look at the logs. I did that one before and discovered an bad way of updating a table. To change n columns in the table they did n updates! I'll let you know what I find if I find anything significant. regards Malcolm TBP wrote: > Obnoxio The Clown wrote: > > Colin Dawson said: > >> Logical Logging > >> Buffer bufused bufsize numrecs numpages numwrits recs/pages pages/io > >> L-2 0 64 32440954 3833640 3636418 8.5 1.1 > > > > Decrease the size of your logical log buffer to the minimum value allowed, > > or just above it. > > > :P > > 1.1 Pages used per I/O and an I/O is 32 Pages, so about 30.9 pages are > "empty" :0 > > So ... shutting down LOGBUFF to 16k will reduce your log usage by a > remarkable 75%. > > However, before doing this I would try and establish how much your > average pages/io is during your busy period. You do not want to fill up > your LOGBUFF. > > So, onstat -z, busy busy busy, check onstat -l pages/io and then set > your LOGBUFF to a nice value which is bigger than your average. > > Mash it up Harry!!!

Well I found mine. As usual it is BAD applications developers. How's this for classic code SELECT * from tablename INTO TEMP tabtemp1; SELECT * FROM tabtemp1 WHERE ..... INTO tabtemp2; SELECT * FROM tablename2 INTO TEMP tabtemp3; SELECT * FROM tabtemp3 WHERE .... INTO tabtemp4; SELECT ... FROM tabtemp2 WHERE join_condition; I've deliberately left out the table names and where clauses to protect the innocent. And if I tell you that the two permanent tables concerned are Master-Detail and each has in excess of 2,000,000 rows. And this poor customer is complaining about performance. Can anybody suggest how I tell the customer to correct the problem. regards Malcolm

mweallans@panacea.co.uk wrote: > Well I found mine. As usual it is BAD applications developers. > > How's this for classic code > > SELECT * from tablename INTO TEMP tabtemp1; > SELECT * FROM tabtemp1 > WHERE ..... > INTO tabtemp2; > SELECT * FROM tablename2 INTO TEMP tabtemp3; > SELECT * FROM tabtemp3 > WHERE .... > INTO tabtemp4; > SELECT ... FROM tabtemp2 WHERE join_condition;> > I've deliberately left out the table names and where clauses to protect > the innocent. > > And if I tell you that the two permanent tables concerned are > Master-Detail and each has in excess of 2,000,000 rows. > And this poor customer is complaining about performance. > > Can anybody suggest how I tell the customer to correct the problem. > > regards > > Malcolm > Well, (tongue in cheek) use "WITH NO LOG" (because this is the Logical Log use thread)! Not enough potato EEEEEEEEEEEEEE

mweallans@panacea.co.uk said: > Well I found mine. As usual it is BAD applications developers. > > How's this for classic code > > SELECT * from tablename INTO TEMP tabtemp1; > SELECT * FROM tabtemp1 > WHERE ..... > INTO tabtemp2; > SELECT * FROM tablename2 INTO TEMP tabtemp3; > SELECT * FROM tabtemp3 > WHERE .... > INTO tabtemp4; > SELECT ... FROM tabtemp2 WHERE join_condition;> > I've deliberately left out the table names and where clauses to protect > the innocent. > > And if I tell you that the two permanent tables concerned are > Master-Detail and each has in excess of 2,000,000 rows. > And this poor customer is complaining about performance. > > Can anybody suggest how I tell the customer to correct the problem. "Your developers put the Scunt in Scunthorpe."? -- Bye now, Obnoxio "It's easier with pictures." -- Cosmo "But wait, it gets worse." -- Cosmo "Run, don't walk, for the nearest exit." -- Cosmo

You miss the point entirely. The classic error in this case is that they select everything into a temp table and then filter on the temp table. There are indexes on the main table but not on the temp table. The main table will probably have distributions. But not satisfied with that they do it on two tables. And my typing let me down. The last select is from tabtemp2, tabtemp4 with a join. It could all have been done in one sql statement and the engine would have done it expeditiously. And on the BIG word I rest for the day. Regards Malcolm -----Original Message----- From: informix-list-bounces@iiug.org [mailto:informix-list-bounces@iiug.org] On Behalf Of TBP Sent: 15 March 2006 16:17 To: informix-list@iiug.org Subject: Re: Thoughts on Logical Log use requested mweallans@panacea.co.uk wrote: > Well I found mine. As usual it is BAD applications developers. > > How's this for classic code > > SELECT * from tablename INTO TEMP tabtemp1; > SELECT * FROM tabtemp1 > WHERE ..... > INTO tabtemp2; > SELECT * FROM tablename2 INTO TEMP tabtemp3; > SELECT * FROM tabtemp3 > WHERE .... > INTO tabtemp4; > SELECT ... FROM tabtemp2 WHERE join_condition;> > I've deliberately left out the table names and where clauses to > protect the innocent. > > And if I tell you that the two permanent tables concerned are > Master-Detail and each has in excess of 2,000,000 rows. And this poor > customer is complaining about performance. > > Can anybody suggest how I tell the customer to correct the problem. > > regards > > Malcolm > Well, (tongue in cheek) use "WITH NO LOG" (because this is the Logical Log use thread)! Not enough potato EEEEEEEEEEEEEE _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list

malcolm weallans wrote: > You miss the point entirely. > Well, (tongue in cheek) use "WITH NO LOG" (because this is the Logical > Log use thread)! > > Not enough potato EEEEEEEEEEEEEE Actually, I didn't miss the point, I just felt that the thread was regarding logical log usage not dumb feck sql.