|
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 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:
Thank you for your answers, as always. Thanks, |
|
The temporary dbspace is used to hold:
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:
HTH 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).
(10 Feb '17, 09:24)
Vlad
Replies hidden
2
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).
(10 Feb '17, 10:09)
Mark Culp
Thank you, once again!
(12 Feb '17, 02:55)
Vlad
|
I don't know, but John certainly does...
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.
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.
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.
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).
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.