Postgres Pro
Downloads
Home   >   mailing lists

Re: [PERFORM] performance problem on big tables - Mailing list pgsql-performance

From Mariel Cherkassky
Subject Re: [PERFORM] performance problem on big tables
Date
Msg-id CA+t6e1nB58DtiMmNeY_Nh+wjRyT_WOFFxNJRJK=M30d4HAWJnQ@mail.gmail.com
Whole thread Raw
In response to Re: [PERFORM] performance problem on big tables  (Claudio Freire <klaussfreire@gmail.com>)
Responses Re: [PERFORM] performance problem on big tables  (Mariel Cherkassky <mariel.cherkassky@gmail.com>)
List pgsql-performance
Tree view
This server is dedicated to be a postgresql production database, therefore postgresql is the only thing the runs on the server. The fs that I`m using is xfs. I`ll add two different disks - one for the wals and one for the temp tablespace. Regarding the disk, what size should they be considering that the database size is about 250G. Does 16G of ram considered little ? I installed iotop and I see that postgresql writer is writing most of the time and above all.

I mentioned that I perform alot of insert into table select * from table. Before that I remove indexes,constraints and truncate the table. Should I run vacuum before or after the operation ? 

2017-08-17 19:37 GMT+03:00 Claudio Freire <klaussfreire@gmail.com>:
On Thu, Aug 17, 2017 at 6:00 AM, Mariel Cherkassky
<mariel.cherkassky@gmail.com> wrote:
> I checked with the storage team in the company and they saw that I have alot
> of io on the server. How should I reduce the io that the postgresql uses ?

Do you have concurrent activity on that server?

What filesystem are you using wherever the data is sitting?

If you've got concurrent fsyncs happening, some filesystems handle
that poorly. When you've got WAL and data mixed in a single disk, or
worse, filesystem, it happens often that the filesystem won't handle
the write barriers for the WAL efficiently. I/O gets intermingled with
bulk operations, and even small fsyncs will have to flush writes from
bulk operations, which makes a mess of things.

It is a very good idea, and in fact a recommended practice, to put WAL
on its own disk for that reason mainly.

With that little RAM, you'll also probably cause a lot of I/O in temp
files, so I'd also recommend setting aside another disk for a temp
tablespace so that I/O doesn't block other transactions as well.

This is all assuming you've got concurrent activity on the server. If
not, install iotop and try to see who's causing that much I/O.

pgsql-performance by date:

Previous
From: anand086
Date:
Subject: [PERFORM] Performance Issue -- "Materialize"
Next
From: Mariel Cherkassky
Date:
Subject: Re: [PERFORM] performance problem on big tables