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


Re: [HACKERS] measuring lwlock-related latency spikes

2012-04-02 Thread Simon Riggs
On Sun, Apr 1, 2012 at 11:12 PM, Greg Stark st...@mit.edu wrote:
 On Sun, Apr 1, 2012 at 10:27 PM, Simon Riggs si...@2ndquadrant.com 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] Event scheduling

2012-04-02 Thread Simon Riggs
On Mon, Apr 2, 2012 at 1:17 AM, Joe Van Dyk j...@tanga.com 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 Mon, Apr 2, 2012 at 12:00 AM, Greg Stark st...@mit.edu wrote:
 On Sun, Apr 1, 2012 at 4:05 AM, Robert Haas robertmh...@gmail.com 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] 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 dob...@gmail.com wrote:
 On Sat, Mar 31, 2012 at 1:44 AM, Daniel Farina dan...@heroku.com wrote:

 On Fri, Mar 30, 2012 at 10:21 AM, Daniel Farina dan...@heroku.com 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] Event scheduling

2012-04-02 Thread Simon Riggs
On Mon, Apr 2, 2012 at 8:36 AM, Simon Riggs si...@2ndquadrant.com wrote:
 On Mon, Apr 2, 2012 at 1:17 AM, Joe Van Dyk j...@tanga.com 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] Switching to Homebrew as recommended Mac install?

2012-04-02 Thread Dave Page
On Mon, Apr 2, 2012 at 12:29 AM, Jay Levitt jay.lev...@gmail.com 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):admin.


 In any case, the fact that Homebrew does that to /usr/local 

Re: [HACKERS] measuring lwlock-related latency spikes

2012-04-02 Thread Greg Stark
On Mon, Apr 2, 2012 at 8:15 AM, Simon Riggs si...@2ndquadrant.com 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] measuring lwlock-related latency spikes

2012-04-02 Thread Simon Riggs
On Mon, Apr 2, 2012 at 11:49 AM, Greg Stark st...@mit.edu wrote:
 On Mon, Apr 2, 2012 at 8:15 AM, Simon Riggs si...@2ndquadrant.com 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] 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] new group commit behavior not helping?

2012-04-02 Thread Peter Geoghegan
On 1 April 2012 06:41, Robert Haas robertmh...@gmail.com 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 Jay Levitt

Dave Page wrote:

On Mon, Apr 2, 2012 at 12:29 AM, Jay Levittjay.lev...@gmail.com  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 without make, you're 

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 t...@sss.pgh.pa.us wrote:

 Mike Roest mike.ro...@replicon.com 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 Dave Page
On Mon, Apr 2, 2012 at 8:17 AM, Jay Levitt jay.lev...@gmail.com 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] 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);
  	

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

2012-04-02 Thread Tom Lane
Andrew Dunstan and...@dunslane.net 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] 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 
spirit than in 

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] 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 Dunstanand...@dunslane.net  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] measuring lwlock-related latency spikes

2012-04-02 Thread Robert Haas
On Mon, Apr 2, 2012 at 7:01 AM, Simon Riggs si...@2ndquadrant.com 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 

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

2012-04-02 Thread Tom Lane
Andrew Dunstan and...@dunslane.net 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] 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 Dunstanand...@dunslane.net  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] measuring lwlock-related latency spikes

2012-04-02 Thread Kevin Grittner
Robert Haas robertmh...@gmail.com 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 Tom Lane
Andrew Dunstan and...@dunslane.net 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


[HACKERS]

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

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 jay.lev...@gmail.com 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


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] 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 Tom Lane
David E. Wheeler da...@justatheory.com 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: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] measuring lwlock-related latency spikes

2012-04-02 Thread Tom Lane
Robert Haas robertmh...@gmail.com 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] measuring lwlock-related latency spikes

2012-04-02 Thread Simon Riggs
On Mon, Apr 2, 2012 at 8:04 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 Robert Haas robertmh...@gmail.com 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


[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 Tom Lane
Simon Riggs si...@2ndquadrant.com 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


Re: [HACKERS] libxml related crash on git head

2012-04-02 Thread Tom Lane
Greg Sabino Mullane g...@turnstep.com 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 Greg Stark
On Mon, Apr 2, 2012 at 8:16 PM, Simon Riggs si...@2ndquadrant.com 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 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 Jeff Janes
On Mon, Apr 2, 2012 at 12:04 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 Robert Haas robertmh...@gmail.com 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] measuring lwlock-related latency spikes

2012-04-02 Thread Robert Haas
On Apr 2, 2012, at 3:04 PM, Tom Lane t...@sss.pgh.pa.us 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 Robert Haas
On Apr 2, 2012, at 3:16 PM, Simon Riggs si...@2ndquadrant.com 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] new group commit behavior not helping?

2012-04-02 Thread Robert Haas
On Mon, Apr 2, 2012 at 8:14 AM, Peter Geoghegan pe...@2ndquadrant.com 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] 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
heikki.linnakan...@enterprisedb.com 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] measuring lwlock-related latency spikes

2012-04-02 Thread Robert Haas
On Mon, Apr 2, 2012 at 12:58 PM, Kevin Grittner
kevin.gritt...@wicourts.gov 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