Re: [PERFORM] Tuning shared_buffers with ipcs ?
Tom Lane wrote: Doug Y <[EMAIL PROTECTED]> 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. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Tuning shared_buffers with ipcs ?
Doug Y <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] Tuning shared_buffers with ipcs ?
Tom Lane wrote: Doug Y <[EMAIL PROTECTED]> 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. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Tuning shared_buffers with ipcs ?
Doug Y <[EMAIL PROTECTED]> 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 ---(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
[PERFORM] 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 shmidowner last-oplast-changed 847183872 postgresFri Oct 8 11:03:31 2004 Fri Oct 8 11:03:31 2004 847216641 postgresFri Oct 8 11:03:31 2004 Fri Oct 8 11:03:31 2004 847249410 postgresFri Oct 8 11:03:31 2004 Fri Oct 8 11:03:31 2004 847282179 postgresFri Oct 8 11:03:31 2004 Fri Oct 8 11:03:31 2004 847314948 postgresFri Oct 8 11:03:31 2004 Fri Oct 8 11:03:31 2004 847347717 postgresFri Oct 8 11:03:31 2004 Fri Oct 8 11:03:31 2004 847380486 postgresFri Oct 8 11:03:31 2004 Fri Oct 8 11:03:31 2004 847413255 postgresFri Oct 8 11:03:31 2004 Fri Oct 8 11:03:31 2004 847446024 postgresFri Oct 8 11:03:31 2004 Fri Oct 8 11:03:31 2004 847478793 postgresFri Oct 8 11:03:31 2004 Fri Oct 8 11:03:31 2004 847511562 postgresFri Oct 8 11:03:31 2004 Fri Oct 8 11:03:31 2004 847544331 postgresFri Oct 8 11:03:31 2004 Fri Oct 8 11:03:31 2004 847577100 postgresFri Oct 8 11:03:31 2004 Fri Oct 8 11:03:31 2004 847609869 postgresFri Oct 15 11:34:28 2004 Fri Oct 15 11:34:29 2004 847642638 postgresFri Oct 15 11:33:35 2004 Fri Oct 15 11:33:35 2004 847675407 postgresFri Oct 15 11:34:28 2004 Fri Oct 15 11:34:29 2004 847708176 postgresFri 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 sharedbuffers cached Mem: 38969283868424 28504 0 597883605548 -/+ buffers/cache: 2030883693840 Swap: 1052216 161052200 Thanks! ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
