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 5DB1053B3D for ; Thu, 14 Apr 2005 15:45:10 -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 52673-05 for ; Thu, 14 Apr 2005 18:45:05 +0000 (GMT) Received: from mail.tecarta.com (66.238.115.135.ptr.us.xo.net [66.238.115.135]) by svr1.postgresql.org (Postfix) with ESMTP id 843FC53B08 for ; Thu, 14 Apr 2005 15:45:04 -0300 (ADT) Received: from mail pickup service by mail.tecarta.com with Microsoft SMTPSVC; Thu, 14 Apr 2005 11:48:03 -0700 Received: from mail.tecarta.com ([192.168.160.2]) by mail.tecarta.com with Microsoft SMTPSVC(6.0.3790.0); Thu, 14 Apr 2005 11:48:02 -0700 Received: from barracuda.tecarta.com ([192.168.160.200]) by mail.tecarta.com (SAVSMTP 3.1.0.29) with SMTP id M2005041411480201691 for ; Thu, 14 Apr 2005 11:48:02 -0700 X-ASG-Debug-ID: 1113504300-17788-9-0 X-Barracuda-URL: http://192.168.160.200:8000/cgi-bin/mark.cgi Received: from mail1 (mail1.hq.corp [192.168.160.5]) by barracuda.tecarta.com (Spam Firewall) with SMTP id 10B07200C9DB for ; Thu, 14 Apr 2005 11:45:00 -0700 (PDT) Received: from [192.168.0.13] ([63.206.203.145]) by mail.tecarta.com with Microsoft SMTPSVC(6.0.3790.0); Thu, 14 Apr 2005 11:47:56 -0700 Message-ID: <425EBA0C.6030804@sfnet.cc> Date: Thu, 14 Apr 2005 11:44:28 -0700 From: Steve Poe User-Agent: Mozilla Thunderbird 1.0 (X11/20041228) X-Accept-Language: en-us, en MIME-Version: 1.0 To: "Mohan, Ross" Cc: pgsql-performance@postgresql.org X-ASG-Orig-Subj: Re: [PERFORM] How to improve db performance with $7K? Subject: Re: How to improve db performance with $7K? References: In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-OriginalArrivalTime: 14 Apr 2005 18:47:56.0146 (UTC) FILETIME=[78CF1D20:01C54122] X-Virus-Scanned: by Barracuda Spam Firewall at tecarta.com X-Barracuda-Spam-Score: 0.00 X-Barracuda-Spam-Status: No, SCORE=0.00 using global scores of TAG_LEVEL=4.0 QUARANTINE_LEVEL=1000.0 KILL_LEVEL=7.0 tests=BAYES_50 X-Barracuda-Spam-Report: Code version 3.02, rules version 3.0.289 Rule breakdown below pts rule name description ---- ---------------------- -------------------------------------------------- 0.00 BAYES_50 BODY: Bayesian spam probability is 40 to 60% [score: 0.5000] X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.189 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200504/315 X-Sequence-Number: 11712 If SATA drives don't have the ability to replace SCSI for a multi-user Postgres apps, but you needed to save on cost (ALWAYS an issue), could/would you implement SATA for your logs (pg_xlog) and keep the rest on SCSI? Steve Poe Mohan, Ross wrote: >I've been doing some reading up on this, trying to keep up here, >and have found out that (experts, just yawn and cover your ears) > >1) some SATA drives (just type II, I think?) have a "Phase Zero" > implementation of Tagged Command Queueing (the special sauce > for SCSI). >2) This SATA "TCQ" is called NCQ and I believe it basically > allows the disk software itself to do the reordering > (this is called "simple" in TCQ terminology) It does not > yet allow the TCQ "head of queue" command, allowing the > current tagged request to go to head of queue, which is > a simple way of manifesting a "high priority" request. > >3) SATA drives are not yet multi-initiator? > >Largely b/c of 2 and 3, multi-initiator SCSI RAID'ed drives >are likely to whomp SATA II drives for a while yet (read: a >year or two) in multiuser PostGres applications. > > > >-----Original Message----- >From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Greg Stark >Sent: Thursday, April 14, 2005 2:04 PM >To: Kevin Brown >Cc: pgsql-performance@postgresql.org >Subject: Re: [PERFORM] How to improve db performance with $7K? > > >Kevin Brown writes: > > > >>Greg Stark wrote: >> >> >> >> >>>I think you're being misled by analyzing the write case. >>> >>>Consider the read case. When a user process requests a block and >>>that read makes its way down to the driver level, the driver can't >>>just put it aside and wait until it's convenient. It has to go ahead >>>and issue the read right away. >>> >>> >>Well, strictly speaking it doesn't *have* to. It could delay for a >>couple of milliseconds to see if other requests come in, and then >>issue the read if none do. If there are already other requests being >>fulfilled, then it'll schedule the request in question just like the >>rest. >> >> > >But then the cure is worse than the disease. You're basically describing exactly what does happen anyways, only you're delaying more requests than necessary. That intervening time isn't really idle, it's filled with all the requests that were delayed during the previous large seek... > > > >>Once the first request has been fulfilled, the driver can now schedule >>the rest of the queued-up requests in disk-layout order. >> >>I really don't see how this is any different between a system that has >>tagged queueing to the disks and one that doesn't. The only >>difference is where the queueing happens. >> >> > >And *when* it happens. Instead of being able to issue requests while a large seek is happening and having some of them satisfied they have to wait until that seek is finished and get acted on during the next large seek. > >If my theory is correct then I would expect bandwidth to be essentially equivalent but the latency on SATA drives to be increased by about 50% of the average seek time. Ie, while a busy SCSI drive can satisfy most requests in about 10ms a busy SATA drive would satisfy most requests in 15ms. (add to that that 10k RPM and 15kRPM SCSI drives have even lower seek times and no such IDE/SATA drives exist...) > >In reality higher latency feeds into a system feedback loop causing your application to run slower causing bandwidth demands to be lower as well. It's often hard to distinguish root causes from symptoms when optimizing complex systems. > > >