Re: [HACKERS] [PATCHES] Proposed patch: synchronized_scanning GUC variable
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: OK, but keep in mind if we use synchronized_seqscans in pg_dump we will have to recognize that GUC forever. No, because it's being used on the query side, not in the emitted dump. We have *never* promised that pg_dump version N could dump from server version N+1 .., in fact, personally I'd like to make that case be a hard error, rather than something people could override with -i. Oh, yea, interesting, it is part of the connection. That will help. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [PATCHES] Proposed patch: synchronized_scanning GUC variable
Tom Lane wrote: in fact, personally I'd like to make that case be a hard error, rather than something people could override with -i. +1 to this idea. TODO for 8.4? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] Proposed patch: synchronized_scanning GUC variable
On Thu, 2008-01-31 at 11:20 -0300, Alvaro Herrera wrote: Tom Lane wrote: in fact, personally I'd like to make that case be a hard error, rather than something people could override with -i. +1 to this idea. TODO for 8.4? -1 without some more planning about the effects and implications. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: {**Spam**} Re: [HACKERS] [PATCHES] Proposed patch: synchronized_scanning GUC variable
Hi, Le jeudi 31 janvier 2008, Tom Lane a écrit : We have *never* promised that pg_dump version N could dump from server version N+1 .., in fact, personally I'd like to make that case be a hard error, rather than something people could override with -i. Are you thinking about next major or minor version ? All the same? Is there some real good reason not to dump say 8.2.6 server with the pg_dump from an 8.2.5 installation? -- dim signature.asc Description: This is a digitally signed message part.
Re: {**Spam**} Re: [HACKERS] [PATCHES] Proposed patch: synchronized_scanning GUC variable
Dimitri Fontaine wrote: -- Start of PGP signed section. Hi, Le jeudi 31 janvier 2008, Tom Lane a ?crit?: We have *never* promised that pg_dump version N could dump from server version N+1 .., in fact, personally I'd like to make that case be a hard error, rather than something people could override with -i. Are you thinking about next major or minor version ? All the same? Is there some real good reason not to dump say 8.2.6 server with the pg_dump from an 8.2.5 installation? They are talking about cross-major dumps, 8.2 pg_dump dumping an 8.3 database. They are saying that should be disallowed. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: {**Spam**} Re: [HACKERS] [PATCHES] Proposed patch: synchronized_scanning GUC variable
Le jeudi 31 janvier 2008, Tom Lane a écrit : I'm thinking next major. In principle there could be cases where a minor update could break pg_dump, but it seems unlikely enough that it's not reasonable to embed such a policy in the code. As for next major, though, the mere existence of the -i switch is a foot-gun with no significant value. +2 then :) 1. Current behavior is to issue the « -i warning » even when having minor version mismatch, getting rid of this would be great... even more so now we know there's almost no risk here. 2. Major version mismatch seems to be one of the major migration pitfalls out there. The famous You have to dump with the newer pg_dump version against the current production setup cookbook entry will certainly be better embedded into the code. Regards, -- dim signature.asc Description: This is a digitally signed message part.
Re: {**Spam**} Re: [HACKERS] [PATCHES] Proposed patch: synchronized_scanning GUC variable
Dimitri Fontaine [EMAIL PROTECTED] writes: Le jeudi 31 janvier 2008, Tom Lane a écrit : We have *never* promised that pg_dump version N could dump from server version N+1 .., in fact, personally I'd like to make that case be a hard error, rather than something people could override with -i. Are you thinking about next major or minor version ? All the same? Is there some real good reason not to dump say 8.2.6 server with the pg_dump from an 8.2.5 installation? I'm thinking next major. In principle there could be cases where a minor update could break pg_dump, but it seems unlikely enough that it's not reasonable to embed such a policy in the code. As for next major, though, the mere existence of the -i switch is a foot-gun with no significant value. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: {**Spam**} Re: [HACKERS] [PATCHES] Proposed patch: synchronized_scanning GUC variable
Bruce Momjian [EMAIL PROTECTED] writes: Dimitri Fontaine wrote: -- Start of PGP signed section. Hi, Le jeudi 31 janvier 2008, Tom Lane a ?crit?: We have *never* promised that pg_dump version N could dump from server version N+1 .., in fact, personally I'd like to make that case be a hard error, rather than something people could override with -i. Are you thinking about next major or minor version ? All the same? Is there some real good reason not to dump say 8.2.6 server with the pg_dump from an 8.2.5 installation? They are talking about cross-major dumps, 8.2 pg_dump dumping an 8.3 database. They are saying that should be disallowed. And just to be clearly *only* cross-major dumps with an *older* pg_dump than the database. Dumping with a newer pg_dump than the database is the recommended way to do cross-major dumps. Perhaps we can have something like --force-unsupported-incompatible-connections 1/2 :) -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's PostGIS support! ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] Proposed patch: synchronized_scanning GUC variable
On Mon, 2008-01-28 at 16:21 -0500, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: Rather than having a boolean GUC, we should have a number and make the parameter synchronised_scan_threshold. This would open up a can of worms I'd prefer not to touch, having to do with whether the buffer-access-strategy behavior should track that or not. As the note in heapam.c says, * If the table is large relative to NBuffers, use a bulk-read access * strategy and enable synchronized scanning (see syncscan.c). Although * the thresholds for these features could be different, we make them the * same so that there are only two behaviors to tune rather than four. It's a bit late in the cycle to be revisiting that choice. Now we do already have three behaviors to worry about (BAS on and syncscan off) but throwing in a randomly settable knob will take it back to four, and we have no idea how that fourth case will behave. The other tack we could take (having the one GUC variable control both thresholds) is not good since it will result in pg_dump trashing the buffer cache. I'm still not very happy with any of the options here. BAS is great if you didn't want to trash the cache, but its also annoying to people that really did want to load a large table into cache. However we set it, we're going to have problems because not everybody has the same database. We're trying to guess which data is in memory and which is on disk and then act accordinly. The answer to that question cannot be answered solely by how big shared_buffers is. It really ought to be a combination of (at least) shared_buffers and total database size. I think we must either put some more intelligence into the setting of the threshold, or give it to the user as a parameter, possibly as a parameter not mentioned in the sample .conf. If we set the threshold unintelligently or in a way that cannot be overridden, we will still get weird bug reports from people who set shared_buffers higher and got a performance drop. We need to make a final decision on this quickly, so I'll say no more on this for 8.3 to help that process. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [PATCHES] Proposed patch: synchronized_scanning GUC variable
Simon Riggs [EMAIL PROTECTED] writes: I'm still not very happy with any of the options here. BAS is great if you didn't want to trash the cache, but its also annoying to people that really did want to load a large table into cache. However we set it, we're going to have problems because not everybody has the same database. That argument leads immediately to the conclusion that you need per-table control over the behavior. Which maybe you do, but it's far too late to be proposing it for 8.3. We should put this whole area of more-control-over-BAS-and-syncscan on the TODO agenda. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] Proposed patch: synchronized_scanning GUC variable
Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: I'm still not very happy with any of the options here. BAS is great if you didn't want to trash the cache, but its also annoying to people that really did want to load a large table into cache. However we set it, we're going to have problems because not everybody has the same database. That argument leads immediately to the conclusion that you need per-table control over the behavior. It's even worse than that. Elsewhere in this thread Simon mentioned a partitioned table, where each partition on its own is smaller than the threshold, but you're seq scanning several partitions and the total size of the seq scans is larger than memory size. In that scenario, you would want BAS and synchronized scans, but even a per-table setting wouldn't cut it. One idea would be to look at the access plan and estimate the total size of all scans in the plan. Another idea would be to switch to a more seq scan resistant cache replacement algorithm, and forget about the threshold. For synchronized scans to help in the partitioned situation, I guess you'd want to synchronize across partitions. If someone is already scanning partition 5, you'd want to start from that partition and join the pack, instead of starting from partition 1. I think we'll be wiser after we see some real world use of what we have there now.. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [PATCHES] Proposed patch: synchronized_scanning GUC variable
On Wed, 2008-01-30 at 18:42 +, Heikki Linnakangas wrote: Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: I'm still not very happy with any of the options here. BAS is great if you didn't want to trash the cache, but its also annoying to people that really did want to load a large table into cache. However we set it, we're going to have problems because not everybody has the same database. That argument leads immediately to the conclusion that you need per-table control over the behavior. It's even worse than that. Elsewhere in this thread Simon mentioned a partitioned table, where each partition on its own is smaller than the threshold, but you're seq scanning several partitions and the total size of the seq scans is larger than memory size. In that scenario, you would want BAS and synchronized scans, but even a per-table setting wouldn't cut it. For synchronized scans to help in the partitioned situation, I guess you'd want to synchronize across partitions. If someone is already scanning partition 5, you'd want to start from that partition and join the pack, instead of starting from partition 1. You're right, but in practice its not quite that bad with the multi-table route. When you have partitions you generally exclude most of them, with typically 1-2 per query, usually different ones. If you were scanning lots of partitions in sequence so frequently that you'd get benefit from synch scans then your partitioning scheme isn't working for you - and that is the worst problem by far. But yes, it does need to be addressed. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [PATCHES] Proposed patch: synchronized_scanning GUC variable
Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: I'm still not very happy with any of the options here. BAS is great if you didn't want to trash the cache, but its also annoying to people that really did want to load a large table into cache. However we set it, we're going to have problems because not everybody has the same database. That argument leads immediately to the conclusion that you need per-table control over the behavior. Which maybe you do, but it's far too late to be proposing it for 8.3. We should put this whole area of more-control-over-BAS-and-syncscan on the TODO agenda. Another question --- why don't we just turn off synchronized_seqscans when we do COPY TO? That would fix pg_dump and be transparent. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [PATCHES] Proposed patch: synchronized_scanning GUC variable
Bruce Momjian wrote: Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: I'm still not very happy with any of the options here. BAS is great if you didn't want to trash the cache, but its also annoying to people that really did want to load a large table into cache. However we set it, we're going to have problems because not everybody has the same database. That argument leads immediately to the conclusion that you need per-table control over the behavior. Which maybe you do, but it's far too late to be proposing it for 8.3. We should put this whole area of more-control-over-BAS-and-syncscan on the TODO agenda. Another question --- why don't we just turn off synchronized_seqscans when we do COPY TO? That would fix pg_dump and be transparent. Sorry, I was unclear. I meant don't have a GUC at all but just set an internal variable to turn off synchronized sequential scans when we do COPY TO. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] Proposed patch: synchronized_scanning GUC variable
Bruce Momjian [EMAIL PROTECTED] writes: Another question --- why don't we just turn off synchronized_seqscans when we do COPY TO? That would fix pg_dump and be transparent. Enforcing this from the server side seems a pretty bad idea. Note that there were squawks about having pg_dump behave this way at all; if the control is on the pg_dump side then at least we have the chance to make it a user option later. Also, you forgot about pg_dump -d. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [PATCHES] Proposed patch: synchronized_scanning GUC variable
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Another question --- why don't we just turn off synchronized_seqscans when we do COPY TO? That would fix pg_dump and be transparent. Enforcing this from the server side seems a pretty bad idea. Note that there were squawks about having pg_dump behave this way at all; if the control is on the pg_dump side then at least we have the chance to make it a user option later. Also, you forgot about pg_dump -d. OK, but keep in mind if we use synchronized_seqscans in pg_dump we will have to recognize that GUC forever. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [PATCHES] Proposed patch: synchronized_scanning GUC variable
Bruce Momjian [EMAIL PROTECTED] writes: OK, but keep in mind if we use synchronized_seqscans in pg_dump we will have to recognize that GUC forever. No, because it's being used on the query side, not in the emitted dump. We have *never* promised that pg_dump version N could dump from server version N+1 .., in fact, personally I'd like to make that case be a hard error, rather than something people could override with -i. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [PATCHES] Proposed patch: synchronized_scanning GUC variable
I liked the synchronized_sequential_scans idea myself. I think that's a bit too long. How about synchronized_scans, or synchronized_seqscans? We have enable_seqscan already, so that last choice seems to fit in. Yes looks good, how about synchronized_seqscan without plural ? Andreas ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [PATCHES] Proposed patch: synchronized_scanning GUC variable
On Sun, 2008-01-27 at 21:04 -0500, Tom Lane wrote: [ redirecting thread to -hackers ] Neil Conway [EMAIL PROTECTED] writes: On Sun, 2008-01-27 at 21:54 +, Gregory Stark wrote: I liked the synchronized_sequential_scans idea myself. I think that's a bit too long. How about synchronized_scans, or synchronized_seqscans? We have enable_seqscan already, so that last choice seems to fit in. If we're going to have a GUC, we may as well make it as useful as possible. Currently we set synch scan on when the table is larger than 25% of shared_buffers. So increasing shared_buffers can actually turn this feature off. Rather than having a boolean GUC, we should have a number and make the parameter synchronised_scan_threshold. This would then be the size of a table above which we would perform synch scans. If its set to -1, then this would be the same as off in all cases. The default value would be 25% of shared_buffers. (Think we can only do that at initdb time currently). If we do that, its clearly different from the enable_* parameters, so the name is easier to decide ;-) -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [PATCHES] Proposed patch: synchronized_scanning GUC variable
Simon Riggs [EMAIL PROTECTED] writes: Rather than having a boolean GUC, we should have a number and make the parameter synchronised_scan_threshold. This would open up a can of worms I'd prefer not to touch, having to do with whether the buffer-access-strategy behavior should track that or not. As the note in heapam.c says, * If the table is large relative to NBuffers, use a bulk-read access * strategy and enable synchronized scanning (see syncscan.c). Although * the thresholds for these features could be different, we make them the * same so that there are only two behaviors to tune rather than four. It's a bit late in the cycle to be revisiting that choice. Now we do already have three behaviors to worry about (BAS on and syncscan off) but throwing in a randomly settable knob will take it back to four, and we have no idea how that fourth case will behave. The other tack we could take (having the one GUC variable control both thresholds) is not good since it will result in pg_dump trashing the buffer cache. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [PATCHES] Proposed patch: synchronized_scanning GUC variable
On Jan 28, 2008, at 6:14 PM, Simon Riggs wrote: On Sun, 2008-01-27 at 21:04 -0500, Tom Lane wrote: [ redirecting thread to -hackers ] Neil Conway [EMAIL PROTECTED] writes: On Sun, 2008-01-27 at 21:54 +, Gregory Stark wrote: I liked the synchronized_sequential_scans idea myself. I think that's a bit too long. How about synchronized_scans, or synchronized_seqscans? We have enable_seqscan already, so that last choice seems to fit in. If we're going to have a GUC, we may as well make it as useful as possible. Currently we set synch scan on when the table is larger than 25% of shared_buffers. So increasing shared_buffers can actually turn this feature off. Rather than having a boolean GUC, we should have a number and make the parameter synchronised_scan_threshold. This would then be the size of a table above which we would perform synch scans. If its set to -1, then this would be the same as off in all cases. The default value would be 25% of shared_buffers. (Think we can only do that at initdb time currently). If we do that, its clearly different from the enable_* parameters, so the name is easier to decide ;-) +1 This is in fact a lot more flexible and transparent. It gives us a lot more control over the process and it is easy to explain / understand. best regards, hans -- Cybertec Schönig Schönig GmbH PostgreSQL Solutions and Support Gröhrmühlgasse 26, 2700 Wiener Neustadt Tel: +43/1/205 10 35 / 340 www.postgresql.at, www.cybertec.at
Re: [HACKERS] [PATCHES] Proposed patch: synchronized_scanning GUC variable
On Mon, 2008-01-28 at 16:21 -0500, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: Rather than having a boolean GUC, we should have a number and make the parameter synchronised_scan_threshold. This would open up a can of worms I'd prefer not to touch, having to do with whether the buffer-access-strategy behavior should track that or not. As the note in heapam.c says, * If the table is large relative to NBuffers, use a bulk-read access * strategy and enable synchronized scanning (see syncscan.c). Although * the thresholds for these features could be different, we make them the * same so that there are only two behaviors to tune rather than four. It's a bit late in the cycle to be revisiting that choice. Now we do already have three behaviors to worry about (BAS on and syncscan off) but throwing in a randomly settable knob will take it back to four, and we have no idea how that fourth case will behave. The other tack we could take (having the one GUC variable control both thresholds) is not good since it will result in pg_dump trashing the buffer cache. OK, good points. I'm still concerned that the thresholds gets higher as we increase shared_buffers. We may be removing performance features as fast as we gain performance when we set shared_buffers higher. Might we agree that the threshold should be fixed at 8MB, rather than varying upwards as we try to tune? The objective of having a tuning hook would have been simply to normalise the effects of increasing shared_buffers anyway, so fixing it would solve the problem I see. (8MB is the default, based upon 25% of 32MB). -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] Proposed patch: synchronized_scanning GUC variable
[ redirecting thread to -hackers ] Neil Conway [EMAIL PROTECTED] writes: On Sun, 2008-01-27 at 21:54 +, Gregory Stark wrote: I liked the synchronized_sequential_scans idea myself. I think that's a bit too long. How about synchronized_scans, or synchronized_seqscans? We have enable_seqscan already, so that last choice seems to fit in. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] Proposed patch: synchronized_scanning GUC variable
On Jan 27, 2008, at 21:04 , Tom Lane wrote: [ redirecting thread to -hackers ] Neil Conway [EMAIL PROTECTED] writes: On Sun, 2008-01-27 at 21:54 +, Gregory Stark wrote: I liked the synchronized_sequential_scans idea myself. I think that's a bit too long. How about synchronized_scans, or synchronized_seqscans? We have enable_seqscan already, so that last choice seems to fit in. Would it make sense to match the plural as well? Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] Proposed patch: synchronized_scanning GUC variable
Michael Glaesemann [EMAIL PROTECTED] writes: Neil Conway [EMAIL PROTECTED] writes: I think that's a bit too long. How about synchronized_scans, or synchronized_seqscans? Would it make sense to match the plural as well? Actually, the best suggestion I've seen so far is Guillaume's synchronize_seqscans --- make it a verb phrase. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
