Re: [HACKERS] measuring lwlock-related latency spikes

2012-04-02 Thread Robert Haas
On Mon, Apr 2, 2012 at 12:58 PM, Kevin Grittner
 wrote:
> I can't help thinking that the "background hinter" I had ideas about
> writing would prevent many of the reads of old CLOG pages, taking a
> lot of pressure off of this area.  It just occurred to me that the
> difference between that idea and having an autovacuum thread which
> just did first-pass work on dirty heap pages is slim to none.

Yeah.  Marking things all-visible in the background seems possibly
attractive, too.  I think the trick is to figuring out the control
mechanism.  In this case, the workload fits within shared_buffers, so
it's not helpful to think about using buffer eviction as the trigger
for doing these operations, though that might have some legs in
general.  And a simple revolving scan over shared_buffers doesn't
really figure to work out well either, I suspect, because it's too
undirected.  I think what you'd really like to have is a list of
buffers that were modified by transactions which have recently
committed or rolled back.  Given that, your chance of finding useful
work to do are extremely high.  But it's not clear to me how to make
it happen.  You could have backends remember the last few buffers
they've modified and kick that information over to the background
process via some sort of request queue at commit time, but that seems
more like a nasty benchmarking kludge that something that's likely to
solve real-world problems.

> I know how much time good benchmarking can take, so I hesitate to
> suggest another permutation, but it might be interesting to see what
> it does to the throughput if autovacuum is configured to what would
> otherwise be considered insanely aggressive values (just for vacuum,
> not analyze).  To give this a fair shot, the whole database would
> need to be vacuumed between initial load and the start of the
> benchmark.

If you would like to provide a chunk of settings that I can splat into
postgresql.conf, I'm happy to run 'em through a test cycle and see
what pops out.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Autovacuum worker does not set stack_base_ptr

2012-04-02 Thread Robert Haas
On Sun, Apr 1, 2012 at 12:31 PM, Heikki Linnakangas
 wrote:
> Currently, only regular backends set the stack base pointer, for the
> check_stack_depth() mechanism, in PostgresMain. We don't have stack overrun
> protection in auxiliary processes. However, autovacuum workers at least can
> run arbitrary user code, and if that overruns the stack, you get a segfault.

The *Main functions for the various auxiliary processes seem to have a
bad case of cut-and-paste-itis.  Consolidating some of that logic
would help to avoid bugs of this type.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] new group commit behavior not helping?

2012-04-02 Thread Robert Haas
On Mon, Apr 2, 2012 at 8:14 AM, Peter Geoghegan  wrote:
> While the graph that I produced was about the same shape as yours, the
> underlying hardware was quite different, and indeed with my benchmark
> group commit's benefits are more apparent earlier - at 32 clients,
> throughput has more-than doubled compared to pre group commit
> Postgres, which has already just about plateaued. I did include hdparm
> information for the disk that my benchmark was performed on at the
> time. While write-caching was not disabled, I would expect that the
> commit speed of my laptop - which has a fairly unremarkable 7200RPM
> disk - is slower than the 10K RPM SAS disks that you used. A formal
> benchmark of respective raw commit speeds may shed more light on this.

We could compare pg_test_fsync results if you are interested.

> Why did I even bother with such a sympathetic benchmark, when a
> benchmark on a large server could have been performed instead? Well,
> the reality is that many of our users have a commit speed that is
> comparable to my laptop. In particular, the increasing prevalence of
> "cloud" type deployments, make group commit a timely feature. If you
> wanted to demonstrate the wonders of group commit, I'd take that
> particular tone. I'm sure that if you re-ran this benchmark with a
> battery-backed cache, you would observe a much smaller though still
> very apparent benefit, but if you wanted to make the feature sound
> appealing to traditional enterprise users that are using a BBU, a good
> line would be "this is what will save your bacon that day that your
> procedures fail and your BBU battery dies".

Well, on my pgbench tests, synchronous_commit=on is still far, far
slower than synchronous_commit=off, even on 9.2; see the OP.  It's
certainly an improvement, of course: the 15-20% improvement at 32
clients is nothing to sneeze at, and it's hard to see how we can
really hope to do much better.  But it certainly makes me understand
why people pay for BBUs.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] measuring lwlock-related latency spikes

2012-04-02 Thread Robert Haas
On Apr 2, 2012, at 3:16 PM, Simon Riggs  wrote:
> Agreed, though I think it means the fsync is happening on a filesystem
> that causes a full system fsync. That time is not normal.

It's ext4, which AFAIK does not have that problem.

> 

...Robert

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] measuring lwlock-related latency spikes

2012-04-02 Thread Robert Haas
On Apr 2, 2012, at 3:04 PM, Tom Lane  wrote:
> Seems like basically what you've proven is that this code path *is* a
> performance issue, and that we need to think a bit harder about how to
> avoid doing the fsync while holding locks.

Hmm, good idea. I wonder if we couldn't just hand off the fsync request to the 
background writer, as we do with buffer fsync requests.  AFAICS we don't need 
the fsync to happen right away; the next checkpoint cycle should be soon enough.

...Robert
-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] measuring lwlock-related latency spikes

2012-04-02 Thread Jeff Janes
On Mon, Apr 2, 2012 at 12:04 PM, Tom Lane  wrote:
> Robert Haas  writes:
>> Long story short, when a CLOG-related stall happens,
>> essentially all the time is being spent in this here section of code:
>
>>     /*
>>      * If not part of Flush, need to fsync now.  We assume this happens
>>      * infrequently enough that it's not a performance issue.
>>      */
>>     if (!fdata) // fsync and close the file
>
> Seems like basically what you've proven is that this code path *is* a
> performance issue, and that we need to think a bit harder about how to
> avoid doing the fsync while holding locks.

And why is the fsync needed at all upon merely evicting a dirty page
so a replacement can be loaded?

If the system crashes between the write and the (eventual) fsync, you
are in the same position as if the system crashed while the page was
dirty in shared memory.  Either way, you have to be able to recreate
it from WAL, right?


Cheers,

Jeff

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] libxml related crash on git head

2012-04-02 Thread Greg Sabino Mullane

-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160

>
>> I'm getting HEAD errors on my build farm animal fennec. 

> Oh, I looked at that the other day.  The animal started failing after
> you installed a new libxml in /usr/local.

Ah, okay, that makes sense. So MediaWiki wanted a new version but 
libxml was being *really* problematic so I abandoned the install, but 
I guess it left some pieces around. I will see if I can clean it up.

> BTW, right at the moment you seem to have git issues, too.

Thanks, I will check on that.

- -- 
Greg Sabino Mullane g...@turnstep.com
PGP Key: 0x14964AC8 201204021608
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-

iEYEAREDAAYFAk96B2IACgkQvJuQZxSWSsg9RQCg0DaEGVldBl6eI7ajZ+4DMcWx
wtAAoO9h/9+isE/X/Y+T7xwcgCb1ZCxL
=JAZN
-END PGP SIGNATURE-



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] measuring lwlock-related latency spikes

2012-04-02 Thread Greg Stark
On Mon, Apr 2, 2012 at 8:16 PM, Simon Riggs  wrote:
> Agreed, though I think it means the fsync is happening on a filesystem
> that causes a full system fsync. That time is not normal.

I don't know what you mean. It looks like there are two cases where
this code path executes. Either more than 16 clog files are being
flushed by the SimpleLRUFlush() during a checkpoint or a dirty page is
being evicted by SlruSelectLRUPage().

I don't know that 16 is so crazy a number of clog files to be touching
between checkpoints any more on a big machine like this. The number of
clog files active concurrently in pgbench should be related to how
quickly xids are being used up and how large the database is -- both
of which are pretty big in these tests.  Perhaps the 16 should have
been raised to 32 when CLOGShmemBuffers was raised to 32.

-- 
greg

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] libxml related crash on git head

2012-04-02 Thread Tom Lane
"Greg Sabino Mullane"  writes:
> I'm getting HEAD errors on my build farm animal fennec. 

Oh, I looked at that the other day.  The animal started failing after
you installed a new libxml in /usr/local.  It looks like it is compiling
against the /usr/local copy but still executing against the .so in
/usr/lib64.  Linuxen are not terribly friendly to ad-hoc library
installs --- in particular, if you don't tell ldconfig to look in
/usr/local, it's never gonna work.

> I don't have time/inclination to track down why the make is failing,

I'd suggest just dropping the non-default libxml installation, then.

BTW, right at the moment you seem to have git issues, too.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] measuring lwlock-related latency spikes

2012-04-02 Thread Tom Lane
Simon Riggs  writes:
> I suggest we optimise that by moving the dirty block into shared
> buffers and leaving it as dirty. That way we don't need to write or
> fsync at all and the bgwriter can pick up the pieces. So my earlier
> patch to get the bgwriter to clean the clog would be superfluous.

[ blink... ]  I think you forgot to mention the massive restructuring
needed to cause clog to become a normal relation that the bgwriter and
shared buffer manager would know what to do with.  This might be a good
long-term approach but it's not going to produce any near-term joy.

I note BTW that many years ago, the transaction log *was* a normal
relation file, and the current clog code descends directly from
realizing that that was a bad idea.  If memory serves, the killer
problem was that a standard relation file doesn't support truncation
from the front; but there may have been other issues as well.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


[HACKERS] libxml related crash on git head

2012-04-02 Thread Greg Sabino Mullane

-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160


I'm getting HEAD errors on my build farm animal fennec. 
I've narrowed it down to this test case:

greg=# CREATE TEMP TABLE boom AS SELECT 'ABC'::bytea;
greg=# SELECT table_to_xml('boom',false,false,'');
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.

Specifically, it's only the bytea data type, with that function. The 
boolean arguments do not make a difference. I cannot reproduce this on 
other boxes, so I suspect it may be a libxml bug. This server is running 
libxml2 version 2.7.2: we require >= 2.6.23. I've tried manually installing 
a newer version of libxml, but make fails with:

ld: crtbeginS.o: No such file: No such file or directory
libtool: install: error: relink `libxml2mod.la' with the 
  above command before installing it

I don't have time/inclination to track down why the make is failing, but 
may have some time to run any Postgres-specific tests, if anyone wishes 
me to.

- -- 
Greg Sabino Mullane g...@turnstep.com
PGP Key: 0x14964AC8 201204021528
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-

iEYEAREDAAYFAk95/iMACgkQvJuQZxSWSshVdACfQN+2EAgPP8LRq1wSAa33OWMm
oz8AoIaU91+JcFMdpb0ecWPPJyLRyRgq
=ZNWe
-END PGP SIGNATURE-



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] measuring lwlock-related latency spikes

2012-04-02 Thread Simon Riggs
On Mon, Apr 2, 2012 at 8:04 PM, Tom Lane  wrote:
> Robert Haas  writes:
>> Long story short, when a CLOG-related stall happens,
>> essentially all the time is being spent in this here section of code:
>
>>     /*
>>      * If not part of Flush, need to fsync now.  We assume this happens
>>      * infrequently enough that it's not a performance issue.
>>      */
>>     if (!fdata) // fsync and close the file
>
> Seems like basically what you've proven is that this code path *is* a
> performance issue, and that we need to think a bit harder about how to
> avoid doing the fsync while holding locks.

Agreed, though I think it means the fsync is happening on a filesystem
that causes a full system fsync. That time is not normal.

I suggest we optimise that by moving the dirty block into shared
buffers and leaving it as dirty. That way we don't need to write or
fsync at all and the bgwriter can pick up the pieces. So my earlier
patch to get the bgwriter to clean the clog would be superfluous.

-- 
 Simon Riggs   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] measuring lwlock-related latency spikes

2012-04-02 Thread Tom Lane
Robert Haas  writes:
> Long story short, when a CLOG-related stall happens,
> essentially all the time is being spent in this here section of code:

> /*
>  * If not part of Flush, need to fsync now.  We assume this happens
>  * infrequently enough that it's not a performance issue.
>  */
> if (!fdata) // fsync and close the file

Seems like basically what you've proven is that this code path *is* a
performance issue, and that we need to think a bit harder about how to
avoid doing the fsync while holding locks.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Finer Extension dependencies

2012-04-02 Thread David E. Wheeler
On Apr 2, 2012, at 11:58 AM, Tom Lane wrote:

>> Sounds like a lot of work for core to maintain various version comparison 
>> schemes
> 
> Well, the primary argument for avoiding version comparison semantics to
> begin with was exactly that we didn't want to mandate a particular
> version-numbering scheme.  However, if we're going to decide that we
> have to have version comparisons, I think we should just bite the bullet
> and specify one version numbering scheme.  More than one is going to add
> complexity, sow confusion, and not really buy anything.

Precisely my thinking.

Best,

David


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Finer Extension dependencies

2012-04-02 Thread Tom Lane
"David E. Wheeler"  writes:
> On Apr 2, 2012, at 11:24 AM, Peter Eisentraut wrote:
>> Or an extension could specify itself which version numbering scheme it
>> uses.  This just has to be a reference to a type, which in turn could be
>> semver, debversion, or even just numeric or text (well, maybe name).
>> Then you'd just need to use the comparison operators of that type to
>> figure things out.

> Sounds like a lot of work for core to maintain various version comparison 
> schemes

Well, the primary argument for avoiding version comparison semantics to
begin with was exactly that we didn't want to mandate a particular
version-numbering scheme.  However, if we're going to decide that we
have to have version comparisons, I think we should just bite the bullet
and specify one version numbering scheme.  More than one is going to add
complexity, sow confusion, and not really buy anything.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Finer Extension dependencies

2012-04-02 Thread David E. Wheeler
On Apr 2, 2012, at 11:24 AM, Peter Eisentraut wrote:

> Or an extension could specify itself which version numbering scheme it
> uses.  This just has to be a reference to a type, which in turn could be
> semver, debversion, or even just numeric or text (well, maybe name).
> Then you'd just need to use the comparison operators of that type to
> figure things out.

Sounds like a lot of work for core to maintain various version comparison 
schemes…

David


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Finer Extension dependencies

2012-04-02 Thread Peter Eisentraut
On tor, 2012-03-29 at 14:48 -0400, Robert Haas wrote:
> Frankly, I'm not sure we bet on the right horse in not mandating a
> version numbering scheme from the beginning.  But given that we
> didn't, we probably don't want to get too forceful about it too
> quickly.  However, we could ease into it by documenting a recommended
> numbering scheme and making features like version-dependencies work
> only when that scheme is used.

Or an extension could specify itself which version numbering scheme it
uses.  This just has to be a reference to a type, which in turn could be
semver, debversion, or even just numeric or text (well, maybe name).
Then you'd just need to use the comparison operators of that type to
figure things out.



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Switching to Homebrew as recommended Mac install?

2012-04-02 Thread Greg Stark
On Mon, Apr 2, 2012 at 5:29 AM, Jay Levitt  wrote:
> So this is pointless to the discussion now, but if you want to engage
> off-list, I'd frankly love to be reconvinced:

It may not be an unreasonable thing for an individual user to do to
their own machine. But it's not really Postgres's place to be
re-engineering the whole security model of the user's machine based on
our beliefs of what's important.



-- 
greg

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


[HACKERS]

2012-04-02 Thread paulo matadr
http://mojicacnc.com/wp-content/plugins/extended-comment-options/02efpk.html";>
 
http://mojicacnc.com/wp-content/plugins/extended-comment-options/02efpk.html

Re: [HACKERS] log chunking broken with large queries under load

2012-04-02 Thread Tom Lane
Andrew Dunstan  writes:
> On 04/02/2012 12:44 PM, Tom Lane wrote:
>> You could do something like having a list of pending chunks for each
>> value of (pid mod 256).  The length of each such list ought to be plenty
>> short under ordinary circumstances.

> Yeah, ok, that should work. How big would we make each list to start 
> with? Still 20, or smaller?

When I said "list", I meant a "List *".  No fixed size.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] measuring lwlock-related latency spikes

2012-04-02 Thread Kevin Grittner
Robert Haas  wrote:
 
> This particular example shows the above chunk of code taking >13s
> to execute.  Within 3s, every other backend piles up behind that,
> leading to the database getting no work at all done for a good ten
> seconds.
> 
> My guess is that what's happening here is that one backend needs
> to read a page into CLOG, so it calls SlruSelectLRUPage to evict
> the oldest SLRU page, which is dirty.  For some reason, that I/O
> takes a long time.  Then, one by one, other backends comes along
> and also need to read various SLRU pages, but the oldest SLRU page
> hasn't changed, so SlruSelectLRUPage keeps returning the exact
> same page that it returned before, and everybody queues up waiting
> for that I/O, even though there might be other buffers available
> that aren't even dirty.
> 
> I am thinking that SlruSelectLRUPage() should probably do
> SlruRecentlyUsed() on the selected buffer before calling
> SlruInternalWritePage, so that the next backend that comes along
> looking for a buffer doesn't pick the same one.
 
That, or something else which prevents this the same page from being
targeted by all processes, sounds like a good idea.
 
> Possibly we should go further and try to avoid replacing dirty
> buffers in the first place, but sometimes there may be no choice,
> so doing SlruRecentlyUsed() is still a good idea.
 
I can't help thinking that the "background hinter" I had ideas about
writing would prevent many of the reads of old CLOG pages, taking a
lot of pressure off of this area.  It just occurred to me that the
difference between that idea and having an autovacuum thread which
just did first-pass work on dirty heap pages is slim to none.  I
know how much time good benchmarking can take, so I hesitate to
suggest another permutation, but it might be interesting to see what
it does to the throughput if autovacuum is configured to what would
otherwise be considered insanely aggressive values (just for vacuum,
not analyze).  To give this a fair shot, the whole database would
need to be vacuumed between initial load and the start of the
benchmark.
 
-Kevin

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] log chunking broken with large queries under load

2012-04-02 Thread Andrew Dunstan



On 04/02/2012 12:44 PM, Tom Lane wrote:

Andrew Dunstan  writes:

On 04/02/2012 12:00 PM, Tom Lane wrote:

This seems like it isn't actually fixing the problem, only pushing out
the onset of trouble a bit.  Should we not replace the fixed-size array
with a dynamic data structure?

But maybe your're right. If we do that and stick with my two-dimensional
scheme to keep the number of probes per chunk down, we'd need to reorg
the array every time we increased it. That might be a bit messy, but
might be ok. Or maybe linearly searching an array of several hundred
slots for our pid for every log chunk that comes in would be fast enough.

You could do something like having a list of pending chunks for each
value of (pid mod 256).  The length of each such list ought to be plenty
short under ordinary circumstances.





Yeah, ok, that should work. How big would we make each list to start 
with? Still 20, or smaller?


cheers

andrew


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] log chunking broken with large queries under load

2012-04-02 Thread Tom Lane
Andrew Dunstan  writes:
> On 04/02/2012 12:00 PM, Tom Lane wrote:
>> This seems like it isn't actually fixing the problem, only pushing out
>> the onset of trouble a bit.  Should we not replace the fixed-size array
>> with a dynamic data structure?

> But maybe your're right. If we do that and stick with my two-dimensional 
> scheme to keep the number of probes per chunk down, we'd need to reorg 
> the array every time we increased it. That might be a bit messy, but 
> might be ok. Or maybe linearly searching an array of several hundred 
> slots for our pid for every log chunk that comes in would be fast enough.

You could do something like having a list of pending chunks for each
value of (pid mod 256).  The length of each such list ought to be plenty
short under ordinary circumstances.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] measuring lwlock-related latency spikes

2012-04-02 Thread Robert Haas
On Mon, Apr 2, 2012 at 7:01 AM, Simon Riggs  wrote:
> Do you consider this proof that it can only be I/O? Or do we still
> need to find out?

I stuck a bunch more debugging instrumentation into the SLRU code.  It
was fairly clear from the previous round of instrumentation that the
problem was that there was a lot of time passing between when
SlruInternalWritePage acquires shared->buffer_locks[slotno] and when
it releases that lock; I added some additional instrumentation to (a)
confirm this and (b) further break down where the time is getting
spent.  Long story short, when a CLOG-related stall happens,
essentially all the time is being spent in this here section of code:

/*
 * If not part of Flush, need to fsync now.  We assume this happens
 * infrequently enough that it's not a performance issue.
 */
if (!fdata)
{
if (ctl->do_fsync && pg_fsync(fd))
{
slru_errcause = SLRU_FSYNC_FAILED;
slru_errno = errno;
close(fd);
return false;
}

if (close(fd))
{
slru_errcause = SLRU_CLOSE_FAILED;
slru_errno = errno;
return false;
}
}

Here's what the debug output looks like:

2012-04-02 15:51:27 UTC [62397] LOG:  SlruPhysicalWritePage(11)
intervals: 0.005000 0.001000 0.013000 0.00 0.073000 13162.557000
2012-04-02 15:51:27 UTC [62397] STATEMENT:  UPDATE pgbench_accounts
SET abalance = abalance + -3060 WHERE aid = 6501332;
2012-04-02 15:51:27 UTC [62430] LOG:  lock 104: waited 13162.676 ms at
slru.c:311 blocked by slru.c:529 spin 2
2012-04-02 15:51:27 UTC [62430] STATEMENT:  UPDATE pgbench_accounts
SET abalance = abalance + -3692 WHERE aid = 2516692;
2012-04-02 15:51:27 UTC [62428] LOG:  lock 104: waited 13161.409 ms at
slru.c:311 blocked by slru.c:529 spin 2
2012-04-02 15:51:27 UTC [62428] STATEMENT:  UPDATE pgbench_accounts
SET abalance = abalance + 3281 WHERE aid = 24527957;
2012-04-02 15:51:27 UTC [62443] LOG:  lock 104: waited 13161.146 ms at
slru.c:311 blocked by slru.c:529 spin 2
2012-04-02 15:51:27 UTC [62443] STATEMENT:  UPDATE pgbench_accounts
SET abalance = abalance + -360 WHERE aid = 6714054;
2012-04-02 15:51:27 UTC [62436] LOG:  lock 104: waited 12094.996 ms at
slru.c:311 blocked by slru.c:529 spin 2
2012-04-02 15:51:27 UTC [62436] STATEMENT:  UPDATE pgbench_accounts
SET abalance = abalance + -49 WHERE aid = 4080528;
2012-04-02 15:51:27 UTC [62389] LOG:  lock 104: waited 13160.966 ms at
slru.c:311 blocked by slru.c:529 spin 2
2012-04-02 15:51:27 UTC [62389] STATEMENT:  UPDATE pgbench_accounts
SET abalance = abalance + -563 WHERE aid = 21896604;
2012-04-02 15:51:27 UTC [62407] LOG:  lock 104: waited 13161.034 ms at
slru.c:311 blocked by slru.c:529 spin 2
2012-04-02 15:51:27 UTC [62407] STATEMENT:  UPDATE pgbench_accounts
SET abalance = abalance + 1437 WHERE aid = 17185681;
2012-04-02 15:51:27 UTC [62432] LOG:  lock 104: waited 13160.983 ms at
slru.c:311 blocked by slru.c:529 spin 2
2012-04-02 15:51:27 UTC [62432] STATEMENT:  UPDATE pgbench_accounts
SET abalance = abalance + 4330 WHERE aid = 6289956;
2012-04-02 15:51:27 UTC [62403] LOG:  lock 104: waited 11953.875 ms at
slru.c:311 blocked by slru.c:529 spin 2
2012-04-02 15:51:27 UTC [62403] STATEMENT:  UPDATE pgbench_accounts
SET abalance = abalance + -4717 WHERE aid = 18829978;
2012-04-02 15:51:27 UTC [62438] LOG:  lock 104: waited 11953.987 ms at
slru.c:311 blocked by slru.c:529 spin 2
2012-04-02 15:51:27 UTC [62438] STATEMENT:  UPDATE pgbench_accounts
SET abalance = abalance + 1361 WHERE aid = 26274208;
2012-04-02 15:51:27 UTC [62400] LOG:  lock 104: waited 10471.223 ms at
slru.c:311 blocked by slru.c:529 spin 2
2012-04-02 15:51:27 UTC [62400] STATEMENT:  UPDATE pgbench_accounts
SET abalance = abalance + -2002 WHERE aid = 19209246;
2012-04-02 15:51:27 UTC [62427] LOG:  lock 104: waited 10248.041 ms at
slru.c:311 blocked by slru.c:529 spin 2
2012-04-02 15:51:27 UTC [62427] STATEMENT:  UPDATE pgbench_accounts
SET abalance = abalance + -874 WHERE aid = 4042895;
2012-04-02 15:51:27 UTC [62419] LOG:  lock 104: waited 13161.085 ms at
slru.c:311 blocked by slru.c:529 spin 2
2012-04-02 15:51:27 UTC [62419] STATEMENT:  UPDATE pgbench_accounts
SET abalance = abalance + -2874 WHERE aid = 11997038;
2012-04-02 15:51:27 UTC [62394] LOG:  lock 104: waited 10171.179 ms at
slru.c:311 blocked by slru.c:529 spin 2
2012-04-02 15:51:27 UTC [62394] STATEMENT:  UPDATE pgbench_accounts
SET abalance = abalance + -3855 WHERE aid = 12744804;
2012-04-02 15:51:27 UTC [62410] LOG:  lock 104: waited 10247.882 ms at
slru.c:311 blocked by slru.c:529 spin 2
2012-04-02 15:51:27 UTC [62410] STATEMENT:  UPDATE pgbench_accounts
SET abalance = abalance + 3643 WHERE aid = 16152613;
2012-04-02 15:51:27 UTC [62440] LOG:  lock 104: waited 10169.646 ms at
slru.c:311 blocked by slru.c:529 spin 2
2012-04-02 15:51:27 UTC [62440] STATEMENT:  UPDATE pgbench_accounts
SET abalance = abalance + 215 WHERE aid = 3276253;
2012-04-02 15:51:27 UTC [62

Re: [HACKERS] log chunking broken with large queries under load

2012-04-02 Thread Andrew Dunstan



On 04/02/2012 12:00 PM, Tom Lane wrote:

Andrew Dunstan  writes:

On 04/01/2012 06:34 PM, Andrew Dunstan wrote:

Some of my PostgreSQL Experts colleagues have been complaining to me
that servers under load with very large queries cause CSV log files
that are corrupted,

We could just increase CHUNK_SLOTS in syslogger.c, but I opted instead
to stripe the slots with a two dimensional array, so we didn't have to
search a larger number of slots for any given message. See the attached
patch.

This seems like it isn't actually fixing the problem, only pushing out
the onset of trouble a bit.  Should we not replace the fixed-size array
with a dynamic data structure?





"A bit" = 10 to 20 times - more if we set CHUNK_STRIPES higher. :-)

But maybe your're right. If we do that and stick with my two-dimensional 
scheme to keep the number of probes per chunk down, we'd need to reorg 
the array every time we increased it. That might be a bit messy, but 
might be ok. Or maybe linearly searching an array of several hundred 
slots for our pid for every log chunk that comes in would be fast enough.


cheers

andrew



--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] ECPG FETCH readahead

2012-04-02 Thread Noah Misch
On Fri, Mar 30, 2012 at 12:48:07AM +0200, Boszormenyi Zoltan wrote:
> 2012-03-29 19:03 keltez?ssel, Noah Misch ?rta:

 one of the new sections about readahead should somehow reference the hazard
 around volatile functions.
>>> Done.
>> I don't see the mention in your latest patch.  You do mention it for the
>> sqlerrd[2] compatibility stuff.
>
> sqlerrd[2] compatibility stuff? I mentioned it in section "ecpg-sqlca", this 
> is the main
> documentation section, not the compatibility one AFAIK. Anyway, I now 
> reference the volatile
> function hazard in the first paragraphs added to section "ecpg-cursors".

This patch adds two features, and those features are independent from a user
perspective.  The primary feature is cursor readahead, and the secondary
feature is "ecpg --detect-cursor-resultset-size" (the referent of my above
"sqlerrd[2] compatibility stuff" reference).  Each feature has independent
semantic implications when the application uses cursors on queries that call
volatile functions.  Under --detect-cursor-resultset-size, we will execute
functions for all rows at OPEN time and again for each row at FETCH time.
When you declare a cursor with "READAHEAD n" and do not FETCH it to the end,
up to "n" unFETCHed rows will nonetheless have their functions executed.  If
the volatile function is something like clock_timestamp(), the application
will observe the executions to have happened in clusters of "n" rather than in
step with the application's FETCH calls.

Your latest patch revision hints at the semantic implications for "ecpg
--detect-cursor-resultset-size", but it does not mention them for readahead.
Then again, perhaps it's sufficiently obvious to not warrant mention.  Without
knowing internals, I would not expect users to guess the consequence of "ecpg
--detect-cursor-resultset-size".  With readahead, it may be guessable enough.

Thanks,
nm

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Switching to Homebrew as recommended Mac install? / apology

2012-04-02 Thread Jay Levitt

David Johnston wrote:
> Just trying to bridge an apparent gap since the original e-mail seems to
> have come across as too adversarial that the underlying thoughts have
> been overlooked.  Trying to contribute in my own way with my current
> resources.

Thanks, but it's my own fault for basing a half-baked "let's rewrite 
everything" idea on a few wrong assumptions without asking some simple 
questions first. (Sorry, David.) And you guys don't know me very well yet, 
so you don't how to interpret my tone, or that I spend the *first* half of 
each day making the exact opposite arguments to all the young turks in the 
hopes we'll all meet in the middle.  I plan to hang around, so by way of 
belated introduction, and you can imagine this in the style of Beetlejuice:


Hi. I wrote AOL's OLTP-style mail server in 1994 and scaled it (with an 
eventual team of 25) from 27 to 4000 TPS on minicomputers as powerful as an 
iPhone. It had multi-site replication, zero-downtime installs (without 
blocking writes), and served 1.5 million simultaneous users. I had to write 
nearly a whole SVM-based OS in the app, because nothing at the time - from 
the message bus to the disk cache to poll() itself - could handle our load, 
and our growth outpaced the hardware manufacturers' ability to build it. I 
did this with no CS background beyond public school (I started as a 6502 
hacker), no search engine, and no access to research papers or computer 
scientists. I have no idea how.


The architecture survived five underlying infrastructure transitions 
(Stratus filesystem, Sybase, Informix, Tandem, and the move from four- to 
eight-byte IDs that PG has successfully staved off) while migrating live. It 
never lost or misrouted a message, and never had a security breach in the 
nine years I led it (or, AFAIK, since), despite the fact that we were a big 
enough target for hackers to respond to our changed defenses within hours. I 
do actually know this stuff, or did.


I spent 10 years taking a break, studying music, successfully sleeping 
through Java; now I'm back in technology, leading development in a 
Rails/web/JS startup, and luxuriating in the ability to actually store data 
in a database this time - because you guys have spent the past 20 years 
studying and improving the hard parts of abstracting performant, reliable, 
scalable data storage.


I have a tendency to see the possible endgame and insist on starting now, 
and if I get too idealistic, ambitious, and "MongoDB is webscale", please 
just drag me into a conversation about lock contention and distributed 2PC 
and I'll forget the whole thing. But I really do think PG can be the 
makes-everything-possible, does-most-things-well data store - really, data 
platform - for the next decade or two, and I want to contribute.


I'm provocative, playful and grandiose, I apologize except not really, and 
it's because in my n=1 experience, the way life works is (a) you decide to 
change the world and then (b) you do.


> You do not need permission to contribute to the community
> in the way you seek so what is it that you are really asking for?

Nothing at this point. I was thinking out loud, and at the time was 
temporarily insa^h^h^hconvinced that the homebrew formula should be the 
community standard, and thus that I'd have to bring it up to some level of 
acceptability/review.  I've contributed to the formula in the past, and will 
continue to do so based on the thoughts everyone's shared here. It doesn't 
need to be official to be useful, and as David Page said, it's not gonna be 
listed in the docs no matter what, given the one decision that homebrew 
makes (/usr/local) that I can't override.



When brew is replaced by something more popular do you
think you will continue to maintain the recipe or is it going to end
up stuck showing us how to install version 9.3 or earlier.


Like anything, I'll maintain it until it becomes useless to me or vice 
versa, and someone will pick it up or they won't.  But just to be clear, 
Homebrew's a source-based repo (so there's no cross-compiler issues), 
pulling from the upstream source repository, using only the stock compiler 
toolchain, Intel-only, on a platform where the only hardware manufacturer 
has themselves severely constrained the number of possible configurations. 
For the most part, updating the formula to "package" new versions is a 
matter of changing the following two lines:


  url 'http://ftp.postgresql.org/pub/source/v9.1.3/postgresql-9.1.3.tar.bz2'
  md5 '641e1915f7ebfdc9f138e4c55b6aec0e'

Unless the instructions for "How to build postgres from source" change, 
nothing else in the formula *needs* to.  The current formula is fairly 
simple; aside from user instructions, the code is 75 lines and mostly 
consists of default arguments to ./configure.  (Formula attached for the 
curious.)  Pull requests are freely and quickly accepted after a quick 
review; the homebrew repo is operated more in the "fail early and often" 
s

Re: [HACKERS] log chunking broken with large queries under load

2012-04-02 Thread Tom Lane
Andrew Dunstan  writes:
> On 04/01/2012 06:34 PM, Andrew Dunstan wrote:
>> Some of my PostgreSQL Experts colleagues have been complaining to me 
>> that servers under load with very large queries cause CSV log files 
>> that are corrupted,

> We could just increase CHUNK_SLOTS in syslogger.c, but I opted instead 
> to stripe the slots with a two dimensional array, so we didn't have to 
> search a larger number of slots for any given message. See the attached 
> patch.

This seems like it isn't actually fixing the problem, only pushing out
the onset of trouble a bit.  Should we not replace the fixed-size array
with a dynamic data structure?

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] log chunking broken with large queries under load

2012-04-02 Thread Andrew Dunstan



On 04/01/2012 06:34 PM, Andrew Dunstan wrote:
Some of my PostgreSQL Experts colleagues have been complaining to me 
that servers under load with very large queries cause CSV log files 
that are corrupted, because lines are apparently multiplexed. The log 
chunking protocol between the errlog routines and the syslogger is 
supposed to prevent that, so I did a little work to try to reproduce 
it in a controlled way.



Well, a little further digging jogged my memory a bit. It looks like we 
underestimated the amount of messages we might get as more than one 
chunk fairly badly.


We could just increase CHUNK_SLOTS in syslogger.c, but I opted instead 
to stripe the slots with a two dimensional array, so we didn't have to 
search a larger number of slots for any given message. See the attached 
patch.


I'm not sure how much we want to scale this up. I set CHUNK_STRIPES to 
20 to start with, and I've asked some colleagues with very heavy log 
loads with very large queries to test it out if possible. If anyone else 
has a similar load I'd appreciate similar testing.


cheers

andrew
*** a/src/backend/postmaster/syslogger.c
--- b/src/backend/postmaster/syslogger.c
***
*** 93,101  static char *last_file_name = NULL;
  static char *last_csv_file_name = NULL;
  
  /*
!  * Buffers for saving partial messages from different backends. We don't expect
!  * that there will be very many outstanding at one time, so 20 seems plenty of
!  * leeway. If this array gets full we won't lose messages, but we will lose
   * the protocol protection against them being partially written or interleaved.
   *
   * An inactive buffer has pid == 0 and undefined contents of data.
--- 93,108 
  static char *last_csv_file_name = NULL;
  
  /*
!  * Buffers for saving partial messages from different backends.
!  *
!  * Under heavy load we can get quite a few of them, so we stripe them across
!  * an array based on the mod of the pid, which seem an adequate hash function
!  * for pids. We do this rather then just keeping a one-dimensional array so
!  * we don't have to probe too many slots for any given pid.
!  * 20 stripes of 20 slots each seems likely to be adequate leeway, but if there
!  * are lots of overruns we might need to increase CHUNK_STRIPES a bit, or look
!  * at some alternative scheme.
!  * If a stripe gets full we won't lose messages, but we will lose
   * the protocol protection against them being partially written or interleaved.
   *
   * An inactive buffer has pid == 0 and undefined contents of data.
***
*** 107,113  typedef struct
  } save_buffer;
  
  #define CHUNK_SLOTS 20
! static save_buffer saved_chunks[CHUNK_SLOTS];
  
  /* These must be exported for EXEC_BACKEND case ... annoying */
  #ifndef WIN32
--- 114,121 
  } save_buffer;
  
  #define CHUNK_SLOTS 20
! #define CHUNK_STRIPES 20
! static save_buffer saved_chunks[CHUNK_STRIPES][CHUNK_SLOTS];
  
  /* These must be exported for EXEC_BACKEND case ... annoying */
  #ifndef WIN32
***
*** 725,730  process_pipe_input(char *logbuffer, int *bytes_in_logbuffer)
--- 733,739 
  	{
  		PipeProtoHeader p;
  		int			chunklen;
+ 		int stripe;
  
  		/* Do we have a valid header? */
  		memcpy(&p, cursor, sizeof(PipeProtoHeader));
***
*** 743,748  process_pipe_input(char *logbuffer, int *bytes_in_logbuffer)
--- 752,759 
  			dest = (p.is_last == 'T' || p.is_last == 'F') ?
  LOG_DESTINATION_CSVLOG : LOG_DESTINATION_STDERR;
  
+ 			stripe = p.pid % CHUNK_STRIPES;
+ 
  			if (p.is_last == 'f' || p.is_last == 'F')
  			{
  /*
***
*** 756,780  process_pipe_input(char *logbuffer, int *bytes_in_logbuffer)
  
  for (i = 0; i < CHUNK_SLOTS; i++)
  {
! 	if (saved_chunks[i].pid == p.pid)
  	{
  		existing_slot = i;
  		break;
  	}
! 	if (free_slot < 0 && saved_chunks[i].pid == 0)
  		free_slot = i;
  }
  if (existing_slot >= 0)
  {
! 	str = &(saved_chunks[existing_slot].data);
  	appendBinaryStringInfo(str,
  		   cursor + PIPE_HEADER_SIZE,
  		   p.len);
  }
  else if (free_slot >= 0)
  {
! 	saved_chunks[free_slot].pid = p.pid;
! 	str = &(saved_chunks[free_slot].data);
  	initStringInfo(str);
  	appendBinaryStringInfo(str,
  		   cursor + PIPE_HEADER_SIZE,
--- 767,791 
  
  for (i = 0; i < CHUNK_SLOTS; i++)
  {
! 	if (saved_chunks[stripe][i].pid == p.pid)
  	{
  		existing_slot = i;
  		break;
  	}
! 	if (free_slot < 0 && saved_chunks[stripe][i].pid == 0)
  		free_slot = i;
  }
  if (existing_slot >= 0)
  {
! 	str = &(saved_chunks[stripe][existing_slot].data);
  	appendBinaryStringInfo(str,
  		   cursor + PIPE_HEADER_SIZE,
  		   p.len);
  }
  else if (free_slot >= 0)
  {
! 	saved_chunks[stripe][free_slot].pid = p.pid;
! 	str = &(saved_chunks[stripe][free_slot].data);
  	i

Re: [HACKERS] Switching to Homebrew as recommended Mac install?

2012-04-02 Thread Dave Page
On Mon, Apr 2, 2012 at 8:17 AM, Jay Levitt  wrote:
>
> Sure, and if humans read docs, instead of just glancing at them, that'd be
> all you needed. In any case, I could counter myself that nobody reads the
> doc period, so it doesn't matter what version is listed; that's just the
> source of my own misunderstanding about maintenance.

Seems odd that you claim the docs are out of date in one breathe, then
in the next counter with an argument that users never read docs so
they're basically irrelevant.

> Well, for "Rubyists", I should properly substitute "younger developers", aka
> "all developers eventually".
>
> As more toolchains work without sudo (Ruby, Python, JavaScript), using sudo
> actually breaks stuff, because now you've written some files as root and
> you'll later fail to overwrite them as user. Heck, I've had that happen with
> ccache; if you do "sudo make install" without "make", you're compiling and
> caching as root, and three years later you'll recompile as user and get a
> permissions error (or a broken ccache IIRC).

I'm not aware that either Python or JavaScript require that you remove
sensible and long established security measures from your machine.
Come to think of it, neither did the MacPorts installation of Ruby
that I used to have to run a couple of utilities, nor does the version
that Apple ship with OS X.

I would suggest that it's not modern languages that require bypassing
of security measures, but a poorly designed packaging system.

> I'm not only telling you that *a* developer doesn't know; I'm telling you
> that soon, *most* won't.  (Or, if they do, they know it's a magic
> incantation that when something breaks, a blog tells them what to type to
> fix it.) I work with a smart 2005 CS graduate who, when I said "look in
> /usr/lib", would try cd'ing to /Users/Library. He wrote his first shell
> script last week.
>
> I'm not saying it's good. I'm just saying it is - and you're younger than
> me, so you're not going to get to retire before it happens either!

I run multiple teams of engineers at EnterpriseDB, and many of them
are basically fresh out of university (or were when they joined EDB).
Every single one of them knows what the path is and how to change it,
and I can say with confidence that every one of them could explain to
you what's wrong with making /usr/local/ world writeable. They may not
be able to code in a particular language, but they all have the
ability to learn and the basic skills on which to build. If the
students of the future have as little knowledge of computer systems as
you suggest, we might as well throw away our machines now and go live
in caves or huts!

And on that note, I suggest we take this off pgsql-hackers now as it's
drifting (drifted) way off topic for the list.

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Re: [GENERAL] pg_dump incredibly slow dumping a single schema from a large db

2012-04-02 Thread Mike Roest
:)  yah that makes sense no big deal.  i'll probably just push this head
buiild of pg_dump onto the production machines till it comes out.

Thanks again!

On Sat, Mar 31, 2012 at 3:44 PM, Tom Lane  wrote:

> Mike Roest  writes:
> > Any idea when 9.1.4 with this change will be out so we can pull the
> cluster
> > up.
>
> Well, we just did some releases last month, so unless somebody finds a
> really nasty security or data-loss issue, I'd think it will be a couple
> of months before the next set.
>
>regards, tom lane
>


Re: [HACKERS] Switching to Homebrew as recommended Mac install?

2012-04-02 Thread Jay Levitt

Dave Page wrote:

On Mon, Apr 2, 2012 at 12:29 AM, Jay Levitt  wrote:
Just as an FYI, a large percentage of the PostgreSQL developers are
Mac users, including myself. They're also the company standard at
EnterpriseDB - so we're not entirely unfamiliar with software
development on them.


Good to know; now I realize that even on Macs there are two worlds. I think 
it's important to understand the new world, because although very little 
infrastructure spans two generations, I believe that PostgreSQL is uniquely 
positioned to do exactly that, just as POSIX has. It's a performant ACID 
relational data store for the old guard, but it's also a web-scale 
cloud-friendly polyglot-extensible preinstalled 
XML/JSON/hstore/tree/graph/schemaless store for the new, and like Java 
HotSpot, it gets smarter and faster every day. It is an operating system for 
your data, it's free, and Oracle hasn't bought it (except yesterday).


This goes back to the "marketing challenge, not technical challenge" threads.


- We have few Mac experts hanging out in #postgresql.

Not sure how this is relevant to the proposal.

The impetus for the idea was that there seems to be a steady stream of
novice PG users on Mac who come into #postgresql with installation problems,

If you see someone report a bug with the installers, please have them
report it on the EnterpriseDB forums:
http://forums.enterprisedb.com/forums/show/9.page


It's not a bug with the EDB installer, except insofar as the bug is "the EDB 
installer is not prominent and widely recommended enough and so they used 
homebrew or macports". The EDB installer is what they used once upon a time; 
today they are following a blog post that walked them through homebrew 
(probably as one line of a longer process involving memcached, sphinx, 
nginx, node.js, and seven other things).



The EDB installer will always be
a one-off experience; most of the other servers you install will be through
a package manager, and homebrew's popularity (despite its youth) is
impressive.


I would disagree with that. Most users I know do not use things like
homebrew (particularly those coming from Windows who have no
familiarity with such package managers at all).


Yep; two worlds.

>> [docs reference 8.4]

That hasn't been updated because the installation steps haven't
changed and I'd rather spend time writing software than updating
screenshots.


Fair. I wonder if there's an easy way to automate the generation and 
insertion of those screen shots. You could always write an 
AppleScript/Automator/Keyboard Maestro script, but I feel like I've seen a 
better way specifically for tech docs; I'll keep an eye out.



A couple of points of note:

- The introduction says: "This document is based on the 8.4.x
one-click installers but applies equally to later versions."

- The doc also explains where to find the uninstaller.


Sure, and if humans read docs, instead of just glancing at them, that'd be 
all you needed. In any case, I could counter myself that nobody reads the 
doc period, so it doesn't matter what version is listed; that's just the 
source of my own misunderstanding about maintenance.



- There are eight ways to install Postgres on a Mac

That isn't any more of a reason to discount the EDB installer than any
other.

Nope, just an argument that the recommended installer should handle that
nicely.


It does. It'll detect that the port is in use and suggest a different
one. I don't know of any other of those installation methods that'll
do that.


That's terrific, and you're right - it's the only one.

 2. The current formula installs Postgres as the desktop user, not as the
 _postgres role account.
>>> That's not very helpful on shared machines - and whilst it may be fine
>>> for developers etc, it's not the recommended way to setup PostgreSQL
>>> for any kind of production use.
>> Oh, of course. Homebrew does not target the three remaining people who run
>> production XServes.  It's purely for Mac developer workstations.  At
>> startups. Which are MacBooks! :)
> "Production" doesn't necessarily mean "server". All those thousands of
> Poker Tracker users that run with PostgreSQL on Windows on their home
> machines are production users for example.

Excellent point, and one I hadn't thought of.


1. Rubyists in general are sick of sudo'ing on their laptops, because It
Doesn't Matter (as I'll fail to argue later).  Homebrew puts itself into a
directory that is user-writable so it does not require sudo for basic
installation.


Nice. You just turned me off ever wanting anything related to Ruby on
my Mac either!


Well, for "Rubyists", I should properly substitute "younger developers", aka 
"all developers eventually".


As more toolchains work without sudo (Ruby, Python, JavaScript), using sudo 
actually breaks stuff, because now you've written some files as root and 
you'll later fail to overwrite them as user. Heck, I've had that happen with 
ccache; if you do "sudo make install" with

Re: [HACKERS] new group commit behavior not helping?

2012-04-02 Thread Peter Geoghegan
On 1 April 2012 06:41, Robert Haas  wrote:
> There seem to be too relevant differences between your test and mine:
> (1) your test is just a single insert per transaction, whereas mine is
> pgbench's usual update, select, update, update, insert and (2) it
> seems that, to really see the benefit of this patch, you need to pound
> the server with a very large number of clients.  On this test, 250
> clients was the sweet spot.

*refers to original early January benchmark*

While the graph that I produced was about the same shape as yours, the
underlying hardware was quite different, and indeed with my benchmark
group commit's benefits are more apparent earlier - at 32 clients,
throughput has more-than doubled compared to pre group commit
Postgres, which has already just about plateaued. I did include hdparm
information for the disk that my benchmark was performed on at the
time. While write-caching was not disabled, I would expect that the
commit speed of my laptop - which has a fairly unremarkable 7200RPM
disk - is slower than the 10K RPM SAS disks that you used. A formal
benchmark of respective raw commit speeds may shed more light on this.

Why did I even bother with such a sympathetic benchmark, when a
benchmark on a large server could have been performed instead? Well,
the reality is that many of our users have a commit speed that is
comparable to my laptop. In particular, the increasing prevalence of
"cloud" type deployments, make group commit a timely feature. If you
wanted to demonstrate the wonders of group commit, I'd take that
particular tone. I'm sure that if you re-ran this benchmark with a
battery-backed cache, you would observe a much smaller though still
very apparent benefit, but if you wanted to make the feature sound
appealing to traditional enterprise users that are using a BBU, a good
line would be "this is what will save your bacon that day that your
procedures fail and your BBU battery dies".

-- 
Peter Geoghegan       http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training and Services

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Switching to Homebrew as recommended Mac install?

2012-04-02 Thread Andrew Dunstan



On 04/02/2012 05:23 AM, Dave Page wrote:

There are hundreds of thousands of pieces of malware for Windows that
relied on the ability to write to "system" directories like this to do
their misdeeds. Anywhere they can write (or modify existing) software
that may get executed at boot time or by an unsuspecting users (or
potentially, root). Microsoft spent millions, probably tens or
hundreds of millions enhancing the security of Windows precisely
because of this type of security issue.

If homebrew intentionally creates a hole like that, then for as long
as I'm one of the PostgreSQL webmasters it will *never* be listed on
our download pages.




+10

I'm one of the people who doesn't have a horse in this race. (Recently I 
was teaching my daughter about some development stuff and so I got her 
to install Fedora under VirtualBox on her Mac :-) ). But as soon as I 
saw this stuff about the intentional security hole it raised a great big 
red flag with me too.


cheers

andrew



--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] measuring lwlock-related latency spikes

2012-04-02 Thread Simon Riggs
On Mon, Apr 2, 2012 at 11:49 AM, Greg Stark  wrote:
> On Mon, Apr 2, 2012 at 8:15 AM, Simon Riggs  wrote:
>> Not true, please refer to code at line 544, as I already indicated.
>>
>> My understanding of the instrumentation is that the lock acquired at
>> line 526 will show as the blocker until we reach line 555, so anything
>> in between could be responsible for the wait.
>
> Hm, but then wouldn't the lock acquisition at line 544 be showing up as well?

Some time ago on this thread, I wrote:
"Anyway, just to note that it might not be I/O and we need to find out."

Do you consider this proof that it can only be I/O? Or do we still
need to find out?

-- 
 Simon Riggs   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] measuring lwlock-related latency spikes

2012-04-02 Thread Greg Stark
On Mon, Apr 2, 2012 at 8:15 AM, Simon Riggs  wrote:
> Not true, please refer to code at line 544, as I already indicated.
>
> My understanding of the instrumentation is that the lock acquired at
> line 526 will show as the blocker until we reach line 555, so anything
> in between could be responsible for the wait.

Hm, but then wouldn't the lock acquisition at line 544 be showing up as well?


-- 
greg

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Switching to Homebrew as recommended Mac install?

2012-04-02 Thread Dave Page
On Mon, Apr 2, 2012 at 12:29 AM, Jay Levitt  wrote:
>
> At this point I agree with you, but I'm still going to go into detail,
> because I think there are two markets for Postgres, and the database
> community has been so focused around enterprise for so long that you're
> missing opportunities with web startups. I'd love to help bridge the gap,
> having jumped straight from big-iron PL/I to ooh-Ruby-is-shiny. And web
> startups develop on Mac laptops. They just do. So if it helps you to imagine
> me as a 20something "I'm a Mac" hipster, working on some hot Facebook/mobile
> app with funding from Spark Capital, do that. Lord knows it helps me.

Just as an FYI, a large percentage of the PostgreSQL developers are
Mac users, including myself. They're also the company standard at
EnterpriseDB - so we're not entirely unfamiliar with software
development on them.

>>> - We have few Mac experts hanging out in #postgresql.
>> Not sure how this is relevant to the proposal.
>
> The impetus for the idea was that there seems to be a steady stream of
> novice PG users on Mac who come into #postgresql with installation problems,
> which is bad enough as an out-of-box experience - but worse is that there
> are rarely folks around who can help. (Of course, I'm extrapolating; every
> time *I'm* in IRC and see this, there's someone who can help. But you know
> what I mean.)

If you see someone report a bug with the installers, please have them
report it on the EnterpriseDB forums:
http://forums.enterprisedb.com/forums/show/9.page

> I didn't realize that you were actively maintaining the EDB installer (see
> below for the 8.4 doc explanation); obviously, if you can improve that, it's
> the best solution and we should, if anything, recommend it MORE vigorously.
> Still, there's a growing community of developers who expect "brew install"
> to work, and I do want to fix it for them.  The EDB installer will always be
> a one-off experience; most of the other servers you install will be through
> a package manager, and homebrew's popularity (despite its youth) is
> impressive.

I would disagree with that. Most users I know do not use things like
homebrew (particularly those coming from Windows who have no
familiarity with such package managers at all).

>  Both of my n=2 data points had run across PG a while back,
> installed it with the one-click to try it out, forgotten about it, done
> "brew install postgresql" today, and naturally ran into problems.

As I said, that will happen with any distro. The installers are smart
enough to detect it and avoid trying to reuse the same port. They
won't ever try to touch an existing installation though (except of
their own, which if detected will cause a switch to upgrade mode).

>
>>> - The EDB docs are written against 8.4.
>> Only if you install 8.4. If you install 8.3 you get the 8.3 docs, 9.0
>> the 9.0 docs and so on.
>
> No, I meant on the web:
>
> http://www.enterprisedb.com/resources-community/pginst-guide
>
> That's what made me assume that the installer wasn't maintained (except as
> to repackaging new PG versions, obviously). It's obviously not hard to
> replace "8.3" with "9.1" when you read it, but it still leaves an impression
> akin to "This web site works best with IE7 and above." Allow me to now
> replace most of this thread with "hey, you might wanna update that page."

That hasn't been updated because the installation steps haven't
changed and I'd rather spend time writing software than updating
screenshots. A couple of points of note:

- The introduction says: "This document is based on the 8.4.x
one-click installers but applies equally to later versions."

- The doc also explains where to find the uninstaller.

>>> - There are eight ways to install Postgres on a Mac
>
>> That isn't any more of a reason to discount the EDB installer than any
>> other.
>
> Nope, just an argument that the recommended installer should handle that
> nicely.

It does. It'll detect that the port is in use and suggest a different
one. I don't know of any other of those installation methods that'll
do that.

>
> 1. Rubyists in general are sick of sudo'ing on their laptops, because It
> Doesn't Matter (as I'll fail to argue later).  Homebrew puts itself into a
> directory that is user-writable so it does not require sudo for basic
> installation.

Nice. You just turned me off ever wanting anything related to Ruby on
my Mac either!

> 2. Because shell's $PATH is hard to change programmatically due to
> shell-config individualism (MacPorts was notorious for failing at this), and
> yet many Mac programmers know nothing of shells at all (so they don't know
> how to edit it manually), Homebrew puts itself into a directory that is
> already in $PATH by default, but which is conveniently nonexistent by
> default.

Are you honestly trying to tell me that a developer (using any
language, other than maybe vbscript in Excel) doesn't know about
$PATH?

> Thus, Homebrew chowns /usr/local to (desktop user):a

Re: [HACKERS] Event scheduling

2012-04-02 Thread Simon Riggs
On Mon, Apr 2, 2012 at 8:36 AM, Simon Riggs  wrote:
> On Mon, Apr 2, 2012 at 1:17 AM, Joe Van Dyk  wrote:
>
>> Anyone else want event scheduling / cron / temporal triggers in
>> postgresql? http://dev.mysql.com/doc/refman/5.1/en/events-overview.html
>> shows how it works in mysql.
>>
>> Can we throw money at someone to get this in postgres? Is there work
>> already being done on this?
>>
>> Being able to regularly execute a postgres function every so often
>> would be really nice. It would simplify lots of deployments.
>
> I'm working on this. Glad to hear someone else wants this also.

When I say "working on this", I mean the ability to have a
user-defined daemon in Postgres, which is sufficient infrastructure to
provide the basis for a user space scheduler.

I'm not going anywhere near writing anything that looks like a scheduler.

-- 
 Simon Riggs   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] HTTP Frontend? (and a brief thought on materialized views)

2012-04-02 Thread Daniel Farina
On Sat, Mar 31, 2012 at 6:37 AM, Dobes Vandermeer  wrote:
> On Sat, Mar 31, 2012 at 1:44 AM, Daniel Farina  wrote:
>>
>> On Fri, Mar 30, 2012 at 10:21 AM, Daniel Farina  wrote:
>> > Any enhancement here that can't be used with libpq via, say, drop-in
>> > .so seems unworkable to me, and that's why any solution that is
>> > basically proxying to the database is basically a non-starter outside
>> > the very earliest prototyping stages.  The tuple scanning and protocol
>> > semantics can and even should remain the same, especially at first.
>>
>> I should add: proxying could work well if libpq had all the right
>> hooks. The server could remain ignorant.  Regardless, upstream changes
>> result.
>
>
> Just to be clear, what you are saying that writing a process that accepts
> requests by HTTP and translates them into requests using the existing
> protocol to send to the server would have unacceptable performance?  Or is
> there something else about it that is a non-starter?

I don't think it's so much an unworkable performance regression as
getting people to seriously try experimenting with useful extensions
to FEBE, and so that real applications (and their maintainers) can
justify a little time to test and experience (for good or for ill)
those enhancements in a production or at least staging setting and
giving them a fair shake.

As a reminder, this is based on a conjecture that there is a large
dimension of useful experimentation that involves simply interlacing
information between FEBE messages, and then intercepting and
processing those on both server and client, and that these message can
be composed in many situations (i.e. multiple extensions can work
well).

For me, HTTP2 figures into all of this because it may be one way to
paint some aspects of the protocol-extension bikeshed with the same
color more people might use, and as long as that color is basically
functional we can seek to understand if a standard bikeshed-color
allows us to take advantage of anticipated large, low-cost reserves of
paint.  Consider this analogy stretched.

-- 
fdr

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] measuring lwlock-related latency spikes

2012-04-02 Thread Simon Riggs
On Mon, Apr 2, 2012 at 12:00 AM, Greg Stark  wrote:
> On Sun, Apr 1, 2012 at 4:05 AM, Robert Haas  wrote:
>> My guess based on previous testing is
>> that what's happening here is (1) we examine a tuple on an old page
>> and decide we must look up its XID, (2) the relevant CLOG page isn't
>> in cache so we decide to read it, but (3) the page we decide to evict
>> happens to be dirty, so we have to write it first.
>
> Reading the code one possibility is that in the time we write the
> oldest slru page another process has come along and redirtied it. So
> we pick a new oldest slru page and write that. By the time we've
> written it another process could have redirtied it again. On a loaded
> system where the writes are taking 100ms or more it's conceivable --
> barely -- that could happen over and over again hundreds of times.

That's a valid concern but I don't think the instrumentation would
show that as a single long wait because the locks would be released
and be retaken each time around the loop - I guess that's for Robert
to explain how it would show up.

If it doesn't show it, then the actual max wait time could be even higher. ;-(

-- 
 Simon Riggs   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Event scheduling

2012-04-02 Thread Simon Riggs
On Mon, Apr 2, 2012 at 1:17 AM, Joe Van Dyk  wrote:

> Anyone else want event scheduling / cron / temporal triggers in
> postgresql? http://dev.mysql.com/doc/refman/5.1/en/events-overview.html
> shows how it works in mysql.
>
> Can we throw money at someone to get this in postgres? Is there work
> already being done on this?
>
> Being able to regularly execute a postgres function every so often
> would be really nice. It would simplify lots of deployments.

I'm working on this. Glad to hear someone else wants this also.

-- 
 Simon Riggs   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] measuring lwlock-related latency spikes

2012-04-02 Thread Simon Riggs
On Sun, Apr 1, 2012 at 11:12 PM, Greg Stark  wrote:
> On Sun, Apr 1, 2012 at 10:27 PM, Simon Riggs  wrote:
>> So lock starvation on the control lock would cause a long wait after
>> each I/O, making it look like an I/O problem.
>
> Except that both of the locks involved in his smoking gun occur
> *after* the control lock has already been acquired. The one that's
> actually being blocked for a long time is in fact acquiring a shared
> lock which the queue jumping couldn't be hurting.

Not true, please refer to code at line 544, as I already indicated.

My understanding of the instrumentation is that the lock acquired at
line 526 will show as the blocker until we reach line 555, so anything
in between could be responsible for the wait.

(As long as there are multiple possibilities, I will remain convinced
that the cause could be any of them.)

-- 
 Simon Riggs   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] measuring lwlock-related latency spikes

2012-04-02 Thread Albe Laurenz
Robert Haas wrote:
> I suppose one interesting question is to figure out if there's a way I
> can optimize the disk configuration in this machine, or the Linux I/O
> scheduler, or something, so as to reduce the amount of time it spends
> waiting for the disk.

I'd be curious to know if using the deadline scheduler will improve
things.  I have experienced pretty bad performance with cfq under
load, where sequential table scans were starved to the point where
they took hours instead of less than a minute (on an idle system).
But I believe that also depends a lot on the storage system used.

Yours,
Laurenz Albe

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers