Move TEMPDB to RAM - any theoretical improvements

[Vlad]

Hello everybody,

I have a question that came to my mind when I was thinking about the universe and how things are implemented in the DB servers. I know that SA has so-called temporary DB space that stores some temporary data. I think it should be TEMPORARY DB objects such as tables, stored procedures etc. There is a URL (ALTER DBSPACE statement) that says

Preallocating space to the temporary dbspace of a database can improve performance during execution complex queries that use large work tables.

Please correct me, if I am wrong, but it means, SA needs TEMP even for queries (not against temporary tables). So, here are the questions:

• Would it be a good practice to use RAM-Drive to store temporary DB space there instead of the %TEMP% folder (Windows)? (I admit - it is my headache to foresee the disk size and what to do, when it is empty).
• Since on *nix we have /tmp (that might be either stored in RAM or in the swap partition), can we surely tell that SA tries to allocate TEMPDB in RAM if it can?

Thank you for your answers, as always.

Thanks,
Vlad

[VolkerBarth]

I don't know, but John certainly does...

[Vlad]

Oh, I feel dumb, and my Google skills betrayed me :( Ok, I will wait for few days if Sybase guys can add the feedback and then close the question.

Sad-sad-sad. Sorry.

[VolkerBarth]

FWIW, I did not do a search at all, I just had noticed that other FAQ in the "Related questions" list, which is quite helpful IMHO... (And note, this is not SO, nobody has ever been critized for asking "duplicate questions" here.)

Besides that, I'd think your question is a good one, and the temp space is used by other things as temp tables, too, so I'm curious whether the SQL Anywhere engineers have more to tell about that topic.

[Breck_Carter]

AFAIK SQL Anywhere works very hard to use the RAM cache for temporary data before using the physical file, so the place to start is to allocate as much RAM as possible to the SQL Anywhere cache... that is probably a better use for RAM than taking space away from the cache and giving it to a RAM drive, especially since the RAM drive might run out of space long before the disk drive version of the temporary file.

FWIW I have not heard of anyone using RAM drives for anything, for many years.

Solid-state storage, now that's another story... is that what you are talking about? The term "RAM drive" generally applies to something else.

[Vlad]

To give some background - I saw a big DB on one of customer's machines (~128 GB the database file, 104 GB RAM, 80 GB cache, less than 1 GB - TEMPDB in Windows/Temp).

I had an idea that if the temporary DB space doesn't exceed ~300-500 MB after working many months with these settings, it might make sense to reserve 1 GB of RAM for TEMPDB instead of spending it to the cache (the cache is the thing that usually less than the DB file, but TEMPDB didn't shrink).

[Vlad]

No, I was talking about the HDD in RAM (not SSD) to reduce I/O as much as possible. DCX says that TEMPDB is freed when the server is down (this is true) and thus no persistence or reliable storage is required.

[MarkCulp · Accepted solution]

The temporary dbspace is used to hold:

• Temporary table data that needs to be flushed out from the cache. I.e. it is only written to the temp dbspace when the cache page is needed for something else.
• Intermediate query results. Like above, these pages will only be written to the temp dbspace when they do not completely fit into memory and/or the cache page is needed for something else (that is needing a cache page).
• database server heap pages. The temp dbspace acts as a backing store for server internal data structures. If a server heap page is not in active use then the server can pitch it out to the temp dbspace if some other process needs a cache page.

So the "short" answer is: If you have "extra" RAM that you can spare then you are likely better off allocating the RAM to the database cache rather than making a RAM disk and putting your temp space on that disk. There are several reasons for saying this, and here are a few:

• The database server can better utilize the memory when it is in cache: the extra pages can not only be used to store intermediate query results (as mentioned in the question) but can also be used to hold database pages; and access to a cache page only takes a few CPU cycles whereas access to a temp page takes milliseconds (due to all of the extra code required to do a disk read).
• If the temp space is located in a RAM disk then it is likely limited to a smaller size (i.e. the amount of RAM allocated to the disk), and if your temp space gets full then the database server will not be happy!

HTH

[Vlad · Accepted solution]

Your answer is interesting, and helpful. I appreciate this. If you don't mind, please take a look at my comment above. I think you have answered it already, but just in case if this will be helpful to complete this thread.

I will accept your answer, and I see that in my case I should increase the cache size (since I have ~ 20GB left) and better move the temporary DB space to SSD (if its size is relatively high. Probably SA uses it often).

[MarkCulp · Accepted solution]

There will always be exceptions, but in general terms you are correct: add the memory to your cache and put your temp dbspace on an SSD (if you have an SSD on your system).

[Vlad · Accepted solution]

Thank you, once again!