Re: [GENERAL] effective_io_concurrency increasing

[Peter Geoghegan]

On Mon, Jun 19, 2017 at 4:51 PM, Peter Geoghegan  wrote:
> This would make only the first lookup for each distinct value on the
> outer side actually do an index scan on the inner side. I can imagine
> the optimization saving certain queries from consuming a lot of memory
> bandwidth, as well as saving them from pinning and locking the same
> buffers repeatedly.

Apparently this is sometimes called block nested loop join, and MySQL
has had it for a while now:

https://en.wikipedia.org/wiki/Block_nested_loop

It doesn't necessarily require that the outer side input be sorted,
because you might end up using a hash table, etc.

-- 
Peter Geoghegan


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] effective_io_concurrency increasing

[Peter Geoghegan]

On Mon, Jun 19, 2017 at 4:35 PM, Andres Freund  wrote:
>> I think that this is the way index scan prefetch is normally
>> implemented. Index scans will on average have a much more random
>> access pattern than what is typical for bitmap heap scans, making this
>> optimization more compelling, so hopefully someone will get around to
>> this.
>
> I think for index based merge and nestloop joins, it'd be hugely
> beneficial to do prefetching on the index, but more importantly on the
> heap level.  Not entirely trivial to do however.

Speaking of nestloop join, and on a similar note, we could do some
caching on the inner side of a nestloop join.

We already track if the outer side access path of a nestloop join
preserves sort order within the optimizer. It might not be that hard
to teach the optimizer to generate a plan where, when we know that
this has happened, and we know that the outer side is not unique, the
final plan hints to the executor to opportunistically cache every
lookup on the inner side.

This would make only the first lookup for each distinct value on the
outer side actually do an index scan on the inner side. I can imagine
the optimization saving certain queries from consuming a lot of memory
bandwidth, as well as saving them from pinning and locking the same
buffers repeatedly.

-- 
Peter Geoghegan


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] effective_io_concurrency increasing

[Andres Freund]

On 2017-06-19 15:21:20 -0700, Peter Geoghegan wrote:
> On Mon, Jun 19, 2017 at 8:36 AM, Jeff Janes  wrote:
> > Unfortunately, it is only implemented in very narrow circumstances.  You
> > have to be doing bitmap index scans of many widely scattered rows to make it
> > useful.  I don't think that this is all that common of a situation.  The
> > problem is that at every point in the scan, it has to be possible to know
> > what data block it is going to want N iterations in the future, so you can
> > inform the kernel to pre-fetch it.  That is only easy to know for bitmap
> > scans.
> 
> I think that you could prefetch in index scans by using the
> pointers/downlinks in the immediate parent page of the leaf page that
> the index scan currently pins. The sibling pointer in the leaf itself
> is no good for this, because there is only one block to prefetch
> available at a time.
> 
> I think that this is the way index scan prefetch is normally
> implemented. Index scans will on average have a much more random
> access pattern than what is typical for bitmap heap scans, making this
> optimization more compelling, so hopefully someone will get around to
> this.

I think for index based merge and nestloop joins, it'd be hugely
beneficial to do prefetching on the index, but more importantly on the
heap level.  Not entirely trivial to do however.

- Andres


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] effective_io_concurrency increasing

[Peter Geoghegan]

On Mon, Jun 19, 2017 at 3:25 PM, Alvaro Herrera
 wrote:
> Surely you could prefetch all the heap pages pointed to by index items
> in the current leaf index page ...

I'm sure that you could do that too. I'm not sure how valuable each
prefetching optimization is.

I can imagine prefetching heap pages mattering a lot less for a
primary key index, where there is a strong preexisting correlation
between physical and logical order, while also mattering a lot more
than what I describe in other cases. I suppose that you need both.

-- 
Peter Geoghegan


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] effective_io_concurrency increasing

[Alvaro Herrera]

Peter Geoghegan wrote:
> On Mon, Jun 19, 2017 at 8:36 AM, Jeff Janes  wrote:
> > Unfortunately, it is only implemented in very narrow circumstances.  You
> > have to be doing bitmap index scans of many widely scattered rows to make it
> > useful.  I don't think that this is all that common of a situation.  The
> > problem is that at every point in the scan, it has to be possible to know
> > what data block it is going to want N iterations in the future, so you can
> > inform the kernel to pre-fetch it.  That is only easy to know for bitmap
> > scans.
> 
> I think that you could prefetch in index scans by using the
> pointers/downlinks in the immediate parent page of the leaf page that
> the index scan currently pins. The sibling pointer in the leaf itself
> is no good for this, because there is only one block to prefetch
> available at a time.

Surely you could prefetch all the heap pages pointed to by index items
in the current leaf index page ...

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] effective_io_concurrency increasing

[Peter Geoghegan]

On Mon, Jun 19, 2017 at 8:36 AM, Jeff Janes  wrote:
> Unfortunately, it is only implemented in very narrow circumstances.  You
> have to be doing bitmap index scans of many widely scattered rows to make it
> useful.  I don't think that this is all that common of a situation.  The
> problem is that at every point in the scan, it has to be possible to know
> what data block it is going to want N iterations in the future, so you can
> inform the kernel to pre-fetch it.  That is only easy to know for bitmap
> scans.

I think that you could prefetch in index scans by using the
pointers/downlinks in the immediate parent page of the leaf page that
the index scan currently pins. The sibling pointer in the leaf itself
is no good for this, because there is only one block to prefetch
available at a time.

I think that this is the way index scan prefetch is normally
implemented. Index scans will on average have a much more random
access pattern than what is typical for bitmap heap scans, making this
optimization more compelling, so hopefully someone will get around to
this.

-- 
Peter Geoghegan


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] effective_io_concurrency increasing

[Bruce Momjian]

On Mon, Jun 19, 2017 at 10:49:59AM -0500, Merlin Moncure wrote:
> On Mon, Jun 19, 2017 at 10:36 AM, Jeff Janes  wrote:
> > If you have a RAID, set it to the number of spindles in your RAID and forget
> > it. It is usually one of the less interesting knobs to play with.  (Unless
> > your usage pattern of the database is unusual and exact fits the above
> > pattern.)
> 
> Isn't that advice obsolete in a SSD world though?  I was able to show
> values up to 256 for a single device provided measurable gains for a
> single S3500.  It's true though that the class of queries that this
> would help is pretty narrow.

Our developer docs are much clearer:


https://www.postgresql.org/docs/10/static/runtime-config-resource.html#runtime-config-resource-disk

For magnetic drives, a good starting point for this setting is the
number of separate drives comprising a RAID 0 stripe or RAID 1 mirror
being used for the database. (For RAID 5 the parity drive should not be
counted.) However, if the database is often busy with multiple queries
issued in concurrent sessions, lower values may be sufficient to keep
the disk array busy. A value higher than needed to keep the disks busy
will only result in extra CPU overhead. SSDs and other memory-based
storage can often process many concurrent requests, so the best value
might be in the hundreds.

I didn't backpatch this change since the original docs were not
incorrect.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+  Ancient Roman grave inscription +


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] effective_io_concurrency increasing

[Jeff Janes]

On Mon, Jun 19, 2017 at 8:49 AM, Merlin Moncure  wrote:

> On Mon, Jun 19, 2017 at 10:36 AM, Jeff Janes  wrote:
> > If you have a RAID, set it to the number of spindles in your RAID and
> forget
> > it. It is usually one of the less interesting knobs to play with.
> (Unless
> > your usage pattern of the database is unusual and exact fits the above
> > pattern.)
>
> Isn't that advice obsolete in a SSD world though?  I was able to show
> values up to 256 for a single device provided measurable gains for a
> single S3500.  It's true though that the class of queries that this
> would help is pretty narrow.


I don't think it is obsolete, you just have to be creative with how you
interpret 'spindle' :)

With a single laptop hard-drive, I could get improvements of about 2 fold
by setting it to very high numbers, like 50 or 80. By giving the hard drive
the option of dozens of different possible sectors to read next, it could
minimize head-seek.  But that is with just one query running at a time.
With multiple queries all running simultaneously all trying to take
advantage of this, performance gains quickly fell apart.  I would expect
the SSD situation to be similar to that, where the improvements are
measurable but also fragile, but I haven't tested it.

Cheers,

Jeff

Re: [GENERAL] effective_io_concurrency increasing

[Merlin Moncure]

On Mon, Jun 19, 2017 at 10:36 AM, Jeff Janes  wrote:
> If you have a RAID, set it to the number of spindles in your RAID and forget
> it. It is usually one of the less interesting knobs to play with.  (Unless
> your usage pattern of the database is unusual and exact fits the above
> pattern.)

Isn't that advice obsolete in a SSD world though?  I was able to show
values up to 256 for a single device provided measurable gains for a
single S3500.  It's true though that the class of queries that this
would help is pretty narrow.

merlin


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] effective_io_concurrency increasing

[Jeff Janes]

On Sun, Jun 18, 2017 at 7:09 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Sun, Jun 18, 2017 at 6:02 PM, Patrick B 
> wrote:
>
>> Hi guys.
>>
>> I just wanna understand the effective_io_concurrency value better.
>>
>> My current Master database server has 16 vCPUS and I use
>> ​​
>>  effective_io_concurrency = 0.
>>
>
> ​It seems as though the number of virtual CPUs little to no bearing on
> whether, or to what value, you should set this parameter.  Obviously with
> only one CPU parallelism wouldn't be possible (I'm assuming a single query
> does not make multiple parallel requests for data)
>

Ah, but it does.  That is exactly what this parameter is for.

Unfortunately, it is only implemented in very narrow circumstances.  You
have to be doing bitmap index scans of many widely scattered rows to make
it useful.  I don't think that this is all that common of a situation.  The
problem is that at every point in the scan, it has to be possible to know
what data block it is going to want N iterations in the future, so you can
inform the kernel to pre-fetch it.  That is only easy to know for bitmap
scans.

If you have a RAID, set it to the number of spindles in your RAID and
forget it. It is usually one of the less interesting knobs to play with.
 (Unless your usage pattern of the database is unusual and exact fits the
above pattern.)


Cheers,

Jeff

Re: [GENERAL] effective_io_concurrency increasing

[Andreas Kretschmer]

Am 19.06.2017 um 03:02 schrieb Patrick B:

Hi guys.

I just wanna understand the effective_io_concurrency value better.

My current Master database server has 16 vCPUS and I 
use effective_io_concurrency = 0.


What can be the benefits of increasing that number? Also, do you guys 
have any recommendations?



as far as i know, at the moment only bitmap-index-scans would benefit 
from higher values. You can try 16 or 32 as starting point.

(if you have a proper io-controller with cache)


(it has nothing to do with parallel execution of queries)


Regards, Andreas

--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] effective_io_concurrency increasing

[David G. Johnston]

On Sun, Jun 18, 2017 at 6:02 PM, Patrick B  wrote:

> Hi guys.
>
> I just wanna understand the effective_io_concurrency value better.
>
> My current Master database server has 16 vCPUS and I use
> ​​
>  effective_io_concurrency = 0.
>

​It seems as though the number of virtual CPUs little to no bearing on
whether, or to what value, you should set this parameter.  Obviously with
only one CPU parallelism wouldn't be possible (I'm assuming a single query
does not make multiple parallel requests for data) but the value seems to
strictly describe a characteristic the I/O subsystem.  Whether you can
fully leverage a properly set large value is another matter.

​As general advice, even you are using a soon to be obsolete (or any
non-current really) version of PostgreSQL when you are learning about a new
concept checking the most recent docs can be helpful.  Generally only bugs
in the docs get back-patched but a number of doc contributions are not bug
related but helpful none-the-less.

https://www.postgresql.org/docs/devel/static/runtime-config-resource.html​

​In short, if you want any good advice you will need to figure out the
specifics of your I/O subsystem (non-volatile memory and any associated
hardware), and share that with the list.​ Lacking rules-of-thumb learning
how to test your system and measure changes would help get you to the end
goal.  Sadly not a skill I've really picked up as of yet.

​David J.​

Re: [GENERAL] effective_io_concurrency increasing

[Melvin Davidson]

*As per the docs:*



*1. This is dependent on whether or not you are using a RAID disk,2. "Some
experimentation may be needed to find the best value"*

*IOW, there is no general recommendation.*

On Sun, Jun 18, 2017 at 9:24 PM, Patrick B  wrote:

>
>
> 2017-06-19 13:19 GMT+12:00 Melvin Davidson :
>
>>
>>
>> On Sun, Jun 18, 2017 at 9:02 PM, Patrick B 
>> wrote:
>>
>>> Hi guys.
>>>
>>> I just wanna understand the effective_io_concurrency value better.
>>>
>>> My current Master database server has 16 vCPUS and I
>>> use effective_io_concurrency = 0.
>>>
>>> What can be the benefits of increasing that number? Also, do you guys
>>> have any recommendations?
>>>
>>> I'm using PG 9.2 and the official doc does not say much about which
>>> value you should use.
>>>
>>> If I put it to 1, does it mean I can have a query spread into 1
>>> processor?
>>>
>>> Thanks
>>> P
>>>
>>
>>
>>
>> *Perhaps you should read the
>> doc.https://www.postgresql.org/docs/9.2/static/runtime-config-resource.html
>> *
>> 18.4.6. Asynchronous Behavior *effective_io_concurrency (integer)*
>>
>
>
> I've done that! But I'm looking for some personal experiences and
> suggestions!!
>



-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] effective_io_concurrency increasing

[Patrick B]

2017-06-19 13:19 GMT+12:00 Melvin Davidson :

>
>
> On Sun, Jun 18, 2017 at 9:02 PM, Patrick B 
> wrote:
>
>> Hi guys.
>>
>> I just wanna understand the effective_io_concurrency value better.
>>
>> My current Master database server has 16 vCPUS and I
>> use effective_io_concurrency = 0.
>>
>> What can be the benefits of increasing that number? Also, do you guys
>> have any recommendations?
>>
>> I'm using PG 9.2 and the official doc does not say much about which value
>> you should use.
>>
>> If I put it to 1, does it mean I can have a query spread into 1 processor?
>>
>> Thanks
>> P
>>
>
>
>
> *Perhaps you should read the
> doc.https://www.postgresql.org/docs/9.2/static/runtime-config-resource.html
> *
> 18.4.6. Asynchronous Behavior *effective_io_concurrency (integer)*
>


I've done that! But I'm looking for some personal experiences and
suggestions!!

Re: [GENERAL] effective_io_concurrency increasing

[Melvin Davidson]

On Sun, Jun 18, 2017 at 9:02 PM, Patrick B  wrote:

> Hi guys.
>
> I just wanna understand the effective_io_concurrency value better.
>
> My current Master database server has 16 vCPUS and I
> use effective_io_concurrency = 0.
>
> What can be the benefits of increasing that number? Also, do you guys have
> any recommendations?
>
> I'm using PG 9.2 and the official doc does not say much about which value
> you should use.
>
> If I put it to 1, does it mean I can have a query spread into 1 processor?
>
> Thanks
> P
>



*Perhaps you should read the
doc.https://www.postgresql.org/docs/9.2/static/runtime-config-resource.html
*
18.4.6. Asynchronous Behavior *effective_io_concurrency (integer)*
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

[GENERAL] effective_io_concurrency increasing

[Patrick B]

Hi guys.

I just wanna understand the effective_io_concurrency value better.

My current Master database server has 16 vCPUS and I
use effective_io_concurrency = 0.

What can be the benefits of increasing that number? Also, do you guys have
any recommendations?

I'm using PG 9.2 and the official doc does not say much about which value
you should use.

If I put it to 1, does it mean I can have a query spread into 1 processor?

Thanks
P

Re: [GENERAL] effective_io_concurrency with an SSD SAN?

[Samrat Revagade]

>Given a system with 32 cores, an SSD SAN with 48x drives, and 2x 8Gbps
>paths from the server to the SAN, what would be a good starting point
>to set effective_io_concurrency?  I currently have it set to 32, but I
>kind of feel like the right setting would be "2" since we have two
>paths.  We don't often saturate both links but it does happen from
>time to time.

One assumption about optimal value is that, it is usually the number of data
drives participating in the I/O system excluding the parity drives.
here is the article about setting it in Postgres Plus Advanced Server.
Have a look at it if you find it useful for normal PostgreSQL setup:
http://www.enterprisedb.com/docs/en/8.3R2/perf/Postgres_Plus_Advanced_Server_Performance_Guide-17.htm




-
Greetings,
Samrat Revagade,
NTT DATA OSS Center Pune, India.

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/effective-io-concurrency-with-an-SSD-SAN-tp5768029p5768570.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] effective_io_concurrency with an SSD SAN?

[Evan D. Hoffman]

Given a system with 32 cores, an SSD SAN with 48x drives, and 2x 8Gbps
paths from the server to the SAN, what would be a good starting point
to set effective_io_concurrency?  I currently have it set to 32, but I
kind of feel like the right setting would be "2" since we have two
paths.  We don't often saturate both links but it does happen from
time to time.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] effective_io_concurrency on Windows

[Merlin Moncure]

On Tue, Mar 26, 2013 at 3:35 AM, Bartosz Dmytrak  wrote:
> Hi all
> is it possible to introduce similar solution for Windows systems in future?
> I am aware it is not available because of lack of posix_fadvise function,
> but I believe there is a way to introduce this feature for Win systems.

Probably the best case scenario would be to make some library routines
that wrap the windows API and emulate posix_fadvise.  This may or may
not be possible, but win32 API does support asynchronous i/o so it
might be.

merlin


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] effective_io_concurrency on Windows

[Bartosz Dmytrak]

Hi all
is it possible to introduce similar solution for Windows systems in future?
I am aware it is not available because of lack of posix_fadvise function,
but I believe there is a way to introduce this feature for Win systems.


Regards,
Bartek

Re: [GENERAL] effective_io_concurrency

[Yves Weißig]

Thanks for the detailed answer and explanation.
I am curious if there is another way to force this behaviour. As a part
of my masters thesis I am trying to develop an index which heavily
relies on requesting blocks in parallel. Without knowing the code, do I
have to dive into it and change it manually or can I accomplish this any
other way?

Am 03.02.2011 17:15, schrieb Greg Smith:
> Yves Weißig wrote:
>> I was wondering if there is more information about this switch in the
>> configuration. Does it really work? Where in the source code can I
>> follow how it works? "sgmgr.c" seems to be an entry point, but where
>> exactly is it used?
>>   
> 
> Currently the code only kicks in when you're doing a Bitmap Heap Scan,
> which is really helpful for them, but of no help for any other type of
> query.  This style of heap scan already knows in advance exactly what
> blocks it needs from the database, and normally it just asks for them
> one at a time.  That can turn into a fair amount of random I/O, and it's
> done serially:  the next block isn't requested until the last one
> arrives.  What effective_io_concurrency does is advise the operating
> system of the next few blocks the database is going to ask for, before
> the actual read requests, in hopes that it might grab them if it happens
> to be nearby that area of the disk.e
> 
> I've only seen this feature work at all on Linux. It might work on BSD
> and Mac OS X systems, it certainly doesn't do anything on Solaris and
> Windows.
> 
> The basic idea is that you start with setting the value to the number of
> working drives in the disk array the database is on and see if I/O
> performance goes up and/or query speed drops afterwards. If it does you
> might try further increases beyond that even. As for why there isn't a
> better tuning guide than just those simple guidelines, it's not that
> easy to show a situation where the type of bitmap scan this parameter
> impacts is used on a generated data set, even though it's not that
> uncommon in real-world data. It's hard both to make generic suggestions
> here and to even demonstrate the feature at work.
> 
> Moving up the source code chain from smgr,
> src/backend/storage/buffer/bufmgr.c has PrefetchBuffer, and the one
> place the executor calls that is BitmapHeapNext inside
> src/backend/executor/nodeBitmapHeapscan.c
> 

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] effective_io_concurrency

[Greg Smith]

Yves Weißig wrote:

I was wondering if there is more information about this switch in the
configuration. Does it really work? Where in the source code can I
follow how it works? "sgmgr.c" seems to be an entry point, but where
exactly is it used?
  


Currently the code only kicks in when you're doing a Bitmap Heap Scan, 
which is really helpful for them, but of no help for any other type of 
query.  This style of heap scan already knows in advance exactly what 
blocks it needs from the database, and normally it just asks for them 
one at a time.  That can turn into a fair amount of random I/O, and it's 
done serially:  the next block isn't requested until the last one 
arrives.  What effective_io_concurrency does is advise the operating 
system of the next few blocks the database is going to ask for, before 
the actual read requests, in hopes that it might grab them if it happens 
to be nearby that area of the disk.e


I've only seen this feature work at all on Linux. It might work on BSD 
and Mac OS X systems, it certainly doesn't do anything on Solaris and 
Windows.


The basic idea is that you start with setting the value to the number of 
working drives in the disk array the database is on and see if I/O 
performance goes up and/or query speed drops afterwards. If it does you 
might try further increases beyond that even. As for why there isn't a 
better tuning guide than just those simple guidelines, it's not that 
easy to show a situation where the type of bitmap scan this parameter 
impacts is used on a generated data set, even though it's not that 
uncommon in real-world data. It's hard both to make generic suggestions 
here and to even demonstrate the feature at work.


Moving up the source code chain from smgr, 
src/backend/storage/buffer/bufmgr.c has PrefetchBuffer, and the one 
place the executor calls that is BitmapHeapNext inside 
src/backend/executor/nodeBitmapHeapscan.c


--
Greg Smith   2ndQuadrant USg...@2ndquadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] effective_io_concurrency

[Yves Weißig]

Hi pgsql-general group,

I was wondering if there is more information about this switch in the
configuration. Does it really work? Where in the source code can I
follow how it works? "sgmgr.c" seems to be an entry point, but where
exactly is it used?

Greets, Yves

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] effective_io_concurrency details

[Kenichiro Tanaka]

Hello.

>Is effective_io_concurrency working on WinXP sp2 ?
No.
"effective_io_concurrency"  works when an OS can use posix_fadvise().
But I think WinXP  doesn't have posix_fadvise().

>and what is the difference between effective_io_concurrency = 0
>and effective_io_concurrency = 1
If effective_io_concurrency = 0,PostgreSQL never uses PREFETCH.
On my environment, my PostgreSQL doesn't start up when 
effective_io_concurrency=1.

#My environment WinXP sp2 and PostgreSQL is 8.4.4, too.

What I referenced)
http://archives.postgresql.org/pgsql-hackers/2009-03/msg00911.php

http://doxygen.postgresql.org/bufmgr_8c-source.html#l00075
pgsql/src/backend/storage/buffer/bufmgr.c
00070 /*
00071  * How many buffers PrefetchBuffer callers should try to stay 
ahead of their

00072  * ReadBuffer calls by.  This is maintained by the assign hook for
00073  * effective_io_concurrency.  Zero means "never prefetch".
00074  */


Hello.

I have 2 questions:
Is effective_io_concurrency working on WinXP sp2 ?
and what is the difference between effective_io_concurrency = 0
and effective_io_concurrency = 1


Postgres 8.4.4

pasman

   



--

Kenichiro Tanaka
K.K.Ashisuto
http://www.ashisuto.co.jp/english/index.html



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] effective_io_concurrency details

[Cédric Villemain]

2010/5/26 pasman pasmański :
> Hello.
>
> I have 2 questions:
> Is effective_io_concurrency working on WinXP sp2 ?

no

> and what is the difference between effective_io_concurrency = 0
> and effective_io_concurrency = 1

0 disable prefetching.
1 allow a short prefetch window

>
>
> Postgres 8.4.4
> 
> pasman
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
Cédric Villemain   2ndQuadrant
http://2ndQuadrant.fr/ PostgreSQL : Expertise, Formation et Support

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] effective_io_concurrency details

[pasman pasmański]

Hello.

I have 2 questions:
Is effective_io_concurrency working on WinXP sp2 ?
and what is the difference between effective_io_concurrency = 0
and effective_io_concurrency = 1


Postgres 8.4.4

pasman

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general