Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id DB6CB6325D5 for ; Thu, 4 Feb 2010 05:53:14 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 13867-04 for ; Thu, 4 Feb 2010 09:53:04 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-qy0-f188.google.com (mail-qy0-f188.google.com [209.85.221.188]) by mail.postgresql.org (Postfix) with ESMTP id 5D9936322C6 for ; Thu, 4 Feb 2010 05:53:04 -0400 (AST) Received: by qyk26 with SMTP id 26so1074620qyk.5 for ; Thu, 04 Feb 2010 01:53:03 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :from:date:message-id:subject:to:cc:content-type; bh=i4bMg5HyFOh+l3ORnEfWkkmibqaIHdvoltCLkXvplLQ=; b=H9XQTa/9hfwV0TySz8BR7K8IRgRp4pn4njy471T3V0H9tuvehu7EYDzhXpH06nMK1+ KZ9cZo2WMMbKOUDzZUUXC2ueaRxLB1JKAbnHCGIyGUz8cHZyWNoUmrCfbHOnzUmXuCBT /3waIUI0ykaSBLEQxgv9xbPZpOfksDQlrgPzU= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc:content-type; b=UY9RRClf9d/rsONeXPCUjpC+TuFfe2f2WwxFcgI6MjNJRd1dvoK42+Zxiab/GRP+u+ 2wBYTE/qQh/6gHKSjpipZuVVxEfDS23tQoSZdHSRLu+ftmH2WUhl1JHLSGb5LQdaS9NW BTG1HEoKqP+E8pcq0CaaXES4Uy58UjQOhr95k= MIME-Version: 1.0 Received: by 10.229.128.170 with SMTP id k42mr445980qcs.48.1265277183088; Thu, 04 Feb 2010 01:53:03 -0800 (PST) In-Reply-To: <9bbcef731002040140s2dc07c07pc695b94f75382474@mail.gmail.com> References: <84b68b3d1002030710x76c31adft58c56624409011fd@mail.gmail.com> <84b68b3d1002040102o41b5c121k168accda7e31072b@mail.gmail.com> <9bbcef731002040140s2dc07c07pc695b94f75382474@mail.gmail.com> From: Amitabh Kant Date: Thu, 4 Feb 2010 15:22:43 +0530 Message-ID: <84b68b3d1002040152r6066c57du34e0ce5af504c11e@mail.gmail.com> Subject: Re: Optimizing Postgresql server and FreeBSD for heavy read and writes To: Ivan Voras Cc: pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary=00151757097e0a595a047ec34d7f X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.598 tagged_above=-10 required=5 tests=BAYES_00=-2.599, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201002/84 X-Sequence-Number: 37505 --00151757097e0a595a047ec34d7f Content-Type: text/plain; charset=ISO-8859-1 On Thu, Feb 4, 2010 at 3:10 PM, Ivan Voras wrote: > On 4 February 2010 10:02, Amitabh Kant wrote: > > On Wed, Feb 3, 2010 at 10:05 PM, Ivan Voras wrote: > >> > >> On 02/03/10 16:10, Amitabh Kant wrote: > >>> > >>> Hello > >>> > >>> I have a server dedicated for Postgres with the following specs: > >>> > >>> RAM 16GB, 146GB SAS (15K) x 4 - RAID 10 with BBU, Dual Xeon E5345 @ > >>> 2.33GHz > >>> OS: FreeBSD 8.0 > >> > >> If you really do have "heavy read and write" load on the server, nothing > >> will save you from the bottleneck of having only 4 drives in the system > (or > >> more accurately: adding more memory will help reads but nothing helps > writes > >> except more drivers or faster (SSD) drives). If you can, add another 2 > >> drives in RAID 1 and move+symlink the pg_xlog directory to the new > array. > > > > Can't do anything about this server now, but would surely keep in mind > > before upgrading other servers. Would you recommend the same speed > > drives(15K SAS) for RAID 1, or would a slower drive also work here (10K > SAS > > or even SATA II)? > > Again, it depends on your load. It would probably be best if they are > approximately the same speed; the location of pg_xlog will dictate > your write (UPDATE / INSERT / CREATE) speed. > > Writes to your database go like this: the data is first written to the > WAL (this is the pg_xlog directory - the transaction log), then it is > read and written to the "main" database. If the main database is very > busy reading, transfers from WAL to the database will be slower. > Thanks Ivan. I have to go in for upgrade of couple of more servers. I will be going in for RAID 1 (OS + pg_xlog ) and RAID 10 (Pgsql data), all of them of same speed. With regards Amitabh Kant --00151757097e0a595a047ec34d7f Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable

On Thu, Feb 4, 2010 at 3:10 PM, Ivan Vor= as <ivoras@freeb= sd.org> wrote:
On 4 February 2010 10:02, Amitabh Kant <amitabhkant@gmail.com> wrote:
> On Wed, Feb 3, 2010 at 10:05 PM, Ivan Voras <ivoras@freebsd.org> wrote:
>>
>> On 02/03/10 16:10, Amitabh Kant wrote:
>>>
>>> Hello
>>>
>>> I have a server dedicated for Postgres with the following spec= s:
>>>
>>> RAM 16GB, 146GB SAS (15K) x 4 - =A0RAID 10 with BBU, Dual Xeon= =A0E5345 =A0@
>>> 2.33GHz
>>> OS: FreeBSD 8.0
>>
>> If you really do have "heavy read and write" load on the= server, nothing
>> will save you from the bottleneck of having only 4 drives in the s= ystem (or
>> more accurately: adding more memory will help reads but nothing he= lps writes
>> except more drivers or faster (SSD) drives). If you can, add anoth= er 2
>> drives in RAID 1 and move+symlink the pg_xlog directory to the new= array.
>
> Can't do anything about this server now, but would surely keep in = mind
> before upgrading other servers. Would you recommend the same speed
> drives(15K SAS) for RAID 1, or would a slower drive also work here (10= K SAS
> or even SATA II)?

Again, it depends on your load. It would probably be best if they are=
approximately the same speed; the location of pg_xlog will dictate
your write (UPDATE / INSERT / CREATE) speed.

Writes to your database go like this: the data is first written to the
WAL (this is the pg_xlog directory - the transaction log), then it is
read and written to the "main" database. If the main database is = very
busy reading, transfers from WAL to the database will be slower.

Thanks Ivan. I have to go in for upgrade of couple o= f more servers. I will=A0 be going in for RAID 1 (OS + pg_xlog ) and RAID 1= 0 (Pgsql data), all of them of same speed.

With regards

Amita= bh Kant
--00151757097e0a595a047ec34d7f--