Re: [HACKERS] Autovacuum launcher doesn't notice death of postmaster immediately
Matthew T. O'Connor schrieb: Tom Lane wrote: Andrew Hammond [EMAIL PROTECTED] writes: Hmmm... it seems to me that points new users towards not using autovacuum, which doesn't seem like the best idea. I think it'd be better to say that setting the naptime really high is a Bad Idea. It seems like we should have an upper limit on the GUC variable that's less than INT_MAX ;-). Would an hour be sane? 10 minutes? This is independent of the problem at hand, though, which is that we probably want the launcher to notice postmaster death in less time than autovacuum_naptime, for reasonable values of same. Do we need a configurable autovacuum naptime at all? I know I put it in the original contrib autovacuum because I had no idea what knobs might be needed. I can't see a good reason to ever have a naptime longer than the default 60 seconds, but I suppose one might want a smaller naptime for a very active system? A PostgreSQL database on my laptop for testing. It should use as little resources as possible while being idle. That would be a scenario for naptime greater than 60 seconds, wouldn't it? Best Regards Michael Paesold ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Autovacuum launcher doesn't notice death of postmaster immediately
The launcher is set up to wake up in autovacuum_naptime seconds at most. Imho the fix is usually to have a sleep loop. Andreas ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Controlling Load Distributed Checkpoints
Greg Smith wrote: On Thu, 7 Jun 2007, Heikki Linnakangas wrote: So there's two extreme ways you can use LDC: 1. Finish the checkpoint as soon as possible, without disturbing other activity too much 2. Disturb other activity as little as possible, as long as the checkpoint finishes in a reasonable time. Are both interesting use cases, or is it enough to cater for just one of them? I think 2 is easier to tune. The motivation for the (1) case is that you've got a system that's dirtying the buffer cache very fast in normal use, where even the background writer is hard pressed to keep the buffer pool clean. The checkpoint is the most powerful and efficient way to clean up many dirty buffers out of such a buffer cache in a short period of time so that you're back to having room to work in again. In that situation, since there are many buffers to write out, you'll also be suffering greatly from fsync pauses. Being able to synchronize writes a little better with the underlying OS to smooth those out is a huge help. ISTM the bgwriter just isn't working hard enough in that scenario. Assuming we get the lru autotuning patch in 8.3, do you think there's still merit in using the checkpoints that way? I'm completely biased because of the workloads I've been dealing with recently, but I consider (2) so much easier to tune for that it's barely worth worrying about. If your system is so underloaded that you can let the checkpoints take their own sweet time, I'd ask if you have enough going on that you're suffering very much from checkpoint performance issues anyway. I'm used to being in a situation where if you don't push out checkpoint data as fast as physically possible, you end up fighting with the client backends for write bandwidth once the LRU point moves past where the checkpoint has written out to already. I'm not sure how much always running the LRU background writer will improve that situation. I'd think it eliminates the problem. Assuming we keep the LRU cleaning running as usual, I don't see how writing faster during checkpoints could ever be beneficial for concurrent activity. The more you write, the less bandwidth there's available for others. Doing the checkpoint as quickly as possible might be slightly better for average throughput, but that's a different matter. On every system I've ever played with Postgres write performance on, I discovered that the memory-based parameters like dirty_background_ratio were really driving write behavior, and I almost ignore the expire timeout now. Plotting the Dirty: value in /proc/meminfo as you're running tests is extremely informative for figuring out what Linux is really doing underneath the database writes. Interesting. I haven't touched any of the kernel parameters yet in my tests. It seems we need to try different parameters and see how the dynamics change. But we must also keep in mind that average DBA doesn't change any settings, and might not even be able or allowed to. That means the defaults should work reasonably well without tweaking the OS settings. The influence of the congestion code is why I made the comment about watching how long writes are taking to gauge how fast you can dump data onto the disks. When you're suffering from one of the congestion mechanisms, the initial writes start blocking, even before the fsync. That behavior is almost undocumented outside of the relevant kernel source code. Yeah, that's controlled by dirty_ratio, if I've understood the parameters correctly. If we spread out the writes enough, we shouldn't hit that limit or congestion. That's the point of the patch. Do you have time / resources to do testing? You've clearly spent a lot of time on this, and I'd be very interested to see some actual numbers from your tests with various settings. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] little PITR annoyance
Hi Simon, On Thu, 7 Jun 2007, Simon Riggs wrote: Date: Thu, 07 Jun 2007 23:10:06 +0100 From: Simon Riggs [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: pgsql-hackers list pgsql-hackers@postgresql.org Subject: Re: [HACKERS] little PITR annoyance On Thu, 2007-06-07 at 17:01 +0200, [EMAIL PROTECTED] wrote: I've benn biten twice by this one: I enabled archiving on both my pgsql servers. the archive command was a simple gzip on an nfs mounted dir. Now the nfs server crashed. Both wal space filled up until postgresql shuts itself down because of no more space for WAL. That perfectly normal and expected. What I did'nt expect and don't understand is that postgresql refused to start up after the nfs server was up and running until I added some more space on the WAL fs although if it had started archiving , space would have been there . I wonder if archiving could start before postgresql at least to make a little room for database engine to really start. gzip write a new file and then deletes the old, doesn't it? So it must require space on the xlog drive. Does it still fail if you avoid using gzip and just use scp? The problem is not with gzip or scp, it is that postmaster refuses to start because wal FS is full. My questions was: why don't we start the archiving *BEFORE* postmaster to make room. Another related question would be: What if postmaster instead of shutting down in that case, refused connection until archiving is back? Many thanks -- Olivier PRENANT Tel: +33-5-61-50-97-00 (Work) 15, Chemin des Monges+33-5-61-50-97-01 (Fax) 31190 AUTERIVE +33-6-07-63-80-64 (GSM) FRANCE Email: [EMAIL PROTECTED] -- Make your life a dream, make your dream a reality. (St Exupery) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] TOAST usage setting
My next suggestion would be to leave EXTERN_TUPLES_PER_PAGE as is, but: Split data wider than a page into page sized chunks as long as they fill whole pages. Split the rest with EXTERN_TUPLES_PER_PAGE (4) as now. This would not waste more space than currently, but improve performance for very wide columns. I can try to do a patch if you think that is a good idea, can't do a lot of testing though. I have a PoC patch running, but it is larger than expected because of the size checks during read (toast_fetch_datum_slice not done, but would be straight forward). Also the pg_control variable toast_max_chunk_size would need to be renamed and reflect the EXTERN_TUPLES_PER_PAGE (4) number and the fact that fullpage chunks are used (else the chunk size checks and slice could not work like now). Should I pursue, keep for 8.4, dump it ? The downside of this concept is, that chunks smaller than fullpage still get split into the smaller pieces. And the ~8k chunks may well outnumber the ~8k on real data. The up side is, that I do not see a better solution that would keep slice cheap and still lower the overhead even for pathological cases. Andreas ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Performance regression on CVS head
Tom Lane wrote: Heikki Linnakangas [EMAIL PROTECTED] writes: I tried to repeat the DBT-2 runs with the oldestxmin refresh patch, but to my surprise the baseline run with CVS head, without the patch, behaved very differently than it did back in March. I rerun the a shorter 1h test with CVS head from May 20th, and March 6th (which is when I ran the earlier tests), and something has clearly been changed between those dates that affects the test. Test run 248 is with CVS checkout from May 20th, and 249 is from March 6th: May 20th is not quite my idea of HEAD ;-). It might be worth checking current code before investing any think-time on this. But having said that, it looks a bit like a planner problem --- if I'm reading the graphs correctly, I/O wait time goes through the roof, suggesting a change to a much less efficient plan. I tracked this down to the patch to enable plan invalidation for SPI plans: http://archives.postgresql.org/pgsql-committers/2007-03/msg00136.php Apparently the vacuum causes a plan invalidation and a worse plan is chosen. I'll dig deeper into which queries are being affected and why. Unless someone has any better ideas. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] COPYable logs status
[summarising discussion on -patches] The situation with this patch is that I now have it in a state where I think it could be applied, but there is one blocker, namely that we do not have a way of preventing the interleaving of log messages from different backends, which leads to garbled logs. This is an existing issue about which we have had complaints, but it becomes critical for a facility the whole purpose of which is to provide logs in a format guaranteed to work with our COPY command. Unfortunately, there is no solution in sight for this problem, certainly not one which I think can be devised and implemented simply at this stage of the cycle. The solution we'd like to use, LWLocks, is not workable in his context. In consequence, I don't think we have any option but to shelve this item for the time being. A couple of bugs have been found and fixes identified, during the review process, so it's not a total loss, but it is nevertheless a pity that we can't deliver this feature in 8.3. cheers andrew ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] Index adviser or just [pg_]adviser
I am done with refactoring the Index adviser code to make use of the current planner hooks, and as part of this drive I was thinking of changing the name of the feature/plugin too. Since there's a possibility that the same architecture can be used by other types of adviser plugins (materialized view adviser, query refactoring adviser, etc...) and considering that this same code-base may be used to develop those things (or they may be developed as part of this plugin), would it be prudent to rename this plugin to just 'adviser' or 'pg_adviser' instead of the current name 'index_adviser'? Regards, -- [EMAIL PROTECTED] [EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com 17°29'34.37N 78°30'59.76E - Hyderabad * 18°32'57.25N 73°56'25.42E - Pune Sent from my BlackLaptop device
Re: [HACKERS] COPYable logs status
Andrew Dunstan wrote: Unfortunately, there is no solution in sight for this problem, certainly not one which I think can be devised and implemented simply at this stage of the cycle. The solution we'd like to use, LWLocks, is not workable in his context. In consequence, I don't think we have any option but to shelve this item for the time being. The idea of one pipe per process is not really workable, because it would mean having as many pipes as backends which does not sound very good. But how about a mixed approach -- like have the all the backends share a pipe, controlled by an LWLock, and the auxiliary process have a separate pipe each? One thing I haven't understood yet is how having multiple pipes help on this issue. Is the logger reading from the pipe and then writing to a file? (I haven't read the logger code). -- Alvaro Herrera http://www.amazon.com/gp/registry/CTMLCN8V17R4 Endurecerse, pero jamás perder la ternura (E. Guevara) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Autovacuum launcher doesn't notice death of postmaster immediately
Zeugswetter Andreas ADI SD escribió: The launcher is set up to wake up in autovacuum_naptime seconds at most. Imho the fix is usually to have a sleep loop. This is what we have. The sleep time depends on the schedule of next vacuum for the closest database in time. If naptime is high, the sleep time will be high (depending on number of databases needing attention). -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] COPYable logs status
Martijn van Oosterhout wrote: On Fri, Jun 08, 2007 at 08:31:54AM -0400, Andrew Dunstan wrote: The situation with this patch is that I now have it in a state where I think it could be applied, but there is one blocker, namely that we do not have a way of preventing the interleaving of log messages from different backends, which leads to garbled logs. This is an existing issue about which we have had complaints, but it becomes critical for a facility the whole purpose of which is to provide logs in a format guaranteed to work with our COPY command. The whole semantics of PIPEBUF should prevent garbling, as long as each write is a complete set of lines and no more than PIPEBUF bytes long. Have we determined the actual cause of the garbling? No, that's the main problem -- but it has been reported to happen on entries shorter than PIPE_BUF chars. -- Alvaro Herrera http://www.PlanetPostgreSQL.org/ La persona que no quería pecar / estaba obligada a sentarse en duras y empinadas sillas/ desprovistas, por cierto de blandos atenuantes (Patricio Vogel) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Autovacuum launcher doesn't notice death of postmaster immediately
Michael Paesold wrote: Matthew T. O'Connor schrieb: Do we need a configurable autovacuum naptime at all? I know I put it in the original contrib autovacuum because I had no idea what knobs might be needed. I can't see a good reason to ever have a naptime longer than the default 60 seconds, but I suppose one might want a smaller naptime for a very active system? A PostgreSQL database on my laptop for testing. It should use as little resources as possible while being idle. That would be a scenario for naptime greater than 60 seconds, wouldn't it? Perhaps, but that isn't the use case PostgresSQL is being designed for. If that is what you really need, then you should probably disable autovacuum. Also a very long naptime means that autovacuum will still wake up at random times and to do the work. At least with short naptime, it will do the work shortly after you updated your tables. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] COPYable logs status
On Fri, Jun 08, 2007 at 08:31:54AM -0400, Andrew Dunstan wrote: The situation with this patch is that I now have it in a state where I think it could be applied, but there is one blocker, namely that we do not have a way of preventing the interleaving of log messages from different backends, which leads to garbled logs. This is an existing issue about which we have had complaints, but it becomes critical for a facility the whole purpose of which is to provide logs in a format guaranteed to work with our COPY command. The whole semantics of PIPEBUF should prevent garbling, as long as each write is a complete set of lines and no more than PIPEBUF bytes long. Have we determined the actual cause of the garbling? Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [HACKERS] COPYable logs status
Alvaro Herrera [EMAIL PROTECTED] writes: Martijn van Oosterhout wrote: The whole semantics of PIPEBUF should prevent garbling, as long as each write is a complete set of lines and no more than PIPEBUF bytes long. Have we determined the actual cause of the garbling? No, that's the main problem -- but it has been reported to happen on entries shorter than PIPE_BUF chars. It's not entirely clear to me whether there's been proven cases of interpolation *into* a message shorter than PIPE_BUF (and remember you've got to count all the lines when determining the length). The message intruding into the other could certainly be shorter. If there have been such cases, then our theories about what's going on are all wet, or else there are some rather nasty bugs in some kernels' pipe handling. So it would be good to pin this down. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Controlling Load Distributed Checkpoints
On Fri, Jun 08, 2007 at 09:50:49AM +0100, Heikki Linnakangas wrote: dynamics change. But we must also keep in mind that average DBA doesn't change any settings, and might not even be able or allowed to. That means the defaults should work reasonably well without tweaking the OS settings. Do you mean change the OS settings or something else? (I'm not sure it's true in any case, because shared memory kernel settings have to be fiddled with in many instances, but I thought I'd ask for clarification.) A -- Andrew Sullivan | [EMAIL PROTECTED] Users never remark, Wow, this software may be buggy and hard to use, but at least there is a lot of code underneath. --Damien Katz ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Controlling Load Distributed Checkpoints
On Fri, 8 Jun 2007, Andrew Sullivan wrote: Do you mean change the OS settings or something else? (I'm not sure it's true in any case, because shared memory kernel settings have to be fiddled with in many instances, but I thought I'd ask for clarification.) In a situation where a hosting provider of some sort is providing PostgreSQL, they should know that parameters like SHMMAX need to be increased before customers can create a larger installation. You'd expect they'd take care of that as part of routine server setup. What wouldn't be reasonable is to expect them to tune obscure parts of the kernel just for your application. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] [BUGS] BUG #3326: Invalid lower bound of autovacuum_cost_limit
Alvaro Herrera wrote:
Matthew T. O'Connor wrote:
Can you make 0 and -1 both valid disabled values? That way it will be
compatible with previous releases.
Heh, sure, we can do that too and it doesn't seem like anybody would
object. I will patch the documentation so that that the disabled
value is zero, and still allow -1. That way it doesn't seem like there
should be any objection.
Patch attached.
--
Alvaro Herrera http://www.amazon.com/gp/registry/5ZYLFMCVHXC
Saca el libro que tu religión considere como el indicado para encontrar la
oración que traiga paz a tu alma. Luego rebootea el computador
y ve si funciona (Carlos Duclós)
Index: doc/src/sgml/catalogs.sgml
===
RCS file: /home/alvherre/Code/cvs/pgsql/doc/src/sgml/catalogs.sgml,v
retrieving revision 2.153
diff -c -p -r2.153 catalogs.sgml
*** doc/src/sgml/catalogs.sgml 5 Jun 2007 21:31:03 - 2.153
--- doc/src/sgml/catalogs.sgml 8 Jun 2007 14:40:10 -
***
*** 1339,1347
be used for this particular value. Observe that the
structfieldvac_cost_delay/ variable inherits its default value from the
xref linkend=guc-autovacuum-vacuum-cost-delay configuration parameter,
!or from varnamevacuum_cost_delay/ if the former is set to a negative
!value. The same applies to structfieldvac_cost_limit/.
!Also, autovacuum will ignore attempts to set a per-table
structfieldfreeze_max_age/ larger than the system-wide setting (it can only be set
smaller), and the structfieldfreeze_min_age value/ will be limited to half the
system-wide xref linkend=guc-autovacuum-freeze-max-age setting.
--- 1339,1350
be used for this particular value. Observe that the
structfieldvac_cost_delay/ variable inherits its default value from the
xref linkend=guc-autovacuum-vacuum-cost-delay configuration parameter,
!or from xref linkend=guc-vacuum-cost-delay if the former is set to a negative
!value. structfieldvac_cost_limit/ is an exception to this rule, because
!the value literal0/ is used to indicate that the
!xref linkend=guc-autovacuum-vacuum-cost-limit configuration parameter
!should be used, or xref linkend=guc-vacuum-cost-limit if the former is set to a
!zero or negative value. Note that autovacuum will ignore attempts to set a per-table
structfieldfreeze_max_age/ larger than the system-wide setting (it can only be set
smaller), and the structfieldfreeze_min_age value/ will be limited to half the
system-wide xref linkend=guc-autovacuum-freeze-max-age setting.
Index: doc/src/sgml/config.sgml
===
RCS file: /home/alvherre/Code/cvs/pgsql/doc/src/sgml/config.sgml,v
retrieving revision 1.126
diff -c -p -r1.126 config.sgml
*** doc/src/sgml/config.sgml 7 Jun 2007 19:19:56 - 1.126
--- doc/src/sgml/config.sgml 8 Jun 2007 14:15:33 -
*** SELECT * FROM parent WHERE key = 2400;
*** 3356,3362
listitem
para
Specifies the cost limit value that will be used in automatic
! commandVACUUM/ operations. If literal-1/ is specified (which is the
default), the regular
xref linkend=guc-vacuum-cost-limit value will be used. Note that
the value is distributed proportionally among the running autovacuum
--- 3356,3362
listitem
para
Specifies the cost limit value that will be used in automatic
! commandVACUUM/ operations. If literal0/ is specified (which is the
default), the regular
xref linkend=guc-vacuum-cost-limit value will be used. Note that
the value is distributed proportionally among the running autovacuum
Index: src/backend/postmaster/autovacuum.c
===
RCS file: /home/alvherre/Code/cvs/pgsql/src/backend/postmaster/autovacuum.c,v
retrieving revision 1.47
diff -c -p -r1.47 autovacuum.c
*** src/backend/postmaster/autovacuum.c 30 May 2007 20:11:57 - 1.47
--- src/backend/postmaster/autovacuum.c 8 Jun 2007 14:23:35 -
*** static void
*** 1548,1554
autovac_balance_cost(void)
{
WorkerInfo worker;
! int vac_cost_limit = (autovacuum_vac_cost_limit = 0 ?
autovacuum_vac_cost_limit : VacuumCostLimit);
int vac_cost_delay = (autovacuum_vac_cost_delay = 0 ?
autovacuum_vac_cost_delay : VacuumCostDelay);
--- 1548,1554
autovac_balance_cost(void)
{
WorkerInfo worker;
! int vac_cost_limit = (autovacuum_vac_cost_limit 0 ?
autovacuum_vac_cost_limit : VacuumCostLimit);
int vac_cost_delay = (autovacuum_vac_cost_delay = 0 ?
autovacuum_vac_cost_delay : VacuumCostDelay);
*** table_recheck_autovac(Oid relid)
*** 2143,2151
*/
if (avForm != NULL)
{
! vac_cost_limit =
Re: [HACKERS] Controlling Load Distributed Checkpoints
Andrew Sullivan wrote: On Fri, Jun 08, 2007 at 09:50:49AM +0100, Heikki Linnakangas wrote: dynamics change. But we must also keep in mind that average DBA doesn't change any settings, and might not even be able or allowed to. That means the defaults should work reasonably well without tweaking the OS settings. Do you mean change the OS settings or something else? (I'm not sure it's true in any case, because shared memory kernel settings have to be fiddled with in many instances, but I thought I'd ask for clarification.) Yes, that's what I meant. An average DBA is not likely to change OS settings. You're right on the shmmax setting, though. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] COPYable logs status
Alvaro Herrera [EMAIL PROTECTED] writes: The idea of one pipe per process is not really workable, because it would mean having as many pipes as backends which does not sound very good. But how about a mixed approach -- like have the all the backends share a pipe, controlled by an LWLock, and the auxiliary process have a separate pipe each? Multiple pipes seem like a mess, and in any case the above still doesn't work for stderr output produced by non-cooperative software (dynamic loader for instance). The only solution that I can see is to invent some sort of simple protocol for the syslogger pipe. Assume that the kernel honors PIPE_BUF (this assumption may need proving, see other message). We could imagine having elog.c divvy up its writes to the pipe into chunks of less than PIPE_BUF bytes, where each chunk carries info sufficient to let it be reassembled. Perhaps something on the order of \0 \0 2-byte-length source-PID end-flag text... The syslogger reassembles these by joining messages with the same origination PID, until it gets one with the end-flag set. It would need enough code to track multiple in-progress messages. The logger would have to also be able to deal with random text coming down the pipe (due to aforesaid non-cooperative software). I would be inclined to say just take any text not preceded by \0\0 as a standalone message, up to the next \0\0. Long chunks of non-protocol text would risk getting treated as multiple messages, but there's probably not a lot of harm in that. BTW, exactly what is the COPYable-logs code going to do with random text? I trust the answer is not throw it away. regards, tom lane ---(end of broadcast)--- TIP 1: 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] COPYable logs status
Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: Martijn van Oosterhout wrote: The whole semantics of PIPEBUF should prevent garbling, as long as each write is a complete set of lines and no more than PIPEBUF bytes long. Have we determined the actual cause of the garbling? No, that's the main problem -- but it has been reported to happen on entries shorter than PIPE_BUF chars. It's not entirely clear to me whether there's been proven cases of interpolation *into* a message shorter than PIPE_BUF (and remember you've got to count all the lines when determining the length). The message intruding into the other could certainly be shorter. If there have been such cases, then our theories about what's going on are all wet, or else there are some rather nasty bugs in some kernels' pipe handling. So it would be good to pin this down. Right. But we don't split lines into PIPE_BUF sized chunks. And doing so would make loadable logs possibly rather less pleasant. Ideally we should be able to deal with this despite the PIPE_BUF restriction on atomic writes. cheers andrew ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] COPYable logs status
On Fri, Jun 08, 2007 at 10:29:03AM -0400, Tom Lane wrote: The only solution that I can see is to invent some sort of simple protocol for the syslogger pipe. Perhaps having a look at the current IETF syslog discussion will be helpful in that case? (I know it's not directly relevant, but maybe others have thought about some of these things. I haven't read the draft, note.) http://tools.ietf.org/html/draft-ietf-syslog-protocol-20 There's also the discussion of reliability in RFC 3195: ftp://ftp.rfc-editor.org/in-notes/rfc3195.txt A -- Andrew Sullivan | [EMAIL PROTECTED] The whole tendency of modern prose is away from concreteness. --George Orwell ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] [BUGS] BUG #3326: Invalid lower bound of autovacuum_cost_limit
Alvaro Herrera [EMAIL PROTECTED] writes: Matthew T. O'Connor wrote: Can you make 0 and -1 both valid disabled values? That way it will be compatible with previous releases. Heh, sure, we can do that too and it doesn't seem like anybody would object. I will patch the documentation so that that the disabled value is zero, and still allow -1. That way it doesn't seem like there should be any objection. Patch attached. It seems like documenting vac_cost_limit as being different from the others will just create perceived complexity/confusion, with no real benefit. I'd suggest leaving the documentation and the default value alone, and applying just the part of the patch that causes 0 to be silently treated as if it were -1. A comment at the spot where this is done would be a good idea, but I don't think we need to say anything in the SGML docs. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Controlling Load Distributed Checkpoints
On Fri, Jun 08, 2007 at 10:33:50AM -0400, Greg Smith wrote: they'd take care of that as part of routine server setup. What wouldn't be reasonable is to expect them to tune obscure parts of the kernel just for your application. Well, I suppose it'd depend on what kind of hosting environment you're in (if I'm paying for dedicated hosting, you better believe I'm going to insist they tune the kernel the way I want), but you're right that in shared hosting for $25/mo, it's not going to happen. A -- Andrew Sullivan | [EMAIL PROTECTED] The year's penultimate month is not in truth a good way of saying November. --H.W. Fowler ---(end of broadcast)--- TIP 1: 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] Autovacuum launcher doesn't notice death of postmaster immediately
Zeugswetter Andreas ADI SD escribió: The launcher is set up to wake up in autovacuum_naptime seconds at most. Imho the fix is usually to have a sleep loop. This is what we have. The sleep time depends on the schedule of next vacuum for the closest database in time. If naptime is high, the sleep time will be high (depending on number of databases needing attention). No, I meant a while (sleep 1(or 10) and counter longtime) check for exit instead of sleep longtime. Ah; yes, what I was proposing (or thought about proposing, not sure if I posted it or not) was putting a upper limit of 10 seconds in the sleep (bgwriter sleeps 10 seconds if configured to not do anything). Though 10 seconds may seem like an eternity for systems like the ones Peter was talking about, where there is a script trying to restart the server as soon as the postmaster dies. -- Alvaro Herrera Developer, http://www.PostgreSQL.org/ Limítate a mirar... y algun día veras ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] COPYable logs status
Andrew Dunstan wrote: The situation with this patch is that I now have it in a state where I think it could be applied, but there is one blocker, namely that we do not have a way of preventing the interleaving of log messages from different backends, which leads to garbled logs. This is an existing issue about which we have had complaints, but it becomes critical for a facility the whole purpose of which is to provide logs in a format guaranteed to work with our COPY command. Unfortunately, there is no solution in sight for this problem, certainly not one which I think can be devised and implemented simply at this stage of the cycle. The solution we'd like to use, LWLocks, is not workable in his context. In consequence, I don't think we have any option but to shelve this item for the time being. I think this will get shot down, but here goes anyway... How about creating a log-writing-process? Postmaster could write to the log files directly until the log-writer is up and running, then all processes can send their log output through the log-writer. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[HACKERS] Using the GPU
Does anyone think that PostgreSQL could benefit from using the video card as a parallel computing device? I'm working on a project using Nvidia's CUDA with an 8800 series video card to handle non-graphical algorithms. I'm curious if anyone thinks that this technology could be used to speed up a database? If so which part of the database, and what kind of parallel algorithms would be used? Thanks, sorry if this is a duplicate message. -- John Billings
Re: [HACKERS] Autovacuum launcher doesn't notice death of postmaster immediately
The launcher is set up to wake up in autovacuum_naptime seconds at most. Imho the fix is usually to have a sleep loop. This is what we have. The sleep time depends on the schedule of next vacuum for the closest database in time. If naptime is high, the sleep time will be high (depending on number of databases needing attention). No, I meant a while (sleep 1(or 10) and counter longtime) check for exit instead of sleep longtime. Andreas ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Controlling Load Distributed Checkpoints
Andrew Sullivan wrote: On Fri, Jun 08, 2007 at 10:33:50AM -0400, Greg Smith wrote: they'd take care of that as part of routine server setup. What wouldn't be reasonable is to expect them to tune obscure parts of the kernel just for your application. Well, I suppose it'd depend on what kind of hosting environment you're in (if I'm paying for dedicated hosting, you better believe I'm going to insist they tune the kernel the way I want), but you're right that in shared hosting for $25/mo, it's not going to happen. And consider other operating systems that don't have the same knobs. We should tune as best we can first without kernel knobs. -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] little PITR annoyance
Hi Olivier, On Fri, 2007-06-08 at 11:41 +0200, [EMAIL PROTECTED] wrote: The problem is not with gzip or scp, it is that postmaster refuses to start because wal FS is full. My questions was: why don't we start the archiving *BEFORE* postmaster to make room. The archiver is executed from the postmaster, so thats not possible. We could investigate where best to put some code, but it wouldn't be executed very frequently. Why not just execute the archive_command in a script, replacing the .ready with .done files in archive_status directory when its processed? -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Minor changes to Recovery related code
On Thu, 2007-06-07 at 21:53 +0100, Simon Riggs wrote: We have a number of problems surrounding pg_stop_backup/shutdown: I'll start coding up my proposals, in the absence of alternate views. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[HACKERS] Patch review process; Warning to patch submitters
Based on the patches status page: http://developer.postgresql.org/index.php/Todo:PatchStatus#current_8.3_patch_status we are a few weeks away from completing all patch review and entering beta. If you have been asked to updated your patch by a patch reviewer, please do so in the next few days or your patch risks being held for 8.4. Patch reviewers are not going to chase down patch authors to get patch changes completed; one email request is sufficient, and I will back up any patch reviewer who wants to hold a patch for 8.4 because the patch was not updated by the patch author in a timely manner. This holds true not only for patches where an update has already been requested but for future requests for updates. The original patch might have made the April 1 deadline, but if it isn't updated promptly, then it will be considered to be not ready to apply and be held for 8.4. The bottom line is that the patch review process is hard enough, and we need the assistance of patch submitters to work with the patch reviewers in a timely manner. -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 1: 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] Using the GPU
Aren't most databases constrained by I/O? And postgresql by how fast your kernel can switch between processes under a concurrent load? From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Billings, John Sent: Friday, June 08, 2007 10:55 AM To: pgsql-hackers@postgresql.org Subject: [HACKERS] Using the GPU Does anyone think that PostgreSQL could benefit from using the video card as a parallel computing device? I'm working on a project using Nvidia's CUDA with an 8800 series video card to handle non-graphical algorithms. I'm curious if anyone thinks that this technology could be used to speed up a database? If so which part of the database, and what kind of parallel algorithms would be used? Thanks, sorry if this is a duplicate message. -- John Billings
Re: [HACKERS] COPYable logs status
Matthew T. O'Connor [EMAIL PROTECTED] writes: How about creating a log-writing-process? Postmaster could write to the log files directly until the log-writer is up and running, then all processes can send their log output through the log-writer. We *have* a log-writing process. The problem is in getting the data to it. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] COPYable logs status
Tom Lane wrote: Matthew T. O'Connor [EMAIL PROTECTED] writes: How about creating a log-writing-process? Postmaster could write to the log files directly until the log-writer is up and running, then all processes can send their log output through the log-writer. We *have* a log-writing process. The problem is in getting the data to it. By that I assume you mean the bgwriter, I thought that was for WAL data, I didn't think it could or perhaps should be used for normal log file writing, but I also know I'm way outside my comfort area in talking about this, so excuse the noise if this is way off base. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] Binary mode copy in from stdin
We have (among other things) and ODBC/OLEDB/JDBC/.NET driver for PostgreSQL and we want to optimize fast mode insert/select behavior. When we try to do a binary mode copy from standard input, we get an error message that we can't do it. How can we programmatically insert data using COPY from our own data stream without creating a file. In our case, creating a file is just plain stupid. There is never any reason for the data to hit the disk except at write time. In case you were wondering, we already have the data in the correct binary format. Why was it decided that binary mode is not allowed for stdin or stdout? After all, programs can pipe to stdin and stdout.
Re: [HACKERS] Autovacuum launcher doesn't notice death of postmaster immediately
On Fri, Jun 08, 2007 at 09:49:56AM -0400, Matthew O'Connor wrote: Michael Paesold wrote: Matthew T. O'Connor schrieb: Do we need a configurable autovacuum naptime at all? I know I put it in the original contrib autovacuum because I had no idea what knobs might be needed. I can't see a good reason to ever have a naptime longer than the default 60 seconds, but I suppose one might want a smaller naptime for a very active system? A PostgreSQL database on my laptop for testing. It should use as little resources as possible while being idle. That would be a scenario for naptime greater than 60 seconds, wouldn't it? Perhaps, but that isn't the use case PostgresSQL is being designed for. If that is what you really need, then you should probably disable autovacuum. Also a very long naptime means that autovacuum will still wake up at random times and to do the work. At least with short naptime, it will do the work shortly after you updated your tables. Agreed. Maybe 10 minutes might make sense, but the overhead of checking to see if anything needs vacuuming is pretty tiny. There *is* reason to allow setting the naptime smaller, though (or at least there was; perhaps Alvero's recent changes negate this need): clusters that have a large number of databases. I've worked with folks who are in a hosted environment and give each customer their own database; it's not hard to get a couple hundred databases that way. Setting the naptime higher than a second in such an environment would mean it could be hours before a database is checked for vacuuming. -- Jim Nasby [EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) pgprhXI13515U.pgp Description: PGP signature
Re: [HACKERS] Autovacuum launcher doesn't notice death of postmaster immediately
On Thu, Jun 07, 2007 at 12:13:09PM -0700, Andrew Hammond wrote: On 6/7/07, Jim C. Nasby [EMAIL PROTECTED] wrote: On Mon, Jun 04, 2007 at 11:04:26AM -0400, Alvaro Herrera wrote: The launcher is set up to wake up in autovacuum_naptime seconds at most. So if the user configures a ridiculuos time (for example 86400 seconds, which I've seen) then the launcher would not detect the postmaster death Is there some threshold after which we should have PostgreSQL emit a warning to the effect of autovacuum_naptime is very large. Are you sure you know what you're doing? Yeah, I've seen people set that up with the intention of now autovacuum will only run during our slow time!. I'm thinking it'd be worth mentioning in the docs that this won't work, and instead suggesting that they run vacuumdb -a or equivalent at that time instead. Thoughts? Hmmm... it seems to me that points new users towards not using autovacuum, which doesn't seem like the best idea. I think it'd be I think we could easily word it so that it's clear that just letting autovacuum do it's thing is preferred. better to say that setting the naptime really high is a Bad Idea. Instead, if they want to shift maintenances to off hours they should consider using a cron job that bonks around the pg_autovacuum.vac_base_thresh or vac_scale_factor values for tables they don't want vacuumed during operational hours (set them really high at the start of operational hours, then to normal during off hours). Tweaking the enable column would work too, but they presumably don't want to disable ANALYZE, although it's entirely likely that new users don't know what ANALYZE does, in which case they _really_ don't want to disable it. That sounds like a rather ugly solution, and one that would be hard to implement; not something to be putting in the docs. -- Jim Nasby [EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) pgpcTAxuATxrP.pgp Description: PGP signature
Re: [HACKERS] Autovacuum launcher doesn't notice death of postmaster immediately
Jim C. Nasby escribió: There *is* reason to allow setting the naptime smaller, though (or at least there was; perhaps Alvero's recent changes negate this need): clusters that have a large number of databases. I've worked with folks who are in a hosted environment and give each customer their own database; it's not hard to get a couple hundred databases that way. Setting the naptime higher than a second in such an environment would mean it could be hours before a database is checked for vacuuming. Yes, the code in HEAD is different -- each database will be considered separately. So the huge database taking all day to vacuum will not stop the tiny databases from being vacuumed in a timely manner. And the very huge table in that database will not stop the other tables in the database from being vacuumed either. There can be more than one worker in a single database. The limit is autovacuum_max_workers. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 1: 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] Autovacuum launcher doesn't notice death of postmaster immediately
Alvaro Herrera wrote: Jim C. Nasby escribió: There *is* reason to allow setting the naptime smaller, though (or at least there was; perhaps Alvero's recent changes negate this need): clusters that have a large number of databases. I've worked with folks who are in a hosted environment and give each customer their own database; it's not hard to get a couple hundred databases that way. Setting the naptime higher than a second in such an environment would mean it could be hours before a database is checked for vacuuming. Yes, the code in HEAD is different -- each database will be considered separately. So the huge database taking all day to vacuum will not stop the tiny databases from being vacuumed in a timely manner. And the very huge table in that database will not stop the other tables in the database from being vacuumed either. There can be more than one worker in a single database. Ok, but I think the question posed is that in say a virtual hosting environment there might be say 1,000 databases in the cluster. Am I still going to have to wait a long time for my database to get vacuumed? I don't think this has changed much no? (If default naptime is 1 minute, then autovacuum won't even look at a given database but once every 1,000 minutes (16.67 hours) assuming that there isn't enough work to keep all the workers busy.) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Patch review process; Warning to patch submitters
Simon Riggs wrote: On Fri, 2007-06-08 at 16:29 -0400, Bruce Momjian wrote: The bottom line is that the patch review process is hard enough, and we need the assistance of patch submitters to work with the patch reviewers in a timely manner. This includes me, so: Understood. There is a patch missing from the patch status page: http://archives.postgresql.org/pgsql-hackers/2007-03/msg01795.php http://archives.postgresql.org/pgsql-patches/2007-04/msg00250.php Should I add it myself? Sure, please do. -- Alvaro Herrera http://www.amazon.com/gp/registry/5ZYLFMCVHXC El sudor es la mejor cura para un pensamiento enfermo (Bardia) ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Autovacuum launcher doesn't notice death of postmaster immediately
Matthew T. O'Connor escribió: Ok, but I think the question posed is that in say a virtual hosting environment there might be say 1,000 databases in the cluster. Am I still going to have to wait a long time for my database to get vacuumed? I don't think this has changed much no? Depends on how much time it takes to vacuum the other 999 databases. The default max workers is 3. (If default naptime is 1 minute, then autovacuum won't even look at a given database but once every 1,000 minutes (16.67 hours) assuming that there isn't enough work to keep all the workers busy.) The naptime is per database. Which means if you have 1000 databases and a naptime of 60 seconds, the launcher is going to wake up every 100 milliseconds to check things up. (This results from 6 / 1000 = 60 ms, but there is a minimum of 100 ms just to keep things sane). If there are 3 workers and each of the 1000 databases in average takes 10 seconds to vacuum, there will be around 3000 seconds between autovac runs of your database assuming my math is right. I hope those 1000 databases you put in your shared hosting are not very big. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Patch review process; Warning to patch submitters
On Fri, 2007-06-08 at 16:29 -0400, Bruce Momjian wrote: The bottom line is that the patch review process is hard enough, and we need the assistance of patch submitters to work with the patch reviewers in a timely manner. This includes me, so: Understood. There is a patch missing from the patch status page: http://archives.postgresql.org/pgsql-hackers/2007-03/msg01795.php http://archives.postgresql.org/pgsql-patches/2007-04/msg00250.php Should I add it myself? -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 1: 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] Using the GPU
Billings, John [EMAIL PROTECTED] writes: Does anyone think that PostgreSQL could benefit from using the video card as a parallel computing device? I'm working on a project using Nvidia's CUDA with an 8800 series video card to handle non-graphical algorithms. I'm curious if anyone thinks that this technology could be used to speed up a database? If so which part of the database, and what kind of parallel algorithms would be used? There has been some interesting research on sorting using the GPU which could be very interesting for databases. However I think Postgres would be unlikely to go the route of having compiled driver code for every possible video card. It's unlikely to be interesting for database developers until there's some abstract interface designed for these kinds of optimizations which it can use without caring about the specific graphics card. Perhaps this can be done using OpenGL already but I kind of doubt it. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Command tags in create/drop scripts
On Tue, Jun 05, 2007 at 05:52:39PM -, Andrew Hammond wrote: On Jun 5, 9:19 am, [EMAIL PROTECTED] (Alvaro Herrera) wrote: Zdenek Kotala wrote: Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Is this a TODO? I don't think so; there is no demand from anybody but Zdenek to remove those programs. Has it ever even come up before? Personally I found really strange to have createuser and createdb shipped by Postgres when I started using it. I just didn't complain. +1. Given the prevalence of the pg_foo convention, those names are clunky. So is initdb. I'm less creative than Zdenek, so I'd suggest simply renaming to pg_createuser and friends with the same command line options as the originals. Have the binaries check $0 and emit a warning about using the deprecated name to STDERR if called by a name that doesn't have the pg_ prefix. Default to symlinking the old names for backwards compatibility until 9.0. +1 -- Jim Nasby [EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) pgpFvOvoY19iH.pgp Description: PGP signature
[HACKERS] Issues with factorial operator
I'm working with a customer that recently discovered that some code had generated the following nice query... SELECT ... WHERE table_id = 92838278! AND ... So their production server now has several processes that are trying to compute some absurdly large factorial. There's two issues here: 1) the computation doesn't check for signals. This means both a plain kill and pg_cancel_backend() are useless. 2) Even though the answer is going to be an obscene number of digits, and that's supposed to be fed into a numeric, there's no overflow or bounds checking occurring. This is true even if I store into a field defined as numeric: decibel=# create table n(n numeric); CREATE TABLE decibel=# insert into n select !; INSERT 0 1 decibel=# select char_length(trim(n, '0')) from n; char_length - 9466 (1 row) So at the very least the documentation is confusing: The type numeric can store numbers with up to 1000 digits of precision and perform calculations exactly. ... Specifying NUMERIC without any precision or scale creates a column in which numeric values of any precision and scale can be stored, up to the implementation limit on precision. Yet here we have a numeric that's storing nearly 10,000 digits of precision. -- Jim Nasby [EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) pgpy96qgWmWSR.pgp Description: PGP signature
Re: [HACKERS] Command tags in create/drop scripts
Jim C. Nasby wrote: On Tue, Jun 05, 2007 at 05:52:39PM -, Andrew Hammond wrote: On Jun 5, 9:19 am, [EMAIL PROTECTED] (Alvaro Herrera) wrote: Zdenek Kotala wrote: Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Is this a TODO? I don't think so; there is no demand from anybody but Zdenek to remove those programs. Has it ever even come up before? Personally I found really strange to have createuser and createdb shipped by Postgres when I started using it. I just didn't complain. +1. Given the prevalence of the pg_foo convention, those names are clunky. So is initdb. I'm less creative than Zdenek, so I'd suggest simply renaming to pg_createuser and friends with the same command line options as the originals. Have the binaries check $0 and emit a warning about using the deprecated name to STDERR if called by a name that doesn't have the pg_ prefix. Default to symlinking the old names for backwards compatibility until 9.0. +1 +1 -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Autovacuum launcher doesn't notice death of postmaster immediately
Alvaro Herrera wrote: Matthew T. O'Connor escribió: Ok, but I think the question posed is that in say a virtual hosting environment there might be say 1,000 databases in the cluster. That is uhmmm insane... 1000 databases? Joshua D. Drake Am I still going to have to wait a long time for my database to get vacuumed? I don't think this has changed much no? Depends on how much time it takes to vacuum the other 999 databases. The default max workers is 3. (If default naptime is 1 minute, then autovacuum won't even look at a given database but once every 1,000 minutes (16.67 hours) assuming that there isn't enough work to keep all the workers busy.) The naptime is per database. Which means if you have 1000 databases and a naptime of 60 seconds, the launcher is going to wake up every 100 milliseconds to check things up. (This results from 6 / 1000 = 60 ms, but there is a minimum of 100 ms just to keep things sane). If there are 3 workers and each of the 1000 databases in average takes 10 seconds to vacuum, there will be around 3000 seconds between autovac runs of your database assuming my math is right. I hope those 1000 databases you put in your shared hosting are not very big. -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Issues with factorial operator
It makes sense with factorial function to do an error check on the domain. Calculate beforehand, and figure out what the largest sensible domain value is. For instance, in Maple, I get this: y:=92838278!; Error, object too large The error message returns instantly. For reasonably large values, it might make sense to pre-compute factorials and store them in an array. It should also be possible to store 1/2 of Pascal's triangle in memory and demand load that memory segment the first time someone asks for factorials or combinations or permutations. Just a thought. -Original Message- From: [EMAIL PROTECTED] [mailto:pgsql-hackers- [EMAIL PROTECTED] On Behalf Of Jim C. Nasby Sent: Friday, June 08, 2007 6:45 PM To: pgsql-hackers@postgresql.org Subject: [HACKERS] Issues with factorial operator I'm working with a customer that recently discovered that some code had generated the following nice query... SELECT ... WHERE table_id = 92838278! AND ... So their production server now has several processes that are trying to compute some absurdly large factorial. There's two issues here: 1) the computation doesn't check for signals. This means both a plain kill and pg_cancel_backend() are useless. 2) Even though the answer is going to be an obscene number of digits, and that's supposed to be fed into a numeric, there's no overflow or bounds checking occurring. This is true even if I store into a field defined as numeric: decibel=# create table n(n numeric); CREATE TABLE decibel=# insert into n select !; INSERT 0 1 decibel=# select char_length(trim(n, '0')) from n; char_length - 9466 (1 row) So at the very least the documentation is confusing: The type numeric can store numbers with up to 1000 digits of precision and perform calculations exactly. ... Specifying NUMERIC without any precision or scale creates a column in which numeric values of any precision and scale can be stored, up to the implementation limit on precision. Yet here we have a numeric that's storing nearly 10,000 digits of precision. -- Jim Nasby [EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Autovacuum launcher doesn't notice death of postmaster immediately
-Original Message- From: [EMAIL PROTECTED] [mailto:pgsql-hackers- [EMAIL PROTECTED] On Behalf Of Joshua D. Drake Sent: Friday, June 08, 2007 10:49 PM To: Alvaro Herrera Cc: Matthew T. O'Connor; Jim C. Nasby; Michael Paesold; Tom Lane; Andrew Hammond; Peter Eisentraut; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Autovacuum launcher doesn't notice death of postmaster immediately Alvaro Herrera wrote: Matthew T. O'Connor escribió: Ok, but I think the question posed is that in say a virtual hosting environment there might be say 1,000 databases in the cluster. That is uhmmm insane... 1000 databases? Not in a test environment. We have several hundred databases here. Of course, only a few dozen (or at most ~100) are of any one type, but I can imagine that under certain circumstances 1000 databases would not be unreasonable. [snip] ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
