Re: Excessive WAL generation and related performance issue

On 04/15/2014 11:53 PM, Joe Conway wrote:
> One more question before I get to that. I had applied the following
> patch to XLogInsert
>
> 8<--------------------------
> diff --git a/src/backend/access/transam/xlog.c
> b/src/backend/access/transam/xlog.c
> index 2f71590..e39cd37 100644
> - --- a/src/backend/access/transam/xlog.c
> +++ b/src/backend/access/transam/xlog.c
> @@ -737,10 +737,12 @@ XLogInsert(RmgrId rmid, uint8 info, XLogRecData
> *rdata)
>          uint32          len,
>                                  write_len;
>          unsigned        i;
> +       unsigned        inorm;
>          bool            updrqst;
>          bool            doPageWrites;
>          bool            isLogSwitch = (rmid == RM_XLOG_ID && info ==
> XLOG_SWITCH);
>          uint8           info_orig = info;
> +       uint32          xl_tot_len;
>
>          /* cross-check on whether we should be here or not */
>          if (!XLogInsertAllowed())
> @@ -924,8 +926,23 @@ begin:;
>           * header.
>           */
>          INIT_CRC32(rdata_crc);
> +       i = 0;
> +       inorm = 0;
>          for (rdt = rdata; rdt != NULL; rdt = rdt->next)
> +       {
>                  COMP_CRC32(rdata_crc, rdt->data, rdt->len);
> +
> +               if (rdt_lastnormal == rdt)
> +               {
> +                       inorm = i;
> +                       i = 0;
> +               }
> +               else
> +                       i++;
> +       }
> +       xl_tot_len = SizeOfXLogRecord + write_len;
> +       if ((inorm + i) > 4 || xl_tot_len > 2000)
> +               elog(LOG,
> "XLogInsert;tot_nml_blks;%d;tot_bkp_blks;%d;tot_Xlog_Len;%d", inorm,
> i, xl_tot_len);
>
>          START_CRIT_SECTION();
> 8<--------------------------
>
> The idea was to record number of normal and backup blocks, and total
> size of the record. I have quite a few entries in the log from the
> test run which are like:
>
> 8<--------------------------
> 2014-04-11 08:42:06.904 PDT;LOG:
> XLogInsert;tot_nml_blks;4;tot_bkp_blks;5;tot_Xlog_Len;16168
> 2014-04-11 09:03:12.790 PDT;LOG:
> XLogInsert;tot_nml_blks;4;tot_bkp_blks;5;tot_Xlog_Len;16172
> 2014-04-11 10:16:57.949 PDT;LOG:
> XLogInsert;tot_nml_blks;3;tot_bkp_blks;5;tot_Xlog_Len;16150
> 8<--------------------------
>
> and
>
> 8<--------------------------
> 2014-04-11 11:17:08.313 PDT;LOG:
> XLogInsert;tot_nml_blks;4;tot_bkp_blks;6;tot_Xlog_Len;12332
> 2014-04-11 11:17:08.338 PDT;LOG:
> XLogInsert;tot_nml_blks;4;tot_bkp_blks;6;tot_Xlog_Len;16020
> 2014-04-11 11:17:08.389 PDT;LOG:
> XLogInsert;tot_nml_blks;4;tot_bkp_blks;6;tot_Xlog_Len;12332
> 8<--------------------------
>
>
> In other words, based on my inserted logic, it appears that there are
> 5 and 6 backup blocks on a fairly regular basis.
>
> However in xlog.h it says:
> 8<--------------------------
>   * If we backed up any disk blocks with the XLOG record, we use flag
>   * bits in xl_info to signal it.  We support backup of up to 4 disk
>   * blocks per XLOG record.
> 8<--------------------------
>
> So is my logic to record number of backup blocks wrong, or is the
> comment wrong, or am I otherwise misunderstanding something?

You're counting XLogRecData structs, not backup blocks. Each backup 
block typically consists of three XLogRecData structs, one to record a 
BkpBlock struct, one to record the data before the unused "hole" in the 
middle of the page, and one for after. Sometimes just two, if there is 
no hole to skip. See the loop just before the CRC calculation, that's 
where the XLogRecData entries for backup blocks are created.

- Heikki