pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Christian Fowler <spider@steelsun.com>
To: Shane|SkinnyCorp <shanew@skinnycorp.com>
Cc: PgSQL ADMIN <pgsql-admin@postgresql.org>
Subject: Re: PLEASE GOD HELP US!
Date: Fri, 1 Oct 2004 16:04:35 -0400 (EDT)
Message-ID: <Pine.LNX.4.61.0410011546590.8260@leda.steelsun.com> (raw)
In-Reply-To: <007301c4a7eb$9353eac0$3402a8c0@shanepc>
References: <007301c4a7eb$9353eac0$3402a8c0@shanepc>



> 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.

Here are three concise suggestions:

1. You say your issue is only under load, then I can probably guarantee 
your issue is available connections:

max_connections = 50

That number is way too small for a site with decent traffic. make it like 
500. how mindful are you about opening connections per page view?

2. You have a query taking 9 seconds. run that query by hand on the shell 
to find out why. Rework the query, add or recreate indices as necessary.
Using conditionals in ORDER BY is a bad idea. Do it in the WHERE clause 
per my previous email.

3. Leave your attitude at the door, or just leave. Making comments like:

> I take vast offense to the suggestion that my query / database design is 
> at fault here.... I must admit that I expected much more from this list 
> than I've recieved so far.

make it clear that you have a lot more room for growth as a developer. If 
you stop biting the hands that help you, you will learn a lot more.

> One last thing... even with ALL of the data intact (and yes, we DID do
> testing... we just didn't have enough ppl to test the production server
> load)

Another mistake showing your lack of experience. Use apache bench ( ab 
command ) to simulate load.

>
> The only information I can give at the moment about the number of queries
> per second is this: there is an average of 60 users online at any given
> time, and the average number of queries per page load is 12, and they are
> refreshing and clicking etc quite a bit... so I'd say about 120 queries per
> second or so... (VERY rough estimate)....

And you only have 50 max_connections for postgres? What are you thinking? 
Of course every apache process is waiting for a connection. Also, do you 
even have enough apache workers?

Your attitude sucks. Your problems are right under your nose, and 
you are too cocky to learn your tools. I imagine you are getting hammered 
by your co-workers to get things fixed. I will tell you empathy will 
always get you much farther than animosity.


[ \ /
[ >X<   spider@steelsun.com   |   http://www.steelsun.com/
[ / \



view thread (33+ messages)  latest in thread

Message-ID: <Pine.LNX.4.61.0410011546590.8260@leda.steelsun.com>
Permalink:  ../Pine.LNX.4.61.0410011546590.8260@leda.steelsun.com/
Also on:    postgresql.org/message-id/Pine.LNX.4.61.0410011546590.8260@leda.steelsun.com

 · 

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: spider@steelsun.com, shanew@skinnycorp.com
  Subject: Re: PLEASE GOD HELP US!
  In-Reply-To: <Pine.LNX.4.61.0410011546590.8260@leda.steelsun.com>
  References: <007301c4a7eb$9353eac0$3402a8c0@shanepc> <Pine.LNX.4.61.0410011546590.8260@leda.steelsun.com>

* 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