[HACKERS] Any optimizations to the join code in 7.1?
Hello. I have a particular query which performs a 15-way join; I believe in normalization ;-). Under 7.0.3, using the defaults where GEQO is enabled after 11, the query (which returns 1 row) takes 10 seconds. With GEQO turned off, it takes 18 seconds. Naturally I intend to upgrade as soon as possible, but I looked through the change log and didn't see anything specific WRT large joins. I was wondering if any work had been done in that area for 7.1. I realize you can only squeeze so much blood from stone, but Thanks for any info, Mike Mascari [EMAIL PROTECTED] ---(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] Comment about PostgreSQL on Epinions.com
On Wed, 25 Apr 2001, Alessio Bragadini wrote: While searching for some info and using google.com I came across http://www.epinions.com/ensw-review-7F55-42531AD1-3A43D81B-prod3 I am the first to understand that the opinion in such a site is worthless and the guy seems not to understand anything about DBMSs but it's quite harsh anyway. Considering the review was done in December he was more than likely using an early beta, but even tho he was asked he didn't say. Some of his comments - speed mainly - looked like he had his mysql and postgresql numbers reversed based on EVERY benchmark I've seen. You are 100% correct tho, his opinion is worthless and is based on an apparent lack of facts. He gives no data to back up any of his claims and according to the info on the left side, people actually listen to him and trust his opinions. Go figure! Vince. -- == Vince Vielhaber -- KA8CSHemail: [EMAIL PROTECTED]http://www.pop4.net 56K Nationwide Dialup from $16.00/mo at Pop4 Networking Online Campground Directoryhttp://www.camping-usa.com Online Giftshop Superstorehttp://www.cloudninegifts.com == ---(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] Re: refusing connections based on load ...
On Tue, 24 Apr 2001, Nathan Myers wrote: On Tue, Apr 24, 2001 at 11:28:17PM -0300, The Hermit Hacker wrote: I have a Dual-866, 1gig of RAM and strip'd file systems ... this past week, I've hit many times where CPU usage is 100%, RAM is 500Meg free and disks are pretty much sitting idle ... Assuming strip'd above means striped, it strikes me that you might be much better off operating the drives independently, with the various tables, indexes, and logs scattered each entirely on one drive. have you ever tried to maintain a database doing this? PgSQL is definitely not designed for this sort of setup, I had symlinks goign everywhere,a nd with the new numbering schema, this is even more difficult to try and do :) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Re: [BUG?] tgconstrrelid doesn't survive a dump/restore
I'll make the change ASAP. Now in CVS along with PG 7.0 compat. code. Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 0500 83 82 82 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/ ---(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] Comment about PostgreSQL on Epinions.com
While searching for some info and using google.com I came across http://www.epinions.com/ensw-review-7F55-42531AD1-3A43D81B-prod3 I am the first to understand that the opinion in such a site is worthless and the guy seems not to understand anything about DBMSs but it's quite harsh anyway. -- Alessio ---(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] refusing connections based on load ...
The whole argument over how to get load averages seems rather silly, and it's moot if the idea of using the load information to alter PG behavior is rejected. I personally have no use for it, but I don't think it's a bad idea in general. Particularly given future redundancy/load sharing features. On the other hand, I think almost all of this stuff can and should be done outside of postmaster. Here is the 0-change version, for rejecting connections, and for operating systems that have built-in firewall capability, such as FreeBSD: a standalone daemon that adds a reject rule for the Postgres port when the load gets too high, and drops that rule when the load goes back down. Now here's the small-change version: add support to Postgres for a SET command or similar way to say stop accepting connections, or set accept/transaction delay to X. Write a standalone daemon which monitors the load and issues commands to Postgres as necessary. That daemon may need extra privileges, but it is small, auditable, and doesn't talk to the outside world. It's probably better to include in the Postgres protocol support for accepting (TCP-wise) a connection, then closing it with an error message, because this daemon needs to be able to connect to tell it to let users in again. It's probably as simple as always letting the superuser in. The latter is nicer in a number of ways. Persistent connections were already mentioned - rejecting new connections may not be a good enough solution there. With a fancier approach, you could even hang up on some existing connections with an appropriate message, or just NOTICE them that you're slowing them down or you'd like them to go away voluntarily. From a web-hosting standpoint, someday it would be nifty to have per-user-per-connection limits, so I could put up a couple of big PG servers and only allow user X one connection, which can't use more than Y amount of RAM, and passes a scheduling hint to the OS so it shares CPU time with other economy-class users, which can be throttled down to 25% of what ultra-mega-hosting users get. Simple load shedding is a baby step in the right direction. If nothing else, it will cast a spotlight on some of the problem areas. -- Christopher Masto Senior Network Monkey NetMonger Communications [EMAIL PROTECTED][EMAIL PROTECTED]http://www.netmonger.net Free yourself, free your machine, free the daemon -- http://www.freebsd.org/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] ERROR: parser: parse error at or near JOIN
Tom Lane wrote: Fernando Nasser [EMAIL PROTECTED] writes: Is anyone else seeing this? No. I have the current CVS sources and make check ends up with one failure. My regression.diffs shows: I think you must have built gram.c with a broken bison or yacc. What exactly is configure picking, and what version is it? Yes you are right. With: [12:03:04] flex -V flex version 2.5.4 [12:03:08] bison -V GNU Bison version 1.28 it fails, but using older versions of flex and bison the regression goes away: [12:05:30] flex -V flex Cygnus version 2.5-gnupro-99r1 [12:05:34] bison -V GNU Bison version 1.25 Thank you very much. -- Fernando Nasser Red Hat Canada Ltd. E-Mail: [EMAIL PROTECTED] 2323 Yonge Street, Suite #300 Toronto, Ontario M4P 2C9 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] ERROR: parser: parse error at or near JOIN
Fernando Nasser [EMAIL PROTECTED] writes: Tom Lane wrote: I think you must have built gram.c with a broken bison or yacc. What exactly is configure picking, and what version is it? Yes you are right. With: [12:03:04] flex -V flex version 2.5.4 [12:03:08] bison -V GNU Bison version 1.28 it fails, but using older versions of flex and bison the regression goes away: [12:05:30] flex -V flex Cygnus version 2.5-gnupro-99r1 [12:05:34] bison -V GNU Bison version 1.25 Er, surely you stated that backwards? flex 2.5.4 and bison 1.28 are what all of the developers use, AFAIK (I know that's what I have anyway). bison 1.25 might well have some problems though... 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] Any optimizations to the join code in 7.1?
Mike Mascari [EMAIL PROTECTED] writes: I have a particular query which performs a 15-way join; You should read http://www.postgresql.org/devel-corner/docs/postgres/explicit-joins.html regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] Re: Any optimizations to the join code in 7.1?
On Wed, 25 Apr 2001, Tom Lane wrote: Mike Mascari [EMAIL PROTECTED] writes: I have a particular query which performs a 15-way join; You should read http://www.postgresql.org/devel-corner/docs/postgres/explicit-joins.html I was recently poring over this page myself, as I've been working w/some larger-than-usual queries. Two questions: 1) it appears (from my tests) that SELECT * FROM CREATE VIEW joined as SELECT p.id, p.pname, c.cname FROM p LEFT OUTER JOIN c using (id) gives the same answer as SELECT * FROM CREATE VIEW nested SELECT p.id, p.pname, (select c.cname from c where c.id = p.id) FROM p However, I often am writing VIEWs that will be used by developers in a front-end system. Usually, this view might have 30 items in the select clause, but the developer using it is likely to only as for four or five items. In this case, I often prefer the subquery form because it appears that SELECT id, pname FROM joined is more complicated than SELECT id, pname FROM nested as the first has to perform the join, and the second doesn't. Is this actually correct? 2) The explicit-joins help suggests that manual structuring and experimentation might help -- has anyone written (or could anyone write) anthing about where to start in guessing what join order might be optimal? -- Joel Burton [EMAIL PROTECTED] Director of Information Systems, Support Center of Washington ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] ERROR: parser: parse error at or near JOIN
Fernando Nasser [EMAIL PROTECTED] writes: Is anyone else seeing this? No. I have the current CVS sources and make check ends up with one failure. My regression.diffs shows: I think you must have built gram.c with a broken bison or yacc. What exactly is configure picking, and what version is it? regards, tom lane ---(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] refusing connections based on load ...
Tom Lane writes: A conncurrent-xacts limit isn't perfect of course, but I think it'd be pretty good, and certainly better than anything based on the available load-average numbers. The concurrent transaction limit would allow you to control the absolute load of the PostgreSQL server, but we can already do that and it's not what we're after here. The idea behind the load average based approach is to make the postmaster respect the situation of the overall system. Additionally, the concurrent transaction limit would only be useful on setups that have a lot of idle transactions. Those setups exist, but not everywhere. To me, both of these approaches are in the if you don't like it, don't use it category. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] Re: Any optimizations to the join code in 7.1?
Joel Burton [EMAIL PROTECTED] writes: 1) it appears (from my tests) that SELECT * FROM CREATE VIEW joined as SELECT p.id, p.pname, c.cname FROM p LEFT OUTER JOIN c using (id) gives the same answer as SELECT * FROM CREATE VIEW nested SELECT p.id, p.pname, (select c.cname from c where c.id = p.id) FROM p Only if c.id is a unique column (ie, there are always 0 or 1 matches in c for any given p.id). Otherwise the subselect form will fail. However, I often am writing VIEWs that will be used by developers in a front-end system. Usually, this view might have 30 items in the select clause, but the developer using it is likely to only as for four or five items. In this case, I often prefer the subquery form because it appears that SELECT id, pname FROM joined is more complicated than SELECT id, pname FROM nested as the first has to perform the join, and the second doesn't. Is this actually correct? This approach is probably reasonable if the cname field of the view result is seldom wanted at all, and never used as a WHERE constraint. You'd get a very nonoptimal plan if someone did select * from nested where cname like 'foo%' since the planner has no way to use the LIKE constraint to limit the rows fetched from p. In the JOIN format, on the other hand, I think the constraint could be exploited. Also bear in mind that the subselect form is essentially forcing the join to be done via a nested loop. If you have an index on c.id then this may not be too bad, but without one the performance will be horrid. Even with an index, nested loop with inner indexscan is not the join method of choice if you are retrieving a lot of rows. 2) The explicit-joins help suggests that manual structuring and experimentation might help -- has anyone written (or could anyone write) anthing about where to start in guessing what join order might be optimal? The obvious starting point is the plan produced by the planner from an unconstrained query. Even if you don't feel like trying to improve it, you could cut the time to reproduce the plan quite a bit --- just CROSS JOIN a few of the relation pairs that are joined first in the unconstrained plan. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] refusing connections based on load ...
Jan Wieck and I talked about this for awhile yesterday, and we came to the conclusion that load-average-based throttling is a Bad Idea. Quite aside from the portability and permissions issues that may arise in getting the numbers, the available numbers are the wrong thing: (1) On most Unix systems, the finest-grain load average that you can get is a 1-minute average. This will lose both on the ramp up (by the time you realize you overdid it, you've let *way* too many xacts through the starting gate) and on the ramp down (you'll hold off xacts for circa a minute after the crunch is past). (2) You can also get shorter-time-frame CPU usage numbers (at least, most versions of top(1) seem to display such things) but CPU load is really not very helpful for measuring how badly the system is thrashing. Postgres tends to beat your disks into the ground long before it pegs the CPU. Too bad there's no disk usage numbers. However, there is another possibility that would be simple to implement and perfectly portable: allow the dbadmin to impose a limit on the number of simultaneous concurrent transactions. (Setting this equal to the max allowed number of backends would turn off the limit.) That way, you could have umpteen open connections, but you could limit how many of them were actually *doing* something at any given instant. If more than N try to start transactions at the same time, the later ones have to wait for the earlier ones to finish before they can start. This'd be trivial to do with a semaphore initialized to N --- P() it in StartTransaction and V() it in Commit/AbortTransaction. A conncurrent-xacts limit isn't perfect of course, but I think it'd be pretty good, and certainly better than anything based on the available load-average numbers. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] refusing connections based on load ...
On Wed, 25 Apr 2001, Peter Eisentraut wrote: Tom Lane writes: A conncurrent-xacts limit isn't perfect of course, but I think it'd be pretty good, and certainly better than anything based on the available load-average numbers. The concurrent transaction limit would allow you to control the absolute load of the PostgreSQL server, but we can already do that and it's not what we're after here. The idea behind the load average based approach is to make the postmaster respect the situation of the overall system. Additionally, the concurrent transaction limit would only be useful on setups that have a lot of idle transactions. Those setups exist, but not everywhere. To me, both of these approaches are in the if you don't like it, don't use it category. Agreed ... by default, the loadavg method could be set to zero, to ignore ... I don't care if I'm off by 1min before I catch the increase, the fact is that I have caught it, and prevent any new ones coming in until it drops off again ... Make it two variables: transla rejectla if transla is hit, restrict on transactions, letting others connect, but putting them on hold while the la drops again ... if it goes above rejectla, refuse new connections altogether ... so now I can set something like: transla = 8 rejectla = 16 but if loadavg goes above 16, I want to get rid of what is causing the load to rise *before* adding new variables to the mix that will cause it to rise higher ... and your arg about permissions (Tom's, not Peter's) is moot in at least 3 of the major systems (Linux, *BSD and Solaris) as there is a getloadavg() function in all three for doing 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] refusing connections based on load ...
Peter Eisentraut [EMAIL PROTECTED] writes: The idea behind the load average based approach is to make the postmaster respect the situation of the overall system. That'd be great if we could do it, but as I pointed out, the available stats do not allow us to do it very well. I think this will create a lot of portability headaches for no real gain. If it were something we could just do and forget, I would not object --- but the porting issues will create a LOT more work than I think this can possibly be worth. The fact that the work is distributed and will mostly be incurred by people other than the ones advocating the change doesn't improve matters. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] concurrent Postgres on NUMA - howto ?
Mauricio Breternitz [EMAIL PROTECTED] writes: Notice that WriteBuffer would just put the fresh copy of the page out in the shared space. Other backends would get the latest copy of the page when THEY execute BufferAlloc() afterwards. You seem to be assuming that BufferAlloc is mutually exclusive across backends --- it's not. As I said, you'd have to look at transferring data at LockBuffer time to make this work. [Granted about the bandwidth needs. In my target arch, access to shmem is costlier and local mem, and cannot be done via pointers What? How do you manage to memcpy out of shmem then? (so a lot of code that might have pointers inside the shmem buffer may need to be tracked down changed)]. You're correct, Postgres assumes it can have pointers to data inside the page buffers. I don't think changing that is feasible. I find it hard to believe that you can't have pointers to shmem though; IMHO it's not shmem if it can't be pointed at. [Mhy reasoning for this is that a backend needs to have exclusive access to a buffer when it writes to it. And I think it 'advertises' the new buffer contents to the world when it sets the BM_DIRTY flag.] No. BM_DIRTY only advises the buffer manager that the page must eventually be written back to disk; it does not have anything to do with when/whether other backends see data changes within the page. One more time: LockBuffer is what you need to be looking at. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] Cursor support in pl/pg
Now that 7.1 is safely in the can, is it time to consider this patch? It provides cursor support in PL. http://www.airs.com/ian/postgresql-cursor.patch Nathan Myers [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] tables/indexes/logs on different volumes
On Wed, Apr 25, 2001 at 09:41:57AM -0300, The Hermit Hacker wrote: On Tue, 24 Apr 2001, Nathan Myers wrote: On Tue, Apr 24, 2001 at 11:28:17PM -0300, The Hermit Hacker wrote: I have a Dual-866, 1gig of RAM and strip'd file systems ... this past week, I've hit many times where CPU usage is 100%, RAM is 500Meg free and disks are pretty much sitting idle ... Assuming strip'd above means striped, it strikes me that you might be much better off operating the drives independently, with the various tables, indexes, and logs scattered each entirely on one drive. have you ever tried to maintain a database doing this? PgSQL is definitely not designed for this sort of setup, I had symlinks going everywhere, and with the new numbering schema, this is even more difficult to try and do :) Clearly you need to build a tool to organize it. It would help a lot if PG itself could provide some basic assistance, such as calling a stored procedure to generate the pathname of the file. Has there been any discussion of anything like that? Nathan Myers [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Cursor support in pl/pg
[EMAIL PROTECTED] (Nathan Myers) writes: Now that 7.1 is safely in the can, is it time to consider this patch? Not till we've forked the tree for 7.2, which is probably a week or so away... regards, tom lane ---(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] refusing connections based on load ...
The Hermit Hacker wrote: Agreed ... by default, the loadavg method could be set to zero, to ignore ... I don't care if I'm off by 1min before I catch the increase, the fact is that I have caught it, and prevent any new ones coming in until it drops off again ... Make it two variables: transla rejectla if transla is hit, restrict on transactions, letting others connect, but putting them on hold while the la drops again ... if it goes above rejectla, refuse new connections altogether ... so now I can set something like: transla = 8 rejectla = 16 but if loadavg goes above 16, I want to get rid of what is causing the load to rise *before* adding new variables to the mix that will cause it to rise higher ... and your arg about permissions (Tom's, not Peter's) is moot in at least 3 of the major systems (Linux, *BSD and Solaris) as there is a getloadavg() function in all three for doing this ... I've just recompiled my php4 module to get sysvsem support and limited the number of concurrent DB transactions on the applicationlevel.The (not yet finished) TPC-C implementation I'm working on scales about 3-4 times better now. That's an improvement! This proves that limiting the number of concurrently running transactions is sufficient to keep the system load down. Combined these two look as follows: - We start with a fairly high setting in the semaphore. - When the system load exceeds the high-watermark, we don't increment the semaphore back after transaction end (need to ensure that at least a small minimum of xacts is left, but that's easy). - When the system goes back to normal load level, we slowly increase the semaphore again. This way we might have some peek pushing the system against the wall for a moment. If that doesn't go away quickly, we just delay users (who see some delay anyway actually). Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Cursor support in pl/pg
Tom Lane wrote: [EMAIL PROTECTED] (Nathan Myers) writes: Now that 7.1 is safely in the can, is it time to consider this patch? Not till we've forked the tree for 7.2, which is probably a week or so away... IIRC the patch only provides the syntax for CURSOR to PL/pgSQL. Not real cursor support on the SPI level. So it's still the same as before, the backend will try to suck up the entire resultset into the SPI tuple table (that's memory) and die if it's huge enough. What we really need is an improvement to the SPI manager to support cursor (or cursor like behaviour through repeated executor calls). Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] refusing connections based on load ...
The Hermit Hacker [EMAIL PROTECTED] writes: Autoconf has a 'LOADAVG' check already, so what is so problematic about using that to enabled/disable that feature? Because it's tied to a GNU getloadavg.c implementation, which we'd have license problems with using. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] Re: Any optimizations to the join code in 7.1?
On Wed, 25 Apr 2001, Tom Lane wrote: 2) The explicit-joins help suggests that manual structuring and experimentation might help -- has anyone written (or could anyone write) anthing about where to start in guessing what join order might be optimal? The obvious starting point is the plan produced by the planner from an unconstrained query. Even if you don't feel like trying to improve it, you could cut the time to reproduce the plan quite a bit --- just CROSS JOIN a few of the relation pairs that are joined first in the unconstrained plan. In other words, let it do the work, and steal the credit for ourselves. :-) Thanks, Tom. I appreciate your answers to my questions. In other DB systems I've used, some find that for this original query: SELECT * FROM a, b WHERE a.id=b.id AND b.name = 'foo'; that this version SELECT * FROM a JOIN b USING (id) WHERE b.name = 'foo'; has slower performance than SELECT * FROM b JOIN a USING (id) WHERE b.name = 'foo'; because it can reduce b before any join. Is it safe to assume that this is a valid optimization in PostgreSQL? If this whole thing were a view, except w/o the WHERE clause, and we were querying the view w/the b.name WHERE clause, would we still see a performance boost from the right arrangement? (ie, does our criteria get pushed down early enough in the joining process?) TIA, -- Joel Burton [EMAIL PROTECTED] Director of Information Systems, Support Center of Washington ---(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] refusing connections based on load ...
Jan Wieck [EMAIL PROTECTED] writes: This proves that limiting the number of concurrently running transactions is sufficient to keep the system load down. Combined these two look as follows: - We start with a fairly high setting in the semaphore. - When the system load exceeds the high-watermark, we don't increment the semaphore back after transaction end (need to ensure that at least a small minimum of xacts is left, but that's easy). - When the system goes back to normal load level, we slowly increase the semaphore again. This is a nice way of dealing with the slow reaction time of the load average --- you don't let it directly drive the decision about when to start a new transaction, but instead let it tweak the ceiling on number of concurrent xacts. I like it. You probably don't need to have any additional slowness in the loop other than the inherent averaging in the kernel's load average. I'm still concerned about portability issues, and about whether load average is really the right number to be looking at, however. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Re: Any optimizations to the join code in 7.1?
Joel Burton [EMAIL PROTECTED] writes: In other DB systems I've used, some find that for this original query: SELECT * FROM a, b WHERE a.id=b.id AND b.name = 'foo'; that this version SELECT * FROM a JOIN b USING (id) WHERE b.name = 'foo'; has slower performance than SELECT * FROM b JOIN a USING (id) WHERE b.name = 'foo'; because it can reduce b before any join. Is it safe to assume that this is a valid optimization in PostgreSQL? In general, that'd be a waste of time --- our planner considers the same set of plans in either case. However, it could make a difference if the planner thinks that the two choices (a outer or b outer) have exactly the same cost. In that case the order you wrote them in will influence which plan actually gets picked; and if the planner's estimate is wrong --- ie, there really is a considerable difference in the costs --- then you could see a change in performance depending on which way you wrote it. That's a pretty unusual circumstance, maybe, but it just happens that I'm in the middle of looking at a planning bug wherein exactly this behavior occurs... If this whole thing were a view, except w/o the WHERE clause, and we were querying the view w/the b.name WHERE clause, would we still see a performance boost from the right arrangement? (ie, does our criteria get pushed down early enough in the joining process?) Shouldn't make a difference; AFAIK the WHERE clause will get pushed down as far as possible, independently of whether a view is involved or you wrote it out the hard way. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] refusing connections based on load ...
On Wed, 25 Apr 2001, Tom Lane wrote: Peter Eisentraut [EMAIL PROTECTED] writes: The idea behind the load average based approach is to make the postmaster respect the situation of the overall system. That'd be great if we could do it, but as I pointed out, the available stats do not allow us to do it very well. I think this will create a lot of portability headaches for no real gain. If it were something we could just do and forget, I would not object --- but the porting issues will create a LOT more work than I think this can possibly be worth. The fact that the work is distributed and will mostly be incurred by people other than the ones advocating the change doesn't improve matters. As I mentioned, getloadavg() appears to be support on 3 of the primary platforms we work with, so I'd say for most installations, portability issues aren't an issue ... Autoconf has a 'LOADAVG' check already, so what is so problematic about using that to enabled/disable that feature? If ( loadavg available on OSenabled in postgresql.conf ) operate on it } else ( loadavg not available on OS enabled ) noop with a WARN level error that its not available } ---(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] refusing connections based on load ...
On Wed, 25 Apr 2001, Tom Lane wrote: The Hermit Hacker [EMAIL PROTECTED] writes: Autoconf has a 'LOADAVG' check already, so what is so problematic about using that to enabled/disable that feature? Because it's tied to a GNU getloadavg.c implementation, which we'd have license problems with using. It's part of the standard C library in FreeBSD. Any other platforms have it built in? Vince. -- == Vince Vielhaber -- KA8CSHemail: [EMAIL PROTECTED]http://www.pop4.net 56K Nationwide Dialup from $16.00/mo at Pop4 Networking Online Campground Directoryhttp://www.camping-usa.com Online Giftshop Superstorehttp://www.cloudninegifts.com == ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] Schema Issue
I want to extract tables schema information, i've looked at src/bin/psql/describe.c but i cannot determine the datatype 'serial' and 'references' from pg_*, i understand that triggers are generated for serial and references, so how i can understand from my perl application the full schema ? thanks, valter _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] refusing connections based on load ...
On Wed, 25 Apr 2001, Vince Vielhaber wrote: On Wed, 25 Apr 2001, Tom Lane wrote: The Hermit Hacker [EMAIL PROTECTED] writes: Autoconf has a 'LOADAVG' check already, so what is so problematic about using that to enabled/disable that feature? Because it's tied to a GNU getloadavg.c implementation, which we'd have license problems with using. It's part of the standard C library in FreeBSD. Any other platforms have it built in? As has been mentioned, Solaris and Linux also have it ... ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] refusing connections based on load ...
On Wed, 25 Apr 2001, Tom Lane wrote: I'm still concerned about portability issues, and about whether load average is really the right number to be looking at, however. Its worked for Sendmail for how many years now, and the code is there to use, with all portability issues resolved for every platform they use ... and a growing number of platforms appear to have the mechanisms already built into their C libraries ... ---(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] Open source is great, but too tempting
Just a little note of pseudo humor. We could not postmaster (pg version 7.0.3) and I could not figure out why. I checked directory permissions, all that. It kept complaining that it could not create the pid file. I did not understand why it would not work. I grepped through all the postgres source to find that this error could also be due to an inability to write the pid file. I checked the disk space, of which there was none. Doh! I should have just done a df at the start. -- I'm not offering myself as an example; every life evolves by its own laws. http://www.mohawksoft.com ---(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
