From owner-freebsd-isp Fri Oct 12 20:14: 8 2001 Delivered-To: freebsd-isp@freebsd.org Received: from mail.tgd.net (rand.tgd.net [64.81.67.117]) by hub.freebsd.org (Postfix) with SMTP id 0125337B40D for ; Fri, 12 Oct 2001 20:14:04 -0700 (PDT) Received: (qmail 789 invoked by uid 1001); 13 Oct 2001 03:13:58 -0000 Date: Fri, 12 Oct 2001 20:13:58 -0700 From: Sean Chittenden To: Scott Gerhardt Cc: Marcel Prisi , freebsd-isp@FreeBSD.ORG Subject: Re: PostgreSQL & shared memory Message-ID: <20011012201358.C99867@rand.tgd.net> References: <20011012130959.C97952@rand.tgd.net> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: ; from "scott@gerhardt-it.com" on Fri, Oct 12, 2001 at = 03:02:05PM X-PGP-Key: 0x1EDDFAAD X-PGP-Fingerprint: C665 A17F 9A56 286C 5CFB 1DEA 9F4F 5CEF 1EDD FAAD X-Web-Homepage: http://sean.chittenden.org/ Sender: owner-freebsd-isp@FreeBSD.ORG Precedence: bulk List-ID: List-Archive: (Web Archive) List-Help: (List Instructions) List-Subscribe: List-Unsubscribe: X-Loop: FreeBSD.org > > Whew, I was going to bing you with a clue bat if you didn't suggest > > getting more RAM: 380MB of RAM isn't much. All this aside, what you > > want to do is setup a reverse proxy server. You have these HUGE Apache > > and postgresql processes sitting around in RAM spending most of their > > time doing what? Writing their data out over the wire. The best thing > > you can do to speed up your webapp is to increase your kernel output > > buffers and/or use a reverse proxy (mod_backhand, mod_proxy, or even > > squid). Would you like to have a large 12-20MB process > > sending data out > > over the wire, or a small 400KB process that's mostly shared > > memory? -sc > > You didn't give details of how your system is overloaded i.e. disk IO, > memory, slow response times etc. > Here are a few suggestions I can think of: > > 1.) Yes, add much more RAM, as Sean mentioned. RAM is cheap so max it out > if you can, that will make a huge difference. > 2.) Make sure that your PostgreSQL tables are indexed and your queries are > optimized. Poor querys and non indexed tables design can make things crawl. > 3.) See about tweaking Apache's logging to minimize open file handles and > disk IO. > 4.) Enable soft updates if you are comforable with soft updates. Ack! Okay, real quick: this isn't a PostgreSQL problem. It's a RAM management problem. Follow this through and hopefully everyone will realize that making PostgreSQL go faster isn't going to buy anyone as much performance/ram savings as a reverse proxy server. A) Apache + mod_perl + mod_php4 = ~12-25MB processes B) Apache + mod_backhand = ~500KB (300KB shared!) Now, here's the demeaningly funny point, "Now class, which web setup would you rather have sending data out to a butt-ass-slow client that's connected over a 28.8Kbps modem? Oh! And don't forget about images and that 100KB front page." Hands down, any day of the week, everyone should've gone for setup B. The time that it takes server setup A to process a request is... conservatively, .1, maybe .2 seconds (with persistent database connections enabled on the mod_perl/php processes). Now, how much time do you think a webserver spends sending data to a client? Close to 2, maybe 3 seconds if it's a small request. I'm sure almost everyone here has images, and probably bloated java script files and an equally huge front page that was designed with Front Page or Dream Weaver. Trick question: which of the two server setups mentioned above can send data to a client? Tough one, huh? BOTH! So explain to me again why you want server setup A sending data to a client? In the time that it takes one process from server A to send data to a client, you could've handled (does finger math) 20? maybe 30 extra requests if the sending of the data was handled by server setup B. When you say this box is loaded, are we talking .5M hits? That's not too much for a box of this caliper if the architecture is done right. If you're pushing 5-6M over a single system.. then you're getting a tad gutsy and I'd think about getting another box, but I doubt that's the case and you'd probably be having disk IO issues before you had CPU or RAM issues (logging and DB at the same time don't play friendly). * Soft-updates * Some kind of a network buffer (mod_backhand/mod_proxy) * Persistent database connections (Apache::DBI) * Turn off fsync for PostgreSQL At any rate, if anyone has any questions about either HOW to setup the above system, or need further explanations as to what I just suggested, write back to the list and I'll be less snappy (should have a Guinness in hand/system by that time and I'll be less grumpy about being at work this late schooling other people on this exact concept). It's easy to quickly to point fingers at PostgreSQL because it takes up a noticeable amount of RAM. A database that uses RAM is a good thing, that's caching and will result in many speed ups when it comes to data retrieval. A heavy webserver process that's >10MB that's just sending data out over the wire? That's bad bad bad bad bad. 4-5 PostgreSQL processes + 4-5 heavy Apache processes + 100-150 itty bitty light weight Apache network buffers (mod_backhand) processes = good use of available resources. -sc -- Sean Chittenden To Unsubscribe: send mail to majordomo@FreeBSD.org with "unsubscribe freebsd-isp" in the body of the message