WAL being written during SELECT * query

Started by Tom DalPozzoabout 9 years ago8 messagesgeneral

t.dalpozzo@gmail.com

about 9 years ago

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

Raymond O'Donnell

rod@iol.ie

about 9 years ago

In reply to: Tom DalPozzo (#1)

Re: WAL being written during SELECT * query

On 04/04/17 16:46, Tom DalPozzo 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.

As far as I know (and I could be wrong), WAL segments reflect activity
in the entire cluster, not just one table - so perhaps there is write
activity happening in other tables, or in other databases.

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Scott Marlowe

scott.marlowe@gmail.com

about 9 years ago

In reply to: Tom DalPozzo (#1)

Re: WAL being written during SELECT * query

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Tom DalPozzo

t.dalpozzo@gmail.com

about 9 years ago

In reply to: Scott Marlowe (#3)

Re: 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
Pupillo

David G. Johnston

david.g.johnston@gmail.com

about 9 years ago

In reply to: Tom DalPozzo (#4)

Re: WAL being written during SELECT * query

On Thu, Apr 6, 2017 at 8:51 AM, Tom DalPozzo <t.dalpozzo@gmail.com> wrote:

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.

Tom DalPozzo

t.dalpozzo@gmail.com

about 9 years ago

In reply to: Tom DalPozzo (#4)

Re: WAL being written during SELECT * query

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 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

Bill Moran

wmoran@potentialtech.com

about 9 years ago

In reply to: Tom DalPozzo (#6)

Re: WAL being written during SELECT * query

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>

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Tom DalPozzo

t.dalpozzo@gmail.com

about 9 years ago

In reply to: Bill Moran (#7)

Re: WAL being written during SELECT * query

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