On Thu, May 08, 2014 at 10:11:38AM +0200, Johann Spies wrote:
I understand your remark about the CPU in the light of my wrong assumption earlier, but I do not understand your remark about the RAM. The fact that temporary files of up to 250Gb are created at times during complex queries, is to me an indication of too low RAM.
If you can afford infinite RAM, then infinite RAM is great. If your working set size exceeds the memory size, then you will eventually need to deal with disk IO. At that point, maybe a bit more memory will help and maybe it will not--you'll be able to fit a little bit more working data into memory, but that won't likely radically change the performance. (If you can afford to fit *everything* you need into RAM than that's ideal, but that's not the case for most people with non-trival data sets.) What is certain is that improving the disk IO performance will improve your overall performance if you're IO bound.
(And the mere existence of temporary files isn't an indication of insufficient RAM if the system can utilize the memory more efficiently with the files than it can without them--they could contain data that isn't needed in a particular phase of a query, freeing up resources that are needed for other data in that phase.)
Mike Stone
