X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id DB2AB32A2BC for ; Fri, 1 Oct 2004 21:04:49 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 34949-02 for ; Fri, 1 Oct 2004 20:04:42 +0000 (GMT) Received: from leda.steelsun.com (dsl093-240-204.ral1.dsl.speakeasy.net [66.93.240.204]) by svr1.postgresql.org (Postfix) with ESMTP id 33B2532A2DC for ; Fri, 1 Oct 2004 21:04:42 +0100 (BST) Received: from leda.steelsun.com (localhost [127.0.0.1]) by leda.steelsun.com (8.12.11/8.12.8) with ESMTP id i91K4Zt1009201; Fri, 1 Oct 2004 16:04:35 -0400 Received: from localhost (cfowler@localhost) by leda.steelsun.com (8.12.11/8.12.11/Submit) with ESMTP id i91K4ZbE009198; Fri, 1 Oct 2004 16:04:35 -0400 X-Authentication-Warning: leda.steelsun.com: cfowler owned process doing -bs Date: Fri, 1 Oct 2004 16:04:35 -0400 (EDT) From: Christian Fowler X-X-Sender: cfowler@leda.steelsun.com To: Shane|SkinnyCorp Cc: PgSQL ADMIN Subject: Re: PLEASE GOD HELP US! In-Reply-To: <007301c4a7eb$9353eac0$3402a8c0@shanepc> Message-ID: References: <007301c4a7eb$9353eac0$3402a8c0@shanepc> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200410/27 X-Sequence-Number: 15021 > 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/ [ / \