Message-ID: <53D2213A.8060101@2ndquadrant.com>
Date: Fri, 25 Jul 2014 17:19:54 +0800
From: Craig Ringer <craig@2ndquadrant.com>
Organization: 2nd Quadrant
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:24.0) Gecko/20100101 Thunderbird/24.4.0
MIME-Version: 1.0
To: Borodin Vladimir <root@simply.name>,
 pgsql-performance@postgresql.org
Subject: Re: Debugging writing load
References: <06E73D38-F341-424F-AD89-8F2DC73B7D79@simply.name>
In-Reply-To: <06E73D38-F341-424F-AD89-8F2DC73B7D79@simply.name>
Content-Type: text/plain; charset=windows-1251
Content-Transfer-Encoding: 8bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

On 07/24/2014 10:22 PM, Borodin Vladimir wrote:
> Hi all.
> 
> I have a database with quiet heavy writing load (about 20k tps, 5k of
> which do writes). And I see lots of writing I/O (I mean amount of data,
> not iops) to this database, much more than I expect. My question is how
> can I debug what for backend processes do lots of writes to the
> $PGDATA/base directory? Below are some details.

I'd  be using perf for this - if you set tracepoints on writes and
flushes you can associate that with the proceses performing the work.

That said, the bgwriter and checkpointer will accumulate data from many
different backends. perf can't track that back to the origin backend.

It's also currently impractical to pluck things like the current_user,
statement name, etc from the PostgreSQL process's memory when capturing
perf events, so it's still hard to drill down to "this query is causing
lots of I/O".

> Lsof on several backend processes shows me that backend uses just
> database files (tables and indexes) and last xlog file. Is there any way
> to understand why is backend writing lots of data to $PGDATA/base
> directory? I have tried to use pg_stat_statements for it but I haven’t
> found a good way to understand what is happening. Is there a way to see
> something like "this backend process has written these pages to disk
> while performing this query"?

Pg can't keep track of that its self. At most it can track what buffered
writes it's issued. The OS might write-merge them, or it might not flush
them to disk at all if they're superseded by a later write over the same
range.

You really need the kernel's help, and that's where perf comes in.

I wrote a little about this topic a while ago:

http://blog.2ndquadrant.com/tracing-postgresql-perf/

but it's really a bit introductory.

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


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance