Le 25/08/2014 19:00, Gilles Darold a écrit :
> Le 21/08/2014 10:17, Julien Rouhaud a écrit :
>> Hello,
>>
>> Attached patch implements the following TODO item :
>>
>> Track number of WAL files ready to be archived in pg_stat_archiver
>>
>> However, it will track the total number of any file ready to be
>> archived, not only WAL files.
>>
>> Please let me know what you think about it.
>>
>> Regards.
>
> Hi,
>
> Maybe looking at archive ready count will be more efficient if it is
> done in the view definition through a function. This will avoid any
> issue with incrementing/decrement of archiverStats.ready_count and the
> patch will be more simple. Also I don't think we need an other memory
> allocation for that, the counter information is always in the number of
> .ready files in the archive_status directory and the call to
> pg_stat_archiver doesn't need high speed performances.
>
> For example having a new function called
> pg_stat_get_archive_ready_count() that does the same at what you add
> into pgstat_read_statsfiles() and the pg_stat_archiver defined as follow:
>
> CREATE VIEW pg_stat_archiver AS
> s.failed_count,
> s.last_failed_wal,
> s.last_failed_time,
> pg_stat_get_archive_ready() as ready_count,
> s.stats_reset
> FROM pg_stat_get_archiver() s;
>
> The function pg_stat_get_archive_ready_count() will also be available
> for any other querying.
>
Indeed, this approach should be more efficient. It also avoid unexpected
results, like if someone has the bad idea to remove a .ready file in
pg_xlog/archive_status directory.
Attached v2 patch implements this approach. All the work is still done
in pg_stat_get_archiver, as I don't think that having a specific
function for that information would be really interesting.
--
Julien Rouhaud
http://dalibo.com - http://dalibo.org
*** a/doc/src/sgml/monitoring.sgml
--- b/doc/src/sgml/monitoring.sgml
***************
*** 728,733 **** postgres 27093 0.0 0.0 30096 2752 ? Ss 11:34 0:00 postgres: ser
--- 728,738 ----
<entry>Time of the last failed archival operation</entry>
</row>
<row>
+ <entry><structfield>ready_count</></entry>
+ <entry><type>bigint</type></entry>
+ <entry>Number of files waiting to be archived</entry>
+ </row>
+ <row>
<entry><structfield>stats_reset</></entry>
<entry><type>timestamp with time zone</type></entry>
<entry>Time at which these statistics were last reset</entry>
*** a/src/backend/catalog/system_views.sql
--- b/src/backend/catalog/system_views.sql
***************
*** 697,702 **** CREATE VIEW pg_stat_archiver AS
--- 697,703 ----
s.failed_count,
s.last_failed_wal,
s.last_failed_time,
+ s.ready_count,
s.stats_reset
FROM pg_stat_get_archiver() s;
*** a/src/backend/utils/adt/pgstatfuncs.c
--- b/src/backend/utils/adt/pgstatfuncs.c
***************
*** 15,25 ****
--- 15,27 ----
#include "postgres.h"
#include "access/htup_details.h"
+ #include "access/xlog_internal.h"
#include "catalog/pg_type.h"
#include "funcapi.h"
#include "libpq/ip.h"
#include "miscadmin.h"
#include "pgstat.h"
+ #include "storage/fd.h"
#include "utils/builtins.h"
#include "utils/inet.h"
#include "utils/timestamp.h"
***************
*** 1737,1752 **** Datum
pg_stat_get_archiver(PG_FUNCTION_ARGS)
{
TupleDesc tupdesc;
! Datum values[7];
! bool nulls[7];
PgStat_ArchiverStats *archiver_stats;
/* Initialise values and NULL flags arrays */
MemSet(values, 0, sizeof(values));
MemSet(nulls, 0, sizeof(nulls));
/* Initialise attributes information in the tuple descriptor */
! tupdesc = CreateTemplateTupleDesc(7, false);
TupleDescInitEntry(tupdesc, (AttrNumber) 1, "archived_count",
INT8OID, -1, 0);
TupleDescInitEntry(tupdesc, (AttrNumber) 2, "last_archived_wal",
--- 1739,1758 ----
pg_stat_get_archiver(PG_FUNCTION_ARGS)
{
TupleDesc tupdesc;
! Datum values[8];
! bool nulls[8];
PgStat_ArchiverStats *archiver_stats;
+ char XLogArchiveStatusDir[MAXPGPATH];
+ DIR *rldir;
+ struct dirent *rlde;
+ int ready_count;
/* Initialise values and NULL flags arrays */
MemSet(values, 0, sizeof(values));
MemSet(nulls, 0, sizeof(nulls));
/* Initialise attributes information in the tuple descriptor */
! tupdesc = CreateTemplateTupleDesc(8, false);
TupleDescInitEntry(tupdesc, (AttrNumber) 1, "archived_count",
INT8OID, -1, 0);
TupleDescInitEntry(tupdesc, (AttrNumber) 2, "last_archived_wal",
***************
*** 1759,1765 **** pg_stat_get_archiver(PG_FUNCTION_ARGS)
TEXTOID, -1, 0);
TupleDescInitEntry(tupdesc, (AttrNumber) 6, "last_failed_time",
TIMESTAMPTZOID, -1, 0);
! TupleDescInitEntry(tupdesc, (AttrNumber) 7, "stats_reset",
TIMESTAMPTZOID, -1, 0);
BlessTupleDesc(tupdesc);
--- 1765,1773 ----
TEXTOID, -1, 0);
TupleDescInitEntry(tupdesc, (AttrNumber) 6, "last_failed_time",
TIMESTAMPTZOID, -1, 0);
! TupleDescInitEntry(tupdesc, (AttrNumber) 7, "ready_count",
! INT8OID, -1, 0);
! TupleDescInitEntry(tupdesc, (AttrNumber) 8, "stats_reset",
TIMESTAMPTZOID, -1, 0);
BlessTupleDesc(tupdesc);
***************
*** 1790,1799 **** pg_stat_get_archiver(PG_FUNCTION_ARGS)
else
values[5] = TimestampTzGetDatum(archiver_stats->last_failed_timestamp);
if (archiver_stats->stat_reset_timestamp == 0)
! nulls[6] = true;
else
! values[6] = TimestampTzGetDatum(archiver_stats->stat_reset_timestamp);
/* Returns the record as Datum */
PG_RETURN_DATUM(HeapTupleGetDatum(
--- 1798,1830 ----
else
values[5] = TimestampTzGetDatum(archiver_stats->last_failed_timestamp);
+ snprintf(XLogArchiveStatusDir, MAXPGPATH, XLOGDIR "/archive_status");
+ rldir = AllocateDir(XLogArchiveStatusDir);
+ if (rldir == NULL)
+ ereport(ERROR,
+ (errcode_for_file_access(),
+ errmsg("could not open archive status directory \"%s\": %m",
+ XLogArchiveStatusDir)));
+
+ while ((rlde = ReadDir(rldir, XLogArchiveStatusDir)) != NULL)
+ {
+ int basenamelen = (int) strlen(rlde->d_name) - 6;
+
+ if (basenamelen >= MIN_XFN_CHARS &&
+ basenamelen <= MAX_XFN_CHARS &&
+ strspn(rlde->d_name, VALID_XFN_CHARS) >= basenamelen &&
+ strcmp(rlde->d_name + basenamelen, ".ready") == 0)
+ {
+ ++ready_count;
+ }
+ }
+ FreeDir(rldir);
+ values[6] = Int64GetDatum(ready_count);
+
if (archiver_stats->stat_reset_timestamp == 0)
! nulls[7] = true;
else
! values[7] = TimestampTzGetDatum(archiver_stats->stat_reset_timestamp);
/* Returns the record as Datum */
PG_RETURN_DATUM(HeapTupleGetDatum(
*** a/src/include/catalog/pg_proc.h
--- b/src/include/catalog/pg_proc.h
***************
*** 2751,2757 **** DATA(insert OID = 2844 ( pg_stat_get_db_blk_read_time PGNSP PGUID 12 1 0 0 0 f
DESCR("statistics: block read time, in msec");
DATA(insert OID = 2845 ( pg_stat_get_db_blk_write_time PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0 701 "26" _null_ _null_ _null_ _null_ pg_stat_get_db_blk_write_time _null_ _null_ _null_ ));
DESCR("statistics: block write time, in msec");
! DATA(insert OID = 3195 ( pg_stat_get_archiver PGNSP PGUID 12 1 0 0 0 f f f f f f s 0 0 2249 "" "{20,25,1184,20,25,1184,1184}" "{o,o,o,o,o,o,o}" "{archived_count,last_archived_wal,last_archived_time,failed_count,last_failed_wal,last_failed_time,stats_reset}" _null_ pg_stat_get_archiver _null_ _null_ _null_ ));
DESCR("statistics: information about WAL archiver");
DATA(insert OID = 2769 ( pg_stat_get_bgwriter_timed_checkpoints PGNSP PGUID 12 1 0 0 0 f f f f t f s 0 0 20 "" _null_ _null_ _null_ _null_ pg_stat_get_bgwriter_timed_checkpoints _null_ _null_ _null_ ));
DESCR("statistics: number of timed checkpoints started by the bgwriter");
--- 2751,2757 ----
DESCR("statistics: block read time, in msec");
DATA(insert OID = 2845 ( pg_stat_get_db_blk_write_time PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0 701 "26" _null_ _null_ _null_ _null_ pg_stat_get_db_blk_write_time _null_ _null_ _null_ ));
DESCR("statistics: block write time, in msec");
! DATA(insert OID = 3195 ( pg_stat_get_archiver PGNSP PGUID 12 1 0 0 0 f f f f f f s 0 0 2249 "" "{20,25,1184,20,25,1184,20,1184}" "{o,o,o,o,o,o,o,o}" "{archived_count,last_archived_wal,last_archived_time,failed_count,last_failed_wal,last_failed_time,ready_count,stats_reset}" _null_ pg_stat_get_archiver _null_ _null_ _null_ ));
DESCR("statistics: information about WAL archiver");
DATA(insert OID = 2769 ( pg_stat_get_bgwriter_timed_checkpoints PGNSP PGUID 12 1 0 0 0 f f f f t f s 0 0 20 "" _null_ _null_ _null_ _null_ pg_stat_get_bgwriter_timed_checkpoints _null_ _null_ _null_ ));
DESCR("statistics: number of timed checkpoints started by the bgwriter");
*** a/src/test/regress/expected/rules.out
--- b/src/test/regress/expected/rules.out
***************
*** 1659,1666 **** pg_stat_archiver| SELECT s.archived_count,
s.failed_count,
s.last_failed_wal,
s.last_failed_time,
s.stats_reset
! FROM pg_stat_get_archiver() s(archived_count, last_archived_wal, last_archived_time, failed_count, last_failed_wal, last_failed_time, stats_reset);
pg_stat_bgwriter| SELECT pg_stat_get_bgwriter_timed_checkpoints() AS checkpoints_timed,
pg_stat_get_bgwriter_requested_checkpoints() AS checkpoints_req,
pg_stat_get_checkpoint_write_time() AS checkpoint_write_time,
--- 1659,1667 ----
s.failed_count,
s.last_failed_wal,
s.last_failed_time,
+ s.ready_count,
s.stats_reset
! FROM pg_stat_get_archiver() s(archived_count, last_archived_wal, last_archived_time, failed_count, last_failed_wal, last_failed_time, ready_count, stats_reset);
pg_stat_bgwriter| SELECT pg_stat_get_bgwriter_timed_checkpoints() AS checkpoints_timed,
pg_stat_get_bgwriter_requested_checkpoints() AS checkpoints_req,
pg_stat_get_checkpoint_write_time() AS checkpoint_write_time,
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
