pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: William Yu <wyu@talisys.com>
To: pgsql-admin@postgresql.org
Subject: Re: PLEASE GOD HELP US!
Date: Fri, 01 Oct 2004 14:14:04 -0700
Message-ID: <cjkh7a$1s73$1@news.hub.org> (raw)
In-Reply-To: <007301c4a7eb$9353eac0$3402a8c0@shanepc>
References: <007301c4a7eb$9353eac0$3402a8c0@shanepc>

Shane | SkinnyCorp wrote:

> I would like to also add that I've been a sysadmin for quite some time, and
> I've been designing databases for quite some time as well. I'm no idiot, I
> just can't find the bottleneck here (if one does in fact exist). So in light
> of this, please send me some suggestions I can work with.

My 2 cents. When I see a message titled "PLEASE GOD HELP US", my first 
thought is not "experienced db designer". Less bombastic statements, 
more logs (vacuum verbose, explain analyze), db stats, cpu stats, i/o 
stats -- would have (1) cut down on the obvious advice which you say you 
already know and (2) let the experts hone in on the problem. It's not 
like we can guess what your system is doing.



Here's a few things that may or may not mean anything. Just random 
guesses off the top of my head due to the lack of detailed info.

 > Nothing makes much of a difference.  I even attempted to use
 > persistant connections to the database to get around the connection
 > overhead... and THAT just seemed to eat up all the system's memory
 > while giving little or no gain in performance.

At buffers of 8192, that's just 64MB used up for shared memory + a few 
MB per connection. At the max 60 users clicking on submit at exactly the 
same time, that's about 300MB of memory compared to your total of 1.5GB. 
If something is "eating up all the system memory", it isn't Postgres and 
it certainly wouldn't be a connection pooling mechanism.


 > max_connections = 50

A bit low in db connections for a production website. Fine for 
development but I personally max my DBs out at the full 1024. No harm in 
setting it really high if you can allocate the shared memory. Also could 
be causing delays if you don't have enough connections for the number of 
users. Dunno



 > enable_seqscan = false

There are  cases where seqscan will be faster than indexscans. For 
example, your query to retrieve the latest 25 threads -- always faster 
using seqscan. If it was using indexscan, that would explain the 9 
seconds to run because the HD heads would have to jump back & forth from 
  index to table for every record. (And as an experienced sysadmin, you 
should know that HD latency is way more expensive than HD throughput.) 
On my dev system (P3/750, 1GB ram, 1 IDE HD), I get the following 
results on a 36K table:

   SELECT * FROM 16K_table ORDER BY un_indexed_fld DESC LIMIT 25
   --> 5s -- 1st run
   --> .013s -- 2nd run

Doing the selects on an indexed field -- my results start at < 100 msec 
(1st run) and drops to < 1 msec for cached I/O.



view thread (33+ messages)  latest in thread

Message-ID: <cjkh7a$1s73$1@news.hub.org>
Permalink:  ../cjkh7a$1s73$1@news.hub.org/
Also on:    postgresql.org/message-id/cjkh7a$1s73$1@news.hub.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: wyu@talisys.com
  Subject: Re: PLEASE GOD HELP US!
  In-Reply-To: <cjkh7a$1s73$1@news.hub.org>
  References: <007301c4a7eb$9353eac0$3402a8c0@shanepc> <cjkh7a$1s73$1@news.hub.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox