Re: Monitoring Object access

On 09/12/2010 06:52 PM, adi hirschtein wrote:

Hi,
Using the catalog tables, is there any way to correlate session id/user
id to which object (i.e. tables, indexes etc) it access and much how
disk reads or I/O wait has been done against the objects.
in general, I'd like to see which objects are being accessed by which
user and the time/amount of I/O wait/reads.

There isn't really anything like that, no. You have pg_stat_activity, but it's a pretty coarse tool.

The shared buffer cache and the use of things like synchronized sequential scans means that it wouldn't even be possible to truly track who's causing load reliably. As I understand it, if Joe and Fred both to a "SELECT * FROM sometable", it's quite likely that only one of the backends will actually appear to read anything from disk. Which one is pretty much luck.

Adding something like this would require instrumenting not only the backends' disk I/O code, but also their shared buffer access code. It'd potentially add a lot of overhead, and I'm not sure the results would mean very much because the caching effects would make "fred's backend did X I/O operations" less meaningful. Fred's might just have been the first that ran after Joe's giant seqscan cross join of two big tables that forced everything else out of shared_buffers.

Even if you did have that instrumentation, you'd need OS-level instrumentation (dtrace, prof, etc) to track the OS's buffer cache, which PostgreSQL relies on heavily. Without that you can't tell the difference between a query that caused I/O calls from postgresql but could be satisfied by OS buffer cache and one that required expensive physical disk I/O to satisfy.

Really, I don't know if it's realistic to do what you're suggesting unless every user's data set is isolated from every other user's, in which case you can maybe use OS-level tools like prof or DTrace to achieve it if you set postgresql up to log whichstarts which backend pid.

If there's any significant overlap in the data sets used by the users (if they use the same databases or schema) I wouldn't think you'd be able to get any meaningful results.

--
Craig Ringer