[HACKERS] Autovacuum improvements
I've been thinnking how to improve autovacuum so that we can convince more people that it can be enabled by default. Here are my thoughts. There are two areas of improvements: 1. scheduling, and 2. process handling, i.e., how to have multiple vacuum processes running at any time. I ripped out the part about having multiple vacuum queues, as it was incomplete and it was also getting too complex. We need to discuss how to do that, because it's a fundamental part of this proposal; the idea is to be able to have several vacuums running at any time, but we need to find a way to specify a policy for it. Process Handling My idea here is to morph the current autovacuum daemon from an agent that itself runs a vacuum command, into something that launches other processes to run those vacuum commands. I'll call this the autovacuum launcher process, or the launcher for short. The idea here is that the launcher can take care of the scheduling while the worker processes do their work. If the launcher then determines that a particular instant there should be two vacuums running, then it simply starts two worker processes. The launcher would be running continuously, akin to the postmaster, but would be obviously under control of the latter, so it's postmaster's responsability to start and stop the launcher. The launcher would be connected to shared memory, so it can scan system catalogs to load the schedule (stored in system catalogs) into memory. If the launcher dies, the postmaster should treat it like any other process' crash and cause a restart cycle. The workers would not be postmaster's direct children, which could be a problem. I'm open to ideas here, but I don't like using the postmaster directly as a launcher, because of the shmem connection, which would take robustness away from the postmaster. One idea to solve this is to have the launcher process communicate child process IDs to the postmaster, so that when it (the postmaster) wants to stop, it has those additional PIDs in its process list and can signal them to stop. The launcher process would also signal when it detects that one of the workers stopped, and the postmaster would remove that process from the list. This communication could be made to happen via named pipes, and since the messages are so simple, there's no reliability concern for the postmaster; it's very easy to verify that a message is correct by checking whether the process is actually killable by kill(0). Another idea that I discarded was to have the launcher communicate back to the postmaster when new workers should be started. My fear is that this type of communication (a lot more complex that just sending a PID) could be a cause for postmaster instability. Scheduling == We introduce the following concepts: 1. table groups. We'll have a system catalog for storing OID and group name, and another catalog for membership, linking relid to group OID. pg_av_tablegroup tgrname name pg_av_tgroupmembers groupid oid relid oid 2. interval groups. We'll have a catalog for storing igroup name and OID, and another catalog for membership. We identify an interval by: - month of year - day of month - day of week - start time of day - end time of day This is modelled after crontabs. pg_av_intervalgroup igrnamename pg_av_igroupmembers groupidoid month int domint dowint starttime timetz endtimetimetz Additionally, we'll have another catalog on which we'll store table groups to interval groups relationships. On that catalog we'll also store those autovacuum settings that we want to be able to override: whether to disable it for this interval group, or the values for the vacuum/analyze equations. pg_av_schedule tgroup oid igroup oid enabledbool queue int vac_base_threshint vac_scale_factor float anl_base_threshint anl_scal_factorfloat vac_cost_delay int vac_cost_limit int freeze_min_age int freeze_max_age int So the scheduler, at startup, loads the whole schedule in memory, and then wakes up at reasonable intervals and checks whether these equations hold for some of the tables it's monitoring. If they do, then launch a new worker process to do the job. We need a mechanism for having the scheduler rescan the schedule when a user modifies the catalog -- maybe having a trigger that sends a signal to the process is good enough (implementation detail: the signal must be routed via the postmaster, since the backend cannot hope to know the scheduler's PID. This is easy enough to do.) -- Alvaro Herrera Developer, http://www.PostgreSQL.org/ The problem with the facetime model is not just that it's demoralizing, but that the people pretending to work
Re: [HACKERS] Autovacuum improvements
On Sunday 14 January 2007 05:18, Alvaro Herrera wrote: I've been thinnking how to improve autovacuum so that we can convince more people that it can be enabled by default. Here are my thoughts. There are two areas of improvements: 1. scheduling, and 2. process handling, i.e., how to have multiple vacuum processes running at any time. I ripped out the part about having multiple vacuum queues, as it was incomplete and it was also getting too complex. We need to discuss how to do that, because it's a fundamental part of this proposal; the idea is to be able to have several vacuums running at any time, but we need to find a way to specify a policy for it. 8 snip 8 So the scheduler, at startup, loads the whole schedule in memory, and then wakes up at reasonable intervals and checks whether these equations hold for some of the tables it's monitoring. If they do, then launch a new worker process to do the job. We need a mechanism for having the scheduler rescan the schedule when a user modifies the catalog -- maybe having a trigger that sends a signal to the process is good enough (implementation detail: the signal must be routed via the postmaster, since the backend cannot hope to know the scheduler's PID. This is easy enough to do.) While we are talking autovacuum improvements, I'd like to also see some better logging, something that is akin to the important information of vacuum verbose being logged to a table or baring that the error_log. I'd like to be able to see what was done, and how long it took to do for each relation touched by av. A thought, having this information may even be usefull for the above thought of scheduler because we may be able to build some sort of predictive scheduling into this. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Autovacuum improvements
While we are talking autovacuum improvements, I'd like to also see some better logging, something that is akin to the important information of vacuum verbose being logged to a table or baring that the error_log. I'd like to be able to see what was done, and how long it took to do for each relation touched by av. A thought, having this information may even be usefull for the above thought of scheduler because we may be able to build some sort of predictive scheduling into this. This plays back to the vacuum summary idea that I requested: http://archives.postgresql.org/pgsql-hackers/2005-07/msg00451.php (Man our new search engine is so much better than the old one :)) Joshua D. Drake ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [GENERAL] Autovacuum Improvements
Simon Riggs wrote: On Fri, 2006-12-29 at 20:25 -0300, Alvaro Herrera wrote: Christopher Browne wrote: Seems to me that you could get ~80% of the way by having the simplest 2 queue implementation, where tables with size some threshold get thrown at the little table queue, and tables above that size go to the big table queue. That should keep any small tables from getting vacuum-starved. This is exectly what I am trying, two process autovacuum and a GUC to seperate small tables. In this case, one process takes up vacuuming of the small tables and other process vacuuming of the remaining tables as well as Xid avoidance related vacuuming. The goal is to avoid starvation of small tables when a large table is being vacuumed (which may take several hours) without adding too much complexity to the code. Some feedback from initial testing is that 2 queues probably isn't enough. If you have tables with 100s of blocks and tables with millions of blocks, the tables in the mid-range still lose out. So I'm thinking that a design with 3 queues based upon size ranges, plus the idea that when a queue is empty it will scan for tables slightly above/below its normal range. That way we wouldn't need to specify the cut-offs with a difficult to understand new set of GUC parameters, define them exactly and then have them be wrong when databases grow. The largest queue would be the one reserved for Xid wraparound avoidance. No table would be eligible for more than one queue at a time, though it might change between queues as it grows. Alvaro, have you completed your design? Pavan, what are your thoughts? IMO 2-queue is a good step forward, but in long term we may need to go for a multiprocess autovacuum where the number and tasks of processes are either demand based and/or user configurable. Another idea is to vacuum the tables in round-robin fashion where the quantum could be either time or number of block. The autovacuum process would vacuum 'x' blocks of one table and then schedule next table in the queue. This would avoid starvation of small tables, though cost of index cleanup might go up because of increased IO. Any thoughts of this approach ? Thanks, Pavan ---(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] [COMMITTERS] pgsql: Add support for xmlval IS DOCUMENT expression.
[EMAIL PROTECTED] (Peter Eisentraut) writes: Log Message: --- Add support for xmlval IS DOCUMENT expression. I don't think I trust your reverse-listing of IS DOCUMENT; you need parens around the argument to be sure it is parsed the same way next time, if it's not a trivial expression. Or you could teach the pretty-print code the correct precedence rules, but this patch hasn't done that. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Autovacuum improvements
First, thanks for working on this. I hope to be helpful with the design discussion and possibly some coding if I can find the time. My initial reaction to this proposal is that it seems overly complex, however I don't see a more elegant solution. I'm a bit concerned that most users won't figure out all the knobs. Alvaro Herrera wrote: I've been thinking how to improve autovacuum so that we can convince more people that it can be enabled by default. I would like to see it enabled by default too, however the reason it isn't already enabled by default is that it caused failures in the regression test when we tried to turn it on during the 8.2 dev cycle and it was too close to beta to fix everything. All this new machinery is great, but it doesn't address that problem. Here are my thoughts. There are two areas of improvements: 1. scheduling, and 2. process handling, i.e., how to have multiple vacuum processes running at any time. Fail enough, but I would say the two biggest area for improvement are scheduling and preventing HOT tables from becoming vacuum starved (essentially what you said, but with a different emphasis). [snip] Process Handling My idea here is to morph the current autovacuum daemon from an agent that itself runs a vacuum command, into something that launches other processes to run those vacuum commands. I'll call this the autovacuum launcher process, or the launcher for short. The idea here is that the launcher can take care of the scheduling while the worker processes do their work. If the launcher then determines that a particular instant there should be two vacuums running, then it simply starts two worker processes. How about calling it the autovacuum_master process? [snip autovacuum launcher process description] That all sounds reasonable to me. I think the harder part is what you are getting at below (how to get the launcher to figure out what to vacuum when). Scheduling == We introduce the following concepts: 1. table groups. We'll have a system catalog for storing OID and group name, and another catalog for membership, linking relid to group OID. pg_av_tablegroup tgrname name pg_av_tgroupmembers groupid oid relid oid 2. interval groups. We'll have a catalog for storing igroup name and OID, and another catalog for membership. We identify an interval by: - month of year - day of month - day of week - start time of day - end time of day This is modelled after crontabs. pg_av_intervalgroup igrnamename pg_av_igroupmembers groupidoid month int domint dowint starttime timetz endtimetimetz This seems to assume that the start and end time for an interval will be on the same day, you probably need to specify a start month, dom, dow, time and an end month, dom, dow and time. Since this is modeled after cron, do we allow wild-cards, or any of the other cron tricks like */20 or 1-3,5,7,9-11? Also your notation above is ambiguous, it took me a while to realize that pg_av_igroupmembers.groupid wasn't referencing the id from pg_av_tablegroup. Additionally, we'll have another catalog on which we'll store table groups to interval groups relationships. On that catalog we'll also store those autovacuum settings that we want to be able to override: whether to disable it for this interval group, or the values for the vacuum/analyze equations. pg_av_schedule tgroup oid igroup oid enabledbool queue int vac_base_threshint vac_scale_factor float anl_base_threshint anl_scal_factorfloat vac_cost_delay int vac_cost_limit int freeze_min_age int freeze_max_age int What is queue for? So the scheduler, at startup, loads the whole schedule in memory, and then wakes up at reasonable intervals and checks whether these equations hold for some of the tables it's monitoring. If they do, then launch a new worker process to do the job. We need a mechanism for having the scheduler rescan the schedule when a user modifies the catalog -- maybe having a trigger that sends a signal to the process is good enough (implementation detail: the signal must be routed via the postmaster, since the backend cannot hope to know the scheduler's PID. This is easy enough to do.) This all looks reasonable if not a bit complex. Question, what happens to the current pg_autovacuum relation? Also what about system defaults, will we have a hard coded default interval of always on, and one default table group that contains all the tables with one default entry in pg_av_schedule? I think we need more discussion on scheduling, we need to make sure this solves the vacuum starvation problem. Does the launcher process consider each row in pg_av_schedule that
Re: [HACKERS] Autovacuum improvements
Alvaro Herrera [EMAIL PROTECTED] writes: I've been thinnking how to improve autovacuum so that we can convince more people that it can be enabled by default. Here are my thoughts. There are two areas of improvements: 1. scheduling, and 2. process handling, i.e., how to have multiple vacuum processes running at any time. Actually the reason it's not enabled by default today has nothing to do with either of those; it's 3. Unexpected side effects on foreground processes, such as surprising failures of DROP DATABASE commands. (See archives for details.) Until (3) is addressed I don't think there is any chance of having autovac on by default, and so worrying about (1) and (2) seems a bit premature. Or at least, if you want to work on those fine, but don't expect that it will alter the fact that the factory default is off. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Autovacuum improvements
Tom Lane wrote: Actually the reason it's not enabled by default today has nothing to do with either of those; it's 3. Unexpected side effects on foreground processes, such as surprising failures of DROP DATABASE commands. (See archives for details.) The referred to thread starts here: http://archives.postgresql.org/pgsql-hackers/2006-08/msg01814.php Until (3) is addressed I don't think there is any chance of having autovac on by default, and so worrying about (1) and (2) seems a bit premature. Or at least, if you want to work on those fine, but don't expect that it will alter the fact that the factory default is off. Hmm, right. The mentioned problems are: * manual ANALYZE issued by regression tests fails because autovac is analyzing the same table concurrently. * contrib tests fail in their repeated drop/create database operations because autovac is connected to that database. (pl tests presumably have same issue.) I suggest we should fix at least the second problem and then turn autovac on by default, to see if there are more hurdles (and to get more autovacuum testing during this development cycle, at least as far as regression tests are concerned). We can turn it back off after the 8.3 cycle is done, if we don't find it living up to expectations. I'm not sure how to fix the second problem. If it was autovac's ANALYZE that was failing, ISTM it would be a simple problem, but we don't have much control over the regression test's own ANALYZEs. One idea would be to introduce the concept of launcher process I mentioned, and have it act like the bgwriter for checkpoints: have it start the analyze when backends request it, and then inform when the analyze is done. So if an analyze is already running, then the launcher does nothing except inform the backend when the analyze is finished. So a sort of roadmap for my proposal would be to first introduce the autovacuum launcher, and have backends communicate with it instead of doing the work by themselves; and then introduce the scheduling concept into the launcher. In fact, if we have the scheduler be a separate process from the launcher, the scheduler could be pluggable: sites for which the current autovacuum is enough just use today's autovacuum as scheduler, and sites which need more elaborate configuration just turn on the advanced module. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Autovacuum improvements
Alvaro Herrera [EMAIL PROTECTED] writes: Hmm, right. The mentioned problems are: * manual ANALYZE issued by regression tests fails because autovac is analyzing the same table concurrently. This problem might have gone away since then --- I think we are now taking a lock to ensure only one ANALYZE per table at a time; so the manual ANALYZEs should only be delayed a bit not report errors. * contrib tests fail in their repeated drop/create database operations because autovac is connected to that database. (pl tests presumably have same issue.) The DROP is at risk, but CREATE is also at risk because autovac feels free to connect to template0. (One of the reasons we invented template0 was to prevent CREATE DATABASE failures due to someone-else-connected, but autovac has broken that idea.) Possibly we could handle these by extending create/drop db to check whether a process-connected-to-the-target-db is an autovac, and if so send it a SIGINT and wait for the process to terminate, instead of failing. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Autovacuum improvements
Matthew T. O'Connor wrote: Alvaro Herrera wrote: I've been thinking how to improve autovacuum so that we can convince more people that it can be enabled by default. I would like to see it enabled by default too, however the reason it isn't already enabled by default is that it caused failures in the regression test when we tried to turn it on during the 8.2 dev cycle and it was too close to beta to fix everything. All this new machinery is great, but it doesn't address that problem. See my reply to Tom on this topic. pg_av_igroupmembers groupid oid month int dom int dow int starttime timetz endtime timetz This seems to assume that the start and end time for an interval will be on the same day, you probably need to specify a start month, dom, dow, time and an end month, dom, dow and time. Actually, I was thinking that if you want intervals that cross day boundaries, you just add more tuples (one which finishes at 23:59:59 and another which starts at 00:00:00 the next day). Since this is modeled after cron, do we allow wild-cards, or any of the other cron tricks like */20 or 1-3,5,7,9-11? Wildcards yes (using NULL), but not the rest because it would make the autovacuum code responsible for parsing the values which I don't think is a good idea. And it's not normalized anyway. Also your notation above is ambiguous, it took me a while to realize that pg_av_igroupmembers.groupid wasn't referencing the id from pg_av_tablegroup. Hmm, yeah, that one is referencing pg_av_intervalgroup. pg_av_schedule tgroup oid igroup oid enabled bool queue int vac_base_thresh int vac_scale_factorfloat anl_base_thresh int anl_scal_factor float vac_cost_delay int vac_cost_limit int freeze_min_age int freeze_max_age int What is queue for? Sorry, that was part of the queue stuff which I then deleted :-) So the scheduler, at startup, loads the whole schedule in memory, and then wakes up at reasonable intervals and checks whether these equations hold for some of the tables it's monitoring. If they do, then launch a new worker process to do the job. We need a mechanism for having the scheduler rescan the schedule when a user modifies the catalog -- maybe having a trigger that sends a signal to the process is good enough (implementation detail: the signal must be routed via the postmaster, since the backend cannot hope to know the scheduler's PID. This is easy enough to do.) This all looks reasonable if not a bit complex. Question, what happens to the current pg_autovacuum relation? I had two ideas: one was to make pg_autovacuum hold default config for all tables not mentioned in any group, so sites which are OK with 8.2's representation can still use it. The other idea was to remove it and replace it with this mechanism. Also what about system defaults, will we have a hard coded default interval of always on, and one default table group that contains all the tables with one default entry in pg_av_schedule? Yes, that's what I had in mind. I think we need more discussion on scheduling, we need to make sure this solves the vacuum starvation problem. Does the launcher process consider each row in pg_av_schedule that applies at the current time separately? That is say there are three entries in pg_av_schedule that apply right now, does that mean that the launcher can fire off three different vacuums? Perhaps we need to add a column to pg_av_tablegroup that specifies the max number of concurrent worker processes for this table group. My idea was to assign each table, or maybe each group, to a queue, and then have as much workers as there are queues. So you could put them all in a single queue and it would mean there can be at most one vacuum running at any time. Or you could put each group in a queue, and then there could be as many workers as there are groups. Or you could mix. And also there would be a autovac concurrency limit, which would be a GUC var saying how many vacuums to have at any time. Also, I don't think we need the concept of queues as described in recent threads. I think the idea of the queues was the the system would be able to automatically find small tables and vacuum them frequently, in this proposal the admin would have to create a group for small tables and manually add tables to the group and make sure that there are enough worker processes for that group to prevent vacuum starvation. Perhaps we can create a dynamic group that includes all tables with less than a certain number of rows or blocks? Yeah, my idea of queues was slightly different than the queues that were being discussed. I was thinking that queues would just be a means to group the
Re: [HACKERS] Autovacuum improvements
Tom Lane wrote: * contrib tests fail in their repeated drop/create database operations because autovac is connected to that database. (pl tests presumably have same issue.) The DROP is at risk, but CREATE is also at risk because autovac feels free to connect to template0. (One of the reasons we invented template0 was to prevent CREATE DATABASE failures due to someone-else-connected, but autovac has broken that idea.) Possibly we could handle these by extending create/drop db to check whether a process-connected-to-the-target-db is an autovac, and if so send it a SIGINT and wait for the process to terminate, instead of failing. Hmm, I can see having DROP DATABASE just stopping the autovacuum (since the work will be thrown away), but is a good idea to stop it on CREATE DATABASE? I think it may be better to have CREATE DATABASE wait until the vacuum is finished. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Autovacuum improvements
Alvaro Herrera [EMAIL PROTECTED] writes: Tom Lane wrote: Possibly we could handle these by extending create/drop db to check whether a process-connected-to-the-target-db is an autovac, and if so send it a SIGINT and wait for the process to terminate, instead of failing. Hmm, I can see having DROP DATABASE just stopping the autovacuum (since the work will be thrown away), but is a good idea to stop it on CREATE DATABASE? I think it may be better to have CREATE DATABASE wait until the vacuum is finished. It can always be done again later. I think that the arguments of (1) only one code path needed and (2) not making the user wait should win out over concerns about possible wasted autovac effort. (The wasted effort should generally be pretty small anyway, since a template database probably doesn't contain any large tables.) regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Memory context in exception handler
Tom Lane wrote: As near as I can tell, you're using CopyErrorData not because you need an actual copy but just because elog.c doesn't export any other API to let you see the current sqlerrorcode. Perhaps adding a function to return the top stack entry's sqlerrorcode would be a better API change? Yes, something like that would be good to have. At the moment, there are not a lot of users of this mechanism in our code, so I'm not in a hurry to change this (and I think that I want to rewrite the XML parsing code to do without the exceptions dance). -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] -f output file option for pg_dumpall
On Thu, 2007-01-11 at 14:36 -0500, Neil Conway wrote: I don't think they need to be integrated any time soon, but if we were to design pg_dump and pg_dumpall from scratch, it seems more logical to use a single program On thinking about this some more, it might be useful to factor much of pg_dump's logic for reconstructing the state of a database into a shared library. This would make it relatively easy for developers to plug new archive formats into the library (in addition to the present 3 archive formats), or to make use of this functionality in other applications that want to reconstruct the logical state of a database from the content of the system catalogs. We could then provide a client app implemented on top of the library that would provide similar functionality to pg_dump. Moving pg_dump's functionality into the backend has been suggested in the past (and rejected for good reason), but I think this might be a more practical method for making the pg_dump logic more easily reusable. (While we're on the subject, this would also present a good opportunity to make pg_dump's command-line interface a little more sane. I wonder if pg_dump's syntax has gotten sufficiently complicated that some users might prefer to specify behavior via a configuration file, rather than a long string of command-line options.) -Neil ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Autovacuum improvements
Alvaro Herrera wrote: Hmm, I can see having DROP DATABASE just stopping the autovacuum (since the work will be thrown away), For that same reason DROP DATABASE could just cut all connections to the database. Or at least queue up and wait until the session is over. (The latter would correspond to what DROP TABLE does on a table that is in use.) -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] xml type and encodings
We need to decide on how to handle encoding information embedded in xml data that is passed through the client/server encoding conversion. Here is an example: Client encoding is A, server encoding is B. Client sends an xml datum that looks like this: INSERT INTO table VALUES (xmlparse(document '?xml version=1.0 encoding=C?content.../content')); Assuming that A, B, and C are all distinct, this could fail at a number of places. I suggest that we make the system ignore all encoding declarations in xml data. That is, in the above example, the string would actually have to be encoded in client encoding B on the client, would be converted to A on the server and stored as such. As far as I can tell, this is easily implemented and allowed by the XML standard. The same would be done on the way back. The datum would arrive in encoding B on the client. It might be implementation-dependent whether the datum actually contains an XML declaration specifying an encoding and whether that encoding might read A, B, or C -- I haven't figured that out yet -- but the client will always be required to consider it to be B. What should be done above the binary send/receive functionality? Looking at the send/receive functions for the text type, they communicate all data in the server encoding, so it seems reasonable to do this here as well. Comments? -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(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] Autovacuum improvements
Alvaro Herrera wrote: Matthew T. O'Connor wrote: Alvaro Herrera wrote: pg_av_igroupmembers groupid oid month int dom int dow int starttime timetz endtime timetz This seems to assume that the start and end time for an interval will be on the same day, you probably need to specify a start month, dom, dow, time and an end month, dom, dow and time. Actually, I was thinking that if you want intervals that cross day boundaries, you just add more tuples (one which finishes at 23:59:59 and another which starts at 00:00:00 the next day). This still seems ambiguous to me, how would I handle a maintenance window of Weekends from Friday at 8PM though Monday morning at 6AM? My guess from what said is: mon dom dow starttime endtime null null6 20:00 null null null1 null 06:00 So how do we know to vacuum on Saturday or Sunday? I think clearly defined intervals with explicit start and stop times is cleaner. This all looks reasonable if not a bit complex. Question, what happens to the current pg_autovacuum relation? I had two ideas: one was to make pg_autovacuum hold default config for all tables not mentioned in any group, so sites which are OK with 8.2's representation can still use it. The other idea was to remove it and replace it with this mechanism. Probably best to just get rid of it. GUC variables hold the defaults and if we create a default interval / group, it will also have defaults. I think we need more discussion on scheduling, we need to make sure this solves the vacuum starvation problem. Does the launcher process consider each row in pg_av_schedule that applies at the current time separately? That is say there are three entries in pg_av_schedule that apply right now, does that mean that the launcher can fire off three different vacuums? Perhaps we need to add a column to pg_av_tablegroup that specifies the max number of concurrent worker processes for this table group. My idea was to assign each table, or maybe each group, to a queue, and then have as much workers as there are queues. So you could put them all in a single queue and it would mean there can be at most one vacuum running at any time. Or you could put each group in a queue, and then there could be as many workers as there are groups. Or you could mix. And also there would be a autovac concurrency limit, which would be a GUC var saying how many vacuums to have at any time. Hmm... this seems like queue is nearly a synonym for group. Can't we just add num_workers property to table groups? That seems to accomplish the same thing. And yes, a GUC variable to limits the total number of concurrent autovacuums is probably a good idea. ---(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] xml type and encodings
Peter Eisentraut [EMAIL PROTECTED] writes: Looking at the send/receive functions for the text type, they communicate all data in the server encoding, so it seems reasonable to do this here as well. Uh, no, I'm pretty sure there's a translation to the client encoding. It's in a subroutine not in text_send proper. regards, tom lane ---(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
