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

From: "Jim C(dot) Nasby" <jnasby(at)pervasive(dot)com>
To: Cstdenis <cstdenis(at)voicio(dot)com>
Cc: Nis Jorgensen <nis(at)superlativ(dot)dk>, pgsql-performance(at)postgresql(dot)org
Subject: Re: How can I make this query faster (resend)
Date: 2006-06-05 14:35:29
Message-ID: 20060605143529.GP53487@pervasive.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Jim C. Nasby 2006-06-05 14:43:49 Re: vacuuming problems continued
Previous Message Zoltan Boszormenyi 2006-06-04 22:32:38 Re: psql -A (unaligned format) eats too much memory