Re: Running PostgreSQL as fast as possible no matter the consequences

On Fri, Nov 5, 2010 at 8:12 AM, Jon Nelson <jnelson+pgsql(at)jamponi(dot)net> wrote:
> On Fri, Nov 5, 2010 at 7:08 AM, Guillaume Cottenceau <gc(at)mnc(dot)ch> wrote:
>> Marti Raudsepp <marti 'at' juffo.org> writes:
>>
>>> On Fri, Nov 5, 2010 at 13:32, A B <gentosaker(at)gmail(dot)com> wrote:
>>>> I was just thinking about the case where I will have almost 100%
>>>> selects, but still needs something better than a plain key-value
>>>> storage so I can do some sql queries.
>>>> The server will just boot, load data, run,  hopefully not crash but if
>>>> it would, just start over with load and run.
>>>
>>> If you want fast read queries then changing
>>> fsync/full_page_writes/synchronous_commit won't help you.
>>
>> That illustrates how knowing the reasoning of this particular
>> requests makes new suggestions worthwhile, while previous ones
>> are now seen as useless.
>
> I disagree that they are useless - the stated mechanism was "start,
> load data, and run". Changing the params above won't likely change
> much in the 'run' stage but would they help in the 'load' stage?

Yes, they certainly will. And they might well help in the run stage,
too, if there are temporary tables in use, or checkpoints flushing
hint bit updates, or such things.

It's also important to crank up checkpoint_segments and
checkpoint_timeout very high, especially for the bulk data load but
even afterwards if there is any write activity at all. And it's
important to set shared_buffers correctly, too, which helps on
workloads of all kinds. But as said upthread, turning off fsync,
full_page_writes, and synchronous_commit are the things you can do
that specifically trade reliability away to get speed.

In 9.1, I'm hopeful that we'll have unlogged tables, which will even
better than turning these parameters off, and for which I just posted
a patch to -hackers. Instead of generating WAL and writing WAL to the
OS and then NOT trying to make sure it hits the disk, we just won't
generate it in the first place. But if PostgreSQL or the machine it's
running on crashes, you won't need to completely blow away the cluster
and start over; instead, the particular tables that you chose to
create as unlogged will be truncated, and the rest of your data,
including the system catalogs, will still be intact.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company