Re: [PERFORM] Best replication solution?
On Monday 06 April 2009 14:35:30 Andrew Sullivan wrote: *SkyTools/Londiste* - Don't know anything special about it. I've been quite impressed by the usability. It's not quite as flexible as Slony, but it has the same theory of operation. The documentation is not as voluminous, although it's also much handier as reference material than Slony's (which is, in my experience, a little hard to navigate if you don't already know the system pretty well). As a londiste user I find it really trustworthy solution, and very easy to use and understand. We made some recent efforts on documentation front: http://wiki.postgresql.org/wiki/SkyTools http://wiki.postgresql.org/wiki/Londiste_Tutorial Regards, -- dim signature.asc Description: This is a digitally signed message part.
Re: [PERFORM] Best replication solution?
Andrew Sullivan wrote: On Sun, Apr 05, 2009 at 11:36:33AM -0700, Lists wrote: *Slony-I* - I've used this in the past, but it's a huge pain to work with, caused serious performance issues under heavy load due to long running transactions (may not be the case anymore, it's been a while since I used it on a large database with many writes), and doesn't seem very reliable (I've had replication break on me multiple times). I'm also somewhat puzzled by the claim of unreliability: most of the actual replication failures I've ever seen under Slony are due to operator error (these are trivial to induce, alas -- aforementioned pain to work with again). Slony is baroque and confusing, but it's specifically designed to fail in safe ways (which is not true of some of the other systems: several of them have modes in which it's possible to have systems out of sync with each other, but with no way to detect as much. IMO, that's much worse, so we designed Slony to fail noisily if it was going to fail at all). From my experience - gained from unwittingly being in the wrong place at the wrong time and so being volunteered into helping people with Slony failures - it seems to be quite possible to have nodes out of sync and not be entirely aware of it - in addition to there being numerous ways to shoot yourself in the foot via operator error. Complexity seems to be the major evil here. I've briefly experimented with Londiste, and it is certainly much simpler to administer. Currently it lacks a couple of features Slony has (chained slaves and partial DDL support), but I'll be following its development closely - because if these can be added - whilst keeping the operator overhead (and the foot-gun) small, then this looks like a winner. regards Mark -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Best replication solution?
Lists wrote: I'm currently running 32bit FreeBSD so I can't really add more ram (PAE doesn't work well under FreeBSD from what I've read) That's probably left-over from the time many drivers were not 64-bit friendly. I've yet to see a new configuration that doesn't work with PAE (also, the default PAE configuration file is too conservative. Drivers that work on amd64 should work on PAE without problems). In any case, it's easy to try it - you can always boot the kernel.old. signature.asc Description: OpenPGP digital signature
Re: [PERFORM] plpgsql arrays
On Fri, 3 Apr 2009, Tom Lane wrote: ... alternatively is there a way to read two results streams simultaneously? Use two cursors and FETCH from each as needed? In recent releases you can even scroll backwards, which you're going to need to do to make a merge join work. What would be the syntax for putting a single row from a cursor into a variable? I have tried: FETCH INTO left left_cursor; which says syntax error, and left = FETCH left_cursor; which gives the error 'ERROR: missing datatype declaration at or near =' Matthew -- I've run DOOM more in the last few days than I have the last few months. I just love debugging ;-) -- Linus Torvalds -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] plpgsql arrays
Matthew Wakeling wrote: What would be the syntax for putting a single row from a cursor into a variable? I have tried: FETCH INTO left left_cursor; which says syntax error, and left = FETCH left_cursor; which gives the error 'ERROR: missing datatype declaration at or near =' Matthew Have to declare Left variable as record data type declaration part of the function -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] plpgsql arrays
On Tue, 7 Apr 2009, justin wrote: What would be the syntax for putting a single row from a cursor into a variable? I have tried: FETCH INTO left left_cursor; which says syntax error, and left = FETCH left_cursor; which gives the error 'ERROR: missing datatype declaration at or near =' Have to declare Left variable as record data type declaration part of the function It is. CREATE OR REPLACE FUNCTION overlap_gene_primer() RETURNS SETOF RECORD AS $$ DECLARE left location; retval RECORD; BEGIN DECLARE left_cursor NO SCROLL CURSOR FOR SELECT location FROM location, gene WHERE location.id = gene.id ORDER BY objectid, start, end; left = FETCH left_cursor; END; $$ LANGUAGE plpgsql; Matthew -- Prove to thyself that all circuits that radiateth and upon which thou worketh are grounded, lest they lift thee to high-frequency potential and cause thee to radiate also. -- The Ten Commandments of Electronics -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] plpgsql arrays
Matthew Wakeling wrote: On Tue, 7 Apr 2009, justin wrote: What would be the syntax for putting a single row from a cursor into a variable? I have tried: FETCH INTO left left_cursor; which says syntax error, and left = FETCH left_cursor; which gives the error 'ERROR: missing datatype declaration at or near "="' Have to declare Left variable as record data type declaration part of the function It is. CREATE OR REPLACE FUNCTION overlap_gene_primer() RETURNS SETOF RECORD AS $$ DECLARE left location; retval RECORD; BEGIN DECLARE left_cursor NO SCROLL CURSOR FOR SELECT location FROM location, gene WHERE location.id = gene.id ORDER BY objectid, start, end; left = FETCH left_cursor; END; $$ LANGUAGE plpgsql; Matthew Change the type to Record from the help file FETCH retrieves the next row from the cursor into a target, which might be a row variable, a record variable, or a comma-separated list of simple variables, just like SELECT INTO. If there is no next row, the target is set to NULL(s). As with SELECT INTO, the special variable FOUND can be checked to see whether a row was obtained or not
Re: [PERFORM] plpgsql arrays
Matthew Wakeling matt...@flymine.org writes: CREATE OR REPLACE FUNCTION overlap_gene_primer() RETURNS SETOF RECORD AS $$ DECLARE left location; retval RECORD; BEGIN DECLARE left_cursor NO SCROLL CURSOR FOR SELECT location FROM location, gene WHERE location.id = gene.id ORDER BY objectid, start, end; left = FETCH left_cursor; END; $$ LANGUAGE plpgsql; Well, the DECLARE for the cursor should go in the DECLARE section, and the syntax for the FETCH should be FETCH cursorname INTO recordvariablename; and I'm too lazy to check right now but I think you might be missing an OPEN for the cursor. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] plpgsql arrays
On Tue, 7 Apr 2009, Tom Lane wrote: Matthew Wakeling matt...@flymine.org writes: CREATE OR REPLACE FUNCTION overlap_gene_primer() RETURNS SETOF RECORD AS $$ DECLARE left location; retval RECORD; BEGIN DECLARE left_cursor NO SCROLL CURSOR FOR SELECT location FROM location, gene WHERE location.id = gene.id ORDER BY objectid, start, end; left = FETCH left_cursor; END; $$ LANGUAGE plpgsql; Well, the DECLARE for the cursor should go in the DECLARE section, and the syntax for the FETCH should be FETCH cursorname INTO recordvariablename; and I'm too lazy to check right now but I think you might be missing an OPEN for the cursor. Yeah, thanks to Justin I found the plpgsql docs for cursors. The main cursors docs should really link there. This seems to do what I want: CREATE OR REPLACE FUNCTION overlap_gene_primer() RETURNS SETOF RECORD AS $$ DECLARE left_cursor NO SCROLL CURSOR FOR SELECT location.* FROM location, gene WHERE location.subjectid = gene.id ORDER BY objectid, start, end; left location; BEGIN OPEN left_cursor; FETCH left_cursor INTO left; END; $$ LANGUAGE plpgsql; Matthew -- Lord grant me patience, and I want it NOW! -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Best replication solution?
On Tue, Apr 07, 2009 at 10:31:02PM +1200, Mark Kirkwood wrote: From my experience - gained from unwittingly being in the wrong place at the wrong time and so being volunteered into helping people with Slony failures - it seems to be quite possible to have nodes out of sync and not be entirely aware of it I should have stated that differently. First, you're right that if you don't know where to look or what to look for, you can easily be unaware of nodes being out of sync. What's not a problem with Slony is that the nodes can get out of internally consistent sync state: if you have a node that is badly lagged, at least it represents, for sure, an actual point in time of the origin set's history. Some of the replication systems aren't as careful about this, and it's possible to get the replica into a state that never happened on the origin. That's much worse, in my view. In addition, it is not possible that Slony's system tables report the replica as being up to date without them actually being so, because the system tables are updated in the same transaction as the data is sent. It's hard to read those tables, however, because you have to check every node and understand all the states. Complexity seems to be the major evil here. Yes. Slony is massively complex. simpler to administer. Currently it lacks a couple of features Slony has (chained slaves and partial DDL support), but I'll be following its development closely - because if these can be added - whilst keeping the operator overhead (and the foot-gun) small, then this looks like a winner. Well, those particular features -- which are indeed the source of much of the complexity in Slony -- were planned in from the beginning. Londiste aimed to be simpler, so it would be interesting to see whether those features could be incorporated without the same complication. A -- Andrew Sullivan a...@crankycanuck.ca -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] plpgsql arrays
Merlin Moncure mmonc...@gmail.com writes: On Tue, Apr 7, 2009 at 11:18 AM, Matthew Wakeling matt...@flymine.org wrote: What would be the syntax for putting a single row from a cursor into a variable? I have tried: FETCH INTO left left_cursor; according to the docs, http://www.postgresql.org/docs/8.3/interactive/plpgsql-cursors.html#PLPGSQL-CURSOR-USING Subsequent discussion showed that the problem was Matthew hadn't found that page. I guess that at least the DECLARE CURSOR reference page ought to have something like if you are trying to use cursors in plpgsql, see link. Matthew, where *were* you looking exactly? regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] plpgsql arrays
On Tue, Apr 7, 2009 at 11:18 AM, Matthew Wakeling matt...@flymine.org wrote: On Fri, 3 Apr 2009, Tom Lane wrote: ... alternatively is there a way to read two results streams simultaneously? Use two cursors and FETCH from each as needed? In recent releases you can even scroll backwards, which you're going to need to do to make a merge join work. What would be the syntax for putting a single row from a cursor into a variable? I have tried: FETCH INTO left left_cursor; according to the docs, Examples: FETCH curs1 INTO rowvar; FETCH curs2 INTO foo, bar, baz; FETCH LAST FROM curs3 INTO x, y; FETCH RELATIVE -2 FROM curs4 INTO x; http://www.postgresql.org/docs/8.3/interactive/plpgsql-cursors.html#PLPGSQL-CURSOR-USING merlin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] determining the locks that will be held by a query
I know that EXPLAIN will show the query plan. I know that pg_locks will show the locks currently held for activity transactions. Is there a way to determine what locks a query will hold when it is executed? Thanks, Brian -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] 8.4 Performance improvements: was Re: Proposal of tunable fix for scalability of 8.4
Bruce Momjian wrote:
Tom Lane wrote:
Gregory Stark st...@enterprisedb.com writes:
Tom Lane t...@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 br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
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 - 1.635
--- configure 7 Apr 2009 22:45:59 -
***
*** 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) 2conftest.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
!
