pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: Read/Write block sizes (Was: Caching by Postgres)
3+ messages / 3 participants
[nested] [flat]
* Re: Read/Write block sizes (Was: Caching by Postgres)
@ 2005-08-23 21:29 Jignesh Shah <J.K.Shah@Sun.COM>
0 siblings, 1 reply; 3+ messages in thread
From: Jignesh Shah @ 2005-08-23 21:29 UTC (permalink / raw)
To: Michael Stone <mstone+postgres@mathom.us>; +Cc: Josh Berkus <josh@agliodbs.com>; pgsql-performance
> Does that include increasing the size of read/write blocks? I've
> noticedthat with a large enough table it takes a while to do a
> sequential scan,
> even if it's cached; I wonder if the fact that it takes a million
> read(2) calls to get through an 8G table is part of that.
>
Actually some of that readaheads,etc the OS does already if it does some sort of throttling/clubbing of reads/writes. But its not enough for such types of workloads.
Here is what I think will help:
* Support for different Blocksize TABLESPACE without recompiling the code.. (Atlease support for a different Blocksize for the whole database without recompiling the code)
* Support for bigger sizes of WAL files instead of 16MB files WITHOUT recompiling the code.. Should be a tuneable if you ask me (with checkpoint_segments at 256.. you have too many 16MB files in the log directory) (This will help OLTP benchmarks more since now they don't spend time rotating log files)
* Introduce a multiblock or extent tunable variable where you can define a multiple of 8K (or BlockSize tuneable) to read a bigger chunk and store it in the bufferpool.. (Maybe writes too) (Most devices now support upto 1MB chunks for reads and writes)
*There should be a way to preallocate files for TABLES in TABLESPACES otherwise with multiple table writes in the same filesystem ends with fragmented files which causes poor "READS" from the files.
* With 64bit 1GB file chunks is also moot.. Maybe it should be tuneable too like 100GB without recompiling the code.
Why recompiling is bad? Most companies that will support Postgres will support their own binaries and they won't prefer different versions of binaries for different blocksizes, different WAL file sizes, etc... and hence more function using the same set of binaries is more desirable in enterprise environments
Regards,
Jignesh
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Read/Write block sizes (Was: Caching by Postgres)
@ 2005-08-23 23:12 Michael Stone <mstone+postgres@mathom.us>
parent: Jignesh Shah <J.K.Shah@Sun.COM>
0 siblings, 1 reply; 3+ messages in thread
From: Michael Stone @ 2005-08-23 23:12 UTC (permalink / raw)
To: Jignesh Shah <J.K.Shah@Sun.COM>; +Cc: pgsql-performance
On Tue, Aug 23, 2005 at 05:29:01PM -0400, Jignesh Shah wrote:
>Actually some of that readaheads,etc the OS does already if it does
>some sort of throttling/clubbing of reads/writes.
Note that I specified the fully cached case--even with the workload in
RAM the system still has to process a heck of a lot of read calls.
>* Introduce a multiblock or extent tunable variable where you can
>define a multiple of 8K (or BlockSize tuneable) to read a bigger chunk
>and store it in the bufferpool.. (Maybe writes too) (Most devices now
>support upto 1MB chunks for reads and writes)
Yeah. The problem with relying on OS readahead is that the OS doesn't
know whether you're doing a sequential scan or an index scan; if you
have the OS agressively readahead you'll kill your seek performance.
OTOH, if you don't do readaheads you'll kill your sequential scan
performance. At the app level you know which makes sense for each
operation.
Mike Stone
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Read/Write block sizes (Was: Caching by Postgres)
@ 2005-08-23 23:44 Jeffrey W. Baker <jwbaker@acm.org>
parent: Michael Stone <mstone+postgres@mathom.us>
0 siblings, 0 replies; 3+ messages in thread
From: Jeffrey W. Baker @ 2005-08-23 23:44 UTC (permalink / raw)
To: Michael Stone <mstone+postgres@mathom.us>; +Cc: Jignesh Shah <J.K.Shah@Sun.COM>; pgsql-performance
On Tue, 2005-08-23 at 19:12 -0400, Michael Stone wrote:
> On Tue, Aug 23, 2005 at 05:29:01PM -0400, Jignesh Shah wrote:
> >Actually some of that readaheads,etc the OS does already if it does
> >some sort of throttling/clubbing of reads/writes.
>
> Note that I specified the fully cached case--even with the workload in
> RAM the system still has to process a heck of a lot of read calls.
>
> >* Introduce a multiblock or extent tunable variable where you can
> >define a multiple of 8K (or BlockSize tuneable) to read a bigger chunk
> >and store it in the bufferpool.. (Maybe writes too) (Most devices now
> >support upto 1MB chunks for reads and writes)
>
> Yeah. The problem with relying on OS readahead is that the OS doesn't
> know whether you're doing a sequential scan or an index scan; if you
> have the OS agressively readahead you'll kill your seek performance.
> OTOH, if you don't do readaheads you'll kill your sequential scan
> performance. At the app level you know which makes sense for each
> operation.
This is why we have MADVISE_RANDOM and MADVISE_SEQUENTIAL.
-jwb
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-08-23 23:44 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-08-23 21:29 Re: Read/Write block sizes (Was: Caching by Postgres) Jignesh Shah <J.K.Shah@Sun.COM>
2005-08-23 23:12 ` Michael Stone <mstone+postgres@mathom.us>
2005-08-23 23:44 ` Jeffrey W. Baker <jwbaker@acm.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox