Re: [HACKERS] increasing collapse_limits?
Hello a slow query is just simple like SELECT FROM a LEFT JOIN b ON .. LEFT JOIN c ON .. LEFT JOIN d ON .. LEFT JOIN e ON .. WHERE e.x = number a slow query plan explain analyze select * from v_vypis_parcel_puvodni where par_id = 1396907206 --- "Nested Loop Left Join (cost=4043.95..12777.12 rows=1 width=415) (actual time=46813.256..47130.773 rows=1 loops=1)" " Join Filter: (budovy.id = parcely.bud_id)" " -> Nested Loop Left Join (cost=0.00..27.42 rows=1 width=262) (actual time=0.311..0.634 rows=1 loops=1)" "Join Filter: (katastr_uzemi.kod = parcely.katuze_kod)" "-> Nested Loop Left Join (cost=0.00..20.55 rows=1 width=212) (actual time=0.282..0.301 rows=1 loops=1)" " -> Nested Loop Left Join (cost=0.00..12.26 rows=1 width=208) (actual time=0.162..0.175 rows=1 loops=1)" "Join Filter: (parcely.zdpaze_kod = zdroje_parcel_ze.kod)" "-> Nested Loop Left Join (cost=0.00..11.19 rows=1 width=145) (actual time=0.148..0.159 rows=1 loops=1)" " Join Filter: (d_pozemku.kod = parcely.drupoz_kod)" " -> Nested Loop Left Join (cost=0.00..9.94 rows=1 width=140) (actual time=0.099..0.104 rows=1 loops=1)" "Join Filter: (zp_vyuziti_poz.kod = parcely.zpvypa_kod)" "-> Index Scan using par_pk on parcely (cost=0.00..8.31 rows=1 width=84) (actual time=0.037..0.040 rows=1 loops=1)" " Index Cond: (id = 1396907206::numeric)" "-> Seq Scan on zp_vyuziti_poz (cost=0.00..1.28 rows=28 width=70) (actual time=0.005..0.023 rows=28 loops=1)" " -> Seq Scan on d_pozemku (cost=0.00..1.11 rows=11 width=19) (actual time=0.023..0.033 rows=11 loops=1)" "-> Seq Scan on zdroje_parcel_ze (cost=0.00..1.03 rows=3 width=70) (actual time=0.004..0.006 rows=3 loops=1)" " -> Index Scan using tel_pk on telesa (cost=0.00..8.28 rows=1 width=15) (actual time=0.112..0.116 rows=1 loops=1)" "Index Cond: (parcely.tel_id = public.telesa.id)" "-> Seq Scan on katastr_uzemi (cost=0.00..4.72 rows=172 width=54) (actual time=0.019..0.160 rows=172 loops=1)" " -> Hash Left Join (cost=4043.95..11787.52 rows=76968 width=164) (actual time=19827.669..47069.869 rows=77117 loops=1)" "Hash Cond: (budovy.typbud_kod = t_budov.kod)" "-> Hash Left Join (cost=4042.82..10728.08 rows=76968 width=141) (actual time=19827.625..46938.954 rows=77117 loops=1)" " Hash Cond: (budovy.caobce_kod = casti_obci.kod)" " -> Hash Left Join (cost=4028.14..9827.78 rows=76968 width=46) (actual time=19826.622..46824.288 rows=77117 loops=1)" "Hash Cond: (budovy.id = casti_budov.bud_id)" "-> Hash Left Join (cost=4015.38..8850.54 rows=76968 width=33) (actual time=19825.627..46710.476 rows=76968 loops=1)" " Hash Cond: (budovy.tel_id = public.telesa.id)" " -> Seq Scan on budovy (cost=0.00..1903.68 rows=76968 width=40) (actual time=0.031..86.709 rows=76968 loops=1)" " -> Hash (cost=2214.17..2214.17 rows=103617 width=15) (actual time=19691.650..19691.650 rows=103617 loops=1)" "-> Seq Scan on telesa (cost=0.00..2214.17 rows=103617 width=15) (actual time=0.015..96.548 rows=103617 loops=1)" "-> Hash (cost=9.79..9.79 rows=238 width=28) (actual time=0.937..0.937 rows=238 loops=1)" " -> Hash Left Join (cost=1.14..9.79 rows=238 width=28) (actual time=0.104..0.699 rows=238 loops=1)" "Hash Cond: (casti_budov.typbud_kod = t_bud_ii.kod)" "-> Seq Scan on casti_budov (cost=0.00..5.38 rows=238 width=25) (actual time=0.030..0.201 rows=238 loops=1)" "-> Hash (cost=1.06..1.06 rows=6 width=17) (actual time=0.032..0.032 rows=6 loops=1)" " -> Seq Scan on t_budov t_bud_ii (cost=0.00..1.06 rows=6 width=17) (actual time=0.008..0.014 rows=6 loops=1)" " -> Hash (cost=12.20..12.20 rows=198 width=103) (actual time=0.940..0.940 rows=198 loops=1)" "-> Hash Left Join (cost=4.50..12.20 rows=198 width=103) (actual time=0.255..0.698 rows=198 loops=1)" " Hash Cond: (casti_obci.obce_kod = obce.kod)" " -> Seq Scan on casti_obci (cost=0.00..4.98 rows=198 width=58) (actual time=0.004..0.126 rows=198 loops=1)" " -> Hash (cost=3.11..3.11 rows=111 width=53) (actual time=0.206..0.206 rows=111 loops=1)" "-> Seq Scan on obce (cost=0.00..3.11 rows=111 width=53) (actual time=0.010..0.105 r
Re: [HACKERS] a bit strange btree index tuples
Tomas Vondra writes:
> testdb=# select bt_page_items('test_index', 3);
> bt_page_items
> --
> (1,"(1,1)",8,f,f,"")
> (2,"(2,1)",12,f,f,"ca 01 00 00")
> (3,"(4,1)",12,f,f,"93 03 00 00")
> (3 rows)
> I don't understand the first row and I've been unable to find out if
> it's something special for the btree indexes or what.
You should read src/backend/access/nbtree/README, which would explain to
you why it is that leftmost tuples on interior btree pages don't contain
key values.
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] increasing collapse_limits?
Mark Kirkwood writes: > On 01/05/11 11:53, Greg Stark wrote: >> On Sat, Apr 30, 2011 at 9:21 PM, Tom Lane wrote: >>> - it would require a query in which >>> every relation is linked to every other relation by a join clause. >>> But that *can* happen (remember that clauses generated by transitive >>> equality do count). >> It sounds like you're describing precisely a "star schema" join which >> isn't an uncommon design pattern at all. A normal star schema doesn't really do this because the join conditions are generally on different columns of the central fact table. However... > Nice example here: > http://archives.postgresql.org/pgsql-bugs/2011-04/msg00100.php > Strictly only a 'star-like' query as the foreign key references go the > opposite way from a true star. However it illustrates the planner memory > growth well (1.1G on 32-bit 1.7G on 64-bit systems). > A point I didn't mention is that the memory use is quite dependent on > the choice of "word" values for the "AND keyword = 'word'" clause - the > text example had 6 all the same. Setting them all different (even after > adjusting the data so the there *was* a number of matching rows to find) > resulted in significantly less memory consumed (I can dig up some > examples if it might be interesting). Yeah. What you have there is that n.nodeid is equated to columns of six other tables, so those seven tables form a group in which every table can be joined directly to every other (because of transitive deduction of equality clauses). So it's kinda bad already. But then, if the kwN tables have "keyword" all equated to the same constant (and thus to each other), that's another group of six tables that can all be joined directly to each other. So that results in a large increase in the number of join sequences that will get explored. 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 bit strange btree index tuples
Hi,
when working on the integrity checking tool, I've noticed there are a
bit trange tuples in the btree indexes. E.g. if I do this:
create table test_table (id int);
insert into test_table select i from generate_series(1,1000) s(i);
create index test_index on test_table(id);
then pageinspect returns this:
testdb=# select bt_page_items('test_index', 3);
bt_page_items
--
(1,"(1,1)",8,f,f,"")
(2,"(2,1)",12,f,f,"ca 01 00 00")
(3,"(4,1)",12,f,f,"93 03 00 00")
(3 rows)
I don't understand the first row and I've been unable to find out if
it's something special for the btree indexes or what.
According to ItemId in the PageHeader the tuple has these features
lp_flags=1 (LP_NORMAL)
lp_off=8168
lp_len=8 (i.e. exactly sizeof(IndexTuple)
and according to the IndexTuple, t_info=8 (so the length is 8 and it
does not have any NULL or varwidth attributes).
Yes, the lengths in page header and tuple match (8 in both cases) but
where are the attributes?
I've noticed there is yet another index tuple for ctid=(1,1), right on
the first page of the index
select bt_page_items('test_index', 1);
...
(255,"(0,254)",12,f,f,"fe 00 00 00")
(256,"(0,255)",12,f,f,"ff 00 00 00")
(257,"(1,1)",12,f,f,"00 01 00 00")
(258,"(1,2)",12,f,f,"01 01 00 00")
...
but I still wonder what is the index tuple for.
regards
Tomas
--
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 bit more precise MaxOffsetNumber
Hi, I've been digging in the sources, and I've noticed the MaxOffsetNumber is defined (in storage/off.h) like this (BLCKSZ / sizeof(ItemIdData)) I guess it might be made a bit more precise by subtracting the header like this (BLCKSZ - offsetof(PageHeaderData, pd_linp) / sizeof(ItemIdData)) although the difference is negligible (2048 vs 2042 for 8kB pages). Tomas -- 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] Proposed patch: Smooth replication during VACUUM FULL
On Sat, Apr 30, 2011 at 5:48 PM, Tom Lane wrote: > Jaime Casanova writes: >> On Sat, Apr 30, 2011 at 1:19 PM, Gabriele Bartolini >>> I have noticed that during VACUUM FULL on reasonably big tables, replication >>> lag climbs. In order to smooth down the replication lag, I propose the >>> attached patch which enables vacuum delay for VACUUM FULL. > >> AFAICS, the problem is that those operations involve the rebuild of >> tables, so we can't simply stop in the middle and wait because we will >> need to hold a strong lock more time... also the patch seems to be >> only doing something for CLUSTER and not for VACUUM FULL. >> or am i missing something? > [...] > The argument about holding locks longer doesn't seem relevant to me: > enabling delays during VACUUM FULL would've had that effect in the old > implementation, too, but nobody ever complained about that, you mean, no complaints except the usual: "don't use VACUUM FULL"? -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte y capacitación de PostgreSQL -- 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] increasing collapse_limits?
On 01/05/11 11:53, Greg Stark wrote: On Sat, Apr 30, 2011 at 9:21 PM, Tom Lane wrote: - it would require a query in which every relation is linked to every other relation by a join clause. But that *can* happen (remember that clauses generated by transitive equality do count). It sounds like you're describing precisely a "star schema" join which isn't an uncommon design pattern at all. Nice example here: http://archives.postgresql.org/pgsql-bugs/2011-04/msg00100.php Strictly only a 'star-like' query as the foreign key references go the opposite way from a true star. However it illustrates the planner memory growth well (1.1G on 32-bit 1.7G on 64-bit systems). A point I didn't mention is that the memory use is quite dependent on the choice of "word" values for the "AND keyword = 'word'" clause - the text example had 6 all the same. Setting them all different (even after adjusting the data so the there *was* a number of matching rows to find) resulted in significantly less memory consumed (I can dig up some examples if it might be interesting). Cheers Mark
Re: [HACKERS] increasing collapse_limits?
On Sat, Apr 30, 2011 at 9:21 PM, Tom Lane wrote: > - it would require a query in which > every relation is linked to every other relation by a join clause. > But that *can* happen (remember that clauses generated by transitive > equality do count). It sounds like you're describing precisely a "star schema" join which isn't an uncommon design pattern at all. -- 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] Proposed patch: Smooth replication during VACUUM FULL
Jaime Casanova writes: > On Sat, Apr 30, 2011 at 1:19 PM, Gabriele Bartolini >> I have noticed that during VACUUM FULL on reasonably big tables, replication >> lag climbs. In order to smooth down the replication lag, I propose the >> attached patch which enables vacuum delay for VACUUM FULL. > AFAICS, the problem is that those operations involve the rebuild of > tables, so we can't simply stop in the middle and wait because we will > need to hold a strong lock more time... also the patch seems to be > only doing something for CLUSTER and not for VACUUM FULL. > or am i missing something? No, actually it would have no effect on CLUSTER because VacuumCostActive wouldn't be set. I think this is basically fixing an oversight in the patch that changed VACUUM FULL into a variant of CLUSTER. We used to use vacuum_delay_point() in the main loops in old-style VACUUM FULL, but forgot to consider doing so in the CLUSTER-ish implementation. The argument about holding locks longer doesn't seem relevant to me: enabling delays during VACUUM FULL would've had that effect in the old implementation, too, but nobody ever complained about that, and besides the feature isn't enabled by default. A bigger objection to this patch is that it seems quite incomplete. I'm not sure there's much point in adding delays to the first loop of copy_heap_data() without also providing for delays inside the sorting code and the eventual index rebuilds; which will make the patch significantly more complicated and invasive. Another question is whether this is the right place to be looking at all. If Gabriele's setup can't keep up with replication when a VAC FULL is running, then it can't keep up when under load, period. This seems like a pretty band-aid-ish response to that sort of problem. 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] Proposed patch: Smooth replication during VACUUM FULL
--On 30. April 2011 20:19:36 +0200 Gabriele Bartolini wrote: I have noticed that during VACUUM FULL on reasonably big tables, replication lag climbs. In order to smooth down the replication lag, I propose the attached patch which enables vacuum delay for VACUUM FULL. Hmm, but this will move one problem into another. You need to hold exclusive locks longer than necessary and given that we discourage the regular use of VACUUM FULL i cannot see a real benefit of it... -- Thanks Bernd -- 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] branching for 9.2devel
"Kevin Grittner" writes: > Joshua Berkus wrote: >> I just searched backwards on this thread and I can't find it. > I think he's talking about the bottom of this post: > http://archives.postgresql.org/message-id/BANLkTimnjZNemdpqgK=8Mj=pzq33pz0...@mail.gmail.com ... which was: CF #1: June 1-30 CF #2: August 1-31 CF #3: October 1-31 CF #4 (one week shortened CF): December 1-7 CF #5: January 1-31 I think the main thing we have to think about before choosing is whether we believe that we can shorten the CFs at all. Josh's proposal had 3-week CFs after the first one, which makes it a lot easier to have a fest in November or December, but only if you really can end it on time. In addition to the fun of working around the holiday season, perhaps we should also consider how much work we're likely to get out of people in the summer. Is it going to be useful to schedule a fest in either July or August? Will one month be better than the other? 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] branching for 9.2devel
Joshua Berkus wrote: > I just searched backwards on this thread and I can't find it. I think he's talking about the bottom of this post: http://archives.postgresql.org/message-id/BANLkTimnjZNemdpqgK=8Mj=pzq33pz0...@mail.gmail.com -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] branching for 9.2devel
> > If CF1 is June1, though, when will CF4 be? Having a CF start Dec. 1 > > is probably a bad idea. > > Well, I made a suggestion on this topic in my previous email on the > subject... I just searched backwards on this thread and I can't find it. There's been a lot of posts. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com San Francisco -- 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] increasing collapse_limits?
Robert Haas writes: > I seem to remember that I was the last one to suggest raising these limits > and someone demonstrated rather convincingly that for certain classes of > queries that would cause really big problems. You proposed removing the collapse limits altogether, but that crashed and burned pretty quickly --- see the archives from 2009, eg here http://archives.postgresql.org/pgsql-hackers/2009-07/msg00358.php http://archives.postgresql.org/pgsql-hackers/2009-07/msg00947.php http://archives.postgresql.org/pgsql-hackers/2009-11/msg00306.php I'm not opposed to raising the limits somewhat, but I'd like to see a more thorough case made for what to raise them to. In principle there are k! join orders for a k-way join problem, which means that raising the limit from 8 to 12 could result in a 1-fold increase in planner runtime and memory consumption. In practice, because of the heuristic that we avoid considering clauseless joins if possible, most queries don't see growth rates that bad --- it would require a query in which every relation is linked to every other relation by a join clause. But that *can* happen (remember that clauses generated by transitive equality do count). So there needs to be some attention paid to both average and worst case behaviors. Raising them to 10 would only impose a worst case 100-fold growth, which is not as scary as 1-fold, so maybe we should consider that as an intermediate step. Don't know how much difference that would make in the real world though. It also occurs to me to wonder if we could adjust the limit on-the-fly based on noticing whether or not the query is prone to worst-case behavior, ie how dense is the join connection graph. Right now it'd be difficult to do that with any reliability, though, because we don't look for equivalence classes until after we've fixed our attention on a particular join subproblem. 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] Changing the continuation-line prompt in psql?
Joshua Berkus writes: > I'll bet someone a fancy drink at a conference that this thread goes to at > least 100 posts. Of course, if we all are to argue about this bet… :) -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- 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] PostgreSQL Core Team
Thom Brown writes: > Excellent! Magnus is a very valuable contributor to the PostgreSQL > community and I think the community can only benefit from this addition to > the core team. +1 Congrats, Magnus! -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- 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] increasing collapse_limits?
On Apr 30, 2011, at 7:38 PM, Tom Lane wrote: > Pavel Stehule writes: >> Actually we had to solve a issue with slow SELECT. The problem was in >> low value of JOIN_COLLAPSE_LIMITS. Can we increase a default of this >> value. I checked some complex query, and planner needed about 200ms >> for JOIN_COLLAPSE_LIMIT = 16. So some around 12 can be well. > > I'd like to see a rather larger survey of cases before changing that. > Also, amount of memory consumed is at least as large a concern here > as runtime. I seem to remember that I was the last one to suggest raising these limits and someone demonstrated rather convincingly that for certain classes of queries that would cause really big problems. ...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] CLUSTER vs toast vacuuming: there's still a problem
On Apr 30, 2011, at 8:22 PM, Tom Lane wrote: > I wonder though if it wouldn't be smarter > to insist that autovacuum acquire some lock on the main table > when processing a toast table. Boy, it sure seems better to avoid the above if we can. So +1 for the other way around - make CLUSTER lock the TOAST table if it cares about a VACUUM happening there. ...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] branching for 9.2devel
On Apr 30, 2011, at 9:23 PM, Joshua Berkus wrote: > Robert, > >> Tom and I were talking about starting maybe June 1, rather than July >> 1. You seem opposed but I'm not sure why. > > Because I think -- strictly based on history and the complexity of the new > features -- we'll still be fixing major issues with the beta in June, which > was what Tom said as well the last time he posted about it on this thread. > > If CF1 is June1, though, when will CF4 be? Having a CF start Dec. 1 is > probably a bad idea. Well, I made a suggestion on this topic in my previous email on the subject... ...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] Changing the continuation-line prompt in psql?
I'll bet someone a fancy drink at a conference that this thread goes to at least 100 posts. Let the bikeshedding begin! -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com San Francisco -- 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] branching for 9.2devel
Robert, > Tom and I were talking about starting maybe June 1, rather than July > 1. You seem opposed but I'm not sure why. Because I think -- strictly based on history and the complexity of the new features -- we'll still be fixing major issues with the beta in June, which was what Tom said as well the last time he posted about it on this thread. If CF1 is June1, though, when will CF4 be? Having a CF start Dec. 1 is probably a bad idea. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com San Francisco -- 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] Proposed patch: Smooth replication during VACUUM FULL
On Sat, Apr 30, 2011 at 1:19 PM, Gabriele Bartolini wrote: > Hi guys, > > I have noticed that during VACUUM FULL on reasonably big tables, replication > lag climbs. In order to smooth down the replication lag, I propose the > attached patch which enables vacuum delay for VACUUM FULL. > AFAICS, the problem is that those operations involve the rebuild of tables, so we can't simply stop in the middle and wait because we will need to hold a strong lock more time... also the patch seems to be only doing something for CLUSTER and not for VACUUM FULL. or am i missing something? -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte y capacitación de PostgreSQL -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Large Objects versus transactional behavior
This is related to the "SIREAD lock versus ACCESS EXCLUSIVE lock" thread, but seemed different enough to merit spinning off a new thread. Our shop hasn't used large objects so far because of the lack of security (until 9.1), so I never noticed the rather unusual transactional semantics of large objects. From the devel documentation: http://developer.postgresql.org/pgdocs/postgres/lo-interfaces.html#LO-OPEN | [...] with INV_READ you cannot write on the descriptor, and the | data read from it will reflect the contents of the large object at | the time of the transaction snapshot that was active when lo_open | was executed, regardless of later writes by this or other | transactions. Reading from a descriptor opened with INV_WRITE | returns data that reflects all writes of other committed | transactions as well as writes of the current transaction. This is | similar to the behavior of REPEATABLE READ versus READ COMMITTED | transaction modes for ordinary SQL SELECT commands. Since Serializable Snapshot Isolation can only serialize behavior which is working within the semantics of snapshot isolation, it doesn't seem like SSI has any chance of serializing access to the contents of a large object while the current behavior stands. Modifications to the *references* to large objects within the bodies of normal tables is properly tracked by SSI, but no predicate locks are taken on the large object contents themselves, nor would modifications to the contents be able to generate a rw-conflict between transactions. In other words, I don't think there is any action item here for SSI in terms of C code for 9.1, but we may want to mention the unusual transaction-related behavior of large objects within the Concurrency Control chapter of the docs. Comments? -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] CLUSTER vs toast vacuuming: there's still a problem
I thought some more about the bug #5998 business, and I believe there's still a risk unaddressed by yesterday's patch. Suppose that we begin a CLUSTER operation on some table, and then while it's running (perhaps in the sort stage) autovacuum decides to vacuum the table's TOAST table. The autovacuum could then have a later OldestXmin than the CLUSTER does. This means that it could see as DEAD, and remove, toast tuples belonging to main-table tuples that the CLUSTER thinks are RECENTLY_DEAD. Ooops. This is only a risk because autovacuum processes a toast table independently from its main table, and (so far as I can see) doesn't take any lock on the main table while doing so. We could fix the immediate problem by having CLUSTER take out a vacuum-conflicting lock on the toast table before it determines the OldestXmin it will use. I wonder though if it wouldn't be smarter to insist that autovacuum acquire some lock on the main table when processing a toast table. Without that, we could see this type of problem again. (But on the third hand, locking toast table and then main table is no good because of deadlock risks. And a manual VACUUM on the toast table would be hazardous anyway.) Thoughts? 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] Proposed patch: Smooth replication during VACUUM FULL
Hi guys,
I have noticed that during VACUUM FULL on reasonably big tables,
replication lag climbs. In order to smooth down the replication lag, I
propose the attached patch which enables vacuum delay for VACUUM FULL.
Please find attached the patch and below more information on this
specific issue.
Cheers,
Gabriele
== Scenario
I have setup a simple SyncRep scenario with one master and one standby
on the same server.
On the master I have setup vacuum_cost_delay = 10 milliseconds.
I have created a scale 50 pgbench database, which produces a 640MB
pgbench_accounts table (about 82k pages). I have then launched a 60
seconds pgbench activity with 4 concurrent clients with the goal to make
some changes to the pgbench table (approximately 1800 changes on my laptop).
== Problem observed
Replication lag climbs during VACUUM FULL.
== Proposed change
Enable vacuum delay for VACUUM FULL (and CLUSTER).
== Test
I have then launched a VACUUM FULL operation on the pgbench_accounts
table and measured the lag in bytes every 5 seconds, by calculating the
difference between the current location and the sent location.
Here is a table with lag values. The first column (sec) is the sampling
time (every 5 seconds for the sake of simplicity here), the second
column (mlag) is the master lag on the current HEAD instance, the third
column (mlagpatch) is the lag measured on the patched Postgres instance.
sec | mlag | mlagpatch
-+---+---
0 | 1896424 |0
5 | 15654912 | 4055040
10 | 8019968 | 13893632
15 | 16850944 | 4177920
20 | 10969088 | 21102592
25 | 11468800 | 2277376
30 | 7995392 | 13893632
35 | 14811136 | 20660224
40 | 6127616 |0
45 | 6914048 | 5136384
50 | 5996544 | 13500416
55 | 14155776 | 9043968
60 | 23298048 | 11722752
65 | 15400960 | 18202624
70 | 17858560 | 28049408
75 | 8560640 | 34865152
80 | 19628032 | 33161216
85 | 25526272 | 39976960
90 | 23183360 | 23683072
95 | 23265280 | 303104
100 | 24346624 | 3710976
105 | 24813568 |0
110 | 32587776 | 7651328
115 | 42827776 | 12369920
120 | 50167808 | 14991360
125 | 60260352 | 3850240
130 | 62750720 | 5160960
135 | 68255744 | 9355264
140 | 60653568 | 14336000
145 | 68780032 | 16564224
150 | 74342400 | 5398528
155 | 84639744 | 11321344
160 | 92741632 | 16302080
165 | 70123520 | 20234240
170 | 13606912 | 23248896
175 | 20586496 | 29278208
180 | 16482304 | 1900544
185 |0 |0
As you can see, replication lag on HEAD's PostgreSQL reaches 92MB (160
seconds) before starting to decrease (when the operation terminates).
The test result is consistent with the expected behaviour of cost-based
vacuum delay.
--
Gabriele Bartolini - 2ndQuadrant Italia
PostgreSQL Training, Services and Support
gabriele.bartol...@2ndquadrant.it | www.2ndQuadrant.it
diff --git a/src/backend/commands/cluster.c b/src/backend/commands/cluster.c
index 191ef54..f10ae3c 100644
--- a/src/backend/commands/cluster.c
+++ b/src/backend/commands/cluster.c
@@ -877,7 +877,8 @@ copy_heap_data(Oid OIDNewHeap, Oid OIDOldHeap, Oid
OIDOldIndex,
Buffer buf;
boolisdead;
- CHECK_FOR_INTERRUPTS();
+ /* Launches vacuum delay */
+ vacuum_delay_point();
if (indexScan != NULL)
{
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Update docs to say you need fsync to make sync rep work fast.
Bruce Momjian writes: > Simon Riggs wrote: >> Update docs to say you need fsync to make sync rep work fast. > Should we also issue a warning message in the server logs for the use of > fsync=off on the standby? That patch was entirely wrong and has been reverted. 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] increasing collapse_limits?
Pavel Stehule writes: > Actually we had to solve a issue with slow SELECT. The problem was in > low value of JOIN_COLLAPSE_LIMITS. Can we increase a default of this > value. I checked some complex query, and planner needed about 200ms > for JOIN_COLLAPSE_LIMIT = 16. So some around 12 can be well. I'd like to see a rather larger survey of cases before changing that. Also, amount of memory consumed is at least as large a concern here as runtime. 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] Changing the continuation-line prompt in psql?
Peter Eisentraut writes: > How about just making the continuation prompts empty then? Personally I'd be very annoyed to lose the %R marker (the "are you in a string literal?" flag). So I think that that's not a good default. Of course, anyone who *is* that concerned about copy&paste cases can set PROMPT2 to empty today. 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] Re: [COMMITTERS] pgsql: Update docs to say you need fsync to make sync rep work fast.
Simon Riggs wrote: > Update docs to say you need fsync to make sync rep work fast. > > Branch > -- > master > > Details > --- > http://git.postgresql.org/pg/commitdiff/6e8e7cc580665ddd43c8ca2acc6d60f345570a57 This patch added this documentation text: +Replies are only sent when WAL is written to disk, so setting +fsync to off on the standby will significantly +reduce performance of synchronous replication and should be avoided. Should we also issue a warning message in the server logs for the use of fsync=off on the standby? -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] Feature proposal: distinguish each PostgreSQL instance in the event log
Added to TODO list: Allow multiple Postgres clusters running on the same machine to distinguish themselves in the event log http://archives.postgresql.org/pgsql-hackers/2011-03/msg01297.php --- Andrew Dunstan wrote: > > > On 03/22/2011 11:35 AM, MauMau wrote: > > I'm sorry that I've mistakenly sent an empty mail. This is the > > intended mail. > > > > "Andrew Dunstan" wrote in message > > news:4d889879.3080...@dunslane.net... > >> > >> On 03/22/2011 08:22 AM, MauMau wrote: > >>> I would appreciate your opinions and advice. I'll try making the patch > >>> while I'm waiting for response. I would be very much pleased if I > >>> could contribute to PostgreSQL and my proposal could be included in > >>> 9.1. > >>> > >>> > >> > >> It's a good idea, but 9.1 has been closed for new features for some > >> time. This would have to wait for 9.2 I believe. > >> > >> cheers > >> > >> andrew > >> > > > > OK. I'll try to make a patch for 9.2, considering Tom's advice and > > opinion. By that time, I will learn more about PostgreSQL design and > > source code. > > > > I seem to have misunderstood the commit fest. I've re-read the > > development info, and my corrected understanding related to the > > development cycle is as follows: > > > > According to the following two pages, now is the commit fest 5. The > > current commit fest will end on April 15. I would be grateful if you > > could tell me where I can find out that 9.1 is closed for new features. > > > > PostgreSQL 9.1 Development Plan > > http://wiki.postgresql.org/wiki/PostgreSQL_9.1_Development_Plan > > > > CommitFest 2011-Next (Open) > > https://commitfest.postgresql.org/action/commitfest_view?id=10 > > > The last commitfest for this development cycle opened on January 15th, > as shown in the first URL above, so that was the last date for > submitting items for 9.1 Any items in the next commitfest are for 9.2, > and that is the place to submit your item when you have a patch. There > is no currently running commitfest. > > 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 -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] increasing collapse_limits?
Hello Actually we had to solve a issue with slow SELECT. The problem was in low value of JOIN_COLLAPSE_LIMITS. Can we increase a default of this value. I checked some complex query, and planner needed about 200ms for JOIN_COLLAPSE_LIMIT = 16. So some around 12 can be well. Regards Pavel Stehule -- 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] Changing the continuation-line prompt in psql?
On fre, 2011-04-29 at 19:06 +0100, Greg Stark wrote: > I would second this precise interest. It really annoys me more often > than anything else that when I try to copy/paste an sql query I need > to copy each line one by one. It would be different from MySql but I > think it would be even clearer to the user: > > postgres=> select 1, > /*line 2:*/2, > /*line 3:*/3; > ?column? | ?column? | ?column? > --+--+-- > 1 |2 |3 > (1 row) How about just making the continuation prompts empty then? -- 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] Predicate locking
> Vlad Arkhipov wrote: > 29.04.2011 21:18, Kevin Grittner wrote: >> Vlad Arkhipov wrote: >>> But even if it would work it would not help me anyways. Because >>> my constraint is much more complex and depends on other tables, I >>> cannot express it in terms of exclusion constraints. >> >> Are you aware of the changes to the SERIALIZABLE transaction >> isolation level in the upcoming 9.1 release? >> >> http://wiki.postgresql.org/wiki/Serializable >> http://wiki.postgresql.org/wiki/SSI >> >> If you can wait for that, it might be just what you're looking >> for. > I would not like to make the whole transaction serializable because > of performance and concurrency reasons. I'm curious -- what do you expect the performance and concurrency impact to be? You do realize that unlike SELECT FOR UPDATE, SERIALIZABLE in PostgreSQL 9.1 will not cause any blocking beyond what is there in READ COMMITTED, right? This is not like SERIALIZABLE in any other database. It is the first production implementation of an innovative technique first published in 2008. The paper in which it was introduced won a best paper award from ACM SIGMOD. An ACM committee independently confirmed benchmarks showing that performance was much better than blocking-based SERIALIZABLE techniques, and very close to snapshot isolation for many workloads. Now, it might turn out that there's some reason it's not a good fit for you, but don't assume that based on anything you know about any *other* database's SERIALIZABLE isolation level; this is completely different. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Copy/paste from psql - was: Changing the continuation-line prompt in psql?
On Fri, Apr 29, 2011 at 8:11 PM, Tom Lane wrote: > Greg Stark writes: >> On Fri, Apr 29, 2011 at 5:45 PM, Christopher Browne >> wrote: >>> The "bike shedding" that I'd rather have would involve enclosing >>> prompts with /* comments */ so that cut'n'paste could be expected to >>> generate output that could run, without further editing, in another >>> psql session. Mind you, whenever I have configured such, I have been >>> unhappy at how wide that makes the prompt and at the loss of screen >>> space. > >> I would second this precise interest. It really annoys me more often >> than anything else that when I try to copy/paste an sql query I need >> to copy each line one by one. It would be different from MySql but I >> think it would be even clearer to the user: > >> postgres=> select 1, >> /*line 2:*/ 2, >> /*line 3:*/ 3; > > This looks promising until you stop to think about either string > literals or /* comment blocks being continued across lines ... > The copy paste problem also frustrates me, maybe modifying the prompt isn't an effective answer though. Extending the history command (\s) sounds more promising \s- for a reverse ordered history \s[n] for the last n or n-from-last-th (\s1 different from \p in that it shows the last completed query not the one in progress) and most importantly showing full history through a less-style interface like large result sets rather than in the flow of psql Does that sound like a workable answer? Regards, Bell. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
