Re: (Relatively) Oversized Checkpoint - Mailing list pgsql-general

On Mon, Jun 16, 2014 at 12:40 PM, Nathaniel Talbott
<nathaniel@spreedly.com> wrote:
>> So the fsync (by the kernel) of a single file took 64 seconds.  This
>> single event explains almost all of the overrun and the performance
>> problems.
>
> So the 10x number of buffers being written is a red herring and/or
> correlates with a slow fsync?

Two theories there:

Either the uniformly high level of activity is not actually uniform
and it was extra high during that checkpoint.

Or, much of the writing that happens is normally being done by the
background writer rather than the checkpoint writer. Severe IO
problems caused the background writer to freeze up or slow down,
leaving the work to be done by the checkpoint writer instead.  Once
the checkpoint writer hits the fsync phase of its work, it then gets
blocked by the same IO problems that were affecting the background
writer.  So in this theory, the shift of the work load from background
writer to checkpoint writer is just a symptom of the IO problems.

Unfortunately there is little good way to assess those theories,
unless you have a lot of logging data for the period of interest, like
"sar" or "vmstat" or have been running "select * from
pg_stat_bgwriter" periodically and saving the results.

What is the sustained volume of disk output you experience, for
example from vmstat snapshots?

>
>
>> Lowering /proc/sys/vm/dirty_background_ratio and
>> /proc/sys/vm/dirty_background_bytes
>
> I've never tweaked dirty_background_* before; currently we have:
>
>     $ cat /proc/sys/vm/dirty_background_ratio
>     10
>     $ cat /proc/sys/vm/dirty_background_bytes
>     0
>
> Where should I start with tweaking those?

10% of 8GB (which is sounds like the mount of of RAM you have) is
nearly a gig, which is a lot of dirty data to keep around.  I'd use
dirty_background_bytes to set it, as it give finer control than
dirty_background_ratio does, and set it maybe 100MB (but depends on
your IO system)

>
>
>> not using ext3 for the file system
>
> What are the recommended alternatives? We're using ext4 currently.

ext3 and ext4 are the only thing I've used personally for production
databases.  I think ext4 mostly fixed the problems that ext3 had.
Some people like the XFS or JFS but I have no experience with them.

Since you aren't using the dreaded ext3, I wouldn't worry about that aspect.

>> are the clearest I know of, but people have also reported that
>> lowering shared_buffers can help.
>
> Currently we have:
>
>     => SHOW shared_buffers;
>      shared_buffers
>      ----------------
>      2GB
>     (1 row)
>
> That seems to match up with tuning recommendations I've read (25% of
> available RAM). And of course generally it seems to work great.

People who reported problems with large shared_buffers usually had
huge amounts of RAM, so that 25% of it was far larger than 2GB.  So
probably nothing for you to worry about.

>
>
>> This is fundamentally a kernel/FS/IO issue.  What are the current
>> settings for those, and what kernel and file system, and IO subsystem
>> are you using?
>
> Kernel: Linux 3.2.0-51-generic #77-Ubuntu SMP x86_64
> Filesystem: ext4

Series 3 kernels before 3.8 seem to have a poor (but vague) reputation
for running database-type workloads.

>
> Not sure what you're asking RE IO subsystem - do you mean drivers/hardware?

How many drives of what RPM in what RAID configuration, and does the
RAID controller have BBU.  I personally don't know from drivers, but
other people on the list do, so that info could be useful as well.

Cheers,

Jeff