Thread: [NOVICE] Adding SSD to HDD-based System

On Tue, Sep 5, 2017 at 11:26 AM, Stephen Froehlich
<s.froehlich@cablelabs.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

On Tue, Sep 5, 2017 at 1:37 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
> On Tue, Sep 5, 2017 at 11:26 AM, Stephen Froehlich
> <s.froehlich@cablelabs.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