pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedintel s3500 -- hot stuff
8+ messages / 4 participants
[nested] [flat]
* intel s3500 -- hot stuff
@ 2014-11-05 17:40 Merlin Moncure <mmoncure@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Merlin Moncure @ 2014-11-05 17:40 UTC (permalink / raw)
To: pgsql-performance
I recently sourced a 300gb intel s3500 ssd to do some performance
testing. I didn't see a lot of results on the web so I thought I'd
post some numbers. Testing machine is my workstation crapbox with 4
cores and 8GB ram (of which about 4 is usable by the ~ 50gb database).
The drive cost 260$ at newegg (sub 1$/gb) and is write durable.
Single thread 'select only' results are pretty stable 2200 tps isn't
bad. of particular note is the sub millisecond latency of the read.
Per iostat I'm getting ~ 55mb/sec read off the device and around 4100
device tps:
transaction type: SELECT only
scaling factor: 3000
query mode: simple
number of clients: 1
number of threads: 1
duration: 10 s
number of transactions actually processed: 22061
tps = 2206.019701 (including connections establishing)
tps = 2206.534467 (excluding connections establishing)
statement latencies in milliseconds:
0.003143 \set naccounts 100000 * :scale
0.000776 \setrandom aid 1 :naccounts
0.447513 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
Multi thread 'select only' results are also pretty stable: I get
around 16-17k tps, but of note:
*) iowait in the mid 40's
*) cpu bound
*) consistent 430mb/sec off the device per iostat !! that's
incredible!! (some of the latency may in fact be from SATA).
transaction type: SELECT only
scaling factor: 3000
query mode: simple
number of clients: 32
number of threads: 32
duration: 20 s
number of transactions actually processed: 321823
tps = 16052.052818 (including connections establishing)
tps = 16062.973737 (excluding connections establishing)
statement latencies in milliseconds:
0.002469 \set naccounts 100000 * :scale
0.000528 \setrandom aid 1 :naccounts
1.984443 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
For random write tests, I see around 1000tps for single thread and ~
4700 with 32 threads. These results are more volatile and,
importantly, I disable synchronous commit feature. For the price,
unless you are doing tons and tons of writing (in which case i'd opt
for a more expensive drive like the S3700). This drive is perfectly
suited for OLAP work IMO since ssds like the big sequential loads and
random access of the data is no problem.
merlin
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: intel s3500 -- hot stuff
@ 2014-11-05 18:09 Merlin Moncure <mmoncure@gmail.com>
parent: Merlin Moncure <mmoncure@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Merlin Moncure @ 2014-11-05 18:09 UTC (permalink / raw)
To: pgsql-performance
On Wed, Nov 5, 2014 at 11:40 AM, Merlin Moncure <mmoncure@gmail.com> wrote:
> I recently sourced a 300gb intel s3500 ssd to do some performance
> testing. I didn't see a lot of results on the web so I thought I'd
> post some numbers. Testing machine is my workstation crapbox with 4
> cores and 8GB ram (of which about 4 is usable by the ~ 50gb database).
> The drive cost 260$ at newegg (sub 1$/gb) and is write durable.
Here's another fascinating data point. I was playing around
effective_io_concurrency for the device with bitmap heap scans on the
scale 3000 database (again, the performance numbers are very stable
across runs):
bench=# explain (analyze, buffers) select * from pgbench_accounts
where aid between 1000 and 50000000 and abalance != 0;
QUERY PLAN
────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
Bitmap Heap Scan on pgbench_accounts (cost=1059541.66..6929604.57
rows=1 width=97) (actual time=5040.128..23089.651 rows=1420738
loops=1)
Recheck Cond: ((aid >= 1000) AND (aid <= 50000000))
Rows Removed by Index Recheck: 3394823
Filter: (abalance <> 0)
Rows Removed by Filter: 48578263
Buffers: shared hit=3 read=1023980
-> Bitmap Index Scan on pgbench_accounts_pkey
(cost=0.00..1059541.66 rows=50532109 width=0) (actual
time=5038.707..5038.707 rows=49999001 loops=1)
Index Cond: ((aid >= 1000) AND (aid <= 50000000))
Buffers: shared hit=3 read=136611
Total runtime: 46251.375 ms
effective_io_concurrency 1: 46.3 sec, ~ 170 mb/sec peak via iostat
effective_io_concurrency 2: 49.3 sec, ~ 158 mb/sec peak via iostat
effective_io_concurrency 4: 29.1 sec, ~ 291 mb/sec peak via iostat
effective_io_concurrency 8: 23.2 sec, ~ 385 mb/sec peak via iostat
effective_io_concurrency 16: 22.1 sec, ~ 409 mb/sec peak via iostat
effective_io_concurrency 32: 20.7 sec, ~ 447 mb/sec peak via iostat
effective_io_concurrency 64: 20.0 sec, ~ 468 mb/sec peak via iostat
effective_io_concurrency 128: 19.3 sec, ~ 488 mb/sec peak via iostat
effective_io_concurrency 256: 19.2 sec, ~ 494 mb/sec peak via iostat
Did not see consistent measurable gains > 256
effective_io_concurrency. Interesting that at setting of '2' (the
lowest possible setting with the feature actually working) is
pessimal.
merlin
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: intel s3500 -- hot stuff
@ 2014-12-06 13:08 Bruce Momjian <bruce@momjian.us>
parent: Merlin Moncure <mmoncure@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Bruce Momjian @ 2014-12-06 13:08 UTC (permalink / raw)
To: Merlin Moncure <mmoncure@gmail.com>; +Cc: pgsql-performance
On Wed, Nov 5, 2014 at 12:09:16PM -0600, Merlin Moncure wrote:
> effective_io_concurrency 1: 46.3 sec, ~ 170 mb/sec peak via iostat
> effective_io_concurrency 2: 49.3 sec, ~ 158 mb/sec peak via iostat
> effective_io_concurrency 4: 29.1 sec, ~ 291 mb/sec peak via iostat
> effective_io_concurrency 8: 23.2 sec, ~ 385 mb/sec peak via iostat
> effective_io_concurrency 16: 22.1 sec, ~ 409 mb/sec peak via iostat
> effective_io_concurrency 32: 20.7 sec, ~ 447 mb/sec peak via iostat
> effective_io_concurrency 64: 20.0 sec, ~ 468 mb/sec peak via iostat
> effective_io_concurrency 128: 19.3 sec, ~ 488 mb/sec peak via iostat
> effective_io_concurrency 256: 19.2 sec, ~ 494 mb/sec peak via iostat
>
> Did not see consistent measurable gains > 256
> effective_io_concurrency. Interesting that at setting of '2' (the
> lowest possible setting with the feature actually working) is
> pessimal.
Very interesting. When we added a per-tablespace random_page_cost,
there was a suggestion that we might want to add per-tablespace
effective_io_concurrency someday:
commit d86d51a95810caebcea587498068ff32fe28293e
Author: Robert Haas <rhaas@postgresql.org>
Date: Tue Jan 5 21:54:00 2010 +0000
Support ALTER TABLESPACE name SET/RESET ( tablespace_options ).
This patch only supports seq_page_cost and random_page_cost as parameters,
but it provides the infrastructure to scalably support many more.
In particular, we may want to add support for effective_io_concurrency,
but I'm leaving that as future work for now.
Thanks to Tom Lane for design help and Alvaro Herrera for the review.
It seems that time has come.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: intel s3500 -- hot stuff
@ 2014-12-08 21:40 Merlin Moncure <mmoncure@gmail.com>
parent: Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 8+ messages in thread
From: Merlin Moncure @ 2014-12-08 21:40 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: pgsql-performance
On Sat, Dec 6, 2014 at 7:08 AM, Bruce Momjian <bruce@momjian.us> wrote:
> On Wed, Nov 5, 2014 at 12:09:16PM -0600, Merlin Moncure wrote:
>> effective_io_concurrency 1: 46.3 sec, ~ 170 mb/sec peak via iostat
>> effective_io_concurrency 2: 49.3 sec, ~ 158 mb/sec peak via iostat
>> effective_io_concurrency 4: 29.1 sec, ~ 291 mb/sec peak via iostat
>> effective_io_concurrency 8: 23.2 sec, ~ 385 mb/sec peak via iostat
>> effective_io_concurrency 16: 22.1 sec, ~ 409 mb/sec peak via iostat
>> effective_io_concurrency 32: 20.7 sec, ~ 447 mb/sec peak via iostat
>> effective_io_concurrency 64: 20.0 sec, ~ 468 mb/sec peak via iostat
>> effective_io_concurrency 128: 19.3 sec, ~ 488 mb/sec peak via iostat
>> effective_io_concurrency 256: 19.2 sec, ~ 494 mb/sec peak via iostat
>>
>> Did not see consistent measurable gains > 256
>> effective_io_concurrency. Interesting that at setting of '2' (the
>> lowest possible setting with the feature actually working) is
>> pessimal.
>
> Very interesting. When we added a per-tablespace random_page_cost,
> there was a suggestion that we might want to add per-tablespace
> effective_io_concurrency someday:
What I'd really like to see is to have effective_io_concurrency work
on other types of scans. It's clearly a barn burner on fast storage
and perhaps the default should be something other than '1'. Spinning
storage is clearly dead and ssd seem to really benefit from the posix
readhead api.
merlin
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: intel s3500 -- hot stuff
@ 2014-12-09 20:43 Bruce Momjian <bruce@momjian.us>
parent: Merlin Moncure <mmoncure@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Bruce Momjian @ 2014-12-09 20:43 UTC (permalink / raw)
To: Merlin Moncure <mmoncure@gmail.com>; +Cc: pgsql-performance
On Mon, Dec 8, 2014 at 03:40:43PM -0600, Merlin Moncure wrote:
> >> Did not see consistent measurable gains > 256
> >> effective_io_concurrency. Interesting that at setting of '2' (the
> >> lowest possible setting with the feature actually working) is
> >> pessimal.
> >
> > Very interesting. When we added a per-tablespace random_page_cost,
> > there was a suggestion that we might want to add per-tablespace
> > effective_io_concurrency someday:
>
> What I'd really like to see is to have effective_io_concurrency work
> on other types of scans. It's clearly a barn burner on fast storage
> and perhaps the default should be something other than '1'. Spinning
> storage is clearly dead and ssd seem to really benefit from the posix
> readhead api.
Well, the real question is knowing which blocks to request before
actually needing them. With a bitmap scan, that is easy --- I am
unclear how to do it for other scans. We already have kernel read-ahead
for sequential scans, and any index scan that hits multiple rows will
probably already be using a bitmap heap scan.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: intel s3500 -- hot stuff
@ 2014-12-10 16:52 Jeff Janes <jeff.janes@gmail.com>
parent: Bruce Momjian <bruce@momjian.us>
0 siblings, 1 reply; 8+ messages in thread
From: Jeff Janes @ 2014-12-10 16:52 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: Merlin Moncure <mmoncure@gmail.com>; pgsql-performance
On Tue, Dec 9, 2014 at 12:43 PM, Bruce Momjian <bruce@momjian.us> wrote:
> On Mon, Dec 8, 2014 at 03:40:43PM -0600, Merlin Moncure wrote:
> > >> Did not see consistent measurable gains > 256
> > >> effective_io_concurrency. Interesting that at setting of '2' (the
> > >> lowest possible setting with the feature actually working) is
> > >> pessimal.
> > >
> > > Very interesting. When we added a per-tablespace random_page_cost,
> > > there was a suggestion that we might want to add per-tablespace
> > > effective_io_concurrency someday:
> >
> > What I'd really like to see is to have effective_io_concurrency work
> > on other types of scans. It's clearly a barn burner on fast storage
> > and perhaps the default should be something other than '1'. Spinning
> > storage is clearly dead and ssd seem to really benefit from the posix
> > readhead api.
>
I haven't played much with SSD, but effective_io_concurrency can be a big
win even on spinning disk.
>
> Well, the real question is knowing which blocks to request before
> actually needing them. With a bitmap scan, that is easy --- I am
> unclear how to do it for other scans. We already have kernel read-ahead
> for sequential scans, and any index scan that hits multiple rows will
> probably already be using a bitmap heap scan.
>
If the index scan is used to provide ordering as well as selectivity than
it will resist being converted to an bitmap scan. Also it won't convert to
a bitmap scan solely to get credit for the use of effective_io_concurrency,
as that setting doesn't enter into planning decisions.
For a regular index scan, it should be easy to prefetch table blocks for
all the tuples that will need to be retrieved based on the current index
leaf page, for example. Looking ahead across leaf page boundaries would be
harder.
Cheers,
Jeff
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: intel s3500 -- hot stuff
@ 2015-07-18 10:03 Julien Rouhaud <julien.rouhaud@dalibo.com>
parent: Jeff Janes <jeff.janes@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Julien Rouhaud @ 2015-07-18 10:03 UTC (permalink / raw)
To: Jeff Janes <jeff.janes@gmail.com>; Bruce Momjian <bruce@momjian.us>; +Cc: Merlin Moncure <mmoncure@gmail.com>; pgsql-performance
On 10/12/2014 17:52, Jeff Janes wrote:
> On Tue, Dec 9, 2014 at 12:43 PM, Bruce Momjian <bruce@momjian.us
> <mailto:bruce@momjian.us>> wrote:
>
> On Mon, Dec 8, 2014 at 03:40:43PM -0600, Merlin Moncure wrote:
> > >> Did not see consistent measurable gains > 256
> > >> effective_io_concurrency. Interesting that at setting of '2' (the
> > >> lowest possible setting with the feature actually working) is
> > >> pessimal.
> > >
> > > Very interesting. When we added a per-tablespace random_page_cost,
> > > there was a suggestion that we might want to add per-tablespace
> > > effective_io_concurrency someday:
> >
> > What I'd really like to see is to have effective_io_concurrency work
> > on other types of scans. It's clearly a barn burner on fast storage
> > and perhaps the default should be something other than '1'. Spinning
> > storage is clearly dead and ssd seem to really benefit from the posix
> > readhead api.
>
>
> I haven't played much with SSD, but effective_io_concurrency can be a
> big win even on spinning disk.
>
>
>
> Well, the real question is knowing which blocks to request before
> actually needing them. With a bitmap scan, that is easy --- I am
> unclear how to do it for other scans. We already have kernel read-ahead
> for sequential scans, and any index scan that hits multiple rows will
> probably already be using a bitmap heap scan.
>
>
> If the index scan is used to provide ordering as well as selectivity
> than it will resist being converted to an bitmap scan. Also it won't
> convert to a bitmap scan solely to get credit for the use of
> effective_io_concurrency, as that setting doesn't enter into planning
> decisions.
>
> For a regular index scan, it should be easy to prefetch table blocks for
> all the tuples that will need to be retrieved based on the current index
> leaf page, for example. Looking ahead across leaf page boundaries would
> be harder.
>
I also think that having effective_io_concurrency for other nodes that
bitmap scan would be really great, but for now
having a per-tablespace effective_io_concurrency is simpler to implement
and will already help, so here's a patch to implement it. I'm also
adding it to the next commitfest.
--
Julien Rouhaud
http://dalibo.com - http://dalibo.org
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Attachments:
io_concurrency_per_tablespace-v1.patch (text/x-patch, 15.9K) download
diff --git a/doc/src/sgml/ref/create_tablespace.sgml b/doc/src/sgml/ref/create_tablespace.sgml
index 5756c3e..cf08408 100644
--- a/doc/src/sgml/ref/create_tablespace.sgml
+++ b/doc/src/sgml/ref/create_tablespace.sgml
@@ -104,14 +104,15 @@ CREATE TABLESPACE <replaceable class="parameter">tablespace_name</replaceable>
<listitem>
<para>
A tablespace parameter to be set or reset. Currently, the only
- available parameters are <varname>seq_page_cost</> and
- <varname>random_page_cost</>. Setting either value for a particular
- tablespace will override the planner's usual estimate of the cost of
- reading pages from tables in that tablespace, as established by
- the configuration parameters of the same name (see
- <xref linkend="guc-seq-page-cost">,
- <xref linkend="guc-random-page-cost">). This may be useful if one
- tablespace is located on a disk which is faster or slower than the
+ available parameters are <varname>seq_page_cost</>,
+ <varname>random_page_cost</> and <varname>effective_io_concurrency</>.
+ Setting either value for a particular tablespace will override the
+ planner's usual estimate of the cost of reading pages from tables in
+ that tablespace, as established by the configuration parameters of the
+ same name (see <xref linkend="guc-seq-page-cost">,
+ <xref linkend="guc-random-page-cost">,
+ <xref linkend="guc-effective-io-concurrency">). This may be useful if
+ one tablespace is located on a disk which is faster or slower than the
remainder of the I/O subsystem.
</para>
</listitem>
diff --git a/src/backend/access/common/reloptions.c b/src/backend/access/common/reloptions.c
index 8176b6a..fb24d74 100644
--- a/src/backend/access/common/reloptions.c
+++ b/src/backend/access/common/reloptions.c
@@ -232,6 +232,18 @@ static relopt_int intRelOpts[] =
},
-1, 64, MAX_KILOBYTES
},
+ {
+ {
+ "effective_io_concurrency",
+ "Number of simultaneous requests that can be handled efficiently by the disk subsystem.",
+ RELOPT_KIND_TABLESPACE
+ },
+#ifdef USE_PREFETCH
+ 1, 0, MAX_IO_CONCURRENCY
+#else
+ 0, 0, 0
+#endif
+ },
/* list terminator */
{{NULL}}
@@ -1387,7 +1399,8 @@ tablespace_reloptions(Datum reloptions, bool validate)
int numoptions;
static const relopt_parse_elt tab[] = {
{"random_page_cost", RELOPT_TYPE_REAL, offsetof(TableSpaceOpts, random_page_cost)},
- {"seq_page_cost", RELOPT_TYPE_REAL, offsetof(TableSpaceOpts, seq_page_cost)}
+ {"seq_page_cost", RELOPT_TYPE_REAL, offsetof(TableSpaceOpts, seq_page_cost)},
+ {"effective_io_concurrency", RELOPT_TYPE_INT, offsetof(TableSpaceOpts, effective_io_concurrency)}
};
options = parseRelOptions(reloptions, validate, RELOPT_KIND_TABLESPACE,
diff --git a/src/backend/executor/nodeBitmapHeapscan.c b/src/backend/executor/nodeBitmapHeapscan.c
index 4597437..7ea77c8 100644
--- a/src/backend/executor/nodeBitmapHeapscan.c
+++ b/src/backend/executor/nodeBitmapHeapscan.c
@@ -42,8 +42,10 @@
#include "pgstat.h"
#include "storage/bufmgr.h"
#include "storage/predicate.h"
+#include "utils/guc.h"
#include "utils/memutils.h"
#include "utils/rel.h"
+#include "utils/spccache.h"
#include "utils/snapmgr.h"
#include "utils/tqual.h"
@@ -111,7 +113,7 @@ BitmapHeapNext(BitmapHeapScanState *node)
node->tbmres = tbmres = NULL;
#ifdef USE_PREFETCH
- if (target_prefetch_pages > 0)
+ if (node->target_prefetch_pages > 0)
{
node->prefetch_iterator = prefetch_iterator = tbm_begin_iterate(tbm);
node->prefetch_pages = 0;
@@ -188,10 +190,10 @@ BitmapHeapNext(BitmapHeapScanState *node)
* page/tuple, then to one after the second tuple is fetched, then
* it doubles as later pages are fetched.
*/
- if (node->prefetch_target >= target_prefetch_pages)
+ if (node->prefetch_target >= node->target_prefetch_pages)
/* don't increase any further */ ;
- else if (node->prefetch_target >= target_prefetch_pages / 2)
- node->prefetch_target = target_prefetch_pages;
+ else if (node->prefetch_target >= node->target_prefetch_pages / 2)
+ node->prefetch_target = node->target_prefetch_pages;
else if (node->prefetch_target > 0)
node->prefetch_target *= 2;
else
@@ -211,7 +213,7 @@ BitmapHeapNext(BitmapHeapScanState *node)
* Try to prefetch at least a few pages even before we get to the
* second page if we don't stop reading after the first tuple.
*/
- if (node->prefetch_target < target_prefetch_pages)
+ if (node->prefetch_target < node->target_prefetch_pages)
node->prefetch_target++;
#endif /* USE_PREFETCH */
}
@@ -539,6 +541,9 @@ ExecInitBitmapHeapScan(BitmapHeapScan *node, EState *estate, int eflags)
{
BitmapHeapScanState *scanstate;
Relation currentRelation;
+#ifdef USE_PREFETCH
+ int new_io_concurrency;
+#endif
/* check for unsupported flags */
Assert(!(eflags & (EXEC_FLAG_BACKWARD | EXEC_FLAG_MARK)));
@@ -598,6 +603,25 @@ ExecInitBitmapHeapScan(BitmapHeapScan *node, EState *estate, int eflags)
*/
currentRelation = ExecOpenScanRelation(estate, node->scan.scanrelid, eflags);
+#ifdef USE_PREFETCH
+ /* check if the effective_io_concurrency has been overloaded for the
+ * tablespace storing the relation and compute the target_prefetch_pages,
+ * or just get the current target_prefetch_pages
+ */
+ new_io_concurrency = get_tablespace_io_concurrency(
+ currentRelation->rd_rel->reltablespace);
+
+
+ scanstate->target_prefetch_pages = target_prefetch_pages;
+
+ if (new_io_concurrency != effective_io_concurrency)
+ {
+ double prefetch_pages;
+ if (compute_io_concurrency(new_io_concurrency, &prefetch_pages))
+ scanstate->target_prefetch_pages = rint(prefetch_pages);
+ }
+#endif
+
scanstate->ss.ss_currentRelation = currentRelation;
/*
@@ -634,3 +658,58 @@ ExecInitBitmapHeapScan(BitmapHeapScan *node, EState *estate, int eflags)
*/
return scanstate;
}
+
+bool
+compute_io_concurrency(int io_concurrency, double *target_prefetch_pages)
+{
+ double new_prefetch_pages = 0.0;
+ int i;
+
+ /* make sure the io_concurrency value is correct, it may have been forced
+ * with a pg_tablespace UPDATE
+ */
+ if (io_concurrency > MAX_IO_CONCURRENCY)
+ io_concurrency = MAX_IO_CONCURRENCY;
+
+ /*----------
+ * The user-visible GUC parameter is the number of drives (spindles),
+ * which we need to translate to a number-of-pages-to-prefetch target.
+ * The target value is stashed in *extra and then assigned to the actual
+ * variable by assign_effective_io_concurrency.
+ *
+ * The expected number of prefetch pages needed to keep N drives busy is:
+ *
+ * drives | I/O requests
+ * -------+----------------
+ * 1 | 1
+ * 2 | 2/1 + 2/2 = 3
+ * 3 | 3/1 + 3/2 + 3/3 = 5 1/2
+ * 4 | 4/1 + 4/2 + 4/3 + 4/4 = 8 1/3
+ * n | n * H(n)
+ *
+ * This is called the "coupon collector problem" and H(n) is called the
+ * harmonic series. This could be approximated by n * ln(n), but for
+ * reasonable numbers of drives we might as well just compute the series.
+ *
+ * Alternatively we could set the target to the number of pages necessary
+ * so that the expected number of active spindles is some arbitrary
+ * percentage of the total. This sounds the same but is actually slightly
+ * different. The result ends up being ln(1-P)/ln((n-1)/n) where P is
+ * that desired fraction.
+ *
+ * Experimental results show that both of these formulas aren't aggressive
+ * enough, but we don't really have any better proposals.
+ *
+ * Note that if io_concurrency = 0 (disabled), we must set target = 0.
+ *----------
+ */
+
+
+ for (i = 1; i <= io_concurrency; i++)
+ new_prefetch_pages += (double) io_concurrency / (double) i;
+
+ *target_prefetch_pages = new_prefetch_pages;
+
+ /* This range check shouldn't fail, but let's be paranoid */
+ return (new_prefetch_pages > 0.0 && new_prefetch_pages < (double) INT_MAX);
+}
diff --git a/src/backend/utils/cache/spccache.c b/src/backend/utils/cache/spccache.c
index 1a0c884..970d66b 100644
--- a/src/backend/utils/cache/spccache.c
+++ b/src/backend/utils/cache/spccache.c
@@ -23,7 +23,9 @@
#include "commands/tablespace.h"
#include "miscadmin.h"
#include "optimizer/cost.h"
+#include "storage/bufmgr.h"
#include "utils/catcache.h"
+#include "utils/guc.h"
#include "utils/hsearch.h"
#include "utils/inval.h"
#include "utils/spccache.h"
@@ -198,3 +200,16 @@ get_tablespace_page_costs(Oid spcid,
*spc_seq_page_cost = spc->opts->seq_page_cost;
}
}
+
+int
+get_tablespace_io_concurrency(Oid spcid)
+{
+ TableSpaceCacheEntry *spc = get_tablespace(spcid);
+
+ Assert(spc != NULL);
+
+ if (!spc->opts || spc->opts->effective_io_concurrency < 0)
+ return effective_io_concurrency;
+ else
+ return spc->opts->effective_io_concurrency;
+}
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index 1bed525..6d7c0ae 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -37,6 +37,7 @@
#include "commands/vacuum.h"
#include "commands/variable.h"
#include "commands/trigger.h"
+#include "executor/nodeBitmapHeapscan.h"
#include "funcapi.h"
#include "libpq/auth.h"
#include "libpq/be-fsstubs.h"
@@ -438,6 +439,8 @@ int temp_file_limit = -1;
int num_temp_buffers = 1024;
+int effective_io_concurrency = 0;
+
char *cluster_name = "";
char *ConfigFileName;
char *HbaFileName;
@@ -490,7 +493,6 @@ static int wal_block_size;
static bool data_checksums;
static int wal_segment_size;
static bool integer_datetimes;
-static int effective_io_concurrency;
static bool assert_enabled;
/* should be static, but commands/variable.c needs to get at this */
@@ -2352,7 +2354,7 @@ static struct config_int ConfigureNamesInt[] =
},
&effective_io_concurrency,
#ifdef USE_PREFETCH
- 1, 0, 1000,
+ 1, 0, MAX_IO_CONCURRENCY,
#else
0, 0, 0,
#endif
@@ -9997,47 +9999,9 @@ static bool
check_effective_io_concurrency(int *newval, void **extra, GucSource source)
{
#ifdef USE_PREFETCH
- double new_prefetch_pages = 0.0;
- int i;
-
- /*----------
- * The user-visible GUC parameter is the number of drives (spindles),
- * which we need to translate to a number-of-pages-to-prefetch target.
- * The target value is stashed in *extra and then assigned to the actual
- * variable by assign_effective_io_concurrency.
- *
- * The expected number of prefetch pages needed to keep N drives busy is:
- *
- * drives | I/O requests
- * -------+----------------
- * 1 | 1
- * 2 | 2/1 + 2/2 = 3
- * 3 | 3/1 + 3/2 + 3/3 = 5 1/2
- * 4 | 4/1 + 4/2 + 4/3 + 4/4 = 8 1/3
- * n | n * H(n)
- *
- * This is called the "coupon collector problem" and H(n) is called the
- * harmonic series. This could be approximated by n * ln(n), but for
- * reasonable numbers of drives we might as well just compute the series.
- *
- * Alternatively we could set the target to the number of pages necessary
- * so that the expected number of active spindles is some arbitrary
- * percentage of the total. This sounds the same but is actually slightly
- * different. The result ends up being ln(1-P)/ln((n-1)/n) where P is
- * that desired fraction.
- *
- * Experimental results show that both of these formulas aren't aggressive
- * enough, but we don't really have any better proposals.
- *
- * Note that if *newval = 0 (disabled), we must set target = 0.
- *----------
- */
-
- for (i = 1; i <= *newval; i++)
- new_prefetch_pages += (double) *newval / (double) i;
+ double new_prefetch_pages;
- /* This range check shouldn't fail, but let's be paranoid */
- if (new_prefetch_pages >= 0.0 && new_prefetch_pages < (double) INT_MAX)
+ if (compute_io_concurrency(*newval, &new_prefetch_pages))
{
int *myextra = (int *) guc_malloc(ERROR, sizeof(int));
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 0683548..36b8a75 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1870,7 +1870,7 @@ psql_completion(const char *text, int start, int end)
pg_strcasecmp(prev_wd, "(") == 0)
{
static const char *const list_TABLESPACEOPTIONS[] =
- {"seq_page_cost", "random_page_cost", NULL};
+ {"seq_page_cost", "random_page_cost", "effective_io_concurrency", NULL};
COMPLETE_WITH_LIST(list_TABLESPACEOPTIONS);
}
diff --git a/src/include/commands/tablespace.h b/src/include/commands/tablespace.h
index 6b928a5..be9582a 100644
--- a/src/include/commands/tablespace.h
+++ b/src/include/commands/tablespace.h
@@ -39,6 +39,7 @@ typedef struct TableSpaceOpts
int32 vl_len_; /* varlena header (do not touch directly!) */
float8 random_page_cost;
float8 seq_page_cost;
+ int effective_io_concurrency;
} TableSpaceOpts;
extern Oid CreateTableSpace(CreateTableSpaceStmt *stmt);
diff --git a/src/include/executor/nodeBitmapHeapscan.h b/src/include/executor/nodeBitmapHeapscan.h
index 3183376..698fcf5 100644
--- a/src/include/executor/nodeBitmapHeapscan.h
+++ b/src/include/executor/nodeBitmapHeapscan.h
@@ -20,5 +20,6 @@ extern BitmapHeapScanState *ExecInitBitmapHeapScan(BitmapHeapScan *node, EState
extern TupleTableSlot *ExecBitmapHeapScan(BitmapHeapScanState *node);
extern void ExecEndBitmapHeapScan(BitmapHeapScanState *node);
extern void ExecReScanBitmapHeapScan(BitmapHeapScanState *node);
+extern bool compute_io_concurrency(int io_concurrency, double *target_prefetch_pages);
#endif /* NODEBITMAPHEAPSCAN_H */
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index 541ee18..c6d48fa 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -1410,15 +1410,16 @@ typedef struct BitmapIndexScanState
/* ----------------
* BitmapHeapScanState information
*
- * bitmapqualorig execution state for bitmapqualorig expressions
- * tbm bitmap obtained from child index scan(s)
- * tbmiterator iterator for scanning current pages
- * tbmres current-page data
- * exact_pages total number of exact pages retrieved
- * lossy_pages total number of lossy pages retrieved
- * prefetch_iterator iterator for prefetching ahead of current page
- * prefetch_pages # pages prefetch iterator is ahead of current
- * prefetch_target target prefetch distance
+ * bitmapqualorig execution state for bitmapqualorig expressions
+ * tbm bitmap obtained from child index scan(s)
+ * tbmiterator iterator for scanning current pages
+ * tbmres current-page data
+ * exact_pages total number of exact pages retrieved
+ * lossy_pages total number of lossy pages retrieved
+ * prefetch_iterator iterator for prefetching ahead of current page
+ * prefetch_pages # pages prefetch iterator is ahead of current
+ * prefetch_target target prefetch distance
+ * target_prefetch_pages may be overloaded by tablespace setting
* ----------------
*/
typedef struct BitmapHeapScanState
@@ -1433,6 +1434,9 @@ typedef struct BitmapHeapScanState
TBMIterator *prefetch_iterator;
int prefetch_pages;
int prefetch_target;
+#ifdef USE_PREFETCH
+ int target_prefetch_pages;
+#endif
} BitmapHeapScanState;
/* ----------------
diff --git a/src/include/utils/guc.h b/src/include/utils/guc.h
index dc167f9..57008fc 100644
--- a/src/include/utils/guc.h
+++ b/src/include/utils/guc.h
@@ -26,6 +26,9 @@
#define MAX_KILOBYTES (INT_MAX / 1024)
#endif
+/* upper limit for effective_io_concurrency */
+#define MAX_IO_CONCURRENCY 1000
+
/*
* Automatic configuration file name for ALTER SYSTEM.
* This file will be used to store values of configuration parameters
@@ -256,6 +259,8 @@ extern int temp_file_limit;
extern int num_temp_buffers;
+extern int effective_io_concurrency;
+
extern char *cluster_name;
extern char *ConfigFileName;
extern char *HbaFileName;
diff --git a/src/include/utils/spccache.h b/src/include/utils/spccache.h
index bdd1c0f..e5b9769 100644
--- a/src/include/utils/spccache.h
+++ b/src/include/utils/spccache.h
@@ -15,5 +15,6 @@
void get_tablespace_page_costs(Oid spcid, float8 *spc_random_page_cost,
float8 *spc_seq_page_cost);
+int get_tablespace_io_concurrency(Oid spcid);
#endif /* SPCCACHE_H */
* Re: [PERFORM] intel s3500 -- hot stuff
@ 2015-07-18 10:17 Julien Rouhaud <julien.rouhaud@dalibo.com>
parent: Julien Rouhaud <julien.rouhaud@dalibo.com>
0 siblings, 0 replies; 8+ messages in thread
From: Julien Rouhaud @ 2015-07-18 10:17 UTC (permalink / raw)
To: Jeff Janes <jeff.janes@gmail.com>; Bruce Momjian <bruce@momjian.us>; +Cc: Merlin Moncure <mmoncure@gmail.com>; pgsql-hackers@postgresql.org <pgsql-hackers@postgresql.org>
On 18/07/2015 12:03, Julien Rouhaud wrote:
> On 10/12/2014 17:52, Jeff Janes wrote:
>> On Tue, Dec 9, 2014 at 12:43 PM, Bruce Momjian <bruce@momjian.us
>> <mailto:bruce@momjian.us>> wrote:
>>
>> On Mon, Dec 8, 2014 at 03:40:43PM -0600, Merlin Moncure wrote:
>> > >> Did not see consistent measurable gains > 256
>> > >> effective_io_concurrency. Interesting that at setting of '2' (the
>> > >> lowest possible setting with the feature actually working) is
>> > >> pessimal.
>> > >
>> > > Very interesting. When we added a per-tablespace random_page_cost,
>> > > there was a suggestion that we might want to add per-tablespace
>> > > effective_io_concurrency someday:
>> >
>> > What I'd really like to see is to have effective_io_concurrency work
>> > on other types of scans. It's clearly a barn burner on fast storage
>> > and perhaps the default should be something other than '1'. Spinning
>> > storage is clearly dead and ssd seem to really benefit from the posix
>> > readhead api.
>>
>>
>> I haven't played much with SSD, but effective_io_concurrency can be a
>> big win even on spinning disk.
>>
>>
>>
>> Well, the real question is knowing which blocks to request before
>> actually needing them. With a bitmap scan, that is easy --- I am
>> unclear how to do it for other scans. We already have kernel read-ahead
>> for sequential scans, and any index scan that hits multiple rows will
>> probably already be using a bitmap heap scan.
>>
>>
>> If the index scan is used to provide ordering as well as selectivity
>> than it will resist being converted to an bitmap scan. Also it won't
>> convert to a bitmap scan solely to get credit for the use of
>> effective_io_concurrency, as that setting doesn't enter into planning
>> decisions.
>>
>> For a regular index scan, it should be easy to prefetch table blocks for
>> all the tuples that will need to be retrieved based on the current index
>> leaf page, for example. Looking ahead across leaf page boundaries would
>> be harder.
>>
>
> I also think that having effective_io_concurrency for other nodes that
> bitmap scan would be really great, but for now
> having a per-tablespace effective_io_concurrency is simpler to implement
> and will already help, so here's a patch to implement it. I'm also
> adding it to the next commitfest.
>
I didn't know that the thread must exists on -hackers to be able to add
a commitfest entry, so I transfer the thread here.
Sorry the double post.
--
Julien Rouhaud
http://dalibo.com - http://dalibo.org
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Attachments:
io_concurrency_per_tablespace-v1.patch (text/x-patch, 15.9K) download
diff --git a/doc/src/sgml/ref/create_tablespace.sgml b/doc/src/sgml/ref/create_tablespace.sgml
index 5756c3e..cf08408 100644
--- a/doc/src/sgml/ref/create_tablespace.sgml
+++ b/doc/src/sgml/ref/create_tablespace.sgml
@@ -104,14 +104,15 @@ CREATE TABLESPACE <replaceable class="parameter">tablespace_name</replaceable>
<listitem>
<para>
A tablespace parameter to be set or reset. Currently, the only
- available parameters are <varname>seq_page_cost</> and
- <varname>random_page_cost</>. Setting either value for a particular
- tablespace will override the planner's usual estimate of the cost of
- reading pages from tables in that tablespace, as established by
- the configuration parameters of the same name (see
- <xref linkend="guc-seq-page-cost">,
- <xref linkend="guc-random-page-cost">). This may be useful if one
- tablespace is located on a disk which is faster or slower than the
+ available parameters are <varname>seq_page_cost</>,
+ <varname>random_page_cost</> and <varname>effective_io_concurrency</>.
+ Setting either value for a particular tablespace will override the
+ planner's usual estimate of the cost of reading pages from tables in
+ that tablespace, as established by the configuration parameters of the
+ same name (see <xref linkend="guc-seq-page-cost">,
+ <xref linkend="guc-random-page-cost">,
+ <xref linkend="guc-effective-io-concurrency">). This may be useful if
+ one tablespace is located on a disk which is faster or slower than the
remainder of the I/O subsystem.
</para>
</listitem>
diff --git a/src/backend/access/common/reloptions.c b/src/backend/access/common/reloptions.c
index 8176b6a..fb24d74 100644
--- a/src/backend/access/common/reloptions.c
+++ b/src/backend/access/common/reloptions.c
@@ -232,6 +232,18 @@ static relopt_int intRelOpts[] =
},
-1, 64, MAX_KILOBYTES
},
+ {
+ {
+ "effective_io_concurrency",
+ "Number of simultaneous requests that can be handled efficiently by the disk subsystem.",
+ RELOPT_KIND_TABLESPACE
+ },
+#ifdef USE_PREFETCH
+ 1, 0, MAX_IO_CONCURRENCY
+#else
+ 0, 0, 0
+#endif
+ },
/* list terminator */
{{NULL}}
@@ -1387,7 +1399,8 @@ tablespace_reloptions(Datum reloptions, bool validate)
int numoptions;
static const relopt_parse_elt tab[] = {
{"random_page_cost", RELOPT_TYPE_REAL, offsetof(TableSpaceOpts, random_page_cost)},
- {"seq_page_cost", RELOPT_TYPE_REAL, offsetof(TableSpaceOpts, seq_page_cost)}
+ {"seq_page_cost", RELOPT_TYPE_REAL, offsetof(TableSpaceOpts, seq_page_cost)},
+ {"effective_io_concurrency", RELOPT_TYPE_INT, offsetof(TableSpaceOpts, effective_io_concurrency)}
};
options = parseRelOptions(reloptions, validate, RELOPT_KIND_TABLESPACE,
diff --git a/src/backend/executor/nodeBitmapHeapscan.c b/src/backend/executor/nodeBitmapHeapscan.c
index 4597437..7ea77c8 100644
--- a/src/backend/executor/nodeBitmapHeapscan.c
+++ b/src/backend/executor/nodeBitmapHeapscan.c
@@ -42,8 +42,10 @@
#include "pgstat.h"
#include "storage/bufmgr.h"
#include "storage/predicate.h"
+#include "utils/guc.h"
#include "utils/memutils.h"
#include "utils/rel.h"
+#include "utils/spccache.h"
#include "utils/snapmgr.h"
#include "utils/tqual.h"
@@ -111,7 +113,7 @@ BitmapHeapNext(BitmapHeapScanState *node)
node->tbmres = tbmres = NULL;
#ifdef USE_PREFETCH
- if (target_prefetch_pages > 0)
+ if (node->target_prefetch_pages > 0)
{
node->prefetch_iterator = prefetch_iterator = tbm_begin_iterate(tbm);
node->prefetch_pages = 0;
@@ -188,10 +190,10 @@ BitmapHeapNext(BitmapHeapScanState *node)
* page/tuple, then to one after the second tuple is fetched, then
* it doubles as later pages are fetched.
*/
- if (node->prefetch_target >= target_prefetch_pages)
+ if (node->prefetch_target >= node->target_prefetch_pages)
/* don't increase any further */ ;
- else if (node->prefetch_target >= target_prefetch_pages / 2)
- node->prefetch_target = target_prefetch_pages;
+ else if (node->prefetch_target >= node->target_prefetch_pages / 2)
+ node->prefetch_target = node->target_prefetch_pages;
else if (node->prefetch_target > 0)
node->prefetch_target *= 2;
else
@@ -211,7 +213,7 @@ BitmapHeapNext(BitmapHeapScanState *node)
* Try to prefetch at least a few pages even before we get to the
* second page if we don't stop reading after the first tuple.
*/
- if (node->prefetch_target < target_prefetch_pages)
+ if (node->prefetch_target < node->target_prefetch_pages)
node->prefetch_target++;
#endif /* USE_PREFETCH */
}
@@ -539,6 +541,9 @@ ExecInitBitmapHeapScan(BitmapHeapScan *node, EState *estate, int eflags)
{
BitmapHeapScanState *scanstate;
Relation currentRelation;
+#ifdef USE_PREFETCH
+ int new_io_concurrency;
+#endif
/* check for unsupported flags */
Assert(!(eflags & (EXEC_FLAG_BACKWARD | EXEC_FLAG_MARK)));
@@ -598,6 +603,25 @@ ExecInitBitmapHeapScan(BitmapHeapScan *node, EState *estate, int eflags)
*/
currentRelation = ExecOpenScanRelation(estate, node->scan.scanrelid, eflags);
+#ifdef USE_PREFETCH
+ /* check if the effective_io_concurrency has been overloaded for the
+ * tablespace storing the relation and compute the target_prefetch_pages,
+ * or just get the current target_prefetch_pages
+ */
+ new_io_concurrency = get_tablespace_io_concurrency(
+ currentRelation->rd_rel->reltablespace);
+
+
+ scanstate->target_prefetch_pages = target_prefetch_pages;
+
+ if (new_io_concurrency != effective_io_concurrency)
+ {
+ double prefetch_pages;
+ if (compute_io_concurrency(new_io_concurrency, &prefetch_pages))
+ scanstate->target_prefetch_pages = rint(prefetch_pages);
+ }
+#endif
+
scanstate->ss.ss_currentRelation = currentRelation;
/*
@@ -634,3 +658,58 @@ ExecInitBitmapHeapScan(BitmapHeapScan *node, EState *estate, int eflags)
*/
return scanstate;
}
+
+bool
+compute_io_concurrency(int io_concurrency, double *target_prefetch_pages)
+{
+ double new_prefetch_pages = 0.0;
+ int i;
+
+ /* make sure the io_concurrency value is correct, it may have been forced
+ * with a pg_tablespace UPDATE
+ */
+ if (io_concurrency > MAX_IO_CONCURRENCY)
+ io_concurrency = MAX_IO_CONCURRENCY;
+
+ /*----------
+ * The user-visible GUC parameter is the number of drives (spindles),
+ * which we need to translate to a number-of-pages-to-prefetch target.
+ * The target value is stashed in *extra and then assigned to the actual
+ * variable by assign_effective_io_concurrency.
+ *
+ * The expected number of prefetch pages needed to keep N drives busy is:
+ *
+ * drives | I/O requests
+ * -------+----------------
+ * 1 | 1
+ * 2 | 2/1 + 2/2 = 3
+ * 3 | 3/1 + 3/2 + 3/3 = 5 1/2
+ * 4 | 4/1 + 4/2 + 4/3 + 4/4 = 8 1/3
+ * n | n * H(n)
+ *
+ * This is called the "coupon collector problem" and H(n) is called the
+ * harmonic series. This could be approximated by n * ln(n), but for
+ * reasonable numbers of drives we might as well just compute the series.
+ *
+ * Alternatively we could set the target to the number of pages necessary
+ * so that the expected number of active spindles is some arbitrary
+ * percentage of the total. This sounds the same but is actually slightly
+ * different. The result ends up being ln(1-P)/ln((n-1)/n) where P is
+ * that desired fraction.
+ *
+ * Experimental results show that both of these formulas aren't aggressive
+ * enough, but we don't really have any better proposals.
+ *
+ * Note that if io_concurrency = 0 (disabled), we must set target = 0.
+ *----------
+ */
+
+
+ for (i = 1; i <= io_concurrency; i++)
+ new_prefetch_pages += (double) io_concurrency / (double) i;
+
+ *target_prefetch_pages = new_prefetch_pages;
+
+ /* This range check shouldn't fail, but let's be paranoid */
+ return (new_prefetch_pages > 0.0 && new_prefetch_pages < (double) INT_MAX);
+}
diff --git a/src/backend/utils/cache/spccache.c b/src/backend/utils/cache/spccache.c
index 1a0c884..970d66b 100644
--- a/src/backend/utils/cache/spccache.c
+++ b/src/backend/utils/cache/spccache.c
@@ -23,7 +23,9 @@
#include "commands/tablespace.h"
#include "miscadmin.h"
#include "optimizer/cost.h"
+#include "storage/bufmgr.h"
#include "utils/catcache.h"
+#include "utils/guc.h"
#include "utils/hsearch.h"
#include "utils/inval.h"
#include "utils/spccache.h"
@@ -198,3 +200,16 @@ get_tablespace_page_costs(Oid spcid,
*spc_seq_page_cost = spc->opts->seq_page_cost;
}
}
+
+int
+get_tablespace_io_concurrency(Oid spcid)
+{
+ TableSpaceCacheEntry *spc = get_tablespace(spcid);
+
+ Assert(spc != NULL);
+
+ if (!spc->opts || spc->opts->effective_io_concurrency < 0)
+ return effective_io_concurrency;
+ else
+ return spc->opts->effective_io_concurrency;
+}
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index 1bed525..6d7c0ae 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -37,6 +37,7 @@
#include "commands/vacuum.h"
#include "commands/variable.h"
#include "commands/trigger.h"
+#include "executor/nodeBitmapHeapscan.h"
#include "funcapi.h"
#include "libpq/auth.h"
#include "libpq/be-fsstubs.h"
@@ -438,6 +439,8 @@ int temp_file_limit = -1;
int num_temp_buffers = 1024;
+int effective_io_concurrency = 0;
+
char *cluster_name = "";
char *ConfigFileName;
char *HbaFileName;
@@ -490,7 +493,6 @@ static int wal_block_size;
static bool data_checksums;
static int wal_segment_size;
static bool integer_datetimes;
-static int effective_io_concurrency;
static bool assert_enabled;
/* should be static, but commands/variable.c needs to get at this */
@@ -2352,7 +2354,7 @@ static struct config_int ConfigureNamesInt[] =
},
&effective_io_concurrency,
#ifdef USE_PREFETCH
- 1, 0, 1000,
+ 1, 0, MAX_IO_CONCURRENCY,
#else
0, 0, 0,
#endif
@@ -9997,47 +9999,9 @@ static bool
check_effective_io_concurrency(int *newval, void **extra, GucSource source)
{
#ifdef USE_PREFETCH
- double new_prefetch_pages = 0.0;
- int i;
-
- /*----------
- * The user-visible GUC parameter is the number of drives (spindles),
- * which we need to translate to a number-of-pages-to-prefetch target.
- * The target value is stashed in *extra and then assigned to the actual
- * variable by assign_effective_io_concurrency.
- *
- * The expected number of prefetch pages needed to keep N drives busy is:
- *
- * drives | I/O requests
- * -------+----------------
- * 1 | 1
- * 2 | 2/1 + 2/2 = 3
- * 3 | 3/1 + 3/2 + 3/3 = 5 1/2
- * 4 | 4/1 + 4/2 + 4/3 + 4/4 = 8 1/3
- * n | n * H(n)
- *
- * This is called the "coupon collector problem" and H(n) is called the
- * harmonic series. This could be approximated by n * ln(n), but for
- * reasonable numbers of drives we might as well just compute the series.
- *
- * Alternatively we could set the target to the number of pages necessary
- * so that the expected number of active spindles is some arbitrary
- * percentage of the total. This sounds the same but is actually slightly
- * different. The result ends up being ln(1-P)/ln((n-1)/n) where P is
- * that desired fraction.
- *
- * Experimental results show that both of these formulas aren't aggressive
- * enough, but we don't really have any better proposals.
- *
- * Note that if *newval = 0 (disabled), we must set target = 0.
- *----------
- */
-
- for (i = 1; i <= *newval; i++)
- new_prefetch_pages += (double) *newval / (double) i;
+ double new_prefetch_pages;
- /* This range check shouldn't fail, but let's be paranoid */
- if (new_prefetch_pages >= 0.0 && new_prefetch_pages < (double) INT_MAX)
+ if (compute_io_concurrency(*newval, &new_prefetch_pages))
{
int *myextra = (int *) guc_malloc(ERROR, sizeof(int));
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 0683548..36b8a75 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1870,7 +1870,7 @@ psql_completion(const char *text, int start, int end)
pg_strcasecmp(prev_wd, "(") == 0)
{
static const char *const list_TABLESPACEOPTIONS[] =
- {"seq_page_cost", "random_page_cost", NULL};
+ {"seq_page_cost", "random_page_cost", "effective_io_concurrency", NULL};
COMPLETE_WITH_LIST(list_TABLESPACEOPTIONS);
}
diff --git a/src/include/commands/tablespace.h b/src/include/commands/tablespace.h
index 6b928a5..be9582a 100644
--- a/src/include/commands/tablespace.h
+++ b/src/include/commands/tablespace.h
@@ -39,6 +39,7 @@ typedef struct TableSpaceOpts
int32 vl_len_; /* varlena header (do not touch directly!) */
float8 random_page_cost;
float8 seq_page_cost;
+ int effective_io_concurrency;
} TableSpaceOpts;
extern Oid CreateTableSpace(CreateTableSpaceStmt *stmt);
diff --git a/src/include/executor/nodeBitmapHeapscan.h b/src/include/executor/nodeBitmapHeapscan.h
index 3183376..698fcf5 100644
--- a/src/include/executor/nodeBitmapHeapscan.h
+++ b/src/include/executor/nodeBitmapHeapscan.h
@@ -20,5 +20,6 @@ extern BitmapHeapScanState *ExecInitBitmapHeapScan(BitmapHeapScan *node, EState
extern TupleTableSlot *ExecBitmapHeapScan(BitmapHeapScanState *node);
extern void ExecEndBitmapHeapScan(BitmapHeapScanState *node);
extern void ExecReScanBitmapHeapScan(BitmapHeapScanState *node);
+extern bool compute_io_concurrency(int io_concurrency, double *target_prefetch_pages);
#endif /* NODEBITMAPHEAPSCAN_H */
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index 541ee18..c6d48fa 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -1410,15 +1410,16 @@ typedef struct BitmapIndexScanState
/* ----------------
* BitmapHeapScanState information
*
- * bitmapqualorig execution state for bitmapqualorig expressions
- * tbm bitmap obtained from child index scan(s)
- * tbmiterator iterator for scanning current pages
- * tbmres current-page data
- * exact_pages total number of exact pages retrieved
- * lossy_pages total number of lossy pages retrieved
- * prefetch_iterator iterator for prefetching ahead of current page
- * prefetch_pages # pages prefetch iterator is ahead of current
- * prefetch_target target prefetch distance
+ * bitmapqualorig execution state for bitmapqualorig expressions
+ * tbm bitmap obtained from child index scan(s)
+ * tbmiterator iterator for scanning current pages
+ * tbmres current-page data
+ * exact_pages total number of exact pages retrieved
+ * lossy_pages total number of lossy pages retrieved
+ * prefetch_iterator iterator for prefetching ahead of current page
+ * prefetch_pages # pages prefetch iterator is ahead of current
+ * prefetch_target target prefetch distance
+ * target_prefetch_pages may be overloaded by tablespace setting
* ----------------
*/
typedef struct BitmapHeapScanState
@@ -1433,6 +1434,9 @@ typedef struct BitmapHeapScanState
TBMIterator *prefetch_iterator;
int prefetch_pages;
int prefetch_target;
+#ifdef USE_PREFETCH
+ int target_prefetch_pages;
+#endif
} BitmapHeapScanState;
/* ----------------
diff --git a/src/include/utils/guc.h b/src/include/utils/guc.h
index dc167f9..57008fc 100644
--- a/src/include/utils/guc.h
+++ b/src/include/utils/guc.h
@@ -26,6 +26,9 @@
#define MAX_KILOBYTES (INT_MAX / 1024)
#endif
+/* upper limit for effective_io_concurrency */
+#define MAX_IO_CONCURRENCY 1000
+
/*
* Automatic configuration file name for ALTER SYSTEM.
* This file will be used to store values of configuration parameters
@@ -256,6 +259,8 @@ extern int temp_file_limit;
extern int num_temp_buffers;
+extern int effective_io_concurrency;
+
extern char *cluster_name;
extern char *ConfigFileName;
extern char *HbaFileName;
diff --git a/src/include/utils/spccache.h b/src/include/utils/spccache.h
index bdd1c0f..e5b9769 100644
--- a/src/include/utils/spccache.h
+++ b/src/include/utils/spccache.h
@@ -15,5 +15,6 @@
void get_tablespace_page_costs(Oid spcid, float8 *spc_random_page_cost,
float8 *spc_seq_page_cost);
+int get_tablespace_io_concurrency(Oid spcid);
#endif /* SPCCACHE_H */
end of thread, other threads:[~2015-07-18 10:17 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-11-05 17:40 intel s3500 -- hot stuff Merlin Moncure <mmoncure@gmail.com>
2014-11-05 18:09 ` Merlin Moncure <mmoncure@gmail.com>
2014-12-06 13:08 ` Bruce Momjian <bruce@momjian.us>
2014-12-08 21:40 ` Merlin Moncure <mmoncure@gmail.com>
2014-12-09 20:43 ` Bruce Momjian <bruce@momjian.us>
2014-12-10 16:52 ` Jeff Janes <jeff.janes@gmail.com>
2015-07-18 10:03 ` Julien Rouhaud <julien.rouhaud@dalibo.com>
2015-07-18 10:17 ` Julien Rouhaud <julien.rouhaud@dalibo.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