Enhanded TODO:
* Experiment with multi-threaded backend better resource utilization
This would allow a single query to make use of multiple CPU's or
multiple I/O channels simultaneously. One idea is to create a
background reader that can pre
> That's only going to be true for very high end systems with multiple raid
> controllers and dozens of spindles.
Not true. I have a system right now that would benifit. My database is
only 500 megs and I have 2 gig of ram and two processors... I only have
a raid 1, but that is o.k. because most
On Mon, Apr 10, 2006 at 01:36:45PM -0400, Alvaro Herrera wrote:
> Markus Schiltknecht wrote:
> > On Mon, 2006-04-10 at 17:22 +0800, Qingqing Zhou wrote:
> > > Check the code InitPostgres(). These global varaibles are scattered in
> > > many
> > > places, so I am not sure if it is easy to write cle
On Mon, Apr 10, 2006 at 05:22:13PM +0800, Qingqing Zhou wrote:
>
> "Markus Schiltknecht" <[EMAIL PROTECTED]> wrote
> > Hi Qingqing,
> >
> > >
> > > As Tom pointed out, without big change, a backend on database "D1" can't
> > > connect to "D2". This is because to connect to a database, we need to
>
On Mon, Apr 10, 2006 at 12:02:56PM -0700, Luke Lonergan wrote:
> Hannu,
>
> On 4/10/06 2:23 AM, "Hannu Krosing" <[EMAIL PROTECTED]> wrote:
>
> >> The cost of fetching a page from the OS is not really much of an
> >> overhead,
> >
> > Have you tested this ?
>
> I have - the overhead of fetching
"Joshua D. Drake" <[EMAIL PROTECTED]> writes:
> Well I am go out on a limb here and gather to guess that sequential scans and
> index scans are still very relevant because the CPU could be bound by the scan
> (either one) based on the type of query being performed.
>
> This doesn't really have an
Greg Stark wrote:
Simon Riggs <[EMAIL PROTECTED]> writes:
I think it would be useful to think about exactly what type of
query/activity we are looking to improve the performance on. That way we
can understand the benefit of this proposal and take some baseline
measurements to analyse what is ha
Greg Stark wrote:
> Even on Solaris I'm sure parsing and preparing plans for all the queries,
> building up the system table cache for all the objects in the database, and so
> on are much much more expensive than fork(). I wouldn't be surprised if even
> on windows it was still a pretty close rac
Alvaro Herrera <[EMAIL PROTECTED]> writes:
> Greg Stark wrote:
> > Alvaro Herrera <[EMAIL PROTECTED]> writes:
> >
> > > An idea arising in chat with Joshua Drake: the retargetting code, if it
> > > turns out to work and not be excessively expensive, could also be useful
> > > to implement a ser
On Tue, 2006-04-11 at 07:47, Myron Scott wrote:
> client
> or additional processing. Am I missing something in this analysis?
>
> I've attached my dtrace script.
>
To answer my own question, I suppose my processors are
relatively slow compared to most setups.
Myron Scott
---
Simon Riggs <[EMAIL PROTECTED]> writes:
> I think it would be useful to think about exactly what type of
> query/activity we are looking to improve the performance on. That way we
> can understand the benefit of this proposal and take some baseline
> measurements to analyse what is happening for
Greg Stark wrote:
> Alvaro Herrera <[EMAIL PROTECTED]> writes:
>
> > An idea arising in chat with Joshua Drake: the retargetting code, if it
> > turns out to work and not be excessively expensive, could also be useful
> > to implement a server-side "connection pooling" of sorts: the postmaster
>
Alvaro Herrera <[EMAIL PROTECTED]> writes:
> An idea arising in chat with Joshua Drake: the retargetting code, if it
> turns out to work and not be excessively expensive, could also be useful
> to implement a server-side "connection pooling" of sorts: the postmaster
> could keep idle backends and
On Mon, 2006-04-10 at 02:16, Martijn van Oosterhout wrote:
> The appears to be two seperate cases here though, one is to just farm
> out the read request to another process (basically aio), the other is
> to do actual processing there. The latter is obviously for more useful
> but requires a fair
Gregory,
On 4/9/06 2:04 PM, "Gregory Maxwell" <[EMAIL PROTECTED]> wrote:
> It increases Linux's maximum readahead from 128K to 1meg .. and it
> should be smart enough that you could crank it up further without too
> much risk of hurting performance elsewhere.
Interesting - we are now routinely u
Hannu,
On 4/10/06 2:23 AM, "Hannu Krosing" <[EMAIL PROTECTED]> wrote:
>> The cost of fetching a page from the OS is not really much of an
>> overhead,
>
> Have you tested this ?
I have - the overhead of fetching a page from Linux I/O cache to buffer
cache is about an additional 20% over fetchin
Hi,
On Mon, 2006-04-10 at 13:36 -0400, Alvaro Herrera wrote:
> An idea arising in chat with Joshua Drake: the retargetting code, if it
> turns out to work and not be excessively expensive, could also be useful
> to implement a server-side "connection pooling" of sorts: the postmaster
> could keep
Markus Schiltknecht wrote:
> On Mon, 2006-04-10 at 17:22 +0800, Qingqing Zhou wrote:
> > Check the code InitPostgres(). These global varaibles are scattered in many
> > places, so I am not sure if it is easy to write clean code to clear up these
> > variables. But if you can come up with a patch to
On Mon, 2006-04-10 at 17:22 +0800, Qingqing Zhou wrote:
> Check the code InitPostgres(). These global varaibles are scattered in many
> places, so I am not sure if it is easy to write clean code to clear up these
> variables. But if you can come up with a patch to do reconnect without
> disconnect,
Ühel kenal päeval, P, 2006-04-09 kell 18:26, kirjutas Martijn van
Oosterhout:
> The cost of fetching a page from the OS is not really much of an
> overhead,
Have you tested this ?
I remember having a case, where data load usin COPY into a table with
several indexes ran an order of magnitude fas
"Markus Schiltknecht" <[EMAIL PROTECTED]> wrote
> Hi Qingqing,
>
> >
> > As Tom pointed out, without big change, a backend on database "D1" can't
> > connect to "D2". This is because to connect to a database, we need to
> > initialize a lot of variables. So when you reconnect to another one on
the
On Sun, Apr 09, 2006 at 05:54:56PM -0400, Tom Lane wrote:
> Here's a detailed scenario:
>
> 1. Backend X reads page N of a table T, queues a request for N+1.
> 2. While processing page N, backend X gets an error and aborts
> its transaction, thereby dropping all its lmgr locks
Hi Qingqing,
On Mon, 2006-04-10 at 16:38 +0800, Qingqing Zhou wrote:
> > Why not? What would be needed to retarget a backend to operate in
> > another database?
>
> As Tom pointed out, without big change, a backend on database "D1" can't
> connect to "D2". This is because to connect to a database,
On Sun, 2006-04-09 at 17:11 +0800, Qingqing Zhou wrote:
> "Tom Lane" <[EMAIL PROTECTED]> wrote
> >
> > This is exactly the bit of optimism I was questioning. We've already
> > been sweating blood trying to reduce multiprocessor contention on data
> > structures in which collisions ought to be avoi
"Markus Schiltknecht" <[EMAIL PROTECTED]> wrote
> Hi,
>
> On Sun, 2006-04-09 at 15:11 -0400, Tom Lane wrote:
> > You can't just retarget a backend to operate in another database, at
> > least not without major changes in that infrastructure.
>
> Why not? What would be needed to retarget a backend
Hi,
On Sun, 2006-04-09 at 15:11 -0400, Tom Lane wrote:
> You can't just retarget a backend to operate in another database, at
> least not without major changes in that infrastructure.
Why not? What would be needed to retarget a backend to operate in
another database?
Such a retargetting would b
"Luke Lonergan" <[EMAIL PROTECTED]> writes:
> On 4/9/06 9:27 AM, "Tom Lane" <[EMAIL PROTECTED]> wrote:
>> 2. There are some low-level assumptions that no one reads in pages of
>> a relation without having some kind of lock on the relation (consider
>> eg the case where the relation is being dropped
On 4/9/06, Luke Lonergan <[EMAIL PROTECTED]> wrote:
> Gregory,
>
> On 4/9/06 1:36 PM, "Gregory Maxwell" <[EMAIL PROTECTED]> wrote:
>
> > It might also be interesting for someone with the right testing rig on
> > linux to try the adaptive
> > readahead patch to see if that improves PG's ability to k
Tom,
On 4/9/06 9:27 AM, "Tom Lane" <[EMAIL PROTECTED]> wrote:
> 2. There are some low-level assumptions that no one reads in pages of
> a relation without having some kind of lock on the relation (consider
> eg the case where the relation is being dropped). A bgwriter-like
> process wouldn't be
Gregory,
On 4/9/06 1:36 PM, "Gregory Maxwell" <[EMAIL PROTECTED]> wrote:
> It might also be interesting for someone with the right testing rig on
> linux to try the adaptive
> readahead patch to see if that improves PG's ability to keep the disk busy.
"the" adaptive readahead patch? Did I miss
On 4/9/06, Tom Lane <[EMAIL PROTECTED]> wrote:
> Certainly. If the OS has readahead logic at all, it ought to think that
> a seqscan of a large table qualifies. Your arguments seem to question
> whether readahead is useful at all --- but they would apply *just as
> well* to an app doing its own r
Myron,
On 4/9/06 1:18 PM, "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote:
> I am working with Solaris on SPARC almost exclusively and I believe Josh said
> that Sun was the one who found the bursty behavior with scans. Has it been
> confirmed that this is the case on all/most platforms?
It was o
j: Re: [HACKERS] Support Parallel Query Execution in Executor
Date: Sun Apr 9, 2006 11:48 am
Size: 1K
To: Gregory Maxwell <[EMAIL PROTECTED]>
cc: pgsql-hackers@postgresql.org
"Gregory Maxwell" <[EMAIL PROTECTED]> writes:
> On 4/9/06, Tom Lane <[EMAIL PROTECTED]>
Jonah H. Harris wrote:
> On 4/9/06, Tom Lane <[EMAIL PROTECTED]> wrote:
> > Oh? Anything much above the level of bufmgr is going to be dependent on
> > relcache, syscache, etc, and those are all definitely database-specific.
> > You can't just retarget a backend to operate in another database, at
On 4/9/06, Tom Lane <[EMAIL PROTECTED]> wrote:
> Oh? Anything much above the level of bufmgr is going to be dependent on
> relcache, syscache, etc, and those are all definitely database-specific.
> You can't just retarget a backend to operate in another database, at
> least not without major chang
"Jonah H. Harris" <[EMAIL PROTECTED]> writes:
> The reader-like processes or "execution agents", whatever you want to
> call them, are limited to the postgres database only because they've
> been coded that way... but there's nothing that actually restricts
> them from being used for any database.
On 4/9/06, Tom Lane <[EMAIL PROTECTED]> wrote:
> That's not remotely the same thing: a backend is a very different animal
> from a bgwriter.
Yes, I know. Had I known it would've been picked apart, I would've
described it better.
> In particular, bgwriter (and bgreaders if we had 'em)
> aren't da
"Gregory Maxwell" <[EMAIL PROTECTED]> writes:
> On 4/9/06, Tom Lane <[EMAIL PROTECTED]> wrote:
>> So before we go inventing complicated bits of code with lots of added
>> overhead, we should first find out exactly why the system doesn't
>> already work the way it's supposed to.
> But is that reall
"Jonah H. Harris" <[EMAIL PROTECTED]> writes:
> On 4/9/06, Tom Lane <[EMAIL PROTECTED]> wrote:
>> I was considering a variant idea in the shower this morning:suppose
>> that we invent one or more "background reader" processes that have
>> basically the same infrastructure as the background writer,
On 4/9/06, Tom Lane <[EMAIL PROTECTED]> wrote:
> "Gregory Maxwell" <[EMAIL PROTECTED]> writes:
> > For example, one case made in this thread involved bursty performance
> > with seqscans presumably because the I/O was stalling while processing
> > was being performed.
>
> Actually, the question tha
On Apr 9, 2006, at 9:26 AM, Martijn van Oosterhout wrote:
On Sun, Apr 09, 2006 at 08:23:36AM -0700, Myron Scott wrote:
This is the part I'm curious about - is this using the
shared_buffers region
in a circular buffer fashion to store pre-fetched pages?
Yes. That is basically what the slave
On 4/9/06, Tom Lane <[EMAIL PROTECTED]> wrote:
> I was considering a variant idea in the shower this morning:suppose
> that we invent one or more "background reader" processes that have
> basically the same infrastructure as the background writer, but have
> the responsibility of causing buffer rea
"Gregory Maxwell" <[EMAIL PROTECTED]> writes:
> For example, one case made in this thread involved bursty performance
> with seqscans presumably because the I/O was stalling while processing
> was being performed.
Actually, the question that that raised in my mind is "why isn't the
kernel doing re
Myron Scott <[EMAIL PROTECTED]> writes:
> Gregory Maxwell wrote:
>> There are other cases where it is useful to perform parallel I/O
>> without parallel processing..
> I have done some testing more along these lines with an old fork of
> postgres code (2001). In my tests, I used a thread to deleg
On Sun, Apr 09, 2006 at 08:23:36AM -0700, Myron Scott wrote:
> >This is the part I'm curious about - is this using the
> >shared_buffers region
> >in a circular buffer fashion to store pre-fetched pages?
>
> Yes. That is basically what the slave thread is trying to do. As
> well as weed out
On Apr 8, 2006, at 10:29 PM, Luke Lonergan wrote:
Myron,
First, this sounds really good!
On 4/8/06 9:54 PM, "Myron Scott" <[EMAIL PROTECTED]> wrote:
I added a little hack to the buffer
code to force
pages read into the buffer to stay at the back of the free buffer
list
until the master
"Tom Lane" <[EMAIL PROTECTED]> wrote
>
> This is exactly the bit of optimism I was questioning. We've already
> been sweating blood trying to reduce multiprocessor contention on data
> structures in which collisions ought to be avoidable (ie, buffer arrays
> where you hope not everyone is hitting
Ühel kenal päeval, L, 2006-04-08 kell 18:47, kirjutas Luke Lonergan:
> I agree - there is a real continuing need for overlapped scan and execution,
> though I'm not sure quite how to get it efficiently using inter-process
> communication. The AIO interface is nicely proven at this point, but to
>
Myron,
First, this sounds really good!
On 4/8/06 9:54 PM, "Myron Scott" <[EMAIL PROTECTED]> wrote:
> I added a little hack to the buffer
> code to force
> pages read into the buffer to stay at the back of the free buffer list
> until the master
> thread has had a chance to use it.
This is the
Gregory Maxwell wrote:
We should consider true parallel execution and overlapping execution
with I/O as distinct cases.
For example, one case made in this thread involved bursty performance
with seqscans presumably because the I/O was stalling while processing
was being performed. In general th
Greg,
On 4/8/06 5:43 PM, "Gregory Maxwell" <[EMAIL PROTECTED]> wrote:
> For example, one case made in this thread involved bursty performance
> with seqscans presumably because the I/O was stalling while processing
> was being performed. In general this can be avoided without parallel
> executio
On 4/8/06, Tom Lane <[EMAIL PROTECTED]> wrote:
> This is exactly the bit of optimism I was questioning. We've already
> been sweating blood trying to reduce multiprocessor contention on data
> structures in which collisions ought to be avoidable (ie, buffer arrays
> where you hope not everyone is
On 4/8/06, Tom Lane <[EMAIL PROTECTED]> wrote:
> passing large volumes of data between different processes is going
> to incur quite a lot of locking overhead, pipeline stalls for cache line
> transfers, etc, etc, because heavy contention for the transfer buffer is
> simply not going to be avoidabl
"Jonah H. Harris" <[EMAIL PROTECTED]> writes:
> On 4/8/06, Markus Schiltknecht <[EMAIL PROTECTED]> wrote:
>> Ehm.. which additional I/O cost? Or do you count inter-process
>> communication to I/O?
> Inter-process will add a minimal amount, but if it's done correctly,
> you'll still end up ahead.
On 4/8/06, Markus Schiltknecht <[EMAIL PROTECTED]> wrote:
> ACK. As long as processes, signals and shared memory are used this could
> be as portable as PostgreSQL is now.
This is certainly the case.
> Ehm.. which additional I/O cost? Or do you count inter-process
> communication to I/O?
Inter-p
Hi,
On Sat, 2006-04-08 at 13:16 -0400, Jonah H. Harris wrote:
> On 4/8/06, Tom Lane <[EMAIL PROTECTED]> wrote:
> > ... but I'm failing to follow where it says that parallel processing
> > will fix that. All I can foresee in that direction is extra data
> > transfer costs, bought at the price of p
On 4/8/06, Tom Lane <[EMAIL PROTECTED]> wrote:
> ... but I'm failing to follow where it says that parallel processing
> will fix that. All I can foresee in that direction is extra data
> transfer costs, bought at the price of portability and locking headaches.
I don't think it's any less portable
Josh Berkus writes:
> Basically reading a large table off disk does this:
> read some table while not processing
> process in cpu while not reading
> read some more table while not processing
> process some more in cpu while not reading
> etc.
> resulting in an I/O througput graph that looks like
On 4/7/06, Josh Berkus wrote:
> So even on a single seq scan, parallel query
> execution would make a significant difference
> in performance, possibly as
> much as +75% on seq scans of large tables.
I've been looking at several commercial systems which employ dynamic
partitioning for sequential
Qingquing,
> First, I want to second Jonah's enthusiasm. This is very exciting!
Me, three! I didn't think this was ever going to come to Postgres absent
major corporate funding.
> This is really only a gut feeling for me (it can't be otherwise, since
> we can't yet test), but I think parallel
On 4/6/06, Qingqing Zhou <[EMAIL PROTECTED]> wrote:
>
> ""Jonah H. Harris"" <[EMAIL PROTECTED]> wrote
> >
> > Great work! I had looked into this a little bit and came to the same
> > ideas/problems you did, but none of them seemed insurmountable at all.
> > I'd be interested in working with you o
""Jonah H. Harris"" <[EMAIL PROTECTED]> wrote
>
> Great work! I had looked into this a little bit and came to the same
> ideas/problems you did, but none of them seemed insurmountable at all.
> I'd be interested in working with you on this if you'd like.
>
Yes, I am happy to work with anyone on
Hear hear ;-)
Regards,
Thomas Hallgren
Andrew Dunstan wrote:
Jonah H. Harris wrote:
On 4/6/06, Qingqing Zhou <[EMAIL PROTECTED]> wrote:
I have written some experimental code of doing master-slave seqscan in
PostgreSQL. During the work, I feel we had enough infrastructure to
support
parall
Jonah H. Harris wrote:
On 4/6/06, Qingqing Zhou <[EMAIL PROTECTED]> wrote:
I have written some experimental code of doing master-slave seqscan in
PostgreSQL. During the work, I feel we had enough infrastructure to support
parallel query execution.
Great work! I had looked into this a
On 4/6/06, Qingqing Zhou <[EMAIL PROTECTED]> wrote:
> I have written some experimental code of doing master-slave seqscan in
> PostgreSQL. During the work, I feel we had enough infrastructure to support
> parallel query execution.
Great work! I had looked into this a little bit and came to the sa
On Thu, Apr 06, 2006 at 06:28:33PM +0800, Qingqing Zhou wrote:
> I have written some experimental code of doing master-slave seqscan in
> PostgreSQL. During the work, I feel we had enough infrastructure to support
> parallel query execution.
Good work. One question though, temporary tables. Curren
I have written some experimental code of doing master-slave seqscan in
PostgreSQL. During the work, I feel we had enough infrastructure to support
parallel query execution.
What I did is adding a new node PARA and plug it above the node that we want
to execute in parallel. In this stage, a PARA no
67 matches
Mail list logo
