pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedRunning PostgreSQL as fast as possible no matter the consequences
31+ messages / 18 participants
[nested] [flat]
* Running PostgreSQL as fast as possible no matter the consequences
@ 2010-11-05 10:59 A B <gentosaker@gmail.com>
0 siblings, 6 replies; 31+ messages in thread
From: A B @ 2010-11-05 10:59 UTC (permalink / raw)
To: pgsql-performance
Hi there.
If you just wanted PostgreSQL to go as fast as possible WITHOUT any
care for your data (you accept 100% dataloss and datacorruption if any
error should occur), what settings should you use then?
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: Running PostgreSQL as fast as possible no matter the consequences
@ 2010-11-05 11:11 Guillaume Cottenceau <gc@mnc.ch>
parent: A B <gentosaker@gmail.com>
5 siblings, 1 reply; 31+ messages in thread
From: Guillaume Cottenceau @ 2010-11-05 11:11 UTC (permalink / raw)
To: A B <gentosaker@gmail.com>; +Cc: pgsql-performance
A B <gentosaker 'at' gmail.com> writes:
> Hi there.
>
> If you just wanted PostgreSQL to go as fast as possible WITHOUT any
> care for your data (you accept 100% dataloss and datacorruption if any
> error should occur), what settings should you use then?
Don't use PostgreSQL, just drop your data, you will end up with
the same results and be even faster than any use of PostgreSQL.
If anyone needs data, then just say you had data corruption, and
that since 100% dataloss is accepted, then all's well.
--
Guillaume Cottenceau
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: Running PostgreSQL as fast as possible no matter the consequences
@ 2010-11-05 11:14 Thom Brown <thom@linux.com>
parent: A B <gentosaker@gmail.com>
5 siblings, 2 replies; 31+ messages in thread
From: Thom Brown @ 2010-11-05 11:14 UTC (permalink / raw)
To: A B <gentosaker@gmail.com>; +Cc: pgsql-performance
On 5 November 2010 10:59, A B <gentosaker@gmail.com> wrote:
> Hi there.
>
> If you just wanted PostgreSQL to go as fast as possible WITHOUT any
> care for your data (you accept 100% dataloss and datacorruption if any
> error should occur), what settings should you use then?
>
>
Turn off fsync and full_page_writes (i.e. running with scissors).
Also depends on what you mean by "as fast as possible". Fast at doing
what? Bulk inserts, selecting from massive tables?
--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: Running PostgreSQL as fast as possible no matter the consequences
@ 2010-11-05 11:15 Thom Brown <thom@linux.com>
parent: Thom Brown <thom@linux.com>
1 sibling, 0 replies; 31+ messages in thread
From: Thom Brown @ 2010-11-05 11:15 UTC (permalink / raw)
To: A B <gentosaker@gmail.com>; +Cc: pgsql-performance
On 5 November 2010 11:14, Thom Brown <thom@linux.com> wrote:
> On 5 November 2010 10:59, A B <gentosaker@gmail.com> wrote:
>
>> Hi there.
>>
>> If you just wanted PostgreSQL to go as fast as possible WITHOUT any
>> care for your data (you accept 100% dataloss and datacorruption if any
>> error should occur), what settings should you use then?
>>
>>
> Turn off fsync and full_page_writes (i.e. running with scissors).
>
> Also depends on what you mean by "as fast as possible". Fast at doing
> what? Bulk inserts, selecting from massive tables?
>
>
Oh, and turn synchronous_commit off too.
--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: Running PostgreSQL as fast as possible no matter the consequences
@ 2010-11-05 11:23 Szymon Guz <mabewlun@gmail.com>
parent: A B <gentosaker@gmail.com>
5 siblings, 1 reply; 31+ messages in thread
From: Szymon Guz @ 2010-11-05 11:23 UTC (permalink / raw)
To: A B <gentosaker@gmail.com>; +Cc: pgsql-performance
On 5 November 2010 11:59, A B <gentosaker@gmail.com> wrote:
> Hi there.
>
> If you just wanted PostgreSQL to go as fast as possible WITHOUT any
> care for your data (you accept 100% dataloss and datacorruption if any
> error should occur), what settings should you use then?
>
>
I'm just curious, what do you need that for?
regards
Szymon
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: Running PostgreSQL as fast as possible no matter the consequences
@ 2010-11-05 11:24 A B <gentosaker@gmail.com>
parent: Thom Brown <thom@linux.com>
1 sibling, 0 replies; 31+ messages in thread
From: A B @ 2010-11-05 11:24 UTC (permalink / raw)
To: Thom Brown <thom@linux.com>; +Cc: pgsql-performance
> Turn off fsync and full_page_writes (i.e. running with scissors).
> Also depends on what you mean by "as fast as possible". Fast at doing
> what? Bulk inserts, selecting from massive tables?
I guess some tuning has to be done to make it work well with the
particular workload (in this case most selects). But thanks for the
suggestions on the more general parameters.
"running with scissors" sounds nice :-)
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: Running PostgreSQL as fast as possible no matter the consequences
@ 2010-11-05 11:27 Craig Ringer <craig@postnewspapers.com.au>
parent: A B <gentosaker@gmail.com>
5 siblings, 1 reply; 31+ messages in thread
From: Craig Ringer @ 2010-11-05 11:27 UTC (permalink / raw)
To: A B <gentosaker@gmail.com>; +Cc: pgsql-performance
On 05/11/10 18:59, A B wrote:
> Hi there.
>
> If you just wanted PostgreSQL to go as fast as possible WITHOUT any
> care for your data (you accept 100% dataloss and datacorruption if any
> error should occur), what settings should you use then?
Others have suggested appropriate parameters ("running with scissors").
I'd like to add something else to the discussion: have you looked at
memcached yet? Or pgpool? If you haven't, start there.
--
Craig Ringer
Tech-related writing: http://soapyfrogs.blogspot.com/
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: Running PostgreSQL as fast as possible no matter the consequences
@ 2010-11-05 11:30 Marti Raudsepp <marti@juffo.org>
parent: Guillaume Cottenceau <gc@mnc.ch>
0 siblings, 1 reply; 31+ messages in thread
From: Marti Raudsepp @ 2010-11-05 11:30 UTC (permalink / raw)
To: Guillaume Cottenceau <gc@mnc.ch>; +Cc: A B <gentosaker@gmail.com>; pgsql-performance
On Fri, Nov 5, 2010 at 13:11, Guillaume Cottenceau <gc@mnc.ch> wrote:
> Don't use PostgreSQL, just drop your data, you will end up with
> the same results and be even faster than any use of PostgreSQL.
> If anyone needs data, then just say you had data corruption, and
> that since 100% dataloss is accepted, then all's well.
You're not helping. There are legitimate reasons for trading off
safety for performance.
Regards,
Marti
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: Running PostgreSQL as fast as possible no matter the consequences
@ 2010-11-05 11:32 A B <gentosaker@gmail.com>
parent: Szymon Guz <mabewlun@gmail.com>
0 siblings, 3 replies; 31+ messages in thread
From: A B @ 2010-11-05 11:32 UTC (permalink / raw)
To: Szymon Guz <mabewlun@gmail.com>; +Cc: pgsql-performance
>> If you just wanted PostgreSQL to go as fast as possible WITHOUT any
>> care for your data (you accept 100% dataloss and datacorruption if any
>> error should occur), what settings should you use then?
>>
>
>
> I'm just curious, what do you need that for?
>
> regards
> Szymon
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.
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: Running PostgreSQL as fast as possible no matter the consequences
@ 2010-11-05 11:36 A B <gentosaker@gmail.com>
parent: Craig Ringer <craig@postnewspapers.com.au>
0 siblings, 0 replies; 31+ messages in thread
From: A B @ 2010-11-05 11:36 UTC (permalink / raw)
To: Craig Ringer <craig@postnewspapers.com.au>; +Cc: pgsql-performance
>> If you just wanted PostgreSQL to go as fast as possible WITHOUT any
>> care for your data (you accept 100% dataloss and datacorruption if any
>> error should occur), what settings should you use then?
>
> Others have suggested appropriate parameters ("running with scissors").
>
> I'd like to add something else to the discussion: have you looked at
> memcached yet? Or pgpool? If you haven't, start there.
>
memcahced has been mentioned in some discussions, but I have not studied it yet.
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: Running PostgreSQL as fast as possible no matter the consequences
@ 2010-11-05 11:36 Marti Raudsepp <marti@juffo.org>
parent: A B <gentosaker@gmail.com>
2 siblings, 2 replies; 31+ messages in thread
From: Marti Raudsepp @ 2010-11-05 11:36 UTC (permalink / raw)
To: A B <gentosaker@gmail.com>; +Cc: Szymon Guz <mabewlun@gmail.com>; pgsql-performance
On Fri, Nov 5, 2010 at 13:32, A B <gentosaker@gmail.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.
Just follow the regular tuning guide. shared_buffers,
effective_cache_size, work_mem, default_statistics_target can make a
difference.
http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
Regards,
Marti
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: Running PostgreSQL as fast as possible no matter the consequences
@ 2010-11-05 11:41 Thom Brown <thom@linux.com>
parent: Marti Raudsepp <marti@juffo.org>
1 sibling, 0 replies; 31+ messages in thread
From: Thom Brown @ 2010-11-05 11:41 UTC (permalink / raw)
To: Marti Raudsepp <marti@juffo.org>; +Cc: A B <gentosaker@gmail.com>; Szymon Guz <mabewlun@gmail.com>; pgsql-performance
On 5 November 2010 11:36, Marti Raudsepp <marti@juffo.org> wrote:
> On Fri, Nov 5, 2010 at 13:32, A B <gentosaker@gmail.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.
Yes, those will be for write-performance only, so useless in this case.
--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: Running PostgreSQL as fast as possible no matter the consequences
@ 2010-11-05 12:06 Guillaume Cottenceau <gc@mnc.ch>
parent: Marti Raudsepp <marti@juffo.org>
0 siblings, 0 replies; 31+ messages in thread
From: Guillaume Cottenceau @ 2010-11-05 12:06 UTC (permalink / raw)
To: Marti Raudsepp <marti@juffo.org>; +Cc: A B <gentosaker@gmail.com>; pgsql-performance
Marti Raudsepp <marti 'at' juffo.org> writes:
> On Fri, Nov 5, 2010 at 13:11, Guillaume Cottenceau <gc@mnc.ch> wrote:
>> Don't use PostgreSQL, just drop your data, you will end up with
>> the same results and be even faster than any use of PostgreSQL.
>> If anyone needs data, then just say you had data corruption, and
>> that since 100% dataloss is accepted, then all's well.
>
> You're not helping. There are legitimate reasons for trading off
> safety for performance.
Cccepting 100% dataloss and datacorruption deserves a little
reasoning, otherwise I'm afraid I'm right in suggesting it makes
little difference to use PG or to drop data altogether.
--
Guillaume Cottenceau
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: Running PostgreSQL as fast as possible no matter the consequences
@ 2010-11-05 12:08 Guillaume Cottenceau <gc@mnc.ch>
parent: Marti Raudsepp <marti@juffo.org>
1 sibling, 1 reply; 31+ messages in thread
From: Guillaume Cottenceau @ 2010-11-05 12:08 UTC (permalink / raw)
To: Marti Raudsepp <marti@juffo.org>; +Cc: A B <gentosaker@gmail.com>; Szymon Guz <mabewlun@gmail.com>; pgsql-performance
Marti Raudsepp <marti 'at' juffo.org> writes:
> On Fri, Nov 5, 2010 at 13:32, A B <gentosaker@gmail.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.
--
Guillaume Cottenceau
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: Running PostgreSQL as fast as possible no matter the consequences
@ 2010-11-05 12:12 Jon Nelson <jnelson+pgsql@jamponi.net>
parent: Guillaume Cottenceau <gc@mnc.ch>
0 siblings, 1 reply; 31+ messages in thread
From: Jon Nelson @ 2010-11-05 12:12 UTC (permalink / raw)
To: ; +Cc: pgsql-performance
On Fri, Nov 5, 2010 at 7:08 AM, Guillaume Cottenceau <gc@mnc.ch> wrote:
> Marti Raudsepp <marti 'at' juffo.org> writes:
>
>> On Fri, Nov 5, 2010 at 13:32, A B <gentosaker@gmail.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?
--
Jon
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: Running PostgreSQL as fast as possible no matter the consequences
@ 2010-11-05 12:26 Lello, Nick <nick.lello@rentrakmail.com>
parent: A B <gentosaker@gmail.com>
2 siblings, 2 replies; 31+ messages in thread
From: Lello, Nick @ 2010-11-05 12:26 UTC (permalink / raw)
To: pgsql-performance
How about either:-
a) Size the pool so all your data fits into it.
b) Use a RAM-based filesystem (ie: a memory disk or SSD) for the
data storage [memory disk will be faster] with a Smaller pool
- Your seed data should be a copy of the datastore on disk filesystem;
at startup time copy the storage files from the physical to memory.
A bigger gain can probably be had if you have a tightly controlled
suite of queries that will be run against the database and you can
spend the time to tune each to ensure it performs no sequential scans
(ie: Every query uses index lookups).
On 5 November 2010 11:32, A B <gentosaker@gmail.com> wrote:
>>> If you just wanted PostgreSQL to go as fast as possible WITHOUT any
>>> care for your data (you accept 100% dataloss and datacorruption if any
>>> error should occur), what settings should you use then?
>>>
>>
>>
>> I'm just curious, what do you need that for?
>>
>> regards
>> Szymon
>
> 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.
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
--
Nick Lello | Web Architect
o +1 503.284.7581 x418 / +44 (0) 8433309374 | m +44 (0) 7917 138319
Email: nick.lello at rentrak.com
RENTRAK | www.rentrak.com | NASDAQ: RENT
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: Running PostgreSQL as fast as possible no matter the consequences
@ 2010-11-05 15:13 Chris Browne <cbbrowne@acm.org>
parent: A B <gentosaker@gmail.com>
5 siblings, 1 reply; 31+ messages in thread
From: Chris Browne @ 2010-11-05 15:13 UTC (permalink / raw)
To: pgsql-performance
gentosaker@gmail.com (A B) writes:
> If you just wanted PostgreSQL to go as fast as possible WITHOUT any
> care for your data (you accept 100% dataloss and datacorruption if any
> error should occur), what settings should you use then?
Use /dev/null. It is web scale, and there are good tutorials.
But seriously, there *are* cases where "blind speed" is of use. When
loading data into a fresh database is a good time for this; if things
fall over, it may be pretty acceptable to start "from scratch" with
mkfs/initdb.
I'd:
- turn off fsync
- turn off synchronous commit
- put as much as possible onto Ramdisk/tmpfs/similar as possible
--
output = reverse("moc.liamg" "@" "enworbbc")
http://linuxfinances.info/info/lsf.html
43% of all statistics are worthless.
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: Running PostgreSQL as fast as possible no matter the consequences
@ 2010-11-05 15:23 Devrim GÜNDÜZ <devrim@gunduz.org>
parent: A B <gentosaker@gmail.com>
5 siblings, 1 reply; 31+ messages in thread
From: Devrim GÜNDÜZ @ 2010-11-05 15:23 UTC (permalink / raw)
To: A B <gentosaker@gmail.com>; +Cc: pgsql-performance
On Fri, 2010-11-05 at 11:59 +0100, A B wrote:
>
> If you just wanted PostgreSQL to go as fast as possible WITHOUT any
> care for your data (you accept 100% dataloss and datacorruption if any
> error should occur), what settings should you use then?
You can initdb to ramdisk, if you have enough RAM. It will fast, really.
--
Devrim GÜNDÜZ
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
PostgreSQL RPM Repository: http://yum.pgrpms.org
Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz
Attachments:
signature.asc (application/pgp-signature, 197B) download
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: Running PostgreSQL as fast as possible no matter the consequences
@ 2010-11-05 16:25 Mladen Gogala <mladen.gogala@vmsinfo.com>
parent: Devrim GÜNDÜZ <devrim@gunduz.org>
0 siblings, 0 replies; 31+ messages in thread
From: Mladen Gogala @ 2010-11-05 16:25 UTC (permalink / raw)
To: Devrim GÜNDÜZ <devrim@gunduz.org>; +Cc: A B <gentosaker@gmail.com>; pgsql-performance
Devrim GÜNDÜZ wrote:
> On Fri, 2010-11-05 at 11:59 +0100, A B wrote:
>
>> If you just wanted PostgreSQL to go as fast as possible WITHOUT any
>> care for your data (you accept 100% dataloss and datacorruption if any
>> error should occur), what settings should you use then?
>>
>
> You can initdb to ramdisk, if you have enough RAM. It will fast, really.
>
>
That is approximately the same thing as the answer to the question
whether Ford Taurus can reach 200mph.
It can, just once, if you run it down the cliff.
--
Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
http://www.vmsinfo.com
The Leader in Integrated Media Intelligence Solutions
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: Running PostgreSQL as fast as possible no matter the consequences
@ 2010-11-07 00:21 Craig Ringer <craig@postnewspapers.com.au>
parent: A B <gentosaker@gmail.com>
2 siblings, 0 replies; 31+ messages in thread
From: Craig Ringer @ 2010-11-07 00:21 UTC (permalink / raw)
To: A B <gentosaker@gmail.com>; +Cc: Szymon Guz <mabewlun@gmail.com>; pgsql-performance
On 11/05/2010 07:32 PM, A B wrote:
> The server will just boot, load data, run, hopefully not crash but if
> it would, just start over with load and run.
Have you looked at VoltDB? It's designed for fast in-memory use.
--
Craig Ringer
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: Running PostgreSQL as fast as possible no matter the consequences
@ 2010-11-08 15:57 Dimitri Fontaine <dimitri@2ndQuadrant.fr>
parent: Lello, Nick <nick.lello@rentrakmail.com>
1 sibling, 0 replies; 31+ messages in thread
From: Dimitri Fontaine @ 2010-11-08 15:57 UTC (permalink / raw)
To: Lello\, Nick <nick.lello@rentrakmail.com>; +Cc: pgsql-performance
"Lello, Nick" <nick.lello@rentrakmail.com> writes:
> A bigger gain can probably be had if you have a tightly controlled
> suite of queries that will be run against the database and you can
> spend the time to tune each to ensure it performs no sequential scans
> (ie: Every query uses index lookups).
Given a fixed pool of queries, you can prepare them in advance so that
you don't usually pay the parsing and planning costs. I've found that
the planning is easily more expensive than the executing when all data
fits in RAM.
Enter pgbouncer and preprepare :
http://wiki.postgresql.org/wiki/PgBouncer
http://preprepare.projects.postgresql.org/README.html
Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: Running PostgreSQL as fast as possible no matter the consequences
@ 2010-11-08 15:58 Klaus Ita <klaus@worstofall.com>
parent: Lello, Nick <nick.lello@rentrakmail.com>
1 sibling, 0 replies; 31+ messages in thread
From: Klaus Ita @ 2010-11-08 15:58 UTC (permalink / raw)
To: Lello, Nick <nick.lello@rentrakmail.com>; +Cc: pgsql-performance
Use a replicated setup?
On Nov 8, 2010 4:21 PM, "Lello, Nick" <nick.lello@rentrakmail.com> wrote:
How about either:-
a) Size the pool so all your data fits into it.
b) Use a RAM-based filesystem (ie: a memory disk or SSD) for the
data storage [memory disk will be faster] with a Smaller pool
- Your seed data should be a copy of the datastore on disk filesystem;
at startup time copy the storage files from the physical to memory.
A bigger gain can probably be had if you have a tightly controlled
suite of queries that will be run against the database and you can
spend the time to tune each to ensure it performs no sequential scans
(ie: Every query uses index lookups).
On 5 November 2010 11:32, A B <gentosaker@gmail.com> wrote:
>>> If you just wanted PostgreSQL to g...
--
Nick Lello | Web Architect
o +1 503.284.7581 x418 / +44 (0) 8433309374 | m +44 (0) 7917 138319
Email: nick.lello at rentrak.com
RENTRAK | www.rentrak.com | NASDAQ: RENT
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to y...
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: Running PostgreSQL as fast as possible no matter the consequences
@ 2010-11-15 15:06 Robert Haas <robertmhaas@gmail.com>
parent: Jon Nelson <jnelson+pgsql@jamponi.net>
0 siblings, 1 reply; 31+ messages in thread
From: Robert Haas @ 2010-11-15 15:06 UTC (permalink / raw)
To: Jon Nelson <jnelson+pgsql@jamponi.net>; +Cc: pgsql-performance
On Fri, Nov 5, 2010 at 8:12 AM, Jon Nelson <jnelson+pgsql@jamponi.net> wrote:
> On Fri, Nov 5, 2010 at 7:08 AM, Guillaume Cottenceau <gc@mnc.ch> wrote:
>> Marti Raudsepp <marti 'at' juffo.org> writes:
>>
>>> On Fri, Nov 5, 2010 at 13:32, A B <gentosaker@gmail.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
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: Running PostgreSQL as fast as possible no matter the consequences
@ 2010-11-15 19:27 Andy Colson <andy@squeakycode.net>
parent: Robert Haas <robertmhaas@gmail.com>
0 siblings, 1 reply; 31+ messages in thread
From: Andy Colson @ 2010-11-15 19:27 UTC (permalink / raw)
To: Robert Haas <robertmhaas@gmail.com>; +Cc: pgsql-performance
On 11/15/2010 9:06 AM, Robert Haas wrote:
> 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.
>
if I am reading this right means: we can run our db safely (with fsync
and full_page_writes enabled) except for tables of our choosing?
If so, I am very +1 for this!
-Andy
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: Running PostgreSQL as fast as possible no matter the consequences
@ 2010-11-15 19:36 Robert Haas <robertmhaas@gmail.com>
parent: Andy Colson <andy@squeakycode.net>
0 siblings, 0 replies; 31+ messages in thread
From: Robert Haas @ 2010-11-15 19:36 UTC (permalink / raw)
To: Andy Colson <andy@squeakycode.net>; +Cc: pgsql-performance
On Mon, Nov 15, 2010 at 2:27 PM, Andy Colson <andy@squeakycode.net> wrote:
> On 11/15/2010 9:06 AM, Robert Haas wrote:
>>
>> 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.
>>
>
> if I am reading this right means: we can run our db safely (with fsync and
> full_page_writes enabled) except for tables of our choosing?
>
> If so, I am very +1 for this!
Yep. But we need some vic^H^Holunteers to reviews and test the patches.
https://commitfest.postgresql.org/action/patch_view?id=424
Code review, benchmarking, or just general tinkering and reporting
what you find out on the -hackers thread would be appreciated.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: Running PostgreSQL as fast as possible no matter the consequences
@ 2011-01-19 17:07 Bruce Momjian <bruce@momjian.us>
parent: Chris Browne <cbbrowne@acm.org>
0 siblings, 2 replies; 31+ messages in thread
From: Bruce Momjian @ 2011-01-19 17:07 UTC (permalink / raw)
To: Chris Browne <cbbrowne@acm.org>; +Cc: pgsql-performance
Chris Browne wrote:
> gentosaker@gmail.com (A B) writes:
> > If you just wanted PostgreSQL to go as fast as possible WITHOUT any
> > care for your data (you accept 100% dataloss and datacorruption if any
> > error should occur), what settings should you use then?
>
> Use /dev/null. It is web scale, and there are good tutorials.
>
> But seriously, there *are* cases where "blind speed" is of use. When
> loading data into a fresh database is a good time for this; if things
> fall over, it may be pretty acceptable to start "from scratch" with
> mkfs/initdb.
>
> I'd:
> - turn off fsync
> - turn off synchronous commit
> - put as much as possible onto Ramdisk/tmpfs/similar as possible
FYI, we do have a documentation section about how to configure Postgres
for improved performance if you don't care about durability:
http://developer.postgresql.org/pgdocs/postgres/non-durability.html
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: Running PostgreSQL as fast as possible no matter the consequences
@ 2011-01-19 17:27 Fabrízio de Royes Mello <fabriziomello@gmail.com>
parent: Bruce Momjian <bruce@momjian.us>
1 sibling, 0 replies; 31+ messages in thread
From: Fabrízio de Royes Mello @ 2011-01-19 17:27 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: Chris Browne <cbbrowne@acm.org>; pgsql-performance
2011/1/19 Bruce Momjian <bruce@momjian.us>
>
> FYI, we do have a documentation section about how to configure Postgres
> for improved performance if you don't care about durability:
>
> http://developer.postgresql.org/pgdocs/postgres/non-durability.html
>
A sometime ago I wrote in my blog [1] (sorry but available only in
pt-br) how to create an in-memory database with PostgreSQL. This little
article is based on post of Mr. Robert Haas about this topic [2].
[1]
http://fabriziomello.blogspot.com/2010/06/postgresql-na-memoria-ram-in-memory.html
[2]
http://rhaas.blogspot.com/2010/06/postgresql-as-in-memory-only-database_24.html
--
Fabrízio de Royes Mello
>> Blog sobre TI: http://fabriziomello.blogspot.com
>> Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: Running PostgreSQL as fast as possible no matter the consequences
@ 2011-01-20 15:25 Robert Haas <robertmhaas@gmail.com>
parent: Bruce Momjian <bruce@momjian.us>
1 sibling, 1 reply; 31+ messages in thread
From: Robert Haas @ 2011-01-20 15:25 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: Chris Browne <cbbrowne@acm.org>; pgsql-performance
On Wed, Jan 19, 2011 at 12:07 PM, Bruce Momjian <bruce@momjian.us> wrote:
> Chris Browne wrote:
>> gentosaker@gmail.com (A B) writes:
>> > If you just wanted PostgreSQL to go as fast as possible WITHOUT any
>> > care for your data (you accept 100% dataloss and datacorruption if any
>> > error should occur), what settings should you use then?
>>
>> Use /dev/null. It is web scale, and there are good tutorials.
>>
>> But seriously, there *are* cases where "blind speed" is of use. When
>> loading data into a fresh database is a good time for this; if things
>> fall over, it may be pretty acceptable to start "from scratch" with
>> mkfs/initdb.
>>
>> I'd:
>> - turn off fsync
>> - turn off synchronous commit
>> - put as much as possible onto Ramdisk/tmpfs/similar as possible
>
> FYI, we do have a documentation section about how to configure Postgres
> for improved performance if you don't care about durability:
>
> http://developer.postgresql.org/pgdocs/postgres/non-durability.html
This sentence looks to me like it should be removed, or perhaps clarified:
This does affect database crash transaction durability.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: Running PostgreSQL as fast as possible no matter the consequences
@ 2011-01-26 01:32 Bruce Momjian <bruce@momjian.us>
parent: Robert Haas <robertmhaas@gmail.com>
0 siblings, 1 reply; 31+ messages in thread
From: Bruce Momjian @ 2011-01-26 01:32 UTC (permalink / raw)
To: Robert Haas <robertmhaas@gmail.com>; +Cc: Chris Browne <cbbrowne@acm.org>; pgsql-performance
Robert Haas wrote:
> On Wed, Jan 19, 2011 at 12:07 PM, Bruce Momjian <bruce@momjian.us> wrote:
> > Chris Browne wrote:
> >> gentosaker@gmail.com (A B) writes:
> >> > If you just wanted PostgreSQL to go as fast as possible WITHOUT any
> >> > care for your data (you accept 100% dataloss and datacorruption if any
> >> > error should occur), what settings should you use then?
> >>
> >> Use /dev/null. ?It is web scale, and there are good tutorials.
> >>
> >> But seriously, there *are* cases where "blind speed" is of use. ?When
> >> loading data into a fresh database is a good time for this; if things
> >> fall over, it may be pretty acceptable to start "from scratch" with
> >> mkfs/initdb.
> >>
> >> I'd:
> >> - turn off fsync
> >> - turn off synchronous commit
> >> - put as much as possible onto Ramdisk/tmpfs/similar as possible
> >
> > FYI, we do have a documentation section about how to configure Postgres
> > for improved performance if you don't care about durability:
> >
> > ? ? ? ?http://developer.postgresql.org/pgdocs/postgres/non-durability.html
>
> This sentence looks to me like it should be removed, or perhaps clarified:
>
> This does affect database crash transaction durability.
Uh, doesn't it affect database crash transaction durability? I have
applied the attached patch to clarify things. Thanks.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
Attachments:
/rtmp/sync.diff (text/x-diff, 835B) download
diff --git a/doc/src/sgml/perform.sgml b/doc/src/sgml/perform.sgml
index 2699828..fb55598 100644
*** a/doc/src/sgml/perform.sgml
--- b/doc/src/sgml/perform.sgml
*************** SELECT * FROM x, y, a, b, c WHERE someth
*** 1159,1165 ****
<para>
Turn off <xref linkend="guc-synchronous-commit">; there might be no
need to write the <acronym>WAL</acronym> to disk on every
! commit. This does affect database crash transaction durability.
</para>
</listitem>
</itemizedlist>
--- 1159,1165 ----
<para>
Turn off <xref linkend="guc-synchronous-commit">; there might be no
need to write the <acronym>WAL</acronym> to disk on every
! commit. This can cause transaction loss after a server crash.
</para>
</listitem>
</itemizedlist>
* Re: Running PostgreSQL as fast as possible no matter the consequences
@ 2011-01-27 16:51 Jeff Janes <jeff.janes@gmail.com>
parent: Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 31+ messages in thread
From: Jeff Janes @ 2011-01-27 16:51 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: Robert Haas <robertmhaas@gmail.com>; Chris Browne <cbbrowne@acm.org>; pgsql-performance
On Tue, Jan 25, 2011 at 5:32 PM, Bruce Momjian <bruce@momjian.us> wrote:
> Robert Haas wrote:
>> On Wed, Jan 19, 2011 at 12:07 PM, Bruce Momjian <bruce@momjian.us> wrote:
>> > ? ? ? ?http://developer.postgresql.org/pgdocs/postgres/non-durability.html
>>
>> This sentence looks to me like it should be removed, or perhaps clarified:
>>
>> This does affect database crash transaction durability.
>
> Uh, doesn't it affect database crash transaction durability? I have
> applied the attached patch to clarify things. Thanks.
I think the point that was trying to be made there was that the other
parameters only lose and corrupt data when the machine crashes.
Synchronous commit turned off will lose data on a mere postgresql
server crash, it doesn't require a machine-level crash to cause data
loss.
Indeed, the currently committed doc is quite misleading.
" The following are configuration changes you can make
to improve performance in such cases; they do not invalidate
commit guarantees related to database crashes, only abrupt operating
system stoppage, except as mentioned below"
We've now removed the thing being mentioned below, but did not remove
the promise we would be mentioning those things.
Cheers,
Jeff
^ permalink raw reply [nested|flat] 31+ messages in thread
* Re: Running PostgreSQL as fast as possible no matter the consequences
@ 2011-01-27 17:07 Bruce Momjian <bruce@momjian.us>
parent: Jeff Janes <jeff.janes@gmail.com>
0 siblings, 0 replies; 31+ messages in thread
From: Bruce Momjian @ 2011-01-27 17:07 UTC (permalink / raw)
To: Jeff Janes <jeff.janes@gmail.com>; +Cc: Robert Haas <robertmhaas@gmail.com>; Chris Browne <cbbrowne@acm.org>; pgsql-performance
Jeff Janes wrote:
> On Tue, Jan 25, 2011 at 5:32 PM, Bruce Momjian <bruce@momjian.us> wrote:
> > Robert Haas wrote:
> >> On Wed, Jan 19, 2011 at 12:07 PM, Bruce Momjian <bruce@momjian.us> wrote:
>
> >> > ? ? ? ?http://developer.postgresql.org/pgdocs/postgres/non-durability.html
> >>
> >> This sentence looks to me like it should be removed, or perhaps clarified:
> >>
> >> ? ? This does affect database crash transaction durability.
> >
> > Uh, doesn't it affect database crash transaction durability? ?I have
> > applied the attached patch to clarify things. ?Thanks.
>
> I think the point that was trying to be made there was that the other
> parameters only lose and corrupt data when the machine crashes.
> Synchronous commit turned off will lose data on a mere postgresql
> server crash, it doesn't require a machine-level crash to cause data
> loss.
>
> Indeed, the currently committed doc is quite misleading.
>
> " The following are configuration changes you can make
> to improve performance in such cases; they do not invalidate
> commit guarantees related to database crashes, only abrupt operating
> system stoppage, except as mentioned below"
>
> We've now removed the thing being mentioned below, but did not remove
> the promise we would be mentioning those things.
Excellent point. The old wording was just too clever and even I forgot
why I was making that point. I have updated the docs to clearly state
why this setting is different from the ones above. Thanks for spotting
this.
Applied patch attached.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
Attachments:
/rtmp/diff (text/x-diff, 853B) download
diff --git a/doc/src/sgml/perform.sgml b/doc/src/sgml/perform.sgml
index 0a10457..1bec5b1 100644
*** a/doc/src/sgml/perform.sgml
--- b/doc/src/sgml/perform.sgml
*************** SELECT * FROM x, y, a, b, c WHERE someth
*** 1157,1165 ****
<listitem>
<para>
! Turn off <xref linkend="guc-synchronous-commit">; there is no
need to write the <acronym>WAL</acronym> to disk on every
! commit.
</para>
</listitem>
</itemizedlist>
--- 1157,1166 ----
<listitem>
<para>
! Turn off <xref linkend="guc-synchronous-commit">; there might be no
need to write the <acronym>WAL</acronym> to disk on every
! commit. This does enable possible tranaction loss in case of
! a <emphasis>database</> crash.
</para>
</listitem>
</itemizedlist>
end of thread, other threads:[~2011-01-27 17:07 UTC | newest]
Thread overview: 31+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-11-05 10:59 Running PostgreSQL as fast as possible no matter the consequences A B <gentosaker@gmail.com>
2010-11-05 11:11 ` Guillaume Cottenceau <gc@mnc.ch>
2010-11-05 11:30 ` Marti Raudsepp <marti@juffo.org>
2010-11-05 12:06 ` Guillaume Cottenceau <gc@mnc.ch>
2010-11-05 11:14 ` Thom Brown <thom@linux.com>
2010-11-05 11:15 ` Thom Brown <thom@linux.com>
2010-11-05 11:24 ` A B <gentosaker@gmail.com>
2010-11-05 11:23 ` Szymon Guz <mabewlun@gmail.com>
2010-11-05 11:32 ` A B <gentosaker@gmail.com>
2010-11-05 11:36 ` Marti Raudsepp <marti@juffo.org>
2010-11-05 11:41 ` Thom Brown <thom@linux.com>
2010-11-05 12:08 ` Guillaume Cottenceau <gc@mnc.ch>
2010-11-05 12:12 ` Jon Nelson <jnelson+pgsql@jamponi.net>
2010-11-15 15:06 ` Robert Haas <robertmhaas@gmail.com>
2010-11-15 19:27 ` Andy Colson <andy@squeakycode.net>
2010-11-15 19:36 ` Robert Haas <robertmhaas@gmail.com>
2010-11-05 12:26 ` Lello, Nick <nick.lello@rentrakmail.com>
2010-11-08 15:57 ` Dimitri Fontaine <dimitri@2ndQuadrant.fr>
2010-11-08 15:58 ` Klaus Ita <klaus@worstofall.com>
2010-11-07 00:21 ` Craig Ringer <craig@postnewspapers.com.au>
2010-11-05 11:27 ` Craig Ringer <craig@postnewspapers.com.au>
2010-11-05 11:36 ` A B <gentosaker@gmail.com>
2010-11-05 15:13 ` Chris Browne <cbbrowne@acm.org>
2011-01-19 17:07 ` Bruce Momjian <bruce@momjian.us>
2011-01-19 17:27 ` Fabrízio de Royes Mello <fabriziomello@gmail.com>
2011-01-20 15:25 ` Robert Haas <robertmhaas@gmail.com>
2011-01-26 01:32 ` Bruce Momjian <bruce@momjian.us>
2011-01-27 16:51 ` Jeff Janes <jeff.janes@gmail.com>
2011-01-27 17:07 ` Bruce Momjian <bruce@momjian.us>
2010-11-05 15:23 ` Devrim GÜNDÜZ <devrim@gunduz.org>
2010-11-05 16:25 ` Mladen Gogala <mladen.gogala@vmsinfo.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