Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1diEHa-0004mE-Qj for pgsql-performance@arkaria.postgresql.org; Thu, 17 Aug 2017 06:27:55 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1diEHZ-0000Gf-9x for pgsql-performance@arkaria.postgresql.org; Thu, 17 Aug 2017 06:27:53 +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 1diEFb-0005Fj-0m for pgsql-performance@postgresql.org; Thu, 17 Aug 2017 06:25:51 +0000 Received: from mail-wr0-x22d.google.com ([2a00:1450:400c:c0c::22d]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1diEFL-0002sE-Uj for pgsql-performance@postgresql.org; Thu, 17 Aug 2017 06:25:49 +0000 Received: by mail-wr0-x22d.google.com with SMTP id z91so24912831wrc.4 for ; Wed, 16 Aug 2017 23:25:35 -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=MXQ6msVl+xdstdN5RqxyRvhJ+YexU/ah60coFiYIItw=; b=Bzjk7FqT05aa3ZliwyJMUW+ncfBxEnrj8nEg5eBixi4RbvFdOZUrp3DQXhY+p77TlB cmc3kR+UC6gdC8CPxvsV0S7m3mSJ5bq7ZIdQx9muXDtZhfiwM9qgTui77C5tYRaZFUyo KX+Ob9sJgYKmd0r8XTMHfChOgJMqpqNfnc8/z4NuiIOHVxyFQKxagrzadUI238/Eu19O svN514cW8GUCT6UkSnf3kr8V8e//QCKTU7y6LDjO7FWmBC/+mqfGr2beBOVuLvZI7Bni Mwz+iWiapoZ/L6DeEuGXYyIVVyw2ASo48KYOd++N2ctEYJ1mcyekoJb4VeaYG8tdeXZ4 BPjw== 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=MXQ6msVl+xdstdN5RqxyRvhJ+YexU/ah60coFiYIItw=; b=AcfN859S80sk0Y4E1exwLSA4L43tP1mSCO6z6UMLLa24GhdkvEckcfdodY4Fg7o0yO rRsNFwk3YxDlYVK6LtnB9ruDABQRcyUJHcQWAbBN1RnPRZi4ioAB1AotQOVVF4C6MDK+ rc5PwP1lZNSxTpqoE6FJySoMKwvUMyX+t+Ge2QfNlDV8u1Q0WZP5H06haqQd1zVArjPy sC0itpeIjdqbF4uxn7HXSjTEuzY1NYbE12ktDkQkNEbvrvbunOy+xq1zmG5X36o2gP36 /4ChCwTiDFJZKn+vE8M6rxEkpOTTi983cbOb8DhC3ZE5Sasbf/IyrlUP69eZR0skDixu ZVWw== X-Gm-Message-State: AHYfb5iSwy8CmGAQLzbDcQqXQjUJCYA2Q0uPSqj0Yzu8HmXjkhEd6tvB Kj6CN8AV+lKSANXqapzvLGgoO5cXXw== X-Received: by 10.28.175.8 with SMTP id y8mr466432wme.42.1502951133712; Wed, 16 Aug 2017 23:25:33 -0700 (PDT) MIME-Version: 1.0 Received: by 10.28.184.85 with HTTP; Wed, 16 Aug 2017 23:25:32 -0700 (PDT) In-Reply-To: References: <88350892-C4AB-4191-9F8E-DB7CFA81F6C6@gmail.com> <00990FBB-D288-4878-8B8E-36E24F808F2C@gmail.com> From: Mariel Cherkassky Date: Thu, 17 Aug 2017 09:25:32 +0300 Message-ID: Subject: Re: performance problem on big tables To: Daniel Blanch Bataller Cc: Scott Marlowe , "pgsql-performance@postgresql.org" Content-Type: multipart/alternative; boundary="001a11444ab270998e0556ed14f6" 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 --001a11444ab270998e0556ed14f6 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hi Daniel, I already tried to set the destination table to unlogged - it improved the performance slightly. Is there a way to make sure that I/O is the problem ? 2017-08-17 0:46 GMT+03:00 Daniel Blanch Bataller < daniel.blanch.bataller@gmail.com>: > Seems your disks are too slow. On my laptop (nothing special, just one > disk) using COPY I can dump 3G in ~ 20 secs, loading takes 120 secs, bare > copying 3G takes 10 secs. > > Similar proportion you had, but much faster. > > confirm I/O is your bottleneck, and tell us how you solved your problem > > Anyway, You can cut import time by half if you set your destination table > to unlogged (postgres will write half the data, it will save the > transaction log writing). Remember to set it to logged when finished!! > > > Regards, > > Daniel > > El 16 ago 2017, a las 16:32, Mariel Cherkassky < > mariel.cherkassky@gmail.com> escribi=C3=B3: > > My server is virtual and it have virtual hd from a vnx storage machine. > The logs and the data are on the same disk. > > 2017-08-16 17:04 GMT+03:00 Daniel Blanch Bataller < > daniel.blanch.bataller@gmail.com>: > >> Considering it has to write logs and data at checkpoints I don=E2=80=99t= see it >> particularly slow compared to the extract phase. What kind of disks you >> have SSD or regular disks? Different disks for ltransaction logs and dat= a? >> >> >> El 16 ago 2017, a las 15:54, Mariel Cherkassky < >> mariel.cherkassky@gmail.com> escribi=C3=B3: >> >> I run the copy command via psql to create a local dump of a 3G table and >> it took me 134059.732ms =3D~2 minutes. After that I imported the data vi= a >> copy and it took 458648.677ms =3D~7 minutes. So the copy command works b= ut >> pretty slow. >> >> 2017-08-16 16:08 GMT+03:00 Daniel Blanch Bataller < >> daniel.blanch.bataller@gmail.com>: >> >>> See if the copy command is actually working, copy should be very fast >>> from your local disk. >>> >>> >>> El 16 ago 2017, a las 14:26, Mariel Cherkassky < >>> mariel.cherkassky@gmail.com> escribi=C3=B3: >>> >>> >>> After all the changes of the memory parameters the same >>> operation(without the copy utility) didnt run much faster - it took on= e >>> minute less. I made a test with the copy command (without the 'with >>> binary') and it took 1.5 hours to create the dumpfile in my local >>> postgresql server. Then I tried to run the copy from the local dump and= it >>> is already running two hours and it didnt even finish. I looked at the >>> server log and I saw that I run the copy command at 13:18:05, 3 minutes >>> later checkpoint started and completed and there are no messages in the= log >>> after that. What can I do ? Improving the memory parameters and the mem= ory >>> on the server didnt help and for now the copy command doesnt help eithe= r. >>> >>> >>> >>> >>> 2017-08-15 20:14 GMT+03:00 Scott Marlowe : >>> >>>> On Tue, Aug 15, 2017 at 4:06 AM, Mariel Cherkassky >>>> wrote: >>>> > Hi, >>>> > So I I run the cheks that jeff mentioned : >>>> > \copy (select * from oracle_remote_table) to /tmp/tmp with binary - = 1 >>>> hour >>>> > and 35 minutes >>>> >>>> So 26G takes 95 minutes, or 27 MB/minute or 456k/second? Sound about >>>> right (it's early, I haven't had enough coffee please check my math). >>>> That's pretty slow unless you're working across pretty big distances >>>> with mediocre connections. My home internet downloads about 100MB/s >>>> by comparison. >>>> >>>> > \copy local_postresql_table from /tmp/tmp with binary - Didnt run >>>> because >>>> > the remote oracle database is currently under maintenance work. >>>> >>>> You shouldn't need the remote oracle server if you've already copied >>>> it over, you're just copying from local disk into the local pgsql db. >>>> Unless I'm missing something. >>>> >>>> > So I decided to follow MichaelDBA tips and I set the ram on my >>>> machine to >>>> > 16G and I configured the effective_cache memory to 14G,tshared_buffe= r >>>> to be >>>> > 2G and maintenance_work_mem to 4G. >>>> >>>> Good settings. Maybe set work_mem to 128MB or so while you're at it. >>>> >>>> > I started running the copy checks again and for now it coppied 5G in >>>> 10 >>>> > minutes. I have some questions : >>>> > 1)When I run insert into local_postresql_table select * from >>>> > remote_oracle_table I insert that data as bulk to the local table or >>>> row by >>>> > row ? If the answer as bulk than why copy is a better option for >>>> this case >>>> > ? >>>> >>>> insert into select from oracle remote is one big copy, but it will >>>> take at least as long as copying from oracle to the local network >>>> took. Compare that to the same thing but use file_fdw on the file >>>> locally. >>>> >>>> > 2)The copy from dump into the postgresql database should take less >>>> time than >>>> > the copy to dump ? >>>> >>>> Yes. The copy from Oracle to your local drive is painfully slow for a >>>> modern network connection. >>>> >>>> > 3)What do you think about the new memory parameters that I cofigured= ? >>>> >>>> They should be OK. I'm more worried about the performance of the io >>>> subsystem tbh. >>>> >>> >>> >>> >> >> > > --001a11444ab270998e0556ed14f6 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi Daniel,
I alread= y tried to set the destination table to unlogged - it improved the performa= nce slightly. Is there a way to make sure that I/O is the problem ?=C2=A0

2017-08-17 0:46 GMT+03:00 Daniel Blanch Bataller <daniel.blanch.bataller@gmail.com>:
Seems your disks are too slow. On my laptop (nothing spe= cial, just one disk) using COPY I can dump 3G in ~ 20 secs, loading takes 1= 20 secs, bare copying 3G takes 10 secs.=C2=A0

Simi= lar proportion you had, but much faster.=C2=A0

con= firm I/O is your bottleneck, and tell us how you solved your problem
<= div>
Anyway, You can cut import time by half if you set your = destination table to unlogged (postgres will write half the data, it will s= ave the transaction log writing). Remember to set it to logged when finishe= d!!


Regards,

Daniel

El 16 ago 2017, a las 16:32, Mariel Cherkassky <mariel.cherkassky@gmail.com= > escribi=C3=B3:

= My server is virtual and it have virtual hd from a vnx storage machine. The= logs and the data are on the same disk.

2017-08-16 17:04 GMT+03= :00 Daniel Blanch Bataller <daniel.blanch.bataller@gmail.com>:
Consider= ing it has to write logs and data at checkpoints I don=E2=80=99t see it par= ticularly slow compared to the extract phase. What kind of disks you have S= SD or regular disks? Different disks for ltransaction logs and data?
<= div>


El 16 ago 2017, a las 15:54, Mariel Cherkassky <mariel.cherk= assky@gmail.com> escribi=C3=B3:

I run the copy command via psql to create a local dump of a 3= G table and it took me=C2=A0134059.732ms =3D~2 minutes. After that I import= ed the data via copy and it took 458648.677ms =3D~7 minutes. So the copy co= mmand works but pretty slow.=C2=A0
2017-08-16 16:08 GMT+03:00 Da= niel Blanch Bataller <daniel.blanch.bataller@gmail.com= >:
See if the cop= y command is actually working, copy should be very fast from your local dis= k.


El 16 ago 2017, a las 1= 4:26, Mariel Cherkassky <mariel.cherkassky@gmail.com> escribi=C3=B3:
<= br class=3D"m_537601640012638934m_3042649036972317580m_-6547562234106903276= Apple-interchange-newline">

After all the changes of th= e memory parameters the same operation(without the copy utility) didnt run = much faster - it =C2=A0took one minute less. I made a test with the copy co= mmand (without the 'with binary') and it took 1.5 hours to create t= he dumpfile in my local postgresql server. Then I tried to run the copy fro= m the local dump and it is already running two hours and it didnt even fini= sh. I looked at the server log and I saw that I run the copy command at 13:= 18:05, 3 minutes later checkpoint started and completed and there are no me= ssages in the log after that. What can I do ? Improving the memory paramete= rs and the memory on the server didnt help and for now the copy command doe= snt help either.

=


<= br>
2017-08-15 20:14 GMT+0= 3:00 Scott Marlowe <scott.marlowe@gmail.com>:
On Tue, Aug 15, 2017 at 4:06 AM, Mariel C= herkassky
<mariel= .cherkassky@gmail.com> wrote:
> Hi,
> So I I run the cheks that jeff mentioned :
> \copy (select * from oracle_remote_table) to /tmp/tmp with binary - 1 = hour
> and 35 minutes

So 26G takes 95 minutes, or 27 MB/minute or 456k/second? Sound about=
right (it's early, I haven't had enough coffee please check my math= ).
That's pretty slow unless you're working across pretty big distance= s
with mediocre connections.=C2=A0 My home internet downloads about 100MB/s by comparison.

> \copy local_postresql_table from /tmp/tmp with binary - Didnt run beca= use
> the remote oracle database is currently under maintenance work.

You shouldn't need the remote oracle server if you've alread= y copied
it over, you're just copying from local disk into the local pgsql db. Unless I'm missing something.

> So I decided to follow MichaelDBA tips and I set the ram on my machine= to
> 16G and I configured the effective_cache memory to 14G,tshared_buffer = to be
> 2G and maintenance_work_mem to 4G.

Good settings. Maybe set work_mem to 128MB or so while you're at= it.

> I started running the copy checks again and for now it coppied 5G in 1= 0
> minutes. I have some questions :
> 1)When I run insert into local_postresql_table select * from
> remote_oracle_table I insert that data as bulk to the local table or r= ow by
> row ?=C2=A0 If the answer as bulk than why copy is a better option for= this case
> ?

insert into select from oracle remote is one big copy, but it will take at least as long as copying from oracle to the local network
took. Compare that to the same thing but use file_fdw on the file
locally.

> 2)The copy from dump into the postgresql database should take less tim= e than
> the copy to dump ?

Yes. The copy from Oracle to your local drive is painfully slow for = a
modern network connection.

> 3)What do you think about the new memory parameters that I cofigured ?=

They should be OK. I'm more worried about the performance of the= io
subsystem tbh.







--001a11444ab270998e0556ed14f6--