Re: Load distributed checkpoint

To: "Takayuki Tsunakawa" <tsunakawa(dot)takay(at)jp(dot)fujitsu(dot)com>
Cc: <pgsql-hackers(at)postgresql(dot)org>
Sent: Thursday, December 21, 2006 6:46 PM
Subject: Re: [HACKERS] Load distributed checkpoint

>
From: "ITAGAKI Takahiro" <itagaki(dot)takahiro(at)oss(dot)ntt(dot)co(dot)jp>
> "Takayuki Tsunakawa" <tsunakawa(dot)takay(at)jp(dot)fujitsu(dot)com> wrote:
>> Oh, really, what an evil fsync is! Yes, I sometimes saw a backend
>> waiting for lseek() to complete when it committed. But why does
the
>> backend which is syncing WAL/pg_control have to wait for syncing
the
>> data file? They are, not to mention, different files, and WAL and
>> data files are stored on separate disks.
>
> Backends call lseek() in planning, so they have to wait fsync() to
> the table that they will access. Even if all of data in the file is
in
> the cache, lseek() conflict with fsync(). You can see a lot of
backends
> are waiting in planning phase in checkpoints, not executing phase.

I see. I found one backend like the following. But one in my case
one out of 16 backends. Most of others are waiting to acquire
WALWRITE lock.

#0 0x0000003a629c6902 in __lseek_nocancel () from
/lib64/tls/libc.so.6
#1 0x000000000056789f in FileSeek ()
#2 0x0000000000574053 in mdnblocks ()
#3 0x0000000000574f4a in smgrnblocks ()
#4 0x00000000005489e8 in estimate_rel_size ()
#5 0x0000000000548bee in get_relation_info ()
#6 0x000000000054aa3d in build_simple_rel ()
#7 0x0000000000539c6b in add_base_rels_to_query ()
#8 0x000000000053b955 in query_planner ()
#9 0x000000000053c1c9 in grouping_planner ()
#10 0x000000000053d3b4 in subquery_planner ()
#11 0x000000000053d5b3 in planner ()
#12 0x00000000005778fc in pg_plan_query ()
#13 0x000000000057798c in pg_plan_queries ()
#14 0x0000000000577c53 in exec_simple_query ()