public inbox for [email protected]
help / color / mirror / Atom feedperformance problem on big tables
39+ messages / 10 participants
[nested] [flat]
* performance problem on big tables
@ 2017-08-14 13:24 Mariel Cherkassky <[email protected]>
2017-08-14 15:10 ` Re: performance problem on big tables MichaelDBA <[email protected]>
2017-08-14 15:11 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-14 16:39 ` Re: performance problem on big tables Jeff Janes <[email protected]>
2017-08-15 10:06 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
0 siblings, 4 replies; 39+ messages in thread
From: Mariel Cherkassky @ 2017-08-14 13:24 UTC (permalink / raw)
To: pgsql-performance
I have performance issues with two big tables. Those tables are located on
an oracle remote database. I'm running the quert : insert into
local_postgresql_table select * from oracle_remote_table.
The first table has 45M records and its size is 23G. The import of the data
from the oracle remote database is taking 1 hour and 38 minutes. After that
I create 13 regular indexes on the table and it takes 10 minutes per table
->2 hours and 10 minutes in total.
The second table has 29M records and its size is 26G. The import of the
data from the oracle remote database is taking 2 hours and 30 minutes. The
creation of the indexes takes 1 hours and 30 minutes (some are indexes on
one column and the creation takes 5 min and some are indexes on multiples
column and it takes 11 min.
Those operation are very problematic for me and I'm searching for a
solution to improve the performance. The parameters I assigned :
min_parallel_relation_size = 200MB
max_parallel_workers_per_gather = 5
max_worker_processes = 8
effective_cache_size = 2500MB
work_mem = 16MB
maintenance_work_mem = 1500MB
shared_buffers = 2000MB
RAM : 5G
CPU CORES : 8
*-I tried running select count(*) from table in oracle and in postgresql
the running time is almost equal.*
*-Before importing the data I drop the indexes and the constraints.*
*-I tried to copy a 23G file from the oracle server to the postgresql
server and it took me 12 minutes.*
Please advice how can I continue ? How can I improve something in this
operation ?
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: performance problem on big tables
2017-08-14 13:24 performance problem on big tables Mariel Cherkassky <[email protected]>
@ 2017-08-14 15:10 ` MichaelDBA <[email protected]>
3 siblings, 0 replies; 39+ messages in thread
From: MichaelDBA @ 2017-08-14 15:10 UTC (permalink / raw)
To: Mariel Cherkassky <[email protected]>; +Cc: pgsql-performance
Total RAM on your host is 5GB, really? Before touching anything else,
increase your RAM. That will be your big performance boost right
there. Then, you can "up" your effective_cache_size and
maintenance_work_mem.
Regards,
Michael Vitale
> Mariel Cherkassky <mailto:[email protected]>
> Monday, August 14, 2017 9:24 AM
>
> I have performance issues with two big tables. Those tables are
> located on an oracle remote database. I'm running the quert : |insert
> into local_postgresql_table select * from oracle_remote_table.|
>
> The first table has 45M records and its size is 23G. The import of the
> data from the oracle remote database is taking 1 hour and 38 minutes.
> After that I create 13 regular indexes on the table and it takes 10
> minutes per table ->2 hours and 10 minutes in total.
>
> The second table has 29M records and its size is 26G. The import of
> the data from the oracle remote database is taking 2 hours and 30
> minutes. The creation of the indexes takes 1 hours and 30 minutes
> (some are indexes on one column and the creation takes 5 min and some
> are indexes on multiples column and it takes 11 min.
>
> Those operation are very problematic for me and I'm searching for a
> solution to improve the performance. The parameters I assigned :
>
> min_parallel_relation_size =200MB
> ||
> max_parallel_workers_per_gather =5
> max_worker_processes =8
> effective_cache_size =2500MB
> work_mem =16MB
> maintenance_work_mem =1500MB
> shared_buffers =2000MB
> RAM :5G
> CPU CORES :8
>
> *-I tried running select count(*) from table in oracle and in
> postgresql the running time is almost equal.*
>
> *-Before importing the data I drop the indexes and the constraints.*
>
> *-I tried to copy a 23G file from the oracle server to the postgresql
> server and it took me 12 minutes.*
>
> Please advice how can I continue ? How can I improve something in this
> operation ?
>
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: performance problem on big tables
2017-08-14 13:24 performance problem on big tables Mariel Cherkassky <[email protected]>
@ 2017-08-14 15:11 ` Daniel Blanch Bataller <[email protected]>
2017-08-14 15:45 ` Re: performance problem on big tables Rick Otten <[email protected]>
3 siblings, 1 reply; 39+ messages in thread
From: Daniel Blanch Bataller @ 2017-08-14 15:11 UTC (permalink / raw)
To: Mariel Cherkassky <[email protected]>; +Cc: pgsql-performance
Hi.
In general using COPY is *much faster* than anything else. You can even split the data load and run it in parallel, start with as many jobs as processors you have. Same with indexes, run them in parallel. With parallel I mean various psql running at the same time.
Tuning postgres will help too, but not as much as using COPY.
https://www.postgresql.org/docs/9.6/static/performance-tips.html <https://www.postgresql.org/docs/9.6/static/performance-tips.html;
https://www.postgresql.org/docs/9.6/static/populate.html <https://www.postgresql.org/docs/9.6/static/populate.html;
https://www.postgresql.org/docs/9.6/static/populate.html#POPULATE-COPY-FROM
Regards,
Daniel Blanch..
> El 14 ago 2017, a las 15:24, Mariel Cherkassky <[email protected]> escribió:
>
> I have performance issues with two big tables. Those tables are located on an oracle remote database. I'm running the quert : insert into local_postgresql_table select * from oracle_remote_table.
>
> The first table has 45M records and its size is 23G. The import of the data from the oracle remote database is taking 1 hour and 38 minutes. After that I create 13 regular indexes on the table and it takes 10 minutes per table ->2 hours and 10 minutes in total.
>
> The second table has 29M records and its size is 26G. The import of the data from the oracle remote database is taking 2 hours and 30 minutes. The creation of the indexes takes 1 hours and 30 minutes (some are indexes on one column and the creation takes 5 min and some are indexes on multiples column and it takes 11 min.
>
> Those operation are very problematic for me and I'm searching for a solution to improve the performance. The parameters I assigned :
>
> min_parallel_relation_size = 200MB
> max_parallel_workers_per_gather = 5
> max_worker_processes = 8
> effective_cache_size = 2500MB
> work_mem = 16MB
> maintenance_work_mem = 1500MB
> shared_buffers = 2000MB
> RAM : 5G
> CPU CORES : 8
> -I tried running select count(*) from table in oracle and in postgresql the running time is almost equal.
>
> -Before importing the data I drop the indexes and the constraints.
>
> -I tried to copy a 23G file from the oracle server to the postgresql server and it took me 12 minutes.
>
> Please advice how can I continue ? How can I improve something in this operation ?
>
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: performance problem on big tables
2017-08-14 13:24 performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-14 15:11 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
@ 2017-08-14 15:45 ` Rick Otten <[email protected]>
0 siblings, 0 replies; 39+ messages in thread
From: Rick Otten @ 2017-08-14 15:45 UTC (permalink / raw)
To: Daniel Blanch Bataller <[email protected]>; +Cc: Mariel Cherkassky <[email protected]>; pgsql-performance
Moving that many gigs of data across your network could also take a long
time simply depending on your network configuration. Before spending a
huge amount of energy tuning postgresql, I'd probably look at how long it
takes to simply copy 20 or 30 G of data between the two machines.
> El 14 ago 2017, a las 15:24, Mariel Cherkassky <
> [email protected]> escribió:
>
> I have performance issues with two big tables. Those tables are located on
> an oracle remote database. I'm running the quert : insert into
> local_postgresql_table select * from oracle_remote_table.
>
> The first table has 45M records and its size is 23G. The import of the
> data from the oracle remote database is taking 1 hour and 38 minutes. After
> that I create 13 regular indexes on the table and it takes 10 minutes per
> table ->2 hours and 10 minutes in total.
>
> The second table has 29M records and its size is 26G. The import of the
> data from the oracle remote database is taking 2 hours and 30 minutes. The
> creation of the indexes takes 1 hours and 30 minutes (some are indexes on
> one column and the creation takes 5 min and some are indexes on multiples
> column and it takes 11 min.
>
>
>
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: performance problem on big tables
2017-08-14 13:24 performance problem on big tables Mariel Cherkassky <[email protected]>
@ 2017-08-14 16:39 ` Jeff Janes <[email protected]>
3 siblings, 0 replies; 39+ messages in thread
From: Jeff Janes @ 2017-08-14 16:39 UTC (permalink / raw)
To: Mariel Cherkassky <[email protected]>; +Cc: pgsql-performance
On Mon, Aug 14, 2017 at 6:24 AM, Mariel Cherkassky <
[email protected]> wrote:
> I have performance issues with two big tables. Those tables are located on
> an oracle remote database. I'm running the quert : insert into
> local_postgresql_table select * from oracle_remote_table.
>
> The first table has 45M records and its size is 23G. The import of the
> data from the oracle remote database is taking 1 hour and 38 minutes.
>
To investigate this, I'd decouple the two steps and see how long each one
takes:
\copy (select * from oracle_remote_table) to /tmp/tmp with binary
\copy local_postresql_table from /tmp/tmp with binary
Cheers,
Jeff
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: performance problem on big tables
2017-08-14 13:24 performance problem on big tables Mariel Cherkassky <[email protected]>
@ 2017-08-15 10:06 ` Mariel Cherkassky <[email protected]>
2017-08-15 16:13 ` Re: performance problem on big tables Jeff Janes <[email protected]>
2017-08-15 17:14 ` Re: performance problem on big tables Scott Marlowe <[email protected]>
3 siblings, 2 replies; 39+ messages in thread
From: Mariel Cherkassky @ 2017-08-15 10:06 UTC (permalink / raw)
To: pgsql-performance
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
\copy local_postresql_table from /tmp/tmp with binary - Didnt run because
the remote oracle database is currently under maintenance work.
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.
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
?
2)The copy from dump into the postgresql database should take less time
than the copy to dump ?
3)What do you think about the new memory parameters that I cofigured ?
2017-08-14 16:24 GMT+03:00 Mariel Cherkassky <[email protected]>:
> I have performance issues with two big tables. Those tables are located on
> an oracle remote database. I'm running the quert : insert into
> local_postgresql_table select * from oracle_remote_table.
>
> The first table has 45M records and its size is 23G. The import of the
> data from the oracle remote database is taking 1 hour and 38 minutes. After
> that I create 13 regular indexes on the table and it takes 10 minutes per
> table ->2 hours and 10 minutes in total.
>
> The second table has 29M records and its size is 26G. The import of the
> data from the oracle remote database is taking 2 hours and 30 minutes. The
> creation of the indexes takes 1 hours and 30 minutes (some are indexes on
> one column and the creation takes 5 min and some are indexes on multiples
> column and it takes 11 min.
>
> Those operation are very problematic for me and I'm searching for a
> solution to improve the performance. The parameters I assigned :
>
> min_parallel_relation_size = 200MB
> max_parallel_workers_per_gather = 5
> max_worker_processes = 8
> effective_cache_size = 2500MB
> work_mem = 16MB
> maintenance_work_mem = 1500MB
> shared_buffers = 2000MB
> RAM : 5G
> CPU CORES : 8
>
> *-I tried running select count(*) from table in oracle and in postgresql
> the running time is almost equal.*
>
> *-Before importing the data I drop the indexes and the constraints.*
>
> *-I tried to copy a 23G file from the oracle server to the postgresql
> server and it took me 12 minutes.*
>
> Please advice how can I continue ? How can I improve something in this
> operation ?
>
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: performance problem on big tables
2017-08-14 13:24 performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-15 10:06 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
@ 2017-08-15 16:13 ` Jeff Janes <[email protected]>
2017-08-15 17:44 ` Re: performance problem on big tables Pavel Stehule <[email protected]>
1 sibling, 1 reply; 39+ messages in thread
From: Jeff Janes @ 2017-08-15 16:13 UTC (permalink / raw)
To: Mariel Cherkassky <[email protected]>; +Cc: pgsql-performance
On Tue, Aug 15, 2017 at 3: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
> \copy local_postresql_table from /tmp/tmp with binary - Didnt run because
> the remote oracle database is currently under maintenance work.
>
The "\copy...from" doesn't depend on oracle, it would be only depend on
local file system (/tmp/tmp), provided that the "\copy...to" finished.
Anyway, given the length of time it took, I think you can conclude the
bottleneck is in oracle_fdw itself, or in Oracle, or the network.
Cheers,
Jeff
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: performance problem on big tables
2017-08-14 13:24 performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-15 10:06 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-15 16:13 ` Re: performance problem on big tables Jeff Janes <[email protected]>
@ 2017-08-15 17:44 ` Pavel Stehule <[email protected]>
0 siblings, 0 replies; 39+ messages in thread
From: Pavel Stehule @ 2017-08-15 17:44 UTC (permalink / raw)
To: Jeff Janes <[email protected]>; +Cc: Mariel Cherkassky <[email protected]>; pgsql-performance
2017-08-15 18:13 GMT+02:00 Jeff Janes <[email protected]>:
> On Tue, Aug 15, 2017 at 3: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
>> \copy local_postresql_table from /tmp/tmp with binary - Didnt run because
>> the remote oracle database is currently under maintenance work.
>>
>
> The "\copy...from" doesn't depend on oracle, it would be only depend on
> local file system (/tmp/tmp), provided that the "\copy...to" finished.
> Anyway, given the length of time it took, I think you can conclude the
> bottleneck is in oracle_fdw itself, or in Oracle, or the network.
>
dumping from Oracle is not fast - I seen it when oracle_fdw or ora2pg cases.
Regards
Pavel
>
> Cheers,
>
> Jeff
>
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: performance problem on big tables
2017-08-14 13:24 performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-15 10:06 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
@ 2017-08-15 17:14 ` Scott Marlowe <[email protected]>
2017-08-16 12:26 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
1 sibling, 1 reply; 39+ messages in thread
From: Scott Marlowe @ 2017-08-15 17:14 UTC (permalink / raw)
To: Mariel Cherkassky <[email protected]>; +Cc: pgsql-performance
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.
--
Sent via pgsql-performance mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: performance problem on big tables
2017-08-14 13:24 performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-15 10:06 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-15 17:14 ` Re: performance problem on big tables Scott Marlowe <[email protected]>
@ 2017-08-16 12:26 ` Mariel Cherkassky <[email protected]>
2017-08-16 13:08 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
0 siblings, 1 reply; 39+ messages in thread
From: Mariel Cherkassky @ 2017-08-16 12:26 UTC (permalink / raw)
To: Scott Marlowe <[email protected]>; +Cc: pgsql-performance
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.
>
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: performance problem on big tables
2017-08-14 13:24 performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-15 10:06 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-15 17:14 ` Re: performance problem on big tables Scott Marlowe <[email protected]>
2017-08-16 12:26 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
@ 2017-08-16 13:08 ` Daniel Blanch Bataller <[email protected]>
2017-08-16 13:54 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
0 siblings, 1 reply; 39+ messages in thread
From: Daniel Blanch Bataller @ 2017-08-16 13:08 UTC (permalink / raw)
To: Mariel Cherkassky <[email protected]>; +Cc: Scott Marlowe <[email protected]>; pgsql-performance
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] <mailto:[email protected]>>:
> On Tue, Aug 15, 2017 at 4:06 AM, Mariel Cherkassky
> <[email protected] <mailto:[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.
>
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: performance problem on big tables
2017-08-14 13:24 performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-15 10:06 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-15 17:14 ` Re: performance problem on big tables Scott Marlowe <[email protected]>
2017-08-16 12:26 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-16 13:08 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
@ 2017-08-16 13:54 ` Mariel Cherkassky <[email protected]>
2017-08-16 14:04 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
0 siblings, 1 reply; 39+ messages in thread
From: Mariel Cherkassky @ 2017-08-16 13:54 UTC (permalink / raw)
To: Daniel Blanch Bataller <[email protected]>; +Cc: Scott Marlowe <[email protected]>; pgsql-performance
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.
>>
>
>
>
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: performance problem on big tables
2017-08-14 13:24 performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-15 10:06 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-15 17:14 ` Re: performance problem on big tables Scott Marlowe <[email protected]>
2017-08-16 12:26 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-16 13:08 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-16 13:54 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
@ 2017-08-16 14:04 ` Daniel Blanch Bataller <[email protected]>
2017-08-16 14:32 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
0 siblings, 1 reply; 39+ messages in thread
From: Daniel Blanch Bataller @ 2017-08-16 14:04 UTC (permalink / raw)
To: Mariel Cherkassky <[email protected]>; +Cc: Scott Marlowe <[email protected]>; pgsql-performance
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] <mailto:[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] <mailto:[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] <mailto:[email protected]>>:
>> On Tue, Aug 15, 2017 at 4:06 AM, Mariel Cherkassky
>> <[email protected] <mailto:[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.
>>
>
>
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: performance problem on big tables
2017-08-14 13:24 performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-15 10:06 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-15 17:14 ` Re: performance problem on big tables Scott Marlowe <[email protected]>
2017-08-16 12:26 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-16 13:08 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-16 13:54 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-16 14:04 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
@ 2017-08-16 14:32 ` Mariel Cherkassky <[email protected]>
2017-08-16 21:46 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
0 siblings, 1 reply; 39+ messages in thread
From: Mariel Cherkassky @ 2017-08-16 14:32 UTC (permalink / raw)
To: Daniel Blanch Bataller <[email protected]>; +Cc: Scott Marlowe <[email protected]>; pgsql-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.
>>>
>>
>>
>>
>
>
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: performance problem on big tables
2017-08-14 13:24 performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-15 10:06 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-15 17:14 ` Re: performance problem on big tables Scott Marlowe <[email protected]>
2017-08-16 12:26 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-16 13:08 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-16 13:54 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-16 14:04 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-16 14:32 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
@ 2017-08-16 21:46 ` Daniel Blanch Bataller <[email protected]>
2017-08-17 06:25 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
0 siblings, 1 reply; 39+ messages in thread
From: Daniel Blanch Bataller @ 2017-08-16 21:46 UTC (permalink / raw)
To: Mariel Cherkassky <[email protected]>; +Cc: Scott Marlowe <[email protected]>; pgsql-performance
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 <[email protected]> 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 <[email protected] <mailto:[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] <mailto:[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] <mailto:[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] <mailto:[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] <mailto:[email protected]>>:
>>> On Tue, Aug 15, 2017 at 4:06 AM, Mariel Cherkassky
>>> <[email protected] <mailto:[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.
>>>
>>
>>
>
>
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: performance problem on big tables
2017-08-14 13:24 performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-15 10:06 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-15 17:14 ` Re: performance problem on big tables Scott Marlowe <[email protected]>
2017-08-16 12:26 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-16 13:08 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-16 13:54 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-16 14:04 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-16 14:32 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-16 21:46 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
@ 2017-08-17 06:25 ` Mariel Cherkassky <[email protected]>
2017-08-17 09:00 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
0 siblings, 1 reply; 39+ messages in thread
From: Mariel Cherkassky @ 2017-08-17 06:25 UTC (permalink / raw)
To: Daniel Blanch Bataller <[email protected]>; +Cc: Scott Marlowe <[email protected]>; 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 <
[email protected]>:
> 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 <
> [email protected]> 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 <
> [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.
>>>>
>>>
>>>
>>>
>>
>>
>
>
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: performance problem on big tables
2017-08-14 13:24 performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-15 10:06 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-15 17:14 ` Re: performance problem on big tables Scott Marlowe <[email protected]>
2017-08-16 12:26 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-16 13:08 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-16 13:54 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-16 14:04 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-16 14:32 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-16 21:46 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-17 06:25 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
@ 2017-08-17 09:00 ` Mariel Cherkassky <[email protected]>
2017-08-17 10:06 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-17 16:37 ` Re: performance problem on big tables Claudio Freire <[email protected]>
0 siblings, 2 replies; 39+ messages in thread
From: Mariel Cherkassky @ 2017-08-17 09:00 UTC (permalink / raw)
To: Daniel Blanch Bataller <[email protected]>; +Cc: Scott Marlowe <[email protected]>; pgsql-performance
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 ?
2017-08-17 9:25 GMT+03:00 Mariel Cherkassky <[email protected]>:
> 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 <
> [email protected]>:
>
>> 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 <
>> [email protected]> 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 <
>> [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.
>>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: performance problem on big tables
2017-08-14 13:24 performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-15 10:06 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-15 17:14 ` Re: performance problem on big tables Scott Marlowe <[email protected]>
2017-08-16 12:26 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-16 13:08 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-16 13:54 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-16 14:04 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-16 14:32 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-16 21:46 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-17 06:25 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-17 09:00 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
@ 2017-08-17 10:06 ` Daniel Blanch Bataller <[email protected]>
1 sibling, 0 replies; 39+ messages in thread
From: Daniel Blanch Bataller @ 2017-08-17 10:06 UTC (permalink / raw)
To: Mariel Cherkassky <[email protected]>; +Cc: Scott Marlowe <[email protected]>; pgsql-performance
I would just check how does it take to copy 3GB using an standard copy command. on my computer it took 10 secs.
> El 17 ago 2017, a las 11:00, Mariel Cherkassky <[email protected]> escribió:
>
> 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 ?
>
> 2017-08-17 9:25 GMT+03:00 Mariel Cherkassky <[email protected] <mailto:[email protected]>>:
> 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 <[email protected] <mailto:[email protected]>>:
> 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 <[email protected] <mailto:[email protected]>> 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 <[email protected] <mailto:[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] <mailto:[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] <mailto:[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] <mailto:[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] <mailto:[email protected]>>:
>>>> On Tue, Aug 15, 2017 at 4:06 AM, Mariel Cherkassky
>>>> <[email protected] <mailto:[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.
>>>>
>>>
>>>
>>
>>
>
>
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: performance problem on big tables
2017-08-14 13:24 performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-15 10:06 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-15 17:14 ` Re: performance problem on big tables Scott Marlowe <[email protected]>
2017-08-16 12:26 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-16 13:08 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-16 13:54 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-16 14:04 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-16 14:32 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-16 21:46 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-17 06:25 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-17 09:00 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
@ 2017-08-17 16:37 ` Claudio Freire <[email protected]>
2017-08-20 06:39 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
1 sibling, 1 reply; 39+ messages in thread
From: Claudio Freire @ 2017-08-17 16:37 UTC (permalink / raw)
To: Mariel Cherkassky <[email protected]>; +Cc: Daniel Blanch Bataller <[email protected]>; Scott Marlowe <[email protected]>; pgsql-performance
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.
--
Sent via pgsql-performance mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: performance problem on big tables
2017-08-14 13:24 performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-15 10:06 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-15 17:14 ` Re: performance problem on big tables Scott Marlowe <[email protected]>
2017-08-16 12:26 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-16 13:08 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-16 13:54 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-16 14:04 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-16 14:32 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-16 21:46 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-17 06:25 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-17 09:00 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-17 16:37 ` Re: performance problem on big tables Claudio Freire <[email protected]>
@ 2017-08-20 06:39 ` Mariel Cherkassky <[email protected]>
2017-08-20 11:00 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
0 siblings, 1 reply; 39+ messages in thread
From: Mariel Cherkassky @ 2017-08-20 06:39 UTC (permalink / raw)
To: Claudio Freire <[email protected]>; +Cc: Daniel Blanch Bataller <[email protected]>; Scott Marlowe <[email protected]>; pgsql-performance
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.
>
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: performance problem on big tables
2017-08-14 13:24 performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-15 10:06 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-15 17:14 ` Re: performance problem on big tables Scott Marlowe <[email protected]>
2017-08-16 12:26 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-16 13:08 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-16 13:54 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-16 14:04 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-16 14:32 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-16 21:46 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-17 06:25 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-17 09:00 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-17 16:37 ` Re: performance problem on big tables Claudio Freire <[email protected]>
2017-08-20 06:39 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
@ 2017-08-20 11:00 ` Mariel Cherkassky <[email protected]>
2017-08-20 11:32 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-21 08:00 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
0 siblings, 2 replies; 39+ messages in thread
From: Mariel Cherkassky @ 2017-08-20 11:00 UTC (permalink / raw)
To: Claudio Freire <[email protected]>; +Cc: Daniel Blanch Bataller <[email protected]>; Scott Marlowe <[email protected]>; pgsql-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.
>>
>
>
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: performance problem on big tables
2017-08-14 13:24 performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-15 10:06 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-15 17:14 ` Re: performance problem on big tables Scott Marlowe <[email protected]>
2017-08-16 12:26 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-16 13:08 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-16 13:54 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-16 14:04 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-16 14:32 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-16 21:46 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-17 06:25 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-17 09:00 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-17 16:37 ` Re: performance problem on big tables Claudio Freire <[email protected]>
2017-08-20 06:39 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-20 11:00 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
@ 2017-08-20 11:32 ` Mariel Cherkassky <[email protected]>
1 sibling, 0 replies; 39+ messages in thread
From: Mariel Cherkassky @ 2017-08-20 11:32 UTC (permalink / raw)
To: Claudio Freire <[email protected]>; +Cc: Daniel Blanch Bataller <[email protected]>; Scott Marlowe <[email protected]>; pgsql-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.
>>>
>>
>>
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: performance problem on big tables
2017-08-14 13:24 performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-15 10:06 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-15 17:14 ` Re: performance problem on big tables Scott Marlowe <[email protected]>
2017-08-16 12:26 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-16 13:08 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-16 13:54 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-16 14:04 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-16 14:32 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-16 21:46 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-17 06:25 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-17 09:00 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-17 16:37 ` Re: performance problem on big tables Claudio Freire <[email protected]>
2017-08-20 06:39 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-20 11:00 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
@ 2017-08-21 08:00 ` Mariel Cherkassky <[email protected]>
2017-08-21 08:37 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-23 23:15 ` Re: performance problem on big tables Claudio Freire <[email protected]>
1 sibling, 2 replies; 39+ messages in thread
From: Mariel Cherkassky @ 2017-08-21 08:00 UTC (permalink / raw)
To: ; +Cc: pgsql-performance
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 <[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.
>>>
>>
>>
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: performance problem on big tables
2017-08-14 13:24 performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-15 10:06 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-15 17:14 ` Re: performance problem on big tables Scott Marlowe <[email protected]>
2017-08-16 12:26 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-16 13:08 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-16 13:54 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-16 14:04 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-16 14:32 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-16 21:46 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-17 06:25 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-17 09:00 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-17 16:37 ` Re: performance problem on big tables Claudio Freire <[email protected]>
2017-08-20 06:39 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-20 11:00 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-21 08:00 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
@ 2017-08-21 08:37 ` Daniel Blanch Bataller <[email protected]>
2017-08-21 11:27 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
1 sibling, 1 reply; 39+ messages in thread
From: Daniel Blanch Bataller @ 2017-08-21 08:37 UTC (permalink / raw)
To: Mariel Cherkassky <[email protected]>; +Cc: pgsql-performance
> El 21 ago 2017, a las 10:00, Mariel Cherkassky <[email protected]> escribió:
>
> 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.
>
>
Are you using a FDW to access oracle server and then dump it using copy? This is going to be slow, FDW isn't fast.
> -When I run copy from the local dump, the reading is very fast 300 M/s.
>
>
You reported it was slow before. What has changed? How much does it take to load the 32G table then?
> -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?
>
>
Extract the table to a file in the oracle server in a format that the COPY utility can read, then copy it to postgres server and load it. You can even pipe commands and do it in a single step.
This is what I meant when I said that COPY is much faster than any thing else. To make it even faster, if I/O is not your bottleneck, you can chop the table in chunks and load it in parallel as I told you before, I have done this many times when migrating data from oracle to postgres. ora2pg uses this method to migrate data from oracle to postgres too.
>
> 2017-08-20 14:00 GMT+03:00 Mariel Cherkassky <[email protected] <mailto:[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] <mailto:[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] <mailto:[email protected]>>:
> On Thu, Aug 17, 2017 at 6:00 AM, Mariel Cherkassky
> <[email protected] <mailto:[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.
>
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: performance problem on big tables
2017-08-14 13:24 performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-15 10:06 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-15 17:14 ` Re: performance problem on big tables Scott Marlowe <[email protected]>
2017-08-16 12:26 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-16 13:08 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-16 13:54 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-16 14:04 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-16 14:32 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-16 21:46 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-17 06:25 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-17 09:00 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-17 16:37 ` Re: performance problem on big tables Claudio Freire <[email protected]>
2017-08-20 06:39 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-20 11:00 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-21 08:00 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-21 08:37 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
@ 2017-08-21 11:27 ` Mariel Cherkassky <[email protected]>
2017-08-21 11:53 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
0 siblings, 1 reply; 39+ messages in thread
From: Mariel Cherkassky @ 2017-08-21 11:27 UTC (permalink / raw)
To: Daniel Blanch Bataller <[email protected]>; +Cc: pgsql-performance
All this operation runs as part of a big transaction that I run. How can I
create a dump in the oracle server and copy it to the postgresql server
from a postgresql transaction ? Chopping the table is optional when I use
copy, but when I use copy to remote oracle table it takes longer to create
the dump.
2017-08-21 11:37 GMT+03:00 Daniel Blanch Bataller <
[email protected]>:
>
> El 21 ago 2017, a las 10:00, Mariel Cherkassky <
> [email protected]> escribió:
>
> 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.
>
>
>
> Are you using a FDW to access oracle server and then dump it using copy?
> This is going to be slow, FDW isn't fast.
>
>
> -When I run copy from the local dump, the reading is very fast 300 M/s.
>
>
> You reported it was slow before. What has changed? How much does it take
> to load the 32G table then?
>
>
> -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?
>
>
>
> Extract the table to a file in the oracle server in a format that the COPY
> utility can read, then copy it to postgres server and load it. You can even
> pipe commands and do it in a single step.
>
> This is what I meant when I said that COPY is much faster than any thing
> else. To make it even faster, if I/O is not your bottleneck, you can chop
> the table in chunks and load it in parallel as I told you before, I have
> done this many times when migrating data from oracle to postgres. ora2pg
> uses this method to migrate data from oracle to postgres too.
>
>
> 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.
>>>>
>>>
>>>
>
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: performance problem on big tables
2017-08-14 13:24 performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-15 10:06 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-15 17:14 ` Re: performance problem on big tables Scott Marlowe <[email protected]>
2017-08-16 12:26 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-16 13:08 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-16 13:54 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-16 14:04 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-16 14:32 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-16 21:46 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-17 06:25 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-17 09:00 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-17 16:37 ` Re: performance problem on big tables Claudio Freire <[email protected]>
2017-08-20 06:39 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-20 11:00 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-21 08:00 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-21 08:37 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-21 11:27 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
@ 2017-08-21 11:53 ` Daniel Blanch Bataller <[email protected]>
2017-08-21 12:22 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
0 siblings, 1 reply; 39+ messages in thread
From: Daniel Blanch Bataller @ 2017-08-21 11:53 UTC (permalink / raw)
To: Mariel Cherkassky <[email protected]>; +Cc: pgsql-performance
> El 21 ago 2017, a las 13:27, Mariel Cherkassky <[email protected]> escribió:
>
> All this operation runs as part of a big transaction that I run.
> How can I create a dump in the oracle server and copy it to the postgresql server from a postgresql transaction ?
I guess you could create a user defined function in any of the available languages (perl, python, java, …). Functions run inside transactions too…this is not simple, though.
> Chopping the table is optional when I use copy, but when I use copy to remote oracle table it takes longer to create the dump.
It may take longer depending on how the oracle machine, table and database are configured. In my experience oracle is not very fast dumping whole tables, not to mention tables with BLOB data, which can be as slow as hundreds of records per second (which is probably not your case).
If this transaction is to synchronize data between transactional servers and data analysis servers you may consider using some type of replication where only changes are sent. EnterpriseDB has tools to do such things, I’m not aware of any other tool that can do this between oracle and postgres.
Regards,
Daniel.
>
> 2017-08-21 11:37 GMT+03:00 Daniel Blanch Bataller <[email protected] <mailto:[email protected]>>:
>
>> El 21 ago 2017, a las 10:00, Mariel Cherkassky <[email protected] <mailto:[email protected]>> escribió:
>>
>> 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.
>>
>>
>
>
> Are you using a FDW to access oracle server and then dump it using copy? This is going to be slow, FDW isn't fast.
>
>
>> -When I run copy from the local dump, the reading is very fast 300 M/s.
>>
>>
>
> You reported it was slow before. What has changed? How much does it take to load the 32G table then?
>
>
>> -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?
>>
>>
>
>
> Extract the table to a file in the oracle server in a format that the COPY utility can read, then copy it to postgres server and load it. You can even pipe commands and do it in a single step.
>
> This is what I meant when I said that COPY is much faster than any thing else. To make it even faster, if I/O is not your bottleneck, you can chop the table in chunks and load it in parallel as I told you before, I have done this many times when migrating data from oracle to postgres. ora2pg uses this method to migrate data from oracle to postgres too.
>
>>
>> 2017-08-20 14:00 GMT+03:00 Mariel Cherkassky <[email protected] <mailto:[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] <mailto:[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] <mailto:[email protected]>>:
>> On Thu, Aug 17, 2017 at 6:00 AM, Mariel Cherkassky
>> <[email protected] <mailto:[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.
>>
>
>
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: performance problem on big tables
2017-08-14 13:24 performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-15 10:06 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-15 17:14 ` Re: performance problem on big tables Scott Marlowe <[email protected]>
2017-08-16 12:26 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-16 13:08 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-16 13:54 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-16 14:04 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-16 14:32 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-16 21:46 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-17 06:25 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-17 09:00 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-17 16:37 ` Re: performance problem on big tables Claudio Freire <[email protected]>
2017-08-20 06:39 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-20 11:00 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-21 08:00 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-21 08:37 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-21 11:27 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-21 11:53 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
@ 2017-08-21 12:22 ` Mariel Cherkassky <[email protected]>
2017-08-21 13:55 ` Re: performance problem on big tables MichaelDBA <[email protected]>
0 siblings, 1 reply; 39+ messages in thread
From: Mariel Cherkassky @ 2017-08-21 12:22 UTC (permalink / raw)
To: Daniel Blanch Bataller <[email protected]>; +Cc: pgsql-performance
I`m searching for a way to improve the current performance, I'm not
interesting in using a different tool or writing something new because I'm
trying to migrate a system on oracle database to a postgresql database.
2017-08-21 14:53 GMT+03:00 Daniel Blanch Bataller <
[email protected]>:
>
> El 21 ago 2017, a las 13:27, Mariel Cherkassky <
> [email protected]> escribió:
>
> All this operation runs as part of a big transaction that I run.
>
> How can I create a dump in the oracle server and copy it to the postgresql
> server from a postgresql transaction ?
>
>
> I guess you could create a user defined function in any of the available
> languages (perl, python, java, …). Functions run inside transactions
> too…this is not simple, though.
>
> Chopping the table is optional when I use copy, but when I use copy to
> remote oracle table it takes longer to create the dump.
>
>
> It may take longer depending on how the oracle machine, table and database
> are configured. In my experience oracle is not very fast dumping whole
> tables, not to mention tables with BLOB data, which can be as slow as
> hundreds of records per second (which is probably not your case).
>
> If this transaction is to synchronize data between transactional servers
> and data analysis servers you may consider using some type of replication
> where only changes are sent. EnterpriseDB has tools to do such things, I’m
> not aware of any other tool that can do this between oracle and postgres.
>
> Regards,
>
> Daniel.
>
>
> 2017-08-21 11:37 GMT+03:00 Daniel Blanch Bataller <
> [email protected]>:
>
>>
>> El 21 ago 2017, a las 10:00, Mariel Cherkassky <
>> [email protected]> escribió:
>>
>> 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.
>>
>>
>>
>> Are you using a FDW to access oracle server and then dump it using copy?
>> This is going to be slow, FDW isn't fast.
>>
>>
>> -When I run copy from the local dump, the reading is very fast 300 M/s.
>>
>>
>> You reported it was slow before. What has changed? How much does it take
>> to load the 32G table then?
>>
>>
>> -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?
>>
>>
>>
>> Extract the table to a file in the oracle server in a format that the
>> COPY utility can read, then copy it to postgres server and load it. You can
>> even pipe commands and do it in a single step.
>>
>> This is what I meant when I said that COPY is much faster than any thing
>> else. To make it even faster, if I/O is not your bottleneck, you can chop
>> the table in chunks and load it in parallel as I told you before, I have
>> done this many times when migrating data from oracle to postgres. ora2pg
>> uses this method to migrate data from oracle to postgres too.
>>
>>
>> 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.
>>>>>
>>>>
>>>>
>>
>
>
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: performance problem on big tables
2017-08-14 13:24 performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-15 10:06 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-15 17:14 ` Re: performance problem on big tables Scott Marlowe <[email protected]>
2017-08-16 12:26 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-16 13:08 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-16 13:54 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-16 14:04 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-16 14:32 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-16 21:46 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-17 06:25 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-17 09:00 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-17 16:37 ` Re: performance problem on big tables Claudio Freire <[email protected]>
2017-08-20 06:39 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-20 11:00 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-21 08:00 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-21 08:37 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-21 11:27 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-21 11:53 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-21 12:22 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
@ 2017-08-21 13:55 ` MichaelDBA <[email protected]>
2017-08-21 14:19 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
0 siblings, 1 reply; 39+ messages in thread
From: MichaelDBA @ 2017-08-21 13:55 UTC (permalink / raw)
To: Mariel Cherkassky <[email protected]>; +Cc: pgsql-performance
Maybe I missed it in this continuous thread activity, but have you tried
'''ora2pg"? You can export from Oracle and import to Postgres in
parallel jobs. The import commands use the efficient COPY command by
default (unless you override it in the ora2pg configuration file). You
can do the export and subsequent import in memory, but I would suggest
the actual file export and import so you can take advantage of the
parallel feature.
Regards,
Michael Vitale
> Mariel Cherkassky <mailto:[email protected]>
> Monday, August 21, 2017 8:22 AM
> I`m searching for a way to improve the current performance, I'm not
> interesting in using a different tool or writing something new because
> I'm trying to migrate a system on oracle database to a postgresql
> database.
>
>
> Daniel Blanch Bataller <mailto:[email protected]>
> Monday, August 21, 2017 4:37 AM
>
>> El 21 ago 2017, a las 10:00, Mariel Cherkassky
>> <[email protected] <mailto:[email protected]>>
>> escribió:
>>
>> 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.
>>
>>
>
>
> Are you using a FDW to access oracle server and then dump it using
> copy? This is going to be slow, FDW isn't fast.
>
>
>> -When I run copy from the local dump, the reading is very fast 300 M/s.
>>
>>
>
> You reported it was slow before. What has changed? How much does it
> take to load the 32G table then?
>
>
>> -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?
>>
>>
>
>
> Extract the table to a file in the oracle server in a format that the
> COPY utility can read, then copy it to postgres server and load it.
> You can even pipe commands and do it in a single step.
>
> This is what I meant when I said that COPY is much faster than any
> thing else. To make it even faster, if I/O is not your bottleneck, you
> can chop the table in chunks and load it in parallel as I told you
> before, I have done this many times when migrating data from oracle to
> postgres. ora2pg uses this method to migrate data from oracle to
> postgres too.
>
>>
>> 2017-08-20 14:00 GMT+03:00 Mariel Cherkassky
>> <[email protected] <mailto:[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] <mailto:[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] <mailto:[email protected]>>:
>>
>> On Thu, Aug 17, 2017 at 6:00 AM, Mariel Cherkassky
>> <[email protected]
>> <mailto:[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.
>>
>>
>
> Mariel Cherkassky <mailto:[email protected]>
> Monday, August 21, 2017 4:00 AM
> 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?
>
>
> Mariel Cherkassky <mailto:[email protected]>
> Sunday, August 20, 2017 7:00 AM
> 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 ?
>
> Mariel Cherkassky <mailto:[email protected]>
> Sunday, August 20, 2017 2:39 AM
> 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 ?
>
>
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: performance problem on big tables
2017-08-14 13:24 performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-15 10:06 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-15 17:14 ` Re: performance problem on big tables Scott Marlowe <[email protected]>
2017-08-16 12:26 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-16 13:08 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-16 13:54 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-16 14:04 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-16 14:32 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-16 21:46 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-17 06:25 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-17 09:00 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-17 16:37 ` Re: performance problem on big tables Claudio Freire <[email protected]>
2017-08-20 06:39 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-20 11:00 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-21 08:00 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-21 08:37 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-21 11:27 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-21 11:53 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-21 12:22 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-21 13:55 ` Re: performance problem on big tables MichaelDBA <[email protected]>
@ 2017-08-21 14:19 ` Mariel Cherkassky <[email protected]>
2017-08-21 14:35 ` Re: performance problem on big tables Igor Neyman <[email protected]>
0 siblings, 1 reply; 39+ messages in thread
From: Mariel Cherkassky @ 2017-08-21 14:19 UTC (permalink / raw)
To: MichaelDBA <[email protected]>; +Cc: pgsql-performance
I had a system that consist from many objects(procedures,functions..) on an
oracle database. We decided to integrate that system to postgresql. That
system coppied alot of big tables from a different read only oracle
database and preformed on it alot of queries to produce reports. The part
of getting the data is part of some procedures, I cant change it so freely.
I'm searching a way to improve the perfomance of the database because I'm
sure that I didnt conifgure something well. Moreover, When I run complicted
queries (joint between 4 big tables and filtering) it takes alot of time
and I see that the server is cacheing all my ram memory.
2017-08-21 16:55 GMT+03:00 MichaelDBA <[email protected]>:
> Maybe I missed it in this continuous thread activity, but have you tried
> '''ora2pg"? You can export from Oracle and import to Postgres in parallel
> jobs. The import commands use the efficient COPY command by default
> (unless you override it in the ora2pg configuration file). You can do the
> export and subsequent import in memory, but I would suggest the actual file
> export and import so you can take advantage of the parallel feature.
>
> Regards,
> Michael Vitale
>
> Mariel Cherkassky <[email protected]>
> Monday, August 21, 2017 8:22 AM
> I`m searching for a way to improve the current performance, I'm not
> interesting in using a different tool or writing something new because I'm
> trying to migrate a system on oracle database to a postgresql database.
>
>
> Daniel Blanch Bataller <[email protected]>
> Monday, August 21, 2017 4:37 AM
>
> El 21 ago 2017, a las 10:00, Mariel Cherkassky <
> [email protected]> escribió:
>
> 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.
>
>
>
> Are you using a FDW to access oracle server and then dump it using copy?
> This is going to be slow, FDW isn't fast.
>
>
> -When I run copy from the local dump, the reading is very fast 300 M/s.
>
>
> You reported it was slow before. What has changed? How much does it take
> to load the 32G table then?
>
>
> -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?
>
>
>
> Extract the table to a file in the oracle server in a format that the COPY
> utility can read, then copy it to postgres server and load it. You can even
> pipe commands and do it in a single step.
>
> This is what I meant when I said that COPY is much faster than any thing
> else. To make it even faster, if I/O is not your bottleneck, you can chop
> the table in chunks and load it in parallel as I told you before, I have
> done this many times when migrating data from oracle to postgres. ora2pg
> uses this method to migrate data from oracle to postgres too.
>
>
> 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.
>>>>
>>>
>>>
> Mariel Cherkassky <[email protected]>
> Monday, August 21, 2017 4:00 AM
> 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?
>
> Mariel Cherkassky <[email protected]>
> Sunday, August 20, 2017 7:00 AM
> 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 ?
>
> Mariel Cherkassky <[email protected]>
> Sunday, August 20, 2017 2:39 AM
> 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 ?
>
>
>
>
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: performance problem on big tables
2017-08-14 13:24 performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-15 10:06 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-15 17:14 ` Re: performance problem on big tables Scott Marlowe <[email protected]>
2017-08-16 12:26 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-16 13:08 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-16 13:54 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-16 14:04 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-16 14:32 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-16 21:46 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-17 06:25 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-17 09:00 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-17 16:37 ` Re: performance problem on big tables Claudio Freire <[email protected]>
2017-08-20 06:39 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-20 11:00 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-21 08:00 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-21 08:37 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-21 11:27 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-21 11:53 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-21 12:22 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-21 13:55 ` Re: performance problem on big tables MichaelDBA <[email protected]>
2017-08-21 14:19 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
@ 2017-08-21 14:35 ` Igor Neyman <[email protected]>
2017-08-21 14:37 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-21 14:51 ` Re: performance problem on big tables Michael DNA <[email protected]>
0 siblings, 2 replies; 39+ messages in thread
From: Igor Neyman @ 2017-08-21 14:35 UTC (permalink / raw)
To: Mariel Cherkassky <[email protected]>; MichaelDBA <[email protected]>; +Cc: pgsql-performance
From: [email protected] [mailto:[email protected]] On Behalf Of Mariel Cherkassky
Sent: Monday, August 21, 2017 10:20 AM
To: MichaelDBA <[email protected]>
Cc: [email protected]
Subject: Re: [PERFORM] performance problem on big tables
I had a system that consist from many objects(procedures,functions..) on an oracle database. We decided to integrate that system to postgresql. That system coppied alot of big tables from a different read only oracle database and preformed on it alot of queries to produce reports. The part of getting the data is part of some procedures, I cant change it so freely. I'm searching a way to improve the perfomance of the database because I'm sure that I didnt conifgure something well. Moreover, When I run complicted queries (joint between 4 big tables and filtering) it takes alot of time and I see that the server is cacheing all my ram memory.
Probably your joins are done on Postgres side.
m.b. instead of Postgres pulling data from Oracle, you should try pushing data from Oracle to Postgres using Oracle’s Heterogeneous Services and Postgres ODBC driver. In this case you do your joins and filtering on Oracles side and just push the result set to Postgres.
That’s how I did migration from Oracle to Postgres.
Regards,
Igor Neyman
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: performance problem on big tables
2017-08-14 13:24 performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-15 10:06 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-15 17:14 ` Re: performance problem on big tables Scott Marlowe <[email protected]>
2017-08-16 12:26 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-16 13:08 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-16 13:54 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-16 14:04 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-16 14:32 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-16 21:46 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-17 06:25 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-17 09:00 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-17 16:37 ` Re: performance problem on big tables Claudio Freire <[email protected]>
2017-08-20 06:39 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-20 11:00 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-21 08:00 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-21 08:37 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-21 11:27 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-21 11:53 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-21 12:22 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-21 13:55 ` Re: performance problem on big tables MichaelDBA <[email protected]>
2017-08-21 14:19 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-21 14:35 ` Re: performance problem on big tables Igor Neyman <[email protected]>
@ 2017-08-21 14:37 ` Mariel Cherkassky <[email protected]>
1 sibling, 0 replies; 39+ messages in thread
From: Mariel Cherkassky @ 2017-08-21 14:37 UTC (permalink / raw)
To: Igor Neyman <[email protected]>; +Cc: MichaelDBA <[email protected]>; pgsql-performance
I already finished migrating the system from oracle to postgresql. Right
now, I'm trying to improve its performance - Im bringing data from another
read only database that is updaded every minute. I cant push data from the
oracle side to the postgresql side because the oracle database is read only.
2017-08-21 17:35 GMT+03:00 Igor Neyman <[email protected]>:
>
>
> *From:* [email protected] [mailto:pgsql-performance-
> [email protected]] *On Behalf Of *Mariel Cherkassky
> *Sent:* Monday, August 21, 2017 10:20 AM
> *To:* MichaelDBA <[email protected]>
> *Cc:* [email protected]
> *Subject:* Re: [PERFORM] performance problem on big tables
>
>
>
> I had a system that consist from many objects(procedures,functions..) on
> an oracle database. We decided to integrate that system to postgresql. That
> system coppied alot of big tables from a different read only oracle
> database and preformed on it alot of queries to produce reports. The part
> of getting the data is part of some procedures, I cant change it so freely.
> I'm searching a way to improve the perfomance of the database because I'm
> sure that I didnt conifgure something well. Moreover, When I run complicted
> queries (joint between 4 big tables and filtering) it takes alot of time
> and I see that the server is cacheing all my ram memory.
>
>
>
>
>
> Probably your joins are done on Postgres side.
>
>
>
> m.b. instead of Postgres pulling data from Oracle, you should try pushing
> data from Oracle to Postgres using Oracle’s Heterogeneous Services and
> Postgres ODBC driver. In this case you do your joins and filtering on
> Oracles side and just push the result set to Postgres.
>
> That’s how I did migration from Oracle to Postgres.
>
>
>
> Regards,
>
> Igor Neyman
>
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: performance problem on big tables
2017-08-14 13:24 performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-15 10:06 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-15 17:14 ` Re: performance problem on big tables Scott Marlowe <[email protected]>
2017-08-16 12:26 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-16 13:08 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-16 13:54 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-16 14:04 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-16 14:32 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-16 21:46 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-17 06:25 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-17 09:00 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-17 16:37 ` Re: performance problem on big tables Claudio Freire <[email protected]>
2017-08-20 06:39 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-20 11:00 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-21 08:00 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-21 08:37 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-21 11:27 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-21 11:53 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-21 12:22 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-21 13:55 ` Re: performance problem on big tables MichaelDBA <[email protected]>
2017-08-21 14:19 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-21 14:35 ` Re: performance problem on big tables Igor Neyman <[email protected]>
@ 2017-08-21 14:51 ` Michael DNA <[email protected]>
1 sibling, 0 replies; 39+ messages in thread
From: Michael DNA @ 2017-08-21 14:51 UTC (permalink / raw)
To: Igor Neyman <[email protected]>; +Cc: Mariel Cherkassky <[email protected]>; pgsql-performance
If your procedures to get the data is part is a query predicate, then you can still use ora2pg
Sent from my iPhone
> On Aug 21, 2017, at 10:35 AM, Igor Neyman <[email protected]> wrote:
>
>
> From: [email protected] [mailto:[email protected]] On Behalf Of Mariel Cherkassky
> Sent: Monday, August 21, 2017 10:20 AM
> To: MichaelDBA <[email protected]>
> Cc: [email protected]
> Subject: Re: [PERFORM] performance problem on big tables
>
> I had a system that consist from many objects(procedures,functions..) on an oracle database. We decided to integrate that system to postgresql. That system coppied alot of big tables from a different read only oracle database and preformed on it alot of queries to produce reports. The part of getting the data is part of some procedures, I cant change it so freely. I'm searching a way to improve the perfomance of the database because I'm sure that I didnt conifgure something well. Moreover, When I run complicted queries (joint between 4 big tables and filtering) it takes alot of time and I see that the server is cacheing all my ram memory.
>
>
> Probably your joins are done on Postgres side.
>
> m.b. instead of Postgres pulling data from Oracle, you should try pushing data from Oracle to Postgres using Oracle’s Heterogeneous Services and Postgres ODBC driver. In this case you do your joins and filtering on Oracles side and just push the result set to Postgres.
> That’s how I did migration from Oracle to Postgres.
>
> Regards,
> Igor Neyman
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: performance problem on big tables
2017-08-14 13:24 performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-15 10:06 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-15 17:14 ` Re: performance problem on big tables Scott Marlowe <[email protected]>
2017-08-16 12:26 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-16 13:08 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-16 13:54 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-16 14:04 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-16 14:32 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-16 21:46 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-17 06:25 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-17 09:00 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-17 16:37 ` Re: performance problem on big tables Claudio Freire <[email protected]>
2017-08-20 06:39 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-20 11:00 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-21 08:00 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
@ 2017-08-23 23:15 ` Claudio Freire <[email protected]>
2017-08-24 07:51 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
1 sibling, 1 reply; 39+ messages in thread
From: Claudio Freire @ 2017-08-23 23:15 UTC (permalink / raw)
To: Mariel Cherkassky <[email protected]>; +Cc: pgsql-performance
On Mon, Aug 21, 2017 at 5:00 AM, Mariel Cherkassky
<[email protected]> wrote:
> 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.
Have you tried increasing the prefetch option in the remote table?
If you left it in its default, latency could be hurting your ability
to saturate the network.
--
Sent via pgsql-performance mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: performance problem on big tables
2017-08-14 13:24 performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-15 10:06 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-15 17:14 ` Re: performance problem on big tables Scott Marlowe <[email protected]>
2017-08-16 12:26 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-16 13:08 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-16 13:54 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-16 14:04 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-16 14:32 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-16 21:46 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-17 06:25 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-17 09:00 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-17 16:37 ` Re: performance problem on big tables Claudio Freire <[email protected]>
2017-08-20 06:39 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-20 11:00 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-21 08:00 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-23 23:15 ` Re: performance problem on big tables Claudio Freire <[email protected]>
@ 2017-08-24 07:51 ` Mariel Cherkassky <[email protected]>
2017-08-24 16:14 ` Re: performance problem on big tables Claudio Freire <[email protected]>
0 siblings, 1 reply; 39+ messages in thread
From: Mariel Cherkassky @ 2017-08-24 07:51 UTC (permalink / raw)
To: Claudio Freire <[email protected]>; +Cc: pgsql-performance
Hi Claudio, how can I do that ? Can you explain me what is this option ?
2017-08-24 2:15 GMT+03:00 Claudio Freire <[email protected]>:
> On Mon, Aug 21, 2017 at 5:00 AM, Mariel Cherkassky
> <[email protected]> wrote:
> > 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.
>
> Have you tried increasing the prefetch option in the remote table?
>
> If you left it in its default, latency could be hurting your ability
> to saturate the network.
>
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: performance problem on big tables
2017-08-14 13:24 performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-15 10:06 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-15 17:14 ` Re: performance problem on big tables Scott Marlowe <[email protected]>
2017-08-16 12:26 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-16 13:08 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-16 13:54 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-16 14:04 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-16 14:32 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-16 21:46 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-17 06:25 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-17 09:00 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-17 16:37 ` Re: performance problem on big tables Claudio Freire <[email protected]>
2017-08-20 06:39 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-20 11:00 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-21 08:00 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-23 23:15 ` Re: performance problem on big tables Claudio Freire <[email protected]>
2017-08-24 07:51 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
@ 2017-08-24 16:14 ` Claudio Freire <[email protected]>
2017-08-27 16:34 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
0 siblings, 1 reply; 39+ messages in thread
From: Claudio Freire @ 2017-08-24 16:14 UTC (permalink / raw)
To: Mariel Cherkassky <[email protected]>; +Cc: pgsql-performance
On Thu, Aug 24, 2017 at 4:51 AM, Mariel Cherkassky
<[email protected]> wrote:
> Hi Claudio, how can I do that ? Can you explain me what is this option ?
>
> 2017-08-24 2:15 GMT+03:00 Claudio Freire <[email protected]>:
>>
>> On Mon, Aug 21, 2017 at 5:00 AM, Mariel Cherkassky
>> <[email protected]> wrote:
>> > 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.
>>
>> Have you tried increasing the prefetch option in the remote table?
>>
>> If you left it in its default, latency could be hurting your ability
>> to saturate the network.
>
>
Please don't top-post.
I'm assuming you're using this: http://laurenz.github.io/oracle_fdw/
If you check the docs, you'll see this:
https://github.com/laurenz/oracle_fdw#foreign-table-options
So I'm guessing you could:
ALTER FOREIGN TABLE remote_table OPTIONS ( SET prefetch 10240 );
--
Sent via pgsql-performance mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: performance problem on big tables
2017-08-14 13:24 performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-15 10:06 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-15 17:14 ` Re: performance problem on big tables Scott Marlowe <[email protected]>
2017-08-16 12:26 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-16 13:08 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-16 13:54 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-16 14:04 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-16 14:32 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-16 21:46 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-17 06:25 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-17 09:00 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-17 16:37 ` Re: performance problem on big tables Claudio Freire <[email protected]>
2017-08-20 06:39 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-20 11:00 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-21 08:00 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-23 23:15 ` Re: performance problem on big tables Claudio Freire <[email protected]>
2017-08-24 07:51 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-24 16:14 ` Re: performance problem on big tables Claudio Freire <[email protected]>
@ 2017-08-27 16:34 ` Mariel Cherkassky <[email protected]>
2017-08-28 05:47 ` Re: performance problem on big tables Claudio Freire <[email protected]>
2017-08-28 05:51 ` Re: performance problem on big tables Claudio Freire <[email protected]>
0 siblings, 2 replies; 39+ messages in thread
From: Mariel Cherkassky @ 2017-08-27 16:34 UTC (permalink / raw)
To: Claudio Freire <[email protected]>; +Cc: pgsql-performance
Hi, yes indeed I'm using laurenz`s oracle_fdw extension. I tried to run it
but I'm getting error
dbch=# ALTER FOREIGN TABLE tc_sub_rate_ver_prod OPTIONS ( SET prefetch
10240 );
ERROR: syntax error at or near "10240"
LINE 1: ...N TABLE tc_sub_rate_ver_prod OPTIONS ( SET prefetch 10240 );
dbch=# alter foreign table tc_sub_rate_ver_prod OPTIONS (SET prefetch
'10240');
ERROR: option "prefetch" not found
2017-08-24 19:14 GMT+03:00 Claudio Freire <[email protected]>:
> On Thu, Aug 24, 2017 at 4:51 AM, Mariel Cherkassky
> <[email protected]> wrote:
> > Hi Claudio, how can I do that ? Can you explain me what is this option ?
> >
> > 2017-08-24 2:15 GMT+03:00 Claudio Freire <[email protected]>:
> >>
> >> On Mon, Aug 21, 2017 at 5:00 AM, Mariel Cherkassky
> >> <[email protected]> wrote:
> >> > 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.
> >>
> >> Have you tried increasing the prefetch option in the remote table?
> >>
> >> If you left it in its default, latency could be hurting your ability
> >> to saturate the network.
> >
> >
>
> Please don't top-post.
>
> I'm assuming you're using this: http://laurenz.github.io/oracle_fdw/
>
> If you check the docs, you'll see this:
> https://github.com/laurenz/oracle_fdw#foreign-table-options
>
> So I'm guessing you could:
>
> ALTER FOREIGN TABLE remote_table OPTIONS ( SET prefetch 10240 );
>
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: performance problem on big tables
2017-08-14 13:24 performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-15 10:06 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-15 17:14 ` Re: performance problem on big tables Scott Marlowe <[email protected]>
2017-08-16 12:26 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-16 13:08 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-16 13:54 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-16 14:04 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-16 14:32 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-16 21:46 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-17 06:25 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-17 09:00 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-17 16:37 ` Re: performance problem on big tables Claudio Freire <[email protected]>
2017-08-20 06:39 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-20 11:00 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-21 08:00 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-23 23:15 ` Re: performance problem on big tables Claudio Freire <[email protected]>
2017-08-24 07:51 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-24 16:14 ` Re: performance problem on big tables Claudio Freire <[email protected]>
2017-08-27 16:34 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
@ 2017-08-28 05:47 ` Claudio Freire <[email protected]>
1 sibling, 0 replies; 39+ messages in thread
From: Claudio Freire @ 2017-08-28 05:47 UTC (permalink / raw)
To: Mariel Cherkassky <[email protected]>; +Cc: pgsql-performance
On Sun, Aug 27, 2017 at 1:34 PM, Mariel Cherkassky
<[email protected]> wrote:
> Hi, yes indeed I'm using laurenz`s oracle_fdw extension. I tried to run it
> but I'm getting error
>
> dbch=# ALTER FOREIGN TABLE tc_sub_rate_ver_prod OPTIONS ( SET prefetch 10240
> );
> ERROR: syntax error at or near "10240"
> LINE 1: ...N TABLE tc_sub_rate_ver_prod OPTIONS ( SET prefetch 10240 );
Yeah, might need to put the 10240 in quotes.
--
Sent via pgsql-performance mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: performance problem on big tables
2017-08-14 13:24 performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-15 10:06 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-15 17:14 ` Re: performance problem on big tables Scott Marlowe <[email protected]>
2017-08-16 12:26 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-16 13:08 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-16 13:54 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-16 14:04 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-16 14:32 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-16 21:46 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-17 06:25 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-17 09:00 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-17 16:37 ` Re: performance problem on big tables Claudio Freire <[email protected]>
2017-08-20 06:39 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-20 11:00 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-21 08:00 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-23 23:15 ` Re: performance problem on big tables Claudio Freire <[email protected]>
2017-08-24 07:51 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-24 16:14 ` Re: performance problem on big tables Claudio Freire <[email protected]>
2017-08-27 16:34 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
@ 2017-08-28 05:51 ` Claudio Freire <[email protected]>
2017-08-28 06:05 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
1 sibling, 1 reply; 39+ messages in thread
From: Claudio Freire @ 2017-08-28 05:51 UTC (permalink / raw)
To: Mariel Cherkassky <[email protected]>; +Cc: pgsql-performance
On Sun, Aug 27, 2017 at 1:34 PM, Mariel Cherkassky
<[email protected]> wrote:
> Hi, yes indeed I'm using laurenz`s oracle_fdw extension. I tried to run it
> but I'm getting error
>
> dbch=# ALTER FOREIGN TABLE tc_sub_rate_ver_prod OPTIONS ( SET prefetch 10240
> );
> ERROR: syntax error at or near "10240"
> LINE 1: ...N TABLE tc_sub_rate_ver_prod OPTIONS ( SET prefetch 10240 );
>
>
> dbch=# alter foreign table tc_sub_rate_ver_prod OPTIONS (SET prefetch
> '10240');
> ERROR: option "prefetch" not found
Oh, sorry, I hadn't seen this until I hit send.
Unless the documentation is inaccurate or you're using a really old
version (from the changelog that option is from 2016), that should
work.
I don't have enough experience with oracle_fdw to help there, most of
my dealings have been with postgres_fdw.
--
Sent via pgsql-performance mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 39+ messages in thread
* Re: performance problem on big tables
2017-08-14 13:24 performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-15 10:06 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-15 17:14 ` Re: performance problem on big tables Scott Marlowe <[email protected]>
2017-08-16 12:26 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-16 13:08 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-16 13:54 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-16 14:04 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-16 14:32 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-16 21:46 ` Re: performance problem on big tables Daniel Blanch Bataller <[email protected]>
2017-08-17 06:25 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-17 09:00 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-17 16:37 ` Re: performance problem on big tables Claudio Freire <[email protected]>
2017-08-20 06:39 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-20 11:00 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-21 08:00 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-23 23:15 ` Re: performance problem on big tables Claudio Freire <[email protected]>
2017-08-24 07:51 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-24 16:14 ` Re: performance problem on big tables Claudio Freire <[email protected]>
2017-08-27 16:34 ` Re: performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-28 05:51 ` Re: performance problem on big tables Claudio Freire <[email protected]>
@ 2017-08-28 06:05 ` Mariel Cherkassky <[email protected]>
0 siblings, 0 replies; 39+ messages in thread
From: Mariel Cherkassky @ 2017-08-28 06:05 UTC (permalink / raw)
To: Claudio Freire <[email protected]>; +Cc: pgsql-performance
I have the newest version :
select oracle_diag();
oracle_diag
---------------------------------------------------------------------------------------------------------------------
oracle_fdw 1.5.0, PostgreSQL 9.6.3, Oracle client 11.2.0.4.0,
ORACLE_HOME=/PostgreSQL/9.6/tools/instantclient_11_2/
(1 row)
Is there a prefetch also for local tables ? I mean If I run with a cursor
over results of a select query, mybe setting the prefetch for a local table
might also improve performance ?
2017-08-28 8:51 GMT+03:00 Claudio Freire <[email protected]>:
> On Sun, Aug 27, 2017 at 1:34 PM, Mariel Cherkassky
> <[email protected]> wrote:
> > Hi, yes indeed I'm using laurenz`s oracle_fdw extension. I tried to run
> it
> > but I'm getting error
> >
> > dbch=# ALTER FOREIGN TABLE tc_sub_rate_ver_prod OPTIONS ( SET prefetch
> 10240
> > );
> > ERROR: syntax error at or near "10240"
> > LINE 1: ...N TABLE tc_sub_rate_ver_prod OPTIONS ( SET prefetch 10240 );
> >
> >
> > dbch=# alter foreign table tc_sub_rate_ver_prod OPTIONS (SET prefetch
> > '10240');
> > ERROR: option "prefetch" not found
>
> Oh, sorry, I hadn't seen this until I hit send.
>
> Unless the documentation is inaccurate or you're using a really old
> version (from the changelog that option is from 2016), that should
> work.
>
> I don't have enough experience with oracle_fdw to help there, most of
> my dealings have been with postgres_fdw.
>
^ permalink raw reply [nested|flat] 39+ messages in thread
end of thread, other threads:[~2017-08-28 06:05 UTC | newest]
Thread overview: 39+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-08-14 13:24 performance problem on big tables Mariel Cherkassky <[email protected]>
2017-08-14 15:10 ` MichaelDBA <[email protected]>
2017-08-14 15:11 ` Daniel Blanch Bataller <[email protected]>
2017-08-14 15:45 ` Rick Otten <[email protected]>
2017-08-14 16:39 ` Jeff Janes <[email protected]>
2017-08-15 10:06 ` Mariel Cherkassky <[email protected]>
2017-08-15 16:13 ` Jeff Janes <[email protected]>
2017-08-15 17:44 ` Pavel Stehule <[email protected]>
2017-08-15 17:14 ` Scott Marlowe <[email protected]>
2017-08-16 12:26 ` Mariel Cherkassky <[email protected]>
2017-08-16 13:08 ` Daniel Blanch Bataller <[email protected]>
2017-08-16 13:54 ` Mariel Cherkassky <[email protected]>
2017-08-16 14:04 ` Daniel Blanch Bataller <[email protected]>
2017-08-16 14:32 ` Mariel Cherkassky <[email protected]>
2017-08-16 21:46 ` Daniel Blanch Bataller <[email protected]>
2017-08-17 06:25 ` Mariel Cherkassky <[email protected]>
2017-08-17 09:00 ` Mariel Cherkassky <[email protected]>
2017-08-17 10:06 ` Daniel Blanch Bataller <[email protected]>
2017-08-17 16:37 ` Claudio Freire <[email protected]>
2017-08-20 06:39 ` Mariel Cherkassky <[email protected]>
2017-08-20 11:00 ` Mariel Cherkassky <[email protected]>
2017-08-20 11:32 ` Mariel Cherkassky <[email protected]>
2017-08-21 08:00 ` Mariel Cherkassky <[email protected]>
2017-08-21 08:37 ` Daniel Blanch Bataller <[email protected]>
2017-08-21 11:27 ` Mariel Cherkassky <[email protected]>
2017-08-21 11:53 ` Daniel Blanch Bataller <[email protected]>
2017-08-21 12:22 ` Mariel Cherkassky <[email protected]>
2017-08-21 13:55 ` MichaelDBA <[email protected]>
2017-08-21 14:19 ` Mariel Cherkassky <[email protected]>
2017-08-21 14:35 ` Igor Neyman <[email protected]>
2017-08-21 14:37 ` Mariel Cherkassky <[email protected]>
2017-08-21 14:51 ` Michael DNA <[email protected]>
2017-08-23 23:15 ` Claudio Freire <[email protected]>
2017-08-24 07:51 ` Mariel Cherkassky <[email protected]>
2017-08-24 16:14 ` Claudio Freire <[email protected]>
2017-08-27 16:34 ` Mariel Cherkassky <[email protected]>
2017-08-28 05:47 ` Claudio Freire <[email protected]>
2017-08-28 05:51 ` Claudio Freire <[email protected]>
2017-08-28 06:05 ` Mariel Cherkassky <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox