Thread

1. Wich hardware suits best for large full-text indexed databases

   Diogo Biazus <diogo@ikono.com.br> — 2004-03-30T21:55:07Z

   Hi folks,
   
   I have a database using tsearch2 to index 300 000 documents.
   I've already have optimized the queries, and the database is vacuumed on 
   a daily basis.
   The stat function tells me that my index has aprox. 460 000 unique words 
   (I'm using stemmer and a nice stopword list).
   The problem is performance, some queries take more than 10 seconds to 
   execute, and I'm not sure if my bottleneck is memory or io.
   The server is a Athlon XP 2000, HD ATA133, 1.5 GB RAM running postgresql 
   7.4.3 over freebsd 5.0 with lots of shared buffers and sort_mem...
   
   Does anyone has an idea of a more cost eficient solution?
   How to get a better performance without having to invest some 
   astronomicaly high amount of money?
   
   TIA,
   
   -- 
   Diogo Biazus
   diogo@ikono.com.br
   http://www.ikono.com.br
   
   
   

2. Re: Wich hardware suits best for large full-text indexed

   PT <wmoran@potentialtech.com> — 2004-03-30T23:59:42Z

   Diogo Biazus wrote:
   > Hi folks,
   > 
   > I have a database using tsearch2 to index 300 000 documents.
   > I've already have optimized the queries, and the database is vacuumed on 
   > a daily basis.
   > The stat function tells me that my index has aprox. 460 000 unique words 
   > (I'm using stemmer and a nice stopword list).
   > The problem is performance, some queries take more than 10 seconds to 
   > execute, and I'm not sure if my bottleneck is memory or io.
   > The server is a Athlon XP 2000, HD ATA133, 1.5 GB RAM running postgresql 
   > 7.4.3 over freebsd 5.0 with lots of shared buffers and sort_mem...
   > 
   > Does anyone has an idea of a more cost eficient solution?
   > How to get a better performance without having to invest some 
   > astronomicaly high amount of money?
   
   This isn't hardware related, but FreeBSD 5 is not a particularly impressive
   performer.  Especially 5.0 ... 5.2.1 would be better, but if you're shooting
   for performance, 4.9 will probably outperform both of them at this stage of
   the game.
   
   Something to consider if the query tuning that others are helping with doesn't
   solve the problem.  Follow through with that _first_ though.
   
   However, if you insist on running 5, make sure your kernel is compiled without
   WITNESS ... it speeds things up noticably.
   
   -- 
   Bill Moran
   Potential Technologies
   http://www.potentialtech.com
   
   
   

3. Re: Wich hardware suits best for large full-text indexed

   Brent Wood <b.wood@niwa.co.nz> — 2004-03-31T02:00:44Z

   
   
   > I have a database using tsearch2 to index 300 000 documents.
   > I've already have optimized the queries, and the database is vacuumed on
   > > a daily basis.
   > > The stat function tells me that my index has aprox. 460 000 unique words
   > > (I'm using stemmer and a nice stopword list).
   > > The problem is performance, some queries take more than 10 seconds to
   > > execute, and I'm not sure if my bottleneck is memory or io.
   > > The server is a Athlon XP 2000, HD ATA133, 1.5 GB RAM running postgresql
   > > 7.4.3 over freebsd 5.0 with lots of shared buffers and sort_mem...
   > >
   > > Does anyone has an idea of a more cost eficient solution?
   > > How to get a better performance without having to invest some
   > > astronomicaly high amount of money?
   
   Can you identify a bottleneck during the 10 sec? CPU at 100%, memory
   swapping like crazy, HDD access non-stop?
   
   I assume you're running cpu FSB & memory at 266. Moving to an Athlon 2600
   or faster with faster synchronous memory (DDR333 or 400) should give at
   least a 20% boost if cpu is an issue, 4Gb of RAM will be a boost if you
   are swapping or using all the memory, make sure the HDD is a 7200RPM 8mb
   cache model, or even adding a second drive & RAIDing them if HDD access is
   the problem. The high performance WD Raptor drive on a suitable SATA
   controller may give a boost, as would moving to a fast SCSI drive.
   
   
   There are also a few hardware tweaks possible, if you are running your cpu
   at FSB 266 and memory at 333, you MAY get better performance dropping
   memory to 266, as the speed loss may be more than offset by the
   gains due to having synchronous memory.
   
   Run a benchmark (bonnie or hdparm) & see that the hard drive is performing
   OK, you should be getting up around 40-50Mb/sec on a good ATA hard drive.
   
   
   It all really depends where the bottleneck is....
   
   
   Cheers,
   
     Brent Wood
   
   
   

4. Re: Wich hardware suits best for large full-text indexed

   Ericson Smith <eric@did-it.com> — 2004-03-31T05:59:04Z

   Look into running Swish-e instead:
   http://www.swish-e.org
   
   Great speed, nice engine, excellent boolean searches. We run it on 
   several sites each with over 500,000 documents. Performance is 
   consistently sub-second response time, and we also integrate it within 
   PHP, Perl and Postgresql too.
   
   I know, it is nice to use tsearch2, but we also found the performance 
   lacking for those big indices. Maybe Oleg and the tsearch2 gang have 
   some extra tips?
   
   - Ericson
   
   Bill Moran wrote:
   
   > Diogo Biazus wrote:
   >
   >> Hi folks,
   >>
   >> I have a database using tsearch2 to index 300 000 documents.
   >> I've already have optimized the queries, and the database is vacuumed 
   >> on a daily basis.
   >> The stat function tells me that my index has aprox. 460 000 unique 
   >> words (I'm using stemmer and a nice stopword list).
   >> The problem is performance, some queries take more than 10 seconds to 
   >> execute, and I'm not sure if my bottleneck is memory or io.
   >> The server is a Athlon XP 2000, HD ATA133, 1.5 GB RAM running 
   >> postgresql 7.4.3 over freebsd 5.0 with lots of shared buffers and 
   >> sort_mem...
   >>
   >> Does anyone has an idea of a more cost eficient solution?
   >> How to get a better performance without having to invest some 
   >> astronomicaly high amount of money?
   >
   >
   > This isn't hardware related, but FreeBSD 5 is not a particularly 
   > impressive
   > performer.  Especially 5.0 ... 5.2.1 would be better, but if you're 
   > shooting
   > for performance, 4.9 will probably outperform both of them at this 
   > stage of
   > the game.
   >
   > Something to consider if the query tuning that others are helping with 
   > doesn't
   > solve the problem.  Follow through with that _first_ though.
   >
   > However, if you insist on running 5, make sure your kernel is compiled 
   > without
   > WITNESS ... it speeds things up noticably.
   >
   
   
   

5. Re: Wich hardware suits best for large full-text indexed

   Oleg Bartunov <oleg@sai.msu.su> — 2004-03-31T11:18:58Z

   On Tue, 30 Mar 2004, Diogo Biazus wrote:
   
   > Hi folks,
   >
   > I have a database using tsearch2 to index 300 000 documents.
   > I've already have optimized the queries, and the database is vacuumed on
   > a daily basis.
   > The stat function tells me that my index has aprox. 460 000 unique words
   > (I'm using stemmer and a nice stopword list).
   
   460 000 unique words is a lot ! Have you seen on them ? Sometimes it's
   very useful to analyze what did you indexed and do you want all of them.
   I suggest you to use ispell dictionary and, if you index numbers
   (look statistics), use special dictionaries for integer and decimal numbers
   http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/dicts/README.intdict
   
   
   > The problem is performance, some queries take more than 10 seconds to
   > execute, and I'm not sure if my bottleneck is memory or io.
   > The server is a Athlon XP 2000, HD ATA133, 1.5 GB RAM running postgresql
   > 7.4.3 over freebsd 5.0 with lots of shared buffers and sort_mem...
   >
   > Does anyone has an idea of a more cost eficient solution?
   > How to get a better performance without having to invest some
   > astronomicaly high amount of money?
   >
   > TIA,
   >
   >
   
   	Regards,
   		Oleg
   _____________________________________________________________
   Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
   Sternberg Astronomical Institute, Moscow University (Russia)
   Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
   phone: +007(095)939-16-83, +007(095)939-23-83
   
   

6. Re: Wich hardware suits best for large full-text indexed

   Oleg Bartunov <oleg@sai.msu.su> — 2004-03-31T11:54:55Z

   On Wed, 31 Mar 2004, Ericson Smith wrote:
   
   > Look into running Swish-e instead:
   > http://www.swish-e.org
   >
   > Great speed, nice engine, excellent boolean searches. We run it on
   > several sites each with over 500,000 documents. Performance is
   > consistently sub-second response time, and we also integrate it within
   > PHP, Perl and Postgresql too.
   
   it's very different story ! There are hundreds *standalone* search engine
   based on inverted indices, but you don't have *native* access to metadata
   stored in database, so your search collection isn't consistent.
   tsearch2 was developed specially for online update and consistency
   (think about access control to documents). If you're not care about that
   you don't need tsearch2. btw, tsearch2 scaled much better with long
   queries.
   
   
   >
   > I know, it is nice to use tsearch2, but we also found the performance
   > lacking for those big indices. Maybe Oleg and the tsearch2 gang have
   > some extra tips?
   >
   
   Not very much, most of them are written in documentation. Other tips are
   general to databases, like use multi-key indices, use dictionaries, stop words,
   check words statistics.
   
   One interesting option we have - is standalone archive search based
   on OpenFTS. The main idea is to have separate searches -
   online search (fresh documents) and archive (static documents) search.
   Online search is performed using as usual (tsearch2), while archive search
   uses inverted indices ) like swish-e, google,...... The nice thing is that
   results from both searches could be easily merged because they use the
   same ranking function ! So, you may have online index for month's news and
   archive part for older news and I bet you could manage millions documents.
   
   
   
   > - Ericson
   >
   > Bill Moran wrote:
   >
   > > Diogo Biazus wrote:
   > >
   > >> Hi folks,
   > >>
   > >> I have a database using tsearch2 to index 300 000 documents.
   > >> I've already have optimized the queries, and the database is vacuumed
   > >> on a daily basis.
   > >> The stat function tells me that my index has aprox. 460 000 unique
   > >> words (I'm using stemmer and a nice stopword list).
   > >> The problem is performance, some queries take more than 10 seconds to
   > >> execute, and I'm not sure if my bottleneck is memory or io.
   > >> The server is a Athlon XP 2000, HD ATA133, 1.5 GB RAM running
   > >> postgresql 7.4.3 over freebsd 5.0 with lots of shared buffers and
   > >> sort_mem...
   > >>
   > >> Does anyone has an idea of a more cost eficient solution?
   > >> How to get a better performance without having to invest some
   > >> astronomicaly high amount of money?
   > >
   > >
   > > This isn't hardware related, but FreeBSD 5 is not a particularly
   > > impressive
   > > performer.  Especially 5.0 ... 5.2.1 would be better, but if you're
   > > shooting
   > > for performance, 4.9 will probably outperform both of them at this
   > > stage of
   > > the game.
   > >
   > > Something to consider if the query tuning that others are helping with
   > > doesn't
   > > solve the problem.  Follow through with that _first_ though.
   > >
   > > However, if you insist on running 5, make sure your kernel is compiled
   > > without
   > > WITNESS ... it speeds things up noticably.
   > >
   >
   >
   > ---------------------------(end of broadcast)---------------------------
   > TIP 6: Have you searched our list archives?
   >
   >                http://archives.postgresql.org
   >
   
   	Regards,
   		Oleg
   _____________________________________________________________
   Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
   Sternberg Astronomical Institute, Moscow University (Russia)
   Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
   phone: +007(095)939-16-83, +007(095)939-23-83
   
   

7. Re: Wich hardware suits best for large full-text indexed

   Ericson Smith <eric@did-it.com> — 2004-03-31T16:35:17Z

   Oleg Bartunov wrote:
   
   >it's very different story ! There are hundreds *standalone* search engine
   >based on inverted indices, but you don't have *native* access to metadata
   >stored in database, so your search collection isn't consistent.
   >tsearch2 was developed specially for online update and consistency
   >(think about access control to documents). If you're not care about that
   >you don't need tsearch2. btw, tsearch2 scaled much better with long
   >queries.
   >
   >  
   >
   Actually swish-e has excellent support for metadata. This allows you to 
   nicely partition your indices, or to search only user-defined parts 
   based on as much custom meta-data as you'd care to define.  Granted 
   tsearch2 allows you to have *live* updates to the index. But we usually 
   reindex nightly and that tends to be good enough for most cases.
   
   - Ericson Smith
   
   >
   >
   >  
   >
   >>- Ericson
   >>
   >>Bill Moran wrote:
   >>
   >>    
   >>
   >>>Diogo Biazus wrote:
   >>>
   >>>      
   >>>
   >>>>Hi folks,
   >>>>
   >>>>I have a database using tsearch2 to index 300 000 documents.
   >>>>I've already have optimized the queries, and the database is vacuumed
   >>>>on a daily basis.
   >>>>The stat function tells me that my index has aprox. 460 000 unique
   >>>>words (I'm using stemmer and a nice stopword list).
   >>>>The problem is performance, some queries take more than 10 seconds to
   >>>>execute, and I'm not sure if my bottleneck is memory or io.
   >>>>The server is a Athlon XP 2000, HD ATA133, 1.5 GB RAM running
   >>>>postgresql 7.4.3 over freebsd 5.0 with lots of shared buffers and
   >>>>sort_mem...
   >>>>
   >>>>Does anyone has an idea of a more cost eficient solution?
   >>>>How to get a better performance without having to invest some
   >>>>astronomicaly high amount of money?
   >>>>        
   >>>>
   >>>This isn't hardware related, but FreeBSD 5 is not a particularly
   >>>impressive
   >>>performer.  Especially 5.0 ... 5.2.1 would be better, but if you're
   >>>shooting
   >>>for performance, 4.9 will probably outperform both of them at this
   >>>stage of
   >>>the game.
   >>>
   >>>Something to consider if the query tuning that others are helping with
   >>>doesn't
   >>>solve the problem.  Follow through with that _first_ though.
   >>>
   >>>However, if you insist on running 5, make sure your kernel is compiled
   >>>without
   >>>WITNESS ... it speeds things up noticably.
   >>>
   >>>      
   >>>
   >>---------------------------(end of broadcast)---------------------------
   >>TIP 6: Have you searched our list archives?
   >>
   >>               http://archives.postgresql.org
   >>
   >>    
   >>
   >
   >	Regards,
   >		Oleg
   >_____________________________________________________________
   >Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
   >Sternberg Astronomical Institute, Moscow University (Russia)
   >Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
   >phone: +007(095)939-16-83, +007(095)939-23-83
   >
   >  
   >
   

8. Re: Wich hardware suits best for large full-text indexed

   Diogo Biazus <diogo@ikono.com.br> — 2004-03-31T16:52:33Z

   Bill Moran wrote:
   
   > Diogo Biazus wrote:
   >
   >> Hi folks,
   >>
   >> I have a database using tsearch2 to index 300 000 documents.
   >> I've already have optimized the queries, and the database is vacuumed 
   >> on a daily basis.
   >> The stat function tells me that my index has aprox. 460 000 unique 
   >> words (I'm using stemmer and a nice stopword list).
   >> The problem is performance, some queries take more than 10 seconds to 
   >> execute, and I'm not sure if my bottleneck is memory or io.
   >> The server is a Athlon XP 2000, HD ATA133, 1.5 GB RAM running 
   >> postgresql 7.4.3 over freebsd 5.0 with lots of shared buffers and 
   >> sort_mem...
   >>
   >> Does anyone has an idea of a more cost eficient solution?
   >> How to get a better performance without having to invest some 
   >> astronomicaly high amount of money?
   >
   >
   > This isn't hardware related, but FreeBSD 5 is not a particularly 
   > impressive
   > performer.  Especially 5.0 ... 5.2.1 would be better, but if you're 
   > shooting
   > for performance, 4.9 will probably outperform both of them at this 
   > stage of
   > the game.
   >
   > Something to consider if the query tuning that others are helping with 
   > doesn't
   > solve the problem.  Follow through with that _first_ though.
   >
   > However, if you insist on running 5, make sure your kernel is compiled 
   > without
   > WITNESS ... it speeds things up noticably.
   
   Thanks for the advice, I'll try recompiling the kernel. Does the freebsd 
   version make a noticeable diference?
   
   Regards,
   
   -- 
   Diogo Biazus
   diogo@ikono.com.br
   http://www.ikono.com.br
   
   
   

9. Re: Wich hardware suits best for large full-text indexed

   Diogo Biazus <diogo@ikono.com.br> — 2004-03-31T17:19:06Z

   Oleg Bartunov wrote:
   
   >On Tue, 30 Mar 2004, Diogo Biazus wrote:
   >
   >  
   >
   >>Hi folks,
   >>
   >>I have a database using tsearch2 to index 300 000 documents.
   >>I've already have optimized the queries, and the database is vacuumed on
   >>a daily basis.
   >>The stat function tells me that my index has aprox. 460 000 unique words
   >>(I'm using stemmer and a nice stopword list).
   >>    
   >>
   >
   >460 000 unique words is a lot ! Have you seen on them ? Sometimes it's
   >very useful to analyze what did you indexed and do you want all of them.
   >I suggest you to use ispell dictionary and, if you index numbers
   >(look statistics), use special dictionaries for integer and decimal numbers
   >http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/dicts/README.intdict
   >  
   >
   I 'll try the ispell dictionaries and dicts for numbers too ;)
   Could the synonym dictionary help me on this (reducing unique words)?
   
   thanks,
   
   -- 
   Diogo Biazus
   diogo@ikono.com.br
   http://www.ikono.com.br
   
   
   

10. Re: Wich hardware suits best for large full-text indexed

    PT <wmoran@potentialtech.com> — 2004-03-31T22:42:43Z

    Diogo Biazus wrote:
    > Bill Moran wrote:
    > 
    >> Diogo Biazus wrote:
    >>
    >>> Hi folks,
    >>>
    <SNIP>
    >>>
    >>> Does anyone has an idea of a more cost eficient solution?
    >>> How to get a better performance without having to invest some 
    >>> astronomicaly high amount of money?
    >>
    >> This isn't hardware related, but FreeBSD 5 is not a particularly 
    >> impressive
    >> performer.  Especially 5.0 ... 5.2.1 would be better, but if you're 
    >> shooting
    >> for performance, 4.9 will probably outperform both of them at this 
    >> stage of
    >> the game.
    >>
    >> Something to consider if the query tuning that others are helping with 
    >> doesn't
    >> solve the problem.  Follow through with that _first_ though.
    >>
    >> However, if you insist on running 5, make sure your kernel is compiled 
    >> without
    >> WITNESS ... it speeds things up noticably.
    > 
    > Thanks for the advice, I'll try recompiling the kernel. Does the freebsd 
    > version make a noticeable diference?
    
    Absolutely ... FreeBSD 5.0 is awful slow.  5.1 is better, 5.2.1 is almost
    as fast as 4.9.
    
    If you're really concerned with speed, though, you need to stick with 4.9
    for now.  I'd stay focused on the db tuning as long as there look like
    reasonable things to tune there, but FreeBSD 5.0 is NOT a good performer -
    it's still too early in the development process.  If you have a reason to
    use 5, 5.2.1 is what you want, but if you want the best performer, use 4.9.
    
    -- 
    Bill Moran
    Potential Technologies
    http://www.potentialtech.com
    
    
    

11. Re: Wich hardware suits best for large full-text indexed

    Oleg Bartunov <oleg@sai.msu.su> — 2004-04-01T10:02:07Z

    On Wed, 31 Mar 2004, Diogo Biazus wrote:
    
    > Oleg Bartunov wrote:
    >
    > >On Tue, 30 Mar 2004, Diogo Biazus wrote:
    > >
    > >
    > >
    > >>Hi folks,
    > >>
    > >>I have a database using tsearch2 to index 300 000 documents.
    > >>I've already have optimized the queries, and the database is vacuumed on
    > >>a daily basis.
    > >>The stat function tells me that my index has aprox. 460 000 unique words
    > >>(I'm using stemmer and a nice stopword list).
    > >>
    > >>
    > >
    > >460 000 unique words is a lot ! Have you seen on them ? Sometimes it's
    > >very useful to analyze what did you indexed and do you want all of them.
    > >I suggest you to use ispell dictionary and, if you index numbers
    > >(look statistics), use special dictionaries for integer and decimal numbers
    > >http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/dicts/README.intdict
    > >
    > >
    > I 'll try the ispell dictionaries and dicts for numbers too ;)
    > Could the synonym dictionary help me on this (reducing unique words)?
    
    why not ? It useful for words, which doesnt' correctly stemmed.
    
    >
    > thanks,
    >
    >
    
    	Regards,
    		Oleg
    _____________________________________________________________
    Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
    Sternberg Astronomical Institute, Moscow University (Russia)
    Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
    phone: +007(095)939-16-83, +007(095)939-23-83