pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedTablespace-derived stats?
6+ messages / 3 participants
[nested] [flat]
* Tablespace-derived stats?
@ 2012-10-19 14:29 Shaun Thomas <sthomas@optionshouse.com>
2012-10-19 14:51 ` Re: Tablespace-derived stats? Tom Lane <tgl@sss.pgh.pa.us>
2012-10-19 15:05 ` Re: Tablespace-derived stats? Jeff Janes <jeff.janes@gmail.com>
0 siblings, 2 replies; 6+ messages in thread
From: Shaun Thomas @ 2012-10-19 14:29 UTC (permalink / raw)
To: pgsql-performance
Hello Perf,
Lately I've been pondering. As systems get more complex, it's not
uncommon for tiered storage to enter the picture. Say for instance, a
user has some really fast tables on a NVRAM-based device, and
slower-access stuff on a RAID, even slower stuff on an EDB, and variants
like local disk or a RAM drive.
Yet there's only one global setting for random_page_cost, and
seq_page_cost, and so on.
Would there be any benefit at all to adding these as parameters to the
tablespaces themselves? I can imagine the planner could override the
default with the tablespace setting on a per-table basis when
calculating the cost of retrieving rows from tables/indexes on faster or
slower storage.
This is especially true since each of the storage engines I listed have
drastically different performance profiles, but no way to hint to the
planner. There was a talk at the last PG Open about his EDB tests vastly
preferring partitioning and sequential access because random access was
so terrible. But NVRAM has the opposite metric. Currently, tuning for
one necessarily works against the other.
I didn't see anything in the Todo Wiki, so I figured I'd ask. :)
Thanks!
--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604
312-444-8534
sthomas@optionshouse.com
______________________________________________
See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Tablespace-derived stats?
2012-10-19 14:29 Tablespace-derived stats? Shaun Thomas <sthomas@optionshouse.com>
@ 2012-10-19 14:51 ` Tom Lane <tgl@sss.pgh.pa.us>
2012-10-19 14:54 ` Re: Tablespace-derived stats? Shaun Thomas <sthomas@optionshouse.com>
1 sibling, 1 reply; 6+ messages in thread
From: Tom Lane @ 2012-10-19 14:51 UTC (permalink / raw)
To: sthomas@optionshouse.com; +Cc: pgsql-performance
Shaun Thomas <sthomas@optionshouse.com> writes:
> Yet there's only one global setting for random_page_cost, and
> seq_page_cost, and so on.
We've had tablespace-specific settings for those for some time.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Tablespace-derived stats?
2012-10-19 14:29 Tablespace-derived stats? Shaun Thomas <sthomas@optionshouse.com>
2012-10-19 14:51 ` Re: Tablespace-derived stats? Tom Lane <tgl@sss.pgh.pa.us>
@ 2012-10-19 14:54 ` Shaun Thomas <sthomas@optionshouse.com>
0 siblings, 0 replies; 6+ messages in thread
From: Shaun Thomas @ 2012-10-19 14:54 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
On 10/19/2012 09:51 AM, Tom Lane wrote:
> We've had tablespace-specific settings for those for some time.
Ah, my apologies. I didn't see any in the CREATE TABLESPACE page, and
didn't think to check ALTER TABLESPACE.
I withdraw my question. :)
--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604
312-444-8534
sthomas@optionshouse.com
______________________________________________
See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Tablespace-derived stats?
2012-10-19 14:29 Tablespace-derived stats? Shaun Thomas <sthomas@optionshouse.com>
@ 2012-10-19 15:05 ` Jeff Janes <jeff.janes@gmail.com>
2012-10-19 15:07 ` Re: Tablespace-derived stats? Shaun Thomas <sthomas@optionshouse.com>
1 sibling, 1 reply; 6+ messages in thread
From: Jeff Janes @ 2012-10-19 15:05 UTC (permalink / raw)
To: sthomas@optionshouse.com; +Cc: pgsql-performance
On Fri, Oct 19, 2012 at 7:29 AM, Shaun Thomas <sthomas@optionshouse.com> wrote:
> Hello Perf,
>
> Lately I've been pondering. As systems get more complex, it's not uncommon
> for tiered storage to enter the picture. Say for instance, a user has some
> really fast tables on a NVRAM-based device, and slower-access stuff on a
> RAID, even slower stuff on an EDB, and variants like local disk or a RAM
> drive.
>
> Yet there's only one global setting for random_page_cost, and seq_page_cost,
> and so on.
>
> Would there be any benefit at all to adding these as parameters to the
> tablespaces themselves?
Been done already:
http://www.postgresql.org/docs/9.0/static/sql-altertablespace.html
Cheers,
Jeff
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Tablespace-derived stats?
2012-10-19 14:29 Tablespace-derived stats? Shaun Thomas <sthomas@optionshouse.com>
2012-10-19 15:05 ` Re: Tablespace-derived stats? Jeff Janes <jeff.janes@gmail.com>
@ 2012-10-19 15:07 ` Shaun Thomas <sthomas@optionshouse.com>
2012-10-19 15:26 ` Re: Tablespace-derived stats? Jeff Janes <jeff.janes@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Shaun Thomas @ 2012-10-19 15:07 UTC (permalink / raw)
To: Jeff Janes <jeff.janes@gmail.com>; +Cc: pgsql-performance
On 10/19/2012 10:05 AM, Jeff Janes wrote:
> http://www.postgresql.org/docs/9.0/static/sql-altertablespace.html
Yep. I realized my error was not checking the ALTER page after going
through CREATE. I swore I remembered seeing it in the past, but was
surprised it wasn't there.
I keep forgetting Postgres prefers a CREATE + ALTER style than
overloading every CREATE with all ALTER options. Though in my opinion
that just adds extra unnecessary steps.
--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604
312-444-8534
sthomas@optionshouse.com
______________________________________________
See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Tablespace-derived stats?
2012-10-19 14:29 Tablespace-derived stats? Shaun Thomas <sthomas@optionshouse.com>
2012-10-19 15:05 ` Re: Tablespace-derived stats? Jeff Janes <jeff.janes@gmail.com>
2012-10-19 15:07 ` Re: Tablespace-derived stats? Shaun Thomas <sthomas@optionshouse.com>
@ 2012-10-19 15:26 ` Jeff Janes <jeff.janes@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Jeff Janes @ 2012-10-19 15:26 UTC (permalink / raw)
To: sthomas@optionshouse.com; +Cc: pgsql-performance
On Fri, Oct 19, 2012 at 8:07 AM, Shaun Thomas <sthomas@optionshouse.com> wrote:
> On 10/19/2012 10:05 AM, Jeff Janes wrote:
>
>> http://www.postgresql.org/docs/9.0/static/sql-altertablespace.html
>
>
> Yep. I realized my error was not checking the ALTER page after going through
> CREATE. I swore I remembered seeing it in the past, but was surprised it
> wasn't there.
When I didn't see it under CREATE, I went to the docs page for the
global page_cost settings, and that page directed me to the ALTER for
the tablespace specific ones. It does seem like a statement in the
CREATION page indicating that more options are available only via the
ALTER of an already existing tablespace would be beneficial.
> I keep forgetting Postgres prefers a CREATE + ALTER style than overloading
> every CREATE with all ALTER options. Though in my opinion that just adds
> extra unnecessary steps.
I was surprised by the absence of the option in CREATE. I didn't
recognize that as a general pgsql pattern though, I just thought it
was peculiar to tablespaces. But I haven't surveyed the universe of
create and alter commands.
Cheers,
Jeff
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2012-10-19 15:26 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-10-19 14:29 Tablespace-derived stats? Shaun Thomas <sthomas@optionshouse.com>
2012-10-19 14:51 ` Tom Lane <tgl@sss.pgh.pa.us>
2012-10-19 14:54 ` Shaun Thomas <sthomas@optionshouse.com>
2012-10-19 15:05 ` Jeff Janes <jeff.janes@gmail.com>
2012-10-19 15:07 ` Shaun Thomas <sthomas@optionshouse.com>
2012-10-19 15:26 ` Jeff Janes <jeff.janes@gmail.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