Re: PLEASE GOD HELP US!

I have a question:

You say this happens under load and that it slows down.  I would think
that a problem with max connections would cause other problems but not
the slowness.  Someone can correct me though.

What sort of performance tuning have you done?  I suspect we can help
you narrow down the problems.  Historically, the default settings are
designed around systems with limited memory so that it will *start* on
anything.  To get decent performance under load you have to change some
settings.

Also how much RAM do you have?  Which version of PostgreSQL are you running?

Best Wishes,
Chris Travers
Metatron Technology Consulting

Christian Fowler 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.
>
>
> 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/
> [ / \
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>      subscribe-nomail command to majordomo@postgresql.org so that your
>      message can get through to the mailing list cleanly
>
>