On Mon, 2007-12-03 at 15:50 -0800, Joshua D. Drake wrote:
> >
> > Note also some of the more popular pg support companies also offer
> > personal training, even if it isn't advertised. HTH.
>
> Right, I believe some even offer (besides CMD) custom training.
> 2ndQuandrant (sp?) for example recent
I have a question about how Postgres makes use of RAID arrays for
performance, because we are considering buying a 12-disc array for
performance reasons. I'm interested in how the performance scales with the
number of discs in the array.
Now, I know that for an OLTP workload (in other words, lots
"Matthew" <[EMAIL PROTECTED]> writes:
> Does Postgres issue requests to each random access in turn, waiting for
> each one to complete before issuing the next request (in which case the
> performance will not exceed that of a single disc), or does it use some
> clever asynchronous access method to
On Tue, 4 Dec 2007, Gregory Stark wrote:
> "Matthew" <[EMAIL PROTECTED]> writes:
>
> > Does Postgres issue requests to each random access in turn, waiting for
> > each one to complete before issuing the next request (in which case the
> > performance will not exceed that of a single disc), or does
On Sat, 1 Dec 2007, Jonah H. Harris wrote:
> I believe the threads you're talking about were related to scanning,
> not parallel query. Though, when Qingqing and I were discussing
> parallel query a little over a year ago, I do seem to recall several
> uninformed opinions stating that sequential s
Matthew wrote:
On Tue, 4 Dec 2007, Gregory Stark wrote:
"Matthew" <[EMAIL PROTECTED]> writes
Does Postgres issue requests to each random access in turn, waiting for
each one to complete before issuing the next request (in which case the
performance will not exceed that of a single disc), or
Matthew wrote:
On Tue, 4 Dec 2007, Mark Mielke wrote:
The bitmap scan method does ordered reads of the table, which can
partially take advantage of sequential reads. Not sure whether bitmap
scan is optimal for your situation or whether your situation would allow
this to be taken advantage of.
On Tue, 4 Dec 2007, Mark Mielke wrote:
> The bitmap scan method does ordered reads of the table, which can
> partially take advantage of sequential reads. Not sure whether bitmap
> scan is optimal for your situation or whether your situation would allow
> this to be taken advantage of.
Bitmap scan
"Mark Mielke" <[EMAIL PROTECTED]> writes:
> Matthew wrote:
>
>> I don't think you would have to create a more intelligent table scanning
>> algorithm. What you would need to do is take the results of the index,
>> convert that to a list of page fetches, then pass that list to the OS as
>> an asyn
On Tue, 4 Dec 2007, Mark Mielke wrote:
> The disk head has less theoretical distance to travel if always moving
> in a single direction instead of randomly seeking back and forth.
True... and false. The head can move pretty quickly, and it also has
rotational latency and settling time to deal with
On Tue, 4 Dec 2007, Gregory Stark wrote:
> Also, it's true, you need to preread more than 12 blocks to handle a 12-disk
> raid. My offhand combinatorics analysis seems to indicate you would expect to
> need to n(n-1)/2 blocks on average before you've hit all the blocks. There's
> little penalty to
Fwiw, what made you bring up this topic now? You're the second person in about
two days to bring up precisely this issue and it was an issue I had been
planning to bring up on -hackers as it was.
"Matthew" <[EMAIL PROTECTED]> writes:
> Kind of. The system cache is just a method to make it simple
Matthew wrote:
On Tue, 4 Dec 2007, Gregory Stark wrote:
Also, it's true, you need to preread more than 12 blocks to handle a 12-disk
raid. My offhand combinatorics analysis seems to indicate you would expect to
need to n(n-1)/2 blocks on average before you've hit all the blocks. There's
littl
On Tue, 4 Dec 2007, Mark Mielke wrote:
> > The larger the set of requests, the closer the performance will scale to
> > the number of discs
>
> This assumes that you can know which pages to fetch ahead of time -
> which you do not except for sequential read of a single table.
There are circumstanc
Hi,
Postgres 8.2.4 is not using the right plan for different values.
From the below queries listing.addressvaluation table has 19million
records , the other table listing.valuationchangeperiod is just lookup
table with 3 records.
If you can see the explain plans for the statements
James Mansion wrote:
Mark Mielke wrote:
This assumes that you can know which pages to fetch ahead of time -
which you do not except for sequential read of a single table.
Why doesn't it help to issue IO ahead-of-time requests when you are
scanning an index? You can read-ahead
in index pages, a
Matthew wrote:
On Tue, 4 Dec 2007, Mark Mielke wrote:
The larger the set of requests, the closer the performance will scale to
the number of discs
This assumes that you can know which pages to fetch ahead of time -
which you do not except for sequential read of a single table.
T
[EMAIL PROTECTED] wrote:
So, if you hand requests one by one to the disc, it will almost always be
faster to order them. On the other hand, if you hand a huge long list of
requests to a decent SCSI or SATA-NCQ disc in one go, it will reorder the
reads itself, and it will do it much better than yo
Mark Mielke wrote:
This assumes that you can know which pages to fetch ahead of time -
which you do not except for sequential read of a single table.
Why doesn't it help to issue IO ahead-of-time requests when you are
scanning an index? You can read-ahead
in index pages, and submit requests f
On Tue, 4 Dec 2007, Gregory Stark wrote:
> Fwiw, what made you bring up this topic now? You're the second person in about
> two days to bring up precisely this issue and it was an issue I had been
> planning to bring up on -hackers as it was.
I only just joined the performance mailing list to talk
Matthew wrote:
On Tue, 4 Dec 2007, Gregory Stark wrote:
Fwiw, what made you bring up this topic now? You're the second person in about
two days to bring up precisely this issue and it was an issue I had been
planning to bring up on -hackers as it was.
I only just joined the performance
Pallav Kalva <[EMAIL PROTECTED]> writes:
>why does it have different plans for different values
Because the values occur different numbers of times (or so it thinks
anyway). If the rowcount estimates are far from reality, perhaps
increasing the statistics target would help. However, since yo
On Tue, 4 Dec 2007, Mark Mielke wrote:
> So much excitement and zeal - refreshing to see. And yet, no numbers! :-)
What sort of numbers did you want to see?
> You describe a new asynchronous I/O system to map integers to Java
> objects above. Why would you write this? Have you tried BerkeleyDB or
Tom Lane wrote:
Pallav Kalva <[EMAIL PROTECTED]> writes:
why does it have different plans for different values
Because the values occur different numbers of times (or so it thinks
anyway). If the rowcount estimates are far from reality, perhaps
increasing the statistics target woul
"Matthew" <[EMAIL PROTECTED]> writes:
> On Tue, 4 Dec 2007, Mark Mielke wrote:
>> So much excitement and zeal - refreshing to see. And yet, no numbers! :-)
>
> What sort of numbers did you want to see?
FWIW I posted some numbers from a synthetic case to pgsql-hackers
http://archives.postgresql.o
Hi all,
I have a large database with e-mail meta-data (no bodies) for over 100
million messages. I am running PostgreSQL 8.2.4 on a server with 2GB of
RAM (shared_buffers = 240MB, temp_buffers = 128MB, work_mem = 256MB,
maintenance_work_mem = 256MB). I have the data split in two separate
tables,
Julian Mehnle wrote:
> I have a large database with e-mail meta-data (no bodies) for over 100
> million messages. I am running PostgreSQL 8.2.4 on a server with 2GB
> of RAM (shared_buffers = 240MB, temp_buffers = 128MB, work_mem = 256MB,
> maintenance_work_mem = 256MB). I have the data split in
Mark Mielke wrote:
At a minimum, this breaks your query into: 1) Preload all the index
pages you will need
Isn't this fairly predictable - the planner has chosen the index so it
will be operating
on a bounded subset.
, 2) Scan the index pages you needed
Yes, and AIO helps when you can scan the
Gregory Stark wrote:
> There are a few things going on here.
>
> 1) The optimizer can't build a plan which ignores those partitions
> because the statistics are just approximations. You could insert into
> one of them at any time and the statistics won't update immediately. If
> you have a partitio
"Julian Mehnle" <[EMAIL PROTECTED]> writes:
> However, if I restrict the query to just the partitions that actually do
> have data in them ...
There are a few things going on here.
1) The optimizer can't build a plan which ignores those partitions because the
statistics are just approximations.
James Mansion wrote:
Mark Mielke wrote:
At a minimum, this breaks your query into: 1) Preload all the index
pages you will need
Isn't this fairly predictable - the planner has chosen the index so it
will be operating
on a bounded subset.
What is the bounded subset? It is bounded by the value.
Mark Mielke wrote:
PostgreSQL or the kernel should already have the hottest pages in
memory, so the value of doing async I/O is very likely the cooler
pages that are unique to the query. We don't know what the cooler
pages are until we follow three tree down.
I'm assuming that at the time we
"Julian Mehnle" <[EMAIL PROTECTED]> writes:
> I actually do have constraints on all the partitions, e.g. for week 34:
>
> Check constraints [for email_2007_week34]:
> "email_2007_week34_ts_check" CHECK (ts >= '2007-08-20
> 00:00:00'::timestamp without time zone AND ts < '2007-08-27
> 00:00
James Mansion wrote:
Mark Mielke wrote:
PostgreSQL or the kernel should already have the hottest pages in
memory, so the value of doing async I/O is very likely the cooler
pages that are unique to the query. We don't know what the cooler
pages are until we follow three tree down.
I'm assumin
Gregory Stark wrote:
> "Julian Mehnle" <[EMAIL PROTECTED]> writes:
> > I actually do have constraints on all the partitions, e.g. for week
> > 34: [...]
> >
> > Shouldn't this be enough to give the query planner a clue that it
> > only has to join the "email" and "email_extras" tables' partitions
>
On Tue, 4 Dec 2007, Mark Mielke wrote:
This is bikeshed land, right?
I am only interested by juicy projects that have a hope of success. This
subject does interest me - I am hoping my devil's advocate participation
encourages people to seek a practical implementation that will benefit me.
Na
36 matches
Mail list logo
