Re: PLEASE GOD HELP US! - Mailing list pgsql-admin

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