Re: requested shared memory size overflows size_t - Mailing list pgsql-performance

Tom Wilcox wrote:
> default_statistics_target=10000
> wal_buffers=1GB
> max_connections=3
> effective_cache_size=15GB
> maintenance_work_mem=5GB
> shared_buffers=7000MB
> work_mem=5GB

That value for default_statistics_target means that every single query
you ever run will take a seriously long time to generate a plan for.
Even on an OLAP system, I would consider 10,000 an appropriate setting
for a column or two in a particularly troublesome table.  I wouldn't
consider a value of even 1,000 in the postgresql.conf to be a good
idea.  You should consider making the system default much lower, and
increase it only on columns that need it, not for every column on every
table.

There is no reason to set wal_buffers larger than 16MB, the size of a
full WAL segment.  Have you read
http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server yet?
checkpoint_segments is the main parameter you haven't touched yet you
should consider increasing.  Even if you have a low write load, when
VACUUM runs it will be very inefficient running against a large set of
tables without the checkpoint frequency being decreased some.  Something
in the 16-32 range would be plenty for an OLAP setup.

At 3 connections, a work_mem of 5GB is possibly reasonable.  I would
normally recommend that you make the default much smaller than that
though, and instead just increase to a large value for queries that
benefit from it.  If someone later increases max_connections to
something higher, your server could run completely out of memory if
work_mem isn't cut way back as part of that change.

You could consider setting effective_cache_size to something even larger
than that,

> EXPLAIN ANALYZE UPDATE nlpg.match_data SET org = org WHERE
> match_data_id < 100000;

By the way--repeatedly running this form of query to test for
improvements in speed is not going to give you particularly good
results.  Each run will execute a bunch of UPDATE statements that leave
behind dead rows.  So the next run done for comparison sake will either
have to cope with that additional overhead, or it will end up triggering
autovacuum and suffer from that.  If you're going to use an UPDATE
statement as your benchmark, at a minimum run a manual VACUUM ANALYZE in
between each test run, to level out the consistency of results a bit.
Ideally you'd restore the whole database to an initial state before each
test.

> 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.

I really cannot imagine taking a system as powerful as you're using here
and crippling it by running through a VM.  You should be running Ubuntu
directly on the hardware, ext3 filesystem without LVM, split off RAID-1
drive pairs dedicated to OS and WAL, then use the rest of them for the
database.

--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com   www.2ndQuadrant.us