We have had a problem on our production system which is becoming
critical after requiring Informix intervention to free our logical
logs twice last week.
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.
1. Will fewer, larger logs make any difference? (e.g. 4 * 24 Meg logs).
2. Have other people had similar problems with ACE reports, and what
did you do to work around this problem?
RS/6000 Model 550 w/64 Meg RAM
AIX 3.15 <gak!>
Informix On-Line 4.00.UH3
Informix SQL 4.00.UH1
Informix 4GL 4.00.UH1
dbspace = 2 X 384 Meg partitions, 64000 pages dbspace free
United Engineers & Constructors Voice: 011-353-21-378811
Ringaskiddy, Ireland FAX: 011-353-21-378285