Message-ID: <167f5d4ec9c3cc46c78300d3db323e0b38883a67.camel@cybertec.at>
Subject: Re: tablespace to benefit from ssd ?
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Nicolas PARIS <nicolas.paris@riseup.net>,
	"pgsql-performance@lists.postgresql.org"
	 <pgsql-performance@lists.postgresql.org>
Date: Wed, 19 Feb 2020 11:48:38 +0100
In-Reply-To: <87ftf7fari.fsf@riseup.net>
References: <87ftf7fari.fsf@riseup.net>
Content-Type: text/plain; charset="UTF-8"
User-Agent: Evolution 3.32.5 (3.32.5-1.fc30) 
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Precedence: bulk

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