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 |
