Re: automatic analyze: readahead - add "IO read time" log message

2021-08-27 Thread Stephen Frost
Greetings, * Stephen Frost (sfr...@snowman.net) wrote: > * Egor Rogov (e.ro...@postgrespro.ru) wrote: > > On 11.02.2021 01:10, Stephen Frost wrote: > > >* Heikki Linnakangas (hlinn...@iki.fi) wrote: > > >>On 05/02/2021 23:22, Stephen Frost wrote: > > >>>Unless there's anything else on this, I'll c

Re: automatic analyze: readahead - add "IO read time" log message

2021-05-25 Thread Stephen Frost
Greetings, * Justin Pryzby (pry...@telsasoft.com) wrote: > This patch adds hits/misses/dirtied, but explain says > hit/read/dirtied/written. > > Should it say "read" instead of "misses" ? > > src/backend/access/heap/vacuumlazy.c: >_("buffer u

Re: automatic analyze: readahead - add "IO read time" log message

2021-05-25 Thread Justin Pryzby
This patch adds hits/misses/dirtied, but explain says hit/read/dirtied/written. Should it say "read" instead of "misses" ? src/backend/access/heap/vacuumlazy.c: _("buffer usage: %lld hits, %lld misses, %lld dirtied\n"), src/backend/commands/exp

Re: automatic analyze: readahead - add "IO read time" log message

2021-05-25 Thread Stephen Frost
Greetings, * Egor Rogov (e.ro...@postgrespro.ru) wrote: > On 11.02.2021 01:10, Stephen Frost wrote: > >* Heikki Linnakangas (hlinn...@iki.fi) wrote: > >>On 05/02/2021 23:22, Stephen Frost wrote: > >>>Unless there's anything else on this, I'll commit these sometime next > >>>week. > >>One more thin

Re: automatic analyze: readahead - add "IO read time" log message

2021-05-25 Thread Egor Rogov
Hi, On 11.02.2021 01:10, Stephen Frost wrote: Greetings, * Heikki Linnakangas (hlinn...@iki.fi) wrote: On 05/02/2021 23:22, Stephen Frost wrote: Unless there's anything else on this, I'll commit these sometime next week. One more thing: Instead of using 'effective_io_concurrency' GUC direct

Re: automatic analyze: readahead - add "IO read time" log message

2021-03-16 Thread Stephen Frost
Greetings, * Stephen Frost (sfr...@snowman.net) wrote: > > >> I think e.g. prefetch_targblock could be moved to the next #ifdef, which > > >> will eliminate the one-line ifdef. > > > > > > Sure, done in the attached. > > > > > > Thanks for the review! Unless there's other comments, I'll plan to

Re: automatic analyze: readahead - add "IO read time" log message

2021-03-11 Thread Stephen Frost
Greetings, * Tomas Vondra (tomas.von...@enterprisedb.com) wrote: > On 3/12/21 1:11 AM, Stephen Frost wrote: > > * Tomas Vondra (tomas.von...@enterprisedb.com) wrote: > >> On 3/8/21 8:42 PM, Stephen Frost wrote: > >>> * Tomas Vondra (tomas.von...@enterprisedb.com) wrote: > On 2/10/21 11:10 PM,

Re: automatic analyze: readahead - add "IO read time" log message

2021-03-11 Thread Tomas Vondra
On 3/12/21 1:11 AM, Stephen Frost wrote: > Greetings, > > * Tomas Vondra (tomas.von...@enterprisedb.com) wrote: >> On 3/8/21 8:42 PM, Stephen Frost wrote: >>> * Tomas Vondra (tomas.von...@enterprisedb.com) wrote: On 2/10/21 11:10 PM, Stephen Frost wrote: > * Heikki Linnakangas (hlinn..

Re: automatic analyze: readahead - add "IO read time" log message

2021-03-11 Thread Stephen Frost
Greetings, * Tomas Vondra (tomas.von...@enterprisedb.com) wrote: > On 3/8/21 8:42 PM, Stephen Frost wrote: > > * Tomas Vondra (tomas.von...@enterprisedb.com) wrote: > >> On 2/10/21 11:10 PM, Stephen Frost wrote: > >>> * Heikki Linnakangas (hlinn...@iki.fi) wrote: > On 05/02/2021 23:22, Stephe

Re: automatic analyze: readahead - add "IO read time" log message

2021-03-10 Thread Tomas Vondra
On 3/8/21 8:42 PM, Stephen Frost wrote: > Greetings, > > * Tomas Vondra (tomas.von...@enterprisedb.com) wrote: >> On 2/10/21 11:10 PM, Stephen Frost wrote: >>> * Heikki Linnakangas (hlinn...@iki.fi) wrote: On 05/02/2021 23:22, Stephen Frost wrote: > Unless there's anything else on this, I

Re: automatic analyze: readahead - add "IO read time" log message

2021-03-08 Thread Stephen Frost
Greetings, * Tomas Vondra (tomas.von...@enterprisedb.com) wrote: > On 2/10/21 11:10 PM, Stephen Frost wrote: > > * Heikki Linnakangas (hlinn...@iki.fi) wrote: > >> On 05/02/2021 23:22, Stephen Frost wrote: > >>> Unless there's anything else on this, I'll commit these sometime next > >>> week. > >>

Re: automatic analyze: readahead - add "IO read time" log message

2021-03-07 Thread Tomas Vondra
Hi, On 2/10/21 11:10 PM, Stephen Frost wrote: > Greetings, > > * Heikki Linnakangas (hlinn...@iki.fi) wrote: >> On 05/02/2021 23:22, Stephen Frost wrote: >>> Unless there's anything else on this, I'll commit these sometime next >>> week. >> >> One more thing: Instead of using 'effective_io_concur

Re: automatic analyze: readahead - add "IO read time" log message

2021-02-10 Thread Stephen Frost
Greetings, * Heikki Linnakangas (hlinn...@iki.fi) wrote: > On 05/02/2021 23:22, Stephen Frost wrote: > >Unless there's anything else on this, I'll commit these sometime next > >week. > > One more thing: Instead of using 'effective_io_concurrency' GUC directly, > should call get_tablespace_mainten

Re: automatic analyze: readahead - add "IO read time" log message

2021-02-08 Thread Heikki Linnakangas
On 05/02/2021 23:22, Stephen Frost wrote: Unless there's anything else on this, I'll commit these sometime next week. One more thing: Instead of using 'effective_io_concurrency' GUC directly, should call get_tablespace_maintenance_io_concurrency(). - Heikki

Re: automatic analyze: readahead - add "IO read time" log message

2021-02-05 Thread Stephen Frost
Greetings, * Heikki Linnakangas (hlinn...@iki.fi) wrote: > On 13/01/2021 23:17, Stephen Frost wrote: > >Would be great to get a review / comments from others as to if there's > >any concerns. I'll admit that it seems reasonably straight-forward to > >me, but hey, I wrote most of it, so that's not

Re: automatic analyze: readahead - add "IO read time" log message

2021-01-26 Thread Heikki Linnakangas
On 13/01/2021 23:17, Stephen Frost wrote: Would be great to get a review / comments from others as to if there's any concerns. I'll admit that it seems reasonably straight-forward to me, but hey, I wrote most of it, so that's not really a fair assessment... ;) Look good overall. A few minor co

Re: automatic analyze: readahead - add "IO read time" log message

2021-01-13 Thread Stephen Frost
Greetings, * Stephen Frost (sfr...@snowman.net) wrote: > * Tomas Vondra (tomas.von...@enterprisedb.com) wrote: > > Thanks. I'll do some testing/benchmarking once my machines are free, in > > a couple days perhaps. But as I said before, I don't expect this to > > behave very differently from other

Re: automatic analyze: readahead - add "IO read time" log message

2020-12-05 Thread Stephen Frost
Greetings, * Tomas Vondra (tomas.von...@enterprisedb.com) wrote: > Thanks. I'll do some testing/benchmarking once my machines are free, in > a couple days perhaps. But as I said before, I don't expect this to > behave very differently from other places that already do prefetching. Agreed, but wou

Re: automatic analyze: readahead - add "IO read time" log message

2020-11-09 Thread Tomas Vondra
On 11/9/20 7:06 PM, Stephen Frost wrote: > Greetings, > > * Tomas Vondra (tomas.von...@2ndquadrant.com) wrote: >> On 11/4/20 5:02 PM, Stephen Frost wrote: >>> * Tomas Vondra (tomas.von...@2ndquadrant.com) wrote: > If you highlight "738754560" in the output it appears to duplicate the > sys

Re: automatic analyze: readahead - add "IO read time" log message

2020-11-09 Thread Stephen Frost
Greetings, * Tomas Vondra (tomas.von...@2ndquadrant.com) wrote: > On 11/4/20 5:02 PM, Stephen Frost wrote: > >* Tomas Vondra (tomas.von...@2ndquadrant.com) wrote: > >>>If you highlight "738754560" in the output it appears to duplicate the > >>>syscalls issued until it preads() - in case of "738754

Re: automatic analyze: readahead - add "IO read time" log message

2020-11-04 Thread Tomas Vondra
Hi, On 11/4/20 5:02 PM, Stephen Frost wrote: Greetings, * Tomas Vondra (tomas.von...@2ndquadrant.com) wrote: If you highlight "738754560" in the output it appears to duplicate the syscalls issued until it preads() - in case of "738754560" offset it was asked for 3 times. Also I wouldn't imagi

Re: automatic analyze: readahead - add "IO read time" log message

2020-11-04 Thread Stephen Frost
Greetings, * Tomas Vondra (tomas.von...@2ndquadrant.com) wrote: > >If you highlight "738754560" in the output it appears to duplicate the > >syscalls issued until it preads() - in case of "738754560" offset it was > >asked for 3 times. Also I wouldn't imagine in wildest dreams that > >posix_fadvi

Re: automatic analyze: readahead - add "IO read time" log message

2020-11-04 Thread Stephen Frost
Greetings, * Tomas Vondra (tomas.von...@2ndquadrant.com) wrote: > On Wed, Nov 04, 2020 at 09:07:59AM +, Jakub Wartak wrote: > >I saw up 410MB/s for a few seconds with this patch on NVMe, and that's > >huge ~5.2x improvement which is amazing for a such simple patch. Nice! > >The system and da

Re: automatic analyze: readahead - add "IO read time" log message

2020-11-04 Thread Tomas Vondra
On Wed, Nov 04, 2020 at 09:07:59AM +, Jakub Wartak wrote: Greetings Stephen, I saw up 410MB/s for a few seconds with this patch on NVMe, and that's huge ~5.2x improvement which is amazing for a such simple patch. The system and data was identical like last time, so results are directly co

Re: automatic analyze: readahead - add "IO read time" log message

2020-11-04 Thread Jakub Wartak
e in wildest dreams that posix_fadvise(POSIX_FADV_WILLNEED) is such a cheap syscall. -J. From: Stephen Frost Sent: Tuesday, November 3, 2020 6:47 PM To: Jakub Wartak Cc: pgsql-hackers Subject: Re: automatic analyze: readahead - add "IO read time" log me

Re: automatic analyze: readahead - add "IO read time" log message

2020-11-03 Thread Stephen Frost
Greetings, * Jakub Wartak (jakub.war...@tomtom.com) wrote: > >Interesting that you weren't seeing any benefit to disabling readahead. > > I've got some free minutes and I have repeated the exercise in more realistic > and strict environment that previous one to conclude that the current > situat

Re: automatic analyze: readahead - add "IO read time" log message

2020-11-03 Thread Jakub Wartak
Hi Stephen, hackers, >> > With all those 'readahead' calls it certainly makes one wonder if the >> > Linux kernel is reading more than just the block we're looking for >> > because it thinks we're doing a sequential read and will therefore want >> > the next few blocks when, in reality, we're goin

Re: automatic analyze: readahead - add "IO read time" log message

2020-10-27 Thread Stephen Frost
Greetings Jakub, * Jakub Wartak (jakub.war...@tomtom.com) wrote: > > The analyze is doing more-or-less random i/o since it's skipping through > > the table picking out select blocks, not doing regular sequential i/o. > VS > >> Breakpoint 1, heapam_scan_analyze_next_block (scan=0x10c8098, > >> blo

Re: automatic analyze: readahead - add "IO read time" log message

2020-10-27 Thread Jakub Wartak
Hi Stephen, hackers, > The analyze is doing more-or-less random i/o since it's skipping through > the table picking out select blocks, not doing regular sequential i/o. VS >> Breakpoint 1, heapam_scan_analyze_next_block (scan=0x10c8098, >> blockno=19890910, bstrategy=0x1102278) at heapam_handler.

Re: automatic analyze: readahead - add "IO read time" log message

2020-10-26 Thread Stephen Frost
Greetings, * Jakub Wartak (jakub.war...@tomtom.com) wrote: > I have I hope interesting observation (and nano patch proposal) on system > where statistics freshness is a critical factor. Autovacuum/autogathering > statistics was tuned to be pretty very aggressive: > autovacuum_vacuum_cost_delay=0

automatic analyze: readahead - add "IO read time" log message

2020-10-26 Thread Jakub Wartak
Greetings hackers, I have I hope interesting observation (and nano patch proposal) on system where statistics freshness is a critical factor. Autovacuum/autogathering statistics was tuned to be pretty very aggressive: autovacuum_vacuum_cost_delay=0 (makes autovacuum_vacuum_cost_limit irrelevant)