SEO

#1 (**permalink**) 04-20-2008, 10:54 AM

Hi All,

Does anyone have any recommendations / bad experiences / general
opinion on using RAMDISK for temp dbspaces ?

Over 90% of our read & write disk activity is on 20 temp dbspaces
(approx. 30-35GB) but we are in the fortunate position of having that
much spare RAM on the box. So we kind of figure this could maybe help
;-)

Phil Torkington

#2 (**permalink**) 04-20-2008, 10:54 AM

ahum not supported...

if you do decide to use it; make sure that you have a couple of real
temdbspaces
configured.
if the machine reboots your ramdisk tempspaces are gone and
fast recovery may need tempspace in order to do things.

Superboer

topdeck schreef:

> Hi All,
>
> Does anyone have any recommendations / bad experiences / general
> opinion on using RAMDISK for temp dbspaces ?
>
> Over 90% of our read & write disk activity is on 20 temp dbspaces
> (approx. 30-35GB) but we are in the fortunate position of having that
> much spare RAM on the box. So we kind of figure this could maybe help
> ;-)
>
> Phil Torkington

#3 (**permalink**) 04-20-2008, 10:54 AM

Another try could be to increase DS_NONPDQ_QUERY_MEM significatntly:
Must be >= 128 and <= 0.25 * DS_TOTAL_MEMORY
DS_NONPDQ_QUERY_MEM 65536 # Non PDQ query memory (Kbytes)
$ onmode -wm DS_NONPDQ_QUERY_MEM=65536 (IDS 10)
Could decrease IO into temp spaces.

#4 (**permalink**) 04-20-2008, 10:55 AM

and you will need to recreate the temp dbspaces on startup...

you might not be able to drop them if they are "corrupt" since the
underlying file has gone..

Check the application usage of temp tables no rows /columns you do not
need.
It may be faster to

- get the first n tables in the query into temp table 1
- build an index on temp table 1
- update statistics high on temp table 1
- run a second query joining temp table 1 to the rest of the tables.

This allow IDS to elimiate rows earlier in the query and partially
forces it to use
a given query plan...

and make sure it is using the fright indexes..

#5 (**permalink**) 04-20-2008, 10:59 AM

thanks Superboer......according to IBM they will support us using
RAMDISK as temp dbspaces and there existing customers out there doing
this. But def. hear what you're saying about keeping some real
dbspaces...

We are actively tuning the sql at the same time but we have a strategy
in place where certain applications pull out huge amounts of data
before carrying all kinds of processing on the data for search engines-
obviously this ain't necessarily the best way of doing things but its
not going to change overnight. So I guess we're looking for a quick win
in terms of performance but don't want to lose stability at the same
time.

We're going to do extensive benchmark/load runner testing as well
testing some failure scenarios.

#6 (**permalink**) 04-20-2008, 10:59 AM

You can also point PSORT_DBTEMP to ramdisks. This seemed to help us.

/ramdisk/psort1/
/ramdisk/psort2/
/ramdisk/psort3/
/ramdisk/psort4/
....

then
PSORT_DBTEMP=/ramdisk/psort1:/ramdisk/psort2:/ramdisk/psort3/:/ramdisk/psort4/
....

You just make sure that the ramdisks are created when the machine comes
back on line before informix comes backup.

We use more than one directory because I thought and others concurred
that Informix would sort more in parallel with more directories. Of
course if you are running 10 use the high DS_NONPDQ_QUERY_MEM setting
as eriegger recommended.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

IDS - Running temp dbspaces on RAMDISK