Thread: calculating shared data memory space

calculating shared data memory space

From
Jean-Michel Pouré
Date:
Dear Friends,

I am running a phpBB forum with more than 400.000 messages.
I would like to make sure that all indexes fit in shared memory.

How can I calculate the needed space of all indexes?
I remember this was part of VACUUM FULL ANALYSE or the like.

Kind regards,
Jean-Michel


Re: calculating shared data memory space

From
Greg Smith
Date:
On Mon, 7 Jan 2008, Jean-Michel Pour� wrote:

> I am running a phpBB forum with more than 400.000 messages.
> I would like to make sure that all indexes fit in shared memory.
> How can I calculate the needed space of all indexes?

There's a useful commentary on finding the sizes of various objects,
including indexes, at
http://andreas.scherbaum.la/blog/archives/282-table-size,-database-size.html

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

Re: calculating shared data memory space

From
"Scott Marlowe"
Date:
On Jan 7, 2008 11:29 AM, Jean-Michel Pouré <jm@poure.com> wrote:
> Dear Friends,
>
> I am running a phpBB forum with more than 400.000 messages.
> I would like to make sure that all indexes fit in shared memory.

You're kind of leaning towards tying to optimized things in postgresql
the way other dbs are optimized.

Not to say that increasing shared memory won't help, especially with
something like phpBB. It likely will.

A good setting for shared_buffers on a machine running 8.x and with a
fair bit of memory is usually somewhere in the 25% range.  But that is
just a guideline.  Set it to various higher and lower settings and
test to see which is best for you.

Note that the OS caches data as well, and does so quite efficiently.
The real advantage to larger shared_buffers is in the db being able to
fit what it's currently working on into its own memory in one big
chunkj.

> How can I calculate the needed space of all indexes?
> I remember this was part of VACUUM FULL ANALYSE or the like.

That's the Free Space Map I think you're talking about, and it doesn't
need to be a vacuum full analzye usually, just vacuum analyze.