FreeBSD and large shared_buffers a no-go?

Started by Kirk Strauserover 17 years ago4 messagesgeneral
Jump to latest
#1Kirk Strauser
kirk@strauser.com

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

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Kirk Strauser (#1)
Re: FreeBSD and large shared_buffers a no-go?

Kirk Strauser <kirk@strauser.com> writes:

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

Check to see if things work as expected when you have shmmax and shmall
set to a shade less than 2GB and fail when they are a shade more. If
so, it would seem there's a signed-integer-overflow bug somewhere in the
kernel's handling of shmem requests ... which would be a reportable
kernel bug.

regards, tom lane

#3Kirk Strauser
kirk@strauser.com
In reply to: Tom Lane (#2)
Re: FreeBSD and large shared_buffers a no-go?

On Jan 7, 2009, at 12:30 PM, Tom Lane wrote:

Kirk Strauser <kirk@strauser.com> writes:

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

Check to see if things work as expected when you have shmmax and
shmall
set to a shade less than 2GB and fail when they are a shade more. If
so, it would seem there's a signed-integer-overflow bug somewhere in
the
kernel's handling of shmem requests ... which would be a reportable
kernel bug.

Actually, it seems that if shmget is trying to get more than 2GB, it
will fail regardless of shmmax. When I backed shared_buffers down to
1980MB, PostgreSQL was able to start as long as shmmax is >= 2GB
(which I verified with 2GB+1MB, 3GB, and 8GB). I'm off to file a
report now.
--
Kirk Strauser

#4Kirk Strauser
kirk@strauser.com
In reply to: Tom Lane (#2)
Re: FreeBSD and large shared_buffers a no-go?

On Jan 7, 2009, at 12:30 PM, Tom Lane wrote:

Kirk Strauser <kirk@strauser.com> writes:

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

Check to see if things work as expected when you have shmmax and
shmall
set to a shade less than 2GB and fail when they are a shade more. If
so, it would seem there's a signed-integer-overflow bug somewhere in
the
kernel's handling of shmem requests ... which would be a reportable
kernel bug.

BTW, that's at http://www.freebsd.org/cgi/query-pr.cgi?pr=130274 for
anyone who wants to follow along.
--
Kirk Strauser