I am intrigued to use the Dataram Ramdisk for temp files of the database for speed up. Does anyone is using this or a smilar product in a productive environment? Is it save to do so? asked 02 Mar '11, 10:19 Martin |
The server should really only be writing to the temp file when it absolutely must do so because the cache is full. You're probably better off taking the memory you would reserve for your RAM disk and just add it to your cache size. You also run the risk of running out of space on your RAM disk if the server decides to write, for example, a very large intermediate result set. If you run out of space, the server will either abort the query or report a fatal error. -john. answered 02 Mar '11, 20:42 John Smirnios That is interesting, I thought, that all temporary tables will go into the tempfile independently of the cache size? 1
Temporary tables are much like base table pages in cache. If dirty, they will only get written out if they are forced out by demand for other pages that are not in cache. In fact, non-temp pages are more likely to get written out than temp pages because the background idle IO starts writing out non-temp pages in anticipation of an upcoming checkpoint. Temporary pages do not need to be written out at checkpoint time. If a temp table is created, used, then later dropped and no other query needed the memory, nothing will ever be written to disk for that temp table. 1
I should also add that if the cache space occupied by the temp table is needed by another query, only enough pages to satisfy demand will be written out to the temp file. It is not the case that would would write then entire table to disk just because a query needed a little bit more memory. |