Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dhH30-0003rN-0U for pgsql-performance@arkaria.postgresql.org; Mon, 14 Aug 2017 15:12:54 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dhH2z-000493-DM for pgsql-performance@arkaria.postgresql.org; Mon, 14 Aug 2017 15:12:53 +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 1dhH19-0008D3-JG for pgsql-performance@postgresql.org; Mon, 14 Aug 2017 15:10:59 +0000 Received: from mout.perfora.net ([74.208.4.196]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1dhH16-0006aL-Mv for pgsql-performance@postgresql.org; Mon, 14 Aug 2017 15:10:58 +0000 Received: from [192.168.1.101] ([98.169.212.183]) by mrelay.perfora.net (mreueus001 [74.208.5.2]) with ESMTPSA (Nemesis) id 0LzZYq-1dUVRe3dYo-014llg; Mon, 14 Aug 2017 17:10:53 +0200 Message-ID: <5991BD7B.4060102@sqlexec.com> Date: Mon, 14 Aug 2017 11:10:51 -0400 From: MichaelDBA User-Agent: Postbox 5.0.16 (Windows/20170718) MIME-Version: 1.0 To: Mariel Cherkassky CC: pgsql-performance@postgresql.org Subject: Re: performance problem on big tables References: In-Reply-To: Content-Type: multipart/alternative; boundary="------------060907050102090905050903" X-Provags-ID: V03:K0:otl/tL+36B9yDRW+nfPfhQ+VHxmgdv/RK1m7wgCvtez4wrMdPMa I/Zpiv9uFrzsPpaFCCwxnuEumrhx4+Go7UMcblF5ydhHTGeoVhBHccHXjMFkYnIADq1Id5u 5Vn2C7kqeUcjDItMHLm2wS/N2mo+/1a2dy6PF/Rf1ckOeVxVyNVqlb0b0wR0J2PCisI5JI4 1KeHQLJUn4C8WQLKlN8fA== X-UI-Out-Filterresults: notjunk:1;V01:K0:Lqam0WSbtzE=:gC+NzgLZNXxbfh52osmPkR OmHZPnnlUy8IrAlNJESnpTnv3QRWKRHQjzdccbodzyOU0XWbOEXMLHz+HigAcogX0PVFTVDDB CjUObzbiWC6YwqcRF6M5HDX8uxMaqBZ8CE2skZh27k8EdeJzGOgnuNGTjyaS16b+TvIk8pypC WuevNyyHvwoDHeWlV2CJNcxmDaaDntsh6RGkgPgOe9tqfa2hjrCJzy8sF3g7+UOhG205rvGkf AaVpSRRoNtPmlzmHhqXTI8DX2GlzkPDOSZ0CzYpyY9FiNr+36ZzzZ9Pv0SBoV4xuWGGVU1xar dg3RDijJP19csb9JStxIgDwiBlby64zGhjpspG6XLnrQ0JJcr+0RGby/8XuuNZepC3V9Ggor2 hubfZwCokeVb3myPyxJvLazcKy/JDrfypJ7iymqLi/GrkyxjnuUfM/A3NR+SzgRSnmr0gqd4H Ivgcl41o9CjEj1A0GXUea/8FnTIBO17n9YBD1umcTGR73qPs37OXfpHiZrPWCtAtO0N8FkEku 9pG7/K3+q0sfyG/MsDbvfggyk378kUKIRRHc0s3jiT3aQRQnMucCU14wZjZqMhU1Ez4lRUUyX FLOlFV3thmXNIrowT5LrC+6VbNEt6izILYB641n8x9XwK3M36l2HrjJFAiRXlTVbWSYKf8Phf TCXfVTdYUnKYENKsAhzsLqzNdHXtEEM78UrtMlQjm3Du6SW0NcZ5c1/tELnHCB5BhZIfBBoJh SDdOyFvlYoNBSK9rT6hZK0Fm/j4qWWgp7O2ZFLYxbje1dvznM30GjPiX53o= 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 This is a multi-part message in MIME format. --------------060907050102090905050903 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit Total RAM on your host is 5GB, really? Before touching anything else, increase your RAM. That will be your big performance boost right there. Then, you can "up" your effective_cache_size and maintenance_work_mem. Regards, Michael Vitale > Mariel Cherkassky > Monday, August 14, 2017 9:24 AM > > 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 ? > --------------060907050102090905050903 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit Total RAM on your host is 5GB, really?  Before touching anything else, increase your RAM.  That will be your big performance boost right there.  Then, you can "up" your effective_cache_size and maintenance_work_mem.

Regards,
Michael Vitale

Monday, August 14, 2017 9:24 AM

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 ?


--------------060907050102090905050903--