Обсуждение: Buffer Requests Trace

* Lucas Lersch (lucaslersch@gmail.com) wrote:
> Unfortunately, in the generated trace with over 2 million buffer requests,
> only ~14k different pages are being accessed, out of the 800k of the whole
> database. Am I missing something here?

What do you have shared_buffers set to..?

        Thanks,

                Stephen

Sorry, I do not understand the question.

But I forgot to give an additional information: I am printing the page id for the trace file in ReleaseBuffer() only if it is a shared buffer, I am not considering local buffers. I assumed that local buffers were used only for temporary tables.

On Tue, Oct 14, 2014 at 6:25 PM, Stephen Frost <sfrost@snowman.net> wrote:

* Lucas Lersch (lucaslersch@gmail.com) wrote:
> Unfortunately, in the generated trace with over 2 million buffer requests,
> only ~14k different pages are being accessed, out of the 800k of the whole
> database. Am I missing something here?

What do you have shared_buffers set to..?

        Thanks,

                Stephen

--
Lucas Lersch

* Lucas Lersch (lucaslersch@gmail.com) wrote:
> shared_buffers is 128MB and the version of pgsql is 9.3.5

I suspect you're not tracking what you think you're tracking, which is
why I brought up shared_buffers.

~14k * 8192 (page size) = ~110MB

What it sounds like you're actually tracking are shared buffer requests
and not heap or index requests.

Now, perhaps the test you're running only touched 110MB of the 6G
database, but that seems pretty unlikely.

        Thanks,

                Stephen

* Lucas Lersch (lucaslersch@gmail.com) wrote:
> Aren't heap and index requests supposed to go through the shared buffers
> anyway?

Sure they do, but a given page in shared_buffers can be used over and
over again for different heap and index pages..

        Thanks,

                Stephen

On 14 October 2014 17:08, Lucas Lersch <lucaslersch@gmail.com> wrote:

> Unfortunately, in the generated trace with over 2 million buffer requests,
> only ~14k different pages are being accessed, out of the 800k of the whole
> database. Am I missing something here?

We can't tell what you're doing just by knowing the number of unique
items in your list.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

On 15 October 2014 12:49, Lucas Lersch <lucaslersch@gmail.com> wrote:
> Sorry for taking so long to answer. I am sending attached the patch with the
> changes I did to pgsql code. I followed the steps for compiling and
> installing pgsql from:
> http://www.postgresql.org/docs/current/static/install-short.html

Are you recording the bufferid or the blockid?

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

On 15 October 2014 13:44, Lucas Lersch <lucaslersch@gmail.com> wrote:

> I am recording the BufferDesc.tag.blockNum for the buffer along with the
> spcNode, dbNode, relNode, also present in the tag.

The TPC-C I/O is random, so if you run it for longer you should see a wider set.

Cacheing isn't possible as a way to improve txn rates.

Check that you're touching all tables.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

* Lucas Lersch (lucaslersch@gmail.com) wrote:
> So is it a possible normal behavior that running tpcc for 10min only access
> 50% of the database? Furthermore, is there a guideline of parameters for
> tpcc (# of warehouses, execution time, operations weight)?

Depends- you may be aware that we support index-only scans in certain
situations.  This means that only the index page for a given relation
(and the visibility map) are accessed, and the heap is not.

        Thanks,

                Stephen

On Wed, Oct 15, 2014 at 6:22 AM, Lucas Lersch <lucaslersch@gmail.com> wrote:

So is it a possible normal behavior that running tpcc for 10min only access 50% of the database? Furthermore, is there a guideline of parameters for tpcc (# of warehouses, execution time, operations weight)?

I'm not familiar with your benchmarking tool.  With the one I am most familiar with, pgbench, if you run it against a database which is too big to fit in memory, it can take a very long time to touch each page once, because the constant random disk reads makes it run very slowly.  Maybe that is something to consider here--how many transactions were actually executed during your 10 min run?

Also, the tool might build tables that are only used under certain run options.  Perhaps you just aren't choosing the options which invoke usage of those tables.  Since you have the trace data, it should be pretty easy to count how many distinct blocks are accessed from each relation, and compare that to the size of the relations to see which relations are unused or lightly used.

Cheers,

Jeff