On Thu, 18 Sep 2008, Greg Smith wrote:
It's just that our system is doing a lot of bitmap index scans at the
moment, and it'd help to be able to spread them across the 16 discs in the
RAID array. It's the bottleneck in our system at the moment.
If you have some specific bitmap index scan test
Matthew Wakeling [EMAIL PROTECTED] writes:
In order to improve the performance, I made the system look ahead in the
source, in groups of a thousand entries, so instead of running:
SELECT * FROM table WHERE field = 'something';
a thousand times, we now run:
SELECT * FROM table WHERE field IN
On Fri, 19 Sep 2008, Tom Lane wrote:
Your example shows the IN-list as being sorted, but I wonder whether you
actually are sorting the items in practice? If not, you might try that
to improve locality of access to the index.
Well, like I said, we generally don't have the luxury of dictating
Matthew Wakeling [EMAIL PROTECTED] writes:
In order to improve the performance, I made the system look ahead in the
source, in groups of a thousand entries, so instead of running:
SELECT * FROM table WHERE field = 'something';
a thousand times, we now run:
SELECT * FROM table WHERE field IN
On Tue, 29 Jan 2008, Gregory Stark wrote:
So, this is FYI, and also an added encouragement to implement fadvise
prefetching in some form or another. How's that going by the way?
I have a patch which implements it for the low hanging fruit of bitmap index
scans. it does it using an extra trip
On Thu, 18 Sep 2008, Matthew Wakeling wrote:
On Tue, 29 Jan 2008, Gregory Stark wrote:
I have a patch which implements it for the low hanging fruit of bitmap
index scans. it does it using an extra trip through the buffer manager
which is the least invasive approach but not necessarily the
On Tue, Jan 29, 2008 at 9:52 AM, in message
[EMAIL PROTECTED], Gregory Stark [EMAIL PROTECTED]
wrote:
I got this from a back-of-the-envelope calculation which now that I'm trying
to reproduce it seems to be wrong. Previously I thought it was n(n+1)/2 or
about n^2/2. So at 16 I would have
On Tue, Jan 29, 2008 at 10:45 AM, in message
[EMAIL PROTECTED], Gregory Stark [EMAIL PROTECTED] wrote:
Well consider when you've reached n-1 drives; the expected number of requests
before you hit the 1 idle drive remaining out of n would be n requests. When
you're at n-2 the expected number
Kevin Grittner [EMAIL PROTECTED] writes:
On Tue, Jan 29, 2008 at 9:52 AM, in message
[EMAIL PROTECTED], Gregory Stark [EMAIL PROTECTED]
wrote:
I got this from a back-of-the-envelope calculation which now that I'm trying
to reproduce it seems to be wrong. Previously I thought it was
On Tue, 29 Jan 2008, Gregory Stark wrote:
This was with 8192 random requests of size 8192 bytes from an 80GB test file.
Unsorted requests ranged from 1.8 MB/s with no prefetching to 28MB/s with lots
of prefetching. Sorted requests went from 2.4MB/s to 38MB/s. That's almost
exactly 16x
Matthew [EMAIL PROTECTED] writes:
On Tue, 4 Dec 2007, Gregory Stark wrote:
FWIW I posted some numbers from a synthetic case to pgsql-hackers
http://archives.postgresql.org/pgsql-hackers/2007-12/msg00088.php
...
This was with 8192 random requests of size 8192 bytes from an 80GB test file.
Mark Mielke wrote:
Asynchronous I/O is no more a magic bullet than threading. It requires a
lot of work to get it right, and if one gets it wrong, it can be slower
than the regular I/O or single threaded scenarios. Both look sexy on
paper, neither may be the solution to your problem. Or
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,
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 send a
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 it use some
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
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 asynchronous
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 simpler to
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
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 circumstances
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,
[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
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
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
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
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
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
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
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
33 matches
Mail list logo
