pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: Read/Write block sizes
24+ messages / 15 participants
[nested] [flat]
* Re: Read/Write block sizes
@ 2005-08-23 22:09 Chris Browne <cbbrowne@acm.org>
2005-08-23 23:24 ` Re: Read/Write block sizes Michael Stone <mstone+postgres@mathom.us>
2005-08-23 23:36 ` Re: Read/Write block sizes Jim C. Nasby <jnasby@pervasive.com>
2005-08-24 01:25 ` Re: Read/Write block sizes Steve Poe <spoe@sfnet.cc>
0 siblings, 3 replies; 24+ messages in thread
From: Chris Browne @ 2005-08-23 22:09 UTC (permalink / raw)
To: pgsql-performance
J.K.Shah@Sun.COM (Jignesh Shah) writes:
>> 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
Every single one of these still begs the question of whether the
changes will have a *material* impact on performance.
What we have been finding, as RAID controllers get smarter, is that it
is getting increasingly futile to try to attach knobs to 'disk stuff;'
it is *way* more effective to add a few more spindles to an array than
it is to fiddle with which disks are to be allocated to what database
'objects.'
The above suggested 'knobs' are all going to add to complexity and it
is NOT evident that any of them will forcibly help.
I could be wrong; code contributions combined with Actual Benchmarking
would be the actual proof of the merits of the ideas.
But it also suggests another question, namely...
Will these represent more worthwhile improvements to speed than
working on other optimizations that are on the TODO list?
If someone spends 100h working on one of these items, and gets a 2%
performance improvement, that's almost certain to be less desirable
than spending 50h on something else that gets a 4% improvement.
And we might discover that memory management improvements in Linux
2.6.16 or FreeBSD 5.5 allow some OS kernels to provide some such
improvements "for free" behind our backs without *any* need to write
database code. :-)
--
let name="cbbrowne" and tld="ntlug.org" in name ^ "@" ^ tld;;
http://www3.sympatico.ca/cbbrowne/postgresql.html
Wiener's Law of Libraries:
There are no answers, only cross references.
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Read/Write block sizes
2005-08-23 22:09 Re: Read/Write block sizes Chris Browne <cbbrowne@acm.org>
@ 2005-08-23 23:24 ` Michael Stone <mstone+postgres@mathom.us>
2 siblings, 0 replies; 24+ messages in thread
From: Michael Stone @ 2005-08-23 23:24 UTC (permalink / raw)
To: Chris Browne <cbbrowne@acm.org>; +Cc: pgsql-performance
On Tue, Aug 23, 2005 at 06:09:09PM -0400, Chris Browne wrote:
>What we have been finding, as RAID controllers get smarter, is that it
>is getting increasingly futile to try to attach knobs to 'disk stuff;'
>it is *way* more effective to add a few more spindles to an array than
>it is to fiddle with which disks are to be allocated to what database
>'objects.'
That statement doesn't say anything about trying to maximize performance
to or from a disk array. Yes, controllers are getting smarter--but they
aren't omnicient. IME an I/O bound sequential table scan doesn't get
data moving off the disk nearly as fast as say, a dd with a big ibs.
Why? There's obviously a lot of factors at work, but one of those
factors is that the raid controller can optimize "grab this meg" a lot
more than it can optimize "grab this 8k".
Mike Stone
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Read/Write block sizes
2005-08-23 22:09 Re: Read/Write block sizes Chris Browne <cbbrowne@acm.org>
@ 2005-08-23 23:36 ` Jim C. Nasby <jnasby@pervasive.com>
2005-08-24 02:22 ` Re: Read/Write block sizes Jignesh K. Shah <J.K.Shah@Sun.COM>
2 siblings, 1 reply; 24+ messages in thread
From: Jim C. Nasby @ 2005-08-23 23:36 UTC (permalink / raw)
To: Chris Browne <cbbrowne@acm.org>; +Cc: pgsql-performance
On Tue, Aug 23, 2005 at 06:09:09PM -0400, Chris Browne wrote:
> J.K.Shah@Sun.COM (Jignesh Shah) writes:
> >> 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
>
> Every single one of these still begs the question of whether the
> changes will have a *material* impact on performance.
How many of these things are currently easy to change with a recompile?
I should be able to start testing some of these ideas in the near
future, if they only require minor code or configure changes.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com 512-569-9461
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Read/Write block sizes
2005-08-23 22:09 Re: Read/Write block sizes Chris Browne <cbbrowne@acm.org>
2005-08-23 23:36 ` Re: Read/Write block sizes Jim C. Nasby <jnasby@pervasive.com>
@ 2005-08-24 02:22 ` Jignesh K. Shah <J.K.Shah@Sun.COM>
2005-08-24 13:52 ` Re: Read/Write block sizes Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 1 reply; 24+ messages in thread
From: Jignesh K. Shah @ 2005-08-24 02:22 UTC (permalink / raw)
To: Jim C. Nasby <jnasby@pervasive.com>; +Cc: Chris Browne <cbbrowne@acm.org>; pgsql-performance
Hi Jim,
| How many of these things are currently easy to change with a recompile?
| I should be able to start testing some of these ideas in the near
| future, if they only require minor code or configure changes.
The following
* Data File Size 1GB
* WAL File Size of 16MB
* Block Size of 8K
Are very easy to change with a recompile.. A Tunable will be greatly
prefered as it will allow one binary for different tunings
* MultiBlock read/write
Is not available but will greatly help in reducing the number of system
calls which will only increase as the size of the database increases if
something is not done about i.
* Pregrown files... maybe not important at this point since TABLESPACE
can currently work around it a bit (Just need to create a different file
system for each tablespace
But if you really think hardware & OS is the answer for all small
things...... I think we should now start to look on how to make Postgres
Multi-threaded or multi-processed for each connection. With the influx
of "Dual-Core" or "Multi-Core" being the fad.... Postgres can have the
cutting edge if somehow exploiting cores is designed.
Somebody mentioned that adding CPU to Postgres workload halved the
average CPU usage...
YEAH... PostgreSQL uses only 1 CPU per connection (assuming 100%
usage) so if you add another CPU it is idle anyway and the system will
report only 50% :-) BUT the importing to measure is.. whether the query
time was cut down or not? ( No flames I am sure you were talking about
multi-connection multi-user environment :-) ) But my point is then this
approach is worth the ROI and the time and effort spent to solve this
problem.
I actually vote for a multi-threaded solution for each connection while
still maintaining seperate process for each connections... This way the
fundamental architecture of Postgres doesn't change, however a
multi-threaded connection can then start to exploit different cores..
(Maybe have tunables for number of threads to read data files who
knows.. If somebody is interested in actually working a design ..
contact me and I will be interested in assisting this work.
Regards,
Jignesh
Jim C. Nasby wrote:
>On Tue, Aug 23, 2005 at 06:09:09PM -0400, Chris Browne wrote:
>
>
>>J.K.Shah@Sun.COM (Jignesh Shah) writes:
>>
>>
>>>>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
>>>
>>>
>>Every single one of these still begs the question of whether the
>>changes will have a *material* impact on performance.
>>
>>
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Read/Write block sizes
2005-08-23 22:09 Re: Read/Write block sizes Chris Browne <cbbrowne@acm.org>
2005-08-23 23:36 ` Re: Read/Write block sizes Jim C. Nasby <jnasby@pervasive.com>
2005-08-24 02:22 ` Re: Read/Write block sizes Jignesh K. Shah <J.K.Shah@Sun.COM>
@ 2005-08-24 13:52 ` Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 0 replies; 24+ messages in thread
From: Bruce Momjian @ 2005-08-24 13:52 UTC (permalink / raw)
To: Jignesh K. Shah <J.K.Shah@Sun.COM>; +Cc: Jim C. Nasby <jnasby@pervasive.com>; Chris Browne <cbbrowne@acm.org>; pgsql-performance
This thread covers several performance ideas. First is the idea that
more parameters should be configurable. While this seems like a noble
goal, we try to make parameters auto-tuning, or if users have to
configure it, the parameter should be useful for a significant number of
users.
In the commercial software world, if you can convince your boss that a
feature/knob is useful, it usually gets into the product.
Unfortunately, this leads to the golden doorknob on a shack, where some
features are out of sync with the rest of the product in terms of
usefulness and utility. With open source, if a feature can not be
auto-tuned, or has significant overhead, the features has to be
implemented and then proven to be a benefit.
In terms of adding async I/O, threading, and other things, it might make
sense to explore how these could be implemented in a way that fits the
above criteria.
---------------------------------------------------------------------------
Jignesh K. Shah wrote:
> Hi Jim,
>
> | How many of these things are currently easy to change with a recompile?
> | I should be able to start testing some of these ideas in the near
> | future, if they only require minor code or configure changes.
>
>
> The following
> * Data File Size 1GB
> * WAL File Size of 16MB
> * Block Size of 8K
>
> Are very easy to change with a recompile.. A Tunable will be greatly
> prefered as it will allow one binary for different tunings
>
> * MultiBlock read/write
>
> Is not available but will greatly help in reducing the number of system
> calls which will only increase as the size of the database increases if
> something is not done about i.
>
> * Pregrown files... maybe not important at this point since TABLESPACE
> can currently work around it a bit (Just need to create a different file
> system for each tablespace
>
> But if you really think hardware & OS is the answer for all small
> things...... I think we should now start to look on how to make Postgres
> Multi-threaded or multi-processed for each connection. With the influx
> of "Dual-Core" or "Multi-Core" being the fad.... Postgres can have the
> cutting edge if somehow exploiting cores is designed.
>
> Somebody mentioned that adding CPU to Postgres workload halved the
> average CPU usage...
> YEAH... PostgreSQL uses only 1 CPU per connection (assuming 100%
> usage) so if you add another CPU it is idle anyway and the system will
> report only 50% :-) BUT the importing to measure is.. whether the query
> time was cut down or not? ( No flames I am sure you were talking about
> multi-connection multi-user environment :-) ) But my point is then this
> approach is worth the ROI and the time and effort spent to solve this
> problem.
>
> I actually vote for a multi-threaded solution for each connection while
> still maintaining seperate process for each connections... This way the
> fundamental architecture of Postgres doesn't change, however a
> multi-threaded connection can then start to exploit different cores..
> (Maybe have tunables for number of threads to read data files who
> knows.. If somebody is interested in actually working a design ..
> contact me and I will be interested in assisting this work.
>
> Regards,
> Jignesh
>
>
> Jim C. Nasby wrote:
>
> >On Tue, Aug 23, 2005 at 06:09:09PM -0400, Chris Browne wrote:
> >
> >
> >>J.K.Shah@Sun.COM (Jignesh Shah) writes:
> >>
> >>
> >>>>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
> >>>
> >>>
> >>Every single one of these still begs the question of whether the
> >>changes will have a *material* impact on performance.
> >>
> >>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Read/Write block sizes
2005-08-23 22:09 Re: Read/Write block sizes Chris Browne <cbbrowne@acm.org>
@ 2005-08-24 01:25 ` Steve Poe <spoe@sfnet.cc>
2005-08-24 02:31 ` Re: Read/Write block sizes Josh Berkus <josh@agliodbs.com>
2005-08-24 16:12 ` Re: Read/Write block sizes Chris Browne <cbbrowne@acm.org>
2 siblings, 2 replies; 24+ messages in thread
From: Steve Poe @ 2005-08-24 01:25 UTC (permalink / raw)
To: Chris Browne <cbbrowne@acm.org>; +Cc: pgsql-performance
Chris,
Unless I am wrong, you're making the assumpting the amount of time spent
and ROI is known. Maybe those who've been down this path know how to get
that additional 2-4% in 30 minutes or less?
While each person and business' performance gains (or not) could vary,
someone spending the 50-100h to gain 2-4% over a course of a month for a
24x7 operation would seem worth the investment?
I would assume that dbt2 with STP helps minimize the amount of hours
someone has to invest to determine performance gains with configurable
options?
Steve Poe
> If someone spends 100h working on one of these items, and gets a 2%
> performance improvement, that's almost certain to be less desirable
> than spending 50h on something else that gets a 4% improvement.
>
> And we might discover that memory management improvements in Linux
> 2.6.16 or FreeBSD 5.5 allow some OS kernels to provide some such
> improvements "for free" behind our backs without *any* need to write
> database code. :-)
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Read/Write block sizes
2005-08-23 22:09 Re: Read/Write block sizes Chris Browne <cbbrowne@acm.org>
2005-08-24 01:25 ` Re: Read/Write block sizes Steve Poe <spoe@sfnet.cc>
@ 2005-08-24 02:31 ` Josh Berkus <josh@agliodbs.com>
2005-08-24 03:07 ` Re: Read/Write block sizes Jeffrey W. Baker <jwbaker@acm.org>
2005-08-24 04:00 ` Re: Read/Write block sizes Alan Stange <stange@rentec.com>
1 sibling, 2 replies; 24+ messages in thread
From: Josh Berkus @ 2005-08-24 02:31 UTC (permalink / raw)
To: pgsql-performance; +Cc: Steve Poe <spoe@sfnet.cc>; Chris Browne <cbbrowne@acm.org>
Steve,
> I would assume that dbt2 with STP helps minimize the amount of hours
> someone has to invest to determine performance gains with configurable
> options?
Actually, these I/O operation issues show up mainly with DW workloads, so the
STP isn't much use there. If I can ever get some of these machines back
from the build people, I'd like to start testing some stuff.
One issue with testing this is that currently PostgreSQL doesn't support block
sizes above 128K. We've already done testing on that (well, Mark has) and
the performance gains aren't even worth the hassle of remembering you're on a
different block size (like, +4%).
What the Sun people have done with other DB systems is show that substantial
performance gains are possible on large databases (>100G) using block sizes
of 1MB. I believe that's possible (and that it probably makes more of a
difference on Solaris than on BSD) but we can't test it without some hackery
first.
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Read/Write block sizes
2005-08-23 22:09 Re: Read/Write block sizes Chris Browne <cbbrowne@acm.org>
2005-08-24 01:25 ` Re: Read/Write block sizes Steve Poe <spoe@sfnet.cc>
2005-08-24 02:31 ` Re: Read/Write block sizes Josh Berkus <josh@agliodbs.com>
@ 2005-08-24 03:07 ` Jeffrey W. Baker <jwbaker@acm.org>
2005-08-24 05:10 ` Re: Read/Write block sizes Tom Lane <tgl@sss.pgh.pa.us>
2005-08-24 05:20 ` Re: Read/Write block sizes Guy Thornley <guy@esphion.com>
1 sibling, 2 replies; 24+ messages in thread
From: Jeffrey W. Baker @ 2005-08-24 03:07 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-performance@postgresql.org, Steve Poe <spoe@sfnet.cc>; Chris Browne <cbbrowne@acm.org>
On Tue, 2005-08-23 at 19:31 -0700, Josh Berkus wrote:
> Steve,
>
> > I would assume that dbt2 with STP helps minimize the amount of hours
> > someone has to invest to determine performance gains with configurable
> > options?
>
> Actually, these I/O operation issues show up mainly with DW workloads, so the
> STP isn't much use there. If I can ever get some of these machines back
> from the build people, I'd like to start testing some stuff.
>
> One issue with testing this is that currently PostgreSQL doesn't support block
> sizes above 128K. We've already done testing on that (well, Mark has) and
> the performance gains aren't even worth the hassle of remembering you're on a
> different block size (like, +4%).
>
> What the Sun people have done with other DB systems is show that substantial
> performance gains are possible on large databases (>100G) using block sizes
> of 1MB. I believe that's possible (and that it probably makes more of a
> difference on Solaris than on BSD) but we can't test it without some hackery
> first.
To get decent I/O you need 1MB fundamental units all the way down the
stack. You need a filesystem that can take a 1MB write well, and you
need an I/O scheduler that will keep it together, and you need a storage
controller that can eat a 1MB request at once. Ideally you'd like an
architecture with a 1MB page (Itanium has this, and AMD64 Linux will
soon have this.) The Lustre people have done some work in this area,
opening up the datapaths in the kernel so they can keep the hardware
really working. They even modified the QLogic SCSI/FC driver so it
supports such large transfers. Their work has shown that you can get
significant perf boost on Linux just by thinking in terms of larger
transfers.
Unfortunately I'm really afraid that this conversation is about trees
when the forest is the problem. PostgreSQL doesn't even have an async
reader, which is the sort of thing that could double or triple its
performance. You're talking about block sizes and such, but the kinds
of improvements you can get there are in the tens of percents at most.
-jwb
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Read/Write block sizes
2005-08-23 22:09 Re: Read/Write block sizes Chris Browne <cbbrowne@acm.org>
2005-08-24 01:25 ` Re: Read/Write block sizes Steve Poe <spoe@sfnet.cc>
2005-08-24 02:31 ` Re: Read/Write block sizes Josh Berkus <josh@agliodbs.com>
2005-08-24 03:07 ` Re: Read/Write block sizes Jeffrey W. Baker <jwbaker@acm.org>
@ 2005-08-24 05:10 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-08-24 16:26 ` Re: Read/Write block sizes Josh Berkus <josh@agliodbs.com>
1 sibling, 1 reply; 24+ messages in thread
From: Tom Lane @ 2005-08-24 05:10 UTC (permalink / raw)
To: Jeffrey W. Baker <jwbaker@acm.org>; +Cc: Josh Berkus <josh@agliodbs.com>; pgsql-performance@postgresql.org, Steve Poe <spoe@sfnet.cc>; Chris Browne <cbbrowne@acm.org>
"Jeffrey W. Baker" <jwbaker@acm.org> writes:
> To get decent I/O you need 1MB fundamental units all the way down the
> stack.
It would also be a good idea to have an application that isn't likely
to change a single bit in a 1MB range and then expect you to record
that change. This pretty much lets Postgres out of the picture.
regards, tom lane
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Read/Write block sizes
2005-08-23 22:09 Re: Read/Write block sizes Chris Browne <cbbrowne@acm.org>
2005-08-24 01:25 ` Re: Read/Write block sizes Steve Poe <spoe@sfnet.cc>
2005-08-24 02:31 ` Re: Read/Write block sizes Josh Berkus <josh@agliodbs.com>
2005-08-24 03:07 ` Re: Read/Write block sizes Jeffrey W. Baker <jwbaker@acm.org>
2005-08-24 05:10 ` Re: Read/Write block sizes Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-08-24 16:26 ` Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 24+ messages in thread
From: Josh Berkus @ 2005-08-24 16:26 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Jeffrey W. Baker <jwbaker@acm.org>; pgsql-performance@postgresql.org, Steve Poe <spoe@sfnet.cc>; Chris Browne <cbbrowne@acm.org>
Tom, Gavin,
> > To get decent I/O you need 1MB fundamental units all the way down the
> > stack.
>
> It would also be a good idea to have an application that isn't likely
> to change a single bit in a 1MB range and then expect you to record
> that change. This pretty much lets Postgres out of the picture.
We're looking at this pretty much just for data warehousing, where you
constantly have gigabytes of data which don't change from month to month or
even year to year. I agree that it would *not* be an optimization for OLTP
systems. Which is why a build-time option would be fine.
> Ummm... I don't see anything here which will be a win for Postgres. The
> transactional semantics we're interested in are fairly complex:
>
> 1) Modifications to multiple objects can become visible to the system
> atomically
> 2) On error, a series of modifications which had been grouped together
> within a transaction can be rolled back
> 3) Using object version information, determine which version of which
> object is visible to a given session
> 4) Using version information and locking, detect and resolve read/write
> and write/write conflicts
I wasn't thinking of database transactions. I was thinking specifically of
using Reiser4 transactions (and other transactional filesytems) to do things
like eliminate the need for full page writes in the WAL. Filesystems are
low-level things which should take care of low-level needs, like making sure
an 8K page got written to disk even in the event of a system failure.
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Read/Write block sizes
2005-08-23 22:09 Re: Read/Write block sizes Chris Browne <cbbrowne@acm.org>
2005-08-24 01:25 ` Re: Read/Write block sizes Steve Poe <spoe@sfnet.cc>
2005-08-24 02:31 ` Re: Read/Write block sizes Josh Berkus <josh@agliodbs.com>
2005-08-24 03:07 ` Re: Read/Write block sizes Jeffrey W. Baker <jwbaker@acm.org>
@ 2005-08-24 05:20 ` Guy Thornley <guy@esphion.com>
2005-08-24 05:25 ` Re: Read/Write block sizes Jeffrey W. Baker <jwbaker@acm.org>
1 sibling, 1 reply; 24+ messages in thread
From: Guy Thornley @ 2005-08-24 05:20 UTC (permalink / raw)
To: Jeffrey W. Baker <jwbaker@acm.org>; +Cc: Josh Berkus <josh@agliodbs.com>; pgsql-performance@postgresql.org, Steve Poe <spoe@sfnet.cc>; Chris Browne <cbbrowne@acm.org>
> Unfortunately I'm really afraid that this conversation is about trees
> when the forest is the problem. PostgreSQL doesn't even have an async
> reader, which is the sort of thing that could double or triple its
> performance. You're talking about block sizes and such, but the kinds
> of improvements you can get there are in the tens of percents at most.
Not 100% sure, but I'm fairly cirtain we were seeing significant performance
degradation by too much _scheduled_ I/O activity
ie: too much work being submitted to the kernel, due to excessive
parallelism already!!
The classic example of this is a seqscan being interleved by a index scan,
and the disks end up doing nothing but seek activity
Out of all the stuff talked about on this thread so far, only tweaking the
block size (and the madvise() stuff) makes any real-world sense, as its the
only thing talked about that increases the _work_per_seek_.
As for the async IO, sure you might think 'oh async IO would be so cool!!'
and I did, once, too. But then I sat down and _thought_ about it, and
decided well, no, actually, theres _very_ few areas it could actually help,
and in most cases it just make it easier to drive your box into lseek()
induced IO collapse.
Dont forget that already in postgres, you have a process per connection, and
all the processes take care of their own I/O.
Somebody mentioned having threaded backends too, but the only benefit would
be reduced memory footprint (a backend consumes 1-2MB of RAM, which is
almost enough to be a concern for largish systems with a lot of backends)
but personally I _know_ the complixities introduced through threading are
usually not worth it.
IMVVHO (naive experience) what is needed is a complete architecture change
(probably infeasible and only useful as a thought experiment), where:
* a network I/O process deals with client connections
* a limited pool of worker processes deal with statements (perhaps related
to number of spindles somehow)
so when a client issues a statement, the net-IO process simply forwards the
connection state to a worker process and says 'deal with this'.
(Clearly the state object needs to contain all user and transaction state
the connection is involved in).
- Guy Thornley
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Read/Write block sizes
2005-08-23 22:09 Re: Read/Write block sizes Chris Browne <cbbrowne@acm.org>
2005-08-24 01:25 ` Re: Read/Write block sizes Steve Poe <spoe@sfnet.cc>
2005-08-24 02:31 ` Re: Read/Write block sizes Josh Berkus <josh@agliodbs.com>
2005-08-24 03:07 ` Re: Read/Write block sizes Jeffrey W. Baker <jwbaker@acm.org>
2005-08-24 05:20 ` Re: Read/Write block sizes Guy Thornley <guy@esphion.com>
@ 2005-08-24 05:25 ` Jeffrey W. Baker <jwbaker@acm.org>
2005-08-24 05:56 ` Re: Read/Write block sizes Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 24+ messages in thread
From: Jeffrey W. Baker @ 2005-08-24 05:25 UTC (permalink / raw)
To: Guy Thornley <guy@esphion.com>; +Cc: Josh Berkus <josh@agliodbs.com>; pgsql-performance@postgresql.org, Steve Poe <spoe@sfnet.cc>; Chris Browne <cbbrowne@acm.org>
On Wed, 2005-08-24 at 17:20 +1200, Guy Thornley wrote:
> As for the async IO, sure you might think 'oh async IO would be so cool!!'
> and I did, once, too. But then I sat down and _thought_ about it, and
> decided well, no, actually, theres _very_ few areas it could actually help,
> and in most cases it just make it easier to drive your box into lseek()
> induced IO collapse.
>
> Dont forget that already in postgres, you have a process per connection, and
> all the processes take care of their own I/O.
That's the problem. Instead you want 1 or 4 or 10 i/o slaves
coordinating the I/O of all the backends optimally. For instance, with
synchronous scanning.
-jwb
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Read/Write block sizes
2005-08-23 22:09 Re: Read/Write block sizes Chris Browne <cbbrowne@acm.org>
2005-08-24 01:25 ` Re: Read/Write block sizes Steve Poe <spoe@sfnet.cc>
2005-08-24 02:31 ` Re: Read/Write block sizes Josh Berkus <josh@agliodbs.com>
2005-08-24 03:07 ` Re: Read/Write block sizes Jeffrey W. Baker <jwbaker@acm.org>
2005-08-24 05:20 ` Re: Read/Write block sizes Guy Thornley <guy@esphion.com>
2005-08-24 05:25 ` Re: Read/Write block sizes Jeffrey W. Baker <jwbaker@acm.org>
@ 2005-08-24 05:56 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-08-24 06:22 ` Re: Read/Write block sizes Jeffrey W. Baker <jwbaker@acm.org>
2005-08-24 10:35 ` Re: Read/Write block sizes PFC <lists@boutiquenumerique.com>
0 siblings, 2 replies; 24+ messages in thread
From: Tom Lane @ 2005-08-24 05:56 UTC (permalink / raw)
To: Jeffrey W. Baker <jwbaker@acm.org>; +Cc: Guy Thornley <guy@esphion.com>; Josh Berkus <josh@agliodbs.com>; pgsql-performance@postgresql.org, Steve Poe <spoe@sfnet.cc>; Chris Browne <cbbrowne@acm.org>
"Jeffrey W. Baker" <jwbaker@acm.org> writes:
> On Wed, 2005-08-24 at 17:20 +1200, Guy Thornley wrote:
>> Dont forget that already in postgres, you have a process per connection, and
>> all the processes take care of their own I/O.
> That's the problem. Instead you want 1 or 4 or 10 i/o slaves
> coordinating the I/O of all the backends optimally. For instance, with
> synchronous scanning.
And why exactly are we going to do a better job of I/O scheduling than
the OS itself can do?
There's a fairly basic disconnect in viewpoint involved here. The
old-school viewpoint (as embodied in Oracle and a few other DBMSes)
is that the OS is too stupid to be worth anything, and the DB should
bypass the OS to the greatest extent possible, doing its own caching,
disk space layout, I/O scheduling, yadda yadda. That might have been
defensible twenty-odd years ago when Oracle was designed. Postgres
prefers to lay off to the OS anything that the OS can do well --- and
that definitely includes caching and I/O scheduling. There are a whole
lot of smart people working on those problems at the OS level. Maybe we
could make marginal improvements on their results after spending a lot
of effort reinventing the wheel ... but our time will be repaid much
more if we work at levels that the OS cannot have knowledge of, such as
join planning and data statistics.
There are some things we could do to reduce the impedance between us and
the OS --- for instance, the upthread criticism that a seqscan asks the
OS for only 8K at a time is fair enough. But that doesn't translate
to a conclusion that we should schedule the I/O instead of the OS.
regards, tom lane
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Read/Write block sizes
2005-08-23 22:09 Re: Read/Write block sizes Chris Browne <cbbrowne@acm.org>
2005-08-24 01:25 ` Re: Read/Write block sizes Steve Poe <spoe@sfnet.cc>
2005-08-24 02:31 ` Re: Read/Write block sizes Josh Berkus <josh@agliodbs.com>
2005-08-24 03:07 ` Re: Read/Write block sizes Jeffrey W. Baker <jwbaker@acm.org>
2005-08-24 05:20 ` Re: Read/Write block sizes Guy Thornley <guy@esphion.com>
2005-08-24 05:25 ` Re: Read/Write block sizes Jeffrey W. Baker <jwbaker@acm.org>
2005-08-24 05:56 ` Re: Read/Write block sizes Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-08-24 06:22 ` Jeffrey W. Baker <jwbaker@acm.org>
2005-08-25 19:45 ` Re: Read/Write block sizes Josh Berkus <josh@agliodbs.com>
1 sibling, 1 reply; 24+ messages in thread
From: Jeffrey W. Baker @ 2005-08-24 06:22 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Guy Thornley <guy@esphion.com>; Josh Berkus <josh@agliodbs.com>; pgsql-performance@postgresql.org, Steve Poe <spoe@sfnet.cc>; Chris Browne <cbbrowne@acm.org>
On Wed, 2005-08-24 at 01:56 -0400, Tom Lane wrote:
> "Jeffrey W. Baker" <jwbaker@acm.org> writes:
> > On Wed, 2005-08-24 at 17:20 +1200, Guy Thornley wrote:
> >> Dont forget that already in postgres, you have a process per connection, and
> >> all the processes take care of their own I/O.
>
> > That's the problem. Instead you want 1 or 4 or 10 i/o slaves
> > coordinating the I/O of all the backends optimally. For instance, with
> > synchronous scanning.
>
> And why exactly are we going to do a better job of I/O scheduling than
> the OS itself can do?
...
> There are some things we could do to reduce the impedance between us and
> the OS --- for instance, the upthread criticism that a seqscan asks the
> OS for only 8K at a time is fair enough. But that doesn't translate
> to a conclusion that we should schedule the I/O instead of the OS.
Synchronous scanning is a fairly huge and obvious win. If you have two
processes 180 degrees out-of-phase in a linear read, neither process is
going to get anywhere near the throughput they would get from a single
scan.
I think you're being deliberately obtuse with regards to file I/O and
the operating system. The OS isn't magical. It has to strike a balance
between a reasonable read latency and a reasonable throughput. As far
as the kernel is concerned, a busy postgresql server is
indistinguishable from 100 unrelated activities. All backends will be
served equally, even if in this case "equally" means "quite badly all
around."
An I/O slave process could be a big win in Postgres for many kinds of
reads. Instead of opening and reading files the backends would connect
to the I/O slave and request the file be read. If a scan of that file
were already underway, the new backends would be attached. Otherwise a
new scan would commence. In either case, the slave process can issue
(sometimes non-dependant) reads well ahead of the needs of the backend.
You may think the OS can do this for you but it can't. On postgres
knows that it needs the whole file from beginning to end. The OS can
only guess.
Ask me sometime about my replacement for GNU sort. It uses the same
sorting algorithm, but it's an order of magnitude faster due to better
I/O strategy. Someday, in my infinite spare time, I hope to demonstrate
that kind of improvement with a patch to pg.
-jwb
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Read/Write block sizes
2005-08-23 22:09 Re: Read/Write block sizes Chris Browne <cbbrowne@acm.org>
2005-08-24 01:25 ` Re: Read/Write block sizes Steve Poe <spoe@sfnet.cc>
2005-08-24 02:31 ` Re: Read/Write block sizes Josh Berkus <josh@agliodbs.com>
2005-08-24 03:07 ` Re: Read/Write block sizes Jeffrey W. Baker <jwbaker@acm.org>
2005-08-24 05:20 ` Re: Read/Write block sizes Guy Thornley <guy@esphion.com>
2005-08-24 05:25 ` Re: Read/Write block sizes Jeffrey W. Baker <jwbaker@acm.org>
2005-08-24 05:56 ` Re: Read/Write block sizes Tom Lane <tgl@sss.pgh.pa.us>
2005-08-24 06:22 ` Re: Read/Write block sizes Jeffrey W. Baker <jwbaker@acm.org>
@ 2005-08-25 19:45 ` Josh Berkus <josh@agliodbs.com>
2005-08-25 20:26 ` Re: Read/Write block sizes Ron <rjpeace@earthlink.net>
0 siblings, 1 reply; 24+ messages in thread
From: Josh Berkus @ 2005-08-25 19:45 UTC (permalink / raw)
To: pgsql-performance; +Cc: Jeffrey W. Baker <jwbaker@acm.org>; Tom Lane <tgl@sss.pgh.pa.us>; Guy Thornley <guy@esphion.com>; Steve Poe <spoe@sfnet.cc>; Chris Browne <cbbrowne@acm.org>
Jeff,
> Ask me sometime about my replacement for GNU sort. Â It uses the same
> sorting algorithm, but it's an order of magnitude faster due to better
> I/O strategy. Â Someday, in my infinite spare time, I hope to demonstrate
> that kind of improvement with a patch to pg.
Since we desperately need some improvements in sort performance, I do hope
you follow up on this.
--
--Josh
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Read/Write block sizes
2005-08-23 22:09 Re: Read/Write block sizes Chris Browne <cbbrowne@acm.org>
2005-08-24 01:25 ` Re: Read/Write block sizes Steve Poe <spoe@sfnet.cc>
2005-08-24 02:31 ` Re: Read/Write block sizes Josh Berkus <josh@agliodbs.com>
2005-08-24 03:07 ` Re: Read/Write block sizes Jeffrey W. Baker <jwbaker@acm.org>
2005-08-24 05:20 ` Re: Read/Write block sizes Guy Thornley <guy@esphion.com>
2005-08-24 05:25 ` Re: Read/Write block sizes Jeffrey W. Baker <jwbaker@acm.org>
2005-08-24 05:56 ` Re: Read/Write block sizes Tom Lane <tgl@sss.pgh.pa.us>
2005-08-24 06:22 ` Re: Read/Write block sizes Jeffrey W. Baker <jwbaker@acm.org>
2005-08-25 19:45 ` Re: Read/Write block sizes Josh Berkus <josh@agliodbs.com>
@ 2005-08-25 20:26 ` Ron <rjpeace@earthlink.net>
2005-08-25 20:49 ` Re: Read/Write block sizes Chris Browne <cbbrowne@acm.org>
0 siblings, 1 reply; 24+ messages in thread
From: Ron @ 2005-08-25 20:26 UTC (permalink / raw)
To: josh@agliodbs.com; pgsql-performance
At 03:45 PM 8/25/2005, Josh Berkus wrote:
>Jeff,
>
> > Ask me sometime about my replacement for GNU sort. Â It uses the same
> > sorting algorithm, but it's an order of magnitude faster due to better
> > I/O strategy. Â Someday, in my infinite spare time, I hope to demonstrate
> > that kind of improvement with a patch to pg.
>
>Since we desperately need some improvements in sort performance, I do hope
>you follow up on this.
>
>--
>--Josh
I'll generalize that. IMO we desperately need
any and all improvements in IO performance. Even
more so than we need improvements in sorting or sorting IO performance.
Ron
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Read/Write block sizes
2005-08-23 22:09 Re: Read/Write block sizes Chris Browne <cbbrowne@acm.org>
2005-08-24 01:25 ` Re: Read/Write block sizes Steve Poe <spoe@sfnet.cc>
2005-08-24 02:31 ` Re: Read/Write block sizes Josh Berkus <josh@agliodbs.com>
2005-08-24 03:07 ` Re: Read/Write block sizes Jeffrey W. Baker <jwbaker@acm.org>
2005-08-24 05:20 ` Re: Read/Write block sizes Guy Thornley <guy@esphion.com>
2005-08-24 05:25 ` Re: Read/Write block sizes Jeffrey W. Baker <jwbaker@acm.org>
2005-08-24 05:56 ` Re: Read/Write block sizes Tom Lane <tgl@sss.pgh.pa.us>
2005-08-24 06:22 ` Re: Read/Write block sizes Jeffrey W. Baker <jwbaker@acm.org>
2005-08-25 19:45 ` Re: Read/Write block sizes Josh Berkus <josh@agliodbs.com>
2005-08-25 20:26 ` Re: Read/Write block sizes Ron <rjpeace@earthlink.net>
@ 2005-08-25 20:49 ` Chris Browne <cbbrowne@acm.org>
2005-08-25 23:46 ` Re: Read/Write block sizes Ron <rjpeace@earthlink.net>
0 siblings, 1 reply; 24+ messages in thread
From: Chris Browne @ 2005-08-25 20:49 UTC (permalink / raw)
To: pgsql-performance
rjpeace@earthlink.net (Ron) writes:
> At 03:45 PM 8/25/2005, Josh Berkus wrote:
>> > Ask me sometime about my replacement for GNU sort. Â It uses the
>> > same sorting algorithm, but it's an order of magnitude faster due
>> > to better I/O strategy. Â Someday, in my infinite spare time, I
>> > hope to demonstrate that kind of improvement with a patch to pg.
>>
>>Since we desperately need some improvements in sort performance, I
>>do hope you follow up on this.
>
> I'll generalize that. IMO we desperately need any and all
> improvements in IO performance. Even more so than we need
> improvements in sorting or sorting IO performance.
That's frankly a step backwards.
Feel free to "specialise" that instead.
A patch that improves some specific aspect of performance is a
thousand times better than any sort of "desperate desire for any and
all improvements in I/O performance."
The latter is unlikely to provide any usable result.
The "specialized patch" is also pointedly better in that a
*confidently submitted* patch is likely to be way better than any sort
of "desperate clutching at whatever may come to hand."
Far too often, I see people trying to address performance problems via
the "desperate clutching at whatever seems near to hand," and that
generally turns out very badly as a particular result of the whole
"desperate clutching" part.
If you can get a sort improvement submitted, that's a concrete
improvement...
--
select 'cbbrowne' || '@' || 'ntlug.org';
http://www3.sympatico.ca/cbbrowne/lisp.html
Appendium to the Rules of the Evil Overlord #1: "I will not build
excessively integrated security-and-HVAC systems. They may be Really
Cool, but are far too vulnerable to breakdowns."
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Read/Write block sizes
2005-08-23 22:09 Re: Read/Write block sizes Chris Browne <cbbrowne@acm.org>
2005-08-24 01:25 ` Re: Read/Write block sizes Steve Poe <spoe@sfnet.cc>
2005-08-24 02:31 ` Re: Read/Write block sizes Josh Berkus <josh@agliodbs.com>
2005-08-24 03:07 ` Re: Read/Write block sizes Jeffrey W. Baker <jwbaker@acm.org>
2005-08-24 05:20 ` Re: Read/Write block sizes Guy Thornley <guy@esphion.com>
2005-08-24 05:25 ` Re: Read/Write block sizes Jeffrey W. Baker <jwbaker@acm.org>
2005-08-24 05:56 ` Re: Read/Write block sizes Tom Lane <tgl@sss.pgh.pa.us>
2005-08-24 06:22 ` Re: Read/Write block sizes Jeffrey W. Baker <jwbaker@acm.org>
2005-08-25 19:45 ` Re: Read/Write block sizes Josh Berkus <josh@agliodbs.com>
2005-08-25 20:26 ` Re: Read/Write block sizes Ron <rjpeace@earthlink.net>
2005-08-25 20:49 ` Re: Read/Write block sizes Chris Browne <cbbrowne@acm.org>
@ 2005-08-25 23:46 ` Ron <rjpeace@earthlink.net>
0 siblings, 0 replies; 24+ messages in thread
From: Ron @ 2005-08-25 23:46 UTC (permalink / raw)
To: Chris Browne <cbbrowne@acm.org>; pgsql-performance
At 04:49 PM 8/25/2005, Chris Browne wrote:
>rjpeace@earthlink.net (Ron) writes:
> > At 03:45 PM 8/25/2005, Josh Berkus wrote:
> >> > Ask me sometime about my replacement for GNU sort. Â It uses the
> >> > same sorting algorithm, but it's an order of magnitude faster due
> >> > to better I/O strategy. Â Someday, in my infinite spare time, I
> >> > hope to demonstrate that kind of improvement with a patch to pg.
> >>
> >>Since we desperately need some improvements in sort performance, I
> >>do hope you follow up on this.
> >
> > I'll generalize that. IMO we desperately need any and all
> > improvements in IO performance. Even more so than we need
> > improvements in sorting or sorting IO performance.
>
>That's frankly a step backwards. Feel free to "specialise" that instead.
We can agree to disagree, I'm cool with that.
I'm well aware that a Systems Approach to SW
Architecture is not always popular in the Open
Source world. Nonetheless, my POV is that if we
want to be taken seriously and beat "the big
boys", we have to do everything smarter and
faster, as well as cheaper, than they do. You
are not likely to be able to do that consistently
without using some of the "icky" stuff one is
required to study as part of formal training in
the Comp Sci and SW Engineering fields.
>A patch that improves some specific aspect of
>performance is a thousand times better than any
>sort of "desperate desire for any and
>all improvements in I/O performance."
minor twisting of my words: substituting "desire"
for "need". The need is provable. Just put "the
big 5" (SQL Server, Oracle, DB2, mySQL, and
PostgreSQL) into some realistic benches to see that.
Major twisting of my words: the apparent
implication by you that I don't appreciate
improvements in the IO behavior of specific
things like sorting as much as I'd appreciate
more "general" IO performance
improvements. Performance optimization is best
done as an iterative improvement process that
starts with measuring where the need is greatest,
then improving that greatest need by the most you
can, then repeating the whole cycle. _Every_
improvement in such a process is a specific
improvement, even if the improvement is a
decision to re-architect the entire product to
solve the current biggest issue. Improving
sorting IO is cool. OTOH, if pg's biggest IO
problems are elsewhere, then the amount of
overall benefit we will get from improving
sorting IO is going to be minimized until we
improve the bigger problem(s). Amdahl's Law.
>The "specialized patch" is also pointedly better
>in that a *confidently submitted* patch is
>likely to be way better than any sort of
>"desperate clutching at whatever may come to hand."
Another distortion of my statement and POV. I
never suggested nor implied any sort of
"desperate clutching...". We have _measurable_
IO issues that need to be addressed in order for
pg to be a better competitor in the
marketplace. Just as we do with sorting performance.
>Far too often, I see people trying to address
>performance problems via the "desperate
>clutching at whatever seems near to hand," and that
>generally turns out very badly as a particular
>result of the whole "desperate clutching" part.
>
>If you can get a sort improvement submitted, that's a concrete improvement...
As I said, I'm all in favor of concrete,
measurable improvement. I do not think I ever
stated I was in favor of anything else.
You evidently are mildly ranting because you've
seen some examples of poor SW Engineering
Discipline/Practice by people with perhaps
inadequate skills for the issues they were trying
to address. We all have. "90% of everything is
Jreck (eg of too low a quality)."
OTOH, I do not think I've given you any reason to
think I lack such Clue, nor do I think my post was advocating such thrashing.
My post was intended to say that we need an
Overall Systems Approach to pg optimization
rather than just applying what compiler writer's
call "peephole optimizations" to pg. No more, no less.
I apologize if I somehow misled you,
Ron Peacetree
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Read/Write block sizes
2005-08-23 22:09 Re: Read/Write block sizes Chris Browne <cbbrowne@acm.org>
2005-08-24 01:25 ` Re: Read/Write block sizes Steve Poe <spoe@sfnet.cc>
2005-08-24 02:31 ` Re: Read/Write block sizes Josh Berkus <josh@agliodbs.com>
2005-08-24 03:07 ` Re: Read/Write block sizes Jeffrey W. Baker <jwbaker@acm.org>
2005-08-24 05:20 ` Re: Read/Write block sizes Guy Thornley <guy@esphion.com>
2005-08-24 05:25 ` Re: Read/Write block sizes Jeffrey W. Baker <jwbaker@acm.org>
2005-08-24 05:56 ` Re: Read/Write block sizes Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-08-24 10:35 ` PFC <lists@boutiquenumerique.com>
1 sibling, 0 replies; 24+ messages in thread
From: PFC @ 2005-08-24 10:35 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Jeffrey W. Baker <jwbaker@acm.org>; +Cc: Guy Thornley <guy@esphion.com>; Josh Berkus <josh@agliodbs.com>; pgsql-performance@postgresql.org, "Steve Poe" <spoe@sfnet.cc>; Chris Browne <cbbrowne@acm.org>
> of effort reinventing the wheel ... but our time will be repaid much
> more if we work at levels that the OS cannot have knowledge of, such as
> join planning and data statistics.
Considering a global budget of man-hours which is the best ?
1- Spend it on reimplementing half of VFS in postgres, half of Windows in
postgres, half of FreeBSD in postgres, half of Solaris in Postgres, only
to discover you gain a meagre speed increase and a million and a half bugs,
2- Spending 5% of that time lowering the impedance between the OS and
Postgres, and another 5% annoying Kernel people and helping them tweaking
stuff for database use, and the rest on useful features that give useful
speedups, like bitmap indexes, skip scans, and other features that enhance
power and usability ?
If you're Oracle and have almost unlimited resources, maybe. But even
Microsoft opted for option 2 : they implemented ReadFileGather and
WriteFileScatter to lower the syscall overhead and that's it.
And point 2 will benefit to many other apps, wether 1 would benefit only
postgres, and then only in certain cases.
I do believe there is something ineresting to uncover with reiser4 though
(it definitely fits point 2).
I'm happy that the pg team chose point 2 and that new versions keep coming
with new features at an unbelievable rate these times. Do you guys sleep ?
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Read/Write block sizes
2005-08-23 22:09 Re: Read/Write block sizes Chris Browne <cbbrowne@acm.org>
2005-08-24 01:25 ` Re: Read/Write block sizes Steve Poe <spoe@sfnet.cc>
2005-08-24 02:31 ` Re: Read/Write block sizes Josh Berkus <josh@agliodbs.com>
@ 2005-08-24 04:00 ` Alan Stange <stange@rentec.com>
1 sibling, 0 replies; 24+ messages in thread
From: Alan Stange @ 2005-08-24 04:00 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-performance@postgresql.org, Steve Poe <spoe@sfnet.cc>; Chris Browne <cbbrowne@acm.org>
Josh Berkus wrote:
>Steve,
>
>
>
>>I would assume that dbt2 with STP helps minimize the amount of hours
>>someone has to invest to determine performance gains with configurable
>>options?
>>
>>
>
>Actually, these I/O operation issues show up mainly with DW workloads, so the
>STP isn't much use there. If I can ever get some of these machines back
>from the build people, I'd like to start testing some stuff.
>
>One issue with testing this is that currently PostgreSQL doesn't support block
>sizes above 128K. We've already done testing on that (well, Mark has) and
>the performance gains aren't even worth the hassle of remembering you're on a
>different block size (like, +4%).
>
>
What size database was this on?
>What the Sun people have done with other DB systems is show that substantial
>performance gains are possible on large databases (>100G) using block sizes
>of 1MB. I believe that's possible (and that it probably makes more of a
>difference on Solaris than on BSD) but we can't test it without some hackery
>first.
>
We're running on a 100+GB database, with long streams of 8KB reads with
the occasional _llseek(). I've been thinking about running with a
larger blocksize with the expectation that we'd see fewer system calls
and a bit more throughput.
read() calls are a very expensive way to get 8KB of memory (that we know
is already resident) during scans. One has to trap into the kernel, do
the usual process state accounting, find the block, copy the memory to
userspace, return back from the kernel to user space reversing all the
process accounting, pick out the bytes one needs, and repeat all over
again. That's quite a few sacrificial cache lines for 8KB. Yeah,
sure, Linux syscalls are fast, but they aren't that fast, and other
operating systems (windows and solaris) have a bit more overhead on
syscalls.
Regarding large blocks sizes on Solaris: the Solaris folks can also use
large memory pages and avoid a lot of the TLB overhead from the VM
system. The various trapstat and cpustat commands can be quite
interesting to look at when running any large application on a Solaris
system.
It should be noted that having a large shared memory segment can be a
performance looser just from the standpoint of TLB thrashing. O(GB)
memory access patterns can take a huge performance hit in user space
with 4K pages compared to the kernel which would be mapping the "segmap"
(in Solaris parlance) with 4MB pages.
Anyway, I guess my point is that the balance between kernel managed vs.
postgresql managed buffer isn't obvious at all.
-- Alan
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Read/Write block sizes
2005-08-23 22:09 Re: Read/Write block sizes Chris Browne <cbbrowne@acm.org>
2005-08-24 01:25 ` Re: Read/Write block sizes Steve Poe <spoe@sfnet.cc>
@ 2005-08-24 16:12 ` Chris Browne <cbbrowne@acm.org>
2005-08-24 20:57 ` Re: Read/Write block sizes Jim C. Nasby <jnasby@pervasive.com>
1 sibling, 1 reply; 24+ messages in thread
From: Chris Browne @ 2005-08-24 16:12 UTC (permalink / raw)
To: pgsql-performance
spoe@sfnet.cc (Steve Poe) writes:
> Chris,
>
> Unless I am wrong, you're making the assumpting the amount of time spent
> and ROI is known. Maybe those who've been down this path know how to get
> that additional 2-4% in 30 minutes or less?
>
> While each person and business' performance gains (or not) could vary,
> someone spending the 50-100h to gain 2-4% over a course of a month for a
> 24x7 operation would seem worth the investment?
What we *do* know is that adding these "knobs" would involve a
significant amount of effort, as the values are widely used throughout
the database engine. Making them dynamic (e.g. - so they could be
tuned on a tablespace-by-tablespace basis) would undoubtedly require
rather a lot of development effort. They are definitely NOT 30 minute
changes.
Moreover, knowing how to adjust them is almost certainly also NOT a 30
minute configuration change; significant benchmarking effort for the
individual application is almost sure to be needed.
It's not much different from the reason why PostgreSQL doesn't use
threading...
The problem with using threading is that introducing it to the code
base would require a pretty enormous amount of effort (I'll bet
multiple person-years), and it wouldn't provide *any* benefit until
you get rather a long ways down the road.
Everyone involved in development seems to me to have a reasonably keen
understanding as to what the potential benefits of threading are; the
value is that there fall out plenty of opportunities to parallelize
the evaluation of portions of queries. Alas, it wouldn't be until
*after* all the effort goes in that we would get any idea as to what
kinds of speedups this would provide.
In effect, there has to be a year invested in *breaking* PostgreSQL
(because this would initially break a lot, since thread programming is
a really tough skill) where you don't actually see any benefits.
> I would assume that dbt2 with STP helps minimize the amount of hours
> someone has to invest to determine performance gains with
> configurable options?
That's going to help in constructing a "default" knob value. And if
we find an "optimal default," that encourages sticking with the
current approach, of using #define to apply that value...
>> If someone spends 100h working on one of these items, and gets a 2%
>> performance improvement, that's almost certain to be less desirable
>> than spending 50h on something else that gets a 4% improvement.
>>
>> And we might discover that memory management improvements in Linux
>> 2.6.16 or FreeBSD 5.5 allow some OS kernels to provide some such
>> improvements "for free" behind our backs without *any* need to write
>> database code. :-)
--
let name="cbbrowne" and tld="ntlug.org" in String.concat "@" [name;tld];;
http://www.ntlug.org/~cbbrowne/lisp.html
"For those of you who are into writing programs that are as obscure
and complicated as possible, there are opportunities for... real fun
here" -- Arthur Norman
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Read/Write block sizes
2005-08-23 22:09 Re: Read/Write block sizes Chris Browne <cbbrowne@acm.org>
2005-08-24 01:25 ` Re: Read/Write block sizes Steve Poe <spoe@sfnet.cc>
2005-08-24 16:12 ` Re: Read/Write block sizes Chris Browne <cbbrowne@acm.org>
@ 2005-08-24 20:57 ` Jim C. Nasby <jnasby@pervasive.com>
0 siblings, 0 replies; 24+ messages in thread
From: Jim C. Nasby @ 2005-08-24 20:57 UTC (permalink / raw)
To: Chris Browne <cbbrowne@acm.org>; +Cc: pgsql-performance
On Wed, Aug 24, 2005 at 12:12:22PM -0400, Chris Browne wrote:
> Everyone involved in development seems to me to have a reasonably keen
> understanding as to what the potential benefits of threading are; the
> value is that there fall out plenty of opportunities to parallelize
> the evaluation of portions of queries. Alas, it wouldn't be until
> *after* all the effort goes in that we would get any idea as to what
> kinds of speedups this would provide.
My understanding is that the original suggestion was to use threads
within individual backends to allow for parallel query execution, not
swiching to a completely thread-based model.
In any case, there are other ways to enable parallelism without using
threads, such as handing actual query execution off to a set of
processes.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com 512-569-9461
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Read/Write block sizes
@ 2005-08-24 20:10 Lance Obermeyer <LObermey@pervasive.com>
0 siblings, 0 replies; 24+ messages in thread
From: Lance Obermeyer @ 2005-08-24 20:10 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; Jignesh K. Shah <J.K.Shah@Sun.COM>; +Cc: Jim Nasby <jnasby@pervasive.com>; Chris Browne <cbbrowne@acm.org>; pgsql-performance
Since Bruce referred to the "corporate software world" I'll chime in...
It has been a while since adding knobs and dials has been considered a good idea. Customers are almost always bad at tuning their systems, which decreases customer satisfaction. While many people assume the corporate types don't care, that is actually far from the truth. Well run commercial software companies regularly commission (expensive) customer satisfaction surveys. These numbers are the second most important numbers in all of the enterprise, trailing only revenue in importance. Results are sliced and diced in every way imaginable.
The commercial world is trying to auto-tune their systems just as much. Examples are the work that many of the big boys are doing towards "autonomic" computing. While it is driven by naked self interest of wanting to sell version upgrades, those efforts increase customer satisfaction and decrease support costs. Works well for everyone...
-----Original Message-----
From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
Sent: Wednesday, August 24, 2005 8:52 AM
To: Jignesh K. Shah
Cc: Jim Nasby; Chris Browne; pgsql-performance@postgresql.org
Subject: Re: Read/Write block sizes
This thread covers several performance ideas. First is the idea that
more parameters should be configurable. While this seems like a noble
goal, we try to make parameters auto-tuning, or if users have to
configure it, the parameter should be useful for a significant number of
users.
In the commercial software world, if you can convince your boss that a
feature/knob is useful, it usually gets into the product.
Unfortunately, this leads to the golden doorknob on a shack, where some
features are out of sync with the rest of the product in terms of
usefulness and utility. With open source, if a feature can not be
auto-tuned, or has significant overhead, the features has to be
implemented and then proven to be a benefit.
In terms of adding async I/O, threading, and other things, it might make
sense to explore how these could be implemented in a way that fits the
above criteria.
---------------------------------------------------------------------------
Jignesh K. Shah wrote:
> Hi Jim,
>
> | How many of these things are currently easy to change with a recompile?
> | I should be able to start testing some of these ideas in the near
> | future, if they only require minor code or configure changes.
>
>
> The following
> * Data File Size 1GB
> * WAL File Size of 16MB
> * Block Size of 8K
>
> Are very easy to change with a recompile.. A Tunable will be greatly
> prefered as it will allow one binary for different tunings
>
> * MultiBlock read/write
>
> Is not available but will greatly help in reducing the number of system
> calls which will only increase as the size of the database increases if
> something is not done about i.
>
> * Pregrown files... maybe not important at this point since TABLESPACE
> can currently work around it a bit (Just need to create a different file
> system for each tablespace
>
> But if you really think hardware & OS is the answer for all small
> things...... I think we should now start to look on how to make Postgres
> Multi-threaded or multi-processed for each connection. With the influx
> of "Dual-Core" or "Multi-Core" being the fad.... Postgres can have the
> cutting edge if somehow exploiting cores is designed.
>
> Somebody mentioned that adding CPU to Postgres workload halved the
> average CPU usage...
> YEAH... PostgreSQL uses only 1 CPU per connection (assuming 100%
> usage) so if you add another CPU it is idle anyway and the system will
> report only 50% :-) BUT the importing to measure is.. whether the query
> time was cut down or not? ( No flames I am sure you were talking about
> multi-connection multi-user environment :-) ) But my point is then this
> approach is worth the ROI and the time and effort spent to solve this
> problem.
>
> I actually vote for a multi-threaded solution for each connection while
> still maintaining seperate process for each connections... This way the
> fundamental architecture of Postgres doesn't change, however a
> multi-threaded connection can then start to exploit different cores..
> (Maybe have tunables for number of threads to read data files who
> knows.. If somebody is interested in actually working a design ..
> contact me and I will be interested in assisting this work.
>
> Regards,
> Jignesh
>
>
> Jim C. Nasby wrote:
>
> >On Tue, Aug 23, 2005 at 06:09:09PM -0400, Chris Browne wrote:
> >
> >
> >>J.K.Shah@Sun.COM (Jignesh Shah) writes:
> >>
> >>
> >>>>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
> >>>
> >>>
> >>Every single one of these still begs the question of whether the
> >>changes will have a *material* impact on performance.
> >>
> >>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Read/Write block sizes
@ 2005-08-24 20:20 Jignesh Shah <J.K.Shah@Sun.COM>
0 siblings, 0 replies; 24+ messages in thread
From: Jignesh Shah @ 2005-08-24 20:20 UTC (permalink / raw)
To: Lance Obermeyer <LObermey@pervasive.com>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; Jim Nasby <jnasby@pervasive.com>; Chris Browne <cbbrowne@acm.org>; pgsql-performance
Agreed!!!
But the knowledge to Auto-tune your application comes from years of understanding of how users are using the so-called "knobs".. But if the "knobs" are not there in the first place.. how do you know what people are using?
The "so-called" big boys are also using their knowledge base of what works for the customer in their autonomic self healers and its based on the experience of all the settings possible and based on service requests on what had failed that they get the knowledge about avoiding what fails and tuning what works.
Remember "recompiling" is a risk with upteem number of variables which not every production release engineer is happy about.
Its easy to change back the knob to the previous value rather than trying to figure out how do I get my old binaries back.
-Jignesh
----- Original Message -----
From: Lance Obermeyer <LObermey@pervasive.com>
Date: Wednesday, August 24, 2005 4:10 pm
Subject: RE: Read/Write block sizes
> Since Bruce referred to the "corporate software world" I'll chime
> in...
> It has been a while since adding knobs and dials has been
> considered a good idea. Customers are almost always bad at tuning
> their systems, which decreases customer satisfaction. While many
> people assume the corporate types don't care, that is actually far
> from the truth. Well run commercial software companies regularly
> commission (expensive) customer satisfaction surveys. These
> numbers are the second most important numbers in all of the
> enterprise, trailing only revenue in importance. Results are
> sliced and diced in every way imaginable.
>
> The commercial world is trying to auto-tune their systems just as
> much. Examples are the work that many of the big boys are doing
> towards "autonomic" computing. While it is driven by naked self
> interest of wanting to sell version upgrades, those efforts
> increase customer satisfaction and decrease support costs. Works
> well for everyone...
>
>
>
> -----Original Message-----
> From: Bruce Momjian [pgman@candle.pha.pa.us]
> Sent: Wednesday, August 24, 2005 8:52 AM
> To: Jignesh K. Shah
> Cc: Jim Nasby; Chris Browne; pgsql-performance@postgresql.org
> Subject: Re: Read/Write block sizes
>
>
>
> This thread covers several performance ideas. First is the idea that
> more parameters should be configurable. While this seems like a
> noblegoal, we try to make parameters auto-tuning, or if users have to
> configure it, the parameter should be useful for a significant
> number of
> users.
>
> In the commercial software world, if you can convince your boss
> that a
> feature/knob is useful, it usually gets into the product.
> Unfortunately, this leads to the golden doorknob on a shack, where
> somefeatures are out of sync with the rest of the product in terms of
> usefulness and utility. With open source, if a feature can not be
> auto-tuned, or has significant overhead, the features has to be
> implemented and then proven to be a benefit.
>
> In terms of adding async I/O, threading, and other things, it might
> makesense to explore how these could be implemented in a way that
> fits the
> above criteria.
>
> --------------------------------------------------------------------
> -------
>
> Jignesh K. Shah wrote:
> > Hi Jim,
> >
> > | How many of these things are currently easy to change with a
> recompile?> | I should be able to start testing some of these ideas
> in the near
> > | future, if they only require minor code or configure changes.
> >
> >
> > The following
> > * Data File Size 1GB
> > * WAL File Size of 16MB
> > * Block Size of 8K
> >
> > Are very easy to change with a recompile.. A Tunable will be
> greatly
> > prefered as it will allow one binary for different tunings
> >
> > * MultiBlock read/write
> >
> > Is not available but will greatly help in reducing the number of
> system
> > calls which will only increase as the size of the database
> increases if
> > something is not done about i.
> >
> > * Pregrown files... maybe not important at this point since
> TABLESPACE
> > can currently work around it a bit (Just need to create a
> different file
> > system for each tablespace
> >
> > But if you really think hardware & OS is the answer for all
> small
> > things...... I think we should now start to look on how to make
> Postgres
> > Multi-threaded or multi-processed for each connection. With the
> influx
> > of "Dual-Core" or "Multi-Core" being the fad.... Postgres can
> have the
> > cutting edge if somehow exploiting cores is designed.
> >
> > Somebody mentioned that adding CPU to Postgres workload halved
> the
> > average CPU usage...
> > YEAH... PostgreSQL uses only 1 CPU per connection (assuming 100%
> > usage) so if you add another CPU it is idle anyway and the
> system will
> > report only 50% :-) BUT the importing to measure is.. whether
> the query
> > time was cut down or not? ( No flames I am sure you were talking
> about
> > multi-connection multi-user environment :-) ) But my point is
> then this
> > approach is worth the ROI and the time and effort spent to solve
> this
> > problem.
> >
> > I actually vote for a multi-threaded solution for each connection
> while
> > still maintaining seperate process for each connections... This
> way the
> > fundamental architecture of Postgres doesn't change, however a
> > multi-threaded connection can then start to exploit different
> cores..
> > (Maybe have tunables for number of threads to read data files who
> > knows.. If somebody is interested in actually working a design ..
> > contact me and I will be interested in assisting this work.
> >
> > Regards,
> > Jignesh
> >
> >
> > Jim C. Nasby wrote:
> >
> > >On Tue, Aug 23, 2005 at 06:09:09PM -0400, Chris Browne wrote:
> > >
> > >
> > >>J.K.Shah@Sun.COM (Jignesh Shah) writes:
> > >>
> > >>
> > >>>>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
> > >>>
> > >>>
> > >>Every single one of these still begs the question of whether the
> > >>changes will have a *material* impact on performance.
> > >>
> > >>
> >
> >
> > ---------------------------(end of broadcast)---------------------
> ------
> > TIP 3: Have you checked our extensive FAQ?
> >
> > http://www.postgresql.org/docs/faq
> >
>
> --
> Bruce Momjian | http://candle.pha.pa.us
> pgman@candle.pha.pa.us | (610) 359-1001
> + If your life is a hard drive, | 13 Roberts Road
> + Christ can be your backup. | Newtown Square,
> Pennsylvania 19073
>
^ permalink raw reply [nested|flat] 24+ messages in thread
end of thread, other threads:[~2005-08-25 23:46 UTC | newest]
Thread overview: 24+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-08-23 22:09 Re: Read/Write block sizes Chris Browne <cbbrowne@acm.org>
2005-08-23 23:24 ` Michael Stone <mstone+postgres@mathom.us>
2005-08-23 23:36 ` Jim C. Nasby <jnasby@pervasive.com>
2005-08-24 02:22 ` Jignesh K. Shah <J.K.Shah@Sun.COM>
2005-08-24 13:52 ` Bruce Momjian <pgman@candle.pha.pa.us>
2005-08-24 01:25 ` Steve Poe <spoe@sfnet.cc>
2005-08-24 02:31 ` Josh Berkus <josh@agliodbs.com>
2005-08-24 03:07 ` Jeffrey W. Baker <jwbaker@acm.org>
2005-08-24 05:10 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-08-24 16:26 ` Josh Berkus <josh@agliodbs.com>
2005-08-24 05:20 ` Guy Thornley <guy@esphion.com>
2005-08-24 05:25 ` Jeffrey W. Baker <jwbaker@acm.org>
2005-08-24 05:56 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-08-24 06:22 ` Jeffrey W. Baker <jwbaker@acm.org>
2005-08-25 19:45 ` Josh Berkus <josh@agliodbs.com>
2005-08-25 20:26 ` Ron <rjpeace@earthlink.net>
2005-08-25 20:49 ` Chris Browne <cbbrowne@acm.org>
2005-08-25 23:46 ` Ron <rjpeace@earthlink.net>
2005-08-24 10:35 ` PFC <lists@boutiquenumerique.com>
2005-08-24 04:00 ` Alan Stange <stange@rentec.com>
2005-08-24 16:12 ` Chris Browne <cbbrowne@acm.org>
2005-08-24 20:57 ` Jim C. Nasby <jnasby@pervasive.com>
2005-08-24 20:10 Re: Read/Write block sizes Lance Obermeyer <LObermey@pervasive.com>
2005-08-24 20:20 Re: Read/Write block sizes Jignesh Shah <J.K.Shah@Sun.COM>
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