Re: [HACKERS] 8.3 beta testing suggestions welcome
On Mon, 20 Aug 2007, Kevin Grittner wrote: The problem went away completely when we used a very aggressive background writer configuration, to put the dirty pages in front of the OS file system right away, so that its algorithms and the controller cache could deal with things before they got out of hand. Can you share what the aggressive BGW settings you settled on were? Knowing that information would allow giving better suggestions on how to translate what you're currently doing into the very different settings 8.3 uses in this area. Also be helpful to know about how often you have a checkpoint with your current configuration, and how big your shared_buffers is to get a general context for the size/frequency of potential checkpoint problems. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Status of 8.3 patches
Tom Lane wrote: This patch was originally submitted before we realized that pg_stats failed to distinguish the effects of committed vs rolled-back transactions (which was fixed about three months ago); and we also recently fixed several other bugs such as losing stats data for shared catalogs. So there's a significant probability that the errors it was trying to compensate for are already fixed. Quite possible. I don't recall a real world example or a test case preceding the patch. I guess the problem scenario would be a table with a lot of update/delete activity, and very unaggressive cost_delay. Also, I'm still quite unhappy that the patch converts the tracking of n_dead_tuples into a dead-reckoning system in which incremental changes are continually applied without any feedback that'd prevent the value from diverging arbitrarily far from reality. Murphy's law says that the value *will* diverge, if you don't have any negative feedback in the loop to force it to track reality. I believe the latest version doesn't have that problem. At the beginning of vacuum, n_dead_tuples is saved, and at the end of vacuum n_dead_tuples is decremented by the value it had at the beginning. At the end n_dead_tuples will be equal to the number of new dead tuples generated during the vacuum, no matter how out of whack it was in the beginning. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] tsearch2 patch status report
On Mon, Aug 20, 2007 at 10:50:49PM -0400, Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: You broke my shiny new MinGW and Cygwin buildfarm members too :-) Yeah, I was just looking at that. I seem to recall that the fu01.o(.idata$3+0xc): undefined reference to `libpostgres_a_iname' bleat is a symptom of a reference to a variable that isn't marked DLLIMPORT ... but CurrentMemoryContext certainly is, so there's not anything here sufficient to fix it. I trust someone with access to a Windows build environment will dig into that. Mingw fixed, I think. I think that will also fix cygwin, but less sure there.. //Magnus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] Stage Configure failed with status 77
Hi, I am writing a set of articles about Automated Testing in Open Source Software http://www.szabgab.com/blog/2007/07/1183825266.html As I would like to write about PostgreSQL as well I tried to setup the BuildFarm client. Here is the output: $ ./run_build.pl --test [16:44:34] checking out source ... [16:45:20] checking if build run needed ... [16:45:20] copying source to pgsql.24847 ... [16:45:20] running configure ... Branch: HEAD Stage Configure failed with status 77 What does this mean? Digging a bit in HEAD/lastrun-logs/config.log revealed that I was missing ccache but it was not really clean from that error message. Well, never mind, this must be testing my capabilities as well :-) The second run was slightly better: $ ./run_build.pl --test [16:49:44] checking out source ... [16:50:11] checking if build run needed ... [16:50:11] copying source to pgsql.25565 ... [16:50:12] running configure ... [16:50:42] running make ... Branch: HEAD Stage Make failed with status 2 This time I found HEAD/lastrun-logs/make.log much faster In that file I got tons of error messages mostly related to plpython.c e.g. plpython.c:2984: error: 'PLy_exc_error' undeclared (first use in this function) I installed python-dev and it seems my guess was correct regarding what was missing as on the third attempt the build made further progress and in the end I got All stages succeeded Nice. These two attempts to stop me from running the build client were not really serious. I spent only 15 minutes trying to locate the problem and you even get this mail as a result. :-) Now waiting for my registration to be processed before I can start supplying test reports. -- Gabor Szabo http://www.szabgab.com/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Stage Configure failed with status 77
Gabor Szabo wrote: Hi, I am writing a set of articles about Automated Testing in Open Source Software http://www.szabgab.com/blog/2007/07/1183825266.html As I would like to write about PostgreSQL as well I tried to setup the BuildFarm client. Here is the output: $ ./run_build.pl --test [16:44:34] checking out source ... [16:45:20] checking if build run needed ... [16:45:20] copying source to pgsql.24847 ... [16:45:20] running configure ... Branch: HEAD Stage Configure failed with status 77 What does this mean? Digging a bit in HEAD/lastrun-logs/config.log revealed that I was missing ccache but it was not really clean from that error message. Well, never mind, this must be testing my capabilities as well :-) The second run was slightly better: $ ./run_build.pl --test [16:49:44] checking out source ... [16:50:11] checking if build run needed ... [16:50:11] copying source to pgsql.25565 ... [16:50:12] running configure ... [16:50:42] running make ... Branch: HEAD Stage Make failed with status 2 This time I found HEAD/lastrun-logs/make.log much faster In that file I got tons of error messages mostly related to plpython.c e.g. plpython.c:2984: error: 'PLy_exc_error' undeclared (first use in this function) I installed python-dev and it seems my guess was correct regarding what was missing as on the third attempt the build made further progress and in the end I got All stages succeeded Nice. These two attempts to stop me from running the build client were not really serious. I spent only 15 minutes trying to locate the problem and you even get this mail as a result. :-) try run_build.pl --help and look at the options :-) In effect something like ./run_build.pl --test --verbose=2 would be much more useful for testing Stefan ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] 8.3 beta testing suggestions welcome
On Tue, Aug 21, 2007 at 2:36 AM, in message [EMAIL PROTECTED], Greg Smith [EMAIL PROTECTED] wrote: On Mon, 20 Aug 2007, Kevin Grittner wrote: The problem went away completely when we used a very aggressive background writer configuration, to put the dirty pages in front of the OS file system right away, so that its algorithms and the controller cache could deal with things before they got out of hand. Can you share what the aggressive BGW settings you settled on were? Under 8.2.4, the postgresql.conf file, minus blank lines and comments: listen_addresses = '*' max_connections = 200 shared_buffers = 160MB temp_buffers = 50MB work_mem = 10MB maintenance_work_mem = 160MB max_fsm_pages = 80 bgwriter_lru_percent = 20.0 bgwriter_lru_maxpages = 200 bgwriter_all_percent = 10.0 bgwriter_all_maxpages = 600 wal_buffers = 160kB checkpoint_segments = 10 random_page_cost = 2.0 effective_cache_size = 5GB redirect_stderr = on log_line_prefix = '[%m] %p %q%u %d %r ' stats_block_level = on stats_row_level = on autovacuum = on autovacuum_naptime = 10s autovacuum_vacuum_threshold = 1 autovacuum_analyze_threshold = 1 datestyle = 'iso, mdy' lc_messages = 'C' lc_monetary = 'C' lc_numeric = 'C' lc_time = 'C' escape_string_warning = off standard_conforming_strings = on sql_inheritance = off Also be helpful to know about how often you have a checkpoint with your current configuration Since our problems associated with checkpoints have been eliminated I haven't been tracking them very closely. A scan of our log files on this machine show that there were two episodes in the last two months where we had 20 to 40 checkpoints in a day which were less than 30 seconds after the prior one. These were associated with times when this central copy had fallen significantly behind the source databases and replication was running full out, catching up with the sources. Outside of those events we seem to have on to four episodes on scattered days. A quick check shows that all but a few coincide with a weekly run which passes massive amounts of data to rebuild a table of cases which should not show on the public web site according to the Supreme Court's records retention rules. Is there anything you would like me to do to capture more information about the checkpoints? -Kevin ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] INSERT/SELECT and excessive foreign key checks
On 19-aug-2007, at 12:38, Tom Lane wrote: An additional problem with your proposal is that it fails to consider other changes that might be happening concurrently -- eg, what if some other backend deletes a source row after you copy it, and commits before you do? then the patch indeed failed, but when I change it to check those carried over FKs also once, it catches it correctly. are there other such issues? or is this kind of optimization not going in no matter what? Lodewijk ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] tsearch2 patch status report
On Mon, Aug 20, 2007 at 09:23:25PM -0400, Tom Lane wrote: I've applied version 0.58 of the patch with a lot of further editorializing. I feel fairly confident now in the code that interfaces between tsearch and the rest of the system, but a lot of the lowest-level guts of tsearch (mainly in src/backend/tsearch/*.c and src/backend/utils/adt/ts*.c) left my eyes glazing over. Perhaps someone else can make an extra review pass over that stuff. I am quite confident that this commit broke the MSVC build, which seems to need to know individually about each shared library ... Magnus, can you do something about that? We'll see what other portability problems emerge from the buildfarm. Looking at that now. I get awarning from the following line in bison generated parse.h: #define TEXT 577 Because TEXT is a macro that's used in the windows headers. (A redefinition warning). Any chance to change that, or is it coming out of the syntax itself? (bison newbie here, as you know :-P) Also, we need to decide what to do with contrib/tsearch2, which is currently DOA because of conflicts with the new core code. We could either rip it out entirely, or try to convert it into a compatibility package. In view of the renamings of functions we agreed to do, I think there is some scope for a compatibility package, but I have no time to work on that. OTOH, if we do it as a compat package, we need to set a firm end-date on it, so we don't have to maintain it forever. Given the issues always at hand for doing such an upgrade, my vote is actually for ripping it out completely and take the migration pain once and then be done with it. This is, by a wide margin, the largest single patch ever to hit the Postgres CVS tree. Congratulations to Oleg and Teodor on seeing it through! Yes, congratulations indeed. Great to see this in! //Magnus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] tsearch2 patch status report
Magnus Hagander wrote: On Mon, Aug 20, 2007 at 09:23:25PM -0400, Tom Lane wrote: I get awarning from the following line in bison generated parse.h: #define TEXT 577 That's easy, change the terminal to TEXT_P on gram.y and keywords.c. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(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] INSERT/SELECT and excessive foreign key checks
Lodewijk Vöge escribió: On 19-aug-2007, at 12:38, Tom Lane wrote: An additional problem with your proposal is that it fails to consider other changes that might be happening concurrently -- eg, what if some other backend deletes a source row after you copy it, and commits before you do? then the patch indeed failed, but when I change it to check those carried over FKs also once, it catches it correctly. are there other such issues? or is this kind of optimization not going in no matter what? It might go in if it's correct. If you have an answer to all the objections then there's no reason not to include it. But I must admit I didn't understand what was your answer to the above objection; care to rephrase? -- Alvaro Herrera http://www.amazon.com/gp/registry/CTMLCN8V17R4 On the other flipper, one wrong move and we're Fatal Exceptions (T.U.X.: Term Unit X - http://www.thelinuxreview.com/TUX/) ---(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] tsearch2 patch status report
On Tue, Aug 21, 2007 at 10:59:52AM -0400, Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: Magnus Hagander wrote: I get awarning from the following line in bison generated parse.h: #define TEXT 577 That's easy, change the terminal to TEXT_P on gram.y and keywords.c. Yeah, I was wondering if we'd have to do that with any of the new keywords. Will fix, if you didn't already. Nope, not yet since it's just a warning, so please do. //Magnus ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] tsearch2 patch status report
Alvaro Herrera [EMAIL PROTECTED] writes: Magnus Hagander wrote: I get awarning from the following line in bison generated parse.h: #define TEXT 577 That's easy, change the terminal to TEXT_P on gram.y and keywords.c. Yeah, I was wondering if we'd have to do that with any of the new keywords. Will fix, if you didn't already. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] tsearch2 patch status report
On Mon, 20 Aug 2007, Tom Lane wrote: I've applied version 0.58 of the patch with a lot of further editorializing. I feel fairly confident now in the code that interfaces Great ! Just checked and most things after trivial changes are working ! We need to summarize changes and provide upgraide guide. The main thing that is lacking at the moment is documentation. The stuff Bruce has been working on will be good introductory material, but we've got basically zip in reference material. I'll do some work on that over the next couple of days, but there's probably room for more hands. We have SQL reference and I'm sure Bruce should have it too. Bruce, let me know when you do last changes, so I could read docs again Also, we need to decide what to do with contrib/tsearch2, which is currently DOA because of conflicts with the new core code. We could either rip it out entirely, or try to convert it into a compatibility package. In view of the renamings of functions we agreed to do, I think there is some scope for a compatibility package, but I have no time to work on that. Probably, we could leave tsearch2 in contrib as a compat module and explicitly define 8.4 will be the last release with tsearch2 support. This is, by a wide margin, the largest single patch ever to hit the Postgres CVS tree. Congratulations to Oleg and Teodor on seeing it through! It was really hard task for all of us. It's pity I don't drink at all, but Teodor will drink the health of text search in PostgreSQL today ! Tom, as I know, already drain out bottle of vodka, so Bruce, you need to join us ! Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(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] tsearch2 patch status report
Hi, On Tue, 2007-08-21 at 20:04 +0400, Oleg Bartunov wrote: It was really hard task for all of us. It's pity I don't drink at all, but Teodor will drink the health of text search in PostgreSQL today . Haha :) I'd drink Vodka today, with some of my friends. (I'll drink Absolut, and I know you guys do not like it ;) ) Thanks for the good work guys. Cheers, -- Devrim GÜNDÜZ PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/ signature.asc Description: This is a digitally signed message part
Re: [HACKERS] tsearch2 patch status report
Tom Lane wrote:
I've applied version 0.58 of the patch with a lot of further
editorializing. I feel fairly confident now in the code that interfaces
between tsearch and the rest of the system, but a lot of the
lowest-level guts of tsearch (mainly in src/backend/tsearch/*.c and
src/backend/utils/adt/ts*.c) left my eyes glazing over. Perhaps someone
else can make an extra review pass over that stuff.
I'm skimming through tsearch code, trying to understand it. I'd like to
see more comments, at least function-comments explaining what each
function does, what the arguments are etc. I can try to add them as I go
as well, and send a patch.
The memory management of the init functions looks weird. In spell.c,
there's this piece of code:
/*
* during initialization dictionary requires a lot
* of memory, so it will use temporary context
*/
static MemoryContext tmpCtx = NULL;
#define tmpalloc(sz) MemoryContextAlloc(tmpCtx, (sz))
#define tmpalloc0(sz) MemoryContextAllocZero(tmpCtx, (sz))
static void
checkTmpCtx(void)
{
if (CurrentMemoryContext-firstchild == NULL)
{
tmpCtx = AllocSetContextCreate(CurrentMemoryContext,
Ispell dictionary init context,
ALLOCSET_DEFAULT_MINSIZE,
ALLOCSET_DEFAULT_INITSIZE,
ALLOCSET_DEFAULT_MAXSIZE);
}
else
tmpCtx = CurrentMemoryContext-firstchild;
}
checkTmpCtx is called by all the initialization functions in spell.c. I
believe it's assumed that if firstchild exists, it's a previously
allocated init context. But there isn't actually any guarantee that
the CurrentMemoryContext doesn't already have a child context, in which
case we would use whatever the first child context happens to be. And at
least dispell_init calls
MemoryContextDeleteChildren(CurrentMemoryContext), again with no
guarantee that there isn't other unrelated child contexts. I think
dispell_init should create the new context before calling the functions
in spell.c, and destroy it at the end. I can submit a patch, unless I'm
missing something.
More comments as I get further...
PS. Nice to see tsearch in core!
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
[HACKERS] A couple of tsearch loose ends
There are a couple of naming issues that I left untouched while reviewing the tsearch patch, but wanted to bring up for discussion. One thing that had me confused for awhile is that the patch uses the word template in two different ways. The main use is that a template is an object encapsulating the superuser-only aspects of defining a dictionary. When you do CREATE TEXT SEARCH DICTIONARY you have to specify a template to base it on. So in this context a dictionary and its template are different kinds of objects, and there's a persistent connection between them. On the other hand, CREATE TEXT SEARCH CONFIGURATION also uses the word template, but in this case it's an optional specification of an existing configuration that gets copied. So here, the config and the template are the same kind of object, and there's no connection between them after the copy is made. This seems a bit confusing, and I wonder whether we ought not change the terminology for one thing or the other. I don't particularly want to rename text search templates ... that would be quite a bit of work at this point ... so what I'd suggest is that the option to CREATE TEXT SEARCH CONFIGURATION be renamed COPY instead of TEMPLATE. Another thought here is that I'm inclined to drop the with map option and just always copy the source configuration exactly. If you don't want the map, the only other information the source can provide is a parser name, which you might as well just give directly. The other thing that was bugging me was that a lot of the dictionary types have init options that are named things like DictFile, AffFile, etc. As I mentioned before, I dislike the fact that these things are out in the filesystem rather than inside the database, and hope that that will change eventually. So I think that these names are not future-proof and should be altered to not use the word file; especially so in view of the fact that as committed, the patch doesn't let you specify a path name for them. I already did that to StopFile, which is now StopWords, but did not touch the other dictionary options. I'm not sure what to do with DictFile, because that doesn't seem to have any special meaning at all once you take out file ... Comments? regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] INSERT/SELECT and excessive foreign key checks
On 21-aug-2007, at 10:55, Alvaro Herrera wrote: It might go in if it's correct. If you have an answer to all the objections then there's no reason not to include it. But I must admit I didn't understand what was your answer to the above objection; care to rephrase? sorry, egg on my face, testing error. the revised patch doesn't catch the case of another backend deleting referenced tuples. I'll work on it some more. Lodewijk ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] tsearch2 patch status report
Heikki Linnakangas [EMAIL PROTECTED] writes: I'm skimming through tsearch code, trying to understand it. I'd like to see more comments, at least function-comments explaining what each function does, what the arguments are etc. I can try to add them as I go as well, and send a patch. Yes, the patch is not well commented. I improved matters in the interface code that I worked over (eg tsearchcmds.c), but ran out of patience for trying to comment the guts code. Please send a patch for any improvements you make. The memory management of the init functions looks weird. In spell.c, there's this piece of code: I saw that and didn't care for it much, but didn't look into exactly what would be needed to get rid of it. (I did clean up another place that had a *global* magic context pointer ... that looked like trouble waiting to happen.) If you make the outside caller create the context, does it need to be passed explicitly or can it just be CurrentMemoryContext when they are called? 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] tsearch2 patch status report
Tom Lane wrote: The memory management of the init functions looks weird. In spell.c, there's this piece of code: I saw that and didn't care for it much, but didn't look into exactly what would be needed to get rid of it. (I did clean up another place that had a *global* magic context pointer ... that looked like trouble waiting to happen.) If you make the outside caller create the context, does it need to be passed explicitly or can it just be CurrentMemoryContext when they are called? I believe CurrentMemoryContext would work just fine. I'll put together a patch. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] A couple of tsearch loose ends
Hello The other thing that was bugging me was that a lot of the dictionary types have init options that are named things like DictFile, AffFile, etc. As I mentioned before, I dislike the fact that these things are out in the filesystem rather than inside the database, and hope that that will change eventually. So I think that these names are not future-proof and should be altered to not use the word file; especially so in view of the fact that as committed, the patch doesn't let you specify a path name for them. I already did that to StopFile, which is now StopWords, but did not touch the other dictionary options. I'm not sure what to do with DictFile, because that doesn't seem to have any special meaning at all once you take out file ... and what dictionary based languages? Regards Pavel Stehule ---(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] 8.3 beta testing suggestions welcome
On Tue, 21 Aug 2007, Kevin Grittner wrote: shared_buffers = 160MB effective_cache_size = 5GB bgwriter_lru_percent = 20.0 bgwriter_lru_maxpages = 200 bgwriter_all_percent = 10.0 bgwriter_all_maxpages = 600 checkpoint_segments = 10 The other thing I realized you haven't mentioned yet is what operating system you're using. What you've done here is make your shared_buffers smaller than even the write cache on your disk controller (256MB), so that anytime a checkpoint hits it should be able to cache writing even the entire buffer cache out if necessary. You're letting the OS handle caching everything else, which is memory not involved in the checkpoint process and therefore doesn't impact your problem situation. With checkpoint_segments at 10, you can't do much activity (relative to how fast this powerful of a server can dirty pages) before a checkpoint happens, which also limits how much any one checkpoint has to clean up. And your background writer settings are aggressive enough that the entire pool is getting scanned constantly, which is wasting lots of CPU resources but you have them to spare. This is reasonable solution for working around checkpoint issues in 8.2.4, but the fact that you're still having checkpoints spaced 30 seconds apart sometimes is one weak spot you already have seen. PostgreSQL operating with a tiny shared_buffers cache is another, and you could expect overall performance to improve if it were possible to increase that without getting killed by checkpoints. In 8.3, it should be possible for you to increase both shared_buffers and checkpoint_segments significantly and still have checkpoints happen in an orderly fashion. There is no all-scan anymore, instead there's a parameter called checkpoint_completion_target; see http://developer.postgresql.org/pgdocs/postgres/wal-configuration.html My suggestion for a starting 8.3 configuration for you would be adjusting these settings as follows: shared_buffers=1GB checkpoint_segments = 50 And then try replaying your data with checkpoint_completion_target at 0.5 (default), 0.7, and 0.9 and see how each of those works out for you (0.7 is the least useful of those if you only did two). Hopefully the data replay you have can be setup to invoke the same tightly spaced checkpoint behavior you commented about. Based on the results of those tests, it may be possible to further increase shared_buffers, and checkpoint_segments/checkpoint_timeout may need some adjustment one way or another. Note that some/all of the bgwriter_lru parameters may be going away before 8.3 is done as well, that's a loose end I'm working on right now. If your project gets approved, that's what I think would be a useful test to run. That should get some good results for the community as large as well as research how upgrading to the new version might positively impact your application. You're actually in one of the situations I'm a little concerned about. All the tests that have been done by people here have suggested using checkpoint_completion_target and removing the all scan are always net positive compared to the 8.2.4 behavior, but your situation (where you're heavily using bgwriter_all_percent = 10.0 and bgwriter_all_maxpages = 600) is one where it's possible 8.3 may be a step backwards. Not likely, just possible, and it would be great to get another data point on this during the beta. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(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] 8.3 beta testing suggestions welcome
On Tue, Aug 21, 2007 at 1:12 PM, in message [EMAIL PROTECTED], Greg Smith [EMAIL PROTECTED] wrote: The other thing I realized you haven't mentioned yet is what operating system you're using. Linux version 2.6.5-7.286-bigsmp ([EMAIL PROTECTED]) (gcc version 3.3.3 (SuSE Linux)) #1 SMP Thu May 31 10:12:58 UTC 2007 SUSE LINUX Enterprise Server 9 (i586) VERSION = 9 PATCHLEVEL = 3 I know this is old, but I was happy just to get them off of Windows. :-/ My suggestion for a starting 8.3 configuration for you would be adjusting these settings as follows: shared_buffers=1GB checkpoint_segments = 50 And then try replaying your data with checkpoint_completion_target at 0.5 (default), 0.7, and 0.9 and see how each of those works out for you (0.7 is the least useful of those if you only did two). Hopefully the data replay you have can be setup to invoke the same tightly spaced checkpoint behavior you commented about. Based on the results of those tests, it may be possible to further increase shared_buffers, and checkpoint_segments/checkpoint_timeout may need some adjustment one way or another. Note that some/all of the bgwriter_lru parameters may be going away before 8.3 is done as well, that's a loose end I'm working on right now. Thanks much. If I get the approval, I'll be sure to run these three tests. It probably makes sense to get a baseline with the current config first. I assume you want me to do these with both the update and query streams running full out? Anyone else have a configuration to suggest? -Kevin ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] A couple of tsearch loose ends
COPY instead of TEMPLATE. Another thought here is that I'm inclined to drop the with map option and just always copy the source configuration exactly. If you don't want the map, the only other information the source can provide is a parser name, which you might as well just give directly. I havn't any objections. with map was introduced when another options was existed - locale and default flag. The other thing that was bugging me was that a lot of the dictionary types have init options that are named things like DictFile, AffFile, etc. As I mentioned before, I dislike the fact that these things are out in the filesystem rather than inside the database, and hope that that will change eventually. So I think that these names are not DictFile and AffFile are files of ispell ( or derived from it ) dictionaries, we don't manage that files - they require a lot of lingustic knowledge which we don't have and I don't hope that there is such man in pgsql community. So, we just use they. Managing of stop words are much more simple, so list may be stored in database, not in file. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Crash with empty dictionary
I'm fooling around with tsearch, and bumped into a segfault, when using a custom ispell dictionary with DictFile pointing to an empty file. NISortDictionary assumes there's at least one word in the dictionary, and crashes on line 941: Conf-AffixData[1] = pstrdup(Conf-Spell[0]-p.flag); I'm not sure if this is a sane way to set up a dictionary, but surely seg faulting is not the right thing to do. Should we throw an error on an empty dict file, or should we swallow it without crashing? -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] PGparam extension version 0.4
Version 0.4 of libpq param put and PGresult get functions. Added support for inet and cidr, couple bug fixes. If you compile the test file, make sure you link with the patched libpq.so. Andrew pg_param.tgz Description: application/compressed ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] A couple of tsearch loose ends
Teodor Sigaev [EMAIL PROTECTED] writes: I havn't any objections. with map was introduced when another options was existed - locale and default flag. OK, I'll make that happen. The other thing that was bugging me was that a lot of the dictionary types have init options that are named things like DictFile, AffFile, etc. DictFile and AffFile are files of ispell ( or derived from it ) dictionaries, we don't manage that files - they require a lot of lingustic knowledge which we don't have and I don't hope that there is such man in pgsql community. So, we just use they. Hmm ... I suppose, but I'd still prefer that the option names didn't include the word file. Also, while revising the reference pages for the syntax changes I made, I realized that there's further simplification possible for the dictionary commands. I changed these commands to use the same definition list construct that's used by CREATE OPERATOR and such. It has the nice property that the option keywords aren't actually keywords in the eyes of the grammar, they're just any identifiers. So what we have got as of CVS HEAD is CREATE TEXT SEARCH DICTIONARY name ( TEMPLATE = template [, OPTION = init_options ] ) ALTER TEXT SEARCH DICTIONARY name ( OPTION = init_options ) where init_options is supposed to be a string literal containing stuff like 'Language=swedish, StopWords=swedish' When you look at it, this is downright silly. Why don't we flatten the two levels together and write something like CREATE TEXT SEARCH DICTIONARY swedish ( TEMPLATE = snowball, LANGUAGE = swedish, STOPWORDS = swedish ); The original implementation couldn't do that but it's easy in the definition-list grammar. This is even more useful for ALTER, because it'd be possible to change the value of one option without having to write out the values of all the others. What I'd suggest is that we adopt the convention that an option is dropped if its name appears with no value, otherwise it's kept unless overridden with a new value. So after ALTER TEXT SEARCH DICTIONARY swedish ( STOPWORDS ); this dictionary would have LANGUAGE = swedish and no stopwords option. Any objections to changing it like that? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] PGparam extension version 0.4
Version 0.4 of libpq param put and PGresult get functions. Added support for inet and cidr, couple bug fixes. If you compile the test file, make sure you link with the patched libpq.so. Andrew pg_param.tgz Description: application/compressed ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] TOAST Threshold? Re: Status of 8.3 patches
[EMAIL PROTECTED] (Joshua D. Drake) writes: Alvaro Herrera wrote: Joshua D. Drake wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Heikki Linnakangas wrote: Joshua D. Drake wrote: I guess my point is, if the patch looks good and does not appear to hurt anything, why not apply it? At least that way we can start to review the progress of the feature itself as it starts to see use. Yeah, you mean like commit_delay. It really worked great, that reviewing of a feature, you know. It only took 3 years until someone realized that it didn't work as advertised. You can not compare the relevant smallness of use from three years ago to the explosion of use at present. It is certainly unfortunate that commit_delay didn't work as advertised, but then again had we not applied it, we would have never known in the first place and now we have the opportunity to fix or remove it. You can compare other such features that many people don't touch that are starting to show promise over time such as cpu_tuple_cost. I'd like to see a bit more available for the TOAST threshold, even if only a little bit of documentation to make it an unscary thing to modify the threshold so that I could recommend that our DBAs modify it, and *not* have them get all hinky at the fact that I'm telling them to touch the sources. Yes, I'm telling you to modify the sources. It's a safe modification - see? The documentation says so! Without this, I'll have to fight some ghastly uphill battle in order to get this set in our builds, and I *don't* want to wait until 8.4 to see our ~900 byte long XML values TOASTed. I'd be happy to help make the patch. -- (reverse (concatenate 'string ofni.secnanifxunil @ enworbbc)) http://cbbrowne.com/info/linux.html :FATAL ERROR -- VECTOR OUT OF HILBERT SPACE ---(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] tsearch2 patch status report
[EMAIL PROTECTED] (Tom Lane) writes: The main thing that is lacking at the moment is documentation. The stuff Bruce has been working on will be good introductory material, but we've got basically zip in reference material. I'll do some work on that over the next couple of days, but there's probably room for more hands. If someone has either text or HTML formatted material that seems plausible, I'd be happy to help get it into DocBook form. -- cbbrowne,@,acm.org http://cbbrowne.com/info/linuxxian.html Although Unix is more reliable, NT may become more reliable with time -- Ron Redman, deputy technical director of the Fleet Introduction Division of the Aegis Program Executive Office, US Navy. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] tsearch2 patch status report
Oleg Bartunov wrote: On Mon, 20 Aug 2007, Tom Lane wrote: I've applied version 0.58 of the patch with a lot of further editorializing. I feel fairly confident now in the code that interfaces Great ! Just checked and most things after trivial changes are working ! We need to summarize changes and provide upgraide guide. The main thing that is lacking at the moment is documentation. The stuff Bruce has been working on will be good introductory material, but we've got basically zip in reference material. I'll do some work on that over the next couple of days, but there's probably room for more hands. We have SQL reference and I'm sure Bruce should have it too. Bruce, let me know when you do last changes, so I could read docs again OK, I am unclear how the main documentation is going to change. I will look over the reference pages and get them online soon and we can all see them. This is, by a wide margin, the largest single patch ever to hit the Postgres CVS tree. Congratulations to Oleg and Teodor on seeing it through! It was really hard task for all of us. It's pity I don't drink at all, but Teodor will drink the health of text search in PostgreSQL today ! Tom, as I know, already drain out bottle of vodka, so Bruce, you need to join us ! Shame I don't drink either. -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.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: [HACKERS] tsearch2 patch status report
Just a heads up, not sure if you guys are aware of it. But one of the Makefile's (src/backend/tsearch/Makefile) added by this patch breaks the build out of source tree feature of autoconf/automake. The problem is pretty straightforward, and after adding $(srcdir) everything seems to be fine. Index: src/backend/tsearch/Makefile === RCS file: /u01/mirrors/cvs/pgsql/pgsql/src/backend/tsearch/Makefile,v retrieving revision 1.1 diff -p -c -r1.1 Makefile *** src/backend/tsearch/Makefile21 Aug 2007 01:11:18 - 1.1 --- src/backend/tsearch/Makefile21 Aug 2007 23:58:37 - *** depend dep: *** 31,37 .PHONY: install-data install-data: $(DICTFILES) installdirs for i in $(DICTFILES); \ ! do $(INSTALL_DATA) $$i '$(DESTDIR)$(datadir)/$(DICTDIR)/'$$i; \ done installdirs: --- 31,37 .PHONY: install-data install-data: $(DICTFILES) installdirs for i in $(DICTFILES); \ ! do $(INSTALL_DATA) $(srcdir)/$$i '$(DESTDIR)$(datadir)/$(DICTDIR)/'$$i; \ done installdirs:
Re: [HACKERS] tsearch2 patch status report
On 8/21/07, Magnus Hagander [EMAIL PROTECTED] wrote: OTOH, if we do it as a compat package, we need to set a firm end-date on it, so we don't have to maintain it forever. Given the issues always at hand for doing such an upgrade, my vote is actually for ripping it out completely and take the migration pain once and then be done with it. I would suggest making a pgfoundry project...that's what was done with userlocks. I'm pretty certain no one besides me has ever used the wrappers I created...a lot more people use tsearch2 than userlocks though. merlin ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] Is ALTER TEXT SEARCH CONFIGURATION PARSER = new_parser really sane?
After starting to document this stuff I'm wondering whether it really makes sense to change the parser associated with a tsearch configuration. The problem is that the new parser might have an unrelated set of token types, but we don't do anything about updating the configuration's mappings. Ensuring sane behavior here would take a whole lot of new code, and I'm not sure that I see a use-case that justifies it. So I'm tempted to take out that particular ALTER capability altogether. I note that the corresponding feature of changing a dictionary's template on-the-fly doesn't exist (though it'd actually be a lot easier to support). Comments? regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Is ALTER TEXT SEARCH CONFIGURATION PARSER = new_parser really sane?
Tom Lane wrote: After starting to document this stuff I'm wondering whether it really makes sense to change the parser associated with a tsearch configuration. The problem is that the new parser might have an unrelated set of token types, but we don't do anything about updating the configuration's mappings. Ensuring sane behavior here would take a whole lot of new code, and I'm not sure that I see a use-case that justifies it. So I'm tempted to take out that particular ALTER capability altogether. I note that the corresponding feature of changing a dictionary's template on-the-fly doesn't exist (though it'd actually be a lot easier to support). Comments? Agreed, the parser should be a central part of the configuration and changing it seems odd. If someone really wanted to change it they can create a new configuration with a new parser, then rename the new one into place. -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://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] A couple of tsearch loose ends
Tom Lane wrote: There are a couple of naming issues that I left untouched while reviewing the tsearch patch, but wanted to bring up for discussion. One thing that had me confused for awhile is that the patch uses the word template in two different ways. The main use is that a template is an object encapsulating the superuser-only aspects of defining a dictionary. When you do CREATE TEXT SEARCH DICTIONARY you have to specify a template to base it on. So in this context a dictionary and its template are different kinds of objects, and there's a persistent connection between them. What has me concerned is the idea of database templates being different from text search dictionary templates? Why can't they function the same way? On the other hand, CREATE TEXT SEARCH CONFIGURATION also uses the word template, but in this case it's an optional specification of an existing configuration that gets copied. So here, the config and the template are the same kind of object, and there's no connection between them after the copy is made. This seems a bit confusing, and I wonder whether we ought not change the terminology for one thing or the other. I don't particularly want to rename text search templates ... that would be quite a bit of work at this point ... so what I'd suggest is that the option to CREATE TEXT SEARCH CONFIGURATION be renamed COPY instead of TEMPLATE. Another thought here is that I'm inclined to drop the with map option and just always copy the source configuration exactly. If you don't want the map, the only other information the source can provide is a parser name, which you might as well just give directly. Agreed on the use of COPY. I already pointed out this confusion in a previous email. -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://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
Re: [HACKERS] A couple of tsearch loose ends
On Tue, 21 Aug 2007, Tom Lane wrote: When you look at it, this is downright silly. Why don't we flatten the two levels together and write something like CREATE TEXT SEARCH DICTIONARY swedish ( TEMPLATE = snowball, LANGUAGE = swedish, STOPWORDS = swedish ); Dictionary is a program with its own options, so we can't know in advance what actual options it uses. We can reserve some options, though. This is a very useful feature. Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(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] tsearch2 patch status report
On Tue, 21 Aug 2007, Chris Browne wrote: [EMAIL PROTECTED] (Tom Lane) writes: The main thing that is lacking at the moment is documentation. The stuff Bruce has been working on will be good introductory material, but we've got basically zip in reference material. I'll do some work on that over the next couple of days, but there's probably room for more hands. If someone has either text or HTML formatted material that seems plausible, I'd be happy to help get it into DocBook form. Documentation existed for months on DocBook form. I even got an offer to translate it to chinese while patch was evaluated. Bruce did a lot of editorial work and now he maintain cvs version. What we really need - is a summary of changes and upgrade guide. Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(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] Is ALTER TEXT SEARCH CONFIGURATION PARSER = new_parser really sane?
On Tue, 21 Aug 2007, Tom Lane wrote: After starting to document this stuff I'm wondering whether it really makes sense to change the parser associated with a tsearch configuration. The problem is that the new parser might have an unrelated set of token types, but we don't do anything about updating the configuration's mappings. looks reasonable, we could always create new parser. Ensuring sane behavior here would take a whole lot of new code, and I'm not sure that I see a use-case that justifies it. So I'm tempted to take out that particular ALTER capability altogether. I note that the corresponding feature of changing a dictionary's template on-the-fly doesn't exist (though it'd actually be a lot easier to support). yes, here we tried to be sane Comments? Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Crash with empty dictionary
Heikki Linnakangas [EMAIL PROTECTED] writes: I'm not sure if this is a sane way to set up a dictionary, but surely seg faulting is not the right thing to do. Should we throw an error on an empty dict file, or should we swallow it without crashing? Offhand I'd say that an empty file is a legitimate corner case, so we should just take it silently. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
