Hello.

The quintessence of the problem explained in previous letters: CHECKPOIND
command works quite slow, disk write activity is high just after it is
executed, and diring the CHECKPOINT SELECT queries work many times slower
than usual.

I experimended with bgwriter, but it affects the CHECKPOINT performance very
little (possibly 2-3 times faster but no more).
What else could I tune?

And the general question - why SELECT queries slowdown during the
CHECKPOINT? I thought that Postgres is a version-based database and read
queries are never blocked...

Search Discussions

  • Joseph S at Mar 28, 2007 at 7:53 pm

    Dmitry Koterov wrote:

    And the general question - why SELECT queries slowdown during the
    CHECKPOINT? I thought that Postgres is a version-based database and read
    queries are never blocked...
    Because the disk is busy.
  • Dmitry Koterov at Mar 29, 2007 at 7:06 am
    No. Disk read activity is ALWAYS 0, the system has a lot of disk cache.
    On 3/28/07, Joseph S wrote:

    Dmitry Koterov wrote:
    And the general question - why SELECT queries slowdown during the
    CHECKPOINT? I thought that Postgres is a version-based database and read
    queries are never blocked...
    Because the disk is busy.

    ---------------------------(end of broadcast)---------------------------
    TIP 1: if posting/reading through Usenet, please send an appropriate
    subscribe-nomail command to majordomo@postgresql.org so that your
    message can get through to the mailing list cleanly
  • CAJ CAJ at Mar 29, 2007 at 4:57 pm

    On 3/28/07, Joseph S wrote:
    Dmitry Koterov wrote:
    And the general question - why SELECT queries slowdown during the
    CHECKPOINT? I thought that Postgres is a version-based database and read
    queries are never blocked...
    Because the disk is busy.


    Is your pg_xlog on a separate disk?
  • Dmitry Koterov at Mar 29, 2007 at 8:27 pm
    No, but disks are about 120 MB/s speed, peak writes during CHECKPOINT are
    only 20-30 MB/s, and there is no disk read activity at all, so - no matter
    where the xlog resides.

    But now seems bgwriter tuning gets some effect, CHECKPOINT is running faster
    (about 2-3 seconds instead of 10-15). It is still beats the performance, but
    less.

    On 3/29/07, CAJ CAJ wrote:


    On 3/28/07, Joseph S wrote:

    Dmitry Koterov wrote:
    And the general question - why SELECT queries slowdown during the
    CHECKPOINT? I thought that Postgres is a version-based database and read
    queries are never blocked...
    Because the disk is busy.


    Is your pg_xlog on a separate disk?



  • Scott Marlowe at Mar 29, 2007 at 9:45 pm

    On Thu, 2007-03-29 at 15:13, Dmitry Koterov wrote:
    No, but disks are about 120 MB/s speed, peak writes during CHECKPOINT
    are only 20-30 MB/s, and there is no disk read activity at all, so -
    no matter where the xlog resides.

    But now seems bgwriter tuning gets some effect, CHECKPOINT is running
    faster (about 2-3 seconds instead of 10-15). It is still beats the
    performance, but less.
    Well, I assume bgwriter isn't the only thing writing to those disks.
    Which would mean that your bgwriter output is random access not
    sequential. Which would mean putting pg_xlog on another set of drives
    where it wasn't competing with other postgresql (and system) processes
    for drive access would speed things up.

    Or getting a good battery backed RAID controller. That's the first step
    to building a fast db server if you're using built in storage.

    but I could be wrong or have misunderheard what you were saying.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedMar 28, '07 at 2:05p
activeMar 29, '07 at 9:45p
posts6
users4
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase