Postgres Pro
Downloads
Home   >   mailing lists

Re: PITR and warm standby setup questions - Mailing list pgsql-general

From Mason Hale
Subject Re: PITR and warm standby setup questions
Date
Msg-id 8bca3aa10711122003l2547201bl235585dd06989e2e@mail.gmail.com
Whole thread Raw
In response to Re: PITR and warm standby setup questions  ("Merlin Moncure" <mmoncure@gmail.com>)
Responses Re: PITR and warm standby setup questions  ("Merlin Moncure" <mmoncure@gmail.com>)
List pgsql-general
Tree view

your i/o must be really random to be seeing numbers that lousy (10
seconds to replay a file is 1.6 megabytes/sec), or there is some other
unexplained problem with your server.  is your raid controller
properly caching wites?  have you benchmarked the volume with bonnie++
or similar tool (pay close attention to seeks).

Here's the bonnie++ output (two runs):

Version  1.03       ------Sequential Output------ --Sequential Input- --Random-
                    -Per Chr- --Block-- -Rewrite- -Per Chr- --Block-- --Seeks--
Machine        Size K/sec %CP K/sec %CP K/sec %CP K/sec %CP K/sec %CP  /sec %CP
dev-db-2        32G 43174  99 87421  24 45614  12 48302  97 164574  23 205.3   0
                    ------Sequential Create------ --------Random Create--------
                    -Create-- --Read--- -Delete-- -Create-- --Read--- -Delete--
              files  /sec %CP  /sec %CP  /sec %CP  /sec %CP  /sec %CP  /sec %CP
                 16 +++++ +++ +++++ +++ +++++ +++ +++++ +++ +++++ +++ +++++ +++

Having never used bonnie++ before, I don't have a baseline to compare this against, but that looks like 87MB/s writes and 164MB/s reads to me. Am I reading this correctly? It looks pretty good to me.

Here is some output from iostat

Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
sda               0.00         0.00         0.00          0          0
sdb               1.00         0.00        55.72          0        112
sdc               1.00         0.00        63.68           0        128
sdd             101.49      1699.50         0.00       3416          0

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0.06    0.00    0.06   12.37    0.00   87.51

Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
sda               2.50         0.00        24.00          0         48
sdb               0.00         0.00         0.00          0          0
sdc              42.50         0.00      8288.00          0      16576
sdd             101.50      1688.00         0.00        3376          0

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0.12    0.00    0.06   12.35    0.00   87.46

Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
sda               0.00         0.00         0.00          0          0
sdb               0.00         0.00         0.00           0          0
sdc               0.00         0.00         0.00          0          0
sdd             112.44      1787.06         0.00       3592          0

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0.12    0.00    0.06   12.36    0.00   87.45

Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
sda               0.00         0.00         0.00          0          0
sdb               4.50         0.00        48.00          0         96
sdc               0.50         0.00         4.00          0          8
sdd              97.50      1752.00         0.00        3504          0

In the above: sdb holds the pg_xlog directory, sdc holds the wal archive, and sdd is the 4 disk RAID 1+0 where the pgdata directory is stored. All these disks are ext3 with noatime,data=writeback mount options. The RAID controller is an Adaptec 3805 with 128MB battery backed cache (only option offered by our hosting provider for this server class).

Does any of this shed any light on how to boost my restore performance?

thanks,
Mason


pgsql-general by date:

Previous
From: "yang zhenyu"
Date:
Subject: Re: PQexec(), what should I do for the "NULL in command" problem?
Next
From: Greg Smith
Date:
Subject: Re: PITR and warm standby setup questions