pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: Large tables (was: RAID 0 not as fast as
24+ messages / 10 participants
[nested] [flat]
* Re: Large tables (was: RAID 0 not as fast as
@ 2006-09-19 01:14 Luke Lonergan <llonergan@greenplum.com>
0 siblings, 0 replies; 24+ messages in thread
From: Luke Lonergan @ 2006-09-19 01:14 UTC (permalink / raw)
To: Alex Turner <armtuk@gmail.com>; Bucky Jordan <bjordan@lumeta.com>; +Cc: Merlin Moncure <mmoncure@gmail.com>; pgsql-performance
Alex,
On 9/18/06 4:14 PM, "Alex Turner" <armtuk@gmail.com> wrote:
> Be warned, the tech specs page:
> http://www.sun.com/servers/x64/x4500/specs.xml#anchor3
> doesn't mention RAID 10 as a possible, and this is probably what most would
> recommend for fast data access if you are doing both read and write
> operations. If you are doing mostly Read, then RAID 5 is passable, but it's
> redundancy with large numbers of drives is not so great.
RAID10 works great on the X4500 we get 1.6GB/s + per X4500 using RAID10 in
ZFS. We worked with the Sun Solaris kernel team to make that happen and the
patches are part of Solaris 10 Update 3 due out in November.
- Luke
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Large tables (was: RAID 0 not as fast as
@ 2006-09-19 03:42 Luke Lonergan <llonergan@greenplum.com>
0 siblings, 0 replies; 24+ messages in thread
From: Luke Lonergan @ 2006-09-19 03:42 UTC (permalink / raw)
To: Alex Turner <armtuk@gmail.com>; +Cc: Bucky Jordan <bjordan@lumeta.com>; Merlin Moncure <mmoncure@gmail.com>; pgsql-performance
Yep, Solaris ZFS kicks butt. It does RAID10/5/6, etc and implements most of
the high end features available on high end SANs...
- Luke
On 9/18/06 8:40 PM, "Alex Turner" <armtuk@gmail.com> wrote:
> Sweet - thats good - RAID 10 support seems like an odd thing to leave out.
>
> Alex
>
> On 9/18/06, Luke Lonergan < llonergan@greenplum.com
> <mailto:llonergan@greenplum.com> > wrote:
>> Alex,
>>
>> On 9/18/06 4:14 PM, "Alex Turner" < armtuk@gmail.com> wrote:
>>
>>> Be warned, the tech specs page:
>>> http://www.sun.com/servers/x64/x4500/specs.xml#anchor3
>>> <http://www.sun.com/servers/x64/x4500/specs.xml#anchor3;
>>> doesn't mention RAID 10 as a possible, and this is probably what most would
>>> recommend for fast data access if you are doing both read and write
>>> operations. If you are doing mostly Read, then RAID 5 is passable, but it's
>>> redundancy with large numbers of drives is not so great.
>>
>> RAID10 works great on the X4500 we get 1.6GB/s + per X4500 using RAID10 in
>> ZFS. We worked with the Sun Solaris kernel team to make that happen and the
>> patches are part of Solaris 10 Update 3 due out in November.
>>
>> - Luke
>>
>>
>
>
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Large tables (was: RAID 0 not as fast as
@ 2006-09-19 04:01 Luke Lonergan <llonergan@greenplum.com>
2006-09-20 08:09 ` Re: Large tables (was: RAID 0 not as fast as Markus Schaber <schabi@logix-tt.com>
0 siblings, 1 reply; 24+ messages in thread
From: Luke Lonergan @ 2006-09-19 04:01 UTC (permalink / raw)
To: mark@mark.mielke.cc; +Cc: Bucky Jordan <bjordan@lumeta.com>; Spiegelberg, Greg <gspiegelberg@cranel.com>; Joshua Drake <jd@commandprompt.com>; Craig A. James <cjames@modgraph-usa.com>; pgsql-performance
Mark,
On 9/18/06 8:45 PM, "mark@mark.mielke.cc" <mark@mark.mielke.cc> wrote:
> Does a tool exist yet to time this for a particular configuration?
We're considering building this into ANALYZE on a per-table basis. The
basic approach times sequential access in page rate, then random seeks as
page rate and takes the ratio of same.
Since PG's heap scan is single threaded, the seek rate is equivalent to a
single disk (even though RAID arrays may have many spindles), the typical
random seek rates are around 100-200 seeks per second from within the
backend. That means that as sequential scan performance increases, such as
happens when using large RAID arrays, the random_page_cost will range from
50 to 300 linearly as the size of the RAID array increases.
- Luke
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Large tables (was: RAID 0 not as fast as
2006-09-19 04:01 Re: Large tables (was: RAID 0 not as fast as Luke Lonergan <llonergan@greenplum.com>
@ 2006-09-20 08:09 ` Markus Schaber <schabi@logix-tt.com>
2006-09-20 17:03 ` Re: Large tables (was: RAID 0 not as fast as Luke Lonergan <llonergan@greenplum.com>
0 siblings, 1 reply; 24+ messages in thread
From: Markus Schaber @ 2006-09-20 08:09 UTC (permalink / raw)
To: pgsql-performance
Hi, Luke,
Luke Lonergan wrote:
> Since PG's heap scan is single threaded, the seek rate is equivalent to a
> single disk (even though RAID arrays may have many spindles), the typical
> random seek rates are around 100-200 seeks per second from within the
> backend. That means that as sequential scan performance increases, such as
> happens when using large RAID arrays, the random_page_cost will range from
> 50 to 300 linearly as the size of the RAID array increases.
Do you think that adding some posix_fadvise() calls to the backend to
pre-fetch some blocks into the OS cache asynchroneously could improve
that situation?
I could imagine that e. G. index bitmap scans could profit in the heap
fetching stage by fadvise()ing the next few blocks.
Maybe asynchroneous I/O could be used for the same benefit, but
posix_fadvise is less() intrusive, and can easily be #define'd out on
platforms that don't support it.
Combine this with the Linux Kernel I/O Scheduler patches (readahead
improvements) that were discussed here in summer...
Regards,
Markus
--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in Europe! www.ffii.org
www.nosoftwarepatents.org
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Large tables (was: RAID 0 not as fast as
2006-09-19 04:01 Re: Large tables (was: RAID 0 not as fast as Luke Lonergan <llonergan@greenplum.com>
2006-09-20 08:09 ` Re: Large tables (was: RAID 0 not as fast as Markus Schaber <schabi@logix-tt.com>
@ 2006-09-20 17:03 ` Luke Lonergan <llonergan@greenplum.com>
2006-09-20 18:02 ` Re: Large tables (was: RAID 0 not as fast as Markus Schaber <schabi@logix-tt.com>
2006-09-21 19:13 ` Re: Large tables (was: RAID 0 not as fast as Bucky Jordan <bjordan@lumeta.com>
0 siblings, 2 replies; 24+ messages in thread
From: Luke Lonergan @ 2006-09-20 17:03 UTC (permalink / raw)
To: Markus Schaber <schabi@logix-tt.com>; pgsql-performance
Markus,
On 9/20/06 1:09 AM, "Markus Schaber" <schabi@logix-tt.com> wrote:
> Do you think that adding some posix_fadvise() calls to the backend to
> pre-fetch some blocks into the OS cache asynchroneously could improve
> that situation?
Nope - this requires true multi-threading of the I/O, there need to be
multiple seek operations running simultaneously. The current executor
blocks on each page request, waiting for the I/O to happen before requesting
the next page. The OS can't predict what random page is to be requested
next.
We can implement multiple scanners (already present in MPP), or we could
implement AIO and fire off a number of simultaneous I/O requests for
fulfillment.
- Luke
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Large tables (was: RAID 0 not as fast as
2006-09-19 04:01 Re: Large tables (was: RAID 0 not as fast as Luke Lonergan <llonergan@greenplum.com>
2006-09-20 08:09 ` Re: Large tables (was: RAID 0 not as fast as Markus Schaber <schabi@logix-tt.com>
2006-09-20 17:03 ` Re: Large tables (was: RAID 0 not as fast as Luke Lonergan <llonergan@greenplum.com>
@ 2006-09-20 18:02 ` Markus Schaber <schabi@logix-tt.com>
2006-09-20 19:35 ` Re: Large tables (was: RAID 0 not as fast as Ron <rjpeace@earthlink.net>
2006-09-21 00:23 ` Re: Large tables (was: RAID 0 not as fast as Luke Lonergan <llonergan@greenplum.com>
1 sibling, 2 replies; 24+ messages in thread
From: Markus Schaber @ 2006-09-20 18:02 UTC (permalink / raw)
To: pgsql-performance
Hi, Luke,
Luke Lonergan wrote:
>> Do you think that adding some posix_fadvise() calls to the backend to
>> pre-fetch some blocks into the OS cache asynchroneously could improve
>> that situation?
>
> Nope - this requires true multi-threading of the I/O, there need to be
> multiple seek operations running simultaneously. The current executor
> blocks on each page request, waiting for the I/O to happen before requesting
> the next page. The OS can't predict what random page is to be requested
> next.
I thought that posix_fadvise() with POSIX_FADV_WILLNEED was exactly
meant for this purpose?
My idea was that the executor could posix_fadvise() the blocks it will
need in the near future, and later, when it actually issues the blocking
read, the block is there already. This could even give speedups in the
single-spindle case, as the I/O scheduler could already fetch the next
blocks while the executor processes the current one.
But there must be some details in the executor that prevent this.
> We can implement multiple scanners (already present in MPP), or we could
> implement AIO and fire off a number of simultaneous I/O requests for
> fulfillment.
AIO is much more intrusive to implement, so I'd preferrably look
whether posix_fadvise() could improve the situation.
Thanks,
Markus
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Large tables (was: RAID 0 not as fast as
2006-09-19 04:01 Re: Large tables (was: RAID 0 not as fast as Luke Lonergan <llonergan@greenplum.com>
2006-09-20 08:09 ` Re: Large tables (was: RAID 0 not as fast as Markus Schaber <schabi@logix-tt.com>
2006-09-20 17:03 ` Re: Large tables (was: RAID 0 not as fast as Luke Lonergan <llonergan@greenplum.com>
2006-09-20 18:02 ` Re: Large tables (was: RAID 0 not as fast as Markus Schaber <schabi@logix-tt.com>
@ 2006-09-20 19:35 ` Ron <rjpeace@earthlink.net>
1 sibling, 0 replies; 24+ messages in thread
From: Ron @ 2006-09-20 19:35 UTC (permalink / raw)
To: Markus Schaber <schabi@logix-tt.com>; +Cc: pgsql-performance
IMHO, AIO is the architecturally cleaner and more elegant solution.
We in fact have a project on the boards to do this but funding (as
yet) has not been found.
My $.02,
Ron
At 02:02 PM 9/20/2006, Markus Schaber wrote:
>Hi, Luke,
>
>Luke Lonergan wrote:
>
> >> Do you think that adding some posix_fadvise() calls to the backend to
> >> pre-fetch some blocks into the OS cache asynchroneously could improve
> >> that situation?
> >
> > Nope - this requires true multi-threading of the I/O, there need to be
> > multiple seek operations running simultaneously. The current executor
> > blocks on each page request, waiting for the I/O to happen before
> requesting
> > the next page. The OS can't predict what random page is to be requested
> > next.
>
>I thought that posix_fadvise() with POSIX_FADV_WILLNEED was exactly
>meant for this purpose?
>
>My idea was that the executor could posix_fadvise() the blocks it will
>need in the near future, and later, when it actually issues the blocking
>read, the block is there already. This could even give speedups in the
>single-spindle case, as the I/O scheduler could already fetch the next
>blocks while the executor processes the current one.
>
>But there must be some details in the executor that prevent this.
>
> > We can implement multiple scanners (already present in MPP), or we could
> > implement AIO and fire off a number of simultaneous I/O requests for
> > fulfillment.
>
>AIO is much more intrusive to implement, so I'd preferrably look
>whether posix_fadvise() could improve the situation.
>
>Thanks,
>Markus
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Large tables (was: RAID 0 not as fast as
2006-09-19 04:01 Re: Large tables (was: RAID 0 not as fast as Luke Lonergan <llonergan@greenplum.com>
2006-09-20 08:09 ` Re: Large tables (was: RAID 0 not as fast as Markus Schaber <schabi@logix-tt.com>
2006-09-20 17:03 ` Re: Large tables (was: RAID 0 not as fast as Luke Lonergan <llonergan@greenplum.com>
2006-09-20 18:02 ` Re: Large tables (was: RAID 0 not as fast as Markus Schaber <schabi@logix-tt.com>
@ 2006-09-21 00:23 ` Luke Lonergan <llonergan@greenplum.com>
2006-09-21 07:31 ` Re: Large tables (was: RAID 0 not as fast as Markus Schaber <schabi@logix-tt.com>
1 sibling, 1 reply; 24+ messages in thread
From: Luke Lonergan @ 2006-09-21 00:23 UTC (permalink / raw)
To: Markus Schaber <schabi@logix-tt.com>; pgsql-performance
Markus,
On 9/20/06 11:02 AM, "Markus Schaber" <schabi@logix-tt.com> wrote:
> I thought that posix_fadvise() with POSIX_FADV_WILLNEED was exactly
> meant for this purpose?
This is a good idea - I wasn't aware that this was possible.
We'll do some testing and see if it works as advertised on Linux and
Solaris.
- Luke
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Large tables (was: RAID 0 not as fast as
2006-09-19 04:01 Re: Large tables (was: RAID 0 not as fast as Luke Lonergan <llonergan@greenplum.com>
2006-09-20 08:09 ` Re: Large tables (was: RAID 0 not as fast as Markus Schaber <schabi@logix-tt.com>
2006-09-20 17:03 ` Re: Large tables (was: RAID 0 not as fast as Luke Lonergan <llonergan@greenplum.com>
2006-09-20 18:02 ` Re: Large tables (was: RAID 0 not as fast as Markus Schaber <schabi@logix-tt.com>
2006-09-21 00:23 ` Re: Large tables (was: RAID 0 not as fast as Luke Lonergan <llonergan@greenplum.com>
@ 2006-09-21 07:31 ` Markus Schaber <schabi@logix-tt.com>
2006-09-22 02:52 ` Re: Large tables (was: RAID 0 not as fast as Guy Thornley <guy@esphion.com>
0 siblings, 1 reply; 24+ messages in thread
From: Markus Schaber @ 2006-09-21 07:31 UTC (permalink / raw)
To: Luke Lonergan <llonergan@greenplum.com>; +Cc: pgsql-performance
Hi, Luke,
Luke Lonergan wrote:
>> I thought that posix_fadvise() with POSIX_FADV_WILLNEED was exactly
>> meant for this purpose?
>
> This is a good idea - I wasn't aware that this was possible.
This possibility was the reason for me to propose it. :-)
> We'll do some testing and see if it works as advertised on Linux and
> Solaris.
Fine, I'm looking forward to the results.
According to my small test, it works at least on linux 2.6.17.4.
Btw, posix_fadvise() could even give a small improvement for
multi-threaded backends, given that the I/O subsystem is smart enough to
cope intelligently to cope with large bunches of outstanding requests.
HTH,
Markus
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Large tables (was: RAID 0 not as fast as
2006-09-19 04:01 Re: Large tables (was: RAID 0 not as fast as Luke Lonergan <llonergan@greenplum.com>
2006-09-20 08:09 ` Re: Large tables (was: RAID 0 not as fast as Markus Schaber <schabi@logix-tt.com>
2006-09-20 17:03 ` Re: Large tables (was: RAID 0 not as fast as Luke Lonergan <llonergan@greenplum.com>
2006-09-20 18:02 ` Re: Large tables (was: RAID 0 not as fast as Markus Schaber <schabi@logix-tt.com>
2006-09-21 00:23 ` Re: Large tables (was: RAID 0 not as fast as Luke Lonergan <llonergan@greenplum.com>
2006-09-21 07:31 ` Re: Large tables (was: RAID 0 not as fast as Markus Schaber <schabi@logix-tt.com>
@ 2006-09-22 02:52 ` Guy Thornley <guy@esphion.com>
2006-09-22 03:05 ` Re: Large tables (was: RAID 0 not as fast as Bruce Momjian <bruce@momjian.us>
2006-09-22 03:40 ` Re: Large tables (was: RAID 0 not as fast as mark@mark.mielke.cc
2006-09-22 09:13 ` Re: Large tables (was: RAID 0 not as fast as Markus Schaber <schabi@logix-tt.com>
0 siblings, 3 replies; 24+ messages in thread
From: Guy Thornley @ 2006-09-22 02:52 UTC (permalink / raw)
To: Markus Schaber <schabi@logix-tt.com>; +Cc: Luke Lonergan <llonergan@greenplum.com>; pgsql-performance
> >> I thought that posix_fadvise() with POSIX_FADV_WILLNEED was exactly
> >> meant for this purpose?
> >
> > This is a good idea - I wasn't aware that this was possible.
>
> This possibility was the reason for me to propose it. :-)
posix_fadvise() features in the TODO list already; I'm not sure if any work
on it has been done for pg8.2.
Anyway, I understand that POSIX_FADV_DONTNEED on a linux 2.6 kernel allows
pages to be discarded from memory earlier than usual. This is useful, since
it means you can prevent your seqscan from nuking the OS cache.
Of course you could argue the OS should be able to detect this, and prevent
it occuring anyway. I don't know anything about linux's behaviour in this
area.
.Guy
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Large tables (was: RAID 0 not as fast as
2006-09-19 04:01 Re: Large tables (was: RAID 0 not as fast as Luke Lonergan <llonergan@greenplum.com>
2006-09-20 08:09 ` Re: Large tables (was: RAID 0 not as fast as Markus Schaber <schabi@logix-tt.com>
2006-09-20 17:03 ` Re: Large tables (was: RAID 0 not as fast as Luke Lonergan <llonergan@greenplum.com>
2006-09-20 18:02 ` Re: Large tables (was: RAID 0 not as fast as Markus Schaber <schabi@logix-tt.com>
2006-09-21 00:23 ` Re: Large tables (was: RAID 0 not as fast as Luke Lonergan <llonergan@greenplum.com>
2006-09-21 07:31 ` Re: Large tables (was: RAID 0 not as fast as Markus Schaber <schabi@logix-tt.com>
2006-09-22 02:52 ` Re: Large tables (was: RAID 0 not as fast as Guy Thornley <guy@esphion.com>
@ 2006-09-22 03:05 ` Bruce Momjian <bruce@momjian.us>
2006-09-22 14:02 ` Re: Large tables (was: RAID 0 not as fast as Jim C. Nasby <jim@nasby.net>
2 siblings, 1 reply; 24+ messages in thread
From: Bruce Momjian @ 2006-09-22 03:05 UTC (permalink / raw)
To: Guy Thornley <guy@esphion.com>; +Cc: Markus Schaber <schabi@logix-tt.com>; Luke Lonergan <llonergan@greenplum.com>; pgsql-performance
Guy Thornley wrote:
> > >> I thought that posix_fadvise() with POSIX_FADV_WILLNEED was exactly
> > >> meant for this purpose?
> > >
> > > This is a good idea - I wasn't aware that this was possible.
> >
> > This possibility was the reason for me to propose it. :-)
>
> posix_fadvise() features in the TODO list already; I'm not sure if any work
> on it has been done for pg8.2.
>
> Anyway, I understand that POSIX_FADV_DONTNEED on a linux 2.6 kernel allows
> pages to be discarded from memory earlier than usual. This is useful, since
> it means you can prevent your seqscan from nuking the OS cache.
>
> Of course you could argue the OS should be able to detect this, and prevent
> it occuring anyway. I don't know anything about linux's behaviour in this
> area.
We tried posix_fadvise() during the 8.2 development cycle, but had
problems as outlined in a comment in xlog.c:
/*
* posix_fadvise is problematic on many platforms: on older x86 Linux
* it just dumps core, and there are reports of problems on PPC platforms
* as well. The following is therefore disabled for the time being.
* We could consider some kind of configure test to see if it's safe to
* use, but since we lack hard evidence that there's any useful performance
* gain to be had, spending time on that seems unprofitable for now.
*/
--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Large tables (was: RAID 0 not as fast as
2006-09-19 04:01 Re: Large tables (was: RAID 0 not as fast as Luke Lonergan <llonergan@greenplum.com>
2006-09-20 08:09 ` Re: Large tables (was: RAID 0 not as fast as Markus Schaber <schabi@logix-tt.com>
2006-09-20 17:03 ` Re: Large tables (was: RAID 0 not as fast as Luke Lonergan <llonergan@greenplum.com>
2006-09-20 18:02 ` Re: Large tables (was: RAID 0 not as fast as Markus Schaber <schabi@logix-tt.com>
2006-09-21 00:23 ` Re: Large tables (was: RAID 0 not as fast as Luke Lonergan <llonergan@greenplum.com>
2006-09-21 07:31 ` Re: Large tables (was: RAID 0 not as fast as Markus Schaber <schabi@logix-tt.com>
2006-09-22 02:52 ` Re: Large tables (was: RAID 0 not as fast as Guy Thornley <guy@esphion.com>
2006-09-22 03:05 ` Re: Large tables (was: RAID 0 not as fast as Bruce Momjian <bruce@momjian.us>
@ 2006-09-22 14:02 ` Jim C. Nasby <jim@nasby.net>
0 siblings, 0 replies; 24+ messages in thread
From: Jim C. Nasby @ 2006-09-22 14:02 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: Guy Thornley <guy@esphion.com>; Markus Schaber <schabi@logix-tt.com>; Luke Lonergan <llonergan@greenplum.com>; pgsql-performance
On Thu, Sep 21, 2006 at 11:05:39PM -0400, Bruce Momjian wrote:
> We tried posix_fadvise() during the 8.2 development cycle, but had
> problems as outlined in a comment in xlog.c:
>
> /*
> * posix_fadvise is problematic on many platforms: on older x86 Linux
> * it just dumps core, and there are reports of problems on PPC platforms
> * as well. The following is therefore disabled for the time being.
> * We could consider some kind of configure test to see if it's safe to
> * use, but since we lack hard evidence that there's any useful performance
> * gain to be had, spending time on that seems unprofitable for now.
> */
In case it's not clear, that's a call for someone to do some performance
testing. :)
Bruce, you happen to have a URL for a patch to put fadvise in?
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Large tables (was: RAID 0 not as fast as
2006-09-19 04:01 Re: Large tables (was: RAID 0 not as fast as Luke Lonergan <llonergan@greenplum.com>
2006-09-20 08:09 ` Re: Large tables (was: RAID 0 not as fast as Markus Schaber <schabi@logix-tt.com>
2006-09-20 17:03 ` Re: Large tables (was: RAID 0 not as fast as Luke Lonergan <llonergan@greenplum.com>
2006-09-20 18:02 ` Re: Large tables (was: RAID 0 not as fast as Markus Schaber <schabi@logix-tt.com>
2006-09-21 00:23 ` Re: Large tables (was: RAID 0 not as fast as Luke Lonergan <llonergan@greenplum.com>
2006-09-21 07:31 ` Re: Large tables (was: RAID 0 not as fast as Markus Schaber <schabi@logix-tt.com>
2006-09-22 02:52 ` Re: Large tables (was: RAID 0 not as fast as Guy Thornley <guy@esphion.com>
@ 2006-09-22 03:40 ` mark@mark.mielke.cc
2006-09-22 03:46 ` Re: Large tables (was: RAID 0 not as fast as Luke Lonergan <llonergan@greenplum.com>
2 siblings, 1 reply; 24+ messages in thread
From: mark@mark.mielke.cc @ 2006-09-22 03:40 UTC (permalink / raw)
To: Guy Thornley <guy@esphion.com>; +Cc: Markus Schaber <schabi@logix-tt.com>; Luke Lonergan <llonergan@greenplum.com>; pgsql-performance
On Fri, Sep 22, 2006 at 02:52:09PM +1200, Guy Thornley wrote:
> > >> I thought that posix_fadvise() with POSIX_FADV_WILLNEED was exactly
> > >> meant for this purpose?
> > > This is a good idea - I wasn't aware that this was possible.
> > This possibility was the reason for me to propose it. :-)
> posix_fadvise() features in the TODO list already; I'm not sure if any work
> on it has been done for pg8.2.
>
> Anyway, I understand that POSIX_FADV_DONTNEED on a linux 2.6 kernel allows
> pages to be discarded from memory earlier than usual. This is useful, since
> it means you can prevent your seqscan from nuking the OS cache.
>
> Of course you could argue the OS should be able to detect this, and prevent
> it occuring anyway. I don't know anything about linux's behaviour in this
> area.
I recall either monitoring or participating in the discussion when this
call was added to Linux.
I don't believe the kernel can auto-detect that you do not need a page
any longer. It can only prioritize pages to keep when memory is fully
in use and a new page must be loaded. This is often some sort of LRU
scheme. If the page is truly useless, only the application can know.
I'm not convinced that PostgreSQL can know this. The case where it is
useful is if a single process is sequentially scanning a large file
(much larger than memory). As soon as it is more than one process,
or if it is not a sequential scan, or if it is not a large file, this
call hurts more than it gains. Just because I'm done with the page does
not mean that *you* are done with the page.
I'd advise against using this call unless it can be shown that the page
will not be used in the future, or at least, that the page is less useful
than all other pages currently in memory. This is what the call really means.
It means, "There is no value to keeping this page in memory".
Perhaps certain PostgreSQL loads fit this pattern. None of my uses fit
this pattern, and I have trouble believing that a majority of PostgreSQL
loads fits this pattern.
Cheers,
mark
--
mark@mielke.cc / markm@ncf.ca / markm@nortel.com __________________________
. . _ ._ . . .__ . . ._. .__ . . . .__ | Neighbourhood Coder
|\/| |_| |_| |/ |_ |\/| | |_ | |/ |_ |
| | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada
One ring to rule them all, one ring to find them, one ring to bring them all
and in the darkness bind them...
http://mark.mielke.cc/
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Large tables (was: RAID 0 not as fast as
2006-09-19 04:01 Re: Large tables (was: RAID 0 not as fast as Luke Lonergan <llonergan@greenplum.com>
2006-09-20 08:09 ` Re: Large tables (was: RAID 0 not as fast as Markus Schaber <schabi@logix-tt.com>
2006-09-20 17:03 ` Re: Large tables (was: RAID 0 not as fast as Luke Lonergan <llonergan@greenplum.com>
2006-09-20 18:02 ` Re: Large tables (was: RAID 0 not as fast as Markus Schaber <schabi@logix-tt.com>
2006-09-21 00:23 ` Re: Large tables (was: RAID 0 not as fast as Luke Lonergan <llonergan@greenplum.com>
2006-09-21 07:31 ` Re: Large tables (was: RAID 0 not as fast as Markus Schaber <schabi@logix-tt.com>
2006-09-22 02:52 ` Re: Large tables (was: RAID 0 not as fast as Guy Thornley <guy@esphion.com>
2006-09-22 03:40 ` Re: Large tables (was: RAID 0 not as fast as mark@mark.mielke.cc
@ 2006-09-22 03:46 ` Luke Lonergan <llonergan@greenplum.com>
2006-09-22 14:01 ` Re: Large tables (was: RAID 0 not as fast as Jim C. Nasby <jim@nasby.net>
2006-09-24 13:29 ` Re: Large tables (was: RAID 0 not as fast as Ron Mayer <rm_pg@cheapcomplexdevices.com>
0 siblings, 2 replies; 24+ messages in thread
From: Luke Lonergan @ 2006-09-22 03:46 UTC (permalink / raw)
To: mark@mark.mielke.cc, "Guy Thornley" <guy@esphion.com>; +Cc: Markus Schaber <schabi@logix-tt.com>; pgsql-performance
Mark,
On 9/21/06 8:40 PM, "mark@mark.mielke.cc" <mark@mark.mielke.cc> wrote:
> I'd advise against using this call unless it can be shown that the page
> will not be used in the future, or at least, that the page is less useful
> than all other pages currently in memory. This is what the call really means.
> It means, "There is no value to keeping this page in memory".
Yes, it's a bit subtle.
I think the topic is similar to "cache bypass", used in cache capable vector
processors (Cray, Convex, Multiflow, etc) in the 90's. When you are
scanning through something larger than the cache, it should be marked
"non-cacheable" and bypass caching altogether. This avoids a copy, and
keeps the cache available for things that can benefit from it.
WRT the PG buffer cache, the rule would have to be: "if the heap scan is
going to be larger than "effective_cache_size", then issue the
posix_fadvise(BLOCK_NOT_NEEDED) call". It doesn't sound very efficient to
do this in block/extent increments though, and it would possibly mess with
subsets of the block space that would be re-used for other queries.
- Luke
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Large tables (was: RAID 0 not as fast as
2006-09-19 04:01 Re: Large tables (was: RAID 0 not as fast as Luke Lonergan <llonergan@greenplum.com>
2006-09-20 08:09 ` Re: Large tables (was: RAID 0 not as fast as Markus Schaber <schabi@logix-tt.com>
2006-09-20 17:03 ` Re: Large tables (was: RAID 0 not as fast as Luke Lonergan <llonergan@greenplum.com>
2006-09-20 18:02 ` Re: Large tables (was: RAID 0 not as fast as Markus Schaber <schabi@logix-tt.com>
2006-09-21 00:23 ` Re: Large tables (was: RAID 0 not as fast as Luke Lonergan <llonergan@greenplum.com>
2006-09-21 07:31 ` Re: Large tables (was: RAID 0 not as fast as Markus Schaber <schabi@logix-tt.com>
2006-09-22 02:52 ` Re: Large tables (was: RAID 0 not as fast as Guy Thornley <guy@esphion.com>
2006-09-22 03:40 ` Re: Large tables (was: RAID 0 not as fast as mark@mark.mielke.cc
2006-09-22 03:46 ` Re: Large tables (was: RAID 0 not as fast as Luke Lonergan <llonergan@greenplum.com>
@ 2006-09-22 14:01 ` Jim C. Nasby <jim@nasby.net>
2006-09-25 14:23 ` Re: Large tables (was: RAID 0 not as fast as Luke Lonergan <llonergan@greenplum.com>
1 sibling, 1 reply; 24+ messages in thread
From: Jim C. Nasby @ 2006-09-22 14:01 UTC (permalink / raw)
To: Luke Lonergan <llonergan@greenplum.com>; +Cc: mark@mark.mielke.cc, Guy Thornley <guy@esphion.com>; Markus Schaber <schabi@logix-tt.com>; pgsql-performance
On Thu, Sep 21, 2006 at 08:46:41PM -0700, Luke Lonergan wrote:
> Mark,
>
> On 9/21/06 8:40 PM, "mark@mark.mielke.cc" <mark@mark.mielke.cc> wrote:
>
> > I'd advise against using this call unless it can be shown that the page
> > will not be used in the future, or at least, that the page is less useful
> > than all other pages currently in memory. This is what the call really means.
> > It means, "There is no value to keeping this page in memory".
>
> Yes, it's a bit subtle.
>
> I think the topic is similar to "cache bypass", used in cache capable vector
> processors (Cray, Convex, Multiflow, etc) in the 90's. When you are
> scanning through something larger than the cache, it should be marked
> "non-cacheable" and bypass caching altogether. This avoids a copy, and
> keeps the cache available for things that can benefit from it.
>
> WRT the PG buffer cache, the rule would have to be: "if the heap scan is
> going to be larger than "effective_cache_size", then issue the
> posix_fadvise(BLOCK_NOT_NEEDED) call". It doesn't sound very efficient to
> do this in block/extent increments though, and it would possibly mess with
> subsets of the block space that would be re-used for other queries.
Another issue is that if you start two large seqscans on the same table
at about the same time, right now you should only be issuing one set of
reads for both requests, because one of them will just pull the blocks
back out of cache. If we weren't caching then each query would have to
physically read (which would be horrid).
There's been talk of adding code that would have a seqscan detect if
another seqscan is happening on the table at the same time, and if it
is, to start it's seqscan wherever the other seqscan is currently
running. That would probably ensure that we weren't reading from the
table in 2 different places, even if we weren't caching.
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Large tables (was: RAID 0 not as fast as
2006-09-19 04:01 Re: Large tables (was: RAID 0 not as fast as Luke Lonergan <llonergan@greenplum.com>
2006-09-20 08:09 ` Re: Large tables (was: RAID 0 not as fast as Markus Schaber <schabi@logix-tt.com>
2006-09-20 17:03 ` Re: Large tables (was: RAID 0 not as fast as Luke Lonergan <llonergan@greenplum.com>
2006-09-20 18:02 ` Re: Large tables (was: RAID 0 not as fast as Markus Schaber <schabi@logix-tt.com>
2006-09-21 00:23 ` Re: Large tables (was: RAID 0 not as fast as Luke Lonergan <llonergan@greenplum.com>
2006-09-21 07:31 ` Re: Large tables (was: RAID 0 not as fast as Markus Schaber <schabi@logix-tt.com>
2006-09-22 02:52 ` Re: Large tables (was: RAID 0 not as fast as Guy Thornley <guy@esphion.com>
2006-09-22 03:40 ` Re: Large tables (was: RAID 0 not as fast as mark@mark.mielke.cc
2006-09-22 03:46 ` Re: Large tables (was: RAID 0 not as fast as Luke Lonergan <llonergan@greenplum.com>
2006-09-22 14:01 ` Re: Large tables (was: RAID 0 not as fast as Jim C. Nasby <jim@nasby.net>
@ 2006-09-25 14:23 ` Luke Lonergan <llonergan@greenplum.com>
0 siblings, 0 replies; 24+ messages in thread
From: Luke Lonergan @ 2006-09-25 14:23 UTC (permalink / raw)
To: Jim C. Nasby <jim@nasby.net>; +Cc: mark@mark.mielke.cc, "Guy Thornley" <guy@esphion.com>; Markus Schaber <schabi@logix-tt.com>; pgsql-performance
Jim,
On 9/22/06 7:01 AM, "Jim C. Nasby" <jim@nasby.net> wrote:
> There's been talk of adding code that would have a seqscan detect if
> another seqscan is happening on the table at the same time, and if it
> is, to start it's seqscan wherever the other seqscan is currently
> running. That would probably ensure that we weren't reading from the
> table in 2 different places, even if we weren't caching.
Right, aka "SyncScan"
The optimization you point out that we miss when bypassing cache is a pretty
unlikely event in real world apps, though it makes poorly designed
benchmarks go really fast. It's much more likely that the second seqscan
will start after the block cache is exhausted, which will cause actuator
thrashing (depending on the readahead that the OS uses). SyncScan fixes
that.
- Luke
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Large tables (was: RAID 0 not as fast as
2006-09-19 04:01 Re: Large tables (was: RAID 0 not as fast as Luke Lonergan <llonergan@greenplum.com>
2006-09-20 08:09 ` Re: Large tables (was: RAID 0 not as fast as Markus Schaber <schabi@logix-tt.com>
2006-09-20 17:03 ` Re: Large tables (was: RAID 0 not as fast as Luke Lonergan <llonergan@greenplum.com>
2006-09-20 18:02 ` Re: Large tables (was: RAID 0 not as fast as Markus Schaber <schabi@logix-tt.com>
2006-09-21 00:23 ` Re: Large tables (was: RAID 0 not as fast as Luke Lonergan <llonergan@greenplum.com>
2006-09-21 07:31 ` Re: Large tables (was: RAID 0 not as fast as Markus Schaber <schabi@logix-tt.com>
2006-09-22 02:52 ` Re: Large tables (was: RAID 0 not as fast as Guy Thornley <guy@esphion.com>
2006-09-22 03:40 ` Re: Large tables (was: RAID 0 not as fast as mark@mark.mielke.cc
2006-09-22 03:46 ` Re: Large tables (was: RAID 0 not as fast as Luke Lonergan <llonergan@greenplum.com>
@ 2006-09-24 13:29 ` Ron Mayer <rm_pg@cheapcomplexdevices.com>
1 sibling, 0 replies; 24+ messages in thread
From: Ron Mayer @ 2006-09-24 13:29 UTC (permalink / raw)
To: Luke Lonergan <llonergan@greenplum.com>
Luke Lonergan wrote:
>
> I think the topic is similar to "cache bypass", used in cache capable vector
> processors (Cray, Convex, Multiflow, etc) in the 90's. When you are
> scanning through something larger than the cache, it should be marked
> "non-cacheable" and bypass caching altogether. This avoids a copy, and
> keeps the cache available for things that can benefit from it.
And 'course some file systems do this automatically when they
detect a sequential scan[1] though it can have unexpected (to some)
negative side effects[2]. For file systems that support freebehind
as a configurable parameter, it might be easier to experiment with
the idea there.
[1] http://www.ediaudit.com/doc_sol10/Solaris_10_Doc/common/SUNWaadm/reloc/sun_docs/C/solaris_10/SUNWaad...
[2] http://bugs.opensolaris.org/bugdatabase/view_bug.do?bug_id=6207772
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Large tables (was: RAID 0 not as fast as
2006-09-19 04:01 Re: Large tables (was: RAID 0 not as fast as Luke Lonergan <llonergan@greenplum.com>
2006-09-20 08:09 ` Re: Large tables (was: RAID 0 not as fast as Markus Schaber <schabi@logix-tt.com>
2006-09-20 17:03 ` Re: Large tables (was: RAID 0 not as fast as Luke Lonergan <llonergan@greenplum.com>
2006-09-20 18:02 ` Re: Large tables (was: RAID 0 not as fast as Markus Schaber <schabi@logix-tt.com>
2006-09-21 00:23 ` Re: Large tables (was: RAID 0 not as fast as Luke Lonergan <llonergan@greenplum.com>
2006-09-21 07:31 ` Re: Large tables (was: RAID 0 not as fast as Markus Schaber <schabi@logix-tt.com>
2006-09-22 02:52 ` Re: Large tables (was: RAID 0 not as fast as Guy Thornley <guy@esphion.com>
@ 2006-09-22 09:13 ` Markus Schaber <schabi@logix-tt.com>
2 siblings, 0 replies; 24+ messages in thread
From: Markus Schaber @ 2006-09-22 09:13 UTC (permalink / raw)
To: pgsql-performance
Hi, Guy,
Guy Thornley wrote:
> Of course you could argue the OS should be able to detect this, and prevent
> it occuring anyway. I don't know anything about linux's behaviour in this
> area.
Yes, one can argue that way.
But a generic Algorithm in the OS can never be as smart as the
application which has more informations about semantics and algorithms.
Everything else would need a crystal ball device :-)
HTH,
Markus
--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in Europe! www.ffii.org
www.nosoftwarepatents.org
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Large tables (was: RAID 0 not as fast as
2006-09-19 04:01 Re: Large tables (was: RAID 0 not as fast as Luke Lonergan <llonergan@greenplum.com>
2006-09-20 08:09 ` Re: Large tables (was: RAID 0 not as fast as Markus Schaber <schabi@logix-tt.com>
2006-09-20 17:03 ` Re: Large tables (was: RAID 0 not as fast as Luke Lonergan <llonergan@greenplum.com>
@ 2006-09-21 19:13 ` Bucky Jordan <bjordan@lumeta.com>
2006-09-21 19:41 ` Re: Large tables (was: RAID 0 not as fast as Mark Lewis <mark.lewis@mir3.com>
2006-09-21 19:54 ` Re: Large tables (was: RAID 0 not as fast as Markus Schaber <schabi@logix-tt.com>
1 sibling, 2 replies; 24+ messages in thread
From: Bucky Jordan @ 2006-09-21 19:13 UTC (permalink / raw)
To: Luke Lonergan <llonergan@greenplum.com>; Markus Schaber <schabi@logix-tt.com>; pgsql-performance
> > Do you think that adding some posix_fadvise() calls to the backend
to
> > pre-fetch some blocks into the OS cache asynchroneously could
improve
> > that situation?
>
> Nope - this requires true multi-threading of the I/O, there need to be
> multiple seek operations running simultaneously. The current executor
> blocks on each page request, waiting for the I/O to happen before
> requesting
> the next page. The OS can't predict what random page is to be
requested
> next.
>
> We can implement multiple scanners (already present in MPP), or we
could
> implement AIO and fire off a number of simultaneous I/O requests for
> fulfillment.
So this might be a dumb question, but the above statements apply to the
cluster (e.g. postmaster) as a whole, not per postgres
process/transaction correct? So each transaction is blocked waiting for
the main postmaster to retrieve the data in the order it was requested
(i.e. not multiple scanners/aio)?
In this case, the only way to take full advantage of larger hardware
using normal postgres would be to run multiple instances? (Which might
not be a bad idea since it would set your application up to be able to
deal with databases distributed on multiple servers...)
- Bucky
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Large tables (was: RAID 0 not as fast as
2006-09-19 04:01 Re: Large tables (was: RAID 0 not as fast as Luke Lonergan <llonergan@greenplum.com>
2006-09-20 08:09 ` Re: Large tables (was: RAID 0 not as fast as Markus Schaber <schabi@logix-tt.com>
2006-09-20 17:03 ` Re: Large tables (was: RAID 0 not as fast as Luke Lonergan <llonergan@greenplum.com>
2006-09-21 19:13 ` Re: Large tables (was: RAID 0 not as fast as Bucky Jordan <bjordan@lumeta.com>
@ 2006-09-21 19:41 ` Mark Lewis <mark.lewis@mir3.com>
1 sibling, 0 replies; 24+ messages in thread
From: Mark Lewis @ 2006-09-21 19:41 UTC (permalink / raw)
To: Bucky Jordan <bjordan@lumeta.com>; +Cc: Luke Lonergan <llonergan@greenplum.com>; Markus Schaber <schabi@logix-tt.com>; pgsql-performance
> So this might be a dumb question, but the above statements apply to the
> cluster (e.g. postmaster) as a whole, not per postgres
> process/transaction correct? So each transaction is blocked waiting for
> the main postmaster to retrieve the data in the order it was requested
> (i.e. not multiple scanners/aio)?
Each connection runs its own separate back-end process, so these
statements apply per PG connection (=process).
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Large tables (was: RAID 0 not as fast as
2006-09-19 04:01 Re: Large tables (was: RAID 0 not as fast as Luke Lonergan <llonergan@greenplum.com>
2006-09-20 08:09 ` Re: Large tables (was: RAID 0 not as fast as Markus Schaber <schabi@logix-tt.com>
2006-09-20 17:03 ` Re: Large tables (was: RAID 0 not as fast as Luke Lonergan <llonergan@greenplum.com>
2006-09-21 19:13 ` Re: Large tables (was: RAID 0 not as fast as Bucky Jordan <bjordan@lumeta.com>
@ 2006-09-21 19:54 ` Markus Schaber <schabi@logix-tt.com>
2006-09-21 21:16 ` Re: Large tables (was: RAID 0 not as fast as Bucky Jordan <bjordan@lumeta.com>
1 sibling, 1 reply; 24+ messages in thread
From: Markus Schaber @ 2006-09-21 19:54 UTC (permalink / raw)
To: pgsql-performance; +Cc: bjordan@lumeta.com
Hi, Bucky,
Bucky Jordan wrote:
>> We can implement multiple scanners (already present in MPP), or we
> could
>> implement AIO and fire off a number of simultaneous I/O requests for
>> fulfillment.
>
> So this might be a dumb question, but the above statements apply to the
> cluster (e.g. postmaster) as a whole, not per postgres
> process/transaction correct? So each transaction is blocked waiting for
> the main postmaster to retrieve the data in the order it was requested
> (i.e. not multiple scanners/aio)?
No, that's a wrong assumption.
It applies per active backend. When connecting, the Postmaster forks a
new backend process. Each backend process has its own scanner and
executor. The main postmaster is only for coordination (forking, config
reload etc.), all the work is done in the forked per-connection backends.
Furthermore, the PostgreSQL MVCC system ensures that readers are neither
ever blocked nor blocking other backends. Writers can block each other
due to the ACID transaction semantics, however the MVCC limits that to a
minimum.
> In this case, the only way to take full advantage of larger hardware
> using normal postgres would be to run multiple instances? (Which might
> not be a bad idea since it would set your application up to be able to
> deal with databases distributed on multiple servers...)
Typical OLTP applications (Web UIs, Booking systems, etc.) have multiple
connections, and those run fully parallel.
So if your application is of this type, it will take full advantage of
larger hardware. In the list archive, you should find some links to
benchmarks that prove this statement, PostgreSQL scales linearly, up to
8 CPUs and 32 "hyperthreads" in this benchmarks.
Our discussion is about some different type of application, where you
have a single application issuing a single query at a time dealing with
a large amount (several gigs up to teras) of data.
Now, when such a query is generating sequential disk access, the I/O
scheduler of the underlying OS can easily recognize that pattern, and
prefetch the data, thus giving the full speed benefit of the underlying
RAID.
The discussed problem arises when such large queries generate random
(non-continous) disk access (e. G. index scans). Here, the underlying
RAID cannot effectively prefetch data as it does not know what the
application will need next. This effectively limits the speed to that of
a single disk, regardless of the details of the underlying RAID, as it
can only process a request at a time, and has to wait for the
application for the next one.
Now, Bizgres MPP goes the way of having multiple threads per backend,
each one processing a fraction of the data. So there are always several
outstanding read requests that can be scheduled to the disks.
My proposal was to use posix_fadvise() in the single-threaded scanner,
so it can tell the OS "I will need those blocks in the near future". So
the OS can pre-fetch those blocks into the cache, while PostgreSQL still
processes the previous block of data.
Another proposal would be to use so-called asynchroneous I/O. This is
definitely an interesting and promising idea, but needs much more
changes to the code, compared to posix_fadvise().
I hope that this lengthy mail is enlightening, if not, don't hesitate to
ask.
Thanks for your patience,
Markus
--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in Europe! www.ffii.org
www.nosoftwarepatents.org
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Large tables (was: RAID 0 not as fast as
2006-09-19 04:01 Re: Large tables (was: RAID 0 not as fast as Luke Lonergan <llonergan@greenplum.com>
2006-09-20 08:09 ` Re: Large tables (was: RAID 0 not as fast as Markus Schaber <schabi@logix-tt.com>
2006-09-20 17:03 ` Re: Large tables (was: RAID 0 not as fast as Luke Lonergan <llonergan@greenplum.com>
2006-09-21 19:13 ` Re: Large tables (was: RAID 0 not as fast as Bucky Jordan <bjordan@lumeta.com>
2006-09-21 19:54 ` Re: Large tables (was: RAID 0 not as fast as Markus Schaber <schabi@logix-tt.com>
@ 2006-09-21 21:16 ` Bucky Jordan <bjordan@lumeta.com>
2006-09-21 21:42 ` Re: Large tables (was: RAID 0 not as fast as Markus Schaber <schabi@logix-tt.com>
2006-09-21 23:31 ` Re: Large tables (was: RAID 0 not as fast as Luke Lonergan <llonergan@greenplum.com>
0 siblings, 2 replies; 24+ messages in thread
From: Bucky Jordan @ 2006-09-21 21:16 UTC (permalink / raw)
To: pgsql-performance
Markus,
First, thanks- your email was very enlightining. But, it does bring up a
few additional questions, so thanks for your patience also- I've listed
them below.
> It applies per active backend. When connecting, the Postmaster forks a
> new backend process. Each backend process has its own scanner and
> executor. The main postmaster is only for coordination (forking,
config
> reload etc.), all the work is done in the forked per-connection
backends.
Each postgres process also uses shared memory (aka the buffer cache) so
as to not fetch data that another process has already requested,
correct?
> Our discussion is about some different type of application, where you
> have a single application issuing a single query at a time dealing
with
> a large amount (several gigs up to teras) of data.
Commonly these are referred to as OLAP applications, correct? Which is
where I believe my application is more focused (it may be handling some
transactions in the future, but at the moment, it follows the "load lots
of data, then analyze it" pattern).
> The discussed problem arises when such large queries generate random
> (non-continous) disk access (e. G. index scans). Here, the underlying
> RAID cannot effectively prefetch data as it does not know what the
> application will need next. This effectively limits the speed to that
of
> a single disk, regardless of the details of the underlying RAID, as it
> can only process a request at a time, and has to wait for the
> application for the next one.
Does this have anything to do with postgres indexes not storing data, as
some previous posts to this list have mentioned? (In otherwords, having
the index in memory doesn't help? Or are we talking about indexes that
are too large to fit in RAM?)
So this issue would be only on a per query basis? Could it be alleviated
somewhat if I ran multiple smaller queries? For example, I want to
calculate a summary table on 500m records- fire off 5 queries that count
100m records each and update the summary table, leaving MVCC to handle
update contention?
Actually, now that I think about it- that would only work if the
sections I mentioned above were on different disks right? So I would
actually have to do table partitioning with tablespaces on different
spindles to get that to be beneficial? (which is basically not feasible
with RAID, since I don't get to pick what disks the data goes on...)
Are there any other workarounds for current postgres?
Thanks again,
Bucky
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Large tables (was: RAID 0 not as fast as
2006-09-19 04:01 Re: Large tables (was: RAID 0 not as fast as Luke Lonergan <llonergan@greenplum.com>
2006-09-20 08:09 ` Re: Large tables (was: RAID 0 not as fast as Markus Schaber <schabi@logix-tt.com>
2006-09-20 17:03 ` Re: Large tables (was: RAID 0 not as fast as Luke Lonergan <llonergan@greenplum.com>
2006-09-21 19:13 ` Re: Large tables (was: RAID 0 not as fast as Bucky Jordan <bjordan@lumeta.com>
2006-09-21 19:54 ` Re: Large tables (was: RAID 0 not as fast as Markus Schaber <schabi@logix-tt.com>
2006-09-21 21:16 ` Re: Large tables (was: RAID 0 not as fast as Bucky Jordan <bjordan@lumeta.com>
@ 2006-09-21 21:42 ` Markus Schaber <schabi@logix-tt.com>
1 sibling, 0 replies; 24+ messages in thread
From: Markus Schaber @ 2006-09-21 21:42 UTC (permalink / raw)
To: pgsql-performance; +Cc: Bucky Jordan <bjordan@lumeta.com>
Hi, Bucky,
Bucky Jordan wrote:
> Each postgres process also uses shared memory (aka the buffer cache) so
> as to not fetch data that another process has already requested,
> correct?
Yes.
Additinally, the OS caches disk blocks. Most unixoid ones like Linux use
(nearly) all unused memory for this block cache, I don't know about Windows.
> Commonly these are referred to as OLAP applications, correct? Which is
> where I believe my application is more focused (it may be handling some
> transactions in the future, but at the moment, it follows the "load lots
> of data, then analyze it" pattern).
Yes, most OLAP apps fall into this category. But I also think that most
OLAP apps mainly generate sequential data access (sequential scans), for
which the OS prefetching of data works fine.
Btw, some weeks ago, there was a patch mentioned here that improves the
linux kernel I/O scheduler wr/t those prefetching capabilities.
> Does this have anything to do with postgres indexes not storing data, as
> some previous posts to this list have mentioned? (In otherwords, having
> the index in memory doesn't help? Or are we talking about indexes that
> are too large to fit in RAM?)
Yes, it has, but only for the cases where your query fetches only
columns in that index. In case where you fetch other columns, PostgreSQL
has to access the Heap nevertheless to fetch those.
The overhead for checking outdated row versions (those that were updated
or deleted, but not yet vacuumed) is zero, as those "load bulk, then
analyze" applications typically don't create invalid rows, so every row
fetched from the heap is valid. This is very different in OLTP applications.
> So this issue would be only on a per query basis? Could it be alleviated
> somewhat if I ran multiple smaller queries? For example, I want to
> calculate a summary table on 500m records- fire off 5 queries that count
> 100m records each and update the summary table, leaving MVCC to handle
> update contention?
Yes, you could do that, but only if you're CPU bound, and have a
multi-core machine. And you need table partitioning, as LIMIT/OFFSET is
expensive. Btw, the Bizgres people do exactly this under their hood, so
it may be worth a look.
If you're I/O bound, and your query is a full table scan, or something
else that results in (nearly) sequential disk access, the OS prefetch
algorithm will work.
You can use some I/O monitoring tools to compare the actual speed the
data comes in when PostgreSQL does the sequential scan, and compare it
to DD'ing the database table files. For simple aggregates like sum(),
you usually get near the "raw" speed, and the real bottlenecks are the
disk I/O rate, bad RAID implementations or PCI bus contention.
> Actually, now that I think about it- that would only work if the
> sections I mentioned above were on different disks right? So I would
> actually have to do table partitioning with tablespaces on different
> spindles to get that to be beneficial? (which is basically not feasible
> with RAID, since I don't get to pick what disks the data goes on...)
If you really need that much throughput, you can always put the
different partitions on different RAIDs. But hardware gets very
expensive in those dimensions, and it may be better to partition the
data on different machines altogether. AFAIK, Bizgres MPP does exactly that.
> Are there any other workarounds for current postgres?
Are your questions of theoretical nature, or do you have a concrete
problem? In latter case, you could post your details here, and we'll see
whether we can help.
Btw, I'm not related with Bizgres in any way. :-)
HTH,
Markus
--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in Europe! www.ffii.org
www.nosoftwarepatents.org
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Large tables (was: RAID 0 not as fast as
2006-09-19 04:01 Re: Large tables (was: RAID 0 not as fast as Luke Lonergan <llonergan@greenplum.com>
2006-09-20 08:09 ` Re: Large tables (was: RAID 0 not as fast as Markus Schaber <schabi@logix-tt.com>
2006-09-20 17:03 ` Re: Large tables (was: RAID 0 not as fast as Luke Lonergan <llonergan@greenplum.com>
2006-09-21 19:13 ` Re: Large tables (was: RAID 0 not as fast as Bucky Jordan <bjordan@lumeta.com>
2006-09-21 19:54 ` Re: Large tables (was: RAID 0 not as fast as Markus Schaber <schabi@logix-tt.com>
2006-09-21 21:16 ` Re: Large tables (was: RAID 0 not as fast as Bucky Jordan <bjordan@lumeta.com>
@ 2006-09-21 23:31 ` Luke Lonergan <llonergan@greenplum.com>
1 sibling, 0 replies; 24+ messages in thread
From: Luke Lonergan @ 2006-09-21 23:31 UTC (permalink / raw)
To: Bucky Jordan <bjordan@lumeta.com>; pgsql-performance
Bucky,
On 9/21/06 2:16 PM, "Bucky Jordan" <bjordan@lumeta.com> wrote:
> Does this have anything to do with postgres indexes not storing data, as
> some previous posts to this list have mentioned? (In otherwords, having
> the index in memory doesn't help? Or are we talking about indexes that
> are too large to fit in RAM?)
Yes, if the index could be scanned without needing to scan the heap to
satisfy a query, that query would benefit from sequential access. This is
true whether the index fits in RAM or not.
> So this issue would be only on a per query basis? Could it be alleviated
> somewhat if I ran multiple smaller queries? For example, I want to
> calculate a summary table on 500m records- fire off 5 queries that count
> 100m records each and update the summary table, leaving MVCC to handle
> update contention?
Clever, functional and very painful way to do it, but yes, you would get 5
disks worth of seeking.
My goal is to provide for as many disks seeking at the same time as are
available to the RAID. Note that the Sun Appliance (X4500 based) has 11
disk drives available per CPU core. Later it will drop to 5-6 disks per
core with the introduction of quad core CPUs, which is more the norm for
now. Bizgres MPP will achieve one or two concurrent heap scanner per CPU
for a given query in the default configurations, so we're missing out on
lots of potential speedup for index scans in many cases.
With both MPP and stock Postgres you get more seek rate as you add users,
but it would take 44 users to use all of the drives in random seeking for
Postgres, where for MPP it would take more like 5.
> Actually, now that I think about it- that would only work if the
> sections I mentioned above were on different disks right? So I would
> actually have to do table partitioning with tablespaces on different
> spindles to get that to be beneficial? (which is basically not feasible
> with RAID, since I don't get to pick what disks the data goes on...)
On average, for random seeking we can assume that RAID will distribute the
data evenly. The I/Os should balance out.
- Luke
^ permalink raw reply [nested|flat] 24+ messages in thread
end of thread, other threads:[~2006-09-25 14:23 UTC | newest]
Thread overview: 24+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-09-19 01:14 Re: Large tables (was: RAID 0 not as fast as Luke Lonergan <llonergan@greenplum.com>
2006-09-19 03:42 Re: Large tables (was: RAID 0 not as fast as Luke Lonergan <llonergan@greenplum.com>
2006-09-19 04:01 Re: Large tables (was: RAID 0 not as fast as Luke Lonergan <llonergan@greenplum.com>
2006-09-20 08:09 ` Markus Schaber <schabi@logix-tt.com>
2006-09-20 17:03 ` Luke Lonergan <llonergan@greenplum.com>
2006-09-20 18:02 ` Markus Schaber <schabi@logix-tt.com>
2006-09-20 19:35 ` Ron <rjpeace@earthlink.net>
2006-09-21 00:23 ` Luke Lonergan <llonergan@greenplum.com>
2006-09-21 07:31 ` Markus Schaber <schabi@logix-tt.com>
2006-09-22 02:52 ` Guy Thornley <guy@esphion.com>
2006-09-22 03:05 ` Bruce Momjian <bruce@momjian.us>
2006-09-22 14:02 ` Jim C. Nasby <jim@nasby.net>
2006-09-22 03:40 ` mark@mark.mielke.cc
2006-09-22 03:46 ` Luke Lonergan <llonergan@greenplum.com>
2006-09-22 14:01 ` Jim C. Nasby <jim@nasby.net>
2006-09-25 14:23 ` Luke Lonergan <llonergan@greenplum.com>
2006-09-24 13:29 ` Ron Mayer <rm_pg@cheapcomplexdevices.com>
2006-09-22 09:13 ` Markus Schaber <schabi@logix-tt.com>
2006-09-21 19:13 ` Bucky Jordan <bjordan@lumeta.com>
2006-09-21 19:41 ` Mark Lewis <mark.lewis@mir3.com>
2006-09-21 19:54 ` Markus Schaber <schabi@logix-tt.com>
2006-09-21 21:16 ` Bucky Jordan <bjordan@lumeta.com>
2006-09-21 21:42 ` Markus Schaber <schabi@logix-tt.com>
2006-09-21 23:31 ` Luke Lonergan <llonergan@greenplum.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