Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dhFNw-0005kv-SS for pgsql-performance@arkaria.postgresql.org; Mon, 14 Aug 2017 13:26:25 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dhFNv-0004tI-Uv for pgsql-performance@arkaria.postgresql.org; Mon, 14 Aug 2017 13:26:23 +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 1dhFM6-0001fG-Je for pgsql-performance@postgresql.org; Mon, 14 Aug 2017 13:24:30 +0000 Received: from mail-wm0-x233.google.com ([2a00:1450:400c:c09::233]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1dhFM3-0007Rh-Hb for pgsql-performance@postgresql.org; Mon, 14 Aug 2017 13:24:30 +0000 Received: by mail-wm0-x233.google.com with SMTP id f15so43685395wmg.1 for ; Mon, 14 Aug 2017 06:24:27 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:from:date:message-id:subject:to; bh=P9E5xuH0oGCrKPwhXjIBuocTyC/XcEWnHZkzM6Rt/sM=; b=KWJX8b8WxbDMkeK4Fu0N1/o2MisCofr2td3XtLRzDnSgszAbixRA550CAuQrlhXwHe ih92KeNHzVWNv0pg7y6Us1BuHt8rmKAg/XKKMSJYSHCd3YefWsonKF41hKy5K1kr/VAc BEBzl5/bPF0o2tgsycXL4luFFTU1J/LJUKxGPI/uF5yWAdUbmCOY8X7KTiS3CeULEKM0 UHq30rI9/ZqeyjcQtjAHtpe3COGe0txaB5R/U1bWzIBTyWxHkN1DefcW1MaLigP/tjBe +Ifg7X644PEnJFoe+Plbx3w9d+BGgVjjpkyWLG1DFsVeSuCahanXQL91cFougsNG45iq yC1Q== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:from:date:message-id:subject:to; bh=P9E5xuH0oGCrKPwhXjIBuocTyC/XcEWnHZkzM6Rt/sM=; b=pYfLx5N33njgQtM5CYcjcJIOFyV8UlPMMDcoh8szC3BdqpwQfMFjTaoOE6gSfWkalR 1gaB/D5wrO+yrwYFp/SnxM2N2n2sGlhdllOU6nbWuJFtTCmpJJ6EGkVIHKcvgt8FDyOG ruo9OqnLTu5/iRQO8XWUwAukEA6Of9Q7KwLr9Zr2AouW2Wgg2fq/vWa4Fch/anhQ96PB plIepGR5ktCDLLMCeU9HLEwQ2Ld8gTng2XEp2nGzPFibW3YsqzP8OF0CcASHShLYNs3m iupM4r17DvWczK9vktc8wWFDfOzcdu2aD18lPwEQ/lvsIJ7uicW5Yh0LStVKGKI4SVln u/1A== X-Gm-Message-State: AHYfb5hqNBFEhMMhzzsmrA/UkFTsSxFA828y2MMTm2Ltz6eprccVBnHw SjJX9uIJoZD2GzRGGPiwYOU3tOVpXD1ihBs= X-Received: by 10.28.113.90 with SMTP id m87mr3709799wmc.59.1502717065526; Mon, 14 Aug 2017 06:24:25 -0700 (PDT) MIME-Version: 1.0 Received: by 10.28.184.85 with HTTP; Mon, 14 Aug 2017 06:24:24 -0700 (PDT) From: Mariel Cherkassky Date: Mon, 14 Aug 2017 16:24:24 +0300 Message-ID: Subject: performance problem on big tables To: pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary="001a114620c4e3833e0556b6944e" 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 --001a114620c4e3833e0556b6944e Content-Type: text/plain; charset="UTF-8" I have performance issues with two big tables. Those tables are located on an oracle remote database. I'm running the quert : insert into local_postgresql_table select * from oracle_remote_table. The first table has 45M records and its size is 23G. The import of the data from the oracle remote database is taking 1 hour and 38 minutes. After that I create 13 regular indexes on the table and it takes 10 minutes per table ->2 hours and 10 minutes in total. The second table has 29M records and its size is 26G. The import of the data from the oracle remote database is taking 2 hours and 30 minutes. The creation of the indexes takes 1 hours and 30 minutes (some are indexes on one column and the creation takes 5 min and some are indexes on multiples column and it takes 11 min. Those operation are very problematic for me and I'm searching for a solution to improve the performance. The parameters I assigned : min_parallel_relation_size = 200MB max_parallel_workers_per_gather = 5 max_worker_processes = 8 effective_cache_size = 2500MB work_mem = 16MB maintenance_work_mem = 1500MB shared_buffers = 2000MB RAM : 5G CPU CORES : 8 *-I tried running select count(*) from table in oracle and in postgresql the running time is almost equal.* *-Before importing the data I drop the indexes and the constraints.* *-I tried to copy a 23G file from the oracle server to the postgresql server and it took me 12 minutes.* Please advice how can I continue ? How can I improve something in this operation ? --001a114620c4e3833e0556b6944e Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

I have p= erformance issues with two big tables. Those tables are located on an oracl= e remote database. I'm running the quert :=C2=A0insert into local_postgr= esql_table select * from oracle_remote_table.

The first table has 45M records and its size= is 23G. The import of the data from the oracle remote database is taking 1= hour and 38 minutes. After that I create 13 regular indexes on the table a= nd it takes 10 minutes per table ->2 hours and 10 minutes in total.

<= p style=3D"direction:ltr;margin:0px 0px 1em;padding:0px;border:0px;font-var= iant-numeric:inherit;font-stretch:inherit;font-size:15px;line-height:inheri= t;font-family:Arial,"Helvetica Neue",Helvetica,sans-serif;vertica= l-align:baseline;clear:both;color:rgb(36,39,41)">The second table has 29M r= ecords and its size is 26G. The import of the data from the oracle remote d= atabase is taking 2 hours and 30 minutes. The creation of the indexes takes= 1 hours and 30 minutes (some are indexes on one column and the creation ta= kes 5 min and some are indexes on multiples column and it takes 11 min.

=

Those operation are very = problematic for me and I'm searching for a solution to improve the perf= ormance. The parameters I assigned :

mi=
n_parallel_relation_size =3D 200MB
max_para= llel_workers_per_gather =3D 5
max_worker_processes =3D 8
effective_cache_size =3D 2500MB
work_mem =3D 16MB<= /div>
maintenance_work_mem =3D 1500MB
shared_buffers =3D 2000MB
=
RAM : 5= G
CPU CORES : 8
=

-I tried running select count(*) from table in or= acle and in postgresql the running time is almost equal.

-Before importing the data I drop the indexes and the constra= ints.

-I tried to copy a 23G file from the = oracle server to the postgresql server and it took me 12 minutes.<= /p>

Please advice how can = I continue ? How can I improve something in this operation ?

--001a114620c4e3833e0556b6944e--