Re: requested shared memory size overflows size_t - Mailing list pgsql-performance
From | Tom Wilcox |
---|---|
Subject | Re: requested shared memory size overflows size_t |
Date | |
Msg-id | 4C16CC1D.5070108@gmail.com Whole thread Raw |
In response to | Re: requested shared memory size overflows size_t (Dave Crooke <dcrooke@gmail.com>) |
Responses |
Re: requested shared memory size overflows size_t
|
List | pgsql-performance |
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 <hungrytom@gmail.com > <mailto:hungrytom@gmail.com>> 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 <hungrytom@gmail.com > <mailto:hungrytom@gmail.com>> wrote: > > > > From: Tom Wilcox <hungrytom@gmail.com > <mailto:hungrytom@gmail.com>> > Subject: Re: [PERFORM] requested shared memory size > overflows size_t > To: "Bob Lunney" <bob_lunney@yahoo.com > <mailto:bob_lunney@yahoo.com>> > Cc: "Robert Haas" <robertmhaas@gmail.com > <mailto:robertmhaas@gmail.com>>, > pgsql-performance@postgresql.org > <mailto: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<robertmhaas@gmail.com > <mailto:robertmhaas@gmail.com>> > > wrote: > > > > > From: Robert Haas<robertmhaas@gmail.com > <mailto:robertmhaas@gmail.com>> > Subject: Re: [PERFORM] requested shared memory > > > size overflows size_t > > > To: "Bob Lunney"<bob_lunney@yahoo.com > <mailto:bob_lunney@yahoo.com>> > Cc: pgsql-performance@postgresql.org > <mailto:pgsql-performance@postgresql.org>, > > > "Tom Wilcox"<hungrytom@googlemail.com > <mailto:hungrytom@googlemail.com>> > > > Date: Wednesday, June 9, 2010, 9:49 PM > On Wed, Jun 2, 2010 at 9:26 PM, Bob > Lunney<bob_lunney@yahoo.com > <mailto:bob_lunney@yahoo.com>> > 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 > <mailto:pgsql-performance@postgresql.org>) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance > >
pgsql-performance by date: