Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1djktx-0000SN-4L for pgsql-performance@arkaria.postgresql.org; Mon, 21 Aug 2017 11:29:49 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1djktw-0005lV-3E for pgsql-performance@arkaria.postgresql.org; Mon, 21 Aug 2017 11:29:48 +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 1djkry-0002VS-Kj for pgsql-performance@postgresql.org; Mon, 21 Aug 2017 11:27:46 +0000 Received: from mail-wr0-x22c.google.com ([2a00:1450:400c:c0c::22c]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1djkrs-0005Le-56 for pgsql-performance@postgresql.org; Mon, 21 Aug 2017 11:27:45 +0000 Received: by mail-wr0-x22c.google.com with SMTP id f8so60223140wrf.3 for ; Mon, 21 Aug 2017 04:27:39 -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=86ffN4bF+PWXMOLVQB6Mn8nobMTNMqY1uyHnq+xlbSY=; b=AnJfM9JPBFzrWhqV1w/jcPj4VW4ekPc2bNZQqr3dtiQAk+a5tK8Fu7aOYlNrhG3eTe ZC6l5G8al4mwMXnoUr8HpgirZOFxEl913Nj4Oefh3sNY9i8PTbFwFs7bS+bG2afEI2T/ XYzlK5fuE6yx5LpRa/4SVdA5/xid+FP4C55+Lb2Oo6G1anclOjD1Kz0q9vMAwb04+Rjz swg8Y265Z4MbYVmAWnLSbgRcwxdk+ElMzCPVWR7fse4hdL3qkTsMVQXW6tKjyJhWJ+Yp Le02dI5nYniUBD4AlkGk0F8MZW83pyLPH1sEb03iBotpXgoMlE6SBQ+SltB2Z11YwvX6 PSiA== 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=86ffN4bF+PWXMOLVQB6Mn8nobMTNMqY1uyHnq+xlbSY=; b=Ufk0Iq+LxyyTHUzktA2zZCqpA9C2rCcoNixwyGlx8YV/jDnqLf/Qmn1/2PIM08Jfqi /ES0g6AR0NVzkKjGLIzTT+4kjLCEarf0UeNiV0lFT6dqimHWVOCA/YeaMngkRC0Xl5ML x8hHaPY9MPrCyXkqbZCRIHNSrJfbaUvjwhIXipcPItM8QkSpe8xm7l5mrVmc8t7FFwWh OWKD8NowFOUuFDD+11Lcq/7HdS2V8UBpMBo7cU6Bd5dXPATDbyeeijWU1HoF5sK+za/x h96rc/XPK2S88eK6ZfFqP9Wd7b4zsHD1eYXA7Vg1j1EX5Be9BJpVL6j54z0/gT0MIDHP yk+w== X-Gm-Message-State: AHYfb5jpXaa8zl0omSKGKZ4+k8DsqSKQJLYPhI8F3nLBm5EW0uEMWr4S duDqFlwGccSDhIUDWHuVhBoxmQ11VQ== X-Received: by 10.28.236.208 with SMTP id h77mr1674711wmi.162.1503314858105; Mon, 21 Aug 2017 04:27:38 -0700 (PDT) MIME-Version: 1.0 Received: by 10.28.184.85 with HTTP; Mon, 21 Aug 2017 04:27:37 -0700 (PDT) In-Reply-To: <236952AA-596F-4526-BB84-2EDC4906454F@gmail.com> References: <88350892-C4AB-4191-9F8E-DB7CFA81F6C6@gmail.com> <00990FBB-D288-4878-8B8E-36E24F808F2C@gmail.com> <236952AA-596F-4526-BB84-2EDC4906454F@gmail.com> From: Mariel Cherkassky Date: Mon, 21 Aug 2017 14:27:37 +0300 Message-ID: Subject: Re: performance problem on big tables To: Daniel Blanch Bataller Cc: "pgsql-performance@postgresql.org" Content-Type: multipart/alternative; boundary="001a11476f181a5fbc055741c413" 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 --001a11476f181a5fbc055741c413 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable All this operation runs as part of a big transaction that I run. How can I create a dump in the oracle server and copy it to the postgresql server from a postgresql transaction ? Chopping the table is optional when I use copy, but when I use copy to remote oracle table it takes longer to create the dump. 2017-08-21 11:37 GMT+03:00 Daniel Blanch Bataller < daniel.blanch.bataller@gmail.com>: > > 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 minut= es. > > 2)second method - copy (select * from oracle_remote_table) to /tmp/dump g= enerates > 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 > max_worker_processes =3D 8 > 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 COP= Y > utility can read, then copy it to postgres server and load it. You can ev= en > 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 > : > >> 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 d= b >> 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, wi= th >> 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 t= hat >>> I`m using is xfs. I`ll add two different disks - one for the wals and o= ne >>> 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. >>>> >>> >>> > --001a11476f181a5fbc055741c413 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
All this operation runs as part of a big = transaction that I run. How can I create a dump in the oracle server and co= py it to the postgresql server from a postgresql transaction ? Chopping the= table is optional when I use copy, but when I use copy to remote oracle ta= ble it takes longer to create the dump.=C2=A0

2017-08-21 11:37 G= MT+03:00 Daniel Blanch Bataller <daniel.blanch.bataller@gma= il.com>:

El 21 ago 2017, a las 10:00, Ma= riel Cherkassky <mariel.cherkassky@gmail.com> escribi=C3=B3:

To summarize, I still have performance problems. My curre= nt situation :=C2=A0

I'm trying to copy the data of many tables in the oracle database i= nto my postgresql tables. I'm doing so by running=C2=A0insert 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 an= d mybe choose a different alternative.

1)First method -=C2=A0Insert into local_postgresql_table select * from re= mote_oracle_table=C2=A0this 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 -=C2=A0copy = (select * from oracle_remote_table) to /tmp/dump=C2=A0generates tota= l disk write of 4 M/s and actuval disk write of 100 K/s. The copy utility s= uppose to be very fast but it seems very slow.




Are you usin= g 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 i= s very fast 300 M/s.


=
You reported it was slow before. What has changed? Ho= w 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.


<= /div>

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

min_parallel_relation_size =3D 200=
MB
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 :<=
span class=3D"m_2853355547872582334gmail-pln" style=3D"margin:0px;padding:0=
px;border:0px;font-style:inherit;font-variant:inherit;font-weight:inherit;f=
ont-stretch:inherit;font-size:inherit;line-height:inherit;font-family:inher=
it;vertical-align:baseline;color:rgb(48,51,54)"> 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 c= ommands 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 chun= ks and load it in parallel as I told you before, I have done this many time= s when migrating data from oracle to postgres. ora2pg uses this method to m= igrate data from oracle to postgres too.=C2=A0
=

2017-08-20 14:00 GMT+03:00 Mariel Cherka= ssky <mariel.cherkassky@gmail.com>:
I realized something weird. When I`m preforming the co= py utility of postgresql in order to create dump from a local table in my p= ostgresql 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 th= e 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 ?=C2=A0

2017-08-20 9:39 GMT+03:00 Mariel Cherkassky <ma= riel.cherkassky@gmail.com>:
This server is dedicated to be a postgresql production database, therefor= e postgresql is the only thing the runs on the server. The fs that I`m usin= g is xfs. I`ll add two different disks - one for the wals and one for the t= emp tablespace. Regarding the disk, what size should they be considering th= at the database size is about 250G. Does 16G of ram considered little ? I i= nstalled iotop and I see that postgresql writer is writing most of the time= and above all.

I mentione= d 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 b= efore or after the operation ?=C2=A0

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



--001a11476f181a5fbc055741c413--