Skip site navigation (1) Skip section navigation (2)

reducing statistics write overhead

From: Martin Pihlak <martin(dot)pihlak(at)gmail(dot)com>
To: Pg Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: reducing statistics write overhead
Date: 2008-09-05 19:00:36
Message-ID: 48C181D4.8030807@gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
Howdy,

The statistics collector currently dumps the stats file at every 500ms.  This
is a major problem if the file becomes large -- occasionally we've been forced
to disable stats collection to cope with it.  Another issue is that while the
file is frequently written, it is seldom read. Typically once a minute -
autovacuum plus possible user initiated stats queries.

So, as a simple optimization I am proposing that the file should be only
written when some backend requests statistics. This would significantly reduce
the undesired write traffic at the cost of slightly slower stats access.

Attached is a WIP patch, which basically implements this:

Disable periodic writing of the stats file. Introduce new stats message type -
PGSTAT_MTYPE_INQUIRY. Backends send this to notify collector that stats is needed.
Pid of the requestor is provided in the message. Backend then installs an alarm
handler and starts a timer.  Collector processes the messages and compiles a list
of pids to be notified. If there are any, the stats file is written and SIGALRM
is sent to the requestors. Backend then proceeds to read the stats file a usual.

Thoughts, comments?

regards,
Martin

Attachment: stat-write.patch
Description: text/x-diff (10.7 KB)

Responses

pgsql-hackers by date

Next:From: Andrew ChernowDate: 2008-09-05 19:14:59
Subject: libpq events patch (with sgml docs)
Previous:From: Merlin MoncureDate: 2008-09-05 18:59:18
Subject: Re: CVS head has broken make

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group