Re: [HACKERS] Thoughts about updateable views
In a galaxy far, far away Bernd wrote: The context: http://archives.postgresql.org/pgsql-hackers/2004-03/msg00999.php > so joined views are even not updateable, too. I don't find the why of this on the specs and the threads about this issue ignore the comment. Is this right? regards, Jaime Casanova _ Do You Yahoo!? Información de Estados Unidos y América Latina, en Yahoo! Noticias. Visítanos en http://noticias.espanol.yahoo.com ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Bgwriter behavior
Bruce Momjian <[EMAIL PROTECTED]> writes: > The only way I could see it being worse than pre-8.0 is that the > bgwriter is doing fsync of all open files rather than using sync. Other > than that, I think it should behave the same, or slightly better, > right? It's possible that there exist platforms on which this is a loss --- that is, the OS's handling of fsync is so inefficient that multiple fsync calls are worse than one sync call even though less I/O is forced. But I haven't seen any actual evidence of that; and if such platforms do exist I'm not sure I'd blink anyway. We are not required to optimize for brain-dead kernels. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Bgwriter behavior
Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > So what are we doing for 8.0? > > Well, it looks like RC2 has already crashed and burned --- I can't > imagine that Marc will let us release without an RC3 given what was > committed today, never mind the btree bug that Mark Wong seems to have > found. So maybe we should just bite the bullet and do something real > about this. Oh, is it that bad? > I'm willing to code up a proposed patch for the two-track idea I > suggested, and if anyone else has a favorite maybe they could write > something too. But do we have the resources to test such patches and > make a decision in the next few days? > > At the moment my inclination is to sit on what we have. I've not seen > any indication that 8.0 is really worse than earlier releases; the most > you could argue against it is that it's not as much better as we hoped. > That's not grounds to muck around at the RC3 stage. That was my question. It seems bgwriter is fine for low to medium traffic but doesn't handle high traffic, and increasing the scan rate makes things worse. I am fine with doing nothing, but if we are going to do something, I would like to do it now rather than later. The only way I could see it being worse than pre-8.0 is that the bgwriter is doing fsync of all open files rather than using sync. Other than that, I think it should behave the same, or slightly better, right? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Bgwriter behavior
At the moment my inclination is to sit on what we have. I've not seen any indication that 8.0 is really worse than earlier releases; the most you could argue against it is that it's not as much better as we hoped. That's not grounds to muck around at the RC3 stage. If is is any help, CMD is basically dead right now and I expect it will be that way until the new year. 4 of my 5 C programmers are on vacation but I do have one and a couple of non c programmers. We can't fix, but we can definately help test. Sincerely, Joshua D. Drake regards, tom lane ---(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 -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com PostgreSQL Replicator -- production quality replication for PostgreSQL begin:vcard fn:Joshua Drake n:Drake;Joshua org:Command Prompt, Inc. adr:;;PO Box 215 ;Cascade Locks;OR;97014;US email;internet:[EMAIL PROTECTED] title:Consultant tel;work:503-667-4564 tel;fax:503-210-0334 x-mozilla-html:FALSE url:http://www.commandprompt.com version:2.1 end:vcard ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Bgwriter behavior
Bruce Momjian <[EMAIL PROTECTED]> writes: > So what are we doing for 8.0? Well, it looks like RC2 has already crashed and burned --- I can't imagine that Marc will let us release without an RC3 given what was committed today, never mind the btree bug that Mark Wong seems to have found. So maybe we should just bite the bullet and do something real about this. I'm willing to code up a proposed patch for the two-track idea I suggested, and if anyone else has a favorite maybe they could write something too. But do we have the resources to test such patches and make a decision in the next few days? At the moment my inclination is to sit on what we have. I've not seen any indication that 8.0 is really worse than earlier releases; the most you could argue against it is that it's not as much better as we hoped. That's not grounds to muck around at the RC3 stage. regards, tom lane ---(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] Bgwriter behavior
Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > First, we remove the GUC bgwriter_maxpages because I don't see a good > > way to set a default for that. A default value needs to be based on a > > percentage of the full buffer cache size. > > This is nonsense. The admin knows what he set shared_buffers to, and so > maxpages and percent of shared buffers are not really distinct ways of > specifying things. The cases that make a percent spec useful are if > (a) it is a percent of a non-constant number (eg, percent of total dirty > pages as in the current code), or (b) it is defined in a way that lets > it limit the amount of scanning work done (which it isn't useful for in > the current code). But a maxpages spec is useful for (b) too. More to > the point, maxpages is useful to set a hard limit on the amount of I/O > generated by the bgwriter, and I think people will want to be able to do > that. I figured that if we specify a percentage users would not need to update this value regularly if they increase their shared buffers. I agree if you want to limit total I/O by the bgwriter an actual pages a count is better but I assumed we were looking for bgwriter to do a certain percentage of total writes. If the system is doing a lot of writes then limiting the bgwriter doesn't help because then the backends are going to have to do the writes themselves. > > Now, to control the bgwriter frequency we multiply the percent of the > > list it had to span by the bgwriter_delay value to determine when to run > > bgwriter next. > > I'm less than enthused about this. The idea of the bgwriter is to > trickle out writes in a way that doesn't affect overall performance too > much. Not to write everything in sight at any cost. No question my idea makes tuning diffcult. I was hoping it would be self-tuning but I am not sure. > I like the hybrid "keep the bottom of the ARC list clean, plus do a slow > clock scan on the main buffer array" approach better. I can see that > that directly impacts both of the goals that the bgwriter has. I don't > see how a variable I/O rate really improves life on either score; it > just makes things harder to predict. So what are we doing for 8.0? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] pg_autovacuum w/ dbt2
Mark Wong wrote: The overall throughput is better for a run like this: http://www.osdl.org/projects/dbt2dev/results/dev4-010/207/ A drop from 3865 to 2679 (31%) by just adding pg_autovacuum. That's what I meant by "not good". :) I would agree that is "not good" :-) It sounds like pg_autovacuum is being to aggressive for this type of load, that is vacuuming more often than needed, however the lazy vacuum options were added so as to reduce the performance impact of running a vacuum while doing other things, so, I would recommend both higher autovacuum thresholds and trying out some of the lazy vacuum settings. I'll start with the additional debug messages, with 8.0rc2, before I start changing the other settings, if that sounds good. Sounds fine. From Tom Lane's response, we have a backend bug that needs to be resolved and I think that is the priority. ---(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] rc2 bundled
On Tue, Dec 21, 2004 at 01:56:38AM -0400, Marc G. Fournier wrote: > > check her over .. Torrents are ready whenever anyone else is. :) Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote! ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_autovacuum w/ dbt2
Mark Wong <[EMAIL PROTECTED]> writes: > I was going to try Matthew's suggestion of turning up the debug on > pg_autovacuum, unless you don't that'll help find the cause. It won't help --- this is a backend-internal bug of some kind. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] pg_autovacuum w/ dbt2
On Tue, Dec 21, 2004 at 05:56:47PM -0500, Tom Lane wrote: > Mark Wong <[EMAIL PROTECTED]> writes: > > On Tue, Dec 21, 2004 at 02:23:41PM -0500, Tom Lane wrote: > >> Mark Wong <[EMAIL PROTECTED]> writes: > >>> [2004-12-20 15:48:18 PST] The error is [ERROR: failed to > >>> re-find parent key in "pk_district" > >> > >> Yikes. Is this reproducible? > > > Yes, and I think there is one for each of the rollbacks that are > > occuring in the workload. Except for the 1% that's supposed to happen > > for the new-order transaction. > > Well, we need to find out what's causing that. There are two possible > sources of that error (one elog in src/backend/access/nbtree/nbtinsert.c, > and one in src/backend/access/nbtree/nbtpage.c) and neither of them > should ever fire. > > If you want to track it yourself, please change those elog(ERROR)s to > elog(PANIC) so that they'll generate core dumps, then build with > --enable-debug if you didn't already (--enable-cassert would be good too) > and get a debugger stack trace from the core dump. > > Otherwise, can you extract a test case that causes this without needing > vast resources to run? > > regards, tom lane I was going to try Matthew's suggestion of turning up the debug on pg_autovacuum, unless you don't that'll help find the cause. I'm not sure if I can more easily reproduce the problem but i can try. I'll go ahead and make the elog() changes you recommended and do a run overnight either way. Mark ---(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] pg_autovacuum w/ dbt2
Mark Wong <[EMAIL PROTECTED]> writes: > On Tue, Dec 21, 2004 at 02:23:41PM -0500, Tom Lane wrote: >> Mark Wong <[EMAIL PROTECTED]> writes: >>> [2004-12-20 15:48:18 PST] The error is [ERROR: failed to re-find >>> parent key in "pk_district" >> >> Yikes. Is this reproducible? > Yes, and I think there is one for each of the rollbacks that are > occuring in the workload. Except for the 1% that's supposed to happen > for the new-order transaction. Well, we need to find out what's causing that. There are two possible sources of that error (one elog in src/backend/access/nbtree/nbtinsert.c, and one in src/backend/access/nbtree/nbtpage.c) and neither of them should ever fire. If you want to track it yourself, please change those elog(ERROR)s to elog(PANIC) so that they'll generate core dumps, then build with --enable-debug if you didn't already (--enable-cassert would be good too) and get a debugger stack trace from the core dump. Otherwise, can you extract a test case that causes this without needing vast resources to run? 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] pg_autovacuum w/ dbt2
The overall throughput is better for a run like this: http://www.osdl.org/projects/dbt2dev/results/dev4-010/207/ A drop from 3865 to 2679 (31%) by just adding pg_autovacuum. That's what I meant by "not good". :) I'll start with the additional debug messages, with 8.0rc2, before I start changing the other settings, if that sounds good. Mark On Tue, Dec 21, 2004 at 02:33:57PM -0500, Matthew T. O'Connor wrote: > Mark Wong wrote: > > >After all this time I finally got around to vacuuming the database > >with dbt2 with pg_autovacuum. :) > > http://www.osdl.org/projects/dbt2dev/results/dev4-010/215/ > > > > > Thanks! > > >Doesn't look so good though, probably because I'm not using optimal > >settings with pg_autovacuum. So far I have only tried the default > >settings (running without any arguments, except -D). > > > > > I don't know what you mean by "Not Good" since I don't have graphs from > a similar test without pg_autovacuum handy. Do you have a link to such > a test? > > As for better pg_autovacuum settings, It appears that the little > performance dips are happening about once every 5 minutes, which if I > remember correctly is the default sleep time. You might try playing > with the lazy vacuum settings to see if that smooths out the curve. > Beyond that all you can do is play with the thresholds to see if there > is a better sweet spot than the defaults (which by the way I have no > confidence in, they were just conservative guesses) > > >The only thing that's peculiar is a number of unexpected rollbacks > >across all of the transactions. I suspect it was something to do with > >these messages coming from pg_autovacuum: > > > >[2004-12-20 15:48:18 PST] ERROR: Can not refresh statistics information > >from the database dbt2. > >[2004-12-20 15:48:18 PST] The error is [ERROR: failed to re-find > >parent key in "pk_district" > >] > > > > > Not sure what this is all about, but if you turn up the debug level to 4 > or greater (pg_autovacuum -d4), pg_autovacuum will log the query that is > causing the problems, that would be helpful output to have. > > >This is with 8.0rc1. I can get rc2 installed since it just came out. > >So let me know what I can try and what not. > > > > > I don't think anything has changed for pg_autovacuum between rc1 and rc2. > > > thanks again for the good work!!! ---(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] pg_autovacuum w/ dbt2
On Tue, Dec 21, 2004 at 02:23:41PM -0500, Tom Lane wrote: > Mark Wong <[EMAIL PROTECTED]> writes: > > [2004-12-20 15:48:18 PST] The error is [ERROR: failed to re-find > > parent key in "pk_district" > > ] > > Yikes. Is this reproducible? > > regards, tom lane Yes, and I think there is one for each of the rollbacks that are occuring in the workload. Except for the 1% that's supposed to happen for the new-order transaction. Mark ---(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] RC2 and open issues
On Tue, Dec 21, 2004 at 10:26:48AM -0500, Tom Lane wrote: > Richard Huxton <[EMAIL PROTECTED]> writes: > > However, one thing you can say is that if block B hasn't been written to > > since you last checked, then any blocks older than that haven't been > > written to either. > > [ itch... ] Can you? I don't recall exactly when a block gets pushed > up the ARC list during a ReadBuffer/WriteBuffer cycle, but at the very > least I'd have to say that this assumption is vulnerable to race > conditions. > > Also, the cntxDirty mechanism allows a block to be dirtied without > changing the ARC state at all. I am not very clear on whether Vadim > added that mechanism just for performance or because there were > fundamental deadlock issues without it; but in either case we'd have > to think long and hard about taking it out for the bgwriter's benefit. OTOH, ISTM that it's ok if the bgwriter occasionally misses blocks. These blocks would either result in a backend or the checkpointer having to write out a block (not so great), or the bgwriter could occasionally ignore it's bookmart and restart it's scan from the LRU. Of course I'm assuming that any race-conditions could be made to impact only the bgwriter and nothing else, which may be a bad assumption. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Bgwriter behavior
A quick $0.02 on how DB2 does this (at least in 7.x). They used a combination of everything that's been discussed. The first priority of their background writer was to keep the LRU end of the cache free so individual backends would never have to wait to get a page. Then, they would look to pages that had been dirty for 'a long time', which was user configurable. Pages older than this setting were candidates to be written out even if they weren't close to LRU. Finally, I believe there were also settings for how often the writer would fire up, and how much work it would do at once. I agree that the first priority should be to keep clean pages near LRU, but that you also don't want to get hammered at checkpoint time. I think what might be interesting to consider is keeping a list of dirty pages, which would remove the need to scan a very large buffer. Of course, in an environment with a heavy update load, it could be better to just scan the buffers, especially if you don't do a clock-sweep but instead look at where the last page you wrote out has ended up in the LRU list since you last ran, and start scanning from there (by definition everything after that page would have to be clean). Of course this is just conjecture on my part and would need testing to verify, and it's obviously beyond the scope of 8.0. As for 8.0, I suspect at this point it's probably best to just go with whatever method has the smallest amount of code impact unless it's inherenttly broken. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(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] plperl: memory usage stacking
Andrew Dunstan <[EMAIL PROTECTED]> writes: > Please submit as a context diff (made with diff -c) and send it to the > -patches list, rather than -hackers. It would also help if you'd identify what version the diff is against. I can't match up any of this with current sources... regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Shared row locking
On Mon, Dec 20, 2004 at 03:09:24PM -0300, Alvaro Herrera wrote: > To solve the problem I want to solve, we have three orthogonal > possibilities: > > 1. implement shared row locking using the ideas outlined in the mail > starting this thread (pg_clog-like seems to be the winner, details TBD). > > 2. implement shared lock table spill-to-disk mechanism. > > 3. implement lock escalation. > > > Some people seems to think 3 is better than 2. What do they think of 1? > > > Some facts: > > - DB2 implements 3 and some people have problems with deadlocks. FWIW, I have seen DB2 deadlock on a single row update statement in a database with no one else connected. It was an issue with how they were implementing repeatable read concurrency. What this indicates to me is that they've got some 'issues' with their locking mechanisms, and that #3 shouldn't be thrown out just because DB2 doesn't do it right. AFAIK Sybase and SQL-server also use lock escalation and I've not heard of issues with it. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(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] plperl: memory usage stacking
Please submit as a context diff (made with diff -c) and send it to the
-patches list, rather than -hackers.
cheers
andrew
[EMAIL PROTECTED] wrote:
In plperl.c, there are some memory leaks, fixed in the following diff file.
Becuase Perl_eval_pv doesnot release stack, repitition of calling
'plperl_trigger_build_args' makes stack growing up.
The next simple example exhaust memory:(using perl v5.8.0)
while (1) {
AV *av = newAV();
av_undef(av);
}.
I cannot understand the behavior of AV, so, plperl_get_keys, plperl_get_key, and
plperl_get_elem are deleted, and simply implemented by hv_iter* inlined in the
loop.
Tetsuya.
diff plperl.c plperl.c.org
236,237c236
< SV *rv = NULL;
< SV *ret = NULL;
---
SV *rv;
239d237
< char *s;
305,309c303
< ENTER;
< SAVETMPS;
< ret = newSVsv (perl_eval_pv(SvPV(rv, PL_na), TRUE));
< FREETMPS;
< LEAVE;
---
rv = perl_eval_pv(SvPV(rv, PL_na), TRUE);
312d305
< SvREFCNT_dec(rv);
314c307
< return ret;
---
return rv;
320a314,316
char *key;
I32 klen;
SV *val;
325,326c321
< hv_iterinit(hv);
< while (hv_iternext(hv)){
---
while (val = hv_iternextsv(hv, (char**)&key, &klen)){
332a328,371
static
AV*
plperl_get_keys(HV* hv)
{
AV *ret;
SV **svp;
int key_count;
SV *val;
char *key;
I32 klen;
key_count = 0;
ret = newAV();
while (val = hv_iternextsv(hv, (char**)&key, &klen)){
av_store(ret, key_count, eval_pv(key, TRUE));
key_count++;
}
return ret;
}
static
char*
plperl_get_key(AV* keys, int index)
{
SV **svp;
int av_len;
av_len = av_len(keys)+1;
if (index < av_len) svp = av_fetch(keys, index, FALSE); else return
NULL;
return SvPV(*svp, PL_na);
}
static
char*
plperl_get_elem(HV* hash, char* key)
{
SV **svp;
if (hv_exists_ent(hash, eval_pv(key, TRUE), FALSE))
svp = hv_fetch(hash, key, strlen(key), FALSE); else return NULL;
return SvPV(*svp, PL_na);
}
363a403
plkeys = plperl_get_keys(hvNew);
382d421
< hv_iterinit(hvNew);
384a424
char *src;
388,389d427
< I32 retlen;
< SV *sv;
391,396c429,430
< sv = hv_iternextsv(hvNew, &platt, &retlen);
< if (sv == NULL)
< elog(FATAL, "plperl: interpreter is probably corrupted");
< plval = SvPV(sv, PL_na);
< if (plval == NULL)
< elog(FATAL, "plperl: interpreter is probably corrupted");
---
platt = plperl_get_key(plkeys, j);
403a438,440
plval = plperl_get_elem(hvNew, platt);
if (plval == NULL)
elog(FATAL, "plperl: interpreter is probably corrupted");
413,428c450,455
< Oid ti;
<
< ti = SPI_gettypeid(tupdesc, modattrs[j]);
< if ( ( (ti != TEXTOID) && (ti != BPCHAROID) && (ti != VARCHAROID) ) && ( (strlen(plval) == 0) || (strcmp(plval, "(null)") == 0) ) )
< {
< modvalues[j] = (Datum) 0;
< modnulls[j] = 'n';
< }
< else
< {
< modvalues[j] = FunctionCall3(&finfo,
< CStringGetDatum(plval),
< ObjectIdGetDatum(typelem),
< Int32GetDatum(tupdesc->attrs[atti]->atttypmod));
< modnulls[j] = ' ';
< }
---
src = plval;
modvalues[j] = FunctionCall3(&finfo,
CStringGetDatum(src),
ObjectIdGetDatum(typelem),
Int32GetDatum(tupdesc->attrs[atti]->atttypmod));
modnulls[j] = ' ';
877d903
[HACKERS] Interesting performance for function between 7.4.6 and 8.0rc2
Hello,
First some specifics:
OS Linux FC1
1 Gig of Ram
IDE RAID 1 array
AMD 2200 XP
Running both 8.0rc2 and 7.4.6 for testing. The function is a plphp
function. The code for the function is below:
CREATE OR REPLACE FUNCTION get_users(bigint[]) RETURNS SETOF
resolved_users_type AS '
$userData = array();
$temp = array();
foreach ($args[0] as $value) { $temp[] = $value."::bigint"; }
$res = spi_exec_query("SELECT id, realname, email FROM users WHERE
id IN (".implode(",", $temp).")");
if ($res[''processed''] > 0) {
while ($row = spi_fetch_row($res)) {
$userData[] = $row;
}
}
return ($userData) ? $userData : NULL;
' LANGUAGE 'plphp';
Basically we take an array of values and look up a list of users based
on the elements in the array and return the results. There is a static
cast for 7.4.6 sake which may not be required for 8.
Here is a sample dataset for the users table:
id | realname |email
--++--
2 | Jonathan Daugherty | [EMAIL PROTECTED]
4 | Steven1 Klassen| [EMAIL PROTECTED]
8 | Steven1 Klassen| [EMAIL PROTECTED]
9 | Steven1 Klassen| [EMAIL PROTECTED]
12 | Steven1 Klassen| [EMAIL PROTECTED]
56 | Jonathan Daugherty | [EMAIL PROTECTED]
2000 | Steven Klassen | [EMAIL PROTECTED]
23 | Steven1 Klassen| [EMAIL PROTECTED]
690 | Steven Klassen | [EMAIL PROTECTED]
4000 | Steven Klassen | [EMAIL PROTECTED]
1243 | Steven Klassen | [EMAIL PROTECTED]
5 | Steven1 Klassen| [EMAIL PROTECTED]
6 | Steven1 Klassen| [EMAIL PROTECTED]
230 | Steven Klassen | [EMAIL PROTECTED]
330 | Steven Klassen | [EMAIL PROTECTED]
430 | Steven Klassen | [EMAIL PROTECTED]
440 | Steven Klassen | [EMAIL PROTECTED]
550 | Steven Klassen | [EMAIL PROTECTED]
660 | Steven Klassen | [EMAIL PROTECTED]
770 | Steven Klassen | [EMAIL PROTECTED]
Here is the functional_query we are running:
SELECT * FROM
get_users('{2,4,8,9,12,56,2000,23,690,4000,1243,5,6,230,330,430,440,550,660,770}');
Statistics is set to 50 on the id column (which is way to high). It has
about 51000 rows.
8.0 first run: 109.664 ms
8.0 second run: 2.939 ms
7.4.6 first run: 121.416ms
7.4.6 second run: 8.665 ms
So this shows a significant increase in functions performance for second
+ runs and a little difference for first runs. If I enable preload
libraries the numbers are the following:
8.0 first run: 107.689ms
8.0 second run: 2.915 ms
7.4.6 first run: 119.400 ms
7.4.6 second run: 8.629 ms
It doesn't look like preload libraries helps as much as I would like but
every little bit helps. The really good news of course is the amazing
increase in performance from 7.4.6 to 8.0. I assume this is because
we are now keeping statistics for functions.
The changes in 8.0 should help projects like TSearch2 quite a bit.
One thing I did note that is very odd is:
sklassen=# explain analyze select * from
get_users('{2,4,8,9,12,56,2000,23,690,4000,1243,5,6,230,330,430,440,550,660,770}');
QUERY PLAN
--
Function Scan on get_users (cost=0.00..12.50 rows=1000 width=72)
(actual time=2.464..2.488 rows=20 loops=1)
Total runtime: 2.520 ms
SELECT id, realname, email FROM users WHERE id IN
(2::bigint,4::bigint,8::bigint,9::bigint,12::bigint,56::bigint,2000::bigint,23::bigint,690::bigint,4000::bigint,1243::bigint,5::bigint,6::bigint,230::bigint,330::bigint,430::bigint,440::bigint,550::bigint,660::bigint,770::bigint)"
LOG: duration: 2.937 ms
Notice the two durations, they are different but for the exact same run
on the query. Is the duration being calculated between psql and the backend?
Sincerely,
Joshua D. Drake
--
Command Prompt, Inc., home of PostgreSQL Replication, and plPHP.
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL
begin:vcard
fn:Joshua D. Drake
n:Drake;Joshua D.
org:Command Prompt, Inc.
adr:;;PO Box 215;Cascade Locks;Oregon;97014;USA
email;internet:[EMAIL PROTECTED]
title:Consultant
tel;work:503-667-4564
tel;fax:503-210-0334
note:Command Prompt, Inc. is the largest and oldest US based commercial PostgreSQL support provider. We provide the only commercially viable integrated PostgreSQL replication solution, but also custom programming, and support. We authored the book Practical PostgreSQL, the procedural language plPHP, and adding trigger capability to plPerl.
x-mozilla-html:FALSE
url:http://www.commandprompt.com/
version:2.1
end:vcard
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[HACKERS] plperl: memory usage stacking
In plperl.c, there are some memory leaks, fixed in the following diff file.
Becuase Perl_eval_pv doesnot release stack, repitition of calling
'plperl_trigger_build_args' makes stack growing up.
The next simple example exhaust memory:(using perl v5.8.0)
while (1) {
AV *av = newAV();
av_undef(av);
}.
I cannot understand the behavior of AV, so, plperl_get_keys, plperl_get_key, and
plperl_get_elem are deleted, and simply implemented by hv_iter* inlined in the
loop.
Tetsuya.
diff plperl.c plperl.c.org
236,237c236
< SV *rv = NULL;
< SV *ret = NULL;
---
> SV *rv;
239d237
< char *s;
305,309c303
< ENTER;
< SAVETMPS;
< ret = newSVsv (perl_eval_pv(SvPV(rv, PL_na), TRUE));
< FREETMPS;
< LEAVE;
---
> rv = perl_eval_pv(SvPV(rv, PL_na), TRUE);
312d305
< SvREFCNT_dec(rv);
314c307
< return ret;
---
> return rv;
320a314,316
> char *key;
> I32 klen;
> SV *val;
325,326c321
< hv_iterinit(hv);
< while (hv_iternext(hv)){
---
> while (val = hv_iternextsv(hv, (char**)&key, &klen)){
332a328,371
> static
> AV*
> plperl_get_keys(HV* hv)
> {
> AV *ret;
> SV **svp;
> int key_count;
> SV *val;
> char *key;
> I32 klen;
> key_count = 0;
> ret = newAV();
>
> while (val = hv_iternextsv(hv, (char**)&key, &klen)){
> av_store(ret, key_count, eval_pv(key, TRUE));
> key_count++;
> }
>
> return ret;
> }
>
> static
> char*
> plperl_get_key(AV* keys, int index)
> {
> SV **svp;
> int av_len;
>
> av_len = av_len(keys)+1;
> if (index < av_len) svp = av_fetch(keys, index, FALSE); else return
> NULL;
> return SvPV(*svp, PL_na);
> }
>
> static
> char*
> plperl_get_elem(HV* hash, char* key)
> {
> SV **svp;
> if (hv_exists_ent(hash, eval_pv(key, TRUE), FALSE))
> svp = hv_fetch(hash, key, strlen(key), FALSE); else return NULL;
>
> return SvPV(*svp, PL_na);
> }
>
363a403
> plkeys = plperl_get_keys(hvNew);
382d421
< hv_iterinit(hvNew);
384a424
> char *src;
388,389d427
< I32 retlen;
< SV *sv;
391,396c429,430
< sv = hv_iternextsv(hvNew, &platt, &retlen);
< if (sv == NULL)
< elog(FATAL, "plperl: interpreter is probably
corrupted");
< plval = SvPV(sv, PL_na);
< if (plval == NULL)
< elog(FATAL, "plperl: interpreter is probably
corrupted");
---
>
> platt = plperl_get_key(plkeys, j);
403a438,440
> plval = plperl_get_elem(hvNew, platt);
> if (plval == NULL)
> elog(FATAL, "plperl: interpreter is probably
> corrupted");
413,428c450,455
< Oid ti;
<
< ti = SPI_gettypeid(tupdesc, modattrs[j]);
< if ( ( (ti != TEXTOID) && (ti != BPCHAROID) && (ti !=
VARCHAROID) ) && ( (strlen(plval) == 0) || (strcmp(plval, "(null)") == 0) ) )
< {
< modvalues[j] = (Datum) 0;
< modnulls[j] = 'n';
< }
< else
< {
< modvalues[j] = FunctionCall3(&finfo,
<
CStringGetDatum(plval),
<
ObjectIdGetDatum(typelem),
<
Int32GetDatum(tupdesc->attrs[atti]->atttypmod));
< modnulls[j] = ' ';
< }
---
> src = plval;
> modvalues[j] = FunctionCall3(&finfo,
>
> CStringGetDatum(src),
>
> ObjectIdGetDatum(typelem),
>
> Int32GetDatum(tupdesc->attrs[atti]->atttypmod));
> modnulls[j] = ' ';
877d903
< SvREFCNT_dec(svTD);
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] port report: [FAILURE] FreeBSD 6, Intel icc7 SUCESS
On December 21, 2004 10:58 am, Tom Lane wrote: > Darcy Buskermolen <[EMAIL PROTECTED]> writes: > > So it looks like icc dosn't like -export-dynamic > > Patch applied, let us know how it goes ... Buildfarm member herring now passes all checks and returns a green light. > > regards, tom lane -- Darcy Buskermolen Wavefire Technologies Corp. ph: 250.717.0200 fx: 250.763.1759 http://www.wavefire.com ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] pg_autovacuum w/ dbt2
Mark Wong wrote: After all this time I finally got around to vacuuming the database with dbt2 with pg_autovacuum. :) http://www.osdl.org/projects/dbt2dev/results/dev4-010/215/ Thanks! Doesn't look so good though, probably because I'm not using optimal settings with pg_autovacuum. So far I have only tried the default settings (running without any arguments, except -D). I don't know what you mean by "Not Good" since I don't have graphs from a similar test without pg_autovacuum handy. Do you have a link to such a test? As for better pg_autovacuum settings, It appears that the little performance dips are happening about once every 5 minutes, which if I remember correctly is the default sleep time. You might try playing with the lazy vacuum settings to see if that smooths out the curve. Beyond that all you can do is play with the thresholds to see if there is a better sweet spot than the defaults (which by the way I have no confidence in, they were just conservative guesses) The only thing that's peculiar is a number of unexpected rollbacks across all of the transactions. I suspect it was something to do with these messages coming from pg_autovacuum: [2004-12-20 15:48:18 PST] ERROR: Can not refresh statistics information from the database dbt2. [2004-12-20 15:48:18 PST] The error is [ERROR: failed to re-find parent key in "pk_district" ] Not sure what this is all about, but if you turn up the debug level to 4 or greater (pg_autovacuum -d4), pg_autovacuum will log the query that is causing the problems, that would be helpful output to have. This is with 8.0rc1. I can get rc2 installed since it just came out. So let me know what I can try and what not. I don't think anything has changed for pg_autovacuum between rc1 and rc2. thanks again for the good work!!! ---(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] cant execute yyparse() within postgresql
Datum mylanguage_handler(PG_FUNCTION_ARGS){
if (CALLED_AS_TRIGGER(fcinfo))
/*do nothing
else{
char *proc_source;
Datum prosrcdatum;
boolisnull;
//get the oid of the function
Oid funcOid = fcinfo->flinfo->fn_oid;
HeapTuple procTup =
SearchSysCache(PROCOID,ObjectIdGetDatum(funcOid),0, 0,
0);
//get the attribute that holds the function's source
prosrcdatum = SysCacheGetAttr(PROCOID,
procTup,Anum_pg_proc_prosrc, &isnull);
//convert prosrcdatum to C style string
proc_source =
DatumGetCString(DirectFunctionCall1(textout,
prosrcdatum));
if (isnull)
elog(ERROR, "null prosrc");
else
{
elog(INFO,"\n Invoking parser \n");
/*the problem area*/
yyparse();
elog(INFO,"\n parser invoked \n");
}
}//end of function
i receive the following error
"server closed the connection unexpectedly
This probably means the server terminated
abnormally before or while processing the request.
The connection to the server was lost. Attempting
reset: Failed."
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
---(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] pg_autovacuum w/ dbt2
Mark Wong <[EMAIL PROTECTED]> writes: > [2004-12-20 15:48:18 PST] The error is [ERROR: failed to re-find > parent key in "pk_district" > ] Yikes. Is this reproducible? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] cant execute yyparse() within postgresql
Sibtay Abbas <[EMAIL PROTECTED]> writes: > Peter's attitude was rude and i complain against it > and i ask the mailing list maintainers to follow up on > this. You would do better to take his advice and provide the needed context. Personally I had ignored your first message as unanswerable... regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] cant execute yyparse() within postgresql
On Tue, 21 Dec 2004 10:50:25 -0800 (PST) Sibtay Abbas <[EMAIL PROTECTED]> wrote: > i never happen to read this rule when i signed up to > this mailing list...so if you ve made up a rule by > urself for this mailing list just do us a favor by > officially announcing it > > i think anyone who has knowledge of adding new > languages to postgresql will understand what pl call > handler means. > > i ve been getting alot of help from this mailing list > and i thank all the ppl who helped me. > > Peter's attitude was rude and i complain against it > and i ask the mailing list maintainers to follow up on > this. I don't believe Peter was trying to be rude. And while it isn't a "rule" per se, the more information you can provide in a question the more you increase your chances of someone having an answer for you. Something along the lines of "I'm trying to add a new language to postgresql and I need to be able to X from within Y" etc, etc, etc. will go a lot futher than "I can't X from within Y". - Frank Wiles <[EMAIL PROTECTED]> http://www.wiles.org - ---(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] port report: [FAILURE] FreeBSD 6, Intel icc7
Darcy Buskermolen <[EMAIL PROTECTED]> writes: > So it looks like icc dosn't like -export-dynamic Patch applied, let us know how it goes ... 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] cant execute yyparse() within postgresql
i am sorry if i hurt your ego, by asking this question. And i mentioned flex mistakenly...actually by flex i meant yacc (i am using lex & yacc). yyparse is generated by yacc..which i want to invoke. " >Second, you can't just ask > random, highly specific > questions out of the blue and expect someone to >have > an answer for you. " i never happen to read this rule when i signed up to this mailing list...so if you ve made up a rule by urself for this mailing list just do us a favor by officially announcing it i think anyone who has knowledge of adding new languages to postgresql will understand what pl call handler means. i ve been getting alot of help from this mailing list and i thank all the ppl who helped me. Peter's attitude was rude and i complain against it and i ask the mailing list maintainers to follow up on this. Peter if you dont understand someone's question the nice way is to ask him the details instead of trying to insult him. thank you --- Peter Eisentraut <[EMAIL PROTECTED]> wrote: > Sibtay Abbas wrote: > > i am not able to execute the yyparse() function > > generated by flex from within the pl call handler > > function. > > First of all, flex does not generate a yyparse() > function. At most it > generates yylex(). Second, you can't just ask > random, highly specific > questions out of the blue and expect someone to have > an answer for you. > Why are you calling yyparser(), what happens when > you do, what PL > handlers are you talking about, etc., would be some > of the required > context information. > > -- > Peter Eisentraut > http://developer.postgresql.org/~petere/ > __ Do you Yahoo!? Take Yahoo! Mail with you! Get it on your mobile phone. http://mobile.yahoo.com/maildemo ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] port report: [FAILURE] FreeBSD 6, Intel icc7
Peter Eisentraut <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >>> So it looks like icc dosn't like -export-dynamic >> >> Looks like it doesn't like -R either. You need to determine the >> correct alternative spelling of those switches for us, and then we >> can put a conditional into Makefile.freebsd for icc. > Considering that these are all really linker options, adding -Wl, in > each case should do. Various compilers are known to let various linker > options slip by, but using -Wl is always correct. I was just going to ask whether we needed to conditionalize it or not. I will add -Wl, to 'em all. The build farm will let us know soon enough if that's bad ;-) regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] RC2 intermittent errors
Tom Lane wrote: Bruce Momjian <[EMAIL PROTECTED]> writes: Tom Lane wrote: Argh! I put a GetTransactionSnapshot() call into exec_eval_simple_expr, but I forgot CommandCounterIncrement(). Wish I could say it was a copy- and-paste mistake, but it was pure stupidity... Can we continue with RC2 or do we need an RC3? It's a one-liner change (assuming that my theory is right, which I won't know for a little bit because I had just make distclean'd in order to verify my tree against the RC2 tarball). I don't think we should push an RC3 just for this. Wait a few days and see what else turns up ... Did you updated the CVS ? Regards Gaetano Mendola ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] cant execute yyparse() within postgresql
Sibtay Abbas wrote: > i am not able to execute the yyparse() function > generated by flex from within the pl call handler > function. First of all, flex does not generate a yyparse() function. At most it generates yylex(). Second, you can't just ask random, highly specific questions out of the blue and expect someone to have an answer for you. Why are you calling yyparser(), what happens when you do, what PL handlers are you talking about, etc., would be some of the required context information. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(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] port report: [FAILURE] FreeBSD 6, Intel icc7
Tom Lane wrote: > > So it looks like icc dosn't like -export-dynamic > > Looks like it doesn't like -R either. You need to determine the > correct alternative spelling of those switches for us, and then we > can put a conditional into Makefile.freebsd for icc. Considering that these are all really linker options, adding -Wl, in each case should do. Various compilers are known to let various linker options slip by, but using -Wl is always correct. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] port report: [FAILURE] FreeBSD 6, Intel icc7
On December 21, 2004 09:50 am, Tom Lane wrote: > Darcy Buskermolen <[EMAIL PROTECTED]> writes: > > On December 16, 2004 04:29 pm, Tom Lane wrote: > >> Hmm. I see in Makefile.freebsd: > >> > >> ifdef ELF_SYSTEM > >> export_dynamic = -export-dynamic > >> rpath = -R$(rpathdir) > >> shlib_symbolic = -Wl,-Bsymbolic -lc > >> endif > > > > Ok we are making further headway: > > > > icc -O -L../../src/port -R/usr/local/pgsql/lib -export-dynamic > > access/SUBSYS.o bootstrap/SUBSYS.o catalog/SUBSYS.o parser/SUBSYS.o > > commands/SUBSYS.o executor > > /SUBSYS.o lib/SUBSYS.o libpq/SUBSYS.o main/SUBSYS.o nodes/SUBSYS.o > > optimizer/SUBSYS.o port/SUBSYS.o postmaster/SUBSYS.o regex/SUBSYS.o > > rewrite/SUBSYS.o storag > > e/SUBSYS.o tcop/SUBSYS.o utils/SUBSYS.o ../../src/timezone/SUBSYS.o > > -lpgport_srv -lz -lreadline -lcrypt -lcompat -lm -lutil -o postgres > > > > iccbin: Command line warning: ignoring unknown option > > '-R/usr/local/pgsql/lib' > > /usr/local/intel/compiler70/ia32/bin/ldwrapper/ld: warning: cannot find > > entry symbol xport-dynamic; defaulting to 0804acc0 > > > > So it looks like icc dosn't like -export-dynamic > > Looks like it doesn't like -R either. You need to determine the correct > alternative spelling of those switches for us, and then we can put a > conditional into Makefile.freebsd for icc. -Wl,-export-dynamic looks to do the job > > Is icc available on any other platforms besides FreeBSD? Yes icc is available for other platforms, most notably linux and MS windows. > > regards, tom lane -- Darcy Buskermolen Wavefire Technologies Corp. ph: 250.717.0200 fx: 250.763.1759 http://www.wavefire.com ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] cant execute yyparse() within postgresql
hello i am not able to execute the yyparse() function generated by flex from within the pl call handler function. does any one knows the reason for this failure? and how can it be rectified thank you __ Do you Yahoo!? Send a seasonal email greeting and help others. Do good. http://celebrity.mail.yahoo.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] port report: [FAILURE] FreeBSD 6, Intel icc7
Darcy Buskermolen <[EMAIL PROTECTED]> writes: > On December 16, 2004 04:29 pm, Tom Lane wrote: >> Hmm. I see in Makefile.freebsd: >> >> ifdef ELF_SYSTEM >> export_dynamic = -export-dynamic >> rpath = -R$(rpathdir) >> shlib_symbolic = -Wl,-Bsymbolic -lc >> endif > Ok we are making further headway: > icc -O -L../../src/port -R/usr/local/pgsql/lib -export-dynamic > access/SUBSYS.o bootstrap/SUBSYS.o catalog/SUBSYS.o parser/SUBSYS.o > commands/SUBSYS.o executor > /SUBSYS.o lib/SUBSYS.o libpq/SUBSYS.o main/SUBSYS.o nodes/SUBSYS.o > optimizer/SUBSYS.o port/SUBSYS.o postmaster/SUBSYS.o regex/SUBSYS.o > rewrite/SUBSYS.o storag > e/SUBSYS.o tcop/SUBSYS.o utils/SUBSYS.o ../../src/timezone/SUBSYS.o > -lpgport_srv -lz -lreadline -lcrypt -lcompat -lm -lutil -o postgres > iccbin: Command line warning: ignoring unknown option '-R/usr/local/pgsql/lib' > /usr/local/intel/compiler70/ia32/bin/ldwrapper/ld: warning: cannot find entry > symbol xport-dynamic; defaulting to 0804acc0 > So it looks like icc dosn't like -export-dynamic Looks like it doesn't like -R either. You need to determine the correct alternative spelling of those switches for us, and then we can put a conditional into Makefile.freebsd for icc. Is icc available on any other platforms besides FreeBSD? regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] pg_autovacuum w/ dbt2
After all this time I finally got around to vacuuming the database with dbt2 with pg_autovacuum. :) http://www.osdl.org/projects/dbt2dev/results/dev4-010/215/ Doesn't look so good though, probably because I'm not using optimal settings with pg_autovacuum. So far I have only tried the default settings (running without any arguments, except -D). The only thing that's peculiar is a number of unexpected rollbacks across all of the transactions. I suspect it was something to do with these messages coming from pg_autovacuum: [2004-12-20 15:48:18 PST] ERROR: Can not refresh statistics information from the database dbt2. [2004-12-20 15:48:18 PST] The error is [ERROR: failed to re-find parent key in "pk_district" ] This is with 8.0rc1. I can get rc2 installed since it just came out. So let me know what I can try and what not. Mark ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] port report: [FAILURE] FreeBSD 6, Intel icc7
On December 16, 2004 04:29 pm, Tom Lane wrote: > Darcy Buskermolen <[EMAIL PROTECTED]> writes: > > On December 16, 2004 12:37 pm, Tom Lane wrote: > > I think you are in need of the local equivalent to GNU ld's -E or > > --export-dynamic switch, ie, make sure that all global symbols within > > the backend will be available to dynamically loaded libraries. > > > > xild just gets envoked as a wrapper to gnu ld by the looks of it. > > Hmm. I see in Makefile.freebsd: > > ifdef ELF_SYSTEM > export_dynamic = -export-dynamic > rpath = -R$(rpathdir) > shlib_symbolic = -Wl,-Bsymbolic -lc > endif > > Perhaps ELF_SYSTEM isn't getting defined? Or maybe it's being picky > about seeing --export-dynamic instead of -export-dynamic ? Or you need > to spell it like "-Wl,-E" to get it past icc and into the linker? > You did not show the build log, but take a look at how the postgres > executable is getting linked to see if -export-dynamic is getting in > there or not. Ok we are making further headway: gmake[3]: Leaving directory `/buildfarm/pg-buildfarm/HEAD/pgsql.639/src/timezone' icc -O -L../../src/port -R/usr/local/pgsql/lib -export-dynamic access/SUBSYS.o bootstrap/SUBSYS.o catalog/SUBSYS.o parser/SUBSYS.o commands/SUBSYS.o executor /SUBSYS.o lib/SUBSYS.o libpq/SUBSYS.o main/SUBSYS.o nodes/SUBSYS.o optimizer/SUBSYS.o port/SUBSYS.o postmaster/SUBSYS.o regex/SUBSYS.o rewrite/SUBSYS.o storag e/SUBSYS.o tcop/SUBSYS.o utils/SUBSYS.o ../../src/timezone/SUBSYS.o -lpgport_srv -lz -lreadline -lcrypt -lcompat -lm -lutil -o postgres iccbin: Command line warning: ignoring unknown option '-R/usr/local/pgsql/lib' /usr/local/intel/compiler70/ia32/bin/ldwrapper/ld: warning: cannot find entry symbol xport-dynamic; defaulting to 0804acc0 gmake[2]: Leaving directory `/buildfarm/pg-buildfarm/HEAD/pgsql.639/src/backend' gmake -C backend/utils/mb/conversion_procs all So it looks like icc dosn't like -export-dynamic > > regards, tom lane > > ---(end of broadcast)--- > TIP 8: explain analyze is your friend -- Darcy Buskermolen Wavefire Technologies Corp. ph: 250.717.0200 fx: 250.763.1759 http://www.wavefire.com ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] RC2 intermittent errors
Bruce Momjian <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> Argh! I put a GetTransactionSnapshot() call into exec_eval_simple_expr, >> but I forgot CommandCounterIncrement(). Wish I could say it was a copy- >> and-paste mistake, but it was pure stupidity... > Can we continue with RC2 or do we need an RC3? It's a one-liner change (assuming that my theory is right, which I won't know for a little bit because I had just make distclean'd in order to verify my tree against the RC2 tarball). I don't think we should push an RC3 just for this. Wait a few days and see what else turns up ... regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] RC2 intermittent errors
Tom Lane wrote: > Gaetano Mendola <[EMAIL PROTECTED]> writes: > > I'm testing now RC2 against our application and I'm experiencing > > intermittent errors. I isolated this test: > > Argh! I put a GetTransactionSnapshot() call into exec_eval_simple_expr, > but I forgot CommandCounterIncrement(). Wish I could say it was a copy- > and-paste mistake, but it was pure stupidity... Can we continue with RC2 or do we need an RC3? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 4: Don't 'kill -9' the postmaster
Re: [HACKERS] RC2 intermittent errors
Gaetano Mendola <[EMAIL PROTECTED]> writes: > I'm testing now RC2 against our application and I'm experiencing > intermittent errors. I isolated this test: Argh! I put a GetTransactionSnapshot() call into exec_eval_simple_expr, but I forgot CommandCounterIncrement(). Wish I could say it was a copy- and-paste mistake, but it was pure stupidity... regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] RC2 intermittent errors
Hi all,
I'm testing now RC2 against our application and I'm experiencing
intermittent errors. I isolated this test:
CREATE TABLE users (
id_login SERIAL PRIMARY KEY,
login TEXT
);
CREATE OR REPLACE FUNCTION sp_id_user ( TEXT )
RETURNS INTEGER AS $$
DECLARE
a_login ALIAS FOR $1;
my_id INTEGER;
BEGIN
SELECT id_login INTO my_id
FROM users
WHERE login = a_login;
RETURN COALESCE(my_id, -1 );
END;
$$ LANGUAGE 'plpgsql'
STABLE;
CREATE OR REPLACE FUNCTION sp_test ( TEXT )
RETURNS INTEGER AS $$
DECLARE
a_login ALIAS FOR $1;
my_id INTEGER;
BEGIN
my_id = sp_id_user( a_login );
RAISE NOTICE 'ID> %', my_id;
insert into users (login) values ( a_login );
my_id = sp_id_user( a_login );
RAISE NOTICE 'ID> %', my_id;
RETURN 0;
END;
$$ LANGUAGE 'plpgsql';
select sp_test('test1');
select sp_test('test2');
The call of the two above functions shall show:
ID> -1
ID> 1
ID> -1
ID> 2
instead I have:
test=# select sp_test('test1');
NOTICE: ID> -1
NOTICE: ID> 1
sp_test
-
0
(1 row)
test=# select sp_test('test2');
NOTICE: ID> -1
NOTICE: ID> -1
sp_test
-
0
(1 row)
some times I get:
test=# select sp_test('test1');
NOTICE: ID> -1
NOTICE: ID> -1
sp_test
-
0
(1 row)
test=# select sp_test('test2');
NOTICE: ID> -1
NOTICE: ID> -1
sp_test
-
0
(1 row)
Regards
Gaetano Mendola
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Bgwriter behavior
Bruce Momjian <[EMAIL PROTECTED]> writes: > First, we remove the GUC bgwriter_maxpages because I don't see a good > way to set a default for that. A default value needs to be based on a > percentage of the full buffer cache size. This is nonsense. The admin knows what he set shared_buffers to, and so maxpages and percent of shared buffers are not really distinct ways of specifying things. The cases that make a percent spec useful are if (a) it is a percent of a non-constant number (eg, percent of total dirty pages as in the current code), or (b) it is defined in a way that lets it limit the amount of scanning work done (which it isn't useful for in the current code). But a maxpages spec is useful for (b) too. More to the point, maxpages is useful to set a hard limit on the amount of I/O generated by the bgwriter, and I think people will want to be able to do that. > Now, to control the bgwriter frequency we multiply the percent of the > list it had to span by the bgwriter_delay value to determine when to run > bgwriter next. I'm less than enthused about this. The idea of the bgwriter is to trickle out writes in a way that doesn't affect overall performance too much. Not to write everything in sight at any cost. I like the hybrid "keep the bottom of the ARC list clean, plus do a slow clock scan on the main buffer array" approach better. I can see that that directly impacts both of the goals that the bgwriter has. I don't see how a variable I/O rate really improves life on either score; it just makes things harder to predict. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] RC2 and open issues
Richard Huxton <[EMAIL PROTECTED]> writes: > However, one thing you can say is that if block B hasn't been written to > since you last checked, then any blocks older than that haven't been > written to either. [ itch... ] Can you? I don't recall exactly when a block gets pushed up the ARC list during a ReadBuffer/WriteBuffer cycle, but at the very least I'd have to say that this assumption is vulnerable to race conditions. Also, the cntxDirty mechanism allows a block to be dirtied without changing the ARC state at all. I am not very clear on whether Vadim added that mechanism just for performance or because there were fundamental deadlock issues without it; but in either case we'd have to think long and hard about taking it out for the bgwriter's benefit. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Bgwriter behavior
Tom Lane wrote: > Gavin Sherry <[EMAIL PROTECTED]> writes: > > I was also thinking of benchmarking the effect of changing the algorithm > > in StrategyDirtyBufferList(): currently, for each iteration of the loop we > > read a buffer from each of T1 and T2. I was wondering what effect reading > > T1 first then T2 and vice versa would have on performance. > > Looking at StrategyGetBuffer, it definitely seems like a good idea to > try to keep the bottom end of both T1 and T2 lists clean. But we should > work at T1 a bit harder. > > The insight I take away from today's discussion is that there are two > separate goals here: try to keep backends that acquire a buffer via > StrategyGetBuffer from being fed a dirty buffer they have to write, > and try to keep the next upcoming checkpoint from having too much work > to do. Those are both laudable goals but I hadn't really seen before > that they may require different strategies to achieve. I'm liking the > idea that bgwriter should alternate between doing writes in pursuit of > the one goal and doing writes in pursuit of the other. It seems we have added a new limitation to bgwriter by not doing a full scan. With a full scan we could easily grab the first X pages starting from the end of the LRU list and write them. By not scanning the full list we are opening the possibility of not seeing some of the front-most LRU dirty pages. And the full scan was removed so we can run bgwriter more frequently, but we might end up with other problems. I have a new proposal. The idea is to cause bgwriter to increase its frequency based on how quickly it finds dirty pages. First, we remove the GUC bgwriter_maxpages because I don't see a good way to set a default for that. A default value needs to be based on a percentage of the full buffer cache size. Second, we make bgwriter_percent cause the bgwriter to stop its scan once it has found a number of dirty buffers that matches X% of the buffer cache size. So, if it is set to 5%, the bgwriter scan stops once it find enough dirty buffers to equal 5% of the buffer cache size. Bgwriter continues to scan starting from the end of the LRU list, just like it does now. Now, to control the bgwriter frequency we multiply the percent of the list it had to span by the bgwriter_delay value to determine when to run bgwriter next. For example, if you find enough dirty pages by looking at only 10% of the buffer cache you multiple 10% (0.10) * bgwriter_delay and that is when you run next. If you have to scan 50%, bgwriter runs next at 50% (0.50) * bgwriter_delay, and if it has to scan the entire list it is 100% (1.00) * bgwriter_delay. What this does is to cause bgwriter to run more frequently when there are a lot of dirty buffers on the end of the LRU _and_ when the bgwriter scan will be quick. When there are few writes, bgwriter will run less frequently but will write dirty buffers nearer to the head of the LRU. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] RC2 and open issues
[EMAIL PROTECTED] wrote: Tom Lane <[EMAIL PROTECTED]> wrote on 21.12.2004, 05:05:36: Bruce Momjian writes: I am confused. If we change the percentage to be X% of the entire buffer cache, and we set it to 1%, and we exit when either the dirty pages or % are reached, don't we end up just scanning the first 1% of the cache over and over again? Exactly. But 1% would be uselessly small with this definition. Offhand I'd think something like 50% might be a starting point; maybe even more. What that says is that a page isn't a candidate to be written out by the bgwriter until it's fallen halfway down the LRU list. I see the buffer list as a conveyor belt that carries unneeded blocks away from the MRU. Cleaning near the LRU (I agree: How near?) should be all that is sufficient to keep the list clean. Cleaning the first 1% "over and over again" makes it sound like it is the same list of blocks that are being cleaned. It may be the same linked list data structure, but that is dynamically changing to contain completely different blocks from the last time you looked. However, one thing you can say is that if block B hasn't been written to since you last checked, then any blocks older than that haven't been written to either. Of course, the problem is in finding block B again without re-scanning from the LRU end. Is there any non-intrusive way we could add a "bookmark" into the conveyer-belt? (mixing my metaphors again :-) Any blocks written to would move up the cache, effectively moving the bookmark lower. Enough activity would cause the bookmark to drop off the end. If that isn't the case though, we know we can safely skip any blocks older than the bookmark. -- Richard Huxton Archonet Ltd ---(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] rc1 packaged ...
Marc G. Fournier wrote: > 'k, I'm about to screw up rc2 for this too ... FreeBSD ports is > 'stuck' at 1.78 ... Well, file a bug to get it updated? > just went to > http://sourceforge.net/projects/docbook, and there are two '1.79's > ... do both need to be installed, or just one of them? The second one is the documentation. You don't need that unless you plan to read it. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(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] RC2 and open issues
> If we don't start where we left off, I am thinking if you do a lot of > writes then do nothing, the next checkpoint would be huge because a lot > of the LRU will be dirty because the bgwriter never got to it. I think the problem is, that we don't see wether a "read hot" page is also "write hot". We would want to write dirty "read hot" pages, but not "write hot" pages. It does not make sense to write a "write hot" page since it will be dirty again when the checkpoint comes. 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] Heads up: RC2 this evening
Andrew Dunstan wrote: > I have not been able to build Cygwin with pltcl, and neither has > anyone else to the best of my knowledge. This problem has existed for years. See the pgsql-cygwin(?) archives for details. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: Re: [HACKERS] RC2 and open issues
Tom Lane <[EMAIL PROTECTED]> wrote on 21.12.2004, 05:05:36: > Bruce Momjian writes: > > I am confused. If we change the percentage to be X% of the entire > > buffer cache, and we set it to 1%, and we exit when either the dirty > > pages or % are reached, don't we end up just scanning the first 1% of > > the cache over and over again? > > Exactly. But 1% would be uselessly small with this definition. Offhand > I'd think something like 50% might be a starting point; maybe even more. > What that says is that a page isn't a candidate to be written out by the > bgwriter until it's fallen halfway down the LRU list. > I see the buffer list as a conveyor belt that carries unneeded blocks away from the MRU. Cleaning near the LRU (I agree: How near?) should be all that is sufficient to keep the list clean. Cleaning the first 1% "over and over again" makes it sound like it is the same list of blocks that are being cleaned. It may be the same linked list data structure, but that is dynamically changing to contain completely different blocks from the last time you looked. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: Re: [HACKERS] RC2 and open issues
Tom Lane <[EMAIL PROTECTED]> wrote on 21.12.2004, 07:32:52: > Gavin Sherry writes: > > I was also thinking of benchmarking the effect of changing the algorithm "changing the algorithm" is a phrase that sends shivers up my spine. My own preference is towards some change, but as minimal as possible. > > in StrategyDirtyBufferList(): currently, for each iteration of the loop we > > read a buffer from each of T1 and T2. I was wondering what effect reading > > T1 first then T2 and vice versa would have on performance. > > Looking at StrategyGetBuffer, it definitely seems like a good idea to > try to keep the bottom end of both T1 and T2 lists clean. But we should > work at T1 a bit harder. > > The insight I take away from today's discussion is that there are two > separate goals here: try to keep backends that acquire a buffer via > StrategyGetBuffer from being fed a dirty buffer they have to write, > and try to keep the next upcoming checkpoint from having too much work > to do. Those are both laudable goals but I hadn't really seen before > that they may require different strategies to achieve. I'm liking the > idea that bgwriter should alternate between doing writes in pursuit of > the one goal and doing writes in pursuit of the other. Agreed: there are two different goals for buffer list management. I like the way the current algorithm searches both T1 and T2 in parallel, since that works no matter how long each list is. Always cleaning one list in preference to the other would not work well since ARC fluctuates. At any point in time, cleaning one list will have more benefit than cleaning the other, but which one is best switches backwards and forwards as ARC fluctuates. Perhaps the best way would be to concentrate on the list that, at this point in time, is the one that needs to be cleanest. I *think* that means we should concentrate on the LRU of the *longest* list, since that is the direction in which ARC is trying to move (I agree that seems counter-intuitive: but a few pairs of eyes should confirm which way round it is) By observation, DBT2 ends up with T2 >> T1, but that is a result of its fairly static nature. i.e. DBT2 would benefit from T2 LRU cleaning. ISTM it would be good to have: 1) very frequent, but small cleaning action on the lists, say every 50ms to avoid backends having to write a buffer 2) less frequent, deeper cleaning actions, to minimise the effect of checkpoints, which could be done every 10th cycle e.g. 500ms (numbers would vary according to workload...) But, like I said: change, but minimal change seems best to me for now. Best Regards, Simon Riggs ---(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] Shared row locking
> In general, I agree with Tom: I haven't seen many programs that use > extended SELECT FOR UPDATE logic. However, the ones I have seen have > been batch style programs written using a whole-table cursor - these > latter ones have been designed for the cursor stability approach. I think if we add shared locks we should by default behave like cursor stability isolation level, that only holds one shared lock for the current cursor row. The semantics are well defined in SQL. If you want repeatable read you need to change isolation level. I know FK checks will need to keep the locks, but I would special case that. Andreas ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
