Date: Mon, 5 Jun 2006 09:35:29 -0500
From: "Jim C. Nasby" <jnasby@pervasive.com>
To: Cstdenis <cstdenis@voicio.com>
Cc: Nis Jorgensen <nis@superlativ.dk>, pgsql-performance@postgresql.org
Subject: Re: How can I make this query faster (resend)
Message-ID: <20060605143529.GP53487@pervasive.com>
References: <03e001c67b99$8321f010$6401a8c0@chris>
	<447177B2.2020109@superlativ.dk>
	<013801c6832d$1cf73d90$6401a8c0@chris>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <013801c6832d$1cf73d90$6401a8c0@chris>
User-Agent: Mutt/1.5.11

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@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461