|
Larry, Considering these
recommendations, let's try setting shared_buffers to 2GB and work_mem to 16MB. The
thing is that work_mem is per connection, and if we get too aggressive and we
get a lot of simultaneous users, we can potentially eat up a lot of memory. So 2GB + (100 * 16MB) = 3.6GB
total RAM eaten up under peak load for these two values alone. If we wanted to get more
aggressive, we can raise work_mem. Carlo -----Original Message----- On 11/1/07, Carlo Stonebanks <stonec.register@xxxxxxxxxxxx>
wrote: > I am comparing the same query on two different PG 8.2 servers, one
Linux > (8GB RAM) and one Windows (32GB RAM). Both have similar drives and
CPU's. > > The Windows posgrestsql.config is pretty well tuned but it looks
like > someone had wiped out the Linux config so the default one was
re-installed. > All performance-related memory allocation values seem to be set to
the > defaults, but mods have been made: max_connections = 100 and
shared_buffers > = 32MB. > > The performance for this query is terrible on the Linux server,
and good on > the Windows server - presumably because the original Linux PG
config has > been lost. This query requires: that "set enable_seqscan to
'off';" Have you run analyze on the server yet? A few general points on performance tuning. With 8.2 you should
set shared_buffers to a pretty big chunk of memory on linux, up to 25% or so. That means 32 Meg shared buffers is REAL low for a linux
server. Try running anywhere from 512Meg up to 1Gig for starters and see if that helps too. Also turn up work_mem to something like 16 to 32
meg then restart the server after making these changes. Then give us the explain analyze output with all the enable_xxx set to
ON. summary: analyze, increase shared_buffers and work_mem, give us explain
analyze. |
