Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1djO0G-0004d4-Mj for pgsql-performance@arkaria.postgresql.org; Sun, 20 Aug 2017 11:02:48 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1djO0F-0008Bw-DO for pgsql-performance@arkaria.postgresql.org; Sun, 20 Aug 2017 11:02:47 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1djNyT-00055u-Fd for pgsql-performance@postgresql.org; Sun, 20 Aug 2017 11:00:57 +0000 Received: from mail-wr0-x22a.google.com ([2a00:1450:400c:c0c::22a]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1djNyQ-0005GY-3M for pgsql-performance@postgresql.org; Sun, 20 Aug 2017 11:00:55 +0000 Received: by mail-wr0-x22a.google.com with SMTP id k46so1340307wre.2 for ; Sun, 20 Aug 2017 04:00:53 -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=Beg2lp9qDAdGRzNvwFq2ijSoraR+upeR3ADi0xuN+wY=; b=glaxBf9RmKw8STpEVTIAybY2UH7eVteFkq9zd7kZgBUJhGjiu+uoYAosSjyMTZo+Hq hGiPMTMIjK77YLVgEQ2d/qyYIF4xvkZIyOLOMwCxrpAYvSqPKBhwE0xVRchBPFdPPslC ruc0fCI/Rcabd+s3auSQUjDRcG0kc/oQLV629gQrkUmnluRhxmaQgOtDwB+P7rgfmLy5 bp71wJ9ls25Bb4igU1YQme6rLyPyxkBGdAMZZmXnpxpaejIaI5eRORVpR8ngSy3RJmC2 1vifMEzVrXW+gVG/yKXN4Y/avKMJsX+dbdxfdKc0aTPn9eAnf31hSUVbTEbb1t/ZyFAM NvKw== 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=Beg2lp9qDAdGRzNvwFq2ijSoraR+upeR3ADi0xuN+wY=; b=N+xaj/LcnjHvxfwEHkUgArBVaCY7nn+ut3bx+CgrxhpEhgeHoLqUePoq/qEUzugBpK 2NkG/hWWX5WqSYodGqIVVcSTqOxZnO0fQ4WkITiaRNru8dNQBivewLq372HHMMlluG18 g3fv0ZkQMLuNf3yAsBjkBu/Hq+wHXEQGnls1TiUPauMooF1Qyklz1qXz129GTtgIL5pD uPo48ZqpL3RLB8DIXUxMIjf/hqQZ7E704Yjn4ChCn7ZTBd1IN9aGPfIqhNKGHkttV7Pi nhJNz0Uq3l6fMJK8tvUg/XAUXEPTsnPl77dHBw6YyPkD3nPhZEu323CsiwzNb+OdVQCM yHog== X-Gm-Message-State: AHYfb5hA5UeFu8t+QZZn3V/lKYXAiQXP67/ouNm0yf8++k6wUMpx0yzx vwIik8lt7x9LEwaPMnkfp/uyMugcOw== X-Received: by 10.223.174.242 with SMTP id y105mr10182275wrc.262.1503226852102; Sun, 20 Aug 2017 04:00:52 -0700 (PDT) MIME-Version: 1.0 Received: by 10.28.184.85 with HTTP; Sun, 20 Aug 2017 04:00:51 -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 14:00:51 +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="001a11400e9c89573005572d462f" 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 --001a11400e9c89573005572d462f Content-Type: text/plain; charset="UTF-8" I realized something weird. When I`m preforming the copy utility of postgresql in order to create dump from a local table in my postgresql db it takes for 32G table 20 minutes. When I try to use copy for a foregin table (on oracle database) It takes more than 2 hours.. During the copy operation from the foreign table I dont see alot of write operations, with iotop i see that its writes 3 M/s. What else I can check ? 2017-08-20 9:39 GMT+03:00 Mariel Cherkassky : > 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. >> > > --001a11400e9c89573005572d462f Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
I realized something weird. When I`m pref= orming the copy utility of postgresql in order to create dump from a local = table in my postgresql db it takes for 32G table 20 minutes. When I try to = use copy for a foregin table (on oracle database) It takes more than 2 hour= s.. During the copy operation from the foreign table I dont see alot of wri= te operations, with iotop i see that its writes 3 M/s. What else I can chec= k ?=C2=A0

2017-08-20 9:39 GMT+03:00 Mariel Cherkassky <mariel.cherkassky@gmail.com>:
This server is dedicated to be a postgresql production database, t= herefore 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 fo= r the temp tablespace. Regarding the disk, what size should they be conside= ring that the database size is about 250G. Does 16G of ram considered littl= e ? I installed iotop and I see that postgresql writer is writing most of t= he time and above all.

I m= entioned that I perform alot of insert into table select * from table. Befo= re that I remove indexes,constraints and truncate the table. Should I run v= acuum before or after the operation ?=C2=A0

2017-08-17 19:37 GMT+03:00 Claudio Freire <klauss= freire@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 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.

--001a11400e9c89573005572d462f--