On 10/05/2011 03:02 AM, Greg Smith wrote:
Presumably you meant to ask if this makes sense to show when cost
accounting isn't enabled, because the code doesn't do that right now.
No cost accounting, no buffer usage/write rate data as this was
submitted.
This is done in the attached update. I just made the page accounting
happen all the time, regardless of whether the costs were being
accumulated. Added a read rate too, which is how fast reads happened
from the OS cache to shared_buffers. Simple test case generates a 600MB
pgbench_accounts database and wipes out enough to take a while to clean
up; it needs log_autovacuum_min_duration = 0 and then:
$ createdb pgbench
$ pgbench -i -s 10 pgbench
$ psql -d pgbench -c "delete from pgbench_accounts where aid<200000"
LOG: automatic vacuum of table "pgbench.public.pgbench_accounts": index
scans: 1
pages: 0 removed, 16394 remain
tuples: 199999 removed, 640011 remain
buffer usage: 13742 hits, 2708 misses, 1058 dirtied
avg read rate: 3.067 MiB/s, avg write rate: 1.198 MiB/s
system usage: CPU 0.05s/0.61u sec elapsed 6.89 sec
Now that you mention it, people who do a manual, full-speed VACUUM
would certainly appreciate some feedback on the rate it ran at.
This is more of a pain because this whole code path is only active when
IsAutoVacuumWorkerProcess. I have some larger refactoring in mind to
perhaps make that more feasible. I didn't want to hold this update
aiming at the more valuable autovac case for that though, can always
layer it on later.
--
Greg Smith 2ndQuadrant US g...@2ndquadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index f42504c..6ef85dd 100644
*** a/src/backend/commands/vacuum.c
--- b/src/backend/commands/vacuum.c
*************** vacuum(VacuumStmt *vacstmt, Oid relid, b
*** 214,219 ****
--- 214,222 ----
VacuumCostActive = (VacuumCostDelay > 0);
VacuumCostBalance = 0;
+ VacuumPageHit = 0;
+ VacuumPageMiss = 0;
+ VacuumPageDirty = 0;
/*
* Loop to process each selected relation.
diff --git a/src/backend/commands/vacuumlazy.c b/src/backend/commands/vacuumlazy.c
index 38deddc..c59fceb 100644
*** a/src/backend/commands/vacuumlazy.c
--- b/src/backend/commands/vacuumlazy.c
*************** lazy_vacuum_rel(Relation onerel, VacuumS
*** 154,160 ****
int nindexes;
BlockNumber possibly_freeable;
PGRUsage ru0;
! TimestampTz starttime = 0;
bool scan_all;
TransactionId freezeTableLimit;
BlockNumber new_rel_pages;
--- 154,163 ----
int nindexes;
BlockNumber possibly_freeable;
PGRUsage ru0;
! TimestampTz starttime = 0, endtime;
! long secs;
! int usecs;
! double read_rate, write_rate;
bool scan_all;
TransactionId freezeTableLimit;
BlockNumber new_rel_pages;
*************** lazy_vacuum_rel(Relation onerel, VacuumS
*** 166,173 ****
if (IsAutoVacuumWorkerProcess() && Log_autovacuum_min_duration >= 0)
{
pg_rusage_init(&ru0);
! if (Log_autovacuum_min_duration > 0)
! starttime = GetCurrentTimestamp();
}
if (vacstmt->options & VACOPT_VERBOSE)
--- 169,175 ----
if (IsAutoVacuumWorkerProcess() && Log_autovacuum_min_duration >= 0)
{
pg_rusage_init(&ru0);
! starttime = GetCurrentTimestamp();
}
if (vacstmt->options & VACOPT_VERBOSE)
*************** lazy_vacuum_rel(Relation onerel, VacuumS
*** 262,274 ****
/* and log the action if appropriate */
if (IsAutoVacuumWorkerProcess() && Log_autovacuum_min_duration >= 0)
{
if (Log_autovacuum_min_duration == 0 ||
! TimestampDifferenceExceeds(starttime, GetCurrentTimestamp(),
Log_autovacuum_min_duration))
ereport(LOG,
(errmsg("automatic vacuum of table \"%s.%s.%s\": index scans: %d\n"
"pages: %d removed, %d remain\n"
"tuples: %.0f removed, %.0f remain\n"
"system usage: %s",
get_database_name(MyDatabaseId),
get_namespace_name(RelationGetNamespace(onerel)),
--- 264,290 ----
/* and log the action if appropriate */
if (IsAutoVacuumWorkerProcess() && Log_autovacuum_min_duration >= 0)
{
+ endtime = GetCurrentTimestamp();
if (Log_autovacuum_min_duration == 0 ||
! TimestampDifferenceExceeds(starttime, endtime,
Log_autovacuum_min_duration))
+ {
+ TimestampDifference(starttime, endtime, &secs, &usecs);
+ read_rate = 0;
+ write_rate = 0;
+ if ((secs > 0) || (usecs > 0))
+ {
+ read_rate = (double) BLCKSZ * VacuumPageMiss / (1024 * 1024) /
+ (secs + usecs / 1000000.0);
+ write_rate = (double) BLCKSZ * VacuumPageDirty / (1024 * 1024) /
+ (secs + usecs / 1000000.0);
+ }
ereport(LOG,
(errmsg("automatic vacuum of table \"%s.%s.%s\": index scans: %d\n"
"pages: %d removed, %d remain\n"
"tuples: %.0f removed, %.0f remain\n"
+ "buffer usage: %d hits, %d misses, %d dirtied\n"
+ "avg read rate: %.3f MiB/s, avg write rate: %.3f MiB/s\n"
"system usage: %s",
get_database_name(MyDatabaseId),
get_namespace_name(RelationGetNamespace(onerel)),
*************** lazy_vacuum_rel(Relation onerel, VacuumS
*** 277,284 ****
vacrelstats->pages_removed,
vacrelstats->rel_pages,
vacrelstats->tuples_deleted,
! new_rel_tuples,
pg_rusage_show(&ru0))));
}
}
--- 293,305 ----
vacrelstats->pages_removed,
vacrelstats->rel_pages,
vacrelstats->tuples_deleted,
! vacrelstats->new_rel_tuples,
! VacuumPageHit,
! VacuumPageMiss,
! VacuumPageDirty,
! read_rate,write_rate,
pg_rusage_show(&ru0))));
+ }
}
}
diff --git a/src/backend/storage/buffer/bufmgr.c b/src/backend/storage/buffer/bufmgr.c
index e59af33..5306cb4 100644
*** a/src/backend/storage/buffer/bufmgr.c
--- b/src/backend/storage/buffer/bufmgr.c
*************** ReadBuffer_common(SMgrRelation smgr, cha
*** 340,345 ****
--- 340,346 ----
{
/* Just need to update stats before we exit */
*hit = true;
+ VacuumPageHit++;
if (VacuumCostActive)
VacuumCostBalance += VacuumCostPageHit;
*************** ReadBuffer_common(SMgrRelation smgr, cha
*** 471,476 ****
--- 472,478 ----
TerminateBufferIO(bufHdr, false, BM_VALID);
}
+ VacuumPageMiss++;
if (VacuumCostActive)
VacuumCostBalance += VacuumCostPageMiss;
*************** MarkBufferDirty(Buffer buffer)
*** 972,981 ****
Assert(bufHdr->refcount > 0);
/*
! * If the buffer was not dirty already, do vacuum cost accounting.
*/
! if (!(bufHdr->flags & BM_DIRTY) && VacuumCostActive)
! VacuumCostBalance += VacuumCostPageDirty;
bufHdr->flags |= (BM_DIRTY | BM_JUST_DIRTIED);
--- 974,987 ----
Assert(bufHdr->refcount > 0);
/*
! * If the buffer was not dirty already, do vacuum accounting.
*/
! if (!(bufHdr->flags & BM_DIRTY))
! {
! VacuumPageDirty++;
! if (VacuumCostActive)
! VacuumCostBalance += VacuumCostPageDirty;
! }
bufHdr->flags |= (BM_DIRTY | BM_JUST_DIRTIED);
*************** SetBufferCommitInfoNeedsSave(Buffer buff
*** 2326,2333 ****
{
LockBufHdr(bufHdr);
Assert(bufHdr->refcount > 0);
! if (!(bufHdr->flags & BM_DIRTY) && VacuumCostActive)
! VacuumCostBalance += VacuumCostPageDirty;
bufHdr->flags |= (BM_DIRTY | BM_JUST_DIRTIED);
UnlockBufHdr(bufHdr);
}
--- 2332,2343 ----
{
LockBufHdr(bufHdr);
Assert(bufHdr->refcount > 0);
! if (!(bufHdr->flags & BM_DIRTY))
! {
! VacuumPageDirty++;
! if (VacuumCostActive)
! VacuumCostBalance += VacuumCostPageDirty;
! }
bufHdr->flags |= (BM_DIRTY | BM_JUST_DIRTIED);
UnlockBufHdr(bufHdr);
}
diff --git a/src/backend/utils/init/globals.c b/src/backend/utils/init/globals.c
index c4c4154..9ce64e6 100644
*** a/src/backend/utils/init/globals.c
--- b/src/backend/utils/init/globals.c
*************** int VacuumCostPageDirty = 20;
*** 115,120 ****
--- 115,124 ----
int VacuumCostLimit = 200;
int VacuumCostDelay = 0;
+ int VacuumPageHit = 0;
+ int VacuumPageMiss = 0;
+ int VacuumPageDirty = 0;
+
int VacuumCostBalance = 0; /* working state for vacuum */
bool VacuumCostActive = false;
diff --git a/src/include/miscadmin.h b/src/include/miscadmin.h
index 9d19417..4ee08fe 100644
*** a/src/include/miscadmin.h
--- b/src/include/miscadmin.h
*************** extern int VacuumCostPageDirty;
*** 230,235 ****
--- 230,239 ----
extern int VacuumCostLimit;
extern int VacuumCostDelay;
+ extern int VacuumPageHit;
+ extern int VacuumPageMiss;
+ extern int VacuumPageDirty;
+
extern int VacuumCostBalance;
extern bool VacuumCostActive;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
