how much ram do i give postgres?

#1

I know this is kinda a debate, but how much ram do I give postgres?
I've seen many places say around 10-15% or some say 25%....... If all
this server is doing is running postgres, why can't I give it 75%+?
Should the limit be as much as possible as long as the server doesn't
use any swap?

Any thoughts would be great, but I'd like to know why.

Thanks.

-Josh

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

#2

Josh Close <narshe (AT) gmail (DOT) com> writes:

Quote:

I know this is kinda a debate, but how much ram do I give postgres?
I've seen many places say around 10-15% or some say 25%....... If all
this server is doing is running postgres, why can't I give it 75%+?
Should the limit be as much as possible as long as the server doesn't
use any swap?

The short answer is no; the sweet spot for shared_buffers is usually on
the order of 10000 buffers, and trying to go for "75% of RAM" isn't
going to do anything except hurt. For the long answer see the
pgsql-performance list archives.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

#3

On Tue, 19 Oct 2004 17:42:16 -0400, Tom Lane <tgl (AT) sss (DOT) pgh.pa.us> wrote:

Quote:

The short answer is no; the sweet spot for shared_buffers is usually on
the order of 10000 buffers, and trying to go for "75% of RAM" isn't
going to do anything except hurt. For the long answer see the
pgsql-performance list archives.

regards, tom lane

Well, I didn't find a whole lot in the list-archives, so I emailed
that list whith a few more questions. My postgres server is just
crawling right now

-Josh

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

#4

On 19 Oct 2004 at 17:35, Josh Close wrote:

Quote:

Well, I didn't find a whole lot in the list-archives, so I emailed
that list whith a few more questions. My postgres server is just
crawling right now

Unlike many other database engines the shared buffers of Postgres is
not a private cache of the database data. It is a working area shared
between all the backend processes. This needs to be tuned for number
of connections and overall workload, *not* the amount of your database
that you want to keep in memory. There is still lots of debate about what
the "sweet spot" is. Maybe there isn't one, but its not normally 75% of
RAM.

If anything, the effective_cache_size needs to be 75% of (available)
RAM as this is telling Postgres the amount of your database the *OS* is
likely to cache in memory.

Having said that, I think you will need to define "crawling". Is it
updates/inserts that are slow? This may be triggers/rules/referential
integrity checking etc that is slowing it. If it is selects that are slow, this
may be incorrect indexes or sub-optimal queries. You need to show us
what you are trying to do and what the results are.

Regards,
Gary.

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

#5

On Wed, 20 Oct 2004 08:00:55 +0100, Gary Doades <gpd (AT) gpdnet (DOT) co.uk> wrote:

Quote:

Unlike many other database engines the shared buffers of Postgres is
not a private cache of the database data. It is a working area shared
between all the backend processes. This needs to be tuned for number
of connections and overall workload, *not* the amount of your database
that you want to keep in memory. There is still lots of debate about what
the "sweet spot" is. Maybe there isn't one, but its not normally 75% of
RAM.

If anything, the effective_cache_size needs to be 75% of (available)
RAM as this is telling Postgres the amount of your database the *OS* is
likely to cache in memory.

Having said that, I think you will need to define "crawling". Is it
updates/inserts that are slow? This may be triggers/rules/referential
integrity checking etc that is slowing it. If it is selects that are slow, this
may be incorrect indexes or sub-optimal queries. You need to show us
what you are trying to do and what the results are.

It's slow due to several things happening all at once. There are a lot
of inserts and updates happening. There is periodically a bulk insert
of 500k - 1 mill rows happening. I'm doing a vacuum anaylyze every
hour due to the amount of transactions happening, and a vacuum full
every night. All this has caused selects to be very slow. At times, a
"select count(1)" from a table will take several mins. I don't think
selects would have to wait on locks by inserts/updates would it?

I would just like to do anything possible to help speed this up.

-Josh

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

#6

Quote:

It's slow due to several things happening all at once. There are a lot
of inserts and updates happening. There is periodically a bulk insert
of 500k - 1 mill rows happening. I'm doing a vacuum anaylyze every
hour due to the amount of transactions happening, and a vacuum full
every night. All this has caused selects to be very slow. At times, a
"select count(1)" from a table will take several mins. I don't think
selects would have to wait on locks by inserts/updates would it?

I would just like to do anything possible to help speed this up.

If there are really many rows in table , select count(1) would be a

little bit slow,
for postgresql use sequential scan to count the rows. If the query is
other kind,
then may be check if there are index on search condition or use EXPLAIN
command
to see the query plan would be greatly help.

By the way, what's the version of your postgresql? older version (<7.4?)
still suffer from index
space bloating.

regards

Laser

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

#7

On Wed, 2004-10-20 at 07:25, Josh Close wrote:

Quote:

It's slow due to several things happening all at once. There are a lot
of inserts and updates happening. There is periodically a bulk insert
of 500k - 1 mill rows happening. I'm doing a vacuum anaylyze every
hour due to the amount of transactions happening, and a vacuum full
every night. All this has caused selects to be very slow. At times, a
"select count(1)" from a table will take several mins. I don't think
selects would have to wait on locks by inserts/updates would it?

1: Is the bulk insert being done inside of a single transaction, or as
individual inserts?

2: Are your fsm settings high enough for an hourly vacuum to be
effective?

3: How selective is the where clause for your select (1) query? If
there is no where clause or the where clause isn't very selective, then
there will be a sequential scan every time. Since PostgreSQL has to hit
the table after using an index anyway, if it's going to retrieve a fair
percent of a table, it just goes right to a seq scan, which for
postgresql, is the right thing to do.

Post "explain analyze" of your slowest queries to the performance list
if you can.

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

#8

On Wed, 20 Oct 2004 09:52:25 -0600, Scott Marlowe <smarlowe (AT) qwest (DOT) net> wrote:

Quote:

1: Is the bulk insert being done inside of a single transaction, or as
individual inserts?

The bulk insert is being done by COPY FROM STDIN. It copies in 100,000
rows at a time, then disconnects, reconnects, and copies 100k more,
and repeats 'till done. There are no indexes on the tables that the
copy is being done into either, so it won't be slowed down by that at
all.

Quote:

2: Are your fsm settings high enough for an hourly vacuum to be
effective?

What is fsm? I'll tell you when I find that out.

Quote:

3: How selective is the where clause for your select (1) query? If
there is no where clause or the where clause isn't very selective, then
there will be a sequential scan every time. Since PostgreSQL has to hit
the table after using an index anyway, if it's going to retrieve a fair
percent of a table, it just goes right to a seq scan, which for
postgresql, is the right thing to do.

There was no where clause.

Quote:

Post "explain analyze" of your slowest queries to the performance list
if you can.

I don't think it's a query problem ( but I could optimize them more
I'm sure ), 'cause the same query takes a long time when there are
other queries happening, and not long at all when nothing else is
going on.

-Josh

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

#9

On 20 Oct 2004 at 11:37, Josh Close wrote:

Quote:

On Wed, 20 Oct 2004 09:52:25 -0600, Scott Marlowe <smarlowe (AT) qwest (DOT) net> wrote:
1: Is the bulk insert being done inside of a single transaction, or as
individual inserts?

The bulk insert is being done by COPY FROM STDIN. It copies in 100,000
rows at a time, then disconnects, reconnects, and copies 100k more,
and repeats 'till done. There are no indexes on the tables that the
copy is being done into either, so it won't be slowed down by that at
all.

What about triggers? Also constraints (check contraints, integrity
constraints) All these will slow the inserts/updates down.

If you have integrity constraints make sure you have indexes on the
referenced columns in the referenced tables and make sure the data
types are the same.

How long does 100,000 rows take to insert exactly?

How many updates are you performing each hour?

Regards,
Gary.

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

#10

On Wed, 20 Oct 2004 18:47:25 +0100, Gary Doades <gpd (AT) gpdnet (DOT) co.uk> wrote:

Quote:

What about triggers? Also constraints (check contraints, integrity
constraints) All these will slow the inserts/updates down.

No triggers or constraints. There are some foreign keys, but the
tables that have the inserts don't have anything to them, even
indexes, to help speed up the inserts.

Quote:

If you have integrity constraints make sure you have indexes on the
referenced columns in the referenced tables and make sure the data
types are the same.

How long does 100,000 rows take to insert exactly?

I believe with the bulk inserts, 100k only takes a couple mins.

Quote:

How many updates are you performing each hour?

I'm not sure about this. Is there a pg stats table I can look at to
find this out..... I suppose I could do a count on the time stamp
also. I'll let you know when I find out.

Quote:

Regards,
Gary.

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly