pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feed8.4 Performance improvements: was Re: Proposal of tunable fix for scalability of 8.4
14+ messages / 8 participants
[nested] [flat]
* 8.4 Performance improvements: was Re: Proposal of tunable fix for scalability of 8.4
@ 2009-03-13 12:44 Jignesh K. Shah <J.K.Shah@Sun.COM>
0 siblings, 3 replies; 14+ messages in thread
From: Jignesh K. Shah @ 2009-03-13 12:44 UTC (permalink / raw)
To: Greg Smith <gsmith@gregsmith.com>; +Cc: pgsql-performance
Greg Smith wrote:
> On Thu, 12 Mar 2009, Jignesh K. Shah wrote:
>
>> As soon as I get more "cycles" I will try variations of it but it
>> would help if others can try it out in their own environments to see
>> if it helps their instances.
>
> What you should do next is see whether you can remove the bottleneck
> your test is running into via using a connection pooler. That's what
> I think most informed people would do were you to ask how to setup an
> optimal environment using PostgreSQL that aimed to serve thousands of
> clients. If that makes your bottleneck go away, that's what you should
> be recommending to customers who want to scale in this fashion too.
> If the bottleneck moves to somewhere else, that new hot spot might be
> one people care more about. Given that there are multiple good
> pooling solutions floating around already, it's hard to justify
> dumping coding and testing resources here if that makes the problem
> move somewhere else.
>
> It's great that you've identified an alternate scheduling approach
> that helps on your problematic test case, but you're a long ways from
> having a full model of how changes to the locking model impact other
> database workloads. As for the idea of doing something in this area
> for 8.4, there are a significant number of performance-related changes
> already committed for that version that deserve more focused testing
> during beta. You're way too late to throw another one into that
> already crowded area.
>
On the other hand I have taken up a task of showing 8.4 Performance
improvements over 8.3.
Can we do a vote on which specific performance features we want to test?
I can use dbt2, dbt3 tests to see how 8.4 performs and compare it with
8.3? Also if you have your own favorite test to test it out let me
know.. I have allocated some time for this task so it is feasible for me
to do this.
Many of the improvements may not be visible through this standard tests
so feedback on testing methology for those is also appreciated.
* Visibility map - Reduce Vacuum overhead - (I think I can time vacuum
with some usage on both databases)
* Prefetch IO with posix_fadvice () - Though I am not sure if it is
supported on UNIX or not (but can be tested by standard tests)
* Parallel pg_restore (Can be tested with a big database dump)
Any more features that I can stress during the testing phase?
Regards,
Jignesh
> --
> * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
--
Jignesh Shah http://blogs.sun.com/jkshah
The New Sun Microsystems,Inc http://sun.com/postgresql
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: 8.4 Performance improvements: was Re: Proposal of tunable fix for scalability of 8.4
@ 2009-03-13 13:43 Gregory Stark <stark@enterprisedb.com>
parent: Jignesh K. Shah <J.K.Shah@Sun.COM>
2 siblings, 0 replies; 14+ messages in thread
From: Gregory Stark @ 2009-03-13 13:43 UTC (permalink / raw)
To: Jignesh K. Shah <J.K.Shah@Sun.COM>; +Cc: Greg Smith <gsmith@gregsmith.com>; pgsql-performance
"Jignesh K. Shah" <J.K.Shah@Sun.COM> writes:
> Can we do a vote on which specific performance features we want to test?
>
> Many of the improvements may not be visible through this standard tests so
> feedback on testing methology for those is also appreciated.
> * Visibility map - Reduce Vacuum overhead - (I think I can time vacuum with
> some usage on both databases)
Timing vacuum is kind of pointless -- the only thing that matters is whether
it's "fast enough". But it is worth saying that good benchmarks should include
normal vacuum runs. Benchmarks which don't run long enough to trigger vacuum
aren't realistic.
> * Prefetch IO with posix_fadvice () - Though I am not sure if it is supported
> on UNIX or not (but can be tested by standard tests)
Well clearly this is my favourite :)
AFAIK Opensolaris doesn't implement posix_fadvise() so there's no benefit. It
would be great to hear if you could catch the ear of the right people to get
an implementation committed. Depending on how the i/o scheduler system is
written it might not even be hard -- the Linux implementation of WILLNEED is
all of 20 lines.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's Slony Replication support!
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: 8.4 Performance improvements: was Re: Proposal of tunable fix for scalability of 8.4
@ 2009-03-13 13:57 Gregory Stark <stark@enterprisedb.com>
parent: Jignesh K. Shah <J.K.Shah@Sun.COM>
2 siblings, 1 reply; 14+ messages in thread
From: Gregory Stark @ 2009-03-13 13:57 UTC (permalink / raw)
To: Jignesh K. Shah <J.K.Shah@Sun.COM>; +Cc: Greg Smith <gsmith@gregsmith.com>; pgsql-performance
A minute ago I said:
AFAIK Opensolaris doesn't implement posix_fadvise() so there's no benefit. It
would be great to hear if you could catch the ear of the right people to get
an implementation committed. Depending on how the i/o scheduler system is
written it might not even be hard -- the Linux implementation of WILLNEED is
all of 20 lines.
I noticed after sending it that that's slightly unfair. The 20-line function
calls another function (which calls another function) to do the real readahead
work. That function (mm/readahead.c:__do_page_cache_readahead()) is 48 lines.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's Slony Replication support!
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: 8.4 Performance improvements: was Re: Proposal of tunable fix for scalability of 8.4
@ 2009-03-13 15:20 Alan Stange <stange@rentec.com>
parent: Gregory Stark <stark@enterprisedb.com>
0 siblings, 1 reply; 14+ messages in thread
From: Alan Stange @ 2009-03-13 15:20 UTC (permalink / raw)
To: Gregory Stark <stark@enterprisedb.com>; +Cc: Jignesh K. Shah <J.K.Shah@Sun.COM>; Greg Smith <gsmith@gregsmith.com>; pgsql-performance
Gregory Stark wrote:
> A minute ago I said:
>
> AFAIK Opensolaris doesn't implement posix_fadvise() so there's no benefit. It
> would be great to hear if you could catch the ear of the right people to get
> an implementation committed. Depending on how the i/o scheduler system is
> written it might not even be hard -- the Linux implementation of WILLNEED is
> all of 20 lines.
>
> I noticed after sending it that that's slightly unfair. The 20-line function
> calls another function (which calls another function) to do the real readahead
> work. That function (mm/readahead.c:__do_page_cache_readahead()) is 48 lines.
>
>
It's implemented. I'm guessing it's not what you want to see though:
http://src.opensolaris.org/source/xref/onnv/onnv-gate/usr/src/lib/libc/port/gen/posix_fadvise.c
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: 8.4 Performance improvements: was Re: Proposal of tunable fix for scalability of 8.4
@ 2009-03-13 17:15 Greg Smith <gsmith@gregsmith.com>
parent: Jignesh K. Shah <J.K.Shah@Sun.COM>
2 siblings, 0 replies; 14+ messages in thread
From: Greg Smith @ 2009-03-13 17:15 UTC (permalink / raw)
To: Jignesh K. Shah <J.K.Shah@Sun.COM>; +Cc: pgsql-performance
On Fri, 13 Mar 2009, Jignesh K. Shah wrote:
> I can use dbt2, dbt3 tests to see how 8.4 performs and compare it with
> 8.3?
That would be very helpful. There's been some work at updating the DTrace
capabilities available too; you might compare what that's reporting too.
> * Visibility map - Reduce Vacuum overhead - (I think I can time vacuum with
> some usage on both databases)
The reduced vacuum overhead should show up as just better overall
performance. If you can seperate out the vacuum specific time that would
be great, I don't know that it's essential. If the changes don't just
make a plain old speed improvement in your tests that would be a problem
worth reporting.
> * Parallel pg_restore (Can be tested with a big database dump)
It would be particularly useful if you could throw some of your 32+ core
systems at a parallel restore of something with a bunch of tables. I
don't think there have been (m)any tests of that code on Solaris or with
that many restore workers yet.
--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: 8.4 Performance improvements: was Re: Proposal of tunable fix for scalability of 8.4
@ 2009-03-13 17:23 Tom Lane <tgl@sss.pgh.pa.us>
parent: Alan Stange <stange@rentec.com>
0 siblings, 1 reply; 14+ messages in thread
From: Tom Lane @ 2009-03-13 17:23 UTC (permalink / raw)
To: Alan Stange <stange@rentec.com>; +Cc: Gregory Stark <stark@enterprisedb.com>; Jignesh K. Shah <J.K.Shah@Sun.COM>; Greg Smith <gsmith@gregsmith.com>; pgsql-performance
Alan Stange <stange@rentec.com> writes:
> Gregory Stark wrote:
>> AFAIK Opensolaris doesn't implement posix_fadvise() so there's no benefit.
> It's implemented. I'm guessing it's not what you want to see though:
> http://src.opensolaris.org/source/xref/onnv/onnv-gate/usr/src/lib/libc/port/gen/posix_fadvise.c
Ugh. So apparently, we actually need to special-case Solaris to not
believe that posix_fadvise works, or we'll waste cycles uselessly
calling a do-nothing function. Thanks, Sun.
regards, tom lane
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: 8.4 Performance improvements: was Re: Proposal of tunable fix for scalability of 8.4
@ 2009-03-14 01:58 Gregory Stark <stark@enterprisedb.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 14+ messages in thread
From: Gregory Stark @ 2009-03-14 01:58 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Alan Stange <stange@rentec.com>; Jignesh K. Shah <J.K.Shah@Sun.COM>; Greg Smith <gsmith@gregsmith.com>; pgsql-performance
Tom Lane <tgl@sss.pgh.pa.us> writes:
> Alan Stange <stange@rentec.com> writes:
>> Gregory Stark wrote:
>>> AFAIK Opensolaris doesn't implement posix_fadvise() so there's no benefit.
>
>> It's implemented. I'm guessing it's not what you want to see though:
>> http://src.opensolaris.org/source/xref/onnv/onnv-gate/usr/src/lib/libc/port/gen/posix_fadvise.c
>
> Ugh. So apparently, we actually need to special-case Solaris to not
> believe that posix_fadvise works, or we'll waste cycles uselessly
> calling a do-nothing function. Thanks, Sun.
Do we? Or do we just document that setting effective_cache_size on Solaris
won't help?
I'm leaning towards the latter because I expect Sun will implement this and
there will be people running 8.4 on newer versions of the OS long after it's
out.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's 24x7 Postgres support!
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: 8.4 Performance improvements: was Re: Proposal of tunable fix for scalability of 8.4
@ 2009-03-14 02:06 Tom Lane <tgl@sss.pgh.pa.us>
parent: Gregory Stark <stark@enterprisedb.com>
0 siblings, 2 replies; 14+ messages in thread
From: Tom Lane @ 2009-03-14 02:06 UTC (permalink / raw)
To: Gregory Stark <stark@enterprisedb.com>; +Cc: Alan Stange <stange@rentec.com>; Jignesh K. Shah <J.K.Shah@Sun.COM>; Greg Smith <gsmith@gregsmith.com>; pgsql-performance
Gregory Stark <stark@enterprisedb.com> writes:
> Tom Lane <tgl@sss.pgh.pa.us> writes:
>> Ugh. So apparently, we actually need to special-case Solaris to not
>> believe that posix_fadvise works, or we'll waste cycles uselessly
>> calling a do-nothing function. Thanks, Sun.
> Do we? Or do we just document that setting effective_cache_size on Solaris
> won't help?
I assume you meant effective_io_concurrency. We'd still need a special
case because the default is currently hard-wired at 1, not 0, if
configure thinks the function exists. Also there's a posix_fadvise call
in xlog.c that that parameter doesn't control anyhow.
regards, tom lane
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: 8.4 Performance improvements: was Re: Proposal of tunable fix for scalability of 8.4
@ 2009-03-14 02:37 Robert Haas <robertmhaas@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 2 replies; 14+ messages in thread
From: Robert Haas @ 2009-03-14 02:37 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Gregory Stark <stark@enterprisedb.com>; Alan Stange <stange@rentec.com>; Jignesh K. Shah <J.K.Shah@sun.com>; Greg Smith <gsmith@gregsmith.com>; pgsql-performance
On Fri, Mar 13, 2009 at 10:06 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Gregory Stark <stark@enterprisedb.com> writes:
>> Tom Lane <tgl@sss.pgh.pa.us> writes:
>>> Ugh. So apparently, we actually need to special-case Solaris to not
>>> believe that posix_fadvise works, or we'll waste cycles uselessly
>>> calling a do-nothing function. Thanks, Sun.
>
>> Do we? Or do we just document that setting effective_cache_size on Solaris
>> won't help?
>
> I assume you meant effective_io_concurrency. We'd still need a special
> case because the default is currently hard-wired at 1, not 0, if
> configure thinks the function exists. Also there's a posix_fadvise call
> in xlog.c that that parameter doesn't control anyhow.
I think 1 should mean no prefetching, rather than 0. If the number of
concurrent I/O requests was 0, that would mean you couldn't perform
any I/O at all.
...Robert
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: 8.4 Performance improvements: was Re: Proposal of tunable fix for scalability of 8.4
@ 2009-03-14 04:02 Gregory Stark <stark@enterprisedb.com>
parent: Robert Haas <robertmhaas@gmail.com>
1 sibling, 0 replies; 14+ messages in thread
From: Gregory Stark @ 2009-03-14 04:02 UTC (permalink / raw)
To: Robert Haas <robertmhaas@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Alan Stange <stange@rentec.com>; Jignesh K. Shah <J.K.Shah@sun.com>; Greg Smith <gsmith@gregsmith.com>; pgsql-performance
Robert Haas <robertmhaas@gmail.com> writes:
> On Fri, Mar 13, 2009 at 10:06 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
>> I assume you meant effective_io_concurrency. We'd still need a special
>> case because the default is currently hard-wired at 1, not 0, if
>> configure thinks the function exists. Also there's a posix_fadvise call
>> in xlog.c that that parameter doesn't control anyhow.
>
> I think 1 should mean no prefetching, rather than 0. If the number of
> concurrent I/O requests was 0, that would mean you couldn't perform
> any I/O at all.
That is actually how I had intended it but apparently I messed it up at some
point such that later patches were doing some prefetching at 1 and there was
no way to disable it. When Tom reviewed it he corrected the inability to
disable prefetching by making 0 disable prefetching.
I didn't think it was worth raising as an issue but I didn't realize we were
currently doing prefetching by default? i didn't realize that. Even on a
system with posix_fadvise there's nothing much to be gained unless the data is
on a RAID device, so the original objection holds anyways. We shouldn't do any
prefetching unless the user tells us to.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's 24x7 Postgres support!
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: 8.4 Performance improvements: was Re: Proposal of tunable fix for scalability of 8.4
@ 2009-03-14 15:52 Tom Lane <tgl@sss.pgh.pa.us>
parent: Robert Haas <robertmhaas@gmail.com>
1 sibling, 0 replies; 14+ messages in thread
From: Tom Lane @ 2009-03-14 15:52 UTC (permalink / raw)
To: Robert Haas <robertmhaas@gmail.com>; +Cc: Gregory Stark <stark@enterprisedb.com>; Alan Stange <stange@rentec.com>; Jignesh K. Shah <J.K.Shah@sun.com>; Greg Smith <gsmith@gregsmith.com>; pgsql-performance
Robert Haas <robertmhaas@gmail.com> writes:
> On Fri, Mar 13, 2009 at 10:06 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> I assume you meant effective_io_concurrency. We'd still need a special
>> case because the default is currently hard-wired at 1, not 0, if
>> configure thinks the function exists.
> I think 1 should mean no prefetching, rather than 0.
No, 1 means "prefetch a single block ahead". It doesn't involve I/O
concurrency in the sense of multiple I/O requests being processed at
once; what it does give you is CPU vs I/O concurrency. 0 shuts that
down and returns the system to pre-8.4 behavior.
regards, tom lane
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: 8.4 Performance improvements: was Re: Proposal of tunable fix for scalability of 8.4
@ 2009-04-02 23:08 Bruce Momjian <bruce@momjian.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 14+ messages in thread
From: Bruce Momjian @ 2009-04-02 23:08 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Gregory Stark <stark@enterprisedb.com>; Alan Stange <stange@rentec.com>; Jignesh K. Shah <J.K.Shah@Sun.COM>; Greg Smith <gsmith@gregsmith.com>; pgsql-performance
Tom Lane wrote:
> Gregory Stark <stark@enterprisedb.com> writes:
> > Tom Lane <tgl@sss.pgh.pa.us> writes:
> >> Ugh. So apparently, we actually need to special-case Solaris to not
> >> believe that posix_fadvise works, or we'll waste cycles uselessly
> >> calling a do-nothing function. Thanks, Sun.
>
> > Do we? Or do we just document that setting effective_cache_size on Solaris
> > won't help?
>
> I assume you meant effective_io_concurrency. We'd still need a special
> case because the default is currently hard-wired at 1, not 0, if
> configure thinks the function exists. Also there's a posix_fadvise call
> in xlog.c that that parameter doesn't control anyhow.
The attached patch prevents the posix_fadvise() probe in configure on
Solaris, and adds a comment why. I have already documented why Solaris
can't do effective_io_concurrency.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Attachments:
[text/x-diff] /pgpatches/solaris (1.7K, ../../200904022308.n32N8oF06653@momjian.us/2-%2Fpgpatches%2Fsolaris)
download | inline diff:
Index: configure.in
===================================================================
RCS file: /cvsroot/pgsql/configure.in,v
retrieving revision 1.592
diff -c -c -r1.592 configure.in
*** configure.in 27 Mar 2009 19:58:11 -0000 1.592
--- configure.in 2 Apr 2009 21:23:36 -0000
***************
*** 1141,1150 ****
AC_FUNC_ACCEPT_ARGTYPES
PGAC_FUNC_GETTIMEOFDAY_1ARG
! AC_CHECK_FUNCS([cbrt dlopen fcvt fdatasync getpeereid getpeerucred getrlimit memmove poll posix_fadvise pstat readlink setproctitle setsid sigprocmask symlink sysconf towlower utime utimes waitpid wcstombs])
! AC_CHECK_DECLS(fdatasync, [], [], [#include <unistd.h>])
AC_CHECK_DECLS(posix_fadvise, [], [], [#include <fcntl.h>])
AC_CHECK_DECLS([strlcat, strlcpy])
# This is probably only present on Darwin, but may as well check always
AC_CHECK_DECLS(F_FULLFSYNC, [], [], [#include <fcntl.h>])
--- 1141,1157 ----
AC_FUNC_ACCEPT_ARGTYPES
PGAC_FUNC_GETTIMEOFDAY_1ARG
! AC_CHECK_FUNCS([cbrt dlopen fcvt fdatasync getpeereid getpeerucred getrlimit memmove poll pstat readlink setproctitle setsid sigprocmask symlink sysconf towlower utime utimes waitpid wcstombs])
! # posix_fadvise() is a no-op on Solaris, so don't incur function overhead
! # by calling it, 2009-04-02
! # http://src.opensolaris.org/source/xref/onnv/onnv-gate/usr/src/lib/libc/port/gen/posix_fadvise.c
! if test "$PORTNAME" != "solaris"; then
! AC_CHECK_FUNCS(posix_fadvise);
AC_CHECK_DECLS(posix_fadvise, [], [], [#include <fcntl.h>])
+ fi
+
+ AC_CHECK_DECLS(fdatasync, [], [], [#include <unistd.h>])
AC_CHECK_DECLS([strlcat, strlcpy])
# This is probably only present on Darwin, but may as well check always
AC_CHECK_DECLS(F_FULLFSYNC, [], [], [#include <fcntl.h>])
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: 8.4 Performance improvements: was Re: Proposal of tunable fix for scalability of 8.4
@ 2009-04-07 22:49 Bruce Momjian <bruce@momjian.us>
parent: Bruce Momjian <bruce@momjian.us>
0 siblings, 0 replies; 14+ messages in thread
From: Bruce Momjian @ 2009-04-07 22:49 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Gregory Stark <stark@enterprisedb.com>; Alan Stange <stange@rentec.com>; Jignesh K. Shah <J.K.Shah@Sun.COM>; Greg Smith <gsmith@gregsmith.com>; pgsql-performance
Bruce Momjian wrote:
> Tom Lane wrote:
> > Gregory Stark <stark@enterprisedb.com> writes:
> > > Tom Lane <tgl@sss.pgh.pa.us> writes:
> > >> Ugh. So apparently, we actually need to special-case Solaris to not
> > >> believe that posix_fadvise works, or we'll waste cycles uselessly
> > >> calling a do-nothing function. Thanks, Sun.
> >
> > > Do we? Or do we just document that setting effective_cache_size on Solaris
> > > won't help?
> >
> > I assume you meant effective_io_concurrency. We'd still need a special
> > case because the default is currently hard-wired at 1, not 0, if
> > configure thinks the function exists. Also there's a posix_fadvise call
> > in xlog.c that that parameter doesn't control anyhow.
>
> The attached patch prevents the posix_fadvise() probe in configure on
> Solaris, and adds a comment why. I have already documented why Solaris
> can't do effective_io_concurrency.
Updated patch applied; open item removed as complete.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Attachments:
[text/x-diff] /rtmp/diff (10.6K, ../../200904072249.n37MnMt25858@momjian.us/2-%2Frtmp%2Fdiff)
download | inline diff:
Index: configure
===================================================================
RCS file: /cvsroot/pgsql/configure,v
retrieving revision 1.635
diff -c -c -r1.635 configure
*** configure 4 Apr 2009 21:55:49 -0000 1.635
--- configure 7 Apr 2009 22:45:59 -0000
***************
*** 16324,16331 ****
!
! for ac_func in cbrt dlopen fcvt fdatasync getpeereid getpeerucred getrlimit memmove poll posix_fadvise pstat readlink setproctitle setsid sigprocmask symlink sysconf towlower utime utimes waitpid wcstombs
do
as_ac_var=`echo "ac_cv_func_$ac_func" | $as_tr_sh`
{ echo "$as_me:$LINENO: checking for $ac_func" >&5
--- 16324,16330 ----
! for ac_func in cbrt dlopen fcvt fdatasync getpeereid getpeerucred getrlimit memmove poll pstat readlink setproctitle setsid sigprocmask symlink sysconf towlower utime utimes waitpid wcstombs
do
as_ac_var=`echo "ac_cv_func_$ac_func" | $as_tr_sh`
{ echo "$as_me:$LINENO: checking for $ac_func" >&5
***************
*** 16419,16427 ****
done
! { echo "$as_me:$LINENO: checking whether fdatasync is declared" >&5
! echo $ECHO_N "checking whether fdatasync is declared... $ECHO_C" >&6; }
! if test "${ac_cv_have_decl_fdatasync+set}" = set; then
echo $ECHO_N "(cached) $ECHO_C" >&6
else
cat >conftest.$ac_ext <<_ACEOF
--- 16418,16434 ----
done
! # posix_fadvise() is a no-op on Solaris, so don't incur function overhead
! # by calling it, 2009-04-02
! # http://src.opensolaris.org/source/xref/onnv/onnv-gate/usr/src/lib/libc/port/gen/posix_fadvise.c
! if test "$PORTNAME" != "solaris"; then
!
! for ac_func in posix_fadvise
! do
! as_ac_var=`echo "ac_cv_func_$ac_func" | $as_tr_sh`
! { echo "$as_me:$LINENO: checking for $ac_func" >&5
! echo $ECHO_N "checking for $ac_func... $ECHO_C" >&6; }
! if { as_var=$as_ac_var; eval "test \"\${$as_var+set}\" = set"; }; then
echo $ECHO_N "(cached) $ECHO_C" >&6
else
cat >conftest.$ac_ext <<_ACEOF
***************
*** 16430,16442 ****
cat confdefs.h >>conftest.$ac_ext
cat >>conftest.$ac_ext <<_ACEOF
/* end confdefs.h. */
! #include <unistd.h>
int
main ()
{
! #ifndef fdatasync
! (void) fdatasync;
#endif
;
--- 16437,16539 ----
cat confdefs.h >>conftest.$ac_ext
cat >>conftest.$ac_ext <<_ACEOF
/* end confdefs.h. */
! /* Define $ac_func to an innocuous variant, in case <limits.h> declares $ac_func.
! For example, HP-UX 11i <limits.h> declares gettimeofday. */
! #define $ac_func innocuous_$ac_func
!
! /* System header to define __stub macros and hopefully few prototypes,
! which can conflict with char $ac_func (); below.
! Prefer <limits.h> to <assert.h> if __STDC__ is defined, since
! <limits.h> exists even on freestanding compilers. */
!
! #ifdef __STDC__
! # include <limits.h>
! #else
! # include <assert.h>
! #endif
!
! #undef $ac_func
!
! /* Override any GCC internal prototype to avoid an error.
! Use char because int might match the return type of a GCC
! builtin and then its argument prototype would still apply. */
! #ifdef __cplusplus
! extern "C"
! #endif
! char $ac_func ();
! /* The GNU C library defines this for functions which it implements
! to always fail with ENOSYS. Some functions are actually named
! something starting with __ and the normal name is an alias. */
! #if defined __stub_$ac_func || defined __stub___$ac_func
! choke me
! #endif
int
main ()
{
! return $ac_func ();
! ;
! return 0;
! }
! _ACEOF
! rm -f conftest.$ac_objext conftest$ac_exeext
! if { (ac_try="$ac_link"
! case "(($ac_try" in
! *\"* | *\`* | *\\*) ac_try_echo=\$ac_try;;
! *) ac_try_echo=$ac_try;;
! esac
! eval "echo \"\$as_me:$LINENO: $ac_try_echo\"") >&5
! (eval "$ac_link") 2>conftest.er1
! ac_status=$?
! grep -v '^ *+' conftest.er1 >conftest.err
! rm -f conftest.er1
! cat conftest.err >&5
! echo "$as_me:$LINENO: \$? = $ac_status" >&5
! (exit $ac_status); } && {
! test -z "$ac_c_werror_flag" ||
! test ! -s conftest.err
! } && test -s conftest$ac_exeext &&
! $as_test_x conftest$ac_exeext; then
! eval "$as_ac_var=yes"
! else
! echo "$as_me: failed program was:" >&5
! sed 's/^/| /' conftest.$ac_ext >&5
!
! eval "$as_ac_var=no"
! fi
!
! rm -f core conftest.err conftest.$ac_objext conftest_ipa8_conftest.oo \
! conftest$ac_exeext conftest.$ac_ext
! fi
! ac_res=`eval echo '${'$as_ac_var'}'`
! { echo "$as_me:$LINENO: result: $ac_res" >&5
! echo "${ECHO_T}$ac_res" >&6; }
! if test `eval echo '${'$as_ac_var'}'` = yes; then
! cat >>confdefs.h <<_ACEOF
! #define `echo "HAVE_$ac_func" | $as_tr_cpp` 1
! _ACEOF
!
! fi
! done
!
! { echo "$as_me:$LINENO: checking whether posix_fadvise is declared" >&5
! echo $ECHO_N "checking whether posix_fadvise is declared... $ECHO_C" >&6; }
! if test "${ac_cv_have_decl_posix_fadvise+set}" = set; then
! echo $ECHO_N "(cached) $ECHO_C" >&6
! else
! cat >conftest.$ac_ext <<_ACEOF
! /* confdefs.h. */
! _ACEOF
! cat confdefs.h >>conftest.$ac_ext
! cat >>conftest.$ac_ext <<_ACEOF
! /* end confdefs.h. */
! #include <fcntl.h>
!
! int
! main ()
! {
! #ifndef posix_fadvise
! (void) posix_fadvise;
#endif
;
***************
*** 16460,16496 ****
test -z "$ac_c_werror_flag" ||
test ! -s conftest.err
} && test -s conftest.$ac_objext; then
! ac_cv_have_decl_fdatasync=yes
else
echo "$as_me: failed program was:" >&5
sed 's/^/| /' conftest.$ac_ext >&5
! ac_cv_have_decl_fdatasync=no
fi
rm -f core conftest.err conftest.$ac_objext conftest.$ac_ext
fi
! { echo "$as_me:$LINENO: result: $ac_cv_have_decl_fdatasync" >&5
! echo "${ECHO_T}$ac_cv_have_decl_fdatasync" >&6; }
! if test $ac_cv_have_decl_fdatasync = yes; then
cat >>confdefs.h <<_ACEOF
! #define HAVE_DECL_FDATASYNC 1
_ACEOF
else
cat >>confdefs.h <<_ACEOF
! #define HAVE_DECL_FDATASYNC 0
_ACEOF
fi
! { echo "$as_me:$LINENO: checking whether posix_fadvise is declared" >&5
! echo $ECHO_N "checking whether posix_fadvise is declared... $ECHO_C" >&6; }
! if test "${ac_cv_have_decl_posix_fadvise+set}" = set; then
echo $ECHO_N "(cached) $ECHO_C" >&6
else
cat >conftest.$ac_ext <<_ACEOF
--- 16557,16595 ----
test -z "$ac_c_werror_flag" ||
test ! -s conftest.err
} && test -s conftest.$ac_objext; then
! ac_cv_have_decl_posix_fadvise=yes
else
echo "$as_me: failed program was:" >&5
sed 's/^/| /' conftest.$ac_ext >&5
! ac_cv_have_decl_posix_fadvise=no
fi
rm -f core conftest.err conftest.$ac_objext conftest.$ac_ext
fi
! { echo "$as_me:$LINENO: result: $ac_cv_have_decl_posix_fadvise" >&5
! echo "${ECHO_T}$ac_cv_have_decl_posix_fadvise" >&6; }
! if test $ac_cv_have_decl_posix_fadvise = yes; then
cat >>confdefs.h <<_ACEOF
! #define HAVE_DECL_POSIX_FADVISE 1
_ACEOF
else
cat >>confdefs.h <<_ACEOF
! #define HAVE_DECL_POSIX_FADVISE 0
_ACEOF
fi
! fi
!
! { echo "$as_me:$LINENO: checking whether fdatasync is declared" >&5
! echo $ECHO_N "checking whether fdatasync is declared... $ECHO_C" >&6; }
! if test "${ac_cv_have_decl_fdatasync+set}" = set; then
echo $ECHO_N "(cached) $ECHO_C" >&6
else
cat >conftest.$ac_ext <<_ACEOF
***************
*** 16499,16511 ****
cat confdefs.h >>conftest.$ac_ext
cat >>conftest.$ac_ext <<_ACEOF
/* end confdefs.h. */
! #include <fcntl.h>
int
main ()
{
! #ifndef posix_fadvise
! (void) posix_fadvise;
#endif
;
--- 16598,16610 ----
cat confdefs.h >>conftest.$ac_ext
cat >>conftest.$ac_ext <<_ACEOF
/* end confdefs.h. */
! #include <unistd.h>
int
main ()
{
! #ifndef fdatasync
! (void) fdatasync;
#endif
;
***************
*** 16529,16556 ****
test -z "$ac_c_werror_flag" ||
test ! -s conftest.err
} && test -s conftest.$ac_objext; then
! ac_cv_have_decl_posix_fadvise=yes
else
echo "$as_me: failed program was:" >&5
sed 's/^/| /' conftest.$ac_ext >&5
! ac_cv_have_decl_posix_fadvise=no
fi
rm -f core conftest.err conftest.$ac_objext conftest.$ac_ext
fi
! { echo "$as_me:$LINENO: result: $ac_cv_have_decl_posix_fadvise" >&5
! echo "${ECHO_T}$ac_cv_have_decl_posix_fadvise" >&6; }
! if test $ac_cv_have_decl_posix_fadvise = yes; then
cat >>confdefs.h <<_ACEOF
! #define HAVE_DECL_POSIX_FADVISE 1
_ACEOF
else
cat >>confdefs.h <<_ACEOF
! #define HAVE_DECL_POSIX_FADVISE 0
_ACEOF
--- 16628,16655 ----
test -z "$ac_c_werror_flag" ||
test ! -s conftest.err
} && test -s conftest.$ac_objext; then
! ac_cv_have_decl_fdatasync=yes
else
echo "$as_me: failed program was:" >&5
sed 's/^/| /' conftest.$ac_ext >&5
! ac_cv_have_decl_fdatasync=no
fi
rm -f core conftest.err conftest.$ac_objext conftest.$ac_ext
fi
! { echo "$as_me:$LINENO: result: $ac_cv_have_decl_fdatasync" >&5
! echo "${ECHO_T}$ac_cv_have_decl_fdatasync" >&6; }
! if test $ac_cv_have_decl_fdatasync = yes; then
cat >>confdefs.h <<_ACEOF
! #define HAVE_DECL_FDATASYNC 1
_ACEOF
else
cat >>confdefs.h <<_ACEOF
! #define HAVE_DECL_FDATASYNC 0
_ACEOF
Index: configure.in
===================================================================
RCS file: /cvsroot/pgsql/configure.in,v
retrieving revision 1.593
diff -c -c -r1.593 configure.in
*** configure.in 4 Apr 2009 21:55:50 -0000 1.593
--- configure.in 7 Apr 2009 22:45:59 -0000
***************
*** 1141,1150 ****
AC_FUNC_ACCEPT_ARGTYPES
PGAC_FUNC_GETTIMEOFDAY_1ARG
! AC_CHECK_FUNCS([cbrt dlopen fcvt fdatasync getpeereid getpeerucred getrlimit memmove poll posix_fadvise pstat readlink setproctitle setsid sigprocmask symlink sysconf towlower utime utimes waitpid wcstombs])
! AC_CHECK_DECLS(fdatasync, [], [], [#include <unistd.h>])
AC_CHECK_DECLS(posix_fadvise, [], [], [#include <fcntl.h>])
AC_CHECK_DECLS([strlcat, strlcpy])
# This is probably only present on Darwin, but may as well check always
AC_CHECK_DECLS(F_FULLFSYNC, [], [], [#include <fcntl.h>])
--- 1141,1157 ----
AC_FUNC_ACCEPT_ARGTYPES
PGAC_FUNC_GETTIMEOFDAY_1ARG
! AC_CHECK_FUNCS([cbrt dlopen fcvt fdatasync getpeereid getpeerucred getrlimit memmove poll pstat readlink setproctitle setsid sigprocmask symlink sysconf towlower utime utimes waitpid wcstombs])
! # posix_fadvise() is a no-op on Solaris, so don't incur function overhead
! # by calling it, 2009-04-02
! # http://src.opensolaris.org/source/xref/onnv/onnv-gate/usr/src/lib/libc/port/gen/posix_fadvise.c
! if test "$PORTNAME" != "solaris"; then
! AC_CHECK_FUNCS(posix_fadvise)
AC_CHECK_DECLS(posix_fadvise, [], [], [#include <fcntl.h>])
+ fi
+
+ AC_CHECK_DECLS(fdatasync, [], [], [#include <unistd.h>])
AC_CHECK_DECLS([strlcat, strlcpy])
# This is probably only present on Darwin, but may as well check always
AC_CHECK_DECLS(F_FULLFSYNC, [], [], [#include <fcntl.h>])
^ permalink raw reply [nested|flat] 14+ messages in thread
* SV: Oracle to postgres migration via ora2pg (blob data)
@ 2019-07-31 11:45 Niels Jespersen <NJN@dst.dk>
0 siblings, 0 replies; 14+ messages in thread
From: Niels Jespersen @ 2019-07-31 11:45 UTC (permalink / raw)
To: Daulat Ram <Daulat.Ram@exponential.com>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
I would look at the source table in Oracle first. It looks a lot like audit data. Perhaps all content is not needed in Postgres. If it is, then the table and lobs may benefit from being reorganised in oracle.
Alter table CLIENT_DB_AUDIT_LOG move;
Alter table CLIENT_DB_AUDIT_LOG move lob (SYS_LOB0000095961C00008$$);
-- Three more of these.
The syntax is from my the back of my head. You may need to look the details up.
Niels
Fra: Daulat Ram <Daulat.Ram@exponential.com>
Sendt: 31. juli 2019 13:32
Til: pgsql-general@lists.postgresql.org; pgsql-performance@lists.postgresql.org
Emne: Oracle to postgres migration via ora2pg (blob data)
Hello team,
We have to migrate a schema from oracle to postgres but there is one table that is having following large lob segments. This table is taking time to export. What parameters we have to set in ora2pg.conf to speed up the data export by ora2pg.
Table: CLIENT_DB_AUDIT_LOG
LOBSEGMENT SYS_LOB0000095961C00008$$ 80.26
LOBSEGMENT SYS_LOB0000095961C00007$$ 79.96
LOBSEGMENT SYS_LOB0000094338C00008$$ 8.84
LOBSEGMENT SYS_LOB0000084338C00007$$ 8.71
LOBSEGMENT SYS_LOB0000085961C00009$$ 5.32
VM Details are:
RAM 8GB
VCPUs 2 VCPU
Disk 40GB
Thanks,
^ permalink raw reply [nested|flat] 14+ messages in thread
end of thread, other threads:[~2019-07-31 11:45 UTC | newest]
Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-03-13 12:44 8.4 Performance improvements: was Re: Proposal of tunable fix for scalability of 8.4 Jignesh K. Shah <J.K.Shah@Sun.COM>
2009-03-13 13:43 ` Re: 8.4 Performance improvements: was Re: Proposal of tunable fix for scalability of 8.4 Gregory Stark <stark@enterprisedb.com>
2009-03-13 13:57 ` Re: 8.4 Performance improvements: was Re: Proposal of tunable fix for scalability of 8.4 Gregory Stark <stark@enterprisedb.com>
2009-03-13 15:20 ` Alan Stange <stange@rentec.com>
2009-03-13 17:23 ` Re: 8.4 Performance improvements: was Re: Proposal of tunable fix for scalability of 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2009-03-14 01:58 ` Re: 8.4 Performance improvements: was Re: Proposal of tunable fix for scalability of 8.4 Gregory Stark <stark@enterprisedb.com>
2009-03-14 02:06 ` Re: 8.4 Performance improvements: was Re: Proposal of tunable fix for scalability of 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2009-03-14 02:37 ` Re: 8.4 Performance improvements: was Re: Proposal of tunable fix for scalability of 8.4 Robert Haas <robertmhaas@gmail.com>
2009-03-14 04:02 ` Re: 8.4 Performance improvements: was Re: Proposal of tunable fix for scalability of 8.4 Gregory Stark <stark@enterprisedb.com>
2009-03-14 15:52 ` Re: 8.4 Performance improvements: was Re: Proposal of tunable fix for scalability of 8.4 Tom Lane <tgl@sss.pgh.pa.us>
2009-04-02 23:08 ` Re: 8.4 Performance improvements: was Re: Proposal of tunable fix for scalability of 8.4 Bruce Momjian <bruce@momjian.us>
2009-04-07 22:49 ` Re: 8.4 Performance improvements: was Re: Proposal of tunable fix for scalability of 8.4 Bruce Momjian <bruce@momjian.us>
2009-03-13 17:15 ` Greg Smith <gsmith@gregsmith.com>
2019-07-31 11:45 SV: Oracle to postgres migration via ora2pg (blob data) Niels Jespersen <NJN@dst.dk>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox