Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dhyly-0001x2-0H for pgsql-performance@arkaria.postgresql.org; Wed, 16 Aug 2017 13:54:14 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dhylx-0003EW-Cl for pgsql-performance@arkaria.postgresql.org; Wed, 16 Aug 2017 13:54:13 +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 1dhylw-0003EM-NT for pgsql-performance@postgresql.org; Wed, 16 Aug 2017 13:54:12 +0000 Received: from mail-wr0-x22e.google.com ([2a00:1450:400c:c0c::22e]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1dhylt-0005bi-4n for pgsql-performance@postgresql.org; Wed, 16 Aug 2017 13:54:11 +0000 Received: by mail-wr0-x22e.google.com with SMTP id z91so6948091wrc.4 for ; Wed, 16 Aug 2017 06:54:08 -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=qQNwidOe9DlD2Xlf3D0MxpIxQUQNvb+6x2m2EKm616U=; b=fbSe8Y04rfGr9Qe0GhecRzp9FrIQbIuZuB4+g+Jt94xOo7rj45HqDlSYBYBGfelchi uhstx2lYxPgP8h7SMGxRFmdhu0Ze49bdo13a621YFRqZuQQ+H4z76jf7tjOVTZhzOJMb LuoaTIq08Y9aPK1Ze4yx3xLrfaGPvOLBoRtawyZz+YR5SDmtny0KE1dp5CoeGz1ChAVI nm7qsjiUnIaIt6SiTlDubGmJ5IL/BxDoMZK6XbEoWXCQ+Rm2Dh4teJCwDJoUxbZT3qmQ ZcfNYycZW2YSkW2ma7G0OgN6f4ulKD4cqyZiGC806J1KVEXXjqFp/PB3n6bZRcsYtvA9 moFw== 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=qQNwidOe9DlD2Xlf3D0MxpIxQUQNvb+6x2m2EKm616U=; b=qyQQh3dYMGYS5hEXGO1bA3bp/re4RzzHY/oRBL8fCRo6M+rP8QiiBCDj4mAd916cnE eUhgf3jOYLYOM6PoutavItPiGBeiDHk4PLLlV766Asi2Dj4G6Y1t1y4Ao9FtXiydlj3+ dKy19U379zoUZwAjmg+9X8s/9RtCbUge+Rp4Q6cQdubYHjL6D0MngkObVm8tvtY3RsGI 80+gpQWCU+UtC1QwNkr0gkKKDnXKjrC5NTemBOr0mBkWxXmLPw4r9Uqt3XVcTGehX4RP mi7XOfH/o1p7ltzi8huFplfPHIgh5XDlDh4aIeJb1Gk/dSh5d59bkuFfu4CEPBgBPzkO tEpQ== X-Gm-Message-State: AHYfb5hX7xmicUwYSq8u9hdX5buqEiENyvZmAoJM4HbDoYGckBdby+Yr w49PkaZlXY6MmanyH+P0xpsm5EadMg== X-Received: by 10.28.199.207 with SMTP id x198mr1258006wmf.156.1502891647526; Wed, 16 Aug 2017 06:54:07 -0700 (PDT) MIME-Version: 1.0 Received: by 10.28.184.85 with HTTP; Wed, 16 Aug 2017 06:54:06 -0700 (PDT) In-Reply-To: <88350892-C4AB-4191-9F8E-DB7CFA81F6C6@gmail.com> References: <88350892-C4AB-4191-9F8E-DB7CFA81F6C6@gmail.com> From: Mariel Cherkassky Date: Wed, 16 Aug 2017 16:54:06 +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="94eb2c0d8180c96d9b0556df3aa2" 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 --94eb2c0d8180c96d9b0556df3aa2 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 via copy and it took 458648.677ms =3D~7 minutes. So the copy command works but prett= y 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 fro= m > 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 one minute less. I mad= e > 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 co= py > 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 th= e > memory parameters and the memory on the server didnt help and for now the > copy command doesnt help either. > > > > > 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_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 >> 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 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. >> > > > --94eb2c0d8180c96d9b0556df3aa2 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
I run the copy command via psql to create= a local dump of a 3G table and it took me=C2=A0134059.732ms =3D~2 minutes.= After that I imported the data via copy and it took 458648.677ms =3D~7 min= utes. So the copy command works but pretty slow.=C2=A0

2017-08-1= 6 16:08 GMT+03:00 Daniel Blanch Bataller <daniel.blanch.bat= aller@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 &l= t;mariel.c= herkassky@gmail.com> escribi=C3=B3:


After all the chang= es of the memory parameters the same operation(without the copy utility) di= dnt run much faster - it =C2=A0took one 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 e= ven finish. I looked at the server log and I saw that I run the copy comman= d at 13:18:05, 3 minutes later checkpoint started and completed and there a= re no messages in the log after that. What can I do ? Improving the memory = parameters and the memory on the server didnt help and for now the copy com= mand doesnt help either.



2017-08-15 2= 0:14 GMT+03:00 Scott Marlowe <scott.marlowe@gmail.com>= :
On Tue, Aug 15, 2017 at 4:06 AM= , Mariel Cherkassky
<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.



--94eb2c0d8180c96d9b0556df3aa2--