Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1djJtp-0001xC-W7 for pgsql-performance@arkaria.postgresql.org; Sun, 20 Aug 2017 06:39:54 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1djJto-0007Ca-4r for pgsql-performance@arkaria.postgresql.org; Sun, 20 Aug 2017 06:39:52 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1djJtn-0007C5-Gb for pgsql-performance@postgresql.org; Sun, 20 Aug 2017 06:39:51 +0000 Received: from mail-wr0-x235.google.com ([2a00:1450:400c:c0c::235]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1djJtj-0007v4-Lw for pgsql-performance@postgresql.org; Sun, 20 Aug 2017 06:39:50 +0000 Received: by mail-wr0-x235.google.com with SMTP id p14so5947038wrg.1 for ; Sat, 19 Aug 2017 23:39:47 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc; bh=PNf10Q2Zn2+7BZzuBjUw4INWX5/Js83gYPYlNuE3NjQ=; b=hOkTsZxbEhfxC2fw7axvKExsnmNvTxo0Nors5QIrpPZx5Pzh4c5fm4J2M7KqO26VMz 5BWbRZgzFY0wyTHltmdbdp/8HLM1W3hBjcMiyIVbGhI5n7UWk8Jg3zNOVVJMQE+ZkGGt C2C3yMDlkjWCLUX82SI7XUw3fWdCqDiVet9gDX5Gp41ytHPdNVhGbaduFXeE4YcfQ+Cg epZgnqM1vd5SFVghxZHPam6OUJvudBOvpwrohTRkn1/4L20GWpvRiAArrCImxyn/8yOA bV0ivCGgvqnGzzqbWR+qlCP71z2yqv9SIdub11q+EkssuLELzQY1BG9+dvWuRogXYsft 73PQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:in-reply-to:references:from:date :message-id:subject:to:cc; bh=PNf10Q2Zn2+7BZzuBjUw4INWX5/Js83gYPYlNuE3NjQ=; b=O+1ZKn/4tzsi2IJ2nIx2ldTIUZw/y5lwuV+KUTlesrcZpaC6D2169NgdEjUGUerY51 77phLJoDeLj9YsN7AKR6KMuLusQIua01iMzAphU1T8+Ye/tAZJ/wSGmMl08g6y4quD9d ubZwCB6jaaKPmTWg1Y/lFJrkkKyU0bUb1ScW5AYLKQXJrOPS13exNOMNF24AjqugNXsW IOR3RgDvWGwCTvagba54aCg3FOBJ9p6Gb+E/IfuzK2NhUg5rOjE0pSSsKn3lsO1/k2gB wJYtwmME3JtlF+xWgCrVxSjK8KxWmeH8pvNf+CGkbaHqTVgR+VuZED5nC88SRNRG2Jq6 SEVg== X-Gm-Message-State: AHYfb5gVjGXeyu3Xhvd5XT8LhkKrqGjaeLt2XqhCk5wILCroV8zjNWy1 FT0KlSASpEDSunUFH9Q4Jg8Q9kNuZw== X-Received: by 10.223.139.23 with SMTP id n23mr8955166wra.249.1503211185854; Sat, 19 Aug 2017 23:39:45 -0700 (PDT) MIME-Version: 1.0 Received: by 10.28.184.85 with HTTP; Sat, 19 Aug 2017 23:39:45 -0700 (PDT) In-Reply-To: References: <88350892-C4AB-4191-9F8E-DB7CFA81F6C6@gmail.com> <00990FBB-D288-4878-8B8E-36E24F808F2C@gmail.com> From: Mariel Cherkassky Date: Sun, 20 Aug 2017 09:39:45 +0300 Message-ID: Subject: Re: performance problem on big tables To: Claudio Freire Cc: Daniel Blanch Bataller , Scott Marlowe , "pgsql-performance@postgresql.org" Content-Type: multipart/alternative; boundary="f403045eae1ac15e04055729a06a" List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --f403045eae1ac15e04055729a06a Content-Type: text/plain; charset="UTF-8" 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 : > On Thu, Aug 17, 2017 at 6:00 AM, Mariel Cherkassky > 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. > --f403045eae1ac15e04055729a06a Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
This server is dedicated to be a postgres= ql 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 - on= e for the wals and one for the temp tablespace. Regarding the disk, what si= ze should they be considering that the database size is about 250G. Does 16= G of ram considered little ? I installed iotop and I see that postgresql wr= iter is writing most of the time and above all.

<= /div>
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 ?=C2=A0
<= /div>

2017-08-17 19:37 GMT+03:00 Claudio Freire <klaussfreire@gmail.co= m>:
On T= hu, 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 hav= e alot
> of io on the server. How should I reduce the io that the postgresql us= es ?

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.

--f403045eae1ac15e04055729a06a--