2009-07-01

Optimizing Postgresql application performance with Solaris dynamic tracing

There is an excellent BluePrints article about DTrace probes in Postgresql. You can download it over here. Using DTrace you can get amazing information about internals of Postgresql, for instance (example taken from this article) having this DTrace script:

# cat query_load.d
#!/usr/sbin/dtrace -qs
dtrace:::BEGIN
{
printf(“Tracing... Hit Ctrl-C to end.\n”);
}
postgresql*:::query-start
{
self->query = copyinstr(arg0);
self->pid = pid;
}
postgresql*:::query-done
{
@queries[pid, self->query] = count();
}
dtrace:::END
{
printf(“%5s %s %s\n”, “PID”, “COUNT”, “QUERY”);
printa(“%6d %@5d %s\n”, @queries);
}

we get:

PID COUNT QUERY
1221 154 UPDATE tellers SET tbalance = tbalance + -487 WHERE tid = 25;
1221 204 UPDATE tellers SET tbalance = tbalance + 1051 WHERE tid = 42;
1220 215 UPDATE accounts SET abalance = abalance + -4302 WHERE aid = 144958;
1220 227 UPDATE accounts SET abalance = abalance + 2641 WHERE aid = 441283;


Isn't it amazing ?

I don't know if Larry Ellison is aware of DTrace but I wish I had the same DTrace probes in Oracle ...

No comments: