Re: [HACKERS] measuring lwlock-related latency spikes
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
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
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
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)
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
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?
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
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
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?
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?
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?
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
:) 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?
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
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
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
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
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
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
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
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
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
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
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]
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?
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
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
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
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
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
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
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
-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
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
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
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
-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
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
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
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?
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
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
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
