Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dhyxr-0002lc-L0 for pgsql-performance@arkaria.postgresql.org; Wed, 16 Aug 2017 14:06:31 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dhyxr-0006y3-2i for pgsql-performance@arkaria.postgresql.org; Wed, 16 Aug 2017 14:06:31 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1dhyw2-0002dd-9j for pgsql-performance@postgresql.org; Wed, 16 Aug 2017 14:04:38 +0000 Received: from mail-wr0-x236.google.com ([2a00:1450:400c:c0c::236]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1dhyvz-0001qj-Ay for pgsql-performance@postgresql.org; Wed, 16 Aug 2017 14:04:37 +0000 Received: by mail-wr0-x236.google.com with SMTP id 49so8327425wrw.2 for ; Wed, 16 Aug 2017 07:04:34 -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=1Y2hwXL7MQwNuV1X9yK7hLkkgb+x+eXPvHBrFrvKDy8=; b=ni57c5Lb+UL9UQbDCU4rp2uVHlHzskqLzBmwIUdo0eNbjcVLNId93NYJ9vJ9BmOCHP ttBjRyoXfGrHABq/0PkjFoLmgzyPDZPfQdrwZ47XBVSlocQZ9kTBqEVrYJ0LePnl92ww LLxjl2OOGGdnb3GIF61G0+OhElLj7R+fZ/AhNZB12lISOzV6MaRi0ADxhguY0WtYI/D5 U6Lmmo4fG9bdRNlVjsmy92DNgshHyU+RS8XAfHXBcZnwnkbxtfCQxOlBGujQjyIU2lEi 43wuZrzFQwqBF6O9Hc4YROkMNANp6snus4S6+JMASOjRGrb3NlVUPz1xYaK9WNZxrcVu 1mDQ== 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=1Y2hwXL7MQwNuV1X9yK7hLkkgb+x+eXPvHBrFrvKDy8=; b=AcSVvH1o6fba+Vqn+A5Ewh9PFtTNrYoV4ft+ZlsUlQpYo6pm9N8x1yyXxe82VrxBGd vy8nQwdkWpEucv76+Pf4sp819OeprgJQUcAJLI4S/SnQyOc/bHbOvidRmfZxhNifQV6A YYVjKzEfmzBeqpFyiNWaARQpb0/wfBfjijV7N0/Ew7FMNCdtIV+TYTj6pI31O/vTC8Ux K6329VtLUifOPatm3bbFcZgsmxX7Q/hbrACkVTl0QGMk0lgfUrbq6EOwQ1V9NPNwOkYG yZbI8Sq95Td2rcTnmkfVgtmreEt8sZYjd0PWa+qmxLkzyNJQJbaNHHfFdPqG4t68t1ik 4rig== X-Gm-Message-State: AHYfb5jqXjyL87pAQ0qpWJxzWafs+3h/rQzpwLA2q7sBMEe6dq/TQBda rslq+hqWOdifEQ== X-Received: by 10.223.144.131 with SMTP id i3mr1312641wri.40.1502892273541; Wed, 16 Aug 2017 07:04:33 -0700 (PDT) Received: from [192.168.8.100] (89.red-79-149-182.dynamicip.rima-tde.net. [79.149.182.89]) by smtp.gmail.com with ESMTPSA id c13sm710468wrb.87.2017.08.16.07.04.32 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Wed, 16 Aug 2017 07:04:33 -0700 (PDT) From: Daniel Blanch Bataller Message-Id: <00990FBB-D288-4878-8B8E-36E24F808F2C@gmail.com> Content-Type: multipart/alternative; boundary="Apple-Mail=_3DABF3F1-9400-4124-A343-A10F84BA9B3F" Mime-Version: 1.0 (Mac OS X Mail 10.3 \(3273\)) Subject: Re: performance problem on big tables Date: Wed, 16 Aug 2017 16:04:30 +0200 In-Reply-To: Cc: Scott Marlowe , "pgsql-performance@postgresql.org" To: Mariel Cherkassky References: <88350892-C4AB-4191-9F8E-DB7CFA81F6C6@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=_3DABF3F1-9400-4124-A343-A10F84BA9B3F Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 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 = 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 --Apple-Mail=_3DABF3F1-9400-4124-A343-A10F84BA9B3F Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8
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=_3DABF3F1-9400-4124-A343-A10F84BA9B3F--