public inbox for [email protected]
help / color / mirror / Atom feedFrom: 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:00:51 +0300
Message-ID: <CA+t6e1n6nqwyh1Jb1WTU6kYr9kgkdZe+GABY9XgDozixoS08DA@mail.gmail.com> (raw)
In-Reply-To: <CA+t6e1nB58DtiMmNeY_Nh+wjRyT_WOFFxNJRJK=M30d4HAWJnQ@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>
List-Unsubscribe: <mailto:[email protected]?body=unsub%20pgsql-performance>
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+t6e1n6nqwyh1Jb1WTU6kYr9kgkdZe+GABY9XgDozixoS08DA@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