temporary space - doesn't scale?

Hi Folks, We're running IDS.2000 (9.21.UC3-2) on RedHat Linux 6.2, and our program is running out of temp space (-229: Could not open or create a temporary file.) What bothers me is I've allocated a 2GB dbspace for temporary usage, but I'm running the program on our "tiny" database (6000 records). I'm thinking there is no *way* informix will scale to our "small" database of 1M records. It seems to me that the engine is a hog when it comes to space usage - I guess they traded space for time? How are others getting around this problem? Here's the query, FYI: SELECT P1.item_id, P2.item_id, date, time, quantity, trans_label FROM av_2 P1, av_2 P2, training_data_fully_labeled WHERE P1.value=training_data_fully_labeled.origin AND P2.value=training_data_fully_labeled.destination Thanks. matt

In article <3A37878E.7F9B63E6@cs.umass.edu>, Matthew Cornell <cornell@cs.umass.edu> wrote: > Hi Folks, > > We're running IDS.2000 (9.21.UC3-2) on RedHat Linux 6.2, and our program > is running out of temp space (-229: Could not open or create a temporary > file.) What bothers me is I've allocated a 2GB dbspace for temporary > usage, but I'm running the program on our "tiny" database (6000 > records). I'm thinking there is no *way* informix will scale to our > "small" database of 1M records. It seems to me that the engine is a hog > when it comes to space usage - I guess they traded space for time? How > are others getting around this problem? Here's the query, FYI: > > SELECT P1.item_id, P2.item_id, date, time, quantity, trans_label > FROM av_2 P1, av_2 P2, training_data_fully_labeled > WHERE P1.value=training_data_fully_labeled.origin AND > P2.value=training_data_fully_labeled.destination> > Thanks. > > matt Is DBSPACETEMP in your ONCONFIG set to that temporary dbpsace? > Sent via Deja.com http://www.deja.com/

Looks to me like you've joined P1 to T and P2 to T but you haven't joined P1 to P2 so the engine is doing a cross product on P1 and P2. Since P1 is the same table as P2, then if P1 has 6,000 rows, P1 x P2 = at least 36,000 rows in the result set (ignoring T). If the goal is to retrieve rows from T where the origin is in P1 and the destination is in P2, you may need something like: select ... from T where exists (select 1 from P1 where P1.item_id = T.item_id) and exists (select 1 from P2 where P2.item_id = T.item_id) In article <3A37878E.7F9B63E6@cs.umass.edu>, Matthew Cornell <cornell@cs.umass.edu> wrote: > Hi Folks, > > We're running IDS.2000 (9.21.UC3-2) on RedHat Linux 6.2, and our program > is running out of temp space (-229: Could not open or create a temporary > file.) What bothers me is I've allocated a 2GB dbspace for temporary > usage, but I'm running the program on our "tiny" database (6000 > records). I'm thinking there is no *way* informix will scale to our > "small" database of 1M records. It seems to me that the engine is a hog > when it comes to space usage - I guess they traded space for time? How > are others getting around this problem? Here's the query, FYI: > > SELECT P1.item_id, P2.item_id, date, time, quantity, trans_label > FROM av_2 P1, av_2 P2, training_data_fully_labeled > WHERE P1.value=training_data_fully_labeled.origin AND > P2.value=training_data_fully_labeled.destination> > Thanks. > > matt > Sent via Deja.com http://www.deja.com/

In the year of Our Lord Wed, 13 Dec 2000 09:28:30 -0500, Matthew Cornell <cornell@cs.umass.edu> broke a vow of silence to utter: >We're running IDS.2000 (9.21.UC3-2) on RedHat Linux 6.2, and our program >is running out of temp space (-229: Could not open or create a temporary >file.) What bothers me is I've allocated a 2GB dbspace for temporary >usage, but I'm running the program on our "tiny" database (6000 >records). I'm thinking there is no *way* informix will scale to our >"small" database of 1M records. It seems to me that the engine is a hog >when it comes to space usage - I guess they traded space for time? How >are others getting around this problem? Here's the query, FYI: > >SELECT P1.item_id, P2.item_id, date, time, quantity, trans_label >FROM av_2 P1, av_2 P2, training_data_fully_labeled >WHERE P1.value=training_data_fully_labeled.origin AND > P2.value=training_data_fully_labeled.destination That's right, blame the server. You haven't really given us enough info to make any kind of assessment here, but you're definitely doing a cartesian product between P1 and P2, possibly even a 3-way cartesian. -229 also implies an OS file error, rather than a dbspace error. Have you, for instance, got DBTEMP or PSORT_DBTEMP set to a directory that doesn't exist or is full? Are the permissions OK on your tempdbs chunk? Is /tmp full? Anyway, you seem to be like that Marques chappie -- quick to point fingers and slow to read manuals (in this case, the output from finderr) -- maybe you should use that super special Postgres and leave us to fiddle with this inferior junk.

Obnoxio The Clown wrote: > That's right, blame the server. > > You haven't really given us enough info to make any kind of assessment here, but > you're definitely doing a cartesian product between P1 and P2, possibly even a > 3-way cartesian. Right. That's the first way we worked out the SQL. Maybe that's an unreasonable approach. > -229 also implies an OS file error, rather than a dbspace error. Have you, for > instance, got DBTEMP or PSORT_DBTEMP set to a directory that doesn't exist or is > full? Are the permissions OK on your tempdbs chunk? Is /tmp full? DBSPACETEMP is set to tempdbs, where tempdbs was created using: onspaces -c -d tempdbs -t -p ~informix/links/chunk2 -o 0 -s2000000 The chunk is a cooked file, and is working fine for other queries. After I bumped it up to 2GB it got past the error, so it wasn't an OS file error. I used onstat to look at the space/chunk as the program ran, and I could see the free space dropping each second. With 2GB it stops with 300K free left - close! I'm concerned > Anyway, you seem to be like that Marques chappie -- quick to point fingers and > slow to read manuals (in this case, the output from finderr) -- maybe you should > use that super special Postgres and leave us to fiddle with this inferior junk. I can't believe Informix is junk - there are so many dedicated users. :-) You are quite right to aim your sarcasm at me - I'm writing while frustrated, tired, and pressed for results, which makes for regrettable, incomplete, and generally stupid posts like mine these last few days. I apologize. matt

georhill@my-deja.com wrote in message <918aml$ruc$1@nnrp1.deja.com>... >Looks to me like you've joined P1 to T and P2 to T but you haven't >joined P1 to P2 so the engine is doing a cross product on P1 and P2. >Since P1 is the same table as P2, then if P1 has 6,000 rows, P1 x P2 = >at least 36,000 rows in the result set (ignoring T). Ehh yes. If you are new to Informix, but perhaps familiar with an engine that automatically does joins between tables, well, informix doesn't. Sadly you must spell out all joins between tables.

georhill@my-deja.com wrote: > > Looks to me like you've joined P1 to T and P2 to T but you haven't > joined P1 to P2 so the engine is doing a cross product on P1 and P2. My immediate reaction to the SQL was precisely this, but I sat down and thought about it for a few moments (and a few extra posts) and I don't think that there is a cross-product. If you have N tables in the FROM clause, you need N-1 join conditions (at minimum) to ensure that there are no cross-products. The FROM clause has 3 tables, so it needs just 2 joins, and that's what it has. Think of it as a sequential scan down the training_data_fully_labelled table (why do Americans keep droping the double leters in the midle of words, but why don't they do the job fuly when they do it at al? :-), with a join to the P1 incarnation of av_2 and a second join to the P2 incarnation of av_2. No cross-producting there. Question: what does SET EXPLAIN ON yield as a query plan? Question: is there an index on av_2.value? Question: what are the number of rows in av_2 and in training_data_fully_labelled? Question: when did you run UPDATE STATISTICS? Question: why not create a table alias for the training_data_fully_labeled table (eg T or TDFL) and then tag each of the items in the SELECT list with the table the data comes from? In practice, the untagged items must be from TDFL as otherwise they'd be ambiguous between P1 and P2, but explicitness is a good idea. Even with bad answers to most of these questions, I'm not convinced you should be running into troubles with the query, so there is probably something up with the temp space, but I have yet to be convinced that the query as written is wrong. > Since P1 is the same table as P2, then if P1 has 6,000 rows, P1 x P2 = > at least 36,000 rows in the result set (ignoring T). > If the goal is to retrieve rows from T where the origin is in P1 and > the destination is in P2, you may need something like: > select ... > from T > where exists (select 1 from P1 where P1.item_id = T.item_id) > and exists (select 1 from P2 where P2.item_id = T.item_id)> > In article <3A37878E.7F9B63E6@cs.umass.edu>, > Matthew Cornell <cornell@cs.umass.edu> wrote: > > We're running IDS.2000 (9.21.UC3-2) on RedHat Linux 6.2, and our program > > is running out of temp space (-229: Could not open or create a temporary > > file.) What bothers me is I've allocated a 2GB dbspace for temporary > > usage, but I'm running the program on our "tiny" database (6000 > > records). I'm thinking there is no *way* informix will scale to our > > "small" database of 1M records. It seems to me that the engine is a hog > > when it comes to space usage - I guess they traded space for time? How > > are others getting around this problem? Here's the query, FYI: > > > > SELECT P1.item_id, P2.item_id, date, time, quantity, trans_label > > FROM av_2 P1, av_2 P2, training_data_fully_labeled > > WHERE P1.value=training_data_fully_labeled.origin AND > > P2.value=training_data_fully_labeled.destination -- Yours, Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h> Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN "I don't suffer from insanity; I enjoy every minute of it!"