Re: [HACKERS] Quick question regarding tablespaces
On Thu, 1 Jul 2004 22:55:56 -0400, Mike Rylander [EMAIL PROTECTED] wrote: I was thinking of purely tablespace-based random_page_cost, as that variable is tied to the access time of a particular filesystem. Strictly speaking we'd also need tablespace-based sequential_page_cost. Servus Manfred ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Quick question regarding tablespaces
On Thursday 01 July 2004 09:26 pm, Scott Marlowe wrote: On Thu, 2004-07-01 at 18:54, Gavin Sherry wrote: On Thu, 1 Jul 2004, Mike Rylander wrote: On Thursday 01 July 2004 06:43 pm, Gavin Sherry wrote: Hi Mike, In this release, unfortunately not. That't too bad, but it's not that urgent I suppose. I had some idea early on of putting rand_page_cost in pg_tablespace and having the planner have access to it for costing. I didn't actually get around to it but. :-( Well, I haven't looked at the PG source before, but if you have some specific design ideas I would be glad to help out. I'm just not sure where (or when, with the official release coming (sort of) soon) to start, but with some pointers I'll do what I can! Well, it wont be in 7.5. Feel free to start looking at how random_page_cost in cost_index(). It might be worthwhile introducing a per tablespace performance factor so that we could could say that the cost of fetching an index tuple from tablespace A is half that of fetching an index tuple from tablespace B. That idea might not actually turn out to be a very good one once I look at it closely though. How about having a per cluster / database / tablespace / table type setup that goes in a hierarchy, if they're there. I.e. if the database doesn't have it's own random_page_cost, it inherits from cluster, if a tablespace doesn't have one, it inherits from cluster-database, and so on to individual tables / indexes. I was thinking of purely tablespace-based random_page_cost, as that variable is tied to the access time of a particular filesystem. It may be that it's easier to implement for them all now while doing it for tablespaces. Just wondering. I'm a user, not a hacker, so I have no idea how much that idea makes any sense, but I would certainly love to be able to set an index to have a random_page_cost effect of 1.1 while the table it lives in is 1.3, the tablespace 1.4, and so on. But not required, because it always inherits from the parent if it doesn't have one, like stats target. I have been thinking about something along the lines of a 'user_cost_push' index attribute. This would default to 1 (if not set) and would be multiplied against the cost of the plan node for the index to help or hurt the use of the index in cases where the planner consistently makes the wrong choice regarding the use of the index (due to funky stats, etc.). Though perhaps I am just thinking around the problem. I know there has been some pretty big work done on the stats collector recently. --miker ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Quick question regarding tablespaces
On Thursday 01 July 2004 08:54 pm, Gavin Sherry wrote: On Thu, 1 Jul 2004, Mike Rylander wrote: On Thursday 01 July 2004 06:43 pm, Gavin Sherry wrote: Hi Mike, In this release, unfortunately not. That't too bad, but it's not that urgent I suppose. I had some idea early on of putting rand_page_cost in pg_tablespace and having the planner have access to it for costing. I didn't actually get around to it but. :-( Well, I haven't looked at the PG source before, but if you have some specific design ideas I would be glad to help out. I'm just not sure where (or when, with the official release coming (sort of) soon) to start, but with some pointers I'll do what I can! Well, it wont be in 7.5. Feel free to start looking at how random_page_cost in cost_index(). I will start looking there. It might be worthwhile introducing a per tablespace performance factor so that we could could say that the cost of fetching an index tuple from tablespace A is half that of fetching an index tuple from tablespace B. As random_page_cost is tied directly to the performance of a filesystem, my thought was to leave the setting from the config file as a cluster-wide (and default tablespace) setting that would be overridden by a tablespace specific setting... i.e. ALTER TABLESPACE ... SET RANDOM PAGE COST x.x; or even setting a scaling factor that would shift the global random page cost. this scaling factor would be set on all tablespaces and would have a default of 1. Then it could be set lower ( 0.5 means that tablespace is 2 times faster than the default tablespace, or global setting). Is that more what your were thinking? That idea might not actually turn out to be a very good one once I look at it closely though. If the latter is what you were thinking, I tend to agree. But I think a direct setting for each tablespace would be a very big benefit. At least I'm pretty sure I would use it :) --miker Gavin ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Quick question regarding tablespaces
I would like to see some tool that reported an semi-accurate value for random page cost before adding the value per tablespace. --- Scott Marlowe wrote: On Thu, 2004-07-01 at 18:54, Gavin Sherry wrote: On Thu, 1 Jul 2004, Mike Rylander wrote: On Thursday 01 July 2004 06:43 pm, Gavin Sherry wrote: Hi Mike, In this release, unfortunately not. That't too bad, but it's not that urgent I suppose. I had some idea early on of putting rand_page_cost in pg_tablespace and having the planner have access to it for costing. I didn't actually get around to it but. :-( Well, I haven't looked at the PG source before, but if you have some specific design ideas I would be glad to help out. I'm just not sure where (or when, with the official release coming (sort of) soon) to start, but with some pointers I'll do what I can! Well, it wont be in 7.5. Feel free to start looking at how random_page_cost in cost_index(). It might be worthwhile introducing a per tablespace performance factor so that we could could say that the cost of fetching an index tuple from tablespace A is half that of fetching an index tuple from tablespace B. That idea might not actually turn out to be a very good one once I look at it closely though. How about having a per cluster / database / tablespace / table type setup that goes in a hierarchy, if they're there. I.e. if the database doesn't have it's own random_page_cost, it inherits from cluster, if a tablespace doesn't have one, it inherits from cluster-database, and so on to individual tables / indexes. It may be that it's easier to implement for them all now while doing it for tablespaces. Just wondering. I'm a user, not a hacker, so I have no idea how much that idea makes any sense, but I would certainly love to be able to set an index to have a random_page_cost effect of 1.1 while the table it lives in is 1.3, the tablespace 1.4, and so on. But not required, because it always inherits from the parent if it doesn't have one, like stats target. !DSPAM:40e4b98b142131356954127! -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] Quick question regarding tablespaces
Now that PG will have tablespaces I can stick my really high I/O data on a fiberchannel array, and save some money by putting the rest of it (also the majority of it) on less expensive SCSI RAID sets. Will I also be able to tune individual tablespaces with the likes of random_page_cost? Sorry if I missed this somewhere... TIA --miker ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Quick question regarding tablespaces
Hi Mike, In this release, unfortunately not. I had some idea early on of putting rand_page_cost in pg_tablespace and having the planner have access to it for costing. I didn't actually get around to it but. :-( Gavin On Mon, 28 Jun 2004, Mike Rylander wrote: Now that PG will have tablespaces I can stick my really high I/O data on a fiberchannel array, and save some money by putting the rest of it (also the majority of it) on less expensive SCSI RAID sets. Will I also be able to tune individual tablespaces with the likes of random_page_cost? Sorry if I missed this somewhere... TIA --miker ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] !DSPAM:40e47cf853041767292179! ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Quick question regarding tablespaces
On Thu, 1 Jul 2004, Mike Rylander wrote: On Thursday 01 July 2004 06:43 pm, Gavin Sherry wrote: Hi Mike, In this release, unfortunately not. That't too bad, but it's not that urgent I suppose. I had some idea early on of putting rand_page_cost in pg_tablespace and having the planner have access to it for costing. I didn't actually get around to it but. :-( Well, I haven't looked at the PG source before, but if you have some specific design ideas I would be glad to help out. I'm just not sure where (or when, with the official release coming (sort of) soon) to start, but with some pointers I'll do what I can! Well, it wont be in 7.5. Feel free to start looking at how random_page_cost in cost_index(). It might be worthwhile introducing a per tablespace performance factor so that we could could say that the cost of fetching an index tuple from tablespace A is half that of fetching an index tuple from tablespace B. That idea might not actually turn out to be a very good one once I look at it closely though. Gavin ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Quick question regarding tablespaces
On Thu, 2004-07-01 at 18:54, Gavin Sherry wrote: On Thu, 1 Jul 2004, Mike Rylander wrote: On Thursday 01 July 2004 06:43 pm, Gavin Sherry wrote: Hi Mike, In this release, unfortunately not. That't too bad, but it's not that urgent I suppose. I had some idea early on of putting rand_page_cost in pg_tablespace and having the planner have access to it for costing. I didn't actually get around to it but. :-( Well, I haven't looked at the PG source before, but if you have some specific design ideas I would be glad to help out. I'm just not sure where (or when, with the official release coming (sort of) soon) to start, but with some pointers I'll do what I can! Well, it wont be in 7.5. Feel free to start looking at how random_page_cost in cost_index(). It might be worthwhile introducing a per tablespace performance factor so that we could could say that the cost of fetching an index tuple from tablespace A is half that of fetching an index tuple from tablespace B. That idea might not actually turn out to be a very good one once I look at it closely though. How about having a per cluster / database / tablespace / table type setup that goes in a hierarchy, if they're there. I.e. if the database doesn't have it's own random_page_cost, it inherits from cluster, if a tablespace doesn't have one, it inherits from cluster-database, and so on to individual tables / indexes. It may be that it's easier to implement for them all now while doing it for tablespaces. Just wondering. I'm a user, not a hacker, so I have no idea how much that idea makes any sense, but I would certainly love to be able to set an index to have a random_page_cost effect of 1.1 while the table it lives in is 1.3, the tablespace 1.4, and so on. But not required, because it always inherits from the parent if it doesn't have one, like stats target. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
