DBSPACETEMP and Temporary Spaces

Problem: Out of Sort Space

I'm trying to do an indexing operation on a large table (500 million rows), and the index continuously fails with an "out of sort space" error. I have my DBTEMPSPACE set to a series of 10 chunks, each with the maximum size of 2 gigabytes. I can watch the temp space usage, and there's no way that I'm using up all of this space. What's happening?

Solution: Build a Big Tempspace

Informix has a 2-gigabyte size limit for chunks, and the way your system is set up is correct for most types of operations. You decided to use 10 separate sort spaces to spread the temp table activity across multiple spindles in the finest tradition of optimizing disk I/O.

When Informix is doing an index, it begins placing temporary tables in each space of your DBTEMPSPACE. As it gets toward the end of the sort, it begins to consolidate these temporary tables into fewer, larger tables. At some point, the table size exceeds the 2 gigabytes size of the chunk. The sort is limited by the size of your smallest dbspace that is used for a tempspace.

The solution is to reboot the engine with no DBSPACETEMP and to drop the dbspaces that were used for temp space. Then re-create it as one very large dbspace having 20 gigabytes, rather than 10 having 2 gigabytes. You'll lose the benefits of multiple spindles, but when the tablesize goes over 2 gigabytes, it will be able to move into the second and subsequent chunks that comprise the large tempspace and the sort will not fail. You can then break the dbspace up into multiple dbspaces if you want to get the I/O benefits, after the index is created.

If you have lots of space in your UNIX filesystem, you could also try setting PSORT_DBTEMP to a large UNIX filesystem and using the PSORT parameters to do parallel sorting outside of the database.

You could also consider creating the index with more fragments. The sort will then generate more sort files but it should distribute them a little more evenly, since the amount of data sorted for each fragment will be less.

Another thing to be concerned about is the size of the dbspaces listed in the DBSPACETEMP variable. Some versions of IDS have a bug (#83745) that occurs during index builds. If IDS finds that the size of the first dbspace listed in DBSPACETEMP was not the same size as all the others, IDS assumes that the total DBSPACETEMP is the size of the first dbspace and restricts itself to using just the space in the first dbspace. This results in a single-threaded, slow index build. It's best to have equal-sized chunks for the dbspaces comprising DBSPACETEMP.

Problem: Out of Space with no Log

When I create a temporary table using "WITH NO LOG," the temporary table seems to be fragmented across all of the dbspaces included in the DBSPACETEMP variable. When I create the temporary table without using the "WITH NO LOG" flag, only one fragment is used. I have several gigabytes of free DBSPACETEMP, and yet when I create a temp table without the "WITH NO LOG" clause, I run out of space. Why?

Solution: Use a Big Temporary Space

The dbspaces detailed in DBSPACETEMP are only used for unlogged temp tables. If you don't use the "WITH NO LOG," implicit temp tables are created in the rootdbs and explicit temp tables are created in the dbspace where the current database resides. Use the "WITH NO LOG," not only for the benefits of no logging, but also to ensure that the temp table is placed in the DBSPACETEMP dbspaces.

Problem: -229 Error

I'm getting a (-229) error when trying to create a temp table. I have plenty of space in DBSPACETEMP and can't create the table. Why?

Solution: Use at Least Two Chunks for each Temporary Dbspace

Sometimes the optimizer will predict a very large number of rows will be returned from a query and will then go out and grab each block of the first chunk of each dbspace in DBSPACETEMP for the result set. When the engine subsequently needs to write to a temporary space for something like a hash join, the query fails, even though not all of the temp space has actually been used. You can avoid this behavior by creating each dbspace listed in DBSPACETEMP with two or more chunks. This avoids the problem because the big query only lays claim to the entire first chunk of each dbspace.