Re: [HACKERS] [DOCS] Annotated release notes
I included some text about the information schema in this issue of general bits. I also did some documentation of comparison of the changes in the postgresql.conf. Anyone who wants to grab parts of those items in that issue has my permission. I don't have time to re-edit for the release note format. But maybe there is some clarification text you can use. http://cookie.varlena.com:8080/varlena/GeneralBits/48.php elein On Thu, Oct 30, 2003 at 11:59:05PM -0500, Bruce Momjian wrote: OK, I have committed changes to release.sgml so most complex entries have a paragraph describing the change. You can see the result at: http://candle.pha.pa.us/main/writings/pgsql/sgml/release.html#RELEASE-7-4 I need people to check this and help me with the items marked 'bjm'. I am confused about the proper text for those sections. --- Tatsuo Ishii wrote: Tatsuo Ishii wrote: I've been pushing this agenda for a few releases now, but some people have been, er, boycotting it. I think, too, that release notes *must* be written incrementally at the same time that the feature change is made. This is the only way we can get accurate and complete release notes, and the descriptions could even include some context, some motivations, etc. We have release cycles of 10 months, and there is no way we can make sensible release notes by gathering individual commit messages over that period of time. Heck, ECPG has a full Informix compatibility mode and there is no mention of that anywhere, because there was no commit Add Informix mode. I suggest we just do it like the documentation: If you don't document it, it doesn't exist. If you don't write a line for the release notes, it doesn't exist either. I tend to agree it. For every release I and my colleague have been working on creating detailed release notes (of course in Japanese), otherwise we cannot tell people what are changed, added or fixed since there is little info in the official release note. This is painful since we have to dig into the mail archives and cvs commit messages to look for what each item of the official release note actually means. These work take at least 2 to 3 weeks with several people involved. The hardest part is what are fixed. The only useful information seems to be the cvs commit messages, however typical messages are something like see recent discussions in the mail archive for more details. This is not very helpful at least for me. Once I proposed that we add a sequence number to each mail and the commit messages point to the number. This way we could easily trace what are the bug report and what are the actual intention for the fix. For some reason noboy was interested in. Maybe this is due to coulture gap... (In Japan giving a sequence number to each mail in mailing lists is quite common). OK, if Tatsuo and SRA are having problems, I have to address it. I can supply a more detailed list to Tatsuo/SRA, or I can beef up the release notes to contain more information. Seems some in the community would like to have this detail so I might as well do it and have it in the official docs. One idea would be to add a section at the bottom of the release notes that goes into detail on changes listed in the release notes above --- that way, people can still skim the 300-line release notes, and if they want detailed information about the optimizer changes or subtle pg_dump fixes, that will be at the bottom. How does that sound? I can start on this for 7.4 next week. It basically means going through the CVS logs again and pulling out additional details. Sounds good. However this kind of information could become huge and I am afraid it does not suite well in the official docs in the source tree. I think putiing it in somewhere in a web site (maybe http://developer.postgresql.org/?) might be more appropreate. What do you think? -- Tatsuo Ishii -- 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] ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] postmaster startup failure
I ran into this too. Patched the code with Tom's change and it works fine. Thanks again Tom! Richard Schilling On 2003.07.17 11:04 Hannu Krosing wrote: Tom Lane kirjutas N, 17.07.2003 kell 19:49: Ugh. The reason we hadn't seen this happen in the field was that it is a bug I introduced in a patch two months ago :-( 7.3.3 will in fact fail to start up, with the above error, any time the last record of the WAL file ends exactly at a page boundary. I think we're gonna need a quick 7.3.4 ... If you want a source patch for 7.3.3, here it is. Thanks! --- Hannu ---(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 ---(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] static pg_dump
In any case, can't 7.3 pg_dump use the 7.4 libpq? If not then the major version should be bumped. My point being that I would like to actually preserve my cluster settings and stuff when dumping... So it's pg_dump, not libpq, that's important to me. Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] OSDL DBT-2 w/ PostgreSQL 7.3.4 and 7.4beta5
[EMAIL PROTECTED] wrote: Results from 7.4beta5 http://developer.osdl.org/markw/dbt2-pgsql/188/ - metric 1446.01 CPU: P4 / Xeon with 2 hyper-threads, speed 1497.51 MHz (estimated) Counted GLOBAL_POWER_EVENTS events (time during which processor is not stopped) with a unit mask of 0x01 (count cycles when processor is active) count 10 samples %app name symbol name 15369575 9.6780 postgres SearchCatCache 13714258 8.6357 vmlinux .text.lock.signal 10611912 6.6822 vmlinux do_sigaction 4400461 2.7709 vmlinux rm_from_queue 18% cpu time in the kernel signal handlers. What are signals used for by postgres? I've seen the sigalarm to implement timeouts, what else? -- Manfred ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Problems with whitespace-ignoring diff options
Tom Lane [EMAIL PROTECTED] writes: However, if a test fails, it is quite likely that a whitespace-sensitive diff will produce many lines of uninteresting diff, because of psql's habit of justifying column output Perhaps the regression tests should just do \pset format unaligned ? -- greg ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] OSDL DBT-2 w/ PostgreSQL 7.3.4 and 7.4beta5
I've straced
$ pgbench -c 5 -s 6 -t 1000
total 157k syscalls, 70k of them are rt_sigaction(SIGPIPE):
1754 poll([{fd=3, events=POLLOUT|POLLERR, revents=POLLOUT}], 1, -1) = 1
1754 rt_sigaction(SIGPIPE, {SIG_IGN}, {SIG_DFL}, 8) = 0
1754 send(3, \0\0\0%\0\3\0\0user\0postgres\0database\0t..., 37, 0) = 37
1754 rt_sigaction(SIGPIPE, {SIG_DFL}, {SIG_IGN}, 8) = 0
1754 poll([{fd=3, events=POLLIN|POLLERR, revents=POLLIN}], 1, -1) = 1
1754 recv(3, R\0\0\0\10\0\0\0\0S\0\0\0\36client_encoding\0SQ...,
16384, 0) = 169
1754 rt_sigaction(SIGPIPE, {SIG_IGN}, {SIG_DFL}, 8) = 0
1754 send(3, Q\0\0\0\35SET search_path = public\0, 30, 0) = 30
1754 rt_sigaction(SIGPIPE, {SIG_DFL}, {SIG_IGN}, 8) = 0
1754 poll([{fd=3, events=POLLIN|POLLERR, revents=POLLIN}], 1, -1) = 1
1754 recv(3, C\0\0\0\10SET\0Z\0\0\0\5I, 16384, 0) = 15
1754 rt_sigaction(SIGPIPE, {SIG_IGN}, {SIG_DFL}, 8) = 0
and so on. Is that really necessary?
Mark: could you strace your dbt2 app? I guess your app creates a similar
streams of rt_sigaction calls.
--
Manfred
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] OSDL DBT-2 w/ PostgreSQL 7.3.4 and 7.4beta5
Manfred Spraul [EMAIL PROTECTED] writes: Is that really necessary? Unfortunately, yes. libpq can't change the global setting of SIGPIPE without breaking the surrounding application, but we don't want to crash the app if the server connection has disappeared, either. So we have to set the SIGPIPE handler and then restore it around every send(). On some platforms there might be a better way, but this is the only portable way I know about. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Proposal: psql force prompting on notty
I'm the maintainer of sql.el in GNU Emacs. On both the Mac OS X
and the Windows/Cygwin platform we've had reports that psql run
under Emacs does not issue a prompt for commands.
I did a little research and it appears that the cause is that
pset.notty is being set to false because stdin/stdout are not
a tty (pgsql-server/src/bin/psql/startup.c). I'd like to
propose that an option be added to the psql command line to
override the detection of the tty and force the prompting for
commands on stdin.
I believe the attached patch should add the option -I to psql
and force the prompt to appear as if it were an interactive
session regardless of the state of stdin/stdout. I do not
have a configuration available to properly test this however.
Please be sure to include me in any replies since I do not
subscribe to this list. TIA.
-- Michael
__
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com--- pgsql-server/src/bin/psql/startup.c 29 Sep 2003 18:21:33 - 1.80
+++ pgsql-server/src/bin/psql/startup.c 01 Nov 2003 06:10:42 -
@@ -322,6 +322,7 @@
{field-separator, required_argument, NULL, 'F'},
{host, required_argument, NULL, 'h'},
{html, no_argument, NULL, 'H'},
+ {interactive, no_argument, NULL, 'I'},
{list, no_argument, NULL, 'l'},
{no-readline, no_argument, NULL, 'n'},
{output, required_argument, NULL, 'o'},
@@ -352,7 +353,7 @@
memset(options, 0, sizeof *options);
- while ((c = getopt_long(argc, argv,
aAc:d:eEf:F:h:Hlno:p:P:qR:sStT:uU:v:VWxX?,
+ while ((c = getopt_long(argc, argv,
aAc:d:eEf:F:h:HlMno:p:P:qR:sStT:uU:v:VWxX?,
long_options, optindex)) !=
-1)
{
switch (c)
@@ -395,7 +396,10 @@
case 'H':
pset.popt.topt.format = PRINT_HTML;
break;
+ case 'I':
+ pset.notty = 0;
+ break;
case 'l':
options-action = ACT_LIST_DB;
break;
--- pgsql-server/src/bin/psql/help.c02 Oct 2003 06:39:31 - 1.81
+++ pgsql-server/src/bin/psql/help.c01 Nov 2003 06:29:50 -
@@ -103,6 +103,7 @@
puts(_( -a echo all input from script));
puts(_( -e echo commands sent to server));
puts(_( -E display queries that internal commands generate));
+ puts(_( -I force interactive prompting for input));
puts(_( -q run quietly (no messages, only query output)));
puts(_( -o FILENAME send query results to file (or |pipe)));
puts(_( -n disable enhanced command line editing (readline)));
---(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] Vacuum thoughts
I'd rather see us implement a buffer replacement policy that considers both frequency + recency (unlike LRU, which considers only recency). Ideally, that would work automagically. I'm hoping to get a chance to implement ARC[1] during the 7.5 cycle. Actually i've already done some work back in august. I tried to implement ARC in pgsql but failed to due to lack of time. After renewed discussion with Bruce and Jan, i've decided to give it another try. So hopefully w'll have a patch soon from me or Jan. The algorithm itself is very elegant and works automagically. -- Cuong Bui Loki IT Solutions http://www.lokishop.nl Powers your webcommerce! ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Experimental patch for inter-page delay in VACUUM
I tried the Tom Lane's patch on PostgreSQL 7.4-BETA-5 and it works fantastically! Running a few short tests show a significant improvement in responsiveness on my RedHat 9 Linux 2.4-20-8 (IDE 120GB 7200RPM UDMA5). I didn't feel any noticeable delay when vacuum_page_delay is set to 5ms, 10 ms. Vacuum takes 15 to 24 times longer to complete (as expected) but I don't mind at all. Vmstat BI/BO load is reduced by 5 times when vacuum_page_delay = 1ms. Load average reduced significantly also as there are less processes waiting to complete. I find a value of 1ms to 5ms is quite good and will keep system responsive. Going from 10ms to 1ms didn't seem to reduce the total vacuum time by much and I'm not sure why. Any chance we can get this patched into 7.4 permanently? I cannot say how well it would work on a heavy load, but on a light load this patch is highly recommended for 24/7 large DB systems. The database is mostly read-only. There are 133,000 rows and each row is about 2.5kB in size (mostly due to the bytea column holding a binary image). The long row causes system to TOAST the table. I repeatedly ran the following tests while system is idling: Normal operation with no VACUUM === tsdb=# explain analyze select * from table1 where id = '0078997ac809877c1a0d1f76af753608'; QUERY PLAN -- Index Scan using table1_pkey on table1 (cost=0.00..6.01 rows=2 width=344) (actual time=19.030..19.036 rows=1 loops=1) Index Cond: ((id)::text = '0078997ac809877c1a0d1f76af753608'::text) Total runtime: 19.206 ms (3 rows) VACUUM at vacuum_page_delay = 0 === -bash-2.05b$ vmstat 1 procs memory swap io system cpu r b w swpd free buff cache si sobibo incs us sy id 0 1 0 176844 3960 17748 14670400 1408 0 296 556 0 1 99 0 1 0 176844 3960 17748 14626400 1536 0 285 546 0 2 98 tsdb=# explain analyze select * from table1 where id = '00e5ae5f4fddab371f7847f7da65eebb'; QUERY PLAN Index Scan using table1_pkey on table1 (cost=0.00..6.01 rows=2 width=344) (actual time=298.028..298.047 rows=1 loops=1) Index Cond: ((id)::text = '0036edc4a92b6afd41304c6c8b76bc3c'::text) Total runtime: 298.275 ms (3 rows) tsdb=# explain analyze select * from table1 where id = '0046751ac3ec290b9f66ea1d66431923'; QUERY PLAN Index Scan using table1_pkey on table1 (cost=0.00..6.01 rows=2 width=344) (actual time=454.727..454.746 rows=1 loops=1) Index Cond: ((id)::text = '0046751ac3ec290b9f66ea1d66431923'::text) Total runtime: 454.970 ms (3 rows) tsdb=# explain analyze select * from table1 where id = '00a74e6885579a2d50487f5a1dceba22'; QUERY PLAN Index Scan using table1_pkey on table1 (cost=0.00..6.01 rows=2 width=344) (actual time=344.483..344.501 rows=1 loops=1) Index Cond: ((id)::text = '00a74e6885579a2d50487f5a1dceba22'::text) Total runtime: 344.700 ms (3 rows) VACUUM at vacuum_page_delay = 1 === procs memory swap io system cpu r b w swpd free buff cache si sobibo incs us sy id 0 0 0 176840 4292 23700 13741600 384 0 127 302 0 0 100 0 0 0 176840 4220 23700 13711600 512 0 118 286 0 0 100 1 0 0 176840 4220 23700 13665600 384 0 132 303 0 1 99 tsdb=# explain analyze select * from table1 where id = '003d5966f8b9a06e4b0fff9fa8e93be0'; QUERY PLAN -- Index Scan using table1_pkey on table1 (cost=0.00..6.01 rows=2 width=344) (actual time=74.575..74.584 rows=1 loops=1) Index Cond: ((id)::text = '003d5966f8b9a06e4b0fff9fa8e93be0'::text) Total runtime: 74.761 ms (3 rows) tsdb=# explain analyze select * from table1 where id = '00677fe46cd0af3d98564068f34db1cf'; QUERY PLAN
Re: [HACKERS] Proposal: psql force prompting on notty
Michael Mauger writes: I'm the maintainer of sql.el in GNU Emacs. On both the Mac OS X and the Windows/Cygwin platform we've had reports that psql run under Emacs does not issue a prompt for commands. Why can't you make the Emacs session behave like a terminal for psql? -- Peter Eisentraut [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] OSDL DBT-2 w/ PostgreSQL 7.3.4 and 7.4beta5
Manfred Spraul [EMAIL PROTECTED] writes: signal handlers are a process property, not a thread property - that code is broken for multi-threaded apps. Yeah, that's been mentioned before, but I don't see any way around it. What we really want is to turn off SIGPIPE delivery on our socket (only), but AFAIK there is no API to do that. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] OSDL DBT-2 w/ PostgreSQL 7.3.4 and 7.4beta5
Tom Lane wrote: Manfred Spraul [EMAIL PROTECTED] writes: signal handlers are a process property, not a thread property - that code is broken for multi-threaded apps. Yeah, that's been mentioned before, but I don't see any way around it. Do not handle SIGPIPE on multithreaded apps, and ask the caller to do that? The current code doesn't block SIGPIPE reliably, which makes it totally useless (except that it's a debugging nightmare, because triggering it depends on the right timing). What we really want is to turn off SIGPIPE delivery on our socket (only), but AFAIK there is no API to do that. Linux has as MSG_NOSIGNAL flag for send(), but that seems to be Linux specific. -- Manfred ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] OSDL DBT-2 w/ PostgreSQL 7.3.4 and 7.4beta5
On Sat, Nov 01, 2003 at 02:37:21PM +0100, Manfred Spraul wrote: Tom Lane wrote: [EMAIL PROTECTED] writes: 7.4beta5 offers more throughput. One significant difference I see is in the oprofile for the database. For the additional 7% increase in the metric, there are about 32% less ticks in SearchCatCache. Hmm. I have been profiling PG for some years now, and I cannot remember ever seeing a profile in which SearchCatCache topped everything else (the usual suspects for me are palloc/pfree support code). Can you give any explanation why it looks like that? Can your profiling code tell where the hotspot call sites of SearchCatCache are? If I understand the docs correctly, op_to_source -a can do that - the result is annotated assembly, with percentage numbers for each instruction. If the sources were compiled with -g2, even source level annotation is possible. Mark, do you still have the oprofile output? I don't understand why so much time is spent in the kernel signal handlers, i.e. I could use annotated assembly or source of linux/kernel/signal.c. I haven't been saving the raw output, but I will start. I'll try to get some annotated source for the kernel going too. Mark ---(end of broadcast)--- TIP 8: explain analyze is your friend
Avoiding SIGPIPE (was Re: [HACKERS] OSDL DBT-2 w/ PostgreSQL 7.3.4 and 7.4beta5)
Manfred Spraul [EMAIL PROTECTED] writes: Tom Lane wrote: What we really want is to turn off SIGPIPE delivery on our socket (only), but AFAIK there is no API to do that. Linux has as MSG_NOSIGNAL flag for send(), but that seems to be Linux specific. Hmm ... a Linux-specific solution would be better than none at all. A bigger objection is that we couldn't get libssl to use it (AFAIK). The flag really needs to be settable on the socket (eg, via fcntl), not per-send. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] OSDL DBT-2 w/ PostgreSQL 7.3.4 and 7.4beta5
On Sat, Nov 01, 2003 at 07:27:01PM +0100, Manfred Spraul wrote: Tom Lane wrote: Manfred Spraul [EMAIL PROTECTED] writes: Is that really necessary? Unfortunately, yes. libpq can't change the global setting of SIGPIPE without breaking the surrounding application, but we don't want to crash the app if the server connection has disappeared, either. So we have to set the SIGPIPE handler and then restore it around every send(). Ok. Ahm. No, wait. libpq is multi-threaded, right? signal handlers are a process property, not a thread property - that code is broken for multi-threaded apps. At least that's how I understand the opengroup man page, and a quick google confirmed that: http://groups.google.de/groups?selm=353662BF.9D70F63A%40brighttiger.com I haven't found a reliable thread-safe approach yet: My first idea was block with pthread_sigmask, after send check if pending with sigpending, and then delete with sigwait, and restore blocked state. But that breaks if SIGPIPE is blocked and a signal is already pending: there is no way to remove our additional SIGPIPE. I don't see how we can avoid destroying the realtime signal info. Mark: Is your dbt2 testapp multithreaded? I don't see the signal functions near the top in the profiles on the osdl website. Yeah, my dbt2 applications are multithreaded. 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: Avoiding SIGPIPE (was Re: [HACKERS] OSDL DBT-2 w/ PostgreSQL
Tom Lane wrote:
A bigger objection is that we couldn't get libssl to use it (AFAIK).
The flag really needs to be settable on the socket (eg, via fcntl),
not per-send.
It's a per-send flag, it's not possible to force it on with a fcntl :-(
What about an option to skip the sigaction calls for apps that can
handle SIGPIPE? I'm not sure if an option at connect time, or a flag
accessible through a function like PQsetnonblocking() is the better
approach.
Attached is a patch that adds a connstr option, but I don't like it.
--
Manfred
Index: fe-connect.c
===
RCS file: /projects/cvsroot/pgsql-server/src/interfaces/libpq/fe-connect.c,v
retrieving revision 1.260
diff -c -r1.260 fe-connect.c
*** fe-connect.c5 Sep 2003 02:08:36 - 1.260
--- fe-connect.c1 Nov 2003 21:02:04 -
***
*** 65,70
--- 65,71
#else
#define DefaultSSLModedisable
#endif
+ #define DefaultSIGPIPEModesigaction
/* --
***
*** 152,157
--- 153,161
{sslmode, PGSSLMODE, DefaultSSLMode, NULL,
SSL-Mode, , 8}, /* sizeof(disable) == 8 */
+ {sigpipemode, PGSIGPIPEMODE, DefaultSIGPIPEMode, NULL,
+ SIGPIPE-Mode, , 10},/* sizeof(sigaction) == 10 */
+
/* Terminating entry --- MUST BE LAST */
{NULL, NULL, NULL, NULL,
NULL, NULL, 0}
***
*** 369,374
--- 373,380
conn-sslmode = strdup(require);
}
#endif
+ tmp = conninfo_getval(connOptions, sigpipemode);
+ conn-sigpipemode = tmp ? strdup(tmp) : NULL;
/*
* Free the option info - all is in conn now
***
*** 478,483
--- 484,508
else
conn-sslmode = strdup(DefaultSSLMode);
+ /*
+* validate sigpipemode option
+*/
+ if (conn-sigpipemode)
+ {
+ if (strcmp(conn-sigpipemode, caller) != 0
+strcmp(conn-sigpipemode, sigaction) != 0)
+ {
+ conn-status = CONNECTION_BAD;
+ printfPQExpBuffer(conn-errorMessage,
+libpq_gettext(unrecognized
sigpipemode: \%s\\n),
+ conn-sigpipemode);
+ return false;
+ }
+ }
+ else
+ conn-sigpipemode = strdup(DefaultSIGPIPEMode);
+
+
return true;
}
***
*** 951,956
--- 976,986
else if (conn-sslmode[0] == 'a') /* allow */
conn-wait_ssl_try = true;
#endif
+ if (conn-sigpipemode[0] == 's') /* sigaction */
+ conn-do_sigaction = true;
+ else
+ conn-do_sigaction = false;
+
/*
* Set up to try to connect, with protocol 3.0 as the first attempt.
***
*** 2033,2038
--- 2063,2070
free(conn-pgpass);
if (conn-sslmode)
free(conn-sslmode);
+ if (conn-sigpipemode)
+ free(conn-sigpipemode);
/* Note that conn-Pfdebug is not ours to close or free */
if (conn-notifyList)
DLFreeList(conn-notifyList);
Index: fe-secure.c
===
RCS file: /projects/cvsroot/pgsql-server/src/interfaces/libpq/fe-secure.c,v
retrieving revision 1.30
diff -c -r1.30 fe-secure.c
*** fe-secure.c 5 Sep 2003 02:08:36 - 1.30
--- fe-secure.c 1 Nov 2003 21:02:06 -
***
*** 348,354
ssize_t n;
#ifndef WIN32
! pqsigfunc oldsighandler = pqsignal(SIGPIPE, SIG_IGN);
#endif
#ifdef USE_SSL
--- 348,357
ssize_t n;
#ifndef WIN32
! pqsigfunc oldsighandler = NULL;
!
! if (conn-do_sigaction)
! oldsighandler = pqsignal(SIGPIPE, SIG_IGN);
#endif
#ifdef USE_SSL
***
*** 408,414
n = send(conn-sock, ptr, len, 0);
#ifndef WIN32
! pqsignal(SIGPIPE, oldsighandler);
#endif
return n;
--- 411,418
n = send(conn-sock, ptr, len, 0);
#ifndef WIN32
! if (conn-do_sigaction)
! pqsignal(SIGPIPE, oldsighandler);
#endif
return n;
Index: libpq-int.h
===
RCS file: /projects/cvsroot/pgsql-server/src/interfaces/libpq/libpq-int.h,v
retrieving revision 1.82
diff -c -r1.82 libpq-int.h
*** libpq-int.h 5 Sep 2003 02:08:36 - 1.82
--- libpq-int.h 1 Nov 2003 21:02:07 -
***
*** 250,255
--- 250,256
char *pguser; /* Postgres username and password, if
any */
char *pgpass;
char *sslmode;/* SSL mode
Re: [HACKERS] OSDL DBT-2 w/ PostgreSQL 7.3.4 and 7.4beta5
Mark Wong wrote: Yeah, my dbt2 applications are multithreaded. Do you need SIGPIPE delivery in your app? If no, could you try what happens if you apply the attached patch to postgres, and perform the signal(SIGPIPE, SIG_IGN); once in your dbt2 app? -- Manfred --- pgsql.orig/src/interfaces/libpq/fe-secure.c 2003-11-01 22:28:13.0 +0100 +++ pgsql/src/interfaces/libpq/fe-secure.c 2003-11-01 22:27:21.0 +0100 @@ -348,7 +348,7 @@ ssize_t n; #ifndef WIN32 - pqsigfunc oldsighandler = pqsignal(SIGPIPE, SIG_IGN); +/* pqsigfunc oldsighandler = pqsignal(SIGPIPE, SIG_IGN); */ #endif #ifdef USE_SSL @@ -408,7 +408,7 @@ n = send(conn-sock, ptr, len, 0); #ifndef WIN32 - pqsignal(SIGPIPE, oldsighandler); +/* pqsignal(SIGPIPE, oldsighandler); */ #endif return n; ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Proposal: psql force prompting on notty
Michael Mauger writes: Emacs is not a Cygwin (Un*x emulation on Windows) executable (like psql is) Why don't you use the native Windows version of psql? -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: Avoiding SIGPIPE (was Re: [HACKERS] OSDL DBT-2 w/ PostgreSQL 7.3.4 and 7.4beta5)
Manfred Spraul [EMAIL PROTECTED] writes: What about an option to skip the sigaction calls for apps that can handle SIGPIPE? If the app is ignoring SIGPIPE globally, then our calls will have no effect anyway. I don't see that this proposal adds any security. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: Avoiding SIGPIPE (was Re: [HACKERS] OSDL DBT-2 w/ PostgreSQL
Tom Lane wrote: Manfred Spraul [EMAIL PROTECTED] writes: What about an option to skip the sigaction calls for apps that can handle SIGPIPE? If the app is ignoring SIGPIPE globally, then our calls will have no effect anyway. Wrong. From the opengroup manpage: SIG_IGN - ignore signal [snip] - Setting a signal action to SIG_IGN for a signal that is pending will cause the pending signal to be discarded, whether or not it is blocked This is why the kernel spends 20% cpu time processing the SIG_IGN: it must walk through all threads of the process and check if there are any SIGPIPE signals pending. I don't see that this proposal adds any security. It's not about security: Right now multithreaded apps must call signal(SIGPIPE, SIG_IGN), otherwise they could get killed by sudden SIGPIPE signals. Additionally, they can't rely on sigpending, because the pendings bits are cleared regularly. On top, they get a noticable performance hit. My proposal means that apps that know what they are doing (SIGPIPE either SIG_IGN, or blocked, or a suitable handler) can avoid the signal(SIGPIPE, SIG_IGN) in pqsecure_write. With backward compatibility, because the current system works for single threaded apps. -- Manfred ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Experimental patch for inter-page delay in VACUUM
Stephen wrote: I tried the Tom Lane's patch on PostgreSQL 7.4-BETA-5 and it works fantastically! Running a few short tests show a significant improvement in responsiveness on my RedHat 9 Linux 2.4-20-8 (IDE 120GB 7200RPM UDMA5). I am currently looking at implementing ARC as a replacement strategy. I don't have anything that works yet, so I can't really tell what the result would be and it might turn out that we want both features. All I can say is that the theory looks like an extremely smart and generalized version of the crude hack I had done. And that one is able to lower the impact of VACUUM on the foreground clients while increasing the VACUUM speed. The 7.3.4 version of my crude hack is attached. Jan I didn't feel any noticeable delay when vacuum_page_delay is set to 5ms, 10 ms. Vacuum takes 15 to 24 times longer to complete (as expected) but I don't mind at all. Vmstat BI/BO load is reduced by 5 times when vacuum_page_delay = 1ms. Load average reduced significantly also as there are less processes waiting to complete. I find a value of 1ms to 5ms is quite good and will keep system responsive. Going from 10ms to 1ms didn't seem to reduce the total vacuum time by much and I'm not sure why. Any chance we can get this patched into 7.4 permanently? I cannot say how well it would work on a heavy load, but on a light load this patch is highly recommended for 24/7 large DB systems. The database is mostly read-only. There are 133,000 rows and each row is about 2.5kB in size (mostly due to the bytea column holding a binary image). The long row causes system to TOAST the table. I repeatedly ran the following tests while system is idling: Normal operation with no VACUUM === tsdb=# explain analyze select * from table1 where id = '0078997ac809877c1a0d1f76af753608'; QUERY PLAN -- Index Scan using table1_pkey on table1 (cost=0.00..6.01 rows=2 width=344) (actual time=19.030..19.036 rows=1 loops=1) Index Cond: ((id)::text = '0078997ac809877c1a0d1f76af753608'::text) Total runtime: 19.206 ms (3 rows) VACUUM at vacuum_page_delay = 0 === -bash-2.05b$ vmstat 1 procs memory swap io system cpu r b w swpd free buff cache si sobibo incs us sy id 0 1 0 176844 3960 17748 14670400 1408 0 296 556 0 1 99 0 1 0 176844 3960 17748 14626400 1536 0 285 546 0 2 98 tsdb=# explain analyze select * from table1 where id = '00e5ae5f4fddab371f7847f7da65eebb'; QUERY PLAN Index Scan using table1_pkey on table1 (cost=0.00..6.01 rows=2 width=344) (actual time=298.028..298.047 rows=1 loops=1) Index Cond: ((id)::text = '0036edc4a92b6afd41304c6c8b76bc3c'::text) Total runtime: 298.275 ms (3 rows) tsdb=# explain analyze select * from table1 where id = '0046751ac3ec290b9f66ea1d66431923'; QUERY PLAN Index Scan using table1_pkey on table1 (cost=0.00..6.01 rows=2 width=344) (actual time=454.727..454.746 rows=1 loops=1) Index Cond: ((id)::text = '0046751ac3ec290b9f66ea1d66431923'::text) Total runtime: 454.970 ms (3 rows) tsdb=# explain analyze select * from table1 where id = '00a74e6885579a2d50487f5a1dceba22'; QUERY PLAN Index Scan using table1_pkey on table1 (cost=0.00..6.01 rows=2 width=344) (actual time=344.483..344.501 rows=1 loops=1) Index Cond: ((id)::text = '00a74e6885579a2d50487f5a1dceba22'::text) Total runtime: 344.700 ms (3 rows) VACUUM at vacuum_page_delay = 1 === procs memory swap io system cpu r b w swpd free buff cache si sobibo incs us sy id 0 0 0 176840 4292 23700 13741600 384 0 127 302 0 0 100 0 0 0 176840 4220 23700 13711600 512 0 118 286 0 0 100 1 0 0 176840 4220 23700 13665600 384 0 132 303 0 1 99 tsdb=# explain analyze select * from table1 where id = '003d5966f8b9a06e4b0fff9fa8e93be0'; QUERY PLAN -- Index Scan using
[HACKERS] Heading to Germany
I am heading to Frankfurt for the International PHP Conference in an hour. I will return on Thursday afternoon, EST. Looks like we might have an RC1 early this week, but the docs aren't frozen until final release, so I will return to the release notes and ports list when I return. I am not sure what connectivity I will have in Germany. -- 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 7: don't forget to increase your free space map settings
Re: [HACKERS] OSDL DBT-2 w/ PostgreSQL 7.3.4 and 7.4beta5
On Friday 31 October 2003 19:18, [EMAIL PROTECTED] wrote:
These are the only database parameters I've explicitly set for each one,
any other differences will be differences in default values:
- shared_buffers = 4
- tcpip_socket = true
- checkpoint_segments = 200
- checkpoint_timeout = 1800
ISTM that these two are fairly unrepresentative of any real world setups. I
might be better to knock them way back towards there defaults and turn on
checkpoint_warning to see if they should be altered.
- stats_start_collector = true
- stats_command_string = true
- stats_block_level = true
- stats_row_level = true
- stats_reset_on_server_start = true
If anyone has any tuning recommendations for either 7.3 or 7.4, I'll be
happy to try them. Or if anyone wants to be able to poke around on the
system, we can arrange that too. Feel free to ask any questions.
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] OSDL DBT-2 w/ PostgreSQL 7.3.4 and 7.4beta5
On Sat, Nov 01, 2003 at 02:37:21PM +0100, Manfred Spraul wrote: Tom Lane wrote: [EMAIL PROTECTED] writes: 7.4beta5 offers more throughput. One significant difference I see is in the oprofile for the database. For the additional 7% increase in the metric, there are about 32% less ticks in SearchCatCache. Hmm. I have been profiling PG for some years now, and I cannot remember ever seeing a profile in which SearchCatCache topped everything else (the usual suspects for me are palloc/pfree support code). Can you give any explanation why it looks like that? Can your profiling code tell where the hotspot call sites of SearchCatCache are? If I understand the docs correctly, op_to_source -a can do that - the result is annotated assembly, with percentage numbers for each instruction. If the sources were compiled with -g2, even source level annotation is possible. Mark, do you still have the oprofile output? I don't understand why so much time is spent in the kernel signal handlers, i.e. I could use annotated assembly or source of linux/kernel/signal.c. I've rerun a test, capturing the raw oprofile output, running opannotate for source and assmebly output (links for each should be on the page now.) Let me know if I've missed anything: http://developer.osdl.org/markw/dbt2-pgsql/190/ I'm running a test with your patch now too. I should have results shortly. Thanks, Mark ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] OSDL DBT-2 w/ PostgreSQL 7.3.4 and 7.4beta5
On Sat, Nov 01, 2003 at 10:29:34PM +0100, Manfred Spraul wrote: Mark Wong wrote: Yeah, my dbt2 applications are multithreaded. Do you need SIGPIPE delivery in your app? If no, could you try what happens if you apply the attached patch to postgres, and perform the signal(SIGPIPE, SIG_IGN); once in your dbt2 app? Wow, that patch made a pretty big difference: http://developer.osdl.org/markw/dbt2-pgsql/191/ - metric 1605.51 So no one has to look for older mail before I applied that patch: http://developer.osdl.org/markw/dbt2-pgsql/190/ - metric 1427.24 Looks like about a 12% improvement in the overall metric. The first thing I noticed is that do_sigaction in the kernel profile almost disappeared. The top few functions in the database profile doesn't appear to have changed much. Mark ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Getting number of matched records from query
On Sat, 1 Nov 2003, Michael Owens wrote: Isn't this information (the number of matched records) available to the backend --- even in the case of LIMIT? All matched records still have to be materialized right? Therefore the record count should be available and could therefore be available to the client. Is this correct? No. For many plans, the entire record set does not have to be fully materialized in the presence of LIMIT. In general, the limit node should have the effect of making such plans more likely to be chosen. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
