Re: website doc search is extremely SLOW

Hello,

Why are we not using Tsearch2?

Besides the obvious of getting everything into the database?

Sincerely,

Joshua D. Drake

On Tue, 2003-12-30 at 21:24, Marc G. Fournier wrote:
> On Wed, 31 Dec 2003, Dave Cramer wrote:
>
> > Why are their multiple servers hitting the same db
> >
> > what servers are searching through the db?
>
> www.postgresql.org and archives.postgresql.org both hit the same DB ...
> the point is more that whatever alternative that someone can suggest, it
> has to be able to be accessed centrally from several different machines
> ... when I just tried a search, I was the only one hitting the database,
> and the search was dreadful, so it isn't a problem with multiple
> connections :(
>
> Just as an FYI, the database server has sufficient RAM on her, so it isn't
> a swapping issue ... swap usuage right now, after 77 days uptime:
>
> Device 1K-blocks Used Avail Capacity Type
> /dev/da0s1b 8388480 17556 8370924 0% Interleaved
>
>
> >
> > Dave
> > On Wed, 2003-12-31 at 00:04, Marc G. Fournier wrote:
> > > does anyone know anything better then mnogosearch, that works with
> > > PostgreSQL, for doing indexing? the database server is a Dual Xeon 2.4G,
> > > 4G of RAM, and a load avg right now of a lowly 1.5 ... the file system is
> > > 3x72G drive in a RAID5 configuration, and the database server is 7.4 ...
> > > the mnogosearch folk use mysql for their development, so its possible
> > > there is something they are doing that is slowing this process down, to
> > > compensate for a fault in mysql, but this is ridiculous ...
> > >
> > > note that I have it setup with what the mnogosearch folk lists as being
> > > 'the fastest schema for large indexes' or 'crc-multi' ...
> > >
> > > right now, we're running only 373k docs:
> > >
> > > isvr5# indexer -S
> > >
> > > Database statistics
> > >
> > > Status Expired Total
> > > -----------------------------
> > > 415 0 311 Unsupported Media Type
> > > 302 0 1171 Moved Temporarily
> > > 502 0 43 Bad Gateway
> > > 414 0 3 Request-URI Too Long
> > > 301 0 307 Moved Permanently
> > > 404 0 1960 Not found
> > > 410 0 1 Gone
> > > 401 0 51 Unauthorized
> > > 304 0 16591 Not Modified
> > > 200 0 373015 OK
> > > 504 0 48 Gateway Timeout
> > > 400 0 3 Bad Request
> > > 0 2 47 Not indexed yet
> > > -----------------------------
> > > Total 2 393551
> > >
> > > and a vacuum analyze runs nightly ...
> > >
> > > anyone with suggestions/ideas? has to be something client/server, like
> > > mnogosearch, as we're dealing with multiple servers searching against the
> > > same database ... so I don't *think* that ht/Dig is a solution, but may be
> > > wrong there ...
> > >
> > > On Wed, 30 Dec 2003, Dave Cramer wrote:
> > >
> > > > search for create index took 59 seconds ?
> > > >
> > > > I've got a fairly (< 1 second for the same search) fast search engine on
> > > > the docs at
> > > >
> > > > http://postgresintl.com/search?query=create index
> > > >
> > > > if that link doesn't work, try
> > > >
> > > > postgres.fastcrypt.com/search?query=create index
> > > >
> > > > for now you will have to type it, I'm working on indexing it then making
> > > > it pretty
> > > >
> > > > Dave
> > > >
> > > > On Tue, 2003-12-30 at 22:39, D. Dante Lorenso wrote:
> > > > > Marc G. Fournier wrote:
> > > > >
> > > > > >On Mon, 29 Dec 2003, D. Dante Lorenso wrote:
> > > > > >
> > > > > >>Trying to use the 'search' in the docs section of PostgreSQL.org
> > > > > >>is extremely SLOW. Considering this is a website for a database
> > > > > >>and databases are supposed to be good for indexing content, I'd
> > > > > >>expect a much faster performance.
> > > > > >>
> > > > > >>
> > > > > >What is the full URL for the page you are looking at? Just the 'search
> > > > > >link' at the top of the page?
> > > > > >
> > > > > >
> > > > > >>Perhaps this should be worked on?
> > > > > >>
> > > > > >>
> > > > > >Looking into it right now ...
> > > > > >
> > > > > >
> > > > >
> > > > > http://www.postgresql.org/ *click Docs on top of page*
> > > > > http://www.postgresql.org/docs/ * click PostgreSQL static
> > > > > documentation *
> > > > >
> > > > > Search this document set: [ SECURITY INVOKER ] Search!
> > > > >
> > > > >
> > > > > http://www.postgresql.org/search.cgi?ul=http://www.postgresql.org/docs/7.4/static/&q=SECURITY+INVOKER
> > > > >
> > > > > I loaded that URL on IE and I wait like 2 minutes or more for a response.
> > > > > then, it usually returns with 1 result. I click the Search! button again
> > > > > to refresh and it came back a little faster with 0 results?
> > > > >
> > > > > Searched again from the top and it's a little faster now:
> > > > >
> > > > > * click search *
> > > > > > date
> > > > > Wed Dec 31 22:52:01 CST 2003
> > > > >
> > > > > * results come back *
> > > > > > date
> > > > > Wed Dec 31 22:52:27 CST 2003
> > > > >
> > > > > Still one result:
> > > > >
> > > > > PostgreSQL 7.4 Documentation (SQL Key Words)
> > > > > <http://www.postgresql.org/docs/7.4/static/sql-keywords-appendix.html>
> > > > > [*0.087%*]
> > > > > http://www.postgresql.org/docs/7.4/static/sql-keywords-appendix.html
> > > > > Size: 65401 bytes, modified: Tue, 25 Nov 2003, 15:02:33 AST
> > > > >
> > > > > However, the page that I SHOULD have found was this one:
> > > > >
> > > > > http://www.postgresql.org/docs/current/static/sql-createfunction.html
> > > > >
> > > > > That page has SECURITY INVOKER in a whole section:
> > > > >
> > > > > [EXTERNAL] SECURITY INVOKER
> > > > > [EXTERNAL] SECURITY DEFINER
> > > > >
> > > > > SECURITY INVOKER indicates that the function is to be executed with
> > > > > the privileges of the user that calls it. That is the default.
> > > > > SECURITY DEFINER specifies that the function is to be executed with
> > > > > the privileges of the user that created it.
> > > > >
> > > > > Dante
> > > > >
> > > > > ----------
> > > > > D. Dante Lorenso
> > > > > dante(at)lorenso(dot)com
> > > > >
> > > > >
> > > > >
> > > > > ---------------------------(end of broadcast)---------------------------
> > > > > TIP 3: if posting/reading through Usenet, please send an appropriate
> > > > > subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> > > > > message can get through to the mailing list cleanly
> > > > >
> > > > --
> > > > Dave Cramer
> > > > 519 939 0336
> > > > ICQ # 1467551
> > > >
> > > >
> > >
> > > ----
> > > Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
> > > Email: scrappy(at)hub(dot)org Yahoo!: yscrappy ICQ: 7615664
> > >
> > --
> > Dave Cramer
> > 519 939 0336
> > ICQ # 1467551
> >
> >
>
> ----
> Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
> Email: scrappy(at)hub(dot)org Yahoo!: yscrappy ICQ: 7615664
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd(at)commandprompt(dot)com - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL