Re: How can I make this query faster (resend)

On Mon, May 29, 2006 at 07:35:14AM -0700, Cstdenis wrote:
> > To be honest, you're pushing things expecting a machine with only 1G to
> > serve 300 active connections. How large is the database itself?
>
> The database is 3.7G on disk. There is about 1G of actual data in it -- the
> rest is dead tuples and indices. (I vacuum regularly, but a vacuum full
> causes too much downtime to do unless I have to)

It sounds like you're not vacuuming anywhere near regularly enough if
you have that much dead space. You should at least reindex.

> > > I know hyperthreading is considered something that can slow down a
> server but with my very high concurancy (averages about 400-500 concurant
> users during peak hours) I am hoping the extra virtual CPUs wil help. Anyone
> have experance that says diferent at high concurancy?
> >
> > Best bet is to try it and see. Generally, people find HT hurts, but I
> > recently saw it double the performance of pgbench on a windows XP
> > machine, so it's possible that windows is just more clever about how to
> > use it than linux is.
>
> Anyone know if those who have found it hurts are low concurancy complex cpu
> intensive queries or high concurancy simple queries or both? I can
> understand it hurting in the former, but not the later. I'll have to give it
> a try I guess. It should at least help my very high load averages.

The issue is that HT doesn't give you anything close to having 2 CPUs,
so for all but the most trivial and limited cases it's not going to be a
win.

Incidentally, the only good results I've seen with HT are on windows.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461