Re: per table random-page-cost?
От | Robert Haas |
---|---|
Тема | Re: per table random-page-cost? |
Дата | |
Msg-id | 603c8f070910230523x2c7a772aifc56039847c3f177@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: per table random-page-cost? (Cédric Villemain <cedric.villemain@dalibo.com>) |
Ответы |
Re: per table random-page-cost?
(Cédric Villemain <cedric.villemain@dalibo.com>)
|
Список | pgsql-hackers |
On Fri, Oct 23, 2009 at 5:23 AM, Cédric Villemain <cedric.villemain@dalibo.com> wrote: > Le vendredi 23 octobre 2009 01:08:15, Joshua D. Drake a écrit : >> On Thu, 2009-10-22 at 11:33 -0400, Robert Haas wrote: >> > On Thu, Oct 22, 2009 at 11:16 AM, Cédric Villemain >> > >> > <cedric.villemain@dalibo.com> wrote: >> > > Le lundi 19 octobre 2009 23:27:20, Greg Stark a écrit : >> > >> On Mon, Oct 19, 2009 at 2:08 PM, marcin mank <marcin.mank@gmail.com> > wrote: >> > >> > Currently random_page_cost is a GUC. I propose that this could be >> > >> > set per-table. >> > >> >> > >> Or per-tablespace. >> > >> >> > >> Yes, I think there are a class of GUCs which describe the physical >> > >> attributes of the storage system which should be per-table or >> > >> per-tablespace. random_page_cost, sequential_page_cost, >> > >> effective_io_concurrency come to mind. >> > > >> > > and, perhaps effective_cache_size. >> > > >> > > You can have situation where you don't want some tables go to OS memory >> > > (you can disabled that at filesystem level, ... l'd like to be able to >> > > do that at postgres level but it is another point) >> > > >> > > So you put those tables in a separate tablespace, and tell postgresql >> > > that the effective_cache_size is 0 (for this tablespace), up to >> > > postgres to do the right thing with that ;) >> > >> > Why would you ever want to set effective_cache_size to 0? >> >> I think this is a misunderstanding of how effective_cache_size works. I >> can't think of any reason to do that. I could see a reason to tell the >> OS to not throw a relation into cache but that is a different thing. > > Well the effective_cache_size in this context is OS cache memory (0 in my case) > + estimation of shared_buffer.. ah so DBA should estimate the amount in the > shared_buffer only, ok. > > So consider effective_cache_size = 0 + what pg_buffer_cache will tell. > > My case is a table containing 29 GB of bytea in a database of 52 GB. Every row > on the 29GB table is grab only few times. And it will just renew OS cache > memory every time (the server have only 8GB of ram). > So when I remove this table (not the index) from the OS cache memory, I keep > more interesting blocks in the OS cache memory. > > And disk + raid are quick enought to bypass the OS cache memory for this > tablespace. > > > Are things a bit clearer and usage not so silly ? Well, I think you're vastly overestimating the power of effective_cache_size. effective_cache_size changes the planner's estimation of how likely a repeated partial index scan is to find the same block in cache. So it only affects nested-loop-with-inner-indexscan plans, and if effective_cache_size is set to a value larger than the size of the index (or maybe the relation, I'm too lazy to go reread the code right now), one value is as good as another. For a typical user, I think you could set effective_cache_size to, say, a terabyte, and it wouldn't make a bit of difference. Heck, why not 2TB. As far as I can see, the only possible value of setting this knob to a value other than positive-infinity is that if you have a huge dataset that's not close to fitting in memory, this might cause the planner to pick a merge join over a nested loop with inner indexscan, which might be better if it makes the I/O sequential rather than random. Anyone think I'm a pessimist? ...Robert
В списке pgsql-hackers по дате отправления:
Предыдущее
От: Richard HuxtonДата:
Сообщение: Re: Using views for row-level access control is leaky