Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dhy4L-0007YC-Ce for pgsql-performance@arkaria.postgresql.org; Wed, 16 Aug 2017 13:09:09 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dhy4K-0006QM-VJ for pgsql-performance@arkaria.postgresql.org; Wed, 16 Aug 2017 13:09:09 +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 1dhy4J-0006PQ-LU for pgsql-performance@postgresql.org; Wed, 16 Aug 2017 13:09:07 +0000 Received: from mail-wr0-x242.google.com ([2a00:1450:400c:c0c::242]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1dhy4F-0000ZC-FK for pgsql-performance@postgresql.org; Wed, 16 Aug 2017 13:09:06 +0000 Received: by mail-wr0-x242.google.com with SMTP id x43so2996568wrb.1 for ; Wed, 16 Aug 2017 06:09:03 -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=y9eC9ZxQYr8wEycnRRJrA5G7RgZyONGzyOF+57ExBQg=; b=Do3cjRBV8Mpa47YbVuvbXFcBQewLjhE7sxXj3zdfsN4zacALPCdJZqqaS+RiOJbgof goIgkasqgV7NpI0w/e3qeIu5llaQPu3DW1W4Sh9DVwE1E6cin2taRsAa0pN8AwIaxYSB 54gp+AnPgpGtj0kGMgHq+8YNaUn2mJH1kKS54TvwMWtYrK2V6YEuwiMCzc73CkUhO9sh CMsMnZx86RhBKoFMtqAekq3Jdz8VXXNk6Oi95JGUKaV1/vN1YIbQHnH0Svq+fuvdJ8Af H1StcoUXMlswaGO1V0mtmJHOjJtdV0JrYIcO8sb+qLiO9vraEugVQx4wLK3sLNl15uCk PMSQ== 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=y9eC9ZxQYr8wEycnRRJrA5G7RgZyONGzyOF+57ExBQg=; b=fu1QxsV0xb8ZmzpInM18I1z1/ikNMVeZRFI1Gj5HEWaQDUfvVB+WDysUi1ef39Q5F+ HRr0vqoySyPclQRQwV2UjfyS4VI3DIma9bxfZYTKus8yEVueAdppBCUaRgXdMkN+x9Eb PC/JAE63xQVtMysivTK5eGvLcfez80cA8dyXTdKeS5MNdoquifPZXRCpacfyOrbWeDGp 5zSjdx+gbTnfD3smXvbHznZEKB1NH60IFQzOo5WKR8NKXKH3ISgToG3EHeYk+3akkbL/ 0tEftsx8iX90PZek/1f2MW/gEzl/bAWy+YvNRcbi3ANMECqRCkgKDs2MYKK4IOZi1dak 3tFA== X-Gm-Message-State: AHYfb5gKN9ciA/Fro9YQW7Ge8lTzRIywX+/uVqm+E3AM0FA/moLKIWBi 0Yolfi5ICquEakxkdFSjHg== X-Received: by 10.223.139.88 with SMTP id v24mr1352814wra.208.1502888941020; Wed, 16 Aug 2017 06:09:01 -0700 (PDT) Received: from [192.168.8.100] (59.red-176-80-217.dynamicip.rima-tde.net. [176.80.217.59]) by smtp.gmail.com with ESMTPSA id b33sm957901wrg.65.2017.08.16.06.08.59 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Wed, 16 Aug 2017 06:09:00 -0700 (PDT) From: Daniel Blanch Bataller Message-Id: <88350892-C4AB-4191-9F8E-DB7CFA81F6C6@gmail.com> Content-Type: multipart/alternative; boundary="Apple-Mail=_9A428A40-5950-4D54-972D-3FEA968CA57D" Mime-Version: 1.0 (Mac OS X Mail 10.3 \(3273\)) Subject: Re: performance problem on big tables Date: Wed, 16 Aug 2017 15:08:56 +0200 In-Reply-To: Cc: Scott Marlowe , "pgsql-performance@postgresql.org" To: Mariel Cherkassky References: 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=_9A428A40-5950-4D54-972D-3FEA968CA57D Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 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 = 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 --Apple-Mail=_9A428A40-5950-4D54-972D-3FEA968CA57D Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8
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=_9A428A40-5950-4D54-972D-3FEA968CA57D--