Re: [PERFORM] performance problem on big tables - Mailing list pgsql-performance

Hi Daniel,

I already tried to set the destination table to unlogged - it improved the performance slightly. Is there a way to make sure that I/O is the problem ? 

2017-08-17 0:46 GMT+03:00 Daniel Blanch Bataller <daniel.blanch.bataller@gmail.com>:

Seems your disks are too slow. On my laptop (nothing special, just one disk) using COPY I can dump 3G in ~ 20 secs, loading takes 120 secs, bare copying 3G takes 10 secs. 

Similar proportion you had, but much faster. 

confirm I/O is your bottleneck, and tell us how you solved your problem

Anyway, You can cut import time by half if you set your destination table to unlogged (postgres will write half the data, it will save the transaction log writing). Remember to set it to logged when finished!!

Regards,

Daniel

El 16 ago 2017, a las 16:32, Mariel Cherkassky <mariel.cherkassky@gmail.com> escribió:

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 <daniel.blanch.bataller@gmail.com>:

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 <mariel.cherkassky@gmail.com> 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 <daniel.blanch.bataller@gmail.com>:

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 <mariel.cherkassky@gmail.com> 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 <scott.marlowe@gmail.com>:

On Tue, Aug 15, 2017 at 4:06 AM, Mariel Cherkassky
<mariel.cherkassky@gmail.com> 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.