SEO

Hello.

I have a postgres 8.0 and ~400mb database with lots of simple selects
using indexes.
I've installed pgpool on the system. I've set num_init_children to 5 and
here is the top output.
One of postmasters is my demon running some insert/update tasks. I see
that they all use cpu heavily, but do not use the shared memory.
shared_buffers is set to 60000, yet they use a minimal part of that. I'd
like to know why won't they use more? All the indexes and half of the
database should be in the shared memory, is it not? Or am I completely
missing what are the shared_buffers for? If so, then how do I put my
indexes and at least a part of the data into memory?

top - 00:12:35 up 50 days, 13:22, 8 users, load average: 4.84, 9.71,
13.22
Tasks: 279 total, 10 running, 268 sleeping, 1 stopped, 0 zombie
Cpu(s): 50.0% us, 12.9% sy, 0.0% ni, 33.2% id, 1.8% wa, 0.0% hi, 2.1%
si
Mem: 6102304k total, 4206948k used, 1895356k free, 159436k buffers
Swap: 1959888k total, 12304k used, 1947584k free, 2919816k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
11492 postgres 16 0 530m 72m 60m S 14 1.2 0:50.91 postmaster
11493 postgres 16 0 531m 72m 60m R 14 1.2 0:48.78 postmaster
11490 postgres 15 0 530m 71m 59m S 13 1.2 0:50.26 postmaster
11491 postgres 15 0 531m 75m 62m S 11 1.3 0:50.67 postmaster
11495 postgres 16 0 530m 71m 59m R 10 1.2 0:50.71 postmaster
10195 postgres 15 0 536m 84m 66m S 6 1.4 1:11.72 postmaster

postgresql.conf:

shared_buffers = 60000
work_mem = 2048
maintenance_work_mem = 256000

The rest are basically default values

Thank you in advance.

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

On Sun, Aug 26, 2007 at 01:22:58AM +0400, Max Zorloff wrote:
> Hello.
>
> I have a postgres 8.0 and ~400mb database with lots of simple selects
> using indexes.
> I've installed pgpool on the system. I've set num_init_children to 5 and
> here is the top output.
> One of postmasters is my demon running some insert/update tasks. I see
> that they all use cpu heavily, but do not use the shared memory.
> shared_buffers is set to 60000, yet they use a minimal part of that. I'd
> like to know why won't they use more? All the indexes and half of the
> database should be in the shared memory, is it not? Or am I completely
> missing what are the shared_buffers for? If so, then how do I put my
> indexes and at least a part of the data into memory?

shared_memory is used for caching. It is filled as stuff is used. If
you're not using all of it that means it isn't needed. Remember, it is
not the only cache. Since your database is only 400MB it will fit
entirely inside the OS disk cache, so you really don't need much shared
memory at all.

Loading stuff into memory for the hell of it is a waste, let the system
manage the memory itself, if it needs it, it'll use it.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD4DBQFG0JOXIB7bNG8LQkwRAv+yAJYtwvui0QYYslBy0ALO2C QOqjOXAJ4r+fxD
5cbxtF6307Ai2kLv4VqYQg==
=XRxZ
-----END PGP SIGNATURE-----

On Sun, 26 Aug 2007 00:39:52 +0400, Martijn van Oosterhout
<kleptog@svana.org> wrote:

> On Sun, Aug 26, 2007 at 01:22:58AM +0400, Max Zorloff wrote:
>> Hello.
>>
>> I have a postgres 8.0 and ~400mb database with lots of simple selects
>> using indexes.
>> I've installed pgpool on the system. I've set num_init_children to 5 and
>> here is the top output.
>> One of postmasters is my demon running some insert/update tasks. I see
>> that they all use cpu heavily, but do not use the shared memory.
>> shared_buffers is set to 60000, yet they use a minimal part of that. I'd
>> like to know why won't they use more? All the indexes and half of the
>> database should be in the shared memory, is it not? Or am I completely
>> missing what are the shared_buffers for? If so, then how do I put my
>> indexes and at least a part of the data into memory?
>
> shared_memory is used for caching. It is filled as stuff is used. If
> you're not using all of it that means it isn't needed. Remember, it is
> not the only cache. Since your database is only 400MB it will fit
> entirely inside the OS disk cache, so you really don't need much shared
> memory at all.
>
> Loading stuff into memory for the hell of it is a waste, let the system
> manage the memory itself, if it needs it, it'll use it.
>
> Have a nice day,

Could it be that most of the cpu usage is from lots of fast indexed sql
queries
wrapped in sql functions?

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

On Sun, 26 Aug 2007 00:39:52 +0400, Martijn van Oosterhout
<kleptog@svana.org> wrote:

> On Sun, Aug 26, 2007 at 01:22:58AM +0400, Max Zorloff wrote:
>> Hello.
>>
> shared_memory is used for caching. It is filled as stuff is used. If
> you're not using all of it that means it isn't needed. Remember, it is
> not the only cache. Since your database is only 400MB it will fit
> entirely inside the OS disk cache, so you really don't need much shared
> memory at all.
>
> Loading stuff into memory for the hell of it is a waste, let the system
> manage the memory itself, if it needs it, it'll use it.
>

Where do I find my OS disk cache settings? I'm using Linux.

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

> I have a postgres 8.0 and ~400mb database with lots of simple selects
> using indexes.
> I've installed pgpool on the system. I've set num_init_children to 5 and
> here is the top output.
> One of postmasters is my demon running some insert/update tasks. I see
> that they all use cpu heavily, but do not use the shared memory.
> shared_buffers is set to 60000, yet they use a minimal part of that. I'd
> like to know why won't they use more?

This just looks like the output of top; what is telling you that
PostgreSQL is not using the shared memory? Enable statistics collection
and then look in pg_statio_user_tables.

> top - 00:12:35 up 50 days, 13:22, 8 users, load average: 4.84, 9.71,
> 13.22
> Tasks: 279 total, 10 running, 268 sleeping, 1 stopped, 0 zombie
> Cpu(s): 50.0% us, 12.9% sy, 0.0% ni, 33.2% id, 1.8% wa, 0.0% hi, 2.1%
> si
> Mem: 6102304k total, 4206948k used, 1895356k free, 159436k buffers
> Swap: 1959888k total, 12304k used, 1947584k free, 2919816k cached
> PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
> 11492 postgres 16 0 530m 72m 60m S 14 1.2 0:50.91 postmaster
> 11493 postgres 16 0 531m 72m 60m R 14 1.2 0:48.78 postmaster
> 11490 postgres 15 0 530m 71m 59m S 13 1.2 0:50.26 postmaster
> 11491 postgres 15 0 531m 75m 62m S 11 1.3 0:50.67 postmaster
> 11495 postgres 16 0 530m 71m 59m R 10 1.2 0:50.71 postmaster
> 10195 postgres 15 0 536m 84m 66m S 6 1.4 1:11.72 postmaster

--
Adam Tauno Williams, Network & Systems Administrator
Consultant - http://www.whitemiceconsulting.com
Developer - http://www.opengroupware.org


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

http://archives.postgresql.org

On Mon, 27 Aug 2007 14:21:43 +0400, Adam Tauno Williams
<adamtaunowilliams@gmail.com> wrote:

>> I have a postgres 8.0 and ~400mb database with lots of simple selects
>> using indexes.
>> I've installed pgpool on the system. I've set num_init_children to 5 and
>> here is the top output.
>> One of postmasters is my demon running some insert/update tasks. I see
>> that they all use cpu heavily, but do not use the shared memory.
>> shared_buffers is set to 60000, yet they use a minimal part of that. I'd
>> like to know why won't they use more?
>
> This just looks like the output of top; what is telling you that
> PostgreSQL is not using the shared memory? Enable statistics collection
> and then look in pg_statio_user_tables.

I have it enabled. How can I tell whether the shared memory is used from
the information in this table?

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

First off, posting to two lists like you did (-general and -performance)
is frowned on here. Pick whichever is more appropriate for the topic and
post to just that one; in your case, the performance list would be more
appropriate, and I'm only replying to there.

On Sun, 26 Aug 2007, Max Zorloff wrote:

> shared_buffers is set to 60000, yet they use a minimal part of that.
> PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
> 11492 postgres 16 0 530m 72m 60m S 14 1.2 0:50.91 postmaster

Looks to me like PostgreSQL is grabbing 530MB worth of memory on your
system. run the ipcs command to see how big the block that's dedicated to
the main server is; I suspect you'll find it's at 400MB just like you
expect it to be. Here's an example from my server which has a 256MB
shared_buffers:

-bash-3.00$ ipcs
------ Shared Memory Segments --------
key shmid owner perms bytes nattch status
0x0052e2c1 1114114 postgres 600 277856256 3

Also: when you've got top running, hit the "c" key and the postmaster
processes will give you more information about what they're doing you may
find helpful.

> All the indexes and half of the database should be in the shared memory,
> is it not? Or am I completely missing what are the shared_buffers for?
> If so, then how do I put my indexes and at least a part of the data into
> memory?

You can find out what's inside the shared_buffers cache by using the
installing the contrib/pg_buffercache module against your database. The
README.pg_buffercache file in there gives instructions on how to install
it, and the sample query provided there should tell you what you're
looking for here.

> Where do I find my OS disk cache settings? I'm using Linux.

You can get a summary of how much memory Linux is using to cache data by
running the free command, and more in-depth information is available if
you look at the /proc/meminfo information. I have a paper you may find
helpful here, it has more detail in it than you need but it provides some
pointers to resources to help you better understand how memory management
in Linux works: http://www.westnet.com/~gsmith/conte...ux-pdflush.htm

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

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

On Wed, 29 Aug 2007 23:26:06 +0400, Greg Smith <gsmith@gregsmith.com>
wrote:

> First off, posting to two lists like you did (-general and -performance)
> is frowned on here. Pick whichever is more appropriate for the topic
> and post to just that one; in your case, the performance list would be
> more appropriate, and I'm only replying to there.

Sorry, didn't know that.

> On Sun, 26 Aug 2007, Max Zorloff wrote:
>
>> shared_buffers is set to 60000, yet they use a minimal part of that.
>> PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
>> 11492 postgres 16 0 530m 72m 60m S 14 1.2 0:50.91 postmaster
>
> Looks to me like PostgreSQL is grabbing 530MB worth of memory on your
> system. run the ipcs command to see how big the block that's dedicated
> to the main server is; I suspect you'll find it's at 400MB just like you
> expect it to be. Here's an example from my server which has a 256MB
> shared_buffers:
>
> -bash-3.00$ ipcs
> ------ Shared Memory Segments --------
> key shmid owner perms bytes nattch status
> 0x0052e2c1 1114114 postgres 600 277856256 3
>
> Also: when you've got top running, hit the "c" key and the postmaster
> processes will give you more information about what they're doing you
> may find helpful.
>
>> All the indexes and half of the database should be in the shared
>> memory, is it not? Or am I completely missing what are the
>> shared_buffers for? If so, then how do I put my indexes and at least a
>> part of the data into memory?
>
> You can find out what's inside the shared_buffers cache by using the
> installing the contrib/pg_buffercache module against your database. The
> README.pg_buffercache file in there gives instructions on how to install
> it, and the sample query provided there should tell you what you're
> looking for here.

Thanks, I'll see that.

>> Where do I find my OS disk cache settings? I'm using Linux.
>
> You can get a summary of how much memory Linux is using to cache data by
> running the free command, and more in-depth information is available if
> you look at the /proc/meminfo information. I have a paper you may find
> helpful here, it has more detail in it than you need but it provides
> some pointers to resources to help you better understand how memory
> management in Linux works:
> http://www.westnet.com/~gsmith/conte...ux-pdflush.htm

Thanks for that, too.

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

http://www.postgresql.org/docs/faq