Re: [HACKERS] Mini improvement: statement_cost_limit
On Aug 4, 2008, at 1:04 PM, daveg wrote: Ok, that is a different use case where an error seems very useful. What about slightly extending the proposal to have the severity of exceeding the limit configurable too. Something like: costestimate_limit = 10 # default 0 to ignore limit costestimate_limit_severity = error # debug, notice, warning, error I very much like this idea, and I would definitely use something like this on our production oltp app. We had a case recently where a query joining two large tables was very fast 99.9% of the time (i.e., a few ms), but for particular, rare key combinations the planner would make a poor choice turning into a multi-minute monster. It ran longer than the web server timeout, and the client was programmed to retry on error, essentially causing a database DoS. The monster version of the plan had an outrageous cost estimate, many orders of magnitude higher than any regular app query, and would be easy to peg using even a crudely chosen limit value. The problem was first mitigated by setting a query timeout a little longer than the web server timeout (since the query results are discarded for anything running longer), but even this was not a solution, since the client would retry on timeout, still keeping the db too busy. The real solution was to not do the query, but it would have been better to identify this via ERRORs in the logs than by the database becoming saturated in the middle of the day. For our application it is far better for an expensive query to be rejected outright than to attempt to run it in vain. Just thought I'd throw that out as anecdotal support. -Casey -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] autovacuum next steps, take 2
On Feb 26, 2007, at 12:49 PM, Alvaro Herrera wrote: Jim C. Nasby wrote: That's why I'm thinking it would be best to keep the maximum size of stuff for the second worker small. It probably also makes sense to tie it to time and not size, since the key factor is that you want it to hit the high-update tables every X number of seconds. If we wanted to get fancy, we could factor in how far over the vacuum threshold a table is, so even if the table is on the larger size, if it's way over the threshold the second vacuum will hit it. Ok, I think we may be actually getting somewhere. I propose to have two different algorithms for choosing the tables to work on. The worker would behave differently, depending on whether there is one or more workers on the database already or not. The first algorithm is the plain threshold equation stuff we use today. If a worker connects and determines that no other worker is in the database, it uses the plain worker mode. A worker in this mode would examine pgstats, determine what tables to vacuum/analyze, sort them by size (smaller to larger), and goes about its work. This kind of worker can take a long time to vacuum the whole database -- we don't impose any time limit or table size limit to what it can do. The second mode is the hot table worker mode, enabled when the worker detects that there's already a worker in the database. In this mode, the worker is limited to those tables that can be vacuumed in less than autovacuum_naptime, so large tables are not considered. Because of this, it'll generally not compete with the first mode above -- the tables in plain worker were sorted by size, so the small tables were among the first vacuumed by the plain worker. The estimated time to vacuum may be calculated according to autovacuum_vacuum_delay settings, assuming that all pages constitute cache misses. Perhaps this has already been proposed, but maybe some combination of the following inputs could be used to determine which table most needs vacuuming: - The proportion of tuples in a table that are dead (updated rows since last vacuum/estimated row count). This would favor hot tables naturally regardless of size. - The time since the last vacuum, so that larger tables are eventually vacuumed even if hot tables totally dominate Of course tables that did not pass the minimum parameters specified in postgresql.conf would not even get considered. I'm being intentionally vague here on the exact algorithm, since you all have though about this more than I have. One thing I like about the above is that it is independent of table size, and doesn't require anyone to determine which tables are hot manually. -Casey ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [PATCHES] xlog directory at initdb time
On Jan 2, 2007, at 7:18 AM, Tom Lane wrote: Peter Eisentraut [EMAIL PROTECTED] writes: Am Mittwoch, 27. Dezember 2006 02:56 schrieb Euler Taveira de Oliveira: This simple patch lets someone specifies the xlog directory at initdb time. It uses symlinks to do it, and create and/or set permissions at the directory as appropriate. We already had this functionality in initdb a few versions ago. Did you review why it was removed? The discussion thread seems to start here: http://archives.postgresql.org/pgsql-hackers/2002-08/msg00306.php As best I can tell the objections came from the fact that Thomas had implemented it as a postmaster-start-time switch, which made it a foot-gun because you could mistakenly start the postmaster with a different XLOG than you were using before. That would not apply to a symlink-made-by-initdb approach. All this is doing is formalizing something we already suggest people do by hand... I guess the downside there is that it won't work on platforms that don't support symlinks, whereas the postmaster switch would. Not that I condone using such platforms ;^) -Casey ---(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] psql commandline conninfo
On Dec 12, 2006, at 3:37 PM, Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: Right. Here's the patch I just knocked up, which seems to Just Work (tm) ;-) The main objection I can see to this is that you'd get a fairly unhelpful message if you intended a conninfo string and there was anything wrong with your syntax (eg, misspelled keyword). Maybe we should go with the conn: bit, although really that doesn't seem any less likely to collide with actual dbnames than the does it contain = idea. Anyone have other ideas how to disambiguate? I would personally prefer a real option over a prefix, i.e. -- dbconn=service=foo though the inline conninfo string in place of the dbname would be ideal. Perhaps like Tom suggests, if the value matches a conninfo regex (slightly more rigid than just containing an equals character) then we assume it is a conninfo string, but never try it as a dbname. If someone has a database named like a conninfo string (c'mon folks ;^) then they would need to pass it as explicitly an argument to '-d' or '--dbname', not as a bare argument. This is not completely b/w compatible of course, but IMO the added convenience outweighs the incompatibility. -Casey ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] psql commandline conninfo
On Dec 12, 2006, at 5:16 PM, Andrew Dunstan wrote: Casey Duncan wrote: On Dec 12, 2006, at 3:37 PM, Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: Right. Here's the patch I just knocked up, which seems to Just Work (tm) ;-) The main objection I can see to this is that you'd get a fairly unhelpful message if you intended a conninfo string and there was anything wrong with your syntax (eg, misspelled keyword). Maybe we should go with the conn: bit, although really that doesn't seem any less likely to collide with actual dbnames than the does it contain = idea. Anyone have other ideas how to disambiguate? I would personally prefer a real option over a prefix, i.e. -- dbconn=service=foo though the inline conninfo string in place of the dbname would be ideal. Perhaps like Tom suggests, if the value matches a conninfo regex (slightly more rigid than just containing an equals character) then we assume it is a conninfo string, but never try it as a dbname. If someone has a database named like a conninfo string (c'mon folks ;^) then they would need to pass it as explicitly an argument to '-d' or '--dbname', not as a bare argument. You are confusing two things here. The way the patch is written it simply interprets the parameter passed to libpq - it has no idea what was used (if anything) on the commandline. The alternative, as Tom pointed out, is to patch every client. I was speaking from and end-user point of view, but I see your point. It's certainly attractive to just patch libpq and be done. However, that does have the side-effect of implicitly propagating the behavior to all libpg client software. That may be more unpleasantly surprising to more people then just changing the built-in postgresql client utilities. But then again it could also be considered a feature 8^) -Casey ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [DOCS] [HACKERS] Replication documentation addition
Totally agree. The docs will tend to outlive whatever projects or websites they mention. Best to not bake that into stone. -Casey On Oct 25, 2006, at 3:36 AM, Magnus Hagander wrote: I don't think the PostgreSQL documentation should be mentioning commercial solutions. I think maybe the PostgreSQL documentation should be careful about trying to list a complete list of commercial *or* free solutions. Instead linking to something on the main website or on techdocs that can more easily be updated. //Magnus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: Hints (Was: [HACKERS] Index Tuning Features)
On Oct 12, 2006, at 4:26 AM, Andrew Sullivan wrote: On Thu, Oct 12, 2006 at 08:34:45AM +0200, Florian Weimer wrote: Some statistics are very hard to gather from a sample, e.g. the number of distinct values in a column. Then how can the DBA know it, either? The problem with this sort of argument is always that people are claiming some special knowledge is available to the DBA. If it's true that the DBA really _can_ know this stuff, then there must be some way to learn it. Which means that you can, in principle, figure out ways to communicate that to the optimizer. Yes, but it may be much more efficient for the human to tell the computer than for the computer to introspect things. Take, for example, ndisinct as data grows large. I, the database designer, may know (or simply see) that a certain foreign key column will have roughly a certain cardinality regardless of how big the table gets. It's a lot more efficient for me to tell the system that up front then have it need to do a full table scan or tens of millions of rows periodically to figure it out, or worse--as it is currently--to come up with an estimate that is multiple orders of magnitude off, even with the stats target turned all the way up. I realize that this is a case that is possible to do manually now, sort of. I can tweak the stats table myself. But it would be nice if you could do it in such a way that it would override what analyze comes up with on a case-by-case basis. We could have a perfect query planner, but feed it bad stats and it will still make poor decisions. I'm of the strong opinion that hinting the data is much better than hinting the queries. There tends to be many fewer places you need to do that, and new queries can automatically take advantage. I like the suggestion, though, that there be ways to codify known relationships in the system in such a way that the optimizer can learn to use that information. _That_ seems to me to be a big improvement, because it can be taken into consideration along with relationships that emerge from the statistics, that the DBA may not know about. I'm all for things the computer can do for me automagically. It's just good to have the ability to tell the computer about things you know about the data that it either can't efficiently figure out or can't figure out at all. -Casey ---(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] [GENERAL] Array assignment behavior (was Re: [ADMIN] Stored procedure array limits)
On Sep 29, 2006, at 9:14 AM, Tom Lane wrote: [ expanding this thread, as it now needs wider discussion ] Paul B. Anderson [EMAIL PROTECTED] writes: Actually, I was not filling all of the arrays in sequential order. I added code to initialize them in order and the function seems to be working now. Is that a known problem? Well, it's a documented behavior: section 8.10.4 saith A stored array value can be enlarged by assigning to an element adjacent to those already present, or by assigning to a slice that is adjacent to or overlaps the data already present. Up to 8.2 we didn't have a lot of choice about this, because without any ability to have nulls embedded in arrays, there wasn't any sane thing to do with the intermediate positions if you assigned to an element not adjacent to the existing range. As of 8.2 we could allow assignment to arbitrary positions by filling the intermediate positions with nulls. The code hasn't actually been changed to allow that, but it's something we could consider doing now. Comments? At first blush, this strikes me as a bit too magical/implicit. Are there other languages where sequences behave similarly? The best analogy that comes to mind is sparse files, but in that case there is an implicit contract that the intervening empty regions do not actually occupy physical space, doesn't sound like that's true here. I think the result of this change would be more difficult debugging of off-by-one errors and their ilk, rather than actually being a real benefit. OTOH, perhaps there is a real use-case I am missing here. I don't see the rest of this thread on GENERAL and I couldn't find it searching the archives, where did it come from? -Casey ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Questions about guc units
On Sep 25, 2006, at 1:03 AM, Peter Eisentraut wrote: Am Montag, 25. September 2006 04:04 schrieb ITAGAKI Takahiro: #shared_buffers = 32000kB # min 128kB or max_connections*16kB #temp_buffers = 8000kB # min 800kB #effective_cache_size = 8000kB Are there any reasons to continue to use 1000-unit numbers? Megabyte-unit (32MB and 8MB) seems to be more friendly for users. It increases some amount of values (4000 vs. 4096), but there is little in it. The reason with the shared_buffers is that the detection code in initdb has 400kB as minimum value, and it would be pretty complicated to code the detection code to handle both kB and MB units. If someone wants to try it, though, please go ahead. Seems like the unit used for shared_buffers (and others) should be megabytes then with a minimum of 1 (or more). Is less than 1MB granularity really useful here? On modern hardware 1MB of RAM is in the noise. -Casey ---(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] [GENERAL] autovacuum connections are hidden
On May 22, 2006, at 2:37 PM, Alvaro Herrera wrote: Jim C. Nasby wrote: Moving to -hackers You forgot to actually do it apparently? Sorry about posting the patch to -general, BTW. Anyway it was committed to the 8.1 branch, so it is included in the new release (8.1.4?) Does this still obey stats_command_string? Yes. I considered having the ps display show the info, but it's not as useful because you can only get the info if you have access to the process list (i.e. not a remote client). It would be useful for dba's watching the box directly, via ps or top (which I find myself doing fairly often). This has always been a great feature of postgres IMO. I'd put in a vote for having that for that for the autovac backend as well FWIW. In any event thanks a lot for the current fix, as is it's a big improvement! 8^) -Casey ---(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
