Re: Adding SSD to HDD-based System

On Tue, Sep 5, 2017 at 1:37 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
> On Tue, Sep 5, 2017 at 11:26 AM, Stephen Froehlich
> <s(dot)froehlich(at)cablelabs(dot)com> wrote:
>> We’re likely adding a 1TB or 500 GB SSD to our 6TB HDD-based server with the
>> main aim of improving both write performance and also virtual memory
>> performance for people running python on the box. (We’re thinking a Samsung
>> EVO for this, and the solution only needs to last another couple of years.)
>>
>> I’m having trouble finding good documentation on what temp files other than
>> the WAL could be located on the SSD for postgresql and how much room we
>> should allocate for that. My largest writes are about 14 million lines in a
>> go.
>>
>> Also, I could use a pointer to how to configure the WAL to go to a specific
>> drive.
>
> On linux moving pg_xlog or pg_tmp, it's as simple as taking down the
> server, moving the data, and doing symlinks.
>
> For other usages, you can use the tablespace system to move particular
> tables and/or indexes. For the EVO, you will get a lot of benefit
> from random reads of large tables. Write activity however you may or
> may not see as much benefit as you're hoping; the EVO is not an
> enterprise class drive and the consumer class drives are generally not
> designed for high rates of writing in the database pattern (or at
> least not in a sustained way).
>
> If you are having issues with write performance, I would strongly
> consider the intel s3500 line of drives as having proven performance
> and reliability and decent (but not fantastic) write performance at a
> reasonable cost.
>
> merlin

I should have mentioned, there's also temp_tablespaces. Honestly, you
ought to review this entire chapter:

https://www.postgresql.org/docs/9.6/static/manage-ag-tablespaces.html

merlin