Thread: Shared memory usage
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.
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.
Attachment
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?
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.