Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dhxPB-0004yD-Fq for pgsql-performance@arkaria.postgresql.org; Wed, 16 Aug 2017 12:26:37 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dhxPA-0004Dn-BT for pgsql-performance@arkaria.postgresql.org; Wed, 16 Aug 2017 12:26:36 +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 1dhxP9-0004Dd-Hy for pgsql-performance@postgresql.org; Wed, 16 Aug 2017 12:26:35 +0000 Received: from mail-wr0-x22f.google.com ([2a00:1450:400c:c0c::22f]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1dhxP5-0003Vz-QM for pgsql-performance@postgresql.org; Wed, 16 Aug 2017 12:26:34 +0000 Received: by mail-wr0-x22f.google.com with SMTP id x43so14410245wrb.3 for ; Wed, 16 Aug 2017 05:26:31 -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=gbZkiEJcF3VXrOMdH23N+iKIMonjMmAZhQn9ccboUMc=; b=PRLEa8LweSuQIoHj7mPsjSZ9BLCDAr5bYA3zvAtTOyqIgOtsdxORKYR9aOQtcvrmXt 4J1GSjaliezxoTafddgMg980uiVcHZnAFj5DgxDT8JuJOQUeAEDA7ANrV4lrrzG87Az9 nd87Mcu7s2Sp3FVzyqlPdvg2bMw6VlcEnRWcSIh/jXOly42t56i3a95Ay5Kda9r0Spl7 qDWB5UWtzbRq/hQaX15EM69RsZKxez9NnrvyYN5xh3h+p5GXuAchj22DAl8rnmdGAjK6 wfowuwDhRl+n9qgvu9ZNBbijIuGeqAWiU2ggM9N20wy2x4eQT4Pr2Omu8O2WQ0epvL7/ 4D0Q== 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=gbZkiEJcF3VXrOMdH23N+iKIMonjMmAZhQn9ccboUMc=; b=iNM7XG2+nu5VPaqiotkKwicmMychm0ux3f9Q4Jb6P4hCgYImj6i5ZjkeY2W12HQtwb yiX/cPZkPF1KIRRWcA1PPnklvNYTMftSaeMgkqUeICS/jd6PNdXY24dBsUVOZ9K9zcYi QgNjffQnozWyRrgW1ITv4pRO32x0WhlYd5gOPiNpYVyORxxztNK5qDsFwXwawfxbl/NV PEEcb0g4FrLrWxxtPTJnVSu7iJBTzo45p2Nbqa2ReOYQCTJBYNmaboBAckvmROuJuV4w rIUdkc5hwxeNyys/4iyIkVw81mG6p+/sngrAB6jB7tVPDPsFYXS34ARdwUyvkwiVZnTU Hqyg== X-Gm-Message-State: AHYfb5g9l7zTy2gcYioudDk2yToxGxoTnqRLZ6wvR07Lys+l922h2GGT pB5LZED4Mza6Kbp3xQrjFciKa9PP+gaC X-Received: by 10.28.175.8 with SMTP id y8mr1105100wme.42.1502886390686; Wed, 16 Aug 2017 05:26:30 -0700 (PDT) MIME-Version: 1.0 Received: by 10.28.184.85 with HTTP; Wed, 16 Aug 2017 05:26:29 -0700 (PDT) In-Reply-To: References: From: Mariel Cherkassky Date: Wed, 16 Aug 2017 15:26:29 +0300 Message-ID: Subject: Re: performance problem on big tables To: Scott Marlowe Cc: "pgsql-performance@postgresql.org" Content-Type: multipart/alternative; boundary="001a11444ab2746e230556de0160" 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 --001a11444ab2746e230556de0160 Content-Type: text/plain; charset="UTF-8" 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 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 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 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. > --001a11444ab2746e230556de0160 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

After all the changes of the memory parameters the same ope= ration(without the copy utility) didnt run much faster - it =C2=A0took one = minute less. I made a test with the copy command (without the 'with bin= ary') and it took 1.5 hours to create the dumpfile in my local postgres= ql server. Then I tried to run the copy from the local dump and it is alrea= dy running two hours and it didnt even finish. I looked at the server log a= nd I saw that I run the copy command at 13:18:05, 3 minutes later checkpoin= t started and completed and there are no messages in the log after that. Wh= at can I do ? Improving the memory parameters and the memory on the server = didnt help and for now the copy command doesnt help either.



<= div style=3D"text-align:left" dir=3D"ltr">
2017-08-15 20: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.

--001a11444ab2746e230556de0160--