[HACKERS] Server maintenance
postgresql01.managed.contegix.com and therefore developer.pgadmin.org and nagios.pgadmin.org will be going down for maintenance sometime shortly after 9AM GMT today. Downtime is expected to be around 15 minutes. Apologies for the short notice. Regards, Dave ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] pgsql: New versions of mingw have gettimeofday(), so add an autoconf
Magnus Hagander wrote: Log Message: --- New versions of mingw have gettimeofday(), so add an autoconf test for this. Can we backport this fix? I'm trying to setup a new windows build environment and this is currently halting my progress for back branches. Kris Jurka ---(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] Spinlock backoff algorithm
Gregory Stark wrote: Tom Lane [EMAIL PROTECTED] writes: Mark Mielke [EMAIL PROTECTED] writes: Tom Lane wrote: My goodness that's a hardware-dependent proposal. Shall we discuss how many CPUs there are where an integer division is *slower* than a floating-point op? Do you have one in mind, or is this a straw man? :-) I've got one upstairs (HPPA), and I believe that it's actually a pretty common situation in scientifically-oriented workstations from a few years back. I think floating point is fast on many common platforms, even many i386 variants. But usually that's assuming you're comparing doing a whole bunch of work in floating point or integer math. Converting a bunch of integers to floating point for a single operation doesn't seem like a case that's going to shine on any floating point unit. Just for fullness, task context switch is more complex (slower) when application uses FP operation. It is not important for PostgreSQL because it has FP operation on many places, but it is good to know. Zdenek ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] VACUUM/ANALYZE counting of in-doubt tuples
Tom Lane wrote: On further thought though, that's not the whole story, and in fact VACUUM itself isn't doing very well at accounting for in-doubt tuples. The current implementation is that whatever live and dead tuple totals are arrived at by a VACUUM or ANALYZE are sent to the stats collector and simply overwrite its counters on arrival. Meanwhile, the transaction that is responsible for an in-doubt tuple will send a stats message to increment either the live-tuple or dead-tuple count as appropriate when it commits or aborts. If that happens before the VACUUM or ANALYZE completes, the increment will get overwritten by VACUUM/ANALYZE's total; if afterwards, the increment will get added onto the total. So ideally we'd count the state change as already done if we knew the other transaction would commit first, otherwise not. How about this: let's have VACUUM send a message at the start of processing the table. pgstats saves the current counters for the table somewhere and resets them to zero; and any transaction that sends messages after that is counted to the new counter. When vacuum finishes and commits, it sends another message and pgstats forgets the counters it saved. At this point, the count of dead tuples will be correct. (If during vacuum anyone retrieves the number of dead tuples, the logical thing would be to report the saved counter). If vacuum aborts, it sends a message saying so and pgstats restores the saved counter, adding whatever has been accumulated on the other counter during the vacuum. If the system crashes there is no problem because the stats are reset anyway. -- Alvaro Herrera http://www.amazon.com/gp/registry/CTMLCN8V17R4 Este mail se entrega garantizadamente 100% libre de sarcasmo. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] LDC - Load Distributed Checkpoints with PG8.3b2 on Solaris
Gregory Stark wrote: Heikki Linnakangas [EMAIL PROTECTED] writes: Tom Lane wrote: Looking at the autovacuum log output, 2007-11-13 09:21:19.830 PST 9458 LOG: automatic vacuum of table specdb.public.txn_log_table: index scans: 1 pages: 11 removed, 105 remain tuples: 3147 removed, 40 remain system usage: CPU 0.11s/0.09u sec elapsed 6.02 sec it seems like a serious omission that this gives you no hint how many pages were scanned. Isn't it pages removed + remain? 116 in this case. How do 40 tuples take 105 pages? The first 65 pages could be empty. -- Alvaro Herrera http://www.PlanetPostgreSQL.org/ Bob [Floyd] used to say that he was planning to get a Ph.D. by the green stamp method, namely by saving envelopes addressed to him as 'Dr. Floyd'. After collecting 500 such letters, he mused, a university somewhere in Arizona would probably grant him a degree. (Don Knuth) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] VACUUM/ANALYZE counting of in-doubt tuples
Alvaro Herrera [EMAIL PROTECTED] writes: Tom Lane wrote: On further thought though, that's not the whole story, and in fact VACUUM itself isn't doing very well at accounting for in-doubt tuples. How about this: let's have VACUUM send a message at the start of processing the table. pgstats saves the current counters for the table somewhere and resets them to zero; and any transaction that sends messages after that is counted to the new counter. When vacuum finishes and commits, it sends another message and pgstats forgets the counters it saved. At this point, the count of dead tuples will be correct. (If during vacuum anyone retrieves the number of dead tuples, the logical thing would be to report the saved counter). No, that doesn't work (not to mention that adding two more counters per-table will be a lot of bloat for the stats tables). The race conditions are a lot more subtle than that. The stats collector cannot know when it receives a tabstat message after VACUUM starts whether VACUUM has/will see the tuples involved, or whether it will see them as committed or not. That would depend on whether VACUUM has yet reached the page(s) the tuples are in. (Conversely tabstats arriving shortly after the VACUUM completion report might or might not correspond to tuples seen by VACUUM, though neither your proposal nor mine tries to address that.) AFAICS the only way to be 100% correct would be to track live/dead counts on a page-by-page basis, which is obviously impractical. (And I'm not sure even that works, given the possibility of stats messages arriving at the collector out-of-order compared to the actual page-changing operations.) So we have to settle for an approximation, and that being the case I'd rather not have an expensive approximation. 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] VACUUM/ANALYZE counting of in-doubt tuples
On Mon, 2007-11-19 at 10:38 -0500, Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: Tom Lane wrote: On further thought though, that's not the whole story, and in fact VACUUM itself isn't doing very well at accounting for in-doubt tuples. How about this: let's have VACUUM send a message at the start of processing the table. pgstats saves the current counters for the table somewhere and resets them to zero; and any transaction that sends messages after that is counted to the new counter. When vacuum finishes and commits, it sends another message and pgstats forgets the counters it saved. At this point, the count of dead tuples will be correct. (If during vacuum anyone retrieves the number of dead tuples, the logical thing would be to report the saved counter). No, that doesn't work (not to mention that adding two more counters per-table will be a lot of bloat for the stats tables). The race conditions are a lot more subtle than that. The stats collector cannot know when it receives a tabstat message after VACUUM starts whether VACUUM has/will see the tuples involved, or whether it will see them as committed or not. That would depend on whether VACUUM has yet reached the page(s) the tuples are in. (Conversely tabstats arriving shortly after the VACUUM completion report might or might not correspond to tuples seen by VACUUM, though neither your proposal nor mine tries to address that.) AFAICS the only way to be 100% correct would be to track live/dead counts on a page-by-page basis, which is obviously impractical. (And I'm not sure even that works, given the possibility of stats messages arriving at the collector out-of-order compared to the actual page-changing operations.) So we have to settle for an approximation, and that being the case I'd rather not have an expensive approximation. I think the before-and-after approach can be made to work: VACUUM just needs to save the counter in memory, it doesn't need to write that anywhere else. VACUUM can force the flush of the tabstat file so that there is no race condition, or at least a minimised one. We need only do that for larger tables where the chance of concurrent deletes is high enough that its worth making this special correction for. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] VACUUM/ANALYZE counting of in-doubt tuples
Simon Riggs [EMAIL PROTECTED] writes: On Mon, 2007-11-19 at 10:38 -0500, Tom Lane wrote: The race conditions are a lot more subtle than that. The stats collector cannot know when it receives a tabstat message after VACUUM starts whether VACUUM has/will see the tuples involved, or whether it will see them as committed or not. That would depend on whether VACUUM has yet reached the page(s) the tuples are in. I think the before-and-after approach can be made to work: VACUUM just needs to save the counter in memory, it doesn't need to write that anywhere else. VACUUM can force the flush of the tabstat file so that there is no race condition, or at least a minimised one. I don't think you understood what I said at all. The race condition is not before vs after VACUUM starts, it is before vs after when VACUUM scans the page that the in-doubt tuple is in. 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] VACUUM/ANALYZE counting of in-doubt tuples
On Mon, 2007-11-19 at 13:33 -0500, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: On Mon, 2007-11-19 at 10:38 -0500, Tom Lane wrote: The race conditions are a lot more subtle than that. The stats collector cannot know when it receives a tabstat message after VACUUM starts whether VACUUM has/will see the tuples involved, or whether it will see them as committed or not. That would depend on whether VACUUM has yet reached the page(s) the tuples are in. I think the before-and-after approach can be made to work: VACUUM just needs to save the counter in memory, it doesn't need to write that anywhere else. VACUUM can force the flush of the tabstat file so that there is no race condition, or at least a minimised one. I don't think you understood what I said at all. The race condition is not before vs after VACUUM starts, it is before vs after when VACUUM scans the page that the in-doubt tuple is in. I thought we were looking for heuristics, not exact accuracy? I understand the visibility issues. Right now the larger the table the more likely we will get deletes start and complete while we are doing the VACUUM. So right now, the larger the table the larger the error, assuming a constant workload. Large VACUUMs are currently not sweeping up all the rows they could do, as Heikki's patch for re-evaluating xmin and associated test results showed. Those are the same rows that we are currently ignoring now. Carrying on ignoring them probably isn't the right thing to do, even if the exactly right thing to do isn't fully knowable. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Terminal width for help output
Alvaro Herrera a écrit : Peter Eisentraut wrote: Do we care to maintain a maximum width for programs' --help output (and psql's \?)? I think 79 characters was once a recommendation (or perhaps 72), but we have a couple of violations either way, which I'd like to fix, but what to? 79 is perfect IMHO. It would be great to ask translators to preserve the constraint too. I wasn't aware of the constraint. Now, I'll do it. Are there other constraints that translators should know about ? Also it would rock if translation kept the alignment in various output. For example, avoid stuff like $ pg_controldata Numéro de version de pg_control : 833 Numéro de version du catalogue : 200710232 Identifiant du système de base de données : 5125738698090412341 Etat du groupe de base de données : arrêt Dernière modification de pg_control : jeu 15 nov 2007 08:17:22 CLST Dernière localisation du point de contrôle: 0/4D06F8 Précédente localisation du point de contrôle: 0/4B9818 Dernière localisation de reprise du point de contrôle : 0/4D06F8 Dernier TimeLineID du point de vérification : 1 Dernier NextXID du point de contrôle : 0/395 Dernier NextXOID du point de contrôle : 16407 Dernier NextMultiXactId du point de contrôle : 1 Dernier NextMultiOffset du point de contrôle : 0 Heure du dernier point de contrôle : jeu 15 nov 2007 08:17:22 CLST Emplacement de fin de la récupération minimale: 0/0 Alignement maximale de la donnée :8 Taille de bloc de la base de données : 8192 Blocs par segment pour une relation importante : 131072 Taille de bloc du WAL : 8192 Octets par segment WAL : 16777216 Taille maximale des identifiants :64 Nombre de colonnes maximum d'un index: 32 Maximum size of a TOAST chunk:1996 Stockage du type date/heure : nombres à virgule flottante Taille maximum d'un nom local : 128 LC_COLLATE : fr_CA.UTF-8 LC_CTYPE :fr_CA.UTF-8 +1 Thanks for the tips. -- Guillaume. http://www.postgresqlfr.org http://dalibo.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Terminal width for help output
On Thu, Nov 15, 2007 at 06:56:06PM -0300, Alvaro Herrera wrote: Peter Eisentraut wrote: Do we care to maintain a maximum width for programs' --help output (and psql's \?)? I think 79 characters was once a recommendation (or perhaps 72), but we have a couple of violations either way, which I'd like to fix, but what to? 79 is perfect IMHO. It would be great to ask translators to preserve the constraint too. I thought that 79 was good because if output was copied/pasted into an email that didn't fold lines nicely then output wouldn't get immediately mangled. Sam ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
