Re: Transactions jamming logical logs - INFORMIX 4.0

>From: Paul Begley <uunet!sandoz!peb> >Subject: Transactions jamming logical logs - INFORMIX 4.0 >Date: Wed, 10 Mar 93 7:58:58 CUT >X-Informix-List-Id: <list.2015> >Transactions spanning logical logs and 'lock' our On-Line engine. I >recognize that there are features of 4.1 which support high-water marks >for the logs, and you can turn logging off for temp tables, but we >can't migrate to 4.1 until our software provider supports it. This >will be at least 8-12 weeks. Our users run 'ACE reports from hell'. >The worst reports use DISTINCT selects with outer joins on several >tables with 15,000 to 90,000 records. The biggest problem seems to >come from the use of multiple temporary tables. Evaluating the reports >from ISQL with SET EXPLAIN ON showed some costs as high as 29,000. Our >typical report is more on the order of 3,000. >I am interested in suggestions on reconfiguring the logs or engine >which may help the problem until we upgrade. System activity is such >that we fill 120 to 200 four megabyte logical logs between Friday >evening and Monday morning while various weekly reports are running. >The system currently has 24 * 4 Meg logs. We run continuous backup of >logical logs to 8mm tape on weekends and 150 Meg tape daily. The IBM >8mm tape seems to be very particular about media, and although we have >had no problems with Archives, I have had a failure of one sort or >another every weekend this year. >Questions: >1. Will fewer, larger logs make any difference? (e.g. 4 * 24 Meg logs). Yes, and No, but mainly Yes. Yes, it is likely to stop the system jamming up, because with 4.00, a transaction is rolled back (as a long transact or LTX) when its log records move into the last logical log. With 24 small logs, there can actually be insufficient room left to write all the CLR (compensation log records) which are required to record that the transaction has been rolled back, which causes the system to jam. In 4.10 and beyond, the parameters LTXHWM and LTXEHWM (which are percentages 0..100) specify the percentage of the way through the logs at which a transaction starts rolling back, and the percentage at which all other processes are prevented from writing until the rolling back process finishes. The default values for these are 80 and 90. Using 4 logs simulates LTXHWM = 75 and LTXEHWM = 100. However, the downside is that some transactions which would have completed using 24 * 4 MB logs won't complete because they used say 88 MB (22 * 4), and now get stopped at 72 MB (3 * 24). There is no actual guarantee that that 4 logs will prevent the system lockup (nor that the LTXHWM/LTXEHWM parameters will, either), but the possibility is be reduced to negligible proportions. To determine whether you are safe requires detailed knowledge of what the transaction is doing. One other question: are you running with either unbuffered logging or log mode ansi? If so, many of your log pages may be quite empty, because in these modes, the logical log buffer gets written out when any transaction commits, even if the page is partially full. You can see the effect by looking at the output of tbstat -l. The pages per I/O is typically 1.1 or 1.2 on the logical when unbuffered logging is in use, whereas it will usually be 80-90% of bufsize if buffered logging is used. Can you change from unbuffered to buffered logging? >2. Have other people had similar problems with ACE reports, and what >did you do to work around this problem? You can try using: SELECT * FROM table01 INTO TEMP t1 WITH NO LOG My ISQL 4.00 Quick Reference card indicates that the syntax is supported by the engine -- the only issue is whether ACE allows you to use it. If it does, then use it and your reports should cease to be troublesome. Thank you for including the system configuration information in your request. Yours, Jonathan Leffler (johnl@obelix.informix.com) #include <disclaimer.h>