Search Postgresql Archives

FreeBSD and large shared_buffers a no-go?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



I'm running PostgreSQL 8.3.5 on a FreeBSD 7.1/amd64 system with 8GB of RAM and two quad-core Xeon CPUs.  The data filesystem is on a battery-backed RAID-10 system.  This is a dedicated server so I want to commit all resources to PostgreSQL alone.  The database will get hit with a lot of small, quick queries with a few complex 10-second ones thrown in, and gets hourly bulk uploads to the tune of a few million rows.

This is actually an upgrade from older hardware that handled the load just fine, so I know this system will perform well.  My biggest concern is getting the best performance for my boss's money.

I'm using the default postgresql.conf with the following additions:

max_connections = 400                   
listen_addresses = '*'
shared_buffers = 2GB                    
temp_buffers = 32MB                     
work_mem = 64MB                         
maintenance_work_mem = 256MB            
max_stack_depth = 500MB                 
max_fsm_pages = 204800                  
full_page_writes = off                  
wal_buffers = 1MB                       
commit_delay = 100000                   
checkpoint_segments = 32                
random_page_cost = 2.0                  
effective_cache_size = 4GB
default_statistics_target = 100         
log_connections = on
log_disconnections = on
log_min_duration_statement = 5000
log_statement = 'ddl'

Now, what's confusing me is that I've set shmmax to 3GB and shmall to 3GB/4096 (the page size):

$ sysctl kern.ipc.shmmax                           
kern.ipc.shmmax: 3221225472
$ sysctl kern.ipc.shmall
kern.ipc.shmall: 786432
$ sysctl hw.pagesize
hw.pagesize: 4096

However, when shared_buffers is 2GB (one fourth of 8GB of RAM), PostgreSQL's startup fails with a call to allocated shared memory:

Jan  7 11:39:24 db1 postgres[60872]: [1-1] FATAL:  could not create shared memory segment: Cannot allocate memory
Jan  7 11:39:24 db1 postgres[60872]: [1-2] DETAIL:  Failed system call was shmget(key=5432001, size=2209497088, 03600).
Jan  7 11:39:24 db1 postgres[60872]: [1-3] HINT:  This error usually means that PostgreSQL's request for a shared memory segment exceeded available memory or swap space.
Jan  7 11:39:24 db1 postgres[60872]: [1-4]  To reduce the request size (currently 2209497088 bytes), reduce PostgreSQL's shared_buffers parameter (currently 262144) and/or
Jan  7 11:39:24 db1 postgres[60872]: [1-5]  its max_connections parameter (currently 403).
Jan  7 11:39:24 db1 postgres[60872]: [1-6] The PostgreSQL documentation contains more information about shared memory configuration.

First, shmget is asking for a lot less than shmmax - why is it failing?

Second, does the "one fourth of RAM" rule of thumb still apply on systems with decent amounts of memory?

Third, is there anything else I can be doing to take advantage of this RAM and 8 CPU cores?

Thanks!
-- 
Kirk Strauser




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux