[PATCHES] Packed Varlenas update
. Fixed all regressions in contrib (gist indexed) modules . Fixed a bug that occurs when a compressed datum ends up under 128 bytes . Fixed a bug on zero-column tuples . Added regression tests http://community.enterprisedb.com/varlena/patch-varvarlena-16.patch.gz -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[PATCHES] log_autovacuum
log_autovacuum = on produces a single line of output from autovacuum,
with additional useful stats. Patch is proving useful in performance
testing. Not sure what is intended on logging for 8.3
LOG: autovac public.w scans:1 pages:197(-0) tuples:2338(-7199) CPU
0.00s/0.00u sec elapsed 0.39 sec
LOG: autovac public.s scans:1 pages:1926746(-0)
tuples:37000611(-3461867) CPU 99.74s/53.37u sec elapsed 7977.20 sec
No docs yet, but will do this if accepted.
scans: Nnumber of times indexes have been scanned
pages: remaining(-removed)
tuples: remaining(-removed)
CPU
elapsed
--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com
Index: src/backend/commands/vacuumlazy.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/commands/vacuumlazy.c,v
retrieving revision 1.85
diff -c -r1.85 vacuumlazy.c
*** src/backend/commands/vacuumlazy.c 21 Feb 2007 22:47:45 - 1.85
--- src/backend/commands/vacuumlazy.c 8 Mar 2007 15:51:06 -
***
*** 49,54
--- 49,55
#include access/transam.h
#include commands/vacuum.h
#include miscadmin.h
+ #include postmaster/autovacuum.h
#include pgstat.h
#include storage/freespace.h
#include utils/lsyscache.h
***
*** 90,95
--- 91,97
int max_free_pages; /* # slots allocated in array */
PageFreeSpaceInfo *free_pages; /* array or heap of blkno/avail */
BlockNumber tot_free_pages; /* total pages with = threshold space */
+ int num_index_scans; /* number of scans of index */
} LVRelStats;
***
*** 141,146
--- 143,151
Relation *Irel;
int nindexes;
BlockNumber possibly_freeable;
+ PGRUsage ru0;
+
+ pg_rusage_init(ru0);
if (vacstmt-verbose)
elevel = INFO;
***
*** 156,161
--- 161,168
/* XXX should we scale it up or down? Adjust vacuum.c too, if so */
vacrelstats-threshold = GetAvgFSMRequestSize(onerel-rd_node);
+ vacrelstats-num_index_scans = 0;
+
/* Open all indexes of the relation */
vac_open_indexes(onerel, RowExclusiveLock, nindexes, Irel);
vacrelstats-hasindex = (nindexes 0);
***
*** 200,205
--- 207,220
/* report results to the stats collector, too */
pgstat_report_vacuum(RelationGetRelid(onerel), onerel-rd_rel-relisshared,
vacstmt-analyze, vacrelstats-rel_tuples);
+ if (Log_autovacuum IsAutoVacuumProcess())
+ ereport(LOG,
+ (errmsg(autovac \%s.%s\ scans:%d pages:%d(-%d) tuples:%.0f(-%.0f) %s,
+ get_namespace_name(RelationGetNamespace(onerel)),
+ RelationGetRelationName(onerel), vacrelstats-num_index_scans,
+ vacrelstats-rel_pages, vacrelstats-pages_removed,
+ vacrelstats-rel_tuples, vacrelstats-tuples_deleted,
+ pg_rusage_show(ru0;
}
***
*** 282,287
--- 297,303
lazy_vacuum_heap(onerel, vacrelstats);
/* Forget the now-vacuumed tuples, and press on */
vacrelstats-num_dead_tuples = 0;
+ vacrelstats-num_index_scans++;
}
buf = ReadBuffer(onerel, blkno);
***
*** 490,495
--- 506,512
vacrelstats);
/* Remove tuples from heap */
lazy_vacuum_heap(onerel, vacrelstats);
+ vacrelstats-num_index_scans++;
}
/* Do post-vacuum cleanup and statistics update for each index */
Index: src/backend/postmaster/autovacuum.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/postmaster/autovacuum.c,v
retrieving revision 1.33
diff -c -r1.33 autovacuum.c
*** src/backend/postmaster/autovacuum.c 7 Mar 2007 13:35:02 - 1.33
--- src/backend/postmaster/autovacuum.c 8 Mar 2007 15:51:07 -
***
*** 69,74
--- 69,76
int autovacuum_vac_cost_delay;
int autovacuum_vac_cost_limit;
+ bool Log_autovacuum = false;
+
/* Flag to tell if we are in the autovacuum daemon process */
static bool am_autovacuum_launcher = false;
static bool am_autovacuum_worker = false;
Index: src/backend/utils/misc/guc.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/utils/misc/guc.c,v
retrieving revision 1.379
diff -c -r1.379 guc.c
*** src/backend/utils/misc/guc.c 6 Mar 2007 02:06:14 - 1.379
--- src/backend/utils/misc/guc.c 8 Mar 2007 15:51:11 -
***
*** 584,589
--- 584,597
false, NULL, NULL
},
{
+ {log_autovacuum, PGC_BACKEND, LOGGING_WHAT,
+ gettext_noop(Logs end of a session, including duration.),
+ NULL
+ },
+ Log_autovacuum,
+ false, NULL, NULL
+ },
+ {
{debug_assertions, PGC_USERSET, DEVELOPER_OPTIONS,
gettext_noop(Turns on various assertion checks.),
gettext_noop(This is a debugging aid.),
Index: src/include/postmaster/autovacuum.h
===
RCS file:
Re: [PATCHES] Heap page diagnostic/test functions (v2)
On Thu, 2007-03-08 at 15:44 +, Simon Riggs wrote: Docs included Just noticed a typo. File mentioned in func.sgml, line 11049 should be filenamesrc/include/access/htup.h/ and not filenamesrc/include/storage/bufpage.h/ -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] log_autovacuum
On Thu, 2007-03-08 at 16:05 +, Simon Riggs wrote: LOG: autovac public.w scans:1 pages:197(-0) tuples:2338(-7199) CPU 0.00s/0.00u sec elapsed 0.39 sec Seems like a pretty cryptic log format to me. -Neil ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] log_autovacuum
Simon Riggs wrote: log_autovacuum = on produces a single line of output from autovacuum, with additional useful stats. Patch is proving useful in performance testing. Not sure what is intended on logging for 8.3 LOG: autovac public.w scans:1 pages:197(-0) tuples:2338(-7199) CPU 0.00s/0.00u sec elapsed 0.39 sec LOG: autovac public.s scans:1 pages:1926746(-0) tuples:37000611(-3461867) CPU 99.74s/53.37u sec elapsed 7977.20 sec I agree something like this is useful, but I'd try to get rid of cryptic notation, and maybe split in several message categories if reasonable. Your proposal is LOG: autovac public.w scans:1 pages:197(-0) tuples:2338(-7199) CPU 0.00s/0.00u sec elapsed 0.39 sec This looks too much like the old VACUUM VERBOSE reporting, which was awful. Maybe something like this is better: LOG: index passes: 1 pages: removed 0, 197 remain tuples: removed 7199, 2338 remain CPU usage: whatever CONTEXT: Automatic vacuuming of table database.public.w This looks like the sort of thing comparable to picking a bikeshed color though :-( Keep in mind that it's going to be translated, so it's not useful for machine parsing anyway. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] log_autovacuum
On March 8, 2007 09:53 am, Alvaro Herrera wrote: Simon Riggs wrote: log_autovacuum = on produces a single line of output from autovacuum, with additional useful stats. Patch is proving useful in performance testing. Not sure what is intended on logging for 8.3 LOG: autovac public.w scans:1 pages:197(-0) tuples:2338(-7199) CPU 0.00s/0.00u sec elapsed 0.39 sec LOG: autovac public.s scans:1 pages:1926746(-0) tuples:37000611(-3461867) CPU 99.74s/53.37u sec elapsed 7977.20 sec I agree something like this is useful, but I'd try to get rid of cryptic notation, and maybe split in several message categories if reasonable. Your proposal is LOG: autovac public.w scans:1 pages:197(-0) tuples:2338(-7199) CPU 0.00s/0.00u sec elapsed 0.39 sec This looks too much like the old VACUUM VERBOSE reporting, which was awful. Maybe something like this is better: LOG: index passes: 1 pages: removed 0, 197 remain tuples: removed 7199, 2338 remain CPU usage: whatever CONTEXT: Automatic vacuuming of table database.public.w This looks like the sort of thing comparable to picking a bikeshed color though :-( Keep in mind that it's going to be translated, so it's not useful for machine parsing anyway. This goes back to the request for vacuum loging to a table.. -- Darcy Buskermolen Command Prompt, Inc. Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] log_autovacuum
Darcy Buskermolen wrote: On March 8, 2007 09:53 am, Alvaro Herrera wrote: Keep in mind that it's going to be translated, so it's not useful for machine parsing anyway. This goes back to the request for vacuum loging to a table.. That's right, but please let's have at least *something*. -- 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: [PATCHES] log_autovacuum
On Thu, 2007-03-08 at 14:53 -0300, Alvaro Herrera wrote:
Maybe something like this is better:
LOG: index passes: 1 pages: removed 0, 197 remain tuples: removed 7199,
2338 remain CPU usage: whatever
CONTEXT: Automatic vacuuming of table database.public.w
Yours is better.
I've implemented this:
LOG: autovac public.w index passes: 1 pages: removed 0, 197 remain
tuples: removed 7199, 2338 remain CPU usage: whatever
I'm happy if this gets removed later, but I think it will help everybody
understand how multi-vacuums are working and what the best way to
specify the controls should be.
Not sure about the CONTEXT bit. I think its verbose, plus I thought that
was for ERRORs only. I will defer on this point, since I know y'all
understand that better than I.
--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com
Index: src/backend/commands/vacuumlazy.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/commands/vacuumlazy.c,v
retrieving revision 1.85
diff -c -r1.85 vacuumlazy.c
*** src/backend/commands/vacuumlazy.c 21 Feb 2007 22:47:45 - 1.85
--- src/backend/commands/vacuumlazy.c 8 Mar 2007 18:27:45 -
***
*** 49,54
--- 49,55
#include access/transam.h
#include commands/vacuum.h
#include miscadmin.h
+ #include postmaster/autovacuum.h
#include pgstat.h
#include storage/freespace.h
#include utils/lsyscache.h
***
*** 90,95
--- 91,97
int max_free_pages; /* # slots allocated in array */
PageFreeSpaceInfo *free_pages; /* array or heap of blkno/avail */
BlockNumber tot_free_pages; /* total pages with = threshold space */
+ int num_index_scans; /* number of scans of index */
} LVRelStats;
***
*** 141,146
--- 143,151
Relation *Irel;
int nindexes;
BlockNumber possibly_freeable;
+ PGRUsage ru0;
+
+ pg_rusage_init(ru0);
if (vacstmt-verbose)
elevel = INFO;
***
*** 156,161
--- 161,168
/* XXX should we scale it up or down? Adjust vacuum.c too, if so */
vacrelstats-threshold = GetAvgFSMRequestSize(onerel-rd_node);
+ vacrelstats-num_index_scans = 0;
+
/* Open all indexes of the relation */
vac_open_indexes(onerel, RowExclusiveLock, nindexes, Irel);
vacrelstats-hasindex = (nindexes 0);
***
*** 200,205
--- 207,220
/* report results to the stats collector, too */
pgstat_report_vacuum(RelationGetRelid(onerel), onerel-rd_rel-relisshared,
vacstmt-analyze, vacrelstats-rel_tuples);
+ if (Log_autovacuum IsAutoVacuumProcess())
+ ereport(LOG,
+ (errmsg(autovacuum \%s.%s.%s\ index scans:%d pages: removed %d, %d remain tuples:%.0f removed, -%.0f remain %s,
+ get_database_name(MyDatabaseId),get_namespace_name(RelationGetNamespace(onerel)),
+ RelationGetRelationName(onerel), vacrelstats-num_index_scans,
+ vacrelstats-pages_removed, vacrelstats-rel_pages,
+ vacrelstats-tuples_deleted, vacrelstats-rel_tuples,
+ pg_rusage_show(ru0;
}
***
*** 282,287
--- 297,303
lazy_vacuum_heap(onerel, vacrelstats);
/* Forget the now-vacuumed tuples, and press on */
vacrelstats-num_dead_tuples = 0;
+ vacrelstats-num_index_scans++;
}
buf = ReadBuffer(onerel, blkno);
***
*** 490,495
--- 506,512
vacrelstats);
/* Remove tuples from heap */
lazy_vacuum_heap(onerel, vacrelstats);
+ vacrelstats-num_index_scans++;
}
/* Do post-vacuum cleanup and statistics update for each index */
Index: src/backend/postmaster/autovacuum.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/postmaster/autovacuum.c,v
retrieving revision 1.33
diff -c -r1.33 autovacuum.c
*** src/backend/postmaster/autovacuum.c 7 Mar 2007 13:35:02 - 1.33
--- src/backend/postmaster/autovacuum.c 8 Mar 2007 18:27:45 -
***
*** 69,74
--- 69,76
int autovacuum_vac_cost_delay;
int autovacuum_vac_cost_limit;
+ bool Log_autovacuum = false;
+
/* Flag to tell if we are in the autovacuum daemon process */
static bool am_autovacuum_launcher = false;
static bool am_autovacuum_worker = false;
Index: src/backend/utils/misc/guc.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/utils/misc/guc.c,v
retrieving revision 1.379
diff -c -r1.379 guc.c
*** src/backend/utils/misc/guc.c 6 Mar 2007 02:06:14 - 1.379
--- src/backend/utils/misc/guc.c 8 Mar 2007 18:27:48 -
***
*** 584,589
--- 584,597
false, NULL, NULL
},
{
+ {log_autovacuum, PGC_BACKEND, LOGGING_WHAT,
+ gettext_noop(Logs end of a session, including duration.),
+ NULL
+ },
+ Log_autovacuum,
+ false, NULL, NULL
+ },
+ {
{debug_assertions, PGC_USERSET, DEVELOPER_OPTIONS,
Re: [PATCHES] xpath_array with namespaces support
On 3/3/07, Bruce Momjian [EMAIL PROTECTED] wrote: I tried this patch bug found this regression failure: -- Considering only built-in procs (prolang = 12), look for multiple uses -- of the same internal function (ie, matching prosrc fields). It's OK to -- have several entries with different pronames for the same internal function, -- but conflicts in the number of arguments and other critical items should -- be complained of. (We don't check data types here; see next query.) -- Note: ignore aggregate functions here, since they all point to the same -- dummy built-in function. SELECT p1.oid, p1.proname, p2.oid, p2.proname FROM pg_proc AS p1, pg_proc AS p2 WHERE p1.oid p2.oid AND p1.prosrc = p2.prosrc AND p1.prolang = 12 AND p2.prolang = 12 AND (p1.proisagg = false OR p2.proisagg = false) AND (p1.prolang != p2.prolang OR p1.proisagg != p2.proisagg OR p1.prosecdef != p2.prosecdef OR p1.proisstrict != p2.proisstrict OR p1.proretset != p2.proretset OR p1.provolatile != p2.provolatile OR p1.pronargs != p2.pronargs); oid | proname | oid | proname --+-+--+- 2931 | xpath_array | 2932 | xpath_array (1 row) This is because you are calling xpath_array with 2 and 3 arguments. Seems we don't do this anywhere else. I also had to add a #ifdef USE_LIBXML around xml_xmlnodetotext(). Please research a fix to this an resubmit. Thanks. OK. I'll fix these issues and extend the patch with resgression tests and docs for xpath_array(). I'll resubmit it very soon. -- Best regards, Nikolay ---(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: [PATCHES] Patch for pgstatindex to fix a bug reporting a value of strange leaf_fragmentation
Tatsuhito Kasahara wrote:
Hello.
I found a bug in contrib/pgstatindex.c to reports a strange value of
leaf_fragmentation with some cases.
#Look the following test example.
In GetBTPageStatistics(), stat-fragments is not initialized properly
so that invalid leaf_fragments values are inserted in results.
Right. Checking that code, it seems btpo.xact is also being incorrectly
handled, is it not? Incidentally, the return value seems a bit useless.
--
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
Index: pgstatindex.c
===
RCS file: /home/alvherre/Code/cvs/pgsql/contrib/pgstattuple/pgstatindex.c,v
retrieving revision 1.2
diff -c -p -r1.2 pgstatindex.c
*** pgstatindex.c 4 Sep 2006 02:03:04 - 1.2
--- pgstatindex.c 9 Mar 2007 03:16:30 -
*** typedef struct BTIndexStat
*** 139,145
* Collect statistics of single b-tree leaf page
* -
*/
! static bool
GetBTPageStatistics(BlockNumber blkno, Buffer buffer, BTPageStat * stat)
{
Page page = BufferGetPage(buffer);
--- 139,145
* Collect statistics of single b-tree leaf page
* -
*/
! static void
GetBTPageStatistics(BlockNumber blkno, Buffer buffer, BTPageStat * stat)
{
Page page = BufferGetPage(buffer);
*** GetBTPageStatistics(BlockNumber blkno, B
*** 154,159
--- 154,160
stat-max_avail = BLCKSZ - (BLCKSZ - phdr-pd_special + SizeOfPageHeaderData);
stat-dead_items = stat-live_items = 0;
+ stat-fragments = 0;
stat-page_size = PageGetPageSize(page);
*** GetBTPageStatistics(BlockNumber blkno, B
*** 161,167
if (P_ISDELETED(opaque))
{
stat-type = 'd';
! return true;
}
else if (P_IGNORE(opaque))
stat-type = 'e';
--- 162,169
if (P_ISDELETED(opaque))
{
stat-type = 'd';
! stat-btpo.xact = opaque-btpo.xact;
! return;
}
else if (P_IGNORE(opaque))
stat-type = 'e';
*** GetBTPageStatistics(BlockNumber blkno, B
*** 175,184
/* btpage opaque data */
stat-btpo_prev = opaque-btpo_prev;
stat-btpo_next = opaque-btpo_next;
! if (P_ISDELETED(opaque))
! stat-btpo.xact = opaque-btpo.xact;
! else
! stat-btpo.level = opaque-btpo.level;
stat-btpo_flags = opaque-btpo_flags;
stat-btpo_cycleid = opaque-btpo_cycleid;
--- 177,183
/* btpage opaque data */
stat-btpo_prev = opaque-btpo_prev;
stat-btpo_next = opaque-btpo_next;
! stat-btpo.level = opaque-btpo.level;
stat-btpo_flags = opaque-btpo_flags;
stat-btpo_cycleid = opaque-btpo_cycleid;
*** GetBTPageStatistics(BlockNumber blkno, B
*** 187,193
* it means a fragmentation.
*--
*/
- stat-fragments = 0;
if (stat-type == 'l')
{
if (opaque-btpo_next != P_NONE opaque-btpo_next blkno)
--- 186,191
*** GetBTPageStatistics(BlockNumber blkno, B
*** 216,223
stat-avg_item_size = item_size / (stat-live_items + stat-dead_items);
else
stat-avg_item_size = 0;
-
- return true;
}
--- 214,219
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] Patch for pgstatindex to fix a bug reporting a value of strange leaf_fragmentation
Alvaro Herrera wrote: Right. Checking that code, it seems btpo.xact is also being incorrectly handled, is it not? Incidentally, the return value seems a bit useless. Then again, this looks bogus as well. (Sorry for the unidiff -- I had to use interdiff to get it). -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support diff -u pgstatindex.c pgstatindex.c --- pgstatindex.c 9 Mar 2007 03:16:30 - +++ pgstatindex.c 9 Mar 2007 03:31:28 - @@ -334,8 +334,7 @@ int j; char *values[PGSTATINDEX_NCOLUMNS]; - HeapTupleData tupleData; - HeapTuple tuple = tupleData; + HeapTuple tuple; tupleDesc = RelationNameGetTupleDesc(PGSTATINDEX_TYPE); ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] Patch for pgstatindex to fix a bug reporting a value of strange leaf_fragmentation
Hi. Alvaro Herrera wrote: In GetBTPageStatistics(), stat-fragments is not initialized properly so that invalid leaf_fragments values are inserted in results. Right. Checking that code, it seems btpo.xact is also being incorrectly handled, is it not? Yeah, I think so. Incidentally, the return value seems a bit useless. Exactly. Void is enough. -- NTT OSS Center Tatsuhito Kasahara kasahara.tatsuhito _at_ oss.ntt.co.jp ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] log_autovacuum
Hi, On 3/9/07, Simon Riggs [EMAIL PROTECTED] wrote: On Thu, 2007-03-08 at 14:53 -0300, Alvaro Herrera wrote: Maybe something like this is better: LOG: index passes: 1 pages: removed 0, 197 remain tuples: removed 7199, 2338 remain CPU usage: whatever CONTEXT: Automatic vacuuming of table database.public.w Yours is better. I've implemented this: LOG: autovac public.w index passes: 1 pages: removed 0, 197 remain tuples: removed 7199, 2338 remain CPU usage: whatever I'm happy if this gets removed later, but I think it will help everybody understand how multi-vacuums are working and what the best way to specify the controls should be. Not sure about the CONTEXT bit. I think its verbose, plus I thought that was for ERRORs only. I will defer on this point, since I know y'all understand that better than I. IMHO, it would be good to have both the messages spit out. The earlier message is much better for parsing and the later makes READABLE sense. Regards, Nikhils -- EnterpriseDB http://www.enterprisedb.com
