> 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
