Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1djhdm-0005GI-Og for pgsql-performance@arkaria.postgresql.org; Mon, 21 Aug 2017 08:00: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 1djhdm-0000z1-5O for pgsql-performance@arkaria.postgresql.org; Mon, 21 Aug 2017 08:00:54 +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 1djhdk-0000sp-Am for pgsql-performance@postgresql.org; Mon, 21 Aug 2017 08:00:52 +0000 Received: from mail-wr0-x22a.google.com ([2a00:1450:400c:c0c::22a]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1djhdc-0007u9-Sg for pgsql-performance@postgresql.org; Mon, 21 Aug 2017 08:00:51 +0000 Received: by mail-wr0-x22a.google.com with SMTP id p14so22962022wrg.1 for ; Mon, 21 Aug 2017 01:00:44 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:in-reply-to:references:from:date:message-id:subject:cc; bh=OBI+XtF25LaCq34Ti09s+aODbQFA9IuFIz+aKn+sRN8=; b=m2yudMBT4lpnNi2oIBrvqkmdTZb9zIrE6jHJsR8nYGmj06w2KvBBuIL7yZNr4IUGE0 WUOHR/naAhDDubwAVKyM/B8pB50e/gaJSuCv9v+JoJBOJ12acE+pf/qFtqkx5fG1E8ow rxReHNaz32EVSc55NPUwvoTCHCLHDrscuhCNbunCFrJmAWmtzXhm9csGzWcHK+Mp64pH tRkGGe1VVybuXCG6dj9aeMgXBscwPFp1lDRj9mE78OOujsBi+HFOjkg7OzP4JPM/N0Wo aOmikrJKW6oUDPE/gde2KQ6wCCBVx9EAC4xUpfMi7H3F/6pttIa4dQAgY1w1FG4WGmK5 /vhA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:in-reply-to:references:from:date :message-id:subject:cc; bh=OBI+XtF25LaCq34Ti09s+aODbQFA9IuFIz+aKn+sRN8=; b=eRqQGPT1J3EjH+JVTbazdd4fOVT1UfBonB/xV1tE6HzFHIewyF0s1vCUqldZQ9yFru 7q0AKAaR4AK0f4rrNNpLKCCVaj/YS3Y2twm0UpCRFbaEqdrM7o5Nvjbt5H8AHD7JX/5x H4fbVt9V10TXwySb5q106hRB+9SLzvImC9USEdBDZCdDqI/mRfITint17qjkkVAPKArT INdecAP5KFkyxav1L259lNooVHfUBtXpmUXuisfCBm64B08xTWVqPCgCR5lKChoi67d8 9US9hvyjmgmO0MiZ5m2TvOA/1qFknDuHJPWUzQHvdJbTeKk6FMqvl8KmHsU3ukVzKrWL zPjg== X-Gm-Message-State: AHYfb5gT8U/O9bAivX8voUmpOWcE/+A8V4lNLIb1n0Mi875R/RTzL6C0 f7QbF5HTmqtMSHZC7cRaOFbnzkUAed5DwSQ= X-Received: by 10.223.153.244 with SMTP id y107mr10711371wrb.168.1503302442745; Mon, 21 Aug 2017 01:00:42 -0700 (PDT) MIME-Version: 1.0 Received: by 10.28.184.85 with HTTP; Mon, 21 Aug 2017 01:00:41 -0700 (PDT) In-Reply-To: References: <88350892-C4AB-4191-9F8E-DB7CFA81F6C6@gmail.com> <00990FBB-D288-4878-8B8E-36E24F808F2C@gmail.com> From: Mariel Cherkassky Date: Mon, 21 Aug 2017 11:00:41 +0300 Message-ID: Subject: Re: performance problem on big tables Cc: "pgsql-performance@postgresql.org" Content-Type: multipart/alternative; boundary="f403045f54c01701ec05573ee0c8" 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 --f403045f54c01701ec05573ee0c8 Content-Type: text/plain; charset="UTF-8" To summarize, I still have performance problems. My current situation : I'm trying to copy the data of many tables in the oracle database into my postgresql tables. I'm doing so by running insert into local_postgresql_temp select * from remote_oracle_table. The performance of this operation are very slow and I tried to check the reason for that and mybe choose a different alternative. 1)First method - Insert into local_postgresql_table select * from remote_oracle_table this generated total disk write of 7 M/s and actual disk write of 4 M/s(iotop). For 32G table it took me 2 hours and 30 minutes. 2)second method - copy (select * from oracle_remote_table) to /tmp/dump generates total disk write of 4 M/s and actuval disk write of 100 K/s. The copy utility suppose to be very fast but it seems very slow. -When I run copy from the local dump, the reading is very fast 300 M/s. -I created a 32G file on the oracle server and used scp to copy it and it took me a few minutes. -The wals directory is located on a different file system. The parameters I assigned : min_parallel_relation_size = 200MB max_parallel_workers_per_gather = 5 max_worker_processes = 8 effective_cache_size = 12GB work_mem = 128MB maintenance_work_mem = 4GB shared_buffers = 2000MB RAM : 16G CPU CORES : 8 HOW can I increase the writes ? How can I get the data faster from the oracle database to my postgresql database? 2017-08-20 14:00 GMT+03:00 Mariel Cherkassky : > I realized something weird. When I`m preforming the copy utility of > postgresql in order to create dump from a local table in my postgresql db > it takes for 32G table 20 minutes. When I try to use copy for a foregin > table (on oracle database) It takes more than 2 hours.. During the copy > operation from the foreign table I dont see alot of write operations, with > iotop i see that its writes 3 M/s. What else I can check ? > > 2017-08-20 9:39 GMT+03:00 Mariel Cherkassky : > >> This server is dedicated to be a postgresql production database, >> therefore postgresql is the only thing the runs on the server. The fs that >> I`m using is xfs. I`ll add two different disks - one for the wals and one >> for the temp tablespace. Regarding the disk, what size should they be >> considering that the database size is about 250G. Does 16G of ram >> considered little ? I installed iotop and I see that postgresql writer is >> writing most of the time and above all. >> >> I mentioned that I perform alot of insert into table select * from table. >> Before that I remove indexes,constraints and truncate the table. Should I >> run vacuum before or after the operation ? >> >> 2017-08-17 19:37 GMT+03:00 Claudio Freire : >> >>> On Thu, Aug 17, 2017 at 6:00 AM, Mariel Cherkassky >>> wrote: >>> > 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 ? >>> >>> Do you have concurrent activity on that server? >>> >>> What filesystem are you using wherever the data is sitting? >>> >>> If you've got concurrent fsyncs happening, some filesystems handle >>> that poorly. When you've got WAL and data mixed in a single disk, or >>> worse, filesystem, it happens often that the filesystem won't handle >>> the write barriers for the WAL efficiently. I/O gets intermingled with >>> bulk operations, and even small fsyncs will have to flush writes from >>> bulk operations, which makes a mess of things. >>> >>> It is a very good idea, and in fact a recommended practice, to put WAL >>> on its own disk for that reason mainly. >>> >>> With that little RAM, you'll also probably cause a lot of I/O in temp >>> files, so I'd also recommend setting aside another disk for a temp >>> tablespace so that I/O doesn't block other transactions as well. >>> >>> This is all assuming you've got concurrent activity on the server. If >>> not, install iotop and try to see who's causing that much I/O. >>> >> >> --f403045f54c01701ec05573ee0c8 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
To summarize, I still have performance pr= oblems. My current situation :=C2=A0

I'm trying to copy the data of many tables in the o= racle database into my postgresql tables. I'm doing so by running=C2=A0= i= nsert into local_postgresql_temp select * from remote_oracle_table. = The performance of this operation are very slow and I tried to check the re= ason for that and mybe choose a different alternative.

1)First method -=C2=A0Insert into local_postgresql_table = select * from remote_oracle_table=C2=A0this generated total disk wri= te of 7 M/s and actual disk write of 4 M/s(iotop). For 32G table it took me= 2 hours and 30 minutes.

2)second me= thod -=C2=A0copy (select * from oracle_remote_table) to /tmp/dump=C2= =A0generates total disk write of 4 M/s and actuval disk write of 100 K/s. T= he copy utility suppose to be very fast but it seems very slow.

-When I run copy from the local dump, the readi= ng is very fast 300 M/s.

-I created = a 32G file on the oracle server and used scp to copy it and it took me a fe= w minutes.

-The wals directory is lo= cated on a different file system. The parameters I assigned :

min_pa=
rallel_relation_size =3D 200MB
max_parallel_workers_per_gather =3D 5=
=20
max_worker_processes =3D 8=20
effective_cache_size =3D 12GB
work_mem =3D 128MB
maintenance_work_mem =3D 4GB
shared_buffers =3D 2000MB
RAM :=
 16G
CPU CORES : 8

HOW can I increase the writes ? How can I get the = data faster from the oracle database to my postgresql database?


2017-08-20 14:00 GMT+03:00 Mariel Cherkassky <mariel.cherkas= sky@gmail.com>:
I realized= something weird. When I`m preforming the copy utility of postgresql in ord= er to create dump from a local table in my postgresql db it takes for 32G t= able 20 minutes. When I try to use copy for a foregin table (on oracle data= base) It takes more than 2 hours.. During the copy operation from the forei= gn table I dont see alot of write operations, with iotop i see that its wri= tes 3 M/s. What else I can check ?=C2=A0
<= div class=3D"h5">

2017-08-20 9:39 GMT+03:00 Mariel Cherkassky <ma= riel.cherkassky@gmail.com>:
This server is dedicated to be a postgresql production database, therefor= e postgresql is the only thing the runs on the server. The fs that I`m usin= g is xfs. I`ll add two different disks - one for the wals and one for the t= emp tablespace. Regarding the disk, what size should they be considering th= at the database size is about 250G. Does 16G of ram considered little ? I i= nstalled iotop and I see that postgresql writer is writing most of the time= and above all.

I mentione= d that I perform alot of insert into table select * from table. Before that= I remove indexes,constraints and truncate the table. Should I run vacuum b= efore or after the operation ?=C2=A0

2017-08-17 19:37 G= MT+03:00 Claudio Freire <klaussfreire@gmail.com>:
=
On Thu, Aug 17, 2017 at 6:00 AM, Marie= l Cherkassky
<mariel= .cherkassky@gmail.com> wrote:
> I checked with the storage team in the company and they saw that I hav= e alot
> of io on the server. How should I reduce the io that the postgresql us= es ?

Do you have concurrent activity on that server?

What filesystem are you using wherever the data is sitting?

If you've got concurrent fsyncs happening, some filesystems handle
that poorly. When you've got WAL and data mixed in a single disk, or worse, filesystem, it happens often that the filesystem won't handle the write barriers for the WAL efficiently. I/O gets intermingled with
bulk operations, and even small fsyncs will have to flush writes from
bulk operations, which makes a mess of things.

It is a very good idea, and in fact a recommended practice, to put WAL
on its own disk for that reason mainly.

With that little RAM, you'll also probably cause a lot of I/O in temp files, so I'd also recommend setting aside another disk for a temp
tablespace so that I/O doesn't block other transactions as well.

This is all assuming you've got concurrent activity on the server. If not, install iotop and try to see who's causing that much I/O.

--f403045f54c01701ec05573ee0c8--