Re: [BUGS] BUG #5946: Long exclusive lock taken by vacuum (not full)
Likely "too large" is more an issue related to available resources than of absolute figure. On a penta byte of free storage I would not mind allocating some teras with extending a (large) table. If I'm left with some MB only, I'd be concerned for sure. I still prefer an approach that will "just work", without much fiddling of all kind of knobs. I'd see the following points: - There is a minimum size of allocation below which it is unreasonable /inefficient to do allocations - doing allocations on sizes based on current table size honor the assumption that a large table will grow further (and thus somehow improve this track of grows) - large growth is "frightening" - largely (my assumption) due to unwanted behavior towards end of space So what seems to help out is twofold: - support readjusting of allocation size to smaller units in case an intended allocation could not be satisfied while sill allowing the minimum required space to be claimed - allow for allocated but unused space to be reclaimed (It is perfectly OK to have all of my "unused" disk space allocated to a large table that just happens to be not using it, if this can still be used later for some smaller table as soon as this is in need for some space.) Allocation should also take into account the amount of space left. This likely is something to be determined per tablespace. >From that allocation might work like: a) try to get x% of the currently allocated amount for the object b) but not more than y% of the free space on the related tablespace c) and never less than a minimum necessary (for limiting overhead costs) Rainer Am 25.03.2011 22:34, schrieb Tom Lane: > Greg Stark writes: >> On Fri, Mar 25, 2011 at 8:48 PM, Tom Lane wrote: >>> Interesting, but I don't understand/believe your argument as to why this >>> is a bad idea or fixed-size extents are better. It sounds to me just >>> like the typical Oracle DBA compulsion to have a knob to twiddle. A >>> self-adjusting enlargement behavior seems smarter all round. >> So is it ok for inserting one row to cause my table to grow by 90GB? > If the table is already several TB, why not? The whole point here is > that it's very unlikely that you're not going to be inserting more rows > pretty soon. > >> Or should there be some maximum size increment at which it stops >> growing? What should that maximum be? What if I'm on a big raid system >> where that size doesn't even add a block to every stripe element? >> Say you start with 64k (8 pg blocks). That means your growth >> increments will be 64k, 70k, 77kl, 85k, 94k, 103k, 113k, 125k, 137k, >> ... > I have no problem with trying to be smart about allocating in powers of > 2, not allocating more than X at a time, etc etc. I'm just questioning > the idea that the user should be bothered with this, or is likely to be > smarter than the system about such things. Particularly if you believe > that this problem actually justifies attention to such details. I think > you've already demonstrated that a simplistic fixed-size allocation > parameter probably *isn't* good enough. > > regards, tom lane > -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5946: Long exclusive lock taken by vacuum (not full)
Excerpts from Alvaro Herrera's message of lun mar 28 07:26:49 -0300 2011: > Likely "too large" is more an issue related to available resources than > of absolute figure. > > On a penta byte of free storage I would not mind allocating some teras > with extending a (large) table. > If I'm left with some MB only, I'd be concerned for sure. ... Does anybody have an idea just W-T-F happened here? I did NOT send the above email (as evidenced by it being signed by "Rainer"). I notice it even has a "@commandprompt.com" message-id. Should I start signing my email? -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5946: Long exclusive lock taken by vacuum (not full)
Rainer, any idea? Please see http://archives.postgresql.org/message-id/4d906269.6060...@commandprompt.com Excerpts from Alvaro Herrera's message of lun mar 28 11:03:16 -0300 2011: > Excerpts from Alvaro Herrera's message of lun mar 28 07:26:49 -0300 2011: > > Likely "too large" is more an issue related to available resources than > > of absolute figure. > > > > On a penta byte of free storage I would not mind allocating some teras > > with extending a (large) table. > > If I'm left with some MB only, I'd be concerned for sure. > > ... > > Does anybody have an idea just W-T-F happened here? I did NOT send the > above email (as evidenced by it being signed by "Rainer"). I notice it > even has a "@commandprompt.com" message-id. Should I start signing my > email? > -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5946: Long exclusive lock taken by vacuum (not full)
I digged into my sent folder, and the outgoing message already dat the false headers. So probably my MUA (thunderbird) got confused on something and caused that blunder. Sorry for that Rainer Am 28.03.2011 16:05, schrieb Alvaro Herrera: > Rainer, any idea? Please see > http://archives.postgresql.org/message-id/4d906269.6060...@commandprompt.com > > > Excerpts from Alvaro Herrera's message of lun mar 28 11:03:16 -0300 2011: >> Excerpts from Alvaro Herrera's message of lun mar 28 07:26:49 -0300 2011: >>> Likely "too large" is more an issue related to available resources than >>> of absolute figure. >>> >>> On a penta byte of free storage I would not mind allocating some teras >>> with extending a (large) table. >>> If I'm left with some MB only, I'd be concerned for sure. >> ... >> >> Does anybody have an idea just W-T-F happened here? I did NOT send the >> above email (as evidenced by it being signed by "Rainer"). I notice it >> even has a "@commandprompt.com" message-id. Should I start signing my >> email? >> -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5946: Long exclusive lock taken by vacuum (not full)
Fascinating. No real idea. I just hit "reply to list" on a message from tom (probably a reply to a message from you?). So from earlier experience with such operations, I would not have expected to not show up as sender or from of the message. So yes it was me posting and I have no idea on what actually caused a false from to end up there. Rainer Am 28.03.2011 16:05, schrieb Alvaro Herrera: > Rainer, any idea? Please see > http://archives.postgresql.org/message-id/4d906269.6060...@commandprompt.com > > > Excerpts from Alvaro Herrera's message of lun mar 28 11:03:16 -0300 2011: >> Excerpts from Alvaro Herrera's message of lun mar 28 07:26:49 -0300 2011: >>> Likely "too large" is more an issue related to available resources than >>> of absolute figure. >>> >>> On a penta byte of free storage I would not mind allocating some teras >>> with extending a (large) table. >>> If I'm left with some MB only, I'd be concerned for sure. >> ... >> >> Does anybody have an idea just W-T-F happened here? I did NOT send the >> above email (as evidenced by it being signed by "Rainer"). I notice it >> even has a "@commandprompt.com" message-id. Should I start signing my >> email? >> -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5950: backend terminating after altering table
Tom Lane writes: > "alex" writes: > > 1. create table t ( > > ); > > 2. alter table t add childs t; > > 3. alter table t add id serial not null primary key; > > server closed the connection unexpectedly > > Hmm. This seems to be fixed in HEAD: Not fully. There are also two cases with Segmentation fault (from sql.ru forum): 1. create table t (); alter table t add childs t; create table selfchield_new (like t); 2. create table t (); alter table t add childs t; create table selfchield_new as select * from t; I have segmentation fault with current master 7c7fd882. -- Sergey Burladyan -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5950: backend terminating after altering table
Sergey Burladyan writes: > Tom Lane writes: >> "alex" writes: >>> 1. create table t ( >>> ); >>> 2. alter table t add childs t; >>> 3. alter table t add id serial not null primary key; >>> server closed the connection unexpectedly >> Hmm. This seems to be fixed in HEAD: > Not fully. There are also two cases with Segmentation fault > (from sql.ru forum): Yeah, I had already come to the conclusion that it was just luck that HEAD avoids the specific problem. See http://archives.postgresql.org/pgsql-hackers/2011-03/msg01673.php regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5946: Long exclusive lock taken by vacuum (not full)
On Mon, Mar 28, 2011 at 12:29 AM, Tom Lane wrote: > Robert Haas writes: >> I think we've had a number of pieces of evidence that suggest that >> extending 8kB at a time is too costly, but I agree with Greg that the >> idea of extending an arbitrarily large table by 10% at a time is >> pretty frightening - that could involve allocating a gigantic amount >> of space on a big table. I would be inclined to do something like >> extend by 10% of table or 1MB, whichever is smaller. > > Sure, something like that sounds sane, though the precise numbers > need some validation. Yeah. >> ... And a 1MB extension is probably also small enough >> that we can do it in the foreground without too much of a hiccup. > > Less than convinced about this. Well, I guess we can always try it and see. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5946: Long exclusive lock taken by vacuum (not full)
On Mon, Mar 28, 2011 at 2:20 PM, Robert Haas wrote: > On Mon, Mar 28, 2011 at 12:29 AM, Tom Lane wrote: >> Robert Haas writes: >>> I think we've had a number of pieces of evidence that suggest that >>> extending 8kB at a time is too costly, but I agree with Greg that the >>> idea of extending an arbitrarily large table by 10% at a time is >>> pretty frightening - that could involve allocating a gigantic amount >>> of space on a big table. I would be inclined to do something like >>> extend by 10% of table or 1MB, whichever is smaller. >> >> Sure, something like that sounds sane, though the precise numbers >> need some validation. > > Yeah. > >>> ... And a 1MB extension is probably also small enough >>> that we can do it in the foreground without too much of a hiccup. >> >> Less than convinced about this. > > Well, I guess we can always try it and see. Another approach might be to do something "adaptive"... The iterative process might be wrapped with something like the following: - Grab timestamp - Grab exclusive lock - Process [Some number of pages] - Check time. - If [# of ms] have passed then check to see if anyone else has a lock O/S on the table. - Commit & give up the lock for a bit if they do - Go back and process more pages if they don't This offers 3 parameters that are amenable to management: - How many pages to process at a time - How long to process between checking for lock requests - How long to give up processing Robert's suggestion would be consistent with these being set to (128,?,?). The adverse impact would be kept pretty small by something like (16, 10ms, 30ms). And if the table *isn't* being avidly used, it can iterate incessantly, not giving up the lock, because nobody else cares. In the "busy with lots of other users of that table" case, it'll take quite a long time to get the table's extra extensions truncated. Indeed, it's pretty easy for other things to get *heavily* in the way. -- http://linuxfinances.info/info/linuxdistributions.html -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5950: backend terminating after altering table
"alex" writes: > Such steps: > 1. create table t ( > ); > 2. alter table t add childs t; > 3. alter table t add id serial not null primary key; > server closed the connection unexpectedly Thanks for the report. Fixed as per today's discussion (ie, disallow creating a self-referencing rowtype). regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5946: Long exclusive lock taken by vacuum (not full)
Christopher Browne writes: > - Grab timestamp > - Grab exclusive lock > - Process [Some number of pages] > - Check time. > - If [# of ms] have passed then check to see if anyone else has a lock > O/S on the table. > - Commit & give up the lock for a bit if they do > - Go back and process more pages if they don't Actually, we could simplify that even further. Keep the code exactly as-is, but every small-number-of-pages, check to see if someone is waiting on a conflicting lock, and if so, fall out of the page checking loop. Truncate away however many pages we know at that time are safe, and end the vacuum normally. We'd have to rejigger the stuff in the lock manager that tries to boot autovacuum off the lock forcibly, but with a bit of luck that would get less crocky not more so. This wouldn't really have any parameters that require tuning, I think, and the max delay till the lock is released is not too much more than the time needed for ftruncate(). The really good thing about it is that vacuum's work is never wasted. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5946: Long exclusive lock taken by vacuum (not full)
On Mon, Mar 28, 2011 at 4:01 PM, Tom Lane wrote: > Christopher Browne writes: >> - Grab timestamp >> - Grab exclusive lock >> - Process [Some number of pages] >> - Check time. >> - If [# of ms] have passed then check to see if anyone else has a lock >> O/S on the table. >> - Commit & give up the lock for a bit if they do >> - Go back and process more pages if they don't > > Actually, we could simplify that even further. Keep the code exactly > as-is, but every small-number-of-pages, check to see if someone is > waiting on a conflicting lock, and if so, fall out of the page checking > loop. Truncate away however many pages we know at that time are safe, > and end the vacuum normally. > > We'd have to rejigger the stuff in the lock manager that tries to boot > autovacuum off the lock forcibly, but with a bit of luck that would get > less crocky not more so. > > This wouldn't really have any parameters that require tuning, I think, > and the max delay till the lock is released is not too much more than > the time needed for ftruncate(). The really good thing about it is that > vacuum's work is never wasted. > > regards, tom lane That mostly sounds excellent. One caveat is that this has the risk, for a busy table, of having it take nearly forever to get through the truncation of the empty space at the end. If the VACUUM falls out, under this logic, after truncating only a few pages, then there's the considerable cost of rummaging through the table, over and over, truncating only a few pages each time. Supposing we set it up to truncate 32 pages (assuming that to be the "safe" level), and there are 10 empty 1GB files at the end of the table, then it's potentially going to take tens of thousands of VACUUM requests to empty that whole chunk of space out. That seems close enough to "forever" for my purposes :-), assuming I'm understanding that correctly. I hope I'm wrong, and that there's potential here to get quite a bit more pages than that dropped out. -- http://linuxfinances.info/info/linuxdistributions.html -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #5955: One-click installer does not escape password
The following bug has been logged online: Bug reference: 5955 Logged by: Rob Grant Email address: r...@occipital.com PostgreSQL version: 9.0 Operating system: OS X Description:One-click installer does not escape password Details: I provided the one-click installer a password that included a double quote. This caused the following errors: Error running /Library/PostgreSQL/9.0/installer/server/initcluster.sh "postgres" "postgres" "" "/Library/PostgreSQL/9.0" "/Library/PostgreSQL/9.0/data" 5432 DEFAULT : /bin/sh: -c: line 0: unexpected EOF while looking for matching `"' Error running /Library/PostgreSQL/9.0/installer/server/loadmodules.sh "postgres" "postgres" "" "/Library/PostgreSQL/9.0" 5432 : /bin/sh: -c: line 0: unexpected EOF while looking for matching `"' I reinstalled and provided a password that did not include a double quote and was able to complete the installation successfully. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5946: Long exclusive lock taken by vacuum (not full)
On 3/28/2011 4:01 PM, Tom Lane wrote: Christopher Browne writes: - Grab timestamp - Grab exclusive lock - Process [Some number of pages] - Check time. - If [# of ms] have passed then check to see if anyone else has a lock O/S on the table. - Commit& give up the lock for a bit if they do - Go back and process more pages if they don't Actually, we could simplify that even further. Keep the code exactly as-is, but every small-number-of-pages, check to see if someone is waiting on a conflicting lock, and if so, fall out of the page checking loop. Truncate away however many pages we know at that time are safe, and end the vacuum normally. We'd have to rejigger the stuff in the lock manager that tries to boot autovacuum off the lock forcibly, but with a bit of luck that would get less crocky not more so. I somehow fail to see how this complete reversal of who does what and affecting code in entirely different parts of the system will qualify for patching back releases. Jan -- Anyone who trades liberty for security deserves neither liberty nor security. -- Benjamin Franklin -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5946: Long exclusive lock taken by vacuum (not full)
Jan Wieck writes: > I somehow fail to see how this complete reversal of who does what and > affecting code in entirely different parts of the system will qualify > for patching back releases. I don't think any of the proposals make sense for back-patching. We should be considering what's the sanest way to fix this in 9.2. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5946: Long exclusive lock taken by vacuum (not full)
On 3/28/2011 8:07 PM, Tom Lane wrote: Jan Wieck writes: I somehow fail to see how this complete reversal of who does what and affecting code in entirely different parts of the system will qualify for patching back releases. I don't think any of the proposals make sense for back-patching. We should be considering what's the sanest way to fix this in 9.2. So our answer to users, who currently run 8.4 and experience 10+ minute blackouts caused by autovac, would be "upgrade to 9.2 when it's out". Is that actually what you meant? Jan -- Anyone who trades liberty for security deserves neither liberty nor security. -- Benjamin Franklin -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5927: PostgreSQL8.2
On 03/12/2011 03:31 PM, Vishal wrote: Hello Robert, I checked my logs and found the following info - [1] Information (8196), test_db_connection: exception: The Report Server is unable to connect to the database. Database error: could not connect to server: Connection refused (0x274D/10061) Is the server running on host "???" and accepting TCP/IP connections on port 5432? The database logs are in C:\Program Files\Postgresql\8.2\data\pg_log assuming you installed Pg to the default location. Check there. -- Craig Ringer -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
