From owner-freebsd-arch Sat Oct 12 7:22:28 2002 Delivered-To: freebsd-arch@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id B6C5E37B401 for ; Sat, 12 Oct 2002 07:22:24 -0700 (PDT) Received: from trantor.utsl.org (cvg-65-27-234-246.cinci.rr.com [65.27.234.246]) by mx1.FreeBSD.org (Postfix) with ESMTP id B9EBE43ECD for ; Sat, 12 Oct 2002 07:22:23 -0700 (PDT) (envelope-from utsl@quic.net) Received: from hotrod.utsl.org ([10.10.57.3] helo=quic.net) by trantor.utsl.org with esmtp (Exim 3.35 #1 (Debian)) id 180N9X-0001ln-00; Sat, 12 Oct 2002 10:22:03 -0400 Message-ID: <3DA82FBA.9070607@quic.net> Date: Sat, 12 Oct 2002 10:20:42 -0400 From: Nathan Hawkins User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.0.0) Gecko/20020615 Debian/1.0.0-3 MIME-Version: 1.0 To: Wes Peters Cc: arch@FreeBSD.ORG Subject: Re: Database indexes and ram (was Re: using mem above 4Gb was:swapon some regular file) References: <1034105993.913.1.camel@vbook.express.ru> <200210082015.g98KFFrq084625@apollo.backplane.com> <1034109053.913.7.camel@vbook.express.ru> <200210082051.g98KpjU1084793@apollo.backplane.com> <3DA4C271.37AACAA3@softweyr.com> <3DA4C632.325F2EBE@mindspring.com> <3DA7C997.95F3C4FF@softweyr.com> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit Sender: owner-freebsd-arch@FreeBSD.ORG Precedence: bulk List-ID: List-Archive: (Web Archive) List-Help: (List Instructions) List-Subscribe: List-Unsubscribe: X-Loop: FreeBSD.ORG Wes Peters wrote: > Some? You've seen a production database that was normalized at ALL? > Gee, that'd be... nice? astonishing? like seeing the pope tour temple > square? DBA stands for Data Base A..... Sigh. Apparently some actually believe that denormalizing a database is an optimization. I should become a DBA. They get paid more than I do, and most seem to know less. :( > The key to accelerating database access rarely has much to do with I/O > speed. How many Oracle servers do you know that can stuff a Gigabit > channel full, even doing straight selects? Memory usage is VERY important > and DBAs are not famous for optimizing queries to make effecient use of > the processor cache. Or anything else, for that matter. Hmm. I have seen I/O speed become a problem. But that's generally when the DBA did a poor job of placing data volumes. Putting logs on the same disk with the filesystem where the online backups get dumped was spectacular. User imported some data while a backup was going, and that particular disk saw more activity than the other 30 or so combined... (Striping would have helped, but then, separating heavily used regions of disk onto different spindles is what striping is _for_.) Memory usage is critical, and so is correct processor use. Oracle and Sybase require correct tuning on SMP machines, or they waste CPU. Lock tuning, IIRC can be more critical to performance than memory, at least with Sybase. (i.e. You can add more memory and CPUs and performance gets worse.) Informix either worked vastly better, or we had a better DBA... :) The worst performance problem I ever saw was one where the DBA insisted on creating a shared memory segment that was larger than physical memory in the machine. Keeping half your database's cache in swap is a _bad_ idea. ---Nathan To Unsubscribe: send mail to majordomo@FreeBSD.org with "unsubscribe freebsd-arch" in the body of the message