Re: [GENERAL] WAL being written during SELECT * query

Поиск
Список
Период
Сортировка
От Tom DalPozzo
Тема Re: [GENERAL] WAL being written during SELECT * query
Дата
Msg-id CAK77FCRBzm-TNDJpBKERHVHGNMWi_Vm7iJnAsu88pj5aaWYihg@mail.gmail.com
обсуждение исходный текст
Ответы Re: [GENERAL] WAL being written during SELECT * query  ("David G. Johnston" <david.g.johnston@gmail.com>)
Re: [GENERAL] WAL being written during SELECT * query  (Tom DalPozzo <t.dalpozzo@gmail.com>)
Список pgsql-general
Дерево обсуждения


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



В списке pgsql-general по дате отправления:

Предыдущее
От: "David G. Johnston"
Дата:
Сообщение: Re: [GENERAL] Archiving data to another server using copy, psql with pipe
Следующее
От: "David G. Johnston"
Дата:
Сообщение: Re: [GENERAL] WAL being written during SELECT * query