Re: [HACKERS] ARC patent
On Wed, 2005-01-19 at 16:25 +1100, Neil Conway wrote: On Tue, 2005-01-18 at 23:26 -0500, Tom Lane wrote: Not yet --- I suggested it but didn't get any yeas or nays. I don't feel this is solely core's decision anyway ... what do the assembled hackers think? I'm not sure it's a great idea. It's not, but may still be required. We should defer any changes for a month, just to see if its feasible to do that. I think the proper fix for the ARC issue is an 8.0.x release with a new replacement policy. To avoid introducing instability into 8.0, we should obviously test the new buffer replacement policy *very* carefully. Agreed. I prefer a plan that, if required, back ports NewStrategy to 8.0.x than one that hobbles 8.1, just in case. However, I think the ARC replacement should *not* be a fundamental change in behavior: the algorithm should still attempt to balance recency and frequency, to adjust dynamically to changes in workload, to avoid sequential flooding, and to allow constant-time page replacement. Agreed: Those are the requirements. It must also scale better as well. All of which have sufficient prior art that they could never be seen to in-themselves form the basis of a patent. If such a patch were developed, I don't think it would be a herculean task to include it in an 8.0.x release after a lot of careful testing and code review. Agreed. -- Best Regards, Simon Riggs ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] ARC patent
On Mon, 2005-01-17 at 18:43 -0500, Tom Lane wrote: I have already suggested to core that we should insist on 8.1 not requiring an initdb, so as to ensure that people will migrate up to it easily from 8.0. So is it firm policy that changes that require a catversion update cannot be made during the 8.1 cycle? Not yet --- I suggested it but didn't get any yeas or nays. I don't feel this is solely core's decision anyway ... what do the assembled hackers think? An idea around this would be to plan never to release 8.1. Instead, direct HEAD towards 8.2 with a normal dev cycle (or rather, let's aim for a short one, but in reality short may not be all that short..). Then the eventual ARC replacment (assuming there is one) gets backpatched to the 8.1 branch which is basically only contains all patches from 8.0.x plus the ARC stuff. It's a bit more to fiddle around with, but it lets people continue working on features that requires initdb. Just a thought... //Magnus ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] ARC patent
There's a very recent paper at http://carmen.cs.uiuc.edu/~zchen9/paper/TPDS-final.ps on an alternative to ARC which claims superior performance ... From a quick glance, this doesn't look applicable. The authors are discussing buffer replacement strategies for a multi-level cache hierarchy (e.g. they would call the DBMS buffer cache L1, and the Yes, it might not matter however. Another algorithm that was written by university folk (thus probably not patent prone) that looks promising is: http://www.cs.wm.edu/hpcs/WWW/HTML/publications/papers/TR-02-6.pdf http://parapet.ee.princeton.edu/~sigm2002/papers/p31-jiang.pdf (same, but better typeset) It even seems to slightly beat ARC according to the MQ paper. Andreas ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] ARC patent
Tom Lane wrote: What this really boils down to is whether we think we have order-of-a-year before the patent is issued. I'm nervous about assuming that. I'd like to have a plan that will produce a tested, credible patch in less than six months. Why not having a beta on an 8.0.x version if ARC replacement has to be released shortly? Regards, Andreas ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] ARC patent
On Wed, 19 Jan 2005 10:53:14 +0100 Magnus Hagander [EMAIL PROTECTED] wrote: An idea around this would be to plan never to release 8.1. Instead, direct HEAD towards 8.2 with a normal dev cycle (or rather, let's aim for a short one, but in reality short may not be all that short..). Then the eventual ARC replacment (assuming there is one) gets backpatched to the 8.1 branch which is basically only contains all patches from 8.0.x plus the ARC stuff. Personally I prefer the 8.0.1 route for two reasons. 1. We don't know when (or if) the patent will be granted. 8.0.1 fits in no matter what and it doesn't sound like we are going backwards. 2. From a marketing standpoint it is easier to sell our bosses/clients that 8.0.1 is exactly the same as they have running and tested but without a legal constraint than 8.1 which sounds more like a new version. -- D'Arcy J.M. Cain darcy@druid.net | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] ARC patent
* Tom Lane ([EMAIL PROTECTED]) wrote: Neil Conway [EMAIL PROTECTED] writes: So is it firm policy that changes that require a catversion update cannot be made during the 8.1 cycle? Not yet --- I suggested it but didn't get any yeas or nays. I don't feel this is solely core's decision anyway ... what do the assembled hackers think? Don't know if I count, but I've noticed a number of things that people are working on that require initdb's and I think they'd be nice to allow in 8.1 unless the 8.1 cycle is *very* short. I'd also like to get group ownership roles in soon, if possible (and if I find enough time to finish and properly test it). One way to have our cake and eat it too would be for someone to resurrect pg_upgrade during this devel cycle. Anyone feel like working on that? Of course, this would be really nice too.. Stephen signature.asc Description: Digital signature
[HACKERS] Addition to TODO
In working with someone who had ext3 file system corruption, I found the need for this capability I have added to the TODO list: * Add 'tid != tid ' operator for use in corruption recovery -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Much Ado About COUNT(*)
Date: Wed, 12 Jan 2005 18:45:09 -0800
From: Jeff Davis [EMAIL PROTECTED]
To: Alvaro Herrera [EMAIL PROTECTED]
Cc: pgsql-hackers@postgresql.org
Subject: Re: Much Ado About COUNT(*)
Message-ID: [EMAIL PROTECTED]
(cut)
Thanks for the link. It looks like it breaks it up into chunks of about
2KB. I think the
conversation was mostly assuming the tables were somewhat closer to the
size of an
index. If you have more than 2KB per tuple, pretty much anything you do
with an index
would be faster I would think.
Hi Jeff/Alvaro,
I'm considering an application at the moment whereby I would need to do lots
of COUNT(*) on lots of separate tables without a WHERE clause. Would
something like the following help speed up the COUNT(*) by reducing the
tuple size being used for the count?
CREATE SEQUENCE id_seq;
CREATE TABLE person_count (
id int8
);
CREATE TABLE person (
id int8 DEFAULT nextval('id_seq');
first_name text,
surname text,
age int,
address1 text,
address2 text,
address3 text,
address4 text,
postcode text
tel text
);
For each insert:
BEGIN;
INSERT INTO person (first_name, Tel) VALUES ('Fred',
'12345');
INSERT INTO person_count(id) VALUES (currval('id_seq'));
COMMIT;
So then I would use SELECT COUNT(*) FROM person_count whenever I wanted to
know the current number of person records. How much quicker would a COUNT(*)
be if visibility were included in the indices as opposed to a hacked
approach like this?
Many thanks,
Mark.
WebBased Ltd
South West Technology Centre
Tamar Science Park
Plymouth
PL6 8BT
T: +44 (0)1752 791021
F: +44 (0)1752 791023
W: http://www.webbased.co.uk
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [HACKERS] ARC patent
On Jan 19, 2005, at 4:54 AM, Zeugswetter Andreas DAZ SD wrote: Another algorithm that was written by university folk (thus probably not patent prone) that looks promising is: http://www.cs.wm.edu/hpcs/WWW/HTML/publications/papers/TR-02-6.pdf http://parapet.ee.princeton.edu/~sigm2002/papers/p31-jiang.pdf (same, but better typeset) Do not assume that University algorithms are not patent protected. They definitely may be and I know they sometimes are. Princeton has an office dedicated to the issue: http://www.princeton.edu/patents/ -Travis ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Much Ado About COUNT(*)
On Wed, Jan 19, 2005 at 14:59:17 -,
Mark Cave-Ayland [EMAIL PROTECTED] wrote:
BEGIN;
INSERT INTO person (first_name, Tel) VALUES ('Fred',
'12345');
INSERT INTO person_count(id) VALUES (currval('id_seq'));
COMMIT;
So then I would use SELECT COUNT(*) FROM person_count whenever I wanted to
know the current number of person records. How much quicker would a COUNT(*)
be if visibility were included in the indices as opposed to a hacked
approach like this?
You are only going to get a constant factor speed up unless the space savings
allows much better use of cache. You probably want to look at using
triggers to maintain counts in another table.
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
[HACKERS] Caching of frequently used objects
Hi, as there were several questions about increasing performance in dwh systems I was looking for some hints about how to pin an object to the buffer so that they are not aged out due to the space is needed by any other process or object. I know that in oracle you can do this by defining a seperate buffer cache and to asign an object to that special buffer cache. So you could assign objects to one pool and all other objects will use the default one. I think even count(*) queries could benefit from this buffer-splitting due to indexes might be pinned to this buffer pool. So my question is if there is already any comparable functionality or if it is planed. I didn't find any comparable feature or TODO on the list. Regards, Yann ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] ARC patent
Tom Lane wrote: Neil Conway [EMAIL PROTECTED] writes: So is it firm policy that changes that require a catversion update cannot be made during the 8.1 cycle? Not yet --- I suggested it but didn't get any yeas or nays. I don't feel this is solely core's decision anyway ... what do the assembled hackers think? My personal goal for 8.1 is to get autovacuum integrated into the backend. The patch I submitted during the 8.0 dev cycle required a new system table for autovacuum data. Anyway we could get around that without bumping catversion? Perhaps the vacuum daemon could add the table if it's not found? ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Caching of frequently used objects
On Wed, Jan 19, 2005 at 17:44:00 +0100, Yann Michel [EMAIL PROTECTED] wrote: as there were several questions about increasing performance in dwh systems I was looking for some hints about how to pin an object to the buffer so that they are not aged out due to the space is needed by any other process or object. I know that in oracle you can do this by defining a seperate buffer cache and to asign an object to that special buffer cache. So you could assign objects to one pool and all other You can't really do this. You could use a ram file system and use tablespaces to put specified tables in that tablespace. However if your system goes down, the ram fs goes away and that might not be so good. objects will use the default one. I think even count(*) queries could benefit from this buffer-splitting due to indexes might be pinned to this buffer pool. This wouldn't have any special effect on count(*) queries. So my question is if there is already any comparable functionality or if it is planed. I didn't find any comparable feature or TODO on the list. The developers seem to feel that having postgres and the os decide what should be cached based on observed usage is better than having the DBA do this. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Addition to TODO
Bruce Momjian pgman@candle.pha.pa.us writes: In working with someone who had ext3 file system corruption, I found the need for this capability I have added to the TODO list: * Add 'tid != tid ' operator for use in corruption recovery WHERE NOT (tid = tid) ? regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Addition to TODO
Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: In working with someone who had ext3 file system corruption, I found the need for this capability I have added to the TODO list: * Add 'tid != tid ' operator for use in corruption recovery WHERE NOT (tid = tid) ? Oh, that works? Interesting. Thanks. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Refactoring
[Sorry, Neil, for abusing your thread. Moving this discussion back to where it belongs.] On Tue, 18 Jan 2005 13:17:17 -0300, Alvaro Herrera [EMAIL PROTECTED] wrote: Hmm. I think this is a good idea on principle, but what happens in case a previous vacuum was interrupted? Is there a possibility that tuples belonging to that vacuum are still marked MOVED_OFF but are not in vacpage-offsets, for example? These bits are handled by an earlier phase of VACUUM, by HeapTupleSatisfiesVacuum() in scan_heap(). My first vacuum.c refactoring patch, rev 1.281 2004-06-08, added these comments in repair_frag(): /* * VACUUM FULL has an exclusive lock on the relation. So * normally no other transaction can have pending INSERTs or * DELETEs in this relation. A tuple is either * (a) a tuple in a system catalog, inserted or deleted by * a not yet committed transaction or * (b) dead (XMIN_INVALID or XMAX_COMMITTED) or * (c) inserted by a committed xact (XMIN_COMMITTED) or * (d) moved by the currently running VACUUM. * In case (a) we wouldn't be in repair_frag() at all. * In case (b) we cannot be here, because scan_heap() has * already marked the item as unused, see continue above. * Case (c) is what normally is to be expected. * Case (d) is only possible, if a whole tuple chain has been * moved while processing this or a higher numbered block. */ /* * There cannot be another concurrently running VACUUM. If * the tuple had been moved in by a previous VACUUM, the * visibility check would have set XMIN_COMMITTED. If the * tuple had been moved in by the currently running VACUUM, * the loop would have been terminated. We had * elog(ERROR, ...) here, but as we are testing for a * can't-happen condition, Assert() seems more appropriate. */ /* * MOVED_OFF by another VACUUM would have caused the * visibility check to set XMIN_COMMITTED or XMIN_INVALID. */ This should answer your question, unless the comments are wrong. (BTW parts of that patch have been backed out by someone, so you won't find the second comment in current sources.) As for the problem whether the two code paths deal with the same set of tuples, read http://archives.postgresql.org/pgsql-hackers/2004-06/msg00410.php: | [...] These assignments are | preceded either by code that sets the appropriate infomask bits or by | assertions that the bits are already set appropriately. Servus Manfred ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Caching of frequently used objects
Hi, On Wed, Jan 19, 2005 at 11:54:50AM -0600, Bruno Wolff III wrote: objects will use the default one. I think even count(*) queries could benefit from this buffer-splitting due to indexes might be pinned to this buffer pool. This wouldn't have any special effect on count(*) queries. O.K. not full, but due to indexes may be used for some of this queries. the indexes themselves could be pinned into the special buffer pool and need not to be loaded into the cache. So my question is if there is already any comparable functionality or if it is planed. I didn't find any comparable feature or TODO on the list. The developers seem to feel that having postgres and the os decide what should be cached based on observed usage is better than having the DBA do this. The effect while using a seperate buffer cache for different objects, i.e. using a lru list would stay the same. There would be only two more than one buffer cache for a certain object gourp or class. In dwh systems you would normally use a special buffer pool for your dimensions to pin them into memory so that they are not rolled out by any large fact table at all. In fact they can become rolled out but this may only happen if an object belonging to the same pool should be loaded into the cache. This is more or less the fact if the dba has sized the pin-cache to small. Regards, Yann ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] Two-phase commit for 8.1
Hi, Now that we got 8.0 out of the door, I'm submitting my two-phase commit patch again for discussion. http://www.hut.fi/~hlinnaka/pgsql/ Do we want it in 8.1, if we want a short development cycle? It needs a new pg_twophase subdirectory, and it introduces a new system view, so I guess it requires an initdb (or pg_upgrade). Any comments on the implementation or the new commands? I would appreciate help testing the JDBC driver with an application server that does XA recovery properly. - Heikki ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Much Ado About COUNT(*)
On Wed, Jan 19, 2005 at 10:16:38AM -0600, Bruno Wolff III wrote: On Wed, Jan 19, 2005 at 14:59:17 -, Mark Cave-Ayland [EMAIL PROTECTED] wrote: So then I would use SELECT COUNT(*) FROM person_count whenever I wanted to know the current number of person records. How much quicker would a COUNT(*) be if visibility were included in the indices as opposed to a hacked approach like this? You are only going to get a constant factor speed up unless the space savings allows much better use of cache. You probably want to look at using triggers to maintain counts in another table. I'd try using a start value and a differences list. So the differences list would be initially empty and the start value would be 0. On insert or delete, you create a new difference (with +1 or whatever). Periodically, the differences would be added to the start value and the records deleted. Thus the time to calculate the total count is much smaller, and it follows MVCC rules. Of course there are lots of minor details not mentioned here. Not sure if I'd model this with a single table or two. -- Alvaro Herrera ([EMAIL PROTECTED]) I would rather have GNU than GNOT. (ccchips, lwn.net/Articles/37595/) ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Two-phase commit for 8.1
If the patch is ready to be committed early in the cycle, I'd say most definitely ... just depends on how late in the cycle its ready ... I *believe* that 8.1, we're looking at a 2mo cycle before beta, so figure beta for ~April 1st (no april fools jokes, eh?) ... On Wed, 19 Jan 2005, Heikki Linnakangas wrote: Hi, Now that we got 8.0 out of the door, I'm submitting my two-phase commit patch again for discussion. http://www.hut.fi/~hlinnaka/pgsql/ Do we want it in 8.1, if we want a short development cycle? It needs a new pg_twophase subdirectory, and it introduces a new system view, so I guess it requires an initdb (or pg_upgrade). Any comments on the implementation or the new commands? I would appreciate help testing the JDBC driver with an application server that does XA recovery properly. - Heikki ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Much Ado About COUNT(*)
To fill in some details I think what he's saying is this: = create table foo(...); = create table foo_count(num int); = insert into foo_count values(0); = create table foo_change(num int); then create a trigger after delete on foo that does insert into foo_change values(-1) and a trigger after insert on foo that inserts a +1 into foo_change. Periodically, do: = begin; = set transaction isolation level serializable; = update foo_count set num=num+(select sum(num) from foo_change); = delete from foo_change; = commit; = VACUUM; And then any time you need the correct count(*) value, do instead: = select sum(num) from (select num from foo_count union select num from foo_change); And that should work. I haven't tested this exact example, so I may have overlooked something. Hope that helps. That way, you don't have huge waste from the second table, and also triggers maintain it for you and you don't need to think about it. Regards, Jeff Davis On Wed, 2005-01-19 at 17:40 -0300, Alvaro Herrera wrote: On Wed, Jan 19, 2005 at 10:16:38AM -0600, Bruno Wolff III wrote: On Wed, Jan 19, 2005 at 14:59:17 -, Mark Cave-Ayland [EMAIL PROTECTED] wrote: So then I would use SELECT COUNT(*) FROM person_count whenever I wanted to know the current number of person records. How much quicker would a COUNT(*) be if visibility were included in the indices as opposed to a hacked approach like this? You are only going to get a constant factor speed up unless the space savings allows much better use of cache. You probably want to look at using triggers to maintain counts in another table. I'd try using a start value and a differences list. So the differences list would be initially empty and the start value would be 0. On insert or delete, you create a new difference (with +1 or whatever). Periodically, the differences would be added to the start value and the records deleted. Thus the time to calculate the total count is much smaller, and it follows MVCC rules. Of course there are lots of minor details not mentioned here. Not sure if I'd model this with a single table or two. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Caching of frequently used objects
On Wed, 2005-01-19 at 19:14 +0100, Yann Michel wrote: The effect while using a seperate buffer cache for different objects, i.e. using a lru list would stay the same. There would be only two more than one buffer cache for a certain object gourp or class. Multiple buffer caches and pinning objects in the buffer cache have both been discussed extensively in the past. Please check the -hackers archives. -Neil ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Two-phase commit for 8.1
Marc G. Fournier [EMAIL PROTECTED] writes: If the patch is ready to be committed early in the cycle, I'd say most definitely ... just depends on how late in the cycle its ready ... My recollection is that it's quite far from being complete. I had hoped to spend some time during the 8.1 cycle helping Heikki finish it up, but if we stick to the 2-month-dev-cycle idea I'm afraid there's no way it'll be done in time. I thought that some time would probably amount to a solid man-month or so, and there's no way I can spend half my time on just one feature for this cycle. If Heikki wants this in for 8.1, the right thing to do is vote against the short-dev-cycle idea. But we need a plausible answer about what to do about ARC to make that credible... regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [pgsql-hackers] Daily digest v1.4918 (23 messages)
On Mon, 2005-01-17 at 18:43 -0500, Tom Lane wrote: I have already suggested to core that we should insist on 8.1 not requiring an initdb, so as to ensure that people will migrate up to it easily from 8.0. So is it firm policy that changes that require a catversion update cannot be made during the 8.1 cycle? Not yet --- I suggested it but didn't get any yeas or nays. I don't feel this is solely core's decision anyway ... what do the assembled hackers think? (Needless to say, it would be good to get this sorted out early on in the 8.1 development cycle, to avoid the need to revert patches at some point down the line. For those of us working on large projects that will definitely require an initdb, it would also be good to know -- as this policy will likely prevent that work from getting into 8.1) Yes, it has to be decided one way or the other soon. One way to have our cake and eat it too would be for someone to resurrect pg_upgrade during this devel cycle. Anyone feel like working on that? Yup. I feel like working on that and not just feel as I been noising about it in the recent past. In fact I have opend a pgfoundry project for that exact work. regards, tom lane -- Serguei A. Mokhov| /~\The ASCII Computer Science Department | \ / Ribbon Campaign Concordia University | XAgainst HTML Montreal, Quebec, Canada | / \ Email! ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
