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/
[ / \
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