X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 97590D1B172 for ; Fri, 30 Jul 2004 23:17:50 -0300 (ADT) 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 16795-09 for ; Sat, 31 Jul 2004 02:17:38 +0000 (GMT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by svr1.postgresql.org (Postfix) with ESMTP id DE4DFD1B1A1 for ; Fri, 30 Jul 2004 23:17:33 -0300 (ADT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.12.9/8.12.9) with ESMTP id i6V2HXqd053263 for ; Sat, 31 Jul 2004 02:17:33 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.12.9/8.12.9/Submit) id i6V1oN7r050520 for pgsql-performance@postgresql.org; Sat, 31 Jul 2004 01:50:23 GMT From: Christopher Browne X-Newsgroups: comp.databases.postgresql.performance Subject: Re: my boss want to migrate to ORACLE Date: Fri, 30 Jul 2004 21:22:25 -0400 Organization: cbbrowne Computing Inc Lines: 68 Message-ID: References: <1091156393.39517b44365bc@mail.coretech.co.nz> <001f01c4763c$fcef40f0$4e00020a@develavoie> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Complaints-To: usenet@news.hub.org X-message-flag: Outlook is rather hackable, isn't it? X-Home-Page: http://www.cbbrowne.com/info/ X-Affero: http://svcs.affero.net/rm.php?r=cbbrowne User-Agent: Gnus/5.1006 (Gnus v5.10.6) XEmacs/21.4 (Security Through Obscurity, linux) Cancel-Lock: sha1:phQBNJfjLYiYF3arEAm8G36pLNU= To: pgsql-performance@postgresql.org 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: 200407/244 X-Sequence-Number: 7635 After a long battle with technology, stephane.tessier@abovesecurity.com ("Stephane Tessier"), an earthling, wrote: > I think with your help guys I'll do it! > > I'm working on it! > > I'll work on theses issues: > > we have space for more ram(we use 2 gigs on possibility of 3 gigs) That _may_ help; not completely clear. > iowait is very high 98% --> look like postgresql wait for io access In that case, if you haven't got a RAID controller with battery backed cache, then that should buy you a BIG boost in performance. Maybe $1500 USD; that could be money FABULOUSLY well spent. > raid5 -->raid0 if i'm right raid5 use 4 writes(parity,data, etc) for each > write on disk I try to avoid talking about RAID levels, and leave them to others :-). Sticking WAL on a solid state disk would be WAY COOL; you almost certainly are hitting WAL really hard, which eventually cooks disks. What is unfortunate is that there doesn't seem to be a "low end" 1GB SSD; I'd hope that would cost $5K, and that might give a bigger boost than the battery-backed RAID controller with lotsa cache. > use more transactions (we have a lot of insert/update without > transaction). That'll help unless you get the RAID controller, in which case WAL updates become much cheaper. > cpu look like not running very hard Not surprising. > *php is not running on the same machine > *redhat enterprise 3.0 ES > *the version of postgresql is 7.3.4(using RHDB from redhat) All makes sense. It would be attractive to move to 7.4.2 or 7.4.3; they're really quite a lot faster. If there's no option to migrate quickly, then 7.5 has interesting cache management changes that ought to help even more, particularly with your vacuuming issues :-). But it's probably better to get two incremental changes; migrating to 7.4, and being able to tell the boss "That improved performance by x%", and then doing _another_ upgrade that _also_ improves things should provide a pretty compelling argument in favour of keeping up the good work with PostgreSQL. > *pg_autovacuum running at 12 and 24 hour each day That really doesn't make sense. The point of pg_autovacuum is for it to run 24 hours a day. If you kick it off twice, once at 11:59, then stop it, and then once at 23:59, and then stop it, it shouldn't actually do any work. Or have you set it up with a 'sleep period' of ~12 hours? -- let name="cbbrowne" and tld="acm.org" in name ^ "@" ^ tld;; http://www.ntlug.org/~cbbrowne/sgml.html The *Worst* Things to Say to a Police Officer: Hey, is that a 9 mm? That's nothing compared to this .44 magnum.