Обсуждение: Re: [GENERAL] WAL being written during SELECT * query
2017-04-04 19:18 GMT+02:00 Scott Marlowe <scott.marlowe@gmail.com>:
On Tue, Apr 4, 2017 at 9:46 AM, Tom DalPozzo <t.dalpozzo@gmail.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
Thanks
Pupillo
What is the meaning of FPI_FOR_HINT?
Full Page Image for Hint [Bits]
Its noted as being dependent upon checksums being enabled.
I have a feel for the interactions involved here but not enough to explain them in detail.
David J.
2017-04-06 17:51 GMT+02:00 Tom DalPozzo <t.dalpozzo@gmail.com>:
2017-04-04 19:18 GMT+02:00 Scott Marlowe <scott.marlowe@gmail.com>:On Tue, Apr 4, 2017 at 9:46 AM, Tom DalPozzo <t.dalpozzo@gmail.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 FPWrmgr: 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 FPWrmgr: 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 FPWrmgr: 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 FPWrmgr: 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 FPWWhat 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)
ThanksPupillo
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
> >> On Tue, Apr 4, 2017 at 9:46 AM, Tom DalPozzo <t.dalpozzo@gmail.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. These are hint bits. The mechanism and behavior are known and documented: https://wiki.postgresql.org/wiki/Hint_Bits -- Bill Moran <wmoran@potentialtech.com>
2017-04-10 16:49 GMT+02:00 Bill Moran <wmoran@potentialtech.com>:
> >> On Tue, Apr 4, 2017 at 9:46 AM, Tom DalPozzo <t.dalpozzo@gmail.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.
These are hint bits. The mechanism and behavior are known and documented:
https://wiki.postgresql.org/wiki/Hint_Bits
--
Bill Moran <wmoran@potentialtech.com>
Hi, OK! Thank you very much
Pupillo