Since sequential access is not significantly faster than random access in a
MMDB, random_page_cost will be approximately same as sequential page fetch cost.
As every thing is present in Main Memory, we need to give approximately same
cost to read/write to Main Memory and CPU Related operations.
But, in PostgreSQL all costs are scaled relative to a page fetch. If we make both
sequential_page_fetch_cost and random_page_cost to "1", then we need to
increase the various cpu_* paramters by multiplying the default values with appropriate
Scaling Factor. Now, we need to determine this Scaling Factor.
Still, i want to confirm whether this approach is the correct one.
On Sun, 11 Dec 2005, Josh Berkus wrote:
Anjan,
In our case we are reading pages from Main Memory File System, but not from
Disk. Will it be sufficient, if we change the default values of above
paramters in "src/include/optimizer/cost.h and
src/backend/utils/misc/postgresql.conf.sample" as follows:
random_page_cost = 4;
This should be dramatically lowered. It's supposed to represent the ratio of
seek-fetches to seq scans on disk. Since there's no disk, it should be a
flat 1.0. However, we are aware that there are flaws in our calculations
involving random_page_cost, such that the actual number for a system where
there is no disk cost would be lower than 1.0. Your research will hopefully
help us find these flaws.
cpu_tuple_cost = 2;
cpu_index_tuple_cost = 0.2;
cpu_operator_cost = 0.05;
I don't see why you're increasing the various cpu_* costs. CPU costs would be
unaffected by the database being in memory. In general, I lower these by a
divisor based on the cpu speed; for example, on a dual-opteron system I lower
the defaults by /6. However, that's completely unrelated to using an MMDB.
So, other than random_page_cost, I don't know of other existing GUCs that
would be directly related to using a disk/not using a disk. How are you
handling shared memory and work memory?
I look forward to hearing more about your test!
--
Regards.
Anjan Kumar A.
MTech2, Comp Sci.,
www.cse.iitb.ac.in/~anjankumar
______________________________________________________________
Do not handicap your children by making their lives easy.
-- Robert Heinlein
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
