Postgres Pro
Downloads
Home   >   mailing lists

Thread: tablespace to benefit from ssd ?

tablespace to benefit from ssd ?

From
Nicolas PARIS
Date:
Hi

I have both hdd and ssd disk on the postgres server. The cluster is
right now created on the hdd only. I am considering using a tablespace
to put some highly used postgres object on the ssd disk. Of course the
ssd is small compared to the hdd, and I need to choose carefully what
objects are stored on that side.

I am wondering what kind of object (indexes, data) would benefit from
ssd. The database primary/foreign keys are highly used and there is
almost no sequencial scan. However the server has a large amount of ram
memory and I suspect all of those indexes are already cached in ram.

I have read that tablespaces introduce overhead of maintenance and
introduce complication for replication. But on the other hand I have
this ssd disk ready for something.

Any recommandation ?

-- 
nicolas paris

Re: tablespace to benefit from ssd ?

From
Sebastiaan Mannem
Date:
Unless this is about reads exclusively I would start with putting wal on ssd. 
What you might also do, is create separate filesystems (lvm). You can then keep track of io with iostat per filesystem and see what would benefit most. And see storage size usage also. 
And you could use lvm to move filesystems to and from ssd hot. So just downtime once.

Please share your end findings in this thread too.

Op wo 19 feb. 2020 om 04:42 schreef Nicolas PARIS <nicolas.paris@riseup.net>
Hi

I have both hdd and ssd disk on the postgres server. The cluster is
right now created on the hdd only. I am considering using a tablespace
to put some highly used postgres object on the ssd disk. Of course the
ssd is small compared to the hdd, and I need to choose carefully what
objects are stored on that side.

I am wondering what kind of object (indexes, data) would benefit from
ssd. The database primary/foreign keys are highly used and there is
almost no sequencial scan. However the server has a large amount of ram
memory and I suspect all of those indexes are already cached in ram.

I have read that tablespaces introduce overhead of maintenance and
introduce complication for replication. But on the other hand I have
this ssd disk ready for something.

Any recommandation ?

--
nicolas paris


--

Re: tablespace to benefit from ssd ?

From
Laurenz Albe
Date:
On Wed, 2020-02-19 at 05:42 +0100, Nicolas PARIS wrote:
> I have both hdd and ssd disk on the postgres server. The cluster is
> right now created on the hdd only. I am considering using a tablespace
> to put some highly used postgres object on the ssd disk. Of course the
> ssd is small compared to the hdd, and I need to choose carefully what
> objects are stored on that side.
> 
> I am wondering what kind of object (indexes, data) would benefit from
> ssd. The database primary/foreign keys are highly used and there is
> almost no sequencial scan. However the server has a large amount of ram
> memory and I suspect all of those indexes are already cached in ram.
> 
> I have read that tablespaces introduce overhead of maintenance and
> introduce complication for replication. But on the other hand I have
> this ssd disk ready for something.
> 
> Any recommandation ?

Put "pg_stat_statements" into "shared_preload_libraries" and restart the server.

Set "track_io_timing" to on.

Let your workload run for at least a day.

Install the "pg_stat_statements" extension and run

  SELECT blk_read_time, query
  FROM pg_stat_statements
  ORDER BY blk_read_time DESC LIMIT 20;

That will give you the 20 queries that spent the most time reading from I/O.

Examine those queries with EXPLAIN (ANALYZE, BUFFERS) and see which tables or
indexes cause the I/O.

Then you have a list of candidates for the fast tablespace.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: tablespace to benefit from ssd ?

From
Justin Pryzby
Date:
On Wed, Feb 19, 2020 at 05:42:41AM +0100, Nicolas PARIS wrote:
> Hi
> 
> I have both hdd and ssd disk on the postgres server. The cluster is
> right now created on the hdd only. I am considering using a tablespace
> to put some highly used postgres object on the ssd disk. Of course the
> ssd is small compared to the hdd, and I need to choose carefully what
> objects are stored on that side.
> 
> I am wondering what kind of object (indexes, data) would benefit from
> ssd. The database primary/foreign keys are highly used and there is
> almost no sequencial scan. However the server has a large amount of ram
> memory and I suspect all of those indexes are already cached in ram.
> 
> I have read that tablespaces introduce overhead of maintenance and
> introduce complication for replication. But on the other hand I have
> this ssd disk ready for something.

To start with, you can:
ALTER SYSTEM SET temp_tablespaces='ssd';

That will improve speed of sorts which spill to disk (if any).

+1 to using LVM for purposes of instrumentation.

You can also:
ALTER TABLESPACE ssd SET (random_page_cost=1.0);

It'd be difficult to suggest anything further without knowing about your
workload or performance goals or issues.

-- 
Justin

Re: tablespace to benefit from ssd ?

From
Jeff Janes
Date:
On Tue, Feb 18, 2020, 11:42 PM Nicolas PARIS <nicolas.paris@riseup.net> wrote:
 However the server has a large amount of ram
memory and I suspect all of those indexes are already cached in ram.

Then there may be no benefit to be had.


I have read that tablespaces introduce overhead of maintenance and
introduce complication for replication.

Yes, they are a nuisance for the humans who need to document, maintain, configure, etc. And they can induce administrators into making mistakes which can prolong outages or cause data loss.

But on the other hand I have
this ssd disk ready for something.

That isn't a good reason.  Unless your users are complaining, or you think they will be soon as things scale up, or you think they would be complaining of they weren't too apathetic to, then I would make no change that adds complexity just because the hardware exists.

But I would turn on track_io_timing, and load pg_stat_statements, and probably set up auto_explain.  That way when problems do arrive, you will be prepared to tackle them with empirical data.

Cheers,

Jeff