We are having some problems with Maxdb and a complicated query.
The error we receive from the JDBC connector indicates that the space
for results tables has been exhausted. The query in question runs on a
view and after further investigation it appears a "select *" on the view
has the same effect.
This is the error we receive in the log file:
2004-06-02 08:44:14 16 11007 COMMUNIC wait for connection T39
2004-06-02 09:26:40 16 WNG 40 Kernel_E 80 percent of data base
occupied, 2560 pages free
2004-06-02 09:26:44 16 WNG 40 Kernel_E 90 percent of data base
occupied, 1280 pages free
2004-06-02 09:26:56 16 WNG 40 Kernel_E 95 percent of data base
occupied, 640 pages free
2004-06-02 09:26:56 16 WNG 41 Converte Force Rollback due to
DB-Full. Tasks 38 holds to many temp pages #all p.= 12799 #all temp. p.=
10142 #task temp p.=
2004-06-02 09:26:56 16 WNG 41 Converte 10133
2004-06-02 09:26:56 16 42 Kernel_E DB usage fell below 95
percent, 640 pages free
2004-06-02 09:26:56 16 43 Kernel_E 95 percent of log area
occupied, 12159 pages used
2004-06-02 09:26:56 16 42 Kernel_E DB usage fell below 90
percent, 1280 pages free
2004-06-02 09:26:56 16 43 Kernel_E 90 percent of log area
occupied, 11519 pages used
2004-06-02 09:26:56 16 42 Kernel_E DB usage fell below 80
percent, 2560 pages free
2004-06-02 09:26:56 16 43 Kernel_E 80 percent of log area
occupied, 10239 pages used
2004-06-02 09:26:56 16 11560 COMMUNIC Releasing T38
Currently we have two log volumes both 50mb in size which are about 40%
full (ie 60mb free). Originally there was only one and when this query
started to fail (as the data grew) we added another log volume in an
effort to cure the problem. We also have one data volume of 100mb that
is about 17% full.
I am not really an expert on explain plans (see below) but I don't
believe this is that bad. The score is fairly high because there is a
lot of data. However I have seen much higher scores on Oracle and the
queries run fine. At least one table scan has to occur as the view needs
all results in the join, though I do find it odd that it can be a
different table on a different database (?).
ASS TABLE SCAN
24
A ID_AUDITS JOIN VIA KEY
COLUMN 16
S ID_SURVEYS JOIN VIA KEY
COLUMN 1
ST ID_SURVEY_TEMPLATES JOIN VIA KEY
COLUMN 1
C ID_COMPANIES JOIN VIA KEY
COLUMN 12
AR IDX_FK_AR_AUDITS JOIN VIA INDEXED
COLUMN 2
DZ ID_COMPANIES JOIN VIA KEY
RANGE 4
SR ID_SUB_REGIONS JOIN VIA KEY
COLUMN 6
R ID_REGIONS JOIN VIA KEY
COLUMN 4
AA IDX_FK_AUDITANS_RESPONSES JOIN VIA INDEXED
COLUMN 291
Q ID_QUESTIONS JOIN VIA KEY
COLUMN 5
INTERNAL TEMPORARY RESULT TABLE SCAN
500
INTERNAL TEMPORARY RESULT TABLE SCAN
500
INTERNAL TEMPORARY RESULT TABLE SCAN
500
RESULT IS
COPIED , COSTVALUE IS 20203
Does anybody have any ideas as to why this happens and what we can do
to avoid it happening? I don't think the view has more than 150000
entries surely MaxDB can cope with this?? Is there a setting that I have
missed? Any traces I can turn on to get more information? Any help would
be much appreciated.
Thanks in advance, Laurence.
This Message has been Checked at MSXI for all known Viruses.
You open this at your own risk. Please make sure all replies are
also virus free.
Also we do not accept or send Attachments of the type .exe, .vbs,
scr, or .bat due to the virus risk they can contain. These types of
attachments will be stripped from the message.
MSXI
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
