Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1djiFJ-0007UR-Ae for pgsql-performance@arkaria.postgresql.org; Mon, 21 Aug 2017 08:39:41 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1djiFI-000716-TG for pgsql-performance@arkaria.postgresql.org; Mon, 21 Aug 2017 08:39:40 +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 1djiDT-0003t4-Tr for pgsql-performance@postgresql.org; Mon, 21 Aug 2017 08:37:48 +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 1djiDQ-0001OD-It for pgsql-performance@postgresql.org; Mon, 21 Aug 2017 08:37:47 +0000 Received: by mail-wr0-x235.google.com with SMTP id p8so28425590wrf.5 for ; Mon, 21 Aug 2017 01:37:44 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=from:message-id:mime-version:subject:date:in-reply-to:cc:to :references; bh=kEpVg5+UapEc6TL2bmdNMAQUJ+pJobSxX9oaDQfBPoQ=; b=ale/Q7g1scVi1UpmIOOiytrM3femfYkV1nvK7VnZLaMxbBL2NoLuBzzDchyldhOG4L o7koAXIKzxKLxHZ1DyT9sktqU5SwRoonHJt1eFHbCx8mpXu3HIzEoNA5HpBYbhmoOD2G rmQ0OANqC5j06Ul+WaWwJJcf5dD2Oa2tKfXniO0DoGj/VUIHxLIEU4/Sz7Yemv2Q5k4r 4a1F3h409hWVzxYrBX4erTKTVOYI/YzuEi75V1yh7z7d7fo9Paljx9MkwSLF3d26hOoi Ykr0/k6iEsBypvQJsmXYQ7LA51XAOkbMJe+wDHOhVIvvx4jY22W7OvWF40OBOq1pyTio 8EIQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:from:message-id:mime-version:subject:date :in-reply-to:cc:to:references; bh=kEpVg5+UapEc6TL2bmdNMAQUJ+pJobSxX9oaDQfBPoQ=; b=RnsrYrCu7odSkmGFRmxJyPAjLt/4Uv1CeLOCF1Ykrn/wRodXSXfHS5RmoDen9TwsXh bUSUx8OL4Gk3PrTA3b1FI+sRlW1846iLj16snev6mAiyQkc54fneRdOO6uBPcLeUjIZb D165bVb1s3OHUjC+caUW7EX8v/14AyfHRFgy7b5Nii6LGizsjOSWyUsomIqhzybWLLYJ AYLSEpM6afgdQ0694BGcZAplqhs+HUl0mYEJb0gJL44jyW933ruuHowZv4y2B37THhrv px1doMxR5iVACju/iCHLtIU3t9e/vZUJP58wTHtlrqV4IvjtLmI91JTIUhq9awpyMKh3 CNpA== X-Gm-Message-State: AHYfb5iaq6VaxvB7SjLLvqPX+N1HRNrq/b+9yL1Q7bgkj5JtXJwibfdC iWuP6ripQJxAzA== X-Received: by 10.28.176.4 with SMTP id z4mr5893459wme.15.1503304663132; Mon, 21 Aug 2017 01:37:43 -0700 (PDT) Received: from [192.168.1.33] (213.red-81-40-123.staticip.rima-tde.net. [81.40.123.213]) by smtp.gmail.com with ESMTPSA id b130sm6698691wme.28.2017.08.21.01.37.41 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Mon, 21 Aug 2017 01:37:42 -0700 (PDT) From: Daniel Blanch Bataller Message-Id: <236952AA-596F-4526-BB84-2EDC4906454F@gmail.com> Content-Type: multipart/alternative; boundary="Apple-Mail=_BD6E5210-40B8-4987-90BB-15E4A962AFB8" Mime-Version: 1.0 (Mac OS X Mail 10.3 \(3273\)) Subject: Re: performance problem on big tables Date: Mon, 21 Aug 2017 10:37:40 +0200 In-Reply-To: Cc: "pgsql-performance@postgresql.org" To: Mariel Cherkassky References: <88350892-C4AB-4191-9F8E-DB7CFA81F6C6@gmail.com> <00990FBB-D288-4878-8B8E-36E24F808F2C@gmail.com> X-Mailer: Apple Mail (2.3273) 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 --Apple-Mail=_BD6E5210-40B8-4987-90BB-15E4A962AFB8 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 > El 21 ago 2017, a las 10:00, Mariel Cherkassky = escribi=C3=B3: >=20 > To summarize, I still have performance problems. My current situation = :=20 > I'm trying to copy the data of many tables in the oracle database into = my postgresql tables. I'm doing so by running insert into = local_postgresql_temp select * from remote_oracle_table. The performance = of this operation are very slow and I tried to check the reason for that = and mybe choose a different alternative. >=20 > 1)First method - Insert into local_postgresql_table select * from = remote_oracle_table this generated total disk write of 7 M/s and actual = disk write of 4 M/s(iotop). For 32G table it took me 2 hours and 30 = minutes. >=20 > 2)second method - copy (select * from oracle_remote_table) to = /tmp/dump generates total disk write of 4 M/s and actuval disk write of = 100 K/s. The copy utility suppose to be very fast but it seems very = slow. >=20 >=20 Are you using a FDW to access oracle server and then dump it using copy? = This is going to be slow, FDW isn't fast. > -When I run copy from the local dump, the reading is very fast 300 = M/s. >=20 >=20 You reported it was slow before. What has changed? How much does it take = to load the 32G table then? > -I created a 32G file on the oracle server and used scp to copy it and = it took me a few minutes. >=20 >=20 > -The wals directory is located on a different file system. The = parameters I assigned : >=20 > min_parallel_relation_size =3D 200MB > max_parallel_workers_per_gather =3D 5=20 > max_worker_processes =3D 8=20 > effective_cache_size =3D 12GB > work_mem =3D 128MB > maintenance_work_mem =3D 4GB > shared_buffers =3D 2000MB > RAM : 16G > CPU CORES : 8 > HOW can I increase the writes ? How can I get the data faster from the = oracle database to my postgresql database? >=20 >=20 Extract the table to a file in the oracle server in a format that the = COPY utility can read, then copy it to postgres server and load it. You = can even pipe commands and do it in a single step. This is what I meant when I said that COPY is much faster than any thing = else. To make it even faster, if I/O is not your bottleneck, you can = chop the table in chunks and load it in parallel as I told you before, I = have done this many times when migrating data from oracle to postgres. = ora2pg uses this method to migrate data from oracle to postgres too.=20 >=20 > 2017-08-20 14:00 GMT+03:00 Mariel Cherkassky = >: > 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 ?=20 >=20 > 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. >=20 > 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 ?=20 >=20 > 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 ? >=20 > Do you have concurrent activity on that server? >=20 > What filesystem are you using wherever the data is sitting? >=20 > 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. >=20 > It is a very good idea, and in fact a recommended practice, to put WAL > on its own disk for that reason mainly. >=20 > 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. >=20 > 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. >=20 --Apple-Mail=_BD6E5210-40B8-4987-90BB-15E4A962AFB8 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8
El 21 ago 2017, a las 10:00, Mariel Cherkassky <mariel.cherkassky@gmail.com> escribi=C3=B3:

To summarize, I still have = performance problems. My current situation : 

I'm trying to copy the data of many tables in = the oracle database into my postgresql tables. I'm doing so by = running insert into = local_postgresql_temp select * from remote_oracle_table. The = performance of this operation are very slow and I tried to check the = reason for that and mybe choose a different alternative.

1)First method - Insert into = local_postgresql_table select * from = remote_oracle_table this generated total disk write of 7 M/s = and actual disk write of 4 M/s(iotop). For 32G table it took me 2 hours = and 30 minutes.

2)second method - copy (select * from = oracle_remote_table) to /tmp/dump generates total disk write = of 4 M/s and actuval disk write of 100 K/s. The copy utility suppose to = be very fast but it seems very slow.




Are you using a FDW to = access oracle server and then dump it using copy? This is going to be = slow, FDW isn't fast.


-When I run copy from the local dump, the = reading is very fast 300 M/s.



You reported it was slow before. What has changed? = How much does it take to load the 32G table then?


-I created a 32G file on the oracle server and = used scp to copy it and it took me a few minutes.


-The wals directory is located on a different = file system. The parameters I assigned :

min_parallel_relation_size =3D 200MB
max_parallel_workers_per_gather =3D 5=20
max_worker_processes =3D 8=20
effective_cache_size =3D 12GB
work_mem =3D 128MB
maintenance_work_mem =3D 4GB
shared_buffers =3D 2000MB
RAM : 16G
CPU CORES : 8

HOW can I increase the writes ? How can I get = the data faster from the oracle database to my postgresql = database?




Extract the table to a = file in the oracle server in a format that the COPY utility can read, = then copy it to postgres server and load it. You can even pipe commands = and do it in a single step.

This is = what I meant when I said that COPY is much faster than any thing else. = To make it even faster, if I/O is not your bottleneck, you can chop the = table in chunks and load it in parallel as I told you before, I have = done this many times when migrating data from oracle to postgres. ora2pg = uses this method to migrate data from oracle to postgres = too. 


2017-08-20 14:00 GMT+03:00 Mariel = Cherkassky <mariel.cherkassky@gmail.com>:
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 <mariel.cherkassky@gmail.com>:
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.


= --Apple-Mail=_BD6E5210-40B8-4987-90BB-15E4A962AFB8--