Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 9AE26632F24 for ; Mon, 14 Jun 2010 21:41:08 -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 93771-07 for ; Tue, 15 Jun 2010 00:40:59 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-ww0-f46.google.com (mail-ww0-f46.google.com [74.125.82.46]) by mail.postgresql.org (Postfix) with ESMTP id 8FB14632C95 for ; Mon, 14 Jun 2010 21:41:00 -0300 (ADT) Received: by wwb18 with SMTP id 18so4361258wwb.19 for ; Mon, 14 Jun 2010 17:40:58 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from :user-agent:mime-version:to:cc:subject:references:in-reply-to :content-type:content-transfer-encoding; bh=9iLcJtsXCD47RjkyKj2VrKJXXePmkh74QTXSrxPqSlk=; b=fbBfGef2rge3rz+ikhMxoChgAZI0EwS9/tEuzWgpdKYVascvFNzR9vNsso+Qxd+ELD TMXj5zIQaY4dHxtZVzIQrwr9l5UtK4U+lHzeVB1iX6QKvnnP6vTwwY9N/pYJRsHOAoBs c53esMcPXeB5UmcfzG5J3yY97G5lge29aveRI= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:user-agent:mime-version:to:cc:subject :references:in-reply-to:content-type:content-transfer-encoding; b=gG+AvO7Pz0ihWPm99chZnlsOW7RwPEelCiYRmGk5a8BxA6X34Y24rjo4cER1FVuCIG FoII7oPA/Gj54GdQ+s5yMreLwoz+ipNSiNOgU76RuQFcgsjR5j/q5gDFnNk/OO/1K3Cf tdmLAgEoOriLfnCgpyHmcrvjezg9xG5Vfp8BE= Received: by 10.227.128.9 with SMTP id i9mr6353399wbs.203.1276562458291; Mon, 14 Jun 2010 17:40:58 -0700 (PDT) Received: from [192.168.1.101] (93-97-110-189.zone5.bethere.co.uk [93.97.110.189]) by mx.google.com with ESMTPS id p17sm33616790wbe.20.2010.06.14.17.40.57 (version=TLSv1/SSLv3 cipher=RC4-MD5); Mon, 14 Jun 2010 17:40:57 -0700 (PDT) Message-ID: <4C16CC1D.5070108@gmail.com> Date: Tue, 15 Jun 2010 01:41:01 +0100 From: Tom Wilcox User-Agent: Thunderbird 2.0.0.24 (Windows/20100228) MIME-Version: 1.0 To: Dave Crooke CC: Bob Lunney , pgsql-performance@postgresql.org Subject: Re: requested shared memory size overflows size_t References: <584872.74177.qm@web39702.mail.mud.yahoo.com> <4C167A8D.1060002@gmail.com> In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.8 tagged_above=-10 required=5 tests=BAYES_50=0.8, RCVD_IN_DNSWL_NONE=-0.0001 X-Spam-Level: X-Archive-Number: 201006/257 X-Sequence-Number: 39113 Hi Dave, I am definitely able to switch OS if it will get the most out of Postgres. So it is definitely a case of choosing the OS on the needs if the app providing it is well justified. Currently, we are running Ubuntu Server 64-bit in a VirtualBox VM. Cheers, Tom Dave Crooke wrote: > Tom > > I always prefer to choose apps based on business needs, then the OS > based on the needs for the app. > > Cynically, I often feel that the best answer to "we have a policy that > says we're only allowed to use operating system x" is to ignore the > policy .... the kind of people ignorant enough to be that blinkered > are usually not tech-savvy enough to notice when it gets flouted :-) > > More seriously, is the policy "Windows only on the metal" or could you > run e.g. VMware ESX server? I/O is the area that takes the biggest hit > in virtualization, and ESX server has far less overhead loss than > either Hyper-V (which I presume you are using) or VMWare Workstation > for NT (kernels). > > If it's a Windows-only policy, then perhaps you can run those traps in > reverse, and switch to a Windows database, i.e. Microsoft SQL Server. > > Cheers > Dave > > On Mon, Jun 14, 2010 at 1:53 PM, Tom Wilcox > wrote: > > > Hi Bob, > > Thanks a lot. Here's my best attempt to answer your questions: > > The VM is setup with a virtual disk image dynamically expanding to > fill an allocation of 300GB on a fast, local hard drive (avg read > speed = 778MB/s ). > WAL files can have their own disk, but how significantly would > this affect our performance? > The filesystem of the host OS is NTFS (Windows Server 2008 OS 64), > the guest filesystem is Ext2 (Ubuntu 64). > The workload is OLAP (lots of large, complex queries on large > tables run in sequence). > > In addition, I have reconfigured my server to use more memory. > Here's a detailed blow by blow of how I reconfigured my system to > get better performance (for anyone who might be interested)... > > In order to increase the shared memory on Ubuntu I edited the > System V IPC values using sysctl: > > sysctl -w kernel.shmmax=16106127360* > *sysctl -w kernel.shmall=2097152 > > I had some fun with permissions as I somehow managed to change the > owner of the postgresql.conf to root where it needed to be > postgres, resulting in failure to start the service.. (Fixed with > chown postgres:postgres ./data/postgresql.conf and chmod u=rwx > ./data -R). > > I changed the following params in my configuration file.. > > default_statistics_target=10000 > maintenance_work_mem=512MB > work_mem=512MB > shared_buffers=512MB > wal_buffers=128MB > > With this config, the following command took 6,400,000ms: > > EXPLAIN ANALYZE UPDATE nlpg.match_data SET org = org; > > With plan: > "Seq Scan on match_data (cost=0.00..1392900.78 rows=32237278 > width=232) (actual time=0.379..464270.682 rows=27777961 loops=1)" > "Total runtime: 6398238.890 ms" > > With these changes to the previous config, the same command took > 5,610,000ms: > > maintenance_work_mem=4GB > work_mem=4GB > shared_buffers=4GB > effective_cache_size=4GB > wal_buffers=1GB > > Resulting plan: > > "Seq Scan on match_data (cost=0.00..2340147.72 rows=30888572 > width=232) (actual time=0.094..452793.430 rows=27777961 loops=1)" > "Total runtime: 5614140.786 ms" > > Then I performed these changes to the postgresql.conf file: > > max_connections=3 > effective_cache_size=15GB > maintenance_work_mem=5GB > shared_buffers=7000MB > work_mem=5GB > > And ran this query (for a quick look - can't afford the time for > the previous tests..): > > EXPLAIN ANALYZE UPDATE nlpg.match_data SET org = org WHERE > match_data_id < 100000; > > Result: > > "Index Scan using match_data_pkey1 on match_data > (cost=0.00..15662.17 rows=4490 width=232) (actual > time=27.055..1908.027 rows=99999 loops=1)" > " Index Cond: (match_data_id < 100000)" > "Total runtime: 25909.372 ms" > > I then ran EntrepriseDB's Tuner on my postgres install (for a > dedicated machine) and got the following settings and results: > > EXPLAIN ANALYZE UPDATE nlpg.match_data SET org = org WHERE > match_data_id < 100000; > > "Index Scan using match_data_pkey1 on match_data > (cost=0.00..13734.54 rows=4495 width=232) (actual > time=0.348..2928.844 rows=99999 loops=1)" > " Index Cond: (match_data_id < 100000)" > "Total runtime: 1066580.293 ms" > > For now, I will go with the config using 7000MB shared_buffers. > Any suggestions on how I can further optimise this config for a > single session, 64-bit install utilising ALL of 96GB RAM. I will > spend the next week making the case for a native install of Linux, > but first we need to be 100% sure that is the only way to get the > most out of Postgres on this machine. > > Thanks very much. I now feel I am at a position where I can really > explore and find the optimal configuration for my system, but > would still appreciate any suggestions. > > Cheers, > Tom > > > On 11/06/2010 07:25, Bob Lunney wrote: > > Tom, > > First off, I wouldn't use a VM if I could help it, however, > sometimes you have to make compromises. With a 16 Gb machine > running 64-bit Ubuntu and only PostgreSQL, I'd start by > allocating 4 Gb to shared_buffers. That should leave more > than enough room for the OS and file system cache. Then I'd > begin testing by measuring response times of representative > queries with significant amounts of data. > > Also, what is the disk setup for the box? Filesystem? Can > WAL files have their own disk? Is the workload OLTP or OLAP, > or a mixture of both? There is more that goes into tuning a > PG server for good performance than simply installing the > software, setting a couple of GUCs and running it. > > Bob > > --- On Thu, 6/10/10, Tom Wilcox > wrote: > > > > From: Tom Wilcox > > Subject: Re: [PERFORM] requested shared memory size > overflows size_t > To: "Bob Lunney" > > Cc: "Robert Haas" >, > pgsql-performance@postgresql.org > > Date: Thursday, June 10, 2010, 10:45 AM > Thanks guys. I am currently > installing Pg64 onto a Ubuntu Server 64-bit installation > running as a VM in VirtualBox with 16GB of RAM accessible. > If what you say is true then what do you suggest I do to > configure my new setup to best use the available 16GB (96GB > and native install eventually if the test goes well) of RAM > on Linux. > > I was considering starting by using Enterprise DBs tuner to > see if that optimises things to a better quality.. > > Tom > > On 10/06/2010 15:41, Bob Lunney wrote: > > > True, plus there are the other issues of increased > > > checkpoint times and I/O, bgwriter tuning, etc. It may > be better to let the OS cache the files and size > shared_buffers to a smaller value. > > > Bob Lunney > > --- On Wed, 6/9/10, Robert Haas > > > wrote: > > > > > From: Robert Haas > > Subject: Re: [PERFORM] requested shared memory > > > size overflows size_t > > > To: "Bob Lunney" > > Cc: pgsql-performance@postgresql.org > , > > > "Tom Wilcox" > > > > Date: Wednesday, June 9, 2010, 9:49 PM > On Wed, Jun 2, 2010 at 9:26 PM, Bob > Lunney > > wrote: > > > Your other option, of course, is a nice 64-bit > > > linux > > > > > variant, which won't have this problem at all. > > Although, even there, I think I've heard that > > > after 10GB > > > you don't get > much benefit from raising it further. Not > > > sure if > > > that's accurate or > not... > > -- Robert Haas > EnterpriseDB: http://www.enterprisedb.com > The Enterprise Postgres Company > > > > > > > > > > > > -- > Sent via pgsql-performance mailing list > (pgsql-performance@postgresql.org > ) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance > >