Re: Specifications for a new server

On Thu, May 8, 2014 at 1:11 AM, Johann Spies <johann(dot)spies(at)gmail(dot)com> wrote:

>
>
>>
>> So my questions:
>>>
>>> 1. Will the SSD's in this case be worth the cost?
>>> 2. What will the best way to utilize them in the system?
>>>
>>
>> The best way to utilize them would probably be to spend less on the CPU
>> and RAM and more on the storage, and use SSD either for all of the storage
>> or for specific items that have a high level of I/O (such as the indexes).
>> Can't be more specific than that without a lot more information about the
>> database, how it is utilized, and what's actually slow.
>>
>>
>
> 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.
>

Are these PostgreSQL temp files or other temp files? PostgreSQL doesn't
suppress the use of temp files just because you have a lot of RAM. You
would also have to set work_mem to a very large setting, probably
inappropriately large, and even that might not work because there other
limits on how much memory PostgreSQL can use for any given operation (for
example, you can't sort more than 2**32 (or 2**31?) tuples in memory, no
matter how much memory you have, and in older versions even less than
that). But that doesn't mean the RAM is not useful. The OS can use the
RAM to buffer the temp files so that they might not ever see the disk, or
might not be read from disk because they are still in memory.

SSD is probably wasted on temp files, as they are designed to be accessed
mostly sequentially.

Cheers,

Jeff