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< [EMAIL PROTECTED] | http://www.steelsun.com/ [ / \
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
