Re: performance problem on big tables

public inbox for [email protected]  
help / color / mirror / Atom feed

From: Mariel Cherkassky <[email protected]>
To: Claudio Freire <[email protected]>
Cc: Daniel Blanch Bataller <[email protected]>
Cc: Scott Marlowe <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: performance problem on big tables
Date: Sun, 20 Aug 2017 14:32:09 +0300
Message-ID: <CA+t6e1k2b_L3R-+gQ+4isOOFakdTkWif1tyWauRM-8rw_jsC0Q@mail.gmail.com> (raw)
In-Reply-To: <CA+t6e1n6nqwyh1Jb1WTU6kYr9kgkdZe+GABY9XgDozixoS08DA@mail.gmail.com>
References: <CA+t6e1m06=RkReCDxdyjwAMwVwuKxakV8TKmQ2cnMLFYbOjMCA@mail.gmail.com>
	<CA+t6e1nG8bF4-hrvjijhJ_nC5OXmw32eXNYdxuYMkPSk2QLrag@mail.gmail.com>
	<CAOR=d=1JE-GT6iv__uHWQ9DcPcGS5LBTq_gpiKrvjw3moDHuZg@mail.gmail.com>
	<CA+t6e1k7MOf0vgs2t=tWZ-tJ3MGmTqq1bkcQseRCZ9n+u-QBkQ@mail.gmail.com>
	<[email protected]>
	<CA+t6e1nvx3KfKP_QAKQ+WvzJF5k3h5Uq+F674bVk5PjWv2enrw@mail.gmail.com>
	<[email protected]>
	<CA+t6e1nXJz34ECUfJAkq7JQVkzr2mg-cbsW_acBXcyM2eyBKBw@mail.gmail.com>
	<[email protected]>
	<CA+t6e1kXc1=z-4yAB+jBXpLNVCvTO2d2W69Re-N4YrckdXoDvg@mail.gmail.com>
	<CA+t6e1kijisMi3cjNy3HEEvDMMH_u9WkzS3ViQGZ9cPTZ3YtWQ@mail.gmail.com>
	<CAGTBQpaXBW_edddHy649yMVL81zDo=rY8MP1ZxQDxnUZ=9hGaw@mail.gmail.com>
	<CA+t6e1nB58DtiMmNeY_Nh+wjRyT_WOFFxNJRJK=M30d4HAWJnQ@mail.gmail.com>
	<CA+t6e1n6nqwyh1Jb1WTU6kYr9kgkdZe+GABY9XgDozixoS08DA@mail.gmail.com>
List-Unsubscribe:  <mailto:[email protected]?body=unsub%20pgsql-performance>

When I run copy from local table the speed of the writing is 22 M/S. When I
use the copy from remote_oracle_Table it writes 3 M/s. SCP between the
servers coppies very fast. How should I continue ?

2017-08-20 14:00 GMT+03:00 Mariel Cherkassky <[email protected]>:

> 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 <[email protected]>:
>
>> 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 <[email protected]>:
>>
>>> On Thu, Aug 17, 2017 at 6:00 AM, Mariel Cherkassky
>>> <[email protected]> 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.
>>>
>>
>>

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected]
  Subject: Re: performance problem on big tables
  In-Reply-To: <CA+t6e1k2b_L3R-+gQ+4isOOFakdTkWif1tyWauRM-8rw_jsC0Q@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox