Re: [HACKERS] [pgsql-patches] [PATCHES] Patch to log usage of temporary files
In response to "Guillaume Smet" <[EMAIL PROTECTED]>: > On 1/12/07, Bruce Momjian <[EMAIL PROTECTED]> wrote: > > Usually people don't want th query unless they ask for it. One nify > > trick would be to print the query as DETAIL unless they are already > > logging queries, but that just seems too complex. If you want the > > query, why not just log them all? > > Because they can't? On a highly loaded production server, people > usually don't log all the queries. > > Anyway, if it's too complicated to implement it, perhaps it's not > worth it. I'm just curious to see how people will use this information > if they don't know why the temp file was created. I can only speak for myself but: * I'm already using the patch in our lab. Since the lab is the same hardware/config/etc as production, I can use the information to fine tune configs that then get migrated to production after careful testing. Since it's a lab environment, I'm free to turn on and off all sorts of stuff that would be unwise in production. Thus the lab frequently has full query logging turned on. * Currently, our production systems have plenty of spare IO. The result is that I _do_ log queries on production servers, and will continue to do so until it becomes an issue. Additionally, we have lots of room to grow with this hardware, so I can use the data collected about temp file usage to justify additional RAM. Don't know how long I'll be able to leave query logging enabled on the production systems, but I'm taking advantage of it as long as possible. * This variable can be tweaked per-session, which means if I've got queries that I suspect are causing unwarranted temp files on a production server, I can enable it on a per-connection basis to track down the problem and work on a specific query, on production systems, without too much disruption of the rest of the work that's going on: set log_temp_files = 0; set log_temp_files = -1; At least, those are my current plans ... -- Bill Moran Collaborative Fusion Inc. ---(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] [pgsql-patches] [PATCHES] Patch to log usage of
Tom Lane wrote: > "Guillaume Smet" <[EMAIL PROTECTED]> writes: > > On 1/12/07, Tom Lane <[EMAIL PROTECTED]> wrote: > >> (2) there is already a generalized solution to this, it's called > >> log_min_error_statement. > > > I didn't think of that when posting my message but Bruce seems to say > > that we can't use it in this case. > > Dunno why he thinks that. But there is a point here that could use > improvement: shouldn't log_min_error_statement be measured on the same > scale as log_min_messages, ie, LOG is relatively high priority rather > than relatively low priority? As the code stands, you'd have to knock > it down to DEBUG1 in order to see the statement generating a LOG > message. This might be harmless (since messages below log_min_messages > won't generate log output at all), but it's surely a bit confusing. I assume log_min_error_messages wasn't supported because it isn't listed in the postgresql.conf file as a valid value. Let me look at adding LOG in there in the place you suggest. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [pgsql-patches] [PATCHES] Patch to log usage of temporary
Guillaume Smet wrote: > On 1/12/07, Bruce Momjian <[EMAIL PROTECTED]> wrote: > > Usually people don't want th query unless they ask for it. One nify > > trick would be to print the query as DETAIL unless they are already > > logging queries, but that just seems too complex. If you want the > > query, why not just log them all? > > Because they can't? On a highly loaded production server, people > usually don't log all the queries. > > Anyway, if it's too complicated to implement it, perhaps it's not > worth it. I'm just curious to see how people will use this information > if they don't know why the temp file was created. We have to balance functionality and ease of use. That is the way I analyze these issue. We usually wait for a few people to request additional functionality of this type, and then figure out the cleanest way to implement it. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [pgsql-patches] [PATCHES] Patch to log usage of temporary files
"Guillaume Smet" <[EMAIL PROTECTED]> writes: > On 1/12/07, Tom Lane <[EMAIL PROTECTED]> wrote: >> (2) there is already a generalized solution to this, it's called >> log_min_error_statement. > I didn't think of that when posting my message but Bruce seems to say > that we can't use it in this case. Dunno why he thinks that. But there is a point here that could use improvement: shouldn't log_min_error_statement be measured on the same scale as log_min_messages, ie, LOG is relatively high priority rather than relatively low priority? As the code stands, you'd have to knock it down to DEBUG1 in order to see the statement generating a LOG message. This might be harmless (since messages below log_min_messages won't generate log output at all), but it's surely a bit confusing. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [pgsql-patches] [PATCHES] Patch to log usage of temporary files
On 1/12/07, Tom Lane <[EMAIL PROTECTED]> wrote: "Guillaume Smet" <[EMAIL PROTECTED]> writes: > That's not what I had in mind. I was asking if the text of the query > was available when logging the temp file usage. If so it could be good > to add a DETAIL line with it directly and systematically when logging > the temp file usage. (1) you could make that argument about *any* log message whatsoever. Yes, probably. The fact is I can't figure out how I will use this information if I don't know why the file was created but perhaps I should just wait this release and see how I can use it in a production environment. (2) there is already a generalized solution to this, it's called log_min_error_statement. I didn't think of that when posting my message but Bruce seems to say that we can't use it in this case. -- Guillaume ---(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] [pgsql-patches] [PATCHES] Patch to log usage of temporary files
On 1/12/07, Bruce Momjian <[EMAIL PROTECTED]> wrote: Usually people don't want th query unless they ask for it. One nify trick would be to print the query as DETAIL unless they are already logging queries, but that just seems too complex. If you want the query, why not just log them all? Because they can't? On a highly loaded production server, people usually don't log all the queries. Anyway, if it's too complicated to implement it, perhaps it's not worth it. I'm just curious to see how people will use this information if they don't know why the temp file was created. -- Guillaume ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [pgsql-patches] [PATCHES] Patch to log usage of temporary
Guillaume Smet wrote: > Hi Bruce, > > Thanks for your answer. > > On 1/12/07, Bruce Momjian <[EMAIL PROTECTED]> wrote: > > We have the ability to conditionally print statements based on error > > level, but LOG isn't a valid level for log_min_error_statement. > > > > We could add a parameter that few people would use, but the right way to > > do this is to log all queries. > > > > We do not allow unlimited logging control or the system would be too > > complex. > > That's not what I had in mind. I was asking if the text of the query > was available when logging the temp file usage. If so it could be good > to add a DETAIL line with it directly and systematically when logging > the temp file usage. > > Sure that if you log every query, you could find which query was > responsible for that temp file but I suspect that this new log > feature will be used on production servers and so usually without > statement logging enabled. > > IMHO, it's really important to know which queries are responsible for > the temp file usage. > > If the text of the query is not available when logging the temp file > usage then I agree that we cannot do anything. Usually people don't want th query unless they ask for it. One nify trick would be to print the query as DETAIL unless they are already logging queries, but that just seems too complex. If you want the query, why not just log them all? -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.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] [pgsql-patches] [PATCHES] Patch to log usage of temporary files
"Guillaume Smet" <[EMAIL PROTECTED]> writes: > That's not what I had in mind. I was asking if the text of the query > was available when logging the temp file usage. If so it could be good > to add a DETAIL line with it directly and systematically when logging > the temp file usage. (1) you could make that argument about *any* log message whatsoever. (2) there is already a generalized solution to this, it's called log_min_error_statement. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [pgsql-patches] [PATCHES] Patch to log usage of temporary files
Hi Bruce, Thanks for your answer. On 1/12/07, Bruce Momjian <[EMAIL PROTECTED]> wrote: We have the ability to conditionally print statements based on error level, but LOG isn't a valid level for log_min_error_statement. We could add a parameter that few people would use, but the right way to do this is to log all queries. We do not allow unlimited logging control or the system would be too complex. That's not what I had in mind. I was asking if the text of the query was available when logging the temp file usage. If so it could be good to add a DETAIL line with it directly and systematically when logging the temp file usage. Sure that if you log every query, you could find which query was responsible for that temp file but I suspect that this new log feature will be used on production servers and so usually without statement logging enabled. IMHO, it's really important to know which queries are responsible for the temp file usage. If the text of the query is not available when logging the temp file usage then I agree that we cannot do anything. -- Guillaume ---(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] [pgsql-patches] [PATCHES] Patch to log usage of temporary
Guillaume Smet wrote: > Hi all, > > Sorry for arriving so late into the discussion. > > I don't know if it's possible but it could be useful to have the text > of the query which required the creation of the temporary files as an > additional DETAIL line. At least, if it's possible to have it in this > part of the code. > > Thoughts? We have the ability to conditionally print statements based on error level, but LOG isn't a valid level for log_min_error_statement. We could add a parameter that few people would use, but the right way to do this is to log all queries. We do not allow unlimited logging control or the system would be too complex. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend