Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 0E25EB5DBD6 for ; Wed, 17 Aug 2011 13:39:35 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 71626-02 for ; Wed, 17 Aug 2011 16:39:28 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mxout-07.mxes.net (mxout-07.mxes.net [216.86.168.182]) by mail.postgresql.org (Postfix) with ESMTP id 8A21DB5D80A for ; Wed, 17 Aug 2011 13:39:28 -0300 (ADT) Received: from [192.168.1.108] (unknown [173.165.13.38]) by smtp.mxes.net (Postfix) with ESMTPA id B95BE22E257; Wed, 17 Aug 2011 12:39:21 -0400 (EDT) Subject: Re: Tuning Tips for a new Server Mime-Version: 1.0 (Apple Message framework v1082) Content-Type: text/plain; charset=us-ascii From: Ogden X-Priority: 3 (Normal) In-Reply-To: Date: Wed, 17 Aug 2011 11:39:21 -0500 Cc: pgsql-performance@postgresql.org Content-Transfer-Encoding: quoted-printable Message-Id: References: <959BD2FA-0312-458B-BD5F-ED358D2C47ED@darkstatic.com> To: "Tomas Vondra" X-Mailer: Apple Mail (2.1082) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9 X-Spam-Level: X-Archive-Number: 201108/179 X-Sequence-Number: 44626 On Aug 17, 2011, at 9:44 AM, Tomas Vondra wrote: > On 17 Srpen 2011, 3:35, Ogden wrote: >> Hope all is well. I have received tremendous help from this list = prior and >> therefore wanted some more advice. >>=20 >> I bought some new servers and instead of RAID 5 (which I think = greatly >> hindered our writing performance), I configured 6 SCSI 15K drives = with >> RAID 10. This is dedicated to /var/lib/pgsql. The main OS has 2 SCSI = 15K >> drives on a different virtual disk and also Raid 10, a total of = 146Gb. I >> was thinking of putting Postgres' xlog directory on the OS virtual = drive. >> Does this even make sense to do? >=20 > Yes, but it greatly depends on the amount of WAL and your workload. If = you > need to write a lot of WAL data (e.g. during bulk loading), this may > significantly improve performance. It may also help when you have a > write-heavy workload (a lot of clients updating records, background = writer > etc.) as that usually means a lot of seeking (while WAL is written > sequentially). The database is about 200Gb so using /usr/local/pgsql/pg_xlog on a = virtual disk with 100Gb should not be a problem with the disk space = should it? >> The system memory is 64GB and the CPUs are dual Intel E5645 chips = (they >> are 6-core each). >>=20 >> It is a dedicated PostgreSQL box and needs to support heavy read and >> moderately heavy writes. >=20 > What is the size of the database? So those are the new servers? What's = the > difference compared to the old ones? What is the RAID controller, how = much > write cache is there? >=20 I am sorry I overlooked specifying this. The database is about 200Gb and = yes these are new servers which bring more power (RAM, CPU) over the = last one. The RAID Controller is a Perc H700 and there is 512Mb write = cache. The servers are Dells.=20 >> Currently, I have this for the current system which as 16Gb Ram: >>=20 >> max_connections =3D 350 >>=20 >> work_mem =3D 32MB >> maintenance_work_mem =3D 512MB >> wal_buffers =3D 640kB >=20 > Are you really using 350 connections? Something like "#cpus + #drives" = is > usually recommended as a sane number, unless the connections are idle = most > of the time. And even in that case a pooling is recommended usually. >=20 > Anyway if this worked fine for your workload, I don't think you need = to > change those settings. I'd probably bump up the wal_buffers to 16MB - = it > might help a bit, definitely won't hurt and it's so little memory it's = not > worth the effort I guess. So just increasing the wal_buffers is okay? I thought there would be = more as the memory in the system is now 4 times as much. Perhaps = shared_buffers too (down below).=20 >>=20 >> # This is what I was helped with before and made reporting queries = blaze >> by >> seq_page_cost =3D 1.0 >> random_page_cost =3D 3.0 >> cpu_tuple_cost =3D 0.5 >> effective_cache_size =3D 8192MB >=20 > Are you sure the cpu_tuple_cost =3D 0.5 is correct? That seems a bit = crazy > to me, as it says reading a page sequentially is just twice as = expensive > as processing it. This value should be abou 100x lower or something = like > that. These settings are for the old server, keep in mind. It's a 16GB machine = (the new one is 64Gb). The value for cpu_tuple_cost should be 0.005? How = are the other ones? > What are the checkpoint settings (segments, completion target). What = about > shared buffers? #checkpoint_segments =3D 3 # in logfile segments, min 1, = 16MB each #checkpoint_timeout =3D 5min # range 30s-1h checkpoint_completion_target =3D 0.9 # checkpoint target duration, = 0.0 - 1.0 - was 0.5 #checkpoint_warning =3D 30s # 0 disables And shared_buffers =3D 4096MB =20 Thank you very much Ogden