Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1diHjD-00082G-Iz for pgsql-performance@arkaria.postgresql.org; Thu, 17 Aug 2017 10:08:39 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1diHjC-0002fK-Tk for pgsql-performance@arkaria.postgresql.org; Thu, 17 Aug 2017 10:08:38 +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 1diHhO-0006YW-J2 for pgsql-performance@postgresql.org; Thu, 17 Aug 2017 10:06:46 +0000 Received: from mail-wr0-x229.google.com ([2a00:1450:400c:c0c::229]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1diHhL-0007Xv-0T for pgsql-performance@postgresql.org; Thu, 17 Aug 2017 10:06:46 +0000 Received: by mail-wr0-x229.google.com with SMTP id x43so39500128wrb.3 for ; Thu, 17 Aug 2017 03:06:42 -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=gYbjDmLyuFSRs/QFjHimopHzVGNz4d/Sp7A8PAmohYk=; b=B9Ep9GJTXdEu7VHmY1T0fd2V0zAVJ5WWrNGPTwRCi2fbkm6mY0dqgT2AO674CV0CFC LxnxksIHu8+ZGzP/FHBfWRr84ZaPKcDW6mNV+Z35uxSksLdFhWhsewKfNFMBrG+iORua xyrums+/ypNcq8EslWxVgvIo+9VmjcAy+iE4j11y4RGeDC5d1zi9QyBTOi+SAr9agx7B riZ1DwfPaIL64x3v/0zuglv2IpXAalTPGI/LzTeZSZtJH0UUo4/Q+Yne9QhNouAMMovR HSxIsIwITKyqBxGETNIRpdwfkYCzUz4TQKUULjACbBelYaokbaI5j7WZlQgdydi7C6K3 11CQ== 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=gYbjDmLyuFSRs/QFjHimopHzVGNz4d/Sp7A8PAmohYk=; b=XCL3GiuRHwh9xO2vfcxzikhi4f8ME1iTEHHj6mpwx9t/BrZP3MD6wTZXEHLS8ydGwM ismneed5+z1scbovLQWIgkLHjwC2ZICHMwskryIf1vVE8+6nEe9JodzmzjIDfZu8xdXE QG/mtAyaeqC3ghWHh7UNa2Yf5iFQOFT0KqZ8G4KExhQhw7WV6/JpKRQnTpE0Vi6+ifxW 9RQUo706a6FILYJ3CVMpu8BbqXXwn6AvpvTHiaG603z0YLn4F3BPeTIsTBKs5vL53cii 3PHXX06/pUjbjZuWORxedSw0z8nAsF29DH0XvmU0YdB6vdRNCcNgsNSdz+uzjcghLtSr I8Wg== X-Gm-Message-State: AHYfb5gmVSHr5rN+ftdw0+EZP0S/98kSNptbW0ZOOUKSWErhCVHxYpqv /ML7rYn7akAqQw== X-Received: by 10.28.213.75 with SMTP id m72mr935956wmg.41.1502964402199; Thu, 17 Aug 2017 03:06:42 -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 o24sm4194602wmi.8.2017.08.17.03.06.39 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Thu, 17 Aug 2017 03:06:40 -0700 (PDT) From: Daniel Blanch Bataller Message-Id: Content-Type: multipart/alternative; boundary="Apple-Mail=_D0EF6576-E6EA-4602-B7A2-0F5C69B699B6" Mime-Version: 1.0 (Mac OS X Mail 10.3 \(3273\)) Subject: Re: performance problem on big tables Date: Thu, 17 Aug 2017 12:06:39 +0200 In-Reply-To: Cc: Scott Marlowe , "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=_D0EF6576-E6EA-4602-B7A2-0F5C69B699B6 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 I would just check how does it take to copy 3GB using an standard copy = command. on my computer it took 10 secs.=20 > El 17 ago 2017, a las 11:00, Mariel Cherkassky = escribi=C3=B3: >=20 > 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 > 2017-08-17 9:25 GMT+03:00 Mariel Cherkassky = >: > 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 ?=20 >=20 > 2017-08-17 0:46 GMT+03:00 Daniel Blanch Bataller = >: > 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.=20 >=20 > Similar proportion you had, but much faster.=20 >=20 > confirm I/O is your bottleneck, and tell us how you solved your = problem >=20 > 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!! >=20 >=20 > Regards, >=20 > Daniel >=20 >> El 16 ago 2017, a las 16:32, Mariel Cherkassky = > = escribi=C3=B3: >>=20 >> My server is virtual and it have virtual hd from a vnx storage = machine. The logs and the data are on the same disk. >>=20 >> 2017-08-16 17:04 GMT+03:00 Daniel Blanch Bataller = >: >> Considering it has to write logs and data at checkpoints I don=E2=80=99= t 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 data? >>=20 >>=20 >>> El 16 ago 2017, a las 15:54, Mariel Cherkassky = > = escribi=C3=B3: >>>=20 >>> 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 pretty slow.=20 >>>=20 >>> 2017-08-16 16:08 GMT+03:00 Daniel Blanch Bataller = >: >>> See if the copy command is actually working, copy should be very = fast from your local disk. >>>=20 >>>=20 >>>> El 16 ago 2017, a las 14:26, Mariel Cherkassky = > = escribi=C3=B3: >>>>=20 >>>>=20 >>>> 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. >>>>=20 >>>>=20 >>>>=20 >>>>=20 >>>> 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 >>>>=20 >>>> 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. >>>>=20 >>>> > \copy local_postresql_table from /tmp/tmp with binary - Didnt run = because >>>> > the remote oracle database is currently under maintenance work. >>>>=20 >>>> 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. >>>>=20 >>>> > 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. >>>>=20 >>>> Good settings. Maybe set work_mem to 128MB or so while you're at = it. >>>>=20 >>>> > 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 >>>> > ? >>>>=20 >>>> 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. >>>>=20 >>>> > 2)The copy from dump into the postgresql database should take = less time than >>>> > the copy to dump ? >>>>=20 >>>> Yes. The copy from Oracle to your local drive is painfully slow for = a >>>> modern network connection. >>>>=20 >>>> > 3)What do you think about the new memory parameters that I = cofigured ? >>>>=20 >>>> They should be OK. I'm more worried about the performance of the io >>>> subsystem tbh. >>>>=20 >>>=20 >>>=20 >>=20 >>=20 >=20 >=20 --Apple-Mail=_D0EF6576-E6EA-4602-B7A2-0F5C69B699B6 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8
I would just check how does it take to copy = 3GB using an standard copy command. on my computer it took 10 = secs. 


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

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 ?

2017-08-17 9:25 GMT+03:00 Mariel Cherkassky <mariel.cherkassky@gmail.com>:
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 = data?


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 via copy and it took 458648.677ms =3D~7 = minutes. So the copy command works but 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 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 <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 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.








= --Apple-Mail=_D0EF6576-E6EA-4602-B7A2-0F5C69B699B6--