Re: [PATCHES] A way to let Vacuum warn if FSM settings are low.
I find this tiny (9-line) patch useful to help my clients know when FSM settings may need updating. Some of the more frequently asked questions here are in regards to FSM settings. One hint I've seen is to run vacuum verbose;. At the end of thousands of lines of INFO and DETAIL messages vacuum verbose has 2 separate lines with some numbers to compare (total pages needed and FSM size...pages) that help indicate too low fsm settings. I've gotten into the habit of always installing the following patch (below) that automatically does this comparison for me, and if max_fsm_pages is too small, it logs a warning as shown here: patched=# vacuum; WARNING: max_fsm_pages(1601) is smaller than total pages needed(2832) VACUUM I think this patch is great. I can never figure out how to set those settings easily. Chris ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PATCHES] A way to let Vacuum warn if FSM settings are low.
On Wed, 2005-02-23 at 19:31 -0500, Tom Lane wrote: Ron Mayer [EMAIL PROTECTED] writes: +if (needed MaxFSMPages) +ereport(WARNING, +(errmsg(max_fsm_pages(%d) is smaller than total pages needed(%.0f), + MaxFSMPages, needed))); An unconditional WARNING seems a bit strong to me for a case that is not necessarily wrong. Depending on the needs of the installation, this might be a perfectly acceptable situation --- for example if you have lots of large read-mostly tables. The patch seems very useful to me. I had been thinking about doing something like that myself. VACUUM uses an INFO to provide the total pages needed, so it should be a simple matter to change the ereport to an INFO rather than WARNING as well. It would be great to have both lines of INFO, so that VACUUM would produce output like this: patched=# vacuum; INFO: free space map: 77 relations, 470 pages stored INFO: max_fsm_pages(1601) is smaller than total pages needed(2832) DETAIL: Allocated FSM size: 100 relations + 1601 pages = 19 kB shared memory. VACUUM ...where the second info line was conditional...like this... +if (numRels == MaxFSMRelations) +ereport(WARNING, +(errmsg(max_fsm_relations(%d) may be set too low, + MaxFSMRelations))); +else +if (needed MaxFSMPages) +ereport(INFO, +(errmsg(max_fsm_pages(%d) is smaller than total pages needed(%.0f), + MaxFSMPages, needed))); ereport(elevel, (errmsg(free space map: %d relations, %d pages stored; %.0f total pages needed, Which goes more towards Tom's gripes. The manual could have a line added to explain that if max_fsm_relations is set too low, then max_fsm_pages may also inadvertently be too low, yet not be obvious that that is the case. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] A way to let Vacuum warn if FSM settings are low.
Thanks everyone for the feedback on my patch. Objections I've heard (both online and in email) included: * WARNING is too strong for possibly OK behavior * It's similar to checkpoints occuring too frequently... consider increasing...checkpoint_segments which is a LOG not a WARNING. * The end user can't do anything anyway; so the LOG file would be a better place. * My comparison for numRels was broken. * If we're hiting the user to do something (change settings) I should make it put a HINT in the log file. Praise I've heard included: * Even if it's too conservative, some admins want to know. * Unlike the current VACUUM VERBOSE info, all info is on one line, so automated log monitoring software can more easily catch it. * Unlike the current VACUUM VERBOSE info, this one points the user in the right direction. Would the updated patch below address most of the concerns? The output now looks like: LOG: max_fsm_pages(1601) is smaller than the actual number of page slots needed(2832) HINT: You may want to increase max_fsm_pages to be larger than 2832 and only goes in the log file (like the checkpoints hint). I think Tom's outstanding comment that Depending on the installation, this might be a perfectly acceptable situation ... I don't know how toproduce a warning about MaxFSMPages that's worth anything is the only objection left unaddressed. I guess my defense to that statement would be that I think for some installations this does provide value, so by making it a LOG instead of a WARNING are both needs met? Thanks, Ron % diff -U 10 postgresql-8.0.1/src/backend/storage/freespace/freespace.c postgresql-patched/src/backend/storage/freespace/freespace.c --- postgresql-8.0.1/src/backend/storage/freespace/freespace.c2004-12-31 14:00:54.0 -0800 +++ postgresql-patched/src/backend/storage/freespace/freespace.c2005-02-24 13:44:52.361669928 -0800 @@ -704,20 +704,32 @@ /* Convert stats to actual number of page slots needed */ needed = (sumRequests + numRels) * CHUNKPAGES; ereport(elevel, (errmsg(free space map: %d relations, %d pages stored; %.0f total pages needed, numRels, storedPages, needed), errdetail(Allocated FSM size: %d relations + %d pages = %.0f kB shared memory., MaxFSMRelations, MaxFSMPages, (double) FreeSpaceShmemSize() / 1024.0))); + +if (needed MaxFSMPages) +ereport(LOG, +(errmsg(max_fsm_pages(%d) is smaller than the actual number of page slots needed(%.0f), + MaxFSMPages, needed), + errhint(You may want to increase max_fsm_pages to be larger than %.0f,needed))); +if (numRels == MaxFSMRelations) +ereport(LOG, +(errmsg(max_fsm_relations(%d) is equal than the number of relations vacuum checked (%d), + MaxFSMRelations, numRels), + errhint(You probably have more than %d relations. You should increase max_fsm_relations. Pages needed for max_fsm_pages may have been underestimated as well. ,numRels))); + } /* * DumpFreeSpaceMap - dump contents of FSM into a disk file for later reload * * This is expected to be called during database shutdown, after updates to * the FSM have stopped. We lock the FreeSpaceLock but that's purely pro * forma --- if anyone else is still accessing FSM, there's a problem. */ void ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PATCHES] [pgsql-hackers-win32] Repleacement for src/port/snprintf.c
Oh, thanks. That is a great fix. Applied. Glad you could test it on a machine that supports positional parameters. --- Kurt Roeckx wrote: On Mon, Feb 21, 2005 at 10:53:08PM -0500, Bruce Momjian wrote: Applied. The configure test is a little broken. It needs to quote the $'s. I've rewritten the test a little. Kurt [ Attachment, skipping... ] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] [pgsql-hackers-win32] Repleacement for src/port/snprintf.c
Tom Lane wrote: Kurt Roeckx [EMAIL PROTECTED] writes: The configure test is a little broken. It needs to quote the $'s. I've rewritten the test a little. Applied, thanks. Oops, Tom got to it first. (Darn!) :-) -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PATCHES] A way to let Vacuum warn if FSM settings are low.
On Thu, 24 Feb 2005, Tom Lane wrote: I preferred Simon's idea of not trying to produce a warning for pages when we've detected relation overflow. Sounds good. I'll make that update. Should the relation overflow be a WARNING or a LOG? It sounds like if you have that problem it's almost certainly a problem, right? Making it a LOG rather than WARNING does address the issue of being too much in-your-face for an uncertain condition, though. Great. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] A way to let Vacuum warn if FSM settings are low.
Ron Mayer [EMAIL PROTECTED] writes: Should the relation overflow be a WARNING or a LOG? It sounds like if you have that problem it's almost certainly a problem, right? I'd go for making them both LOG, I think. More consistent. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] A way to let Vacuum warn if FSM settings are low.
On Thu, 24 Feb 2005, Ron Mayer wrote: Should the relation overflow be a WARNING or a LOG? It sounds like if you have that problem it's almost certainly a problem, right? And while I'm at it... what's the convention for INFOs vs LOGs? The checkpoint...too frequent seemed similar, and is a LOG. And do people think the HINT's I added add value or just noise? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] A way to let Vacuum warn if FSM settings are low.
On Thu, 24 Feb 2005, Tom Lane wrote: I'd go for making them both LOG, I think. More consistent. Ok, here's another try :) With a couple more questions... 1. If I read Simon's email correctly, it implied that he wanted to see the free space map message for a VACUUM even when VERBOSE is turned off. I could just tweak it in PrintFreeSpaceMapStastics() as shown here... but now elevel (which depended on VACUUM VERBOSE or not) is no longer needed by PrintFreeSpaceMapStastics. 1a. Is that desired to always show this line as an INFO instead of a DEBUG2 (which it currently is when VERBOSE is not selected)? 1b. Should I tweak vacuum.c (feels cleaner) or just freespace.c (minimal changes). 2. If I read Simon's email correctly, it implied that he wanted to see these new lines when you type VACUUM. This would suggest making them INFOs. Making them INFOs would slightly contradict another goal of wanting to see them in the LOG for automated log grepping scripts to find, since that would require turning on INFOs that I think commonly aren't logged. Also making them LOGs is consistent with the checkpoint hint. I suppose they could be made NOTICEs; but that isn't consistent with either. diff -u postgresql-8.0.1/src/backend/storage/freespace/freespace.c postgresql-patched/src/backend/storage/freespace/freespace.c --- postgresql-8.0.1/src/backend/storage/freespace/freespace.c2004-12-31 14:00:54.0 -0800 +++ postgresql-patched/src/backend/storage/freespace/freespace.c2005-02-24 14:54:36.619566040 -0800 @@ -705,12 +705,25 @@ /* Convert stats to actual number of page slots needed */ needed = (sumRequests + numRels) * CHUNKPAGES; -ereport(elevel, +ereport(INFO, (errmsg(free space map: %d relations, %d pages stored; %.0f total pages needed, numRels, storedPages, needed), errdetail(Allocated FSM size: %d relations + %d pages = %.0f kB shared memory., MaxFSMRelations, MaxFSMPages, (double) FreeSpaceShmemSize() / 1024.0))); + +if (numRels == MaxFSMRelations) +ereport(LOG, +(errmsg(max_fsm_relations(%d) is equal than the number of relations vacuum checked (%d), + MaxFSMRelations, numRels), + errhint(You probably have more than %d relations. You should increase max_fsm_relations. Pages needed for max_fsm_pages may have been underestimated. ,numRels))); +else +if (needed MaxFSMPages) +ereport(LOG, +(errmsg(max_fsm_pages(%d) is smaller than the actual number of page slots needed(%.0f), + MaxFSMPages, needed), + errhint(You may want to increase max_fsm_pages to be larger than %.0f,needed))); + } ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] New FAQ questions. minor fixes
Greg Sabino Mullane wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Added a new question about sorting with a NULL. Also some minor cleanups, especially in regards to some of the http links: is some automatic tool messing these up somehow? (many had spaces and odd breaks in the middle) The original is actually in HTML in doc/src/FAQ/FAQ.html. I made the modifications you suggested in the patch and am attaching the diff. One problem is wrapping of URL's. Most are fixed now but I saw at least on that is still wrapped in the text version. I use this command for the HTML to text conversion: lynx -force_html -dont_wrap_pre -dump -hiddenlinks=ignore -nolist $@ Any ideas on how that can be improved? -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 Index: FAQ.html === RCS file: /cvsroot/pgsql/doc/src/FAQ/FAQ.html,v retrieving revision 1.288 retrieving revision 1.291 diff -c -c -r1.288 -r1.291 *** FAQ.html15 Feb 2005 04:35:37 - 1.288 --- FAQ.html25 Feb 2005 00:45:30 - 1.291 *** *** 10,16 alink=#ff H1Frequently Asked Questions (FAQ) for PostgreSQL/H1 ! PLast updated: Mon Feb 14 23:35:09 EST 2005/P PCurrent maintainer: Bruce Momjian (A href= mailto:pgman@candle.pha.pa.us;pgman@candle.pha.pa.us/A) --- 10,16 alink=#ff H1Frequently Asked Questions (FAQ) for PostgreSQL/H1 ! PLast updated: Thu Feb 24 19:45:40 EST 2005/P PCurrent maintainer: Bruce Momjian (A href= mailto:pgman@candle.pha.pa.us;pgman@candle.pha.pa.us/A) *** *** 85,91 searches and case-insensitive regular expression searches? How do I use an index for case-insensitive searches?BR A href=#4.94.9/A) In a query, how do I detect if a field ! is SMALLNULL/SMALL?BR A href=#4.104.10/A) What is the difference between the various character types?BR A href=#4.11.14.11.1/A) How do I create a --- 85,92 searches and case-insensitive regular expression searches? How do I use an index for case-insensitive searches?BR A href=#4.94.9/A) In a query, how do I detect if a field ! is SMALLNULL/SMALL? How can I sort on whether a field is SMALL ! NULL/SMALL or not?BR A href=#4.104.10/A) What is the difference between the various character types?BR A href=#4.11.14.11.1/A) How do I create a *** *** 162,168 PPostgreSQL Data Base Management System/P PPortions Copyright (c) 1996-2005, PostgreSQL Global Development Group ! Portions Copyright (c) 1994-6 Regents of the University of California/P PPermission to use, copy, modify, and distribute this software and its documentation for any purpose, without fee, and without a --- 163,169 PPostgreSQL Data Base Management System/P PPortions Copyright (c) 1996-2005, PostgreSQL Global Development Group ! Portions Copyright (c) 1994-1996 Regents of the University of California/P PPermission to use, copy, modify, and distribute this software and its documentation for any purpose, without fee, and without a *** *** 206,212 H4A name=1.41.4/A) Where can I get PostgreSQL?/H4 PThe primary anonymous ftp site for PostgreSQL is A href= ! ftp://ftp.PostgreSQL.org/pub;ftp://ftp.PostgreSQL.org/pub/A. For mirror sites, see our main web site./P H4A name=1.51.5/A) Where can I get support?/H4 --- 207,213 H4A name=1.41.4/A) Where can I get PostgreSQL?/H4 PThe primary anonymous ftp site for PostgreSQL is A href= ! ftp://ftp.PostgreSQL.org/pub/;ftp://ftp.PostgreSQL.org/pub//A. For mirror sites, see our main web site./P H4A name=1.51.5/A) Where can I get support?/H4 *** *** 225,232 (I#postgresqlfr/I). There is also a PostgreSQL channel on EFNet. PA list of commercial support companies is available at A href= ! http://techdocs.postgresql.org/companies.php;http://techdocs.postg ! resql.org/companies.php/A./P H4A name=1.61.6/A) How do I submit a bug report?/H4 --- 226,233 (I#postgresqlfr/I). There is also a PostgreSQL channel on EFNet. PA list of commercial support companies is available at A href= ! http://techdocs.postgresql.org/companies.php; ! http://techdocs.postgresql.org/companies.php/A./P H4A name=1.61.6/A) How do I submit a bug report?/H4 *** *** 235,241 http://www.postgresql.org/support/submitbug/A./P PAlso check out our ftp site A href= ! ftp://ftp.PostgreSQL.org/pub;ftp://ftp.PostgreSQL.org/pub/A to see if there
Re: [PATCHES] Patch for Postmaster Uptime (from the TODO)
Eric Crampton [EMAIL PROTECTED] writes: I noticed in the TODO that there is an item requesting a function which returns the uptime of the postmaster. I've wanted that as well. So, I've added just such a thing: server_start_time(). This function returns the time when the postmaster was started; a simple now() - server_start_time() returns the uptime. Isn't there one of these in the patch queue already? The patch as given won't work on Windows, because it depends on fork inheritance of the value. I think the previous patcher fixed that. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Where to see the patch queue (was Re: [PATCHES] Patch for Postmaster Uptime (from the TODO))
On Feb 24, 2005, at 5:38 PM, Tom Lane wrote: Eric Crampton [EMAIL PROTECTED] writes: I noticed in the TODO that there is an item requesting a function which returns the uptime of the postmaster. I've wanted that as well. So, I've added just such a thing: server_start_time(). This function returns the time when the postmaster was started; a simple now() - server_start_time() returns the uptime. Isn't there one of these in the patch queue already? There could be, but I didn't see one because perhaps I'm looking in the wrong place. I referenced the TODO list here: http://developer.postgresql.org/todo.php and I referenced the unapplied patches here: http://momjian.postgresql.org/cgi-bin/pgpatches Where should I be looking? Thanks, --Eric ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] [pgsql-hackers-win32] Repleacement for src/port/snprintf.c
Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches It will be applied as soon as one of the PostgreSQL committers reviews and approves it. --- Nicolai Tufar wrote: On Mon, Feb 21, 2005 at 10:53:08PM -0500, Bruce Momjian wrote: Applied. Thanks a lot. The patch attached solves the tread safety problem. Please review it before applying, I am not sure I am doing the right thing On Tue, 22 Feb 2005 19:57:15 +0100, Kurt Roeckx [EMAIL PROTECTED] wrote: The configure test is a little broken. It needs to quote the $'s. I've rewritten the test a little. This one needs applying too. $'s do get scrambled. Best regards, Nicolai. [ Attachment, skipping... ] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PATCHES] [pgsql-hackers-win32] Repleacement for src/port/snprintf.c
Bruce Momjian pgman@candle.pha.pa.us writes: Your patch has been added to the PostgreSQL unapplied patches list at: Didn't we do that already? regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] [pgsql-hackers-win32] Repleacement for src/port/snprintf.c
Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: Your patch has been added to the PostgreSQL unapplied patches list at: Didn't we do that already? This patch is for thread safety: Thanks a lot. The patch attached solves the tread safety problem. Please review it before applying, I am not sure I am doing the right thing -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PATCHES] New FAQ questions. minor fixes
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 The original is actually in HTML in doc/src/FAQ/FAQ.html. I made the modifications you suggested in the patch and am attaching the diff. Thank you - I could not remember which was the chicken and which was the egg :) but I will diff the HTML next time. One problem is wrapping of URL's. Most are fixed now but I saw at least on that is still wrapped in the text version. I use this command for the HTML to text conversion: lynx -force_html -dont_wrap_pre -dump -hiddenlinks=ignore -nolist $@ Any ideas on how that can be improved? Not off the top of my head, but I will play around with it. Worse comes to worse, we can manually clean it up or make a quick perl script to clean up after lynx. - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200502242209 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFCHpbpvJuQZxSWSsgRAud1AJ0QE6FkSp1vfULmMnW+4noTWSzaewCePl69 4/Dx6NZ6dWs1va8dMVE4mEQ= =5wfa -END PGP SIGNATURE- ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PATCHES] New FAQ questions. minor fixes
Greg Sabino Mullane wrote: [ There is text before PGP section. ] -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 The original is actually in HTML in doc/src/FAQ/FAQ.html. I made the modifications you suggested in the patch and am attaching the diff. Thank you - I could not remember which was the chicken and which was the egg :) but I will diff the HTML next time. Yea, the FAQ's master is HTML, and the TODO's master is a text file. One problem is wrapping of URL's. Most are fixed now but I saw at least on that is still wrapped in the text version. I use this command for the HTML to text conversion: lynx -force_html -dont_wrap_pre -dump -hiddenlinks=ignore -nolist $@ Any ideas on how that can be improved? Not off the top of my head, but I will play around with it. Worse comes to worse, we can manually clean it up or make a quick perl script to clean up after lynx. True. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] Change to -f in examples with input files
David Fetter wrote: Please find enclosed a patch, per Dennis BjC3B6rklund, that uses -f for input files rather than . This makes error messages, c. more expressive. David Fetter Applied. Thanks. (I reordered the arguments as Peter suggested.) -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 Index: doc/src/sgml/backup.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/backup.sgml,v retrieving revision 2.55 diff -c -r2.55 backup.sgml *** doc/src/sgml/backup.sgml22 Jan 2005 22:56:35 - 2.55 --- doc/src/sgml/backup.sgml20 Feb 2005 20:45:07 - *** *** 177,183 /synopsis The resulting dump can be restored with applicationpsql/: synopsis ! psql template1 lt; replaceable class=parameterinfile/replaceable /synopsis (Actually, you can specify any existing database name to start from, but if you are reloading in an empty cluster then literaltemplate1/ --- 177,183 /synopsis The resulting dump can be restored with applicationpsql/: synopsis ! psql -f replaceable class=parameterinfile/replaceable template1 /synopsis (Actually, you can specify any existing database name to start from, but if you are reloading in an empty cluster then literaltemplate1/ *** *** 1210,1216 gmake install initdb -D /usr/local/pgsql/data postmaster -D /usr/local/pgsql/data ! psql template1 lt; backup /programlisting See xref linkend=runtime about ways to start and stop the --- 1210,1216 gmake install initdb -D /usr/local/pgsql/data postmaster -D /usr/local/pgsql/data ! psql -f backup template1 /programlisting See xref linkend=runtime about ways to start and stop the ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
