Postgres Pro
Downloads
Home   >   mailing lists

Thread: Adding RAM: seeking advice & warnings of hidden "gotchas"

Adding RAM: seeking advice & warnings of hidden "gotchas"

From
"Nick Fankhauser"
Date:
Hi-

After having done my best to squeeze better performance out of our
application by tuning within our existing resources, I'm falling back on
adding memory as a short-term solution while we get creative for a long-term
fix. I'm curious about what experiences others have had with the process of
adding big chunks of RAM. In particular, if I'm trying to encourage the OS
to cache more of my index information in RAM, what sort of configuration
should I do at both the PostgreSQL and OS level?

In a slightly off-topic vein, I'd also like to hear about it if anyone knows
about any gotchas at the OS level that might become a problem.

The server is a dual processor Athlon 1.2GHz box with hardware SCSI RAID. It
currently has 1 GB RAM, and we're planning to add one GB more for a total of
2GB. The OS is Debian Linux Kernel 2.4.x, and we're on PostgreSQL v7.3.2

My current memory related settings are:

SHMMAX and SHMALL set to 128MB (OS setting)
shared buffers 8192 (64MB)
sort_mem 16384 (16MB)
effective_cache_size  65536 (512MB)


We support up to 70 active users, sharing a connection pool of 16
connections. Most of the queries center around 3 tables that are about 1.5
GB each.


Thanks.
   -Nick

---------------------------------------------------------------------
Nick Fankhauser

    nickf@doxpop.com  Phone 1.765.965.7363  Fax 1.765.962.9788
doxpop - Court records at your fingertips - http://www.doxpop.com/

Re: Adding RAM: seeking advice & warnings of hidden "gotchas"

From
Eric Soroos
Date:
On Dec 17, 2003, at 11:57 AM, Nick Fankhauser wrote:

> Hi-
>
> After having done my best to squeeze better performance out of our
> application by tuning within our existing resources, I'm falling back
> on
> adding memory as a short-term solution while we get creative for a
> long-term
> fix. I'm curious about what experiences others have had with the
> process of
> adding big chunks of RAM. In particular, if I'm trying to encourage
> the OS
> to cache more of my index information in RAM, what sort of
> configuration
> should I do at both the PostgreSQL and OS level?

You need bigmem compiled in the kernel, which you should already have
at the 1 gig level iirc.
You should bump up your effective cache size, probably to around 1.75
gig.

I wouldn't bump up the shared buffers beyond where you have them now.
If you're swapping out sorts to disk, you may gain boosting sortmem
some since you have the additional memory to use.

> The server is a dual processor Athlon 1.2GHz box with hardware SCSI
> RAID. It
> currently has 1 GB RAM, and we're planning to add one GB more for a
> total of
> 2GB. The OS is Debian Linux Kernel 2.4.x, and we're on PostgreSQL
> v7.3.2

I've got a machine running Debian Stable w/2.4.x, 1.3 ghz p3, 1.5 gig
ram, pg 7.2.4 and it's rock solid.


eric

Re: Adding RAM: seeking advice & warnings of hidden "gotchas"

From
"Matt Clark"
Date:
If you have 3 1.5GB tables then you might as well go for 4GB while you're at
it.  Make sure you've got a bigmem kernel either running or available, and
boost effective_cache_size by whatever amount you increase the RAM by.  We
run a Quad Xeon/4GB server on Redhat 7.3 and it's solid as a rock.

There is no way I know of to get indexes preferentially cached over data
though.

Matt

> -----Original Message-----
> From: pgsql-performance-owner@postgresql.org
> [mailto:pgsql-performance-owner@postgresql.org]On Behalf Of Nick
> Fankhauser
> Sent: 17 December 2003 19:57
> To: Pgsql-Performance@Postgresql. Org
> Subject: [PERFORM] Adding RAM: seeking advice & warnings of hidden
> "gotchas"
>
>
> Hi-
>
> After having done my best to squeeze better performance out of our
> application by tuning within our existing resources, I'm falling back on
> adding memory as a short-term solution while we get creative for
> a long-term
> fix. I'm curious about what experiences others have had with the
> process of
> adding big chunks of RAM. In particular, if I'm trying to encourage the OS
> to cache more of my index information in RAM, what sort of configuration
> should I do at both the PostgreSQL and OS level?
>
> In a slightly off-topic vein, I'd also like to hear about it if
> anyone knows
> about any gotchas at the OS level that might become a problem.
>
> The server is a dual processor Athlon 1.2GHz box with hardware
> SCSI RAID. It
> currently has 1 GB RAM, and we're planning to add one GB more for
> a total of
> 2GB. The OS is Debian Linux Kernel 2.4.x, and we're on PostgreSQL v7.3.2
>
> My current memory related settings are:
>
> SHMMAX and SHMALL set to 128MB (OS setting)
> shared buffers 8192 (64MB)
> sort_mem 16384 (16MB)
> effective_cache_size  65536 (512MB)
>
>
> We support up to 70 active users, sharing a connection pool of 16
> connections. Most of the queries center around 3 tables that are about 1.5
> GB each.
>
>
> Thanks.
>    -Nick
>
> ---------------------------------------------------------------------
> Nick Fankhauser
>
>     nickf@doxpop.com  Phone 1.765.965.7363  Fax 1.765.962.9788
> doxpop - Court records at your fingertips - http://www.doxpop.com/
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>