From: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> |
---|---|
To: | Nicolas PARIS <nicolas(dot)paris(at)riseup(dot)net>, "pgsql-performance(at)lists(dot)postgresql(dot)org" <pgsql-performance(at)lists(dot)postgresql(dot)org> |
Subject: | Re: tablespace to benefit from ssd ? |
Date: | 2020-02-19 10:48:38 |
Message-ID: | 167f5d4ec9c3cc46c78300d3db323e0b38883a67.camel@cybertec.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Lars Aksel Opsahl | 2020-02-19 10:49:14 | Re: SubtransControlLock and performance problems |
Previous Message | Sebastiaan Mannem | 2020-02-19 08:02:08 | Re: tablespace to benefit from ssd ? |