Thread: Tuning shared_buffers with ipcs ?
Hello,
I've seen a couple references to using ipcs to help properly size
shared_buffers.
I don't claim to be a SA guru, so could someone help explain how to
interpret the output of ipcs and how that relates to shared_buffers? How
does one determine the size of the segment arrays? I see the total size
using ipcs -m which is roughly shared_buffers * 8k.
I tried all of the dash commands in the ipcs man page, and the only one
that might give a clue is ipcs -t which shows the time the semaphores
were last used. If you look at the example I give below, it appears as
if I'm only using 4 of the 17 semaphores (PG was started on Oct 8).
Am I correct in assuming that if the arrays are all the same size then I
should only need about 1/4 of my currently allocated shared_buffers?
------ Shared Memory Operation/Change Times --------
shmid owner last-op last-changed
847183872 postgres Fri Oct 8 11:03:31 2004 Fri Oct 8 11:03:31 2004
847216641 postgres Fri Oct 8 11:03:31 2004 Fri Oct 8 11:03:31 2004
847249410 postgres Fri Oct 8 11:03:31 2004 Fri Oct 8 11:03:31 2004
847282179 postgres Fri Oct 8 11:03:31 2004 Fri Oct 8 11:03:31 2004
847314948 postgres Fri Oct 8 11:03:31 2004 Fri Oct 8 11:03:31 2004
847347717 postgres Fri Oct 8 11:03:31 2004 Fri Oct 8 11:03:31 2004
847380486 postgres Fri Oct 8 11:03:31 2004 Fri Oct 8 11:03:31 2004
847413255 postgres Fri Oct 8 11:03:31 2004 Fri Oct 8 11:03:31 2004
847446024 postgres Fri Oct 8 11:03:31 2004 Fri Oct 8 11:03:31 2004
847478793 postgres Fri Oct 8 11:03:31 2004 Fri Oct 8 11:03:31 2004
847511562 postgres Fri Oct 8 11:03:31 2004 Fri Oct 8 11:03:31 2004
847544331 postgres Fri Oct 8 11:03:31 2004 Fri Oct 8 11:03:31 2004
847577100 postgres Fri Oct 8 11:03:31 2004 Fri Oct 8 11:03:31 2004
847609869 postgres Fri Oct 15 11:34:28 2004 Fri Oct 15 11:34:29 2004
847642638 postgres Fri Oct 15 11:33:35 2004 Fri Oct 15 11:33:35 2004
847675407 postgres Fri Oct 15 11:34:28 2004 Fri Oct 15 11:34:29 2004
847708176 postgres Fri Oct 15 11:27:17 2004 Fri Oct 15 11:32:20 2004
Also, isn't the shared memory supposed to show up in free? Its always
showing as 0:
# free
total used free shared buffers cached
Mem: 3896928 3868424 28504 0 59788 3605548
-/+ buffers/cache: 203088 3693840
Swap: 1052216 16 1052200
Thanks!
Doug Y <dylists@ptd.net> writes:
> I've seen a couple references to using ipcs to help properly size
> shared_buffers.
I have not seen any such claim, and I do not see any way offhand that
ipcs could help.
> I tried all of the dash commands in the ipcs man page, and the only one
> that might give a clue is ipcs -t which shows the time the semaphores
> were last used. If you look at the example I give below, it appears as
> if I'm only using 4 of the 17 semaphores (PG was started on Oct 8).
This might tell you something about how many concurrent backends you've
used, but nothing about how many shared buffers you need.
regards, tom lane
Tom Lane wrote: >Doug Y <dylists@ptd.net> writes: > > >>I've seen a couple references to using ipcs to help properly size >>shared_buffers. >> >> > >I have not seen any such claim, and I do not see any way offhand that >ipcs could help. > > Directly from: http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html "As a rule of thumb, observe shared memory usage of PostgreSQL with tools like ipcs and determine the setting." I've seen references in the admin >>I tried all of the dash commands in the ipcs man page, and the only one >>that might give a clue is ipcs -t which shows the time the semaphores >>were last used. If you look at the example I give below, it appears as >>if I'm only using 4 of the 17 semaphores (PG was started on Oct 8). >> >> > >This might tell you something about how many concurrent backends you've >used, but nothing about how many shared buffers you need. > > Thats strange, I know I've had more than 4 concurrent connections on that box... (I just checked and there were at least a dozen). A mirror DB with the same config also has the same basic output from ipcs, except that it has times for 11 of the 17 arrays slots and most of them are the time when we do our backup dump (which makes sense that it would require more memory at that time.) > regards, tom lane > > > > I'm not saying you're wrong, because I don't know how the nitty gritty stuff works, I'm just trying to find something to work with, since presently there isn't anything other than anecdotal evidence. From what I've inferred, there seems to be some circumstantial evidence supporting my theory. Thanks.
Doug Y <dylists@ptd.net> writes: > Tom Lane wrote: >> I have not seen any such claim, and I do not see any way offhand that >> ipcs could help. >> > Directly from: > http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html > "As a rule of thumb, observe shared memory usage of PostgreSQL with > tools like ipcs and determine the setting." [ shrug ... ] So ask elein why she thinks that will help. >> This might tell you something about how many concurrent backends you've >> used, but nothing about how many shared buffers you need. >> > Thats strange, I know I've had more than 4 concurrent connections on > that box... (I just checked and there were at least a dozen). There is more than one per-backend semaphore per semaphore set, 16 per set if memory serves; so the ipcs evidence points to a maximum of between 49 and 64 concurrently active backends. It's not telling you a darn thing about appropriate shared_buffers settings, however. > A mirror DB with the same config also has the same basic output from > ipcs, except that it has times for 11 of the 17 arrays slots and most > of them are the time when we do our backup dump (which makes sense > that it would require more memory at that time.) That doesn't follow either. I think you may have some bottleneck that causes client requests to pile up during a backup dump. regards, tom lane
Tom Lane wrote: >Doug Y <dylists@ptd.net> writes: > > >>Tom Lane wrote: >> >> >>>This might tell you something about how many concurrent backends you've >>>used, but nothing about how many shared buffers you need. >>> >>> >>> >>Thats strange, I know I've had more than 4 concurrent connections on >>that box... (I just checked and there were at least a dozen). >> >> > >There is more than one per-backend semaphore per semaphore set, 16 per >set if memory serves; so the ipcs evidence points to a maximum of >between 49 and 64 concurrently active backends. It's not telling you a >darn thing about appropriate shared_buffers settings, however. > > > >>A mirror DB with the same config also has the same basic output from >>ipcs, except that it has times for 11 of the 17 arrays slots and most >>of them are the time when we do our backup dump (which makes sense >>that it would require more memory at that time.) >> >> > >That doesn't follow either. I think you may have some bottleneck that >causes client requests to pile up during a backup dump. > > regards, tom lane > > Ok, that explains the number of arrays... max_connections / 16. Thanks... my mind works better when I can associate actual settings to effects like that. And I'm sure that performance takes a hit during out back-up dump. We're in the process of migrating them to dedicated mirror machine to run dumps/reports etc from crons so that it won't negatively affect the DB servers that get queries from the web applications. Thanks again for clarification.