Re: [HACKERS] default_text_search_config and expression indexes
On Fri, 27 Jul 2007, Pavel Stehule wrote: 1) Document the problem and do nothing else. 2) Make default_text_search_config a postgresql.conf-only setting, thereby making it impossible to change by non-super users, or make it a super-user-only setting. 3) Remove default_text_search_config and require the configuration to be specified in each function call. Hello, 2+. One of the most important purpose of integrating tsearch2 was to facilitate full-text search for people in hosting environment. Usually, they have no superuser rights. I'm asking don't forget about them ! There is no problem with current behaviour once user understand what he do. 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] Updated tsearch documentation
On Thu, 26 Jul 2007, Bruce Momjian wrote:
Oleg Bartunov wrote:
Bruce,
I sent you link to my wiki page with summary of changes
http://www.sai.msu.su/~megera/wiki/ts_changes
Your documentation looks rather old.
I have updated it to reflect your changes:
http://momjian.us/expire/fulltext/HTML/textsearch-tables.html
Bruce, I noticed you miss many changes. For example,
options for stemmer has changed (it's documented in my ts_changes),
so in
http://momjian.us/expire/fulltext/HTML/textsearch-tables.html#TEXTSEARCH-TABLES-CONFIGURATION
ALTER TEXT SEARCH DICTIONARY en_stem SET OPTION 'english-utf8.stop';
should be
ALTER TEXT SEARCH DICTIONARY en_stem SET OPTION
'StopFile=english-utf8.stop, Language=english';
Also, this is wrong
DROP TEXT SEARCH CONFIGURATION MAPPING ON pg FOR email, url, sfloat, uri, float;
it should be
ALTER TEXT SEARCH CONFIGURATION pg DROP MAPPING FOR email, url, sfloat, uri,
float;
Configuration now doesn't have DEFAULT flag, so \dF should not display 'Y'
=> \dF
pg_catalog | russian | Y
public | pg | Y
This is what I see now
postgres=# \dF public.*
List of fulltext configurations
Schema | Name | Description
+--+-
public | pg |
---
Oleg
On Tue, 24 Jul 2007, Bruce Momjian wrote:
I have added more documentation to try to show how full text search is
used by user tables. I think this the documentaiton is almost done:
http://momjian.us/expire/fulltext/HTML/textsearch-tables.html
---
Oleg Bartunov wrote:
On Wed, 18 Jul 2007, Bruce Momjian wrote:
Oleg, Teodor,
I am confused by the following example. How does gin know to create a
tsvector, or does it? Does gist know too?
No, gist doesn't know. I don't remember why, Teodor ?
For GIN see http://archives.postgresql.org/pgsql-hackers/2007-05/msg00625.php
for discussion
FYI, at some point we need to chat via instant messenger or IRC to
discuss the open items. My chat information is here:
http://momjian.us/main/contact.html
I send you invitation for google talk, I use only chat in gmail.
My gmail account is [EMAIL PROTECTED]
---
SELECT title
FROM pgweb
WHERE textcat(title,body) @@ plainto_tsquery('create table')
ORDER BY dlm DESC LIMIT 10;
CREATE INDEX pgweb_idx ON pgweb USING gin(textcat(title,body));
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
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
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] default_text_search_config and expression indexes
> > 1) Document the problem and do nothing else. > 2) Make default_text_search_config a postgresql.conf-only >setting, thereby making it impossible to change by non-super >users, or make it a super-user-only setting. > 3) Remove default_text_search_config and require the >configuration to be specified in each function call. > Hello, 2+. Regards Pavel Stehule ---(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] Updated tsearch documentation
On Thu, 26 Jul 2007, Bruce Momjian wrote: Oleg Bartunov wrote: On Wed, 25 Jul 2007, Erikjan wrote: In http://momjian.us/expire/fulltext/HTML/textsearch-intro.html#TEXTSEARCH-DOCUMENT it says: "A document is any text file that can be opened, read, and modified." OOps, in my original documentation it was: "Document, in usual meaning, is a text file, that one could open, read and modify." I stress that in database document is something another. http://www.sai.msu.su/~megera/postgres/fts/doc/fts-whatdb.html I have updated the documentation: http://momjian.us/expire/fulltext/HTML/textsearch-intro.html#TEXTSEARCH-DOCUMENT Is't worth to reference OpenFTS which used for indexing file system ? 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] Building CVS problem?
* Stephen Frost ([EMAIL PROTECTED]) wrote: > I did a 'cvs -z3 up' prior to this, and most things seemed to be So, of course, in re-reading this I think to myself "wait, this guy should be using 'cvs -z3 up -dP'", and while I havn't tested it yet, the directories showed up, so I'm thinking problem solved. Sorry for the noise. Thanks, Stephen signature.asc Description: Digital signature
[HACKERS] Building CVS problem?
Greetings, In working on the minor patch to COPY CSV mode tonight, I ran into a problem with the utf8 conversion build process in CSV and was just wondering if there's something wacky on my system or if other people have seen this: [EMAIL PROTECTED]:.../pgsql.build> ../pgsql/configure [...] [EMAIL PROTECTED]:.../pgsql.build> make [...] make: Entering an unknown directory make: *** utf8_and_euc_jis_2004: No such file or directory. Stop. make: Leaving an unknown directory I did a 'cvs -z3 up' prior to this, and most things seemed to be fine... Removing the one above and a couple others recently added allowed the build to finish (though was rather annoying) and then I had to hack up the create_conversion.sql to not try to include them, which also wasn't fun. I guess I'll see about doing a new full checkout of the tree, but I wonder if anyone else regularly builds in a directory seperate from the source tree and if this is perhaps an issue with the Makefiles/etc. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] stats_block_level
Satoshi Nagayasu <[EMAIL PROTECTED]> writes: > I think the stats stuff should be on by default even if it causes > some performance penalty. > Because when we have performance problems on the production system, > it needs more performance penalty (about 5%~) to measure the stats > by turning their params on. > In real scenario, we always need the performance information, > so we always need to turn. So I want the performance information > can be taken by default. I don't really agree with this argument. I've been reading pgsql-performance for some years now, and I can't recall any incident whatsoever in which we asked somebody for their stats_block_level numbers. To be honest I think those numbers are just about useless. However, in the current state of the system it seems to be nearly free to collect them if we are collecting row-level stats, and since that's happening by default as of 8.3, it's probably worth simplifying the user-visible behavior by collecting both sets of stats if we collect either. 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
Re: [HACKERS] stats_block_level
Tom, >> Yes. It's pure overhead with no redeeming social value except to those >> who actually want to look at that sort of stat, and those who do can >> certainly turn it on for themselves. I think the stats stuff should be on by default even if it causes some performance penalty. Because when we have performance problems on the production system, it needs more performance penalty (about 5%~) to measure the stats by turning their params on. In real scenario, we always need the performance information, so we always need to turn. So I want the performance information can be taken by default. Just my thought. Tom Lane wrote: > I wrote: >> "Simon Riggs" <[EMAIL PROTECTED]> writes: >>> Anybody got any objection to setting it on by default? > >> Yes. It's pure overhead with no redeeming social value except to those >> who actually want to look at that sort of stat, and those who do can >> certainly turn it on for themselves. > > On second thought ... the cost of incrementing n_blocks_read etc is > certainly negligible. The overhead comes from sending messages to the > collector, having the collector maintain table entries, writing those > entries out to a file, etc. And AFAICS all that overhead is expended > per table: if you touch a relation during a transaction, the ensuing > costs are identical no matter whether you have stats_block_level or > stats_row_level or both turned on. > > Furthermore, it seems pretty likely that a transaction that creates any > row-level counts for a table will also create block-level counts, and > vice versa. > > So maybe the *real* question to ask is why we have separate GUCs for > stats_row_level and stats_block_level. Shouldn't we fold them into a > single switch? It's hard to see what having just one of them turned on > will save. > > regards, tom lane > > ---(end of broadcast)--- > TIP 6: explain analyze is your friend -- NAGAYASU Satoshi <[EMAIL PROTECTED]> Phone: +81-50-5546-2496 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] stats_block_level
Tom Lane wrote: > I wrote: > > "Simon Riggs" <[EMAIL PROTECTED]> writes: > >> Anybody got any objection to setting it on by default? > > > Yes. It's pure overhead with no redeeming social value except to those > > who actually want to look at that sort of stat, and those who do can > > certainly turn it on for themselves. > > On second thought ... the cost of incrementing n_blocks_read etc is > certainly negligible. The overhead comes from sending messages to the > collector, having the collector maintain table entries, writing those > entries out to a file, etc. And AFAICS all that overhead is expended > per table: if you touch a relation during a transaction, the ensuing > costs are identical no matter whether you have stats_block_level or > stats_row_level or both turned on. > > Furthermore, it seems pretty likely that a transaction that creates any > row-level counts for a table will also create block-level counts, and > vice versa. > > So maybe the *real* question to ask is why we have separate GUCs for > stats_row_level and stats_block_level. Shouldn't we fold them into a > single switch? It's hard to see what having just one of them turned on > will save. Agreed. Jan had a tendency to add more GUCs than needed "just in case", but usually "case" never happened. -- 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
[HACKERS] LSN grouping within clog pages
I've been looking at the way that the async-commit patch conserves shared memory space by remembering async commit LSNs for groups of transactions on a clog page, rather than having an LSN for each individual transaction slot. This seems like a good plan to me, but I'm confused about one point. The README text claims that each LSN represents a contiguous group of transactions, that is, with the proposed parameters each LSN would represent 256 sequential transactions. However, it looks to me that what the code is actually doing: #define GetLSNIndex(slotno, xid)((slotno) * CLOG_LSNS_PER_PAGE + \ (xid) % (TransactionId) CLOG_XACTS_PER_LSN) results in transactions that are spaced 256 XIDs apart sharing the same LSN slot. I'm not sure whether the code is good and the README is bogus, or vice versa. Sharing LSNs among contiguous groups of XIDs seems appealing because you'd expect that such a group would have relatively close LSNs, and so not much information is lost. OTOH, the modulo idea is interesting too, because if the transaction rate is less than 256 commits per walwriter cycle, you'd effectively have exact information for all the currently unflushed transactions. But the downside is that transactions that are really quite old might transiently appear un-hintable because some later transaction that happens to share that LSN slot isn't flushed yet. Thoughts? BTW, I don't think I believe at all the arguments given in the README about what CLOG_LSNS_PER_PAGE should be, particularly since possible changes in BLCKSZ weren't factored in. I'm inclined to set it so that the LSNs take up the same amount of space as the clog buffers themselves, ie, BLCKSZ/8 LSNs per page. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Quick idea for reducing VACUUM contention
"Simon Riggs" <[EMAIL PROTECTED]> wrote: > Read the heap blocks in sequence, but make a conditional lock for > cleanup on each block. If we don't get it, sleep, then try again when we > wake up. If we fail the second time, just skip the block completely. When we allow some skips in removing dead tuples, can we guarantee pg_class.relfrozenxid? I think we might need additional "freezing-xmax" operations to avoid XID-wraparound in the first path of vacuum, though it hardly occurs. It might be a future topic ... if we are in the direciton of "optimistic sweeping", is it possible to remove the second path of vacuum completely? We just add XID of the vacuum to dead tuples we see in the first path. When backends find a dead tuple and see the transaction identified by XID in it has commited, they can freely reuse the area of the dead tuple because we can assume index entries pointing the tuple have been removed by the vacuum. We would use the infrastructure introduced by HOT for this purpose. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] stats_block_level
Dave Page <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> So maybe the *real* question to ask is why we have separate GUCs for >> stats_row_level and stats_block_level. Shouldn't we fold them into a >> single switch? It's hard to see what having just one of them turned on >> will save. > Any reason not to just fold them both into stats_start_collector ? Well, then you couldn't turn collection on and off without restarting the postmaster, which might be a pain. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] default_text_search_config and expression indexes
Oleg Bartunov wrote: > >> Second, I can't figure out how to reference a non-default > >> configuration. > > > > See the multi-argument versions of to_tsvector etc. > > > > I do see a problem with having to_tsvector(config, text) plus > > to_tsvector(text) where the latter implicitly references a config > > selected by a GUC variable: how can you tell whether a query using the > > latter matches a particular index using the former? There isn't > > anything in the current planner mechanisms that would make that work. > > Probably, having default text search configuration is not a good idea > and we could just require it as a mandatory parameter, which could > eliminate many confusion with selecting text search configuration. We have to decide if we want a GUC default_text_search_config, and if so when can it be changed. Right now there are three ways to create a tsvector (or tsquery) ::tsvector to_tsvector(value) to_tsvector(config, value) (ignoring plainto_tsvector) Only the last one specifies the configuration. The others use the configuration specified by default_text_search_config. (We had an previous discussion on what the default value of default_text_search_config should be, and it was decided it should be set via initdb based on a flag or the locale.) Now, because most people use a single configuration, they can just set default_text_search_config and there is no need to specify the configuration name. However, expression indexes cause a problem here: http://momjian.us/expire/fulltext/HTML/textsearch-tables.html#TEXTSEARCH-TABLES-INDEX We recommend that users create an expression index on the column they want to do a full text search on, e.g. CREATE INDEX pgweb_idx ON pgweb USING gin(to_tsvector(body)); However, the big problem is that the expressions used in expression indexes should not change their output based on the value of a GUC variable (because it would corrupt the index), but in the case above, default_text_search_config controls what configuration is used, and hence the output of to_tsvector is changed if default_text_search_config changes. We have a few possible options: 1) Document the problem and do nothing else. 2) Make default_text_search_config a postgresql.conf-only setting, thereby making it impossible to change by non-super users, or make it a super-user-only setting. 3) Remove default_text_search_config and require the configuration to be specified in each function call. If we remove default_text_search_config, it would also make ::tsvector casting useless as well. -- 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 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] stats_block_level
Tom Lane wrote: > So maybe the *real* question to ask is why we have separate GUCs for > stats_row_level and stats_block_level. Shouldn't we fold them into a > single switch? It's hard to see what having just one of them turned on > will save. Any reason not to just fold them both into stats_start_collector ? Regards, Dave. ---(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] stats_block_level
I wrote: > "Simon Riggs" <[EMAIL PROTECTED]> writes: >> Anybody got any objection to setting it on by default? > Yes. It's pure overhead with no redeeming social value except to those > who actually want to look at that sort of stat, and those who do can > certainly turn it on for themselves. On second thought ... the cost of incrementing n_blocks_read etc is certainly negligible. The overhead comes from sending messages to the collector, having the collector maintain table entries, writing those entries out to a file, etc. And AFAICS all that overhead is expended per table: if you touch a relation during a transaction, the ensuing costs are identical no matter whether you have stats_block_level or stats_row_level or both turned on. Furthermore, it seems pretty likely that a transaction that creates any row-level counts for a table will also create block-level counts, and vice versa. So maybe the *real* question to ask is why we have separate GUCs for stats_row_level and stats_block_level. Shouldn't we fold them into a single switch? It's hard to see what having just one of them turned on will save. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Updated tsearch documentation
Oleg Bartunov wrote: > On Wed, 25 Jul 2007, Erikjan wrote: > > > In > > http://momjian.us/expire/fulltext/HTML/textsearch-intro.html#TEXTSEARCH-DOCUMENT > > > > it says: > > > > "A document is any text file that can be opened, read, and modified." > > OOps, in my original documentation it was: > "Document, in usual meaning, is a text file, that one could open, read and > modify." > I stress that in database document is something another. > > http://www.sai.msu.su/~megera/postgres/fts/doc/fts-whatdb.html I have updated the documentation: http://momjian.us/expire/fulltext/HTML/textsearch-intro.html#TEXTSEARCH-DOCUMENT -- 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 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] stats_block_level
"Simon Riggs" <[EMAIL PROTECTED]> writes: > Anybody got any objection to setting it on by default? Yes. It's pure overhead with no redeeming social value except to those who actually want to look at that sort of stat, and those who do can certainly turn it on for themselves. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Updated tsearch documentation
Oleg Bartunov wrote:
> Bruce,
>
> I sent you link to my wiki page with summary of changes
> http://www.sai.msu.su/~megera/wiki/ts_changes
>
> Your documentation looks rather old.
I have updated it to reflect your changes:
http://momjian.us/expire/fulltext/HTML/textsearch-tables.html
---
>
> Oleg
> On Tue, 24 Jul 2007, Bruce Momjian wrote:
>
> >
> > I have added more documentation to try to show how full text search is
> > used by user tables. I think this the documentaiton is almost done:
> >
> > http://momjian.us/expire/fulltext/HTML/textsearch-tables.html
> >
> > ---
> >
> > Oleg Bartunov wrote:
> >> On Wed, 18 Jul 2007, Bruce Momjian wrote:
> >>
> >>> Oleg, Teodor,
> >>>
> >>> I am confused by the following example. How does gin know to create a
> >>> tsvector, or does it? Does gist know too?
> >>
> >> No, gist doesn't know. I don't remember why, Teodor ?
> >>
> >> For GIN see
> >> http://archives.postgresql.org/pgsql-hackers/2007-05/msg00625.php
> >> for discussion
> >>
> >>>
> >>> FYI, at some point we need to chat via instant messenger or IRC to
> >>> discuss the open items. My chat information is here:
> >>>
> >>> http://momjian.us/main/contact.html
> >>
> >> I send you invitation for google talk, I use only chat in gmail.
> >> My gmail account is [EMAIL PROTECTED]
> >>
> >>>
> >>> ---
> >>>
> >>> SELECT title
> >>> FROM pgweb
> >>> WHERE textcat(title,body) @@ plainto_tsquery('create table')
> >>> ORDER BY dlm DESC LIMIT 10;
> >>>
> >>> CREATE INDEX pgweb_idx ON pgweb USING gin(textcat(title,body));
> >>>
> >>>
> >>
> >>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
> >
> >
>
> 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
--
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
[HACKERS] stats_block_level
Why is stats_block_level = off by default? Is there a measurable cost to enabling this? We already have stats_row_level = on, so presumably the overhead of setting stats_block_level to on cannot be any worse than that. Anybody got any objection to setting it on by default? -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Machine available for community use
On Thu, 26 Jul 2007, Joshua D. Drake wrote: IMO, a multiboot is o.k. but a vm isn't worth it. This box is big enough to actually starting looking at SMP and I/O issues for PostgreSQL that we normally can't because we don't have access to the hardware in the community. Certainly agree with that; VM overhead is much lower than it used to be, but it's still going to fuzz exactly the kind of performance results that this box would be most useful for exploring. What I normally do in this situation is create a second primary partition on the boot drive with around 10GB of space on it that doesn't get touched by the initial OS install. Then it's straighforward to install a second Linux into there; the only time that gets tricky is if you're doing two RedHat style installs because of how they mount partitions by label. A little bit of GRUB merging after the second install, and now you've got a dual-boot system. Even in a NOC setup where you don't see the boot menu, you'd just have to change the grub.conf default and reboot in order to switch between the two. As long as a bootable partition of reasonable size is set aside like this, there's all kinds of flexibility for being able to confirm results apply to multiple Linux distributions in the future. You might even put a BSD or Solaris in that space one day. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] Quick idea for reducing VACUUM contention
Just wanted to record a quick idea in case its useful in the future. VACUUM reads all blocks in sequence and waits on each one to acquire a cleanup lock. If VACUUM is running with vacuum_delay enabled then we might take a slightly different approach: Read the heap blocks in sequence, but make a conditional lock for cleanup on each block. If we don't get it, sleep, then try again when we wake up. If we fail the second time, just skip the block completely. As long as we skip no more than 1% of the blocks we should be able to do a very good job of cleanup, yet with reduced block contention as the VACUUM proceeds. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(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
[HACKERS] Why are we waiting? Thoughts on Further Scalability
I've been thinking some more about scalability and what we need to measure in order to locate and remove the next set of bottlenecks. EXCLUSIVE LOCKS The lock wait time distribution and the sum of lock held time is of interest in understanding contention. SHARED LOCKS Shared locks present some complexities for analysing contention stats. If we look at the sum of the lock held time then we will get the wrong answer because many backends can hold an LW_SHARED mode lock at the same time. Moreover, LW_SHARED locks have queue jumping characteristics that make LW_EXCLUSIVE locks wait for substantial lengths of time. The worst of those situations was the old CheckpointStartLock which could starve a starting checkpoint for many minutes on a busy server. For locks that can be both shared and exclusive we should measure the lock wait time for shared and exclusive separately and we should measure the lock hold time only for exclusive mode. We've discussed the possibility of a third type of lock, a queued shared lock. I've not found any benefit in prototypes so far, but one day... RARE EVENTS AND TRAFFIC JAMS For queued exclusive locks the queue length is an interesting measurement over time. This is because we may find that certain rare events cause effects out of proportion to their actual duration. If the random arrival rate of new lock requests approaches the lock hold time (service time) then when a traffic jam forms it can take long periods to clear again. e.g. if a lock is randomly requested every 11us and lock service time is 10us then the lock seems like it will mostly be clear. Should the lock ever be held for an extended time, e.g. 1ms (=1000us) then a long queue will form, say about ~99 long. But the every 100us we serve 10 lock requestors while 9 more arrive. So after the traffic jam forms it will take 10,000us to clear, i.e. the traffic jam takes 10 times as long to clear as the original event that caused it. Taken to the extreme, very rare events can still be the major source of contention in a dynamic system. Now introduce non-random effects into the arrival rate distribution and you can see that flash queues can form easily and yet take a long time to clear. The maths for this is fairly hard... WHY ARE WE WAITING? Up to now we've looked at contention on single well-known LWlocks, such as BufMappingLock etc.. There will be times when we need to return to looking at those contention points, but I'm thinking we may need to begin looking at other points of contention in the server. The single well-known locks behave in different ways because each lock has different lock service times and also different access frequencies on different lock modes (shared or exclusive). We should be careful not to consider all of these locks similarly in any analysis. The second source of contention issues I see is where we hold multiple well-known locks. For example holding WALInsertLock is normal, as is holding WALWriteLock, but holding both WALInsertLock while we perform a write with WALWriteLock held is a bad thing and we would want to avoid that condition. So I'd like to look at what combinations of locks we hold and Why they were taken. The third source of contention is data block events. These are much harder to spot because they are spread across the whole buffer space. An example might be index block splits. These will occur at the same logical place in the index, though because of the way we split the new right page is always a new data block and so in a different buffer. So contention on the value "123" in an index could actually move across different buffer locks and not be visible for what it really is. Recursive block splits can cause very long waits. We need ways to be able to track those types of event. So our sources of contention are at least 1. single well-known locks 2. multiple well-known locks 3. data block contention events ??? I've thought about ways of understanding the root cause of a lock wait and there are some. But because of what we said earlier about traffic jams lasting much longer than the original event, its hard to accurately explain why certain tasks wait. Are we waiting because an earlier event caused a traffic jam, or are we waiting because a sudden rush of lock requests occurred before the original traffic jam cleared? -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Machine available for community use
* Joshua D. Drake ([EMAIL PROTECTED]) wrote: > Personally, I think CentOS 5 is probably the most reasonable choice. It is > what (or RHEL 5 which is the same) a good portion of our community is going > to be running. It is also easy to work with. > > Another alternative would be Debian or Ubuntu Dapper but they are all > really the same thing :). The nice thing is any of these three are fairly > static installs that are going to be reasonably predictable. If we can generally agree on "Linux" then it might be reasonable to consider using either VServers or just regular chroot's with different OSes loaded (when/if we want to look at a particular OS). There'd be little to no performance impact from such a solution while we'd still have different OSes to play with. Of course, the kernel would be the same for all of them, so if that's what we're interested mostly in testing/stressing then it's no good. I got the impression from some that various gcc builds, glibc versions, etc, would be good to test though and a VServer or chroot setup could work well for that. As a Debian Developer, I have to also say that Debian would be my choice. :) Though I've got a number of big toys to play w/ at work already so it's unlikely I'd have need of this system (not to mention that most of the stuff I work on in PG is usability rather than things like large-scale performance, currently anyway). Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Machine available for community use
Tom Lane wrote: "Joshua D. Drake" <[EMAIL PROTECTED]> writes: Tom Lane wrote: Really there's a pretty good argument for having several different OS'es available on the box --- I wonder whether Gavin is up to managing some sort of VM or multiboot setup. IMO, a multiboot is o.k. but a vm isn't worth it. Yeah, multiboot would be better --- otherwise you have to wonder if the vm is affecting performance at all. But I suppose multiboot would be harder to manage. Personally, I think CentOS 5 is probably the most reasonable choice. It is what (or RHEL 5 which is the same) a good portion of our community is going to be running. It is also easy to work with. Another alternative would be Debian or Ubuntu Dapper but they are all really the same thing :). The nice thing is any of these three are fairly static installs that are going to be reasonably predictable. Joshua D. Drake regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- === 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 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Machine available for community use
Let me look at what makes sense there, I am open to it. On 7/26/07, Tom Lane <[EMAIL PROTECTED]> wrote: Greg Smith <[EMAIL PROTECTED]> writes: > But this is pushing forward PostgreSQL development you're doing here. If > you've got a problem such that something works differently based on the > order in which you built the packages, which is going to be unique to > every Linux distribution already, that is itself noteworthy and deserves > engineering out. You might think of this high-end machine being a little > different as usefully adding diversity robustness in a similar way to how > the buildfarm helps improve the core right now. Actually, the thing that's concerning me is *exactly* lack of diversity. If we have just one of these things then there's a significant risk of unconsciously tuning PG towards that specific platform. I'd rather we take that risk with a well-standardized, widely used platform than with something no one else can reproduce. Really there's a pretty good argument for having several different OS'es available on the box --- I wonder whether Gavin is up to managing some sort of VM or multiboot setup. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Machine available for community use
"Joshua D. Drake" <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> Really there's a pretty good argument for having several different OS'es >> available on the box --- I wonder whether Gavin is up to managing some >> sort of VM or multiboot setup. > IMO, a multiboot is o.k. but a vm isn't worth it. Yeah, multiboot would be better --- otherwise you have to wonder if the vm is affecting performance at all. But I suppose multiboot would be harder to manage. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Machine available for community use
Tom Lane wrote: Greg Smith <[EMAIL PROTECTED]> writes: Really there's a pretty good argument for having several different OS'es available on the box --- I wonder whether Gavin is up to managing some sort of VM or multiboot setup. IMO, a multiboot is o.k. but a vm isn't worth it. This box is big enough to actually starting looking at SMP and I/O issues for PostgreSQL that we normally can't because we don't have access to the hardware in the community. Sincerely, Joshua D. Drake regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- === 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 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Machine available for community use
Greg Smith <[EMAIL PROTECTED]> writes: > But this is pushing forward PostgreSQL development you're doing here. If > you've got a problem such that something works differently based on the > order in which you built the packages, which is going to be unique to > every Linux distribution already, that is itself noteworthy and deserves > engineering out. You might think of this high-end machine being a little > different as usefully adding diversity robustness in a similar way to how > the buildfarm helps improve the core right now. Actually, the thing that's concerning me is *exactly* lack of diversity. If we have just one of these things then there's a significant risk of unconsciously tuning PG towards that specific platform. I'd rather we take that risk with a well-standardized, widely used platform than with something no one else can reproduce. Really there's a pretty good argument for having several different OS'es available on the box --- I wonder whether Gavin is up to managing some sort of VM or multiboot setup. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] lazy vacuum sleeps with exclusive lock on table
Alvaro Herrera <[EMAIL PROTECTED]> writes: > I propose applying this patch from 8.1 onwards. HEAD would get an > additional treatment to avoid the balancing problem. If you're going to insert an early unlock, it should be as early as possible, ie right after the RelationTruncate() call. The bookkeeping in between is probably trivial, but why hold the lock for it? Also, rather than just removing the vacuum_delay_point call, you need a comment explicitly pointing out why that loop hasn't got one. Otherwise somebody will think it an oversight and put it back in someday. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Machine available for community use
On Thu, 26 Jul 2007, Gregory Stark wrote: So for me to reproduce your [Gentoo] environment you would have to send me the complete history of what packages you installed. I would have to reproduce the entire history including installing and building intermediate versions. If one's goal is to be able to make several copies of a server run completely identical builds of all software down to the build order level, then Gentoo obviously makes that more difficult than other distributions. It's easier if you build each replicant at the same time and then keep them synchronized, but cloning a machine that's already out there and has been through a series of updates that perfectly is as challenging as you describe. If the primary goal here was reproducable benchmarks where you needed SPEC-submission level version control, Gentoo would be a completely inappropriate choice. But this is pushing forward PostgreSQL development you're doing here. If you've got a problem such that something works differently based on the order in which you built the packages, which is going to be unique to every Linux distribution already, that is itself noteworthy and deserves engineering out. You might think of this high-end machine being a little different as usefully adding diversity robustness in a similar way to how the buildfarm helps improve the core right now. I think I have to exit this discussion before I start sounding like a Gentoo fanboi and make my Linux consulting clients nervous. Go RedHat! -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Machine available for community use
"Greg Smith" <[EMAIL PROTECTED]> writes: > On Wed, 25 Jul 2007, Gregory Stark wrote: > >> Does gentoo these days have binary packages? source packages do implicitly >> require custom builds... > > You can install with binaries now so it doesn't take forever to get started, > but the minute you're adding/updating you're going to be building. The main > point I was trying to make is that if you don't do anything special to > customize the standard Gentoo compilation setup, the amount of variation > between Gentoo builds on different machines isn't significantly greater than > that which exists between the various Linux distributions. One could make a > case that the big glibc differences between Debian Stable and everybody else > right now provides a similar scale of variation in results that would impact > reproducibility. Well even so another Debian system with the same set of packages (at the same version) will be equivalent to mine. Whereas gentoo system will depend on the order that the packages were installed. If you installed kerberos while you had an older version of the copiler or crypto libraries installed and then upgraded the crypto library or compiler then your kerberos library will differ from mine which was compiled by a different compiler or against a different set of crypto headers. So for me to reproduce your environment you would have to send me the complete history of what packages you installed. I would have to reproduce the entire history including installing and building intermediate versions. > I threw out some criticism suggesting where RedHat is at a slight disadvantage > for completeness sake, and so Gavin wasn't completely alone at expressing some > distaste for the issues it introduces compared to Gentoo (potentially harder > package installation and less flexiblity for running bleeding-edge kernels > with > RHEL). Sure, that's why I run Debian and get really annoyed whenever I use a Redhat system. One Redhat I'm forever saying "where's this utility" or "why is this program 6 months out of date?". But that's a personal desktop machine. This is shared resource that shouldn't be constantly changing or having new versions of stuff installed -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] lazy vacuum sleeps with exclusive lock on table
Alvaro Herrera wrote:
> Tom Lane wrote:
> > Alvaro Herrera <[EMAIL PROTECTED]> writes:
> > > What I'm requesting here is that the sleep in count_nondeletable_pages()
> > > be removed and that change backpatched to 8.2 and 8.1.
> >
> > Are you sure that that is, and always will be, the only sleep in that
> > part of the code path?
>
> It is currently, as far as I can see, the only sleep. I think we could
> backpatch the removal of that call, and consider changing the
> cost_delay parameters when we acquire the exclusive lock in HEAD.
I noticed that autovacuum can reset VacuumCostDelay to a non-zero value
when the cost balancing code runs. Of course, we can reset the target
value so that resetting it does not cause a problem.
I propose applying this patch from 8.1 onwards. HEAD would get an
additional treatment to avoid the balancing problem.
Note that I am releasing the exclusive lock on the table after the
truncate is done.
--
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Index: src/backend/commands/vacuumlazy.c
===
RCS file: /home/alvherre/cvs/pgsql/src/backend/commands/vacuumlazy.c,v
retrieving revision 1.61.2.2
diff -c -p -r1.61.2.2 vacuumlazy.c
*** src/backend/commands/vacuumlazy.c 4 Mar 2006 19:09:23 - 1.61.2.2
--- src/backend/commands/vacuumlazy.c 26 Jul 2007 06:01:59 -
*** lazy_truncate_heap(Relation onerel, LVRe
*** 821,828
vacrelstats->pages_removed = old_rel_pages - new_rel_pages;
/*
! * We keep the exclusive lock until commit (perhaps not necessary)?
*/
ereport(elevel,
(errmsg("\"%s\": truncated %u to %u pages",
--- 821,829
vacrelstats->pages_removed = old_rel_pages - new_rel_pages;
/*
! * Release our exclusive lock before going away
*/
+ UnlockRelation(onerel, AccessExclusiveLock);
ereport(elevel,
(errmsg("\"%s\": truncated %u to %u pages",
*** count_nondeletable_pages(Relation onerel
*** 854,861
bool tupgone,
hastup;
- vacuum_delay_point();
-
blkno--;
buf = ReadBuffer(onerel, blkno);
--- 855,860
---(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
