public inbox for [email protected]
help / color / mirror / Atom feedFrom: Mariel Cherkassky <[email protected]>
To: Daniel Blanch Bataller <[email protected]>
Cc: Scott Marlowe <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: performance problem on big tables
Date: Wed, 16 Aug 2017 17:32:25 +0300
Message-ID: <CA+t6e1nXJz34ECUfJAkq7JQVkzr2mg-cbsW_acBXcyM2eyBKBw@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
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]>
List-Unsubscribe: <mailto:[email protected]?body=unsub%20pgsql-performance>
My server is virtual and it have virtual hd from a vnx storage machine. The
logs and the data are on the same disk.
2017-08-16 17:04 GMT+03:00 Daniel Blanch Bataller <
[email protected]>:
> Considering it has to write logs and data at checkpoints I don’t see it
> particularly slow compared to the extract phase. What kind of disks you
> have SSD or regular disks? Different disks for ltransaction logs and data?
>
>
> El 16 ago 2017, a las 15:54, Mariel Cherkassky <
> [email protected]> escribió:
>
> I run the copy command via psql to create a local dump of a 3G table and
> it took me 134059.732ms =~2 minutes. After that I imported the data via
> copy and it took 458648.677ms =~7 minutes. So the copy command works but
> pretty slow.
>
> 2017-08-16 16:08 GMT+03:00 Daniel Blanch Bataller <
> [email protected]>:
>
>> See if the copy command is actually working, copy should be very fast
>> from your local disk.
>>
>>
>> El 16 ago 2017, a las 14:26, Mariel Cherkassky <
>> [email protected]> escribió:
>>
>>
>> After all the changes of the memory parameters the same operation(without
>> the copy utility) didnt run much faster - it took one minute less. I made
>> a test with the copy command (without the 'with binary') and it took 1.5
>> hours to create the dumpfile in my local postgresql server. Then I tried to
>> run the copy from the local dump and it is already running two hours and it
>> didnt even finish. I looked at the server log and I saw that I run the copy
>> command at 13:18:05, 3 minutes later checkpoint started and completed and
>> there are no messages in the log after that. What can I do ? Improving the
>> memory parameters and the memory on the server didnt help and for now the
>> copy command doesnt help either.
>>
>>
>>
>>
>> 2017-08-15 20:14 GMT+03:00 Scott Marlowe <[email protected]>:
>>
>>> On Tue, Aug 15, 2017 at 4:06 AM, Mariel Cherkassky
>>> <[email protected]> wrote:
>>> > Hi,
>>> > So I I run the cheks that jeff mentioned :
>>> > \copy (select * from oracle_remote_table) to /tmp/tmp with binary - 1
>>> hour
>>> > and 35 minutes
>>>
>>> So 26G takes 95 minutes, or 27 MB/minute or 456k/second? Sound about
>>> right (it's early, I haven't had enough coffee please check my math).
>>> That's pretty slow unless you're working across pretty big distances
>>> with mediocre connections. My home internet downloads about 100MB/s
>>> by comparison.
>>>
>>> > \copy local_postresql_table from /tmp/tmp with binary - Didnt run
>>> because
>>> > the remote oracle database is currently under maintenance work.
>>>
>>> You shouldn't need the remote oracle server if you've already copied
>>> it over, you're just copying from local disk into the local pgsql db.
>>> Unless I'm missing something.
>>>
>>> > So I decided to follow MichaelDBA tips and I set the ram on my machine
>>> to
>>> > 16G and I configured the effective_cache memory to 14G,tshared_buffer
>>> to be
>>> > 2G and maintenance_work_mem to 4G.
>>>
>>> Good settings. Maybe set work_mem to 128MB or so while you're at it.
>>>
>>> > I started running the copy checks again and for now it coppied 5G in 10
>>> > minutes. I have some questions :
>>> > 1)When I run insert into local_postresql_table select * from
>>> > remote_oracle_table I insert that data as bulk to the local table or
>>> row by
>>> > row ? If the answer as bulk than why copy is a better option for this
>>> case
>>> > ?
>>>
>>> insert into select from oracle remote is one big copy, but it will
>>> take at least as long as copying from oracle to the local network
>>> took. Compare that to the same thing but use file_fdw on the file
>>> locally.
>>>
>>> > 2)The copy from dump into the postgresql database should take less
>>> time than
>>> > the copy to dump ?
>>>
>>> Yes. The copy from Oracle to your local drive is painfully slow for a
>>> modern network connection.
>>>
>>> > 3)What do you think about the new memory parameters that I cofigured ?
>>>
>>> They should be OK. I'm more worried about the performance of the io
>>> subsystem tbh.
>>>
>>
>>
>>
>
>
view thread (39+ messages) latest in thread
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]
Subject: Re: performance problem on big tables
In-Reply-To: <CA+t6e1nXJz34ECUfJAkq7JQVkzr2mg-cbsW_acBXcyM2eyBKBw@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