Re: Read/Write block sizes - Mailing list 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
healersand its based on the experience of all the settings possible and based on service requests on what had failed
thatthey 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
>