Re: WAL being written during SELECT * query

2017-04-06 17:51 GMT+02:00 Tom DalPozzo <t(dot)dalpozzo(at)gmail(dot)com>:

>
>
> 2017-04-04 19:18 GMT+02:00 Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>:
>
>> On Tue, Apr 4, 2017 at 9:46 AM, Tom DalPozzo <t(dot)dalpozzo(at)gmail(dot)com>
>> wrote:
>> > Hi,
>> > I have a very big table (10GB).
>> > I noticed that many WAL segments are being written when elaborating read
>> > only transactions like this:
>> > select * from dati25600000000 where id >43000000 limit 1000000;
>> > I don't understand why are there WAL writings during read only
>> transactions.
>> > Regards
>> > Pupillo
>>
>>
>>
>> I think this is the db setting hint bits, but I'm no expert in that area.
>>
>
> Hi,
> I'm not able to reproduce the problem in a deterministic way. Sometimes it
> does sometimes not.
> Anyway, examining with pg_xlogdum those WAL, I get many many records like
> these:
>
> rmgr: XLOG len (rec/tot): 0/ 8173, tx: 0, lsn:
> C/E502AF28, prev C/E5028F20, desc: FPI_FOR_HINT , blkref #0: rel
> 1663/16384/18846 blk 1454439 FPW
> rmgr: XLOG len (rec/tot): 0/ 8173, tx: 0, lsn:
> C/E502CF30, prev C/E502AF28, desc: FPI_FOR_HINT , blkref #0: rel
> 1663/16384/18846 blk 1454440 FPW
> rmgr: XLOG len (rec/tot): 0/ 8173, tx: 0, lsn:
> C/E502EF38, prev C/E502CF30, desc: FPI_FOR_HINT , blkref #0: rel
> 1663/16384/18846 blk 1454441 FPW
> rmgr: XLOG len (rec/tot): 0/ 8173, tx: 0, lsn:
> C/E5030F40, prev C/E502EF38, desc: FPI_FOR_HINT , blkref #0: rel
> 1663/16384/18846 blk 1454442 FPW
> rmgr: XLOG len (rec/tot): 0/ 8173, tx: 0, lsn:
> C/E5032F48, prev C/E5030F40, desc: FPI_FOR_HINT , blkref #0: rel
> 1663/16384/18846 blk 1454443 FPW
>
> What is the meaning of FPI_FOR_HINT?
>
> I've replication slots and async replication, but slaves are kept off (PCs
> not powered).
> No other queries except that one.
> Even select count (*) from dati25600000000; gives the problem (sometimes)
> Thanks
> Pupillo
>
>
>
>
Hi, I can reproduce the problem.
1) Insert many rows (>50 milions) in the table.
2) perform a select like select count(*) in order to force the system to
read all the rows.
The FIRST time you do step 2) it writes all those FP_FOR_HINTS recs.
Next times or if the table is quite small, it doesn't do the problem.
If, instead of select count (*) you make a select which involved only a
group of tuples, it write only the recs regarding those tuples.

Regards
Pupillo