Re: A sniffer for the buffer

Hi,

I'm a Computer Science student and I'm currently studying databases buffer
managers. I want to do some experiments and see how the pages access works
in PostgreSQL. (and I also will do some I/O experiments)

So, I want to do a "sniffer" on the Storage Layer of Postgresql. It should
work telling me the page that PGSQL is reading or writing. So when I make
some operation on PGSQL, the pages that were requested (to read or write) by
PGSQL for the buffer, are stored in a separated file. And after that i can
build some graphs, conclusions, etc...

So, I made such a code to do that, but i'm not quite sure if I'm doing the
right thing. Can someone of you give me a opinion ??

Many Thanks in advance,
Jonas Jeske

The modified code is in bufmgr.c of PostgreSQL source code.

Buffer
ReadBuffer(Relation reln, BlockNumber blockNum)
{
volatile BufferDesc *bufHdr;
Block bufBlock;
bool found;
bool isExtend;
bool isLocalBuf;

/*jjeske starts here */
FILE *fp;
fp = fopen("/home/jjeske/tpccuva/pgsql/saida.txt","a");
fprintf(fp,"Read Block n: %d\n",(int) blockNum);
fclose(fp);

....

static void
write_buffer(Buffer buffer, bool unpin)
{
volatile BufferDesc *bufHdr;

/*jjeske starts here */
FILE *fp;
fp = fopen("/home/jjeske/tpccuva/pgsql/saida.txt","a");
fprintf(fp,"Write Block n: %d\n",(int) buffer);
fclose(fp);
....

Jonas J wrote:
> Buffer
> ReadBuffer(Relation reln, BlockNumber blockNum)...
> fprintf(fp,"Read Block n: %d\n",(int) blockNum);
The "key" as it were for database blocks read is both of the things
passed into ReadBuffer. You'd need to save both the Relation number
(which turns into the subdirectory used to store that table in the base/
directory) and the block number to do anything useful with this data.

> static void
> write_buffer(Buffer buffer, bool unpin)
> {
> volatile BufferDesc *bufHdr;
>
> /*jjeske starts here */
> FILE *fp;
> fp = fopen("/home/jjeske/tpccuva/pgsql/saida.txt","a");
> fprintf(fp,"Write Block n: %d\n",(int) buffer);
> fclose(fp);
And that won't work at all. "Buffer" is a structure, not an integer.
You need to wait until it's been locked, then save the same data as on
the read side (relation and block number) from inside the structure.
You probably want to hook FlushBuffer to do that job. In fact, there's
already a DTrace probe in there you could easily use to collect the data
you want without even touching the source code if you can get a DTrace
capable system. Note the following code in bufmgr.c FlushBuffer:

TRACE_POSTGRESQL_BUFFER_FLUSH_START(buf->tag.forkNum,
buf->tag.blockNum,
reln->smgr_rnode.spcNode,
reln->smgr_rnode.dbNode,
reln->smgr_rnode.relNode);

That's exporting everything you're trying to save yourself such that a
DTrace program can observe it.

In fact, I'd suggest that any time you're trying to get some data out of
the internals, start by seeing if there's a DTrace probe point with that
info in it alread, because those have been thought through to make sure
they're exporting the right data and in the right way (i.e. after
locking the structures properly). On the read side,
TRACE_POSTGRESQL_BUFFER_READ_START inside of ReadBuffer_common is the
one you should do the same thing as, if you must export this stuff
manually rather than use DTrace.

There's more about the locking I'm alluding to inside
src/backend/storage/buffer/README , you should check out that file for
more info.

P.S. I think you're using a fairly old version of the PostgreSQL source
code, which may not have the same names and DTrace points I'm
mentioning. That's probably a mistake--if you want to hack on the
PostgreSQL code, you should be using at least PostgreSQL 8.4, and it's
better still to checkout from the CVS/git repos.

--
Greg Smith 2ndQuadrant Baltimore, MD
PostgreSQL Training, Services and Support
greg(at)2ndQuadrant(dot)com www.2ndQuadrant.com

On Sun, Dec 6, 2009 at 9:04 AM, Greg Smith <greg(at)2ndquadrant(dot)com> wrote:
> And that won't work at all.  "Buffer" is a structure, not an integer.  You
> need to wait until it's been locked, then save the same data as on the read
> side (relation and block number) from inside the structure.  You probably
> want to hook FlushBuffer to do that job.  In fact, there's already a DTrace
> probe in there you could easily use to collect the data you want without
> even touching the source code if you can get a DTrace capable system.  Note
> the following code in bufmgr.c FlushBuffer:

Apropos DTrace: I have just today compiled Postgres 8.3 with
--enable-dtrace on an Ubuntu 9.10 machine with no issues or
workarounds. I just had to install the package systemtap-sdt-dev.

I haven't tried to do anything with it just yet though...

fdr

Jonas J wrote:
> I took a look in the code again and made some changes. For the
> readBuffer im doing now:
> ReadBuffer(Relation reln, BlockNumber blockNum)
> fprintf(fp,"r%u\n",(unsigned int) blockNum); //as defined in
> header, typedef uint32 BlockNumber;
> and from the write pages:
> write_buffer(Buffer buffer, bool unpin){
> fprintf(fp,"w%d\n",BufferGetBlockNumber(buffer)); //get the
> blockNumber of this buffer
It's better to keep this discussion going on the list so you can get
alternate suggestions besides mine. The above is better. You're still
missing the relation number, which you're really going to want
eventually. And I think you're still vulnerable to printing the
information out before the block is locked properly in the write_buffer
case.

> So, I had never used DTrace, where can I find some good paper to start
> studying it ?? Also, do it work with linux, or only solaris ??
You can get it to work on Linux for PostgreSQL use if you use systemtap;
there's some people who've posted suggestions about that around.
> P.S.: I'm using PostgreSQL 8.1.4 to run with TPCC-UVA benchmarks tests...
Ah. PostgreSQL 8.1 is significantly slower than the current versions,
and you're not going to get as much help with issues related to the
source code as if you're using a newer one. For example, I don't know
the 8.1 buffer implementation code nearly as well because I didn't
really start tinkering with it until 8.2, so some of the things you're
asking about I don't have easy access or recollection of. Also, the
DTrace stuff is only really going to be helpful if you're starting with 8.4.

There may be some work to get TPCC-UVA working with 8.4 though,
particularly due to some changes made in 8.3 related to casting data to
other types. There is a TPC-C implementation that's used pretty often
by developers here named dbt-2: http://wiki.postgresql.org/wiki/DBT-2
that will work with a newer version.

--
Greg Smith 2ndQuadrant Baltimore, MD
PostgreSQL Training, Services and Support
greg(at)2ndQuadrant(dot)com www.2ndQuadrant.com

Thanks for the answers.
I will change it for PostgreSQL 8.4 and try to use DBT-2.

But, I'm not quite sure if DTrace will give me the workload that I want.
Since, i want to trace the Workload that is above the Buffer Layer. With
workload I mean two fields (operation: read/write and Block Number). Then
all blocks that PostgreSQL request to the buffer layer I will capture.
Do DTrace can give my this information?

Thanks in advance,
Jonas Jeske

2009/12/8 Greg Smith <greg(at)2ndquadrant(dot)com>

> Jonas J wrote:
>
>> I took a look in the code again and made some changes. For the readBuffer
>> im doing now:
>> ReadBuffer(Relation reln, BlockNumber blockNum)
>> fprintf(fp,"r%u\n",(unsigned int) blockNum); //as defined in header,
>> typedef uint32 BlockNumber;
>> and from the write pages:
>> write_buffer(Buffer buffer, bool unpin){
>> fprintf(fp,"w%d\n",BufferGetBlockNumber(buffer)); //get the blockNumber of
>> this buffer
>>
> It's better to keep this discussion going on the list so you can get
> alternate suggestions besides mine. The above is better. You're still
> missing the relation number, which you're really going to want eventually.
> And I think you're still vulnerable to printing the information out before
> the block is locked properly in the write_buffer case.
>
>
> So, I had never used DTrace, where can I find some good paper to start
>> studying it ?? Also, do it work with linux, or only solaris ??
>>
> You can get it to work on Linux for PostgreSQL use if you use systemtap;
> there's some people who've posted suggestions about that around.
>
>> P.S.: I'm using PostgreSQL 8.1.4 to run with TPCC-UVA benchmarks tests...
>>
> Ah. PostgreSQL 8.1 is significantly slower than the current versions, and
> you're not going to get as much help with issues related to the source code
> as if you're using a newer one. For example, I don't know the 8.1 buffer
> implementation code nearly as well because I didn't really start tinkering
> with it until 8.2, so some of the things you're asking about I don't have
> easy access or recollection of. Also, the DTrace stuff is only really going
> to be helpful if you're starting with 8.4.
>
> There may be some work to get TPCC-UVA working with 8.4 though,
> particularly due to some changes made in 8.3 related to casting data to
> other types. There is a TPC-C implementation that's used pretty often by
> developers here named dbt-2: http://wiki.postgresql.org/wiki/DBT-2 that
> will work with a newer version.
>
>
> --
> Greg Smith 2ndQuadrant Baltimore, MD
> PostgreSQL Training, Services and Support
> greg(at)2ndQuadrant(dot)com www.2ndQuadrant.com
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

Greg Smith wrote:
> Jonas J wrote:

> >P.S.: I'm using PostgreSQL 8.1.4 to run with TPCC-UVA benchmarks tests...
> Ah. PostgreSQL 8.1 is significantly slower than the current
> versions, and you're not going to get as much help with issues
> related to the source code as if you're using a newer one. For
> example, I don't know the 8.1 buffer implementation code nearly as
> well because I didn't really start tinkering with it until 8.2, so
> some of the things you're asking about I don't have easy access or
> recollection of.

8.1 didn't have lock partitioning, so BufMappingLock was a serious
problem back then. Completely different from the current code.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.