Re: website doc search is extremely SLOW - Mailing list pgsql-general

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@lorenso.com
> > > > >
> > > > >
> > > > >
> > > > > ---------------------------(end of broadcast)---------------------------
> > > > > TIP 3: if posting/reading through Usenet, please send an appropriate
> > > > >       subscribe-nomail command to majordomo@postgresql.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@hub.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@hub.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@commandprompt.com - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL