[PERFORM] Memory reporting on CentOS Linux
I am confused about what the OS is reporting for memory usage on CentOS 5.3 Linux. Looking at the resident memory size of the processes. Looking at the resident size of all postgres processes, the system should be using around 30Gb of physical ram. I know that it states that it is using a lot of shared memory. My question is how to I determine how much physical RAM postgres is using at any point in time? This server has 24Gb of ram, and is reporting that 23GB is free for use. See calculation below (Memory Total - Used) + (Buffers + Cached) = Free Memory (24675740 - 24105052) + (140312 + 22825616) = 23,536,616 or ~23 Gigabytes So if my server has 23Gb of ram that is free for use, why is postgres reporting resident sizes of 30GB? Shared memory is reporting the same values, so how is the OS reporting that only 1Gb of RAM is being used? Help? top - 12:43:41 up 2 days, 19:04, 2 users, load average: 4.99, 4.81, 4.33 Tasks: 245 total, 4 running, 241 sleeping, 0 stopped, 0 zombie Cpu(s): 26.0%us, 0.0%sy, 0.0%ni, 73.9%id, 0.1%wa, 0.0%hi, 0.0%si, 0.0%st Mem: 24675740k total, 24105052k used, 570688k free, 140312k buffers Swap: 2097144k total, 272k used, 2096872k free, 22825616k cached - PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 19469 postgres 15 0 8324m 7.9g 7.9g S 0.0 33.7 0:54.30 postgres: writer process 29763 postgres 25 0 8329m 4.5g 4.5g R 99.8 19.0 24:53.02 postgres: niadmin database x.x.x.49(51136) UPDATE 29765 postgres 25 0 8329m 4.4g 4.4g R 99.8 18.8 24:42.77 postgres: niadmin database x.x.x.49(51138) UPDATE 31778 postgres 25 0 8329m 4.2g 4.2g R 99.5 17.8 17:56.95 postgres: niadmin database x.x.x.49(51288) UPDATE 31779 postgres 25 0 8329m 4.2g 4.2g R 99.1 17.8 17:59.62 postgres: niadmin database x.x.x.49(51289) UPDATE 31780 postgres 23 0 8329m 4.1g 4.1g R 100.1 17.5 17:52.53 postgres: niadmin database x.x.x.49(51290) UPDATE 19467 postgres 15 0 8320m 160m 160m S 0.0 0.7 0:00.24 /opt/PostgreSQL/8.3/bin/postgres -D /opt/PostgreSQL/8.3/data 19470 postgres 15 0 8324m 2392 1880 S 0.0 0.0 0:01.72 postgres: wal writer process
Re: [PERFORM] Memory reporting on CentOS Linux
On Fri, 2009-08-14 at 14:00 -0400, Jeremy Carroll wrote: I am confused about what the OS is reporting for memory usage on CentOS 5.3 Linux. Looking at the resident memory size of the processes. Looking at the resident size of all postgres processes, the system should be using around 30Gb of physical ram. I know that it states that it is using a lot of shared memory. My question is how to I determine how much physical RAM postgres is using at any point in time? This server has 24Gb of ram, and is reporting that 23GB is free for use. See calculation below (Memory Total – Used) + (Buffers + Cached) = Free Memory (24675740 – 24105052) + (140312 + 22825616) = 23,536,616 or ~23 Gigabytes you're using cached swap in your calculation ( 22825616 ) swap is not RAM -- it's disk So if my server has 23Gb of ram that is free for use, why is postgres reporting resident sizes of 30GB? Shared memory is reporting the same values, so how is the OS reporting that only 1Gb of RAM is being used? you have 570688k free RAM + 140312k buffers RAM This looks to me like the OS is saying that you are using 24105052k used Help? top - 12:43:41 up 2 days, 19:04, 2 users, load average: 4.99, 4.81, 4.33 Tasks: 245 total, 4 running, 241 sleeping, 0 stopped, 0 zombie Cpu(s): 26.0%us, 0.0%sy, 0.0%ni, 73.9%id, 0.1%wa, 0.0%hi, 0.0% si, 0.0%st Mem: 24675740k total, 24105052k used, 570688k free, 140312k buffers Swap: 2097144k total, 272k used, 2096872k free, 22825616k cached - PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 19469 postgres 15 0 8324m 7.9g 7.9g S 0.0 33.7 0:54.30 postgres: writer process 29763 postgres 25 0 8329m 4.5g 4.5g R 99.8 19.0 24:53.02 postgres: niadmin database x.x.x.49(51136) UPDATE 29765 postgres 25 0 8329m 4.4g 4.4g R 99.8 18.8 24:42.77 postgres: niadmin database x.x.x.49(51138) UPDATE 31778 postgres 25 0 8329m 4.2g 4.2g R 99.5 17.8 17:56.95 postgres: niadmin database x.x.x.49(51288) UPDATE 31779 postgres 25 0 8329m 4.2g 4.2g R 99.1 17.8 17:59.62 postgres: niadmin database x.x.x.49(51289) UPDATE 31780 postgres 23 0 8329m 4.1g 4.1g R 100.1 17.5 17:52.53 postgres: niadmin database x.x.x.49(51290) UPDATE 19467 postgres 15 0 8320m 160m 160m S 0.0 0.7 0:00.24 /opt/PostgreSQL/8.3/bin/postgres -D /opt/PostgreSQL/8.3/data 19470 postgres 15 0 8324m 2392 1880 S 0.0 0.0 0:01.72 postgres: wal writer process -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Memory reporting on CentOS Linux
But the kernel can take back any of the cache memory if it wants to. Therefore it is free memory. This still does not explain why the top command is reporting ~9GB of resident memory, yet the top command does not suggest that any physical memory is being used. On 8/14/09 2:43 PM, Reid Thompson reid.thomp...@ateb.com wrote: you're using cached swap in your calculation ( 22825616 ) swap is not RAM -- it's disk
Re: [PERFORM] Memory reporting on CentOS Linux
On Fri, Aug 14, 2009 at 12:00 PM, Jeremy Carrolljeremy.carr...@networkedinsights.com wrote: I am confused about what the OS is reporting for memory usage on CentOS 5.3 Linux. Looking at the resident memory size of the processes. Looking at the resident size of all postgres processes, the system should be using around 30Gb of physical ram. I know that it states that it is using a lot of shared memory. My question is how to I determine how much physical RAM postgres is using at any point in time? OK, take the first pg process, and write down its RES size. For all the rest, write down RES-SHR for how much more it's using. Since they use a lot of shared memory, and since you're showing something like 7.9G shared, I'm gonna guess that's the size of your shared_buffers. With those numbers you should get something just over a shade of 7.9G used, and most of that is shared_buffers. Also, a quick check is to look at this number: 22825616k cached which tells you how much memory the OS is using for cache, which is ~22G. I note that you've got 2G swapped out, this might well be shared_buffers or something you'd rather not have swapped out. Look into setting your swappiness lower (5 or so should do) to stop the OS from swapping so much out. /sbin/sysctl -a|grep swappiness vm.swappiness = 60 is the default. You can change it permanently by editing your /etc/sysctl.conf file (or wherever it lives) and rebooting, or running /sbin/sysctl -p to process the entries and make them stick this session. My big servers run with swappiness of 1 with no problems. This server has 24Gb of ram, and is reporting that 23GB is free for use. See calculation below (Memory Total – Used) + (Buffers + Cached) = Free Memory (24675740 – 24105052) + (140312 + 22825616) = 23,536,616 or ~23 Gigabytes So if my server has 23Gb of ram that is free for use, why is postgres reporting resident sizes of 30GB? Shared memory is reporting the same values, so how is the OS reporting that only 1Gb of RAM is being used? Help? top - 12:43:41 up 2 days, 19:04, 2 users, load average: 4.99, 4.81, 4.33 Tasks: 245 total, 4 running, 241 sleeping, 0 stopped, 0 zombie Cpu(s): 26.0%us, 0.0%sy, 0.0%ni, 73.9%id, 0.1%wa, 0.0%hi, 0.0%si, 0.0%st Mem: 24675740k total, 24105052k used, 570688k free, 140312k buffers Swap: 2097144k total, 272k used, 2096872k free, 22825616k cached - PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 19469 postgres 15 0 8324m 7.9g 7.9g S 0.0 33.7 0:54.30 postgres: writer process 29763 postgres 25 0 8329m 4.5g 4.5g R 99.8 19.0 24:53.02 postgres: niadmin database x.x.x.49(51136) UPDATE 29765 postgres 25 0 8329m 4.4g 4.4g R 99.8 18.8 24:42.77 postgres: niadmin database x.x.x.49(51138) UPDATE 31778 postgres 25 0 8329m 4.2g 4.2g R 99.5 17.8 17:56.95 postgres: niadmin database x.x.x.49(51288) UPDATE 31779 postgres 25 0 8329m 4.2g 4.2g R 99.1 17.8 17:59.62 postgres: niadmin database x.x.x.49(51289) UPDATE 31780 postgres 23 0 8329m 4.1g 4.1g R 100.1 17.5 17:52.53 postgres: niadmin database x.x.x.49(51290) UPDATE 19467 postgres 15 0 8320m 160m 160m S 0.0 0.7 0:00.24 /opt/PostgreSQL/8.3/bin/postgres -D /opt/PostgreSQL/8.3/data 19470 postgres 15 0 8324m 2392 1880 S 0.0 0.0 0:01.72 postgres: wal writer process -- When fascism comes to America, it will be intolerance sold as diversity. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Memory reporting on CentOS Linux
I'm betting it's shared_buffers that have been swapped out (2G swapped out on his machine) for kernel cache.The RES and SHR being the same says the actual processes are using hardly any ram, just hitting shared_buffers. On Fri, Aug 14, 2009 at 2:20 PM, Jeremy Carrolljeremy.carr...@networkedinsights.com wrote: But the kernel can take back any of the cache memory if it wants to. Therefore it is free memory. This still does not explain why the top command is reporting ~9GB of resident memory, yet the top command does not suggest that any physical memory is being used. On 8/14/09 2:43 PM, Reid Thompson reid.thomp...@ateb.com wrote: you're using cached swap in your calculation ( 22825616 ) swap is not RAM -- it's disk -- When fascism comes to America, it will be intolerance sold as diversity. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Memory reporting on CentOS Linux
On 8/14/09 11:00 AM, Jeremy Carroll jeremy.carr...@networkedinsights.com wrote: I am confused about what the OS is reporting for memory usage on CentOS 5.3 Linux. Looking at the resident memory size of the processes. Looking at the resident size of all postgres processes, the system should be using around 30Gb of physical ram. I know that it states that it is using a lot of shared memory. My question is how to I determine how much physical RAM postgres is using at any point in time? Resident includes Shared. Shared is shared. So you have to subtract it from all the processes to see what they use on their own. What you really want is RES-SHR, or some of the other columns available in top. Hit 'h' in top to get some help on the other columns available, and 'f' and 'o' manipulate them. In particular, you might find the DATA column useful. It is approximately RES-SHR-CODE This server has 24Gb of ram, and is reporting that 23GB is free for use. See calculation below (Memory Total Used) + (Buffers + Cached) = Free Memory (24675740 24105052) + (140312 + 22825616) = 23,536,616 or ~23 Gigabytes So if my server has 23Gb of ram that is free for use, why is postgres reporting resident sizes of 30GB? Shared memory is reporting the same values, so how is the OS reporting that only 1Gb of RAM is being used? Help? top - 12:43:41 up 2 days, 19:04, 2 users, load average: 4.99, 4.81, 4.33 Tasks: 245 total, 4 running, 241 sleeping, 0 stopped, 0 zombie Cpu(s): 26.0%us, 0.0%sy, 0.0%ni, 73.9%id, 0.1%wa, 0.0%hi, 0.0%si, 0.0%st Mem: 24675740k total, 24105052k used, 570688k free, 140312k buffers Swap: 2097144k total, 272k used, 2096872k free, 22825616k cached - PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 19469 postgres 15 0 8324m 7.9g 7.9g S 0.0 33.7 0:54.30 postgres: writer process 29763 postgres 25 0 8329m 4.5g 4.5g R 99.8 19.0 24:53.02 postgres: niadmin database x.x.x.49(51136) UPDATE Lets just take the two above and pretend that they are the only postgres processes. The RAM used by each exclusively is RES-SHR. Or, close to nothing for these two, aside from the rounding error. The memory used by postgres for shared memory is the largest of all SHR columns for postgres columns. Or, about 7.9GB. So, postgres is using about 7.9GB for shared memory, and very little for anything else. In formula form, its close to SUM(RES) - SUM(SHR) + MAX(SHR). That doesn't cover everything, but is very close. See the other columns available in top. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Per-database warm standby?
8.4 has vastly improved the warm-standby features, but it looks to me like this is still an installation-wide backup, not a per-database backup. That is, if you have (say) a couple hundred databases, and you only want warm-backup on one of them, you can't do it (except using other solutions like Slony). Is that right? Thanks, Craig -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Per-database warm standby?
Craig James craig_ja...@emolecules.com writes: 8.4 has vastly improved the warm-standby features, but it looks to me like this is still an installation-wide backup, not a per-database backup. That is, if you have (say) a couple hundred databases, and you only want warm-backup on one of them, you can't do it (except using other solutions like Slony). Is that right? Correct, and that's always going to be true of any WAL-based solution. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Per-database warm standby?
Tom Lane wrote: Craig James craig_ja...@emolecules.com writes: 8.4 has vastly improved the warm-standby features, but it looks to me like this is still an installation-wide backup, not a per-database backup. That is, if you have (say) a couple hundred databases, and you only want warm-backup on one of them, you can't do it (except using other solutions like Slony). Is that right? Correct, and that's always going to be true of any WAL-based solution. Except that we could create a WAL filter to restore only relevant stuff to particular databases ... Would that work? Of course, it would have to ensure that global objects are also recovered, but we could simply ignore commands for other databases. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Memory usage of writer process
This is postgres 8.4 BTW. It says 2.9Gb of RESIDENT memory, that also seems to be shared. Is this the writer sharing the records it wrote in a shared buffer? PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 11088 postgres 13 -2 3217m 3.0g 3.0g S0 39.5 0:14.23 postgres: writer process 968 postgres 14 -2 3219m 1.4g 1.4g S0 18.8 4:37.57 postgres: cribq cribq [local] idle 24593 postgres 13 -2 3219m 331m 327m S0 4.3 0:10.12 postgres: cribq cribq [local] idle 26181 postgres 13 -2 3219m 323m 319m S0 4.2 0:06.48 postgres: cribq cribq [local] idle 12504 postgres 14 -2 3219m 297m 293m S0 3.9 0:02.71 postgres: cribq cribq [local] idle 13565 postgres 14 -2 3219m 292m 288m S0 3.8 0:02.75 postgres: cribq cribq [local] idle 623 postgres 13 -2 3219m 292m 287m S0 3.8 0:02.28 postgres: cribq cribq [local] idle On Thu, Aug 13, 2009 at 1:29 PM, Alvaro Herreraalvhe...@commandprompt.com wrote: Alex wrote: The writer process seems to be using inordinate amounts of memory: PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 11088 postgres 13 -2 3217m 2.9g 2.9g S 0 38.7 0:10.46 postgres: writer process 20190 postgres 13 -2 3219m 71m 68m S 0 0.9 0:52.48 postgres: cribq cribq [local] idle I am writing moderately large (~3k) records to my database a few times a second. Even when I stop doing that, the process continues to take up all of that memory. Am I reading this right? Why is it using so much memory? shared_buffers? -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Alex Neth Liivid, Inc www.liivid.com +1 206 499 4995 +86 13761577188 Stephen Leacock - I detest life-insurance agents: they always argue that I shall some day die, which is not so. - http://www.brainyquote.com/quotes/authors/s/stephen_leacock.html -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Scalability in postgres
On Thu, 4 Jun 2009 06:57:57 -0400, Robert Haas robertmh...@gmail.com wrote in http://archives.postgresql.org/pgsql-performance/2009-06/msg00065.php : I think I see the distinction you're drawing here. IIUC, you're arguing that other database products use connection pooling to handle rapid connect/disconnect cycles and to throttle the number of simultaneous queries, but not to cope with the possibility of large numbers of idle sessions. My limited understanding of why PostgreSQL has a problem in this area is that it has to do with the size of the process array which must be scanned to derive an MVCC snapshot. I'd be curious to know if anyone thinks that's correct, or not. Assuming for the moment that it's correct, databases that don't use MVCC won't have this problem, but they give up a significant amount of scalability in other areas due to increased blocking (in particular, writers will block readers). So how do other databases that *do* use MVCC mitigate this problem? I apologize if it is bad form to respond to a message that is two months old, but I did not see this question answered elsewhere and thought it would be helpful to have it answered. This my rough understanding. Oracle never takes a snapshot, it computes one the fly, if and when it is needed. It maintains a structure of recently committed transactions, with the XID for when they committed. If a process runs into a tuple that is neither from the future nor from the deep past, it consults this structure to see if that transaction has committed, and if so whether it did so before or after the current query was started. The structure is partionable so it does not have one global lock to serialize on, and the lock is short as it only gets the info it needs, not the entire set of global open transactions. The only one that we've discussed here is Oracle, which seems to get around the problem by having a built-in connection pooler. There are several reasons to have something like Oracle's shared server (or whatever they call it now), and I don't think global serialization on snapshots is high among them, at least not for Oracle. With shared server, you can (theoretically) control memory usage so that 10,000 independent processes don't all decide to do a large in-memory sort or hash join at the same time. It is also a bit more than a standard connection pooler, because multiple connections can be in the middle of non-read-only transactions on the same backend at the same time. I don't think client-based pools allow that. Jeff -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Scalability in postgres
Jeff Janes jeff.ja...@gmail.com writes: I apologize if it is bad form to respond to a message that is two months old, but I did not see this question answered elsewhere and thought it would be helpful to have it answered. This my rough understanding. Oracle never takes a snapshot, it computes one the fly, if and when it is needed. It maintains a structure of recently committed transactions, with the XID for when they committed. If a process runs into a tuple that is neither from the future nor from the deep past, it consults this structure to see if that transaction has committed, and if so whether it did so before or after the current query was started. The structure is partionable so it does not have one global lock to serialize on, and the lock is short as it only gets the info it needs, not the entire set of global open transactions. Are you sure it's partitionable? I've been told that Oracle's transaction log is a serious scalability bottleneck. (But I think I first heard that in 2001, so maybe they've improved it in recent releases.) We know that Postgres' WAL log is a bottleneck --- check for recent discussions involving XLogInsert. But the WAL log is only touched by read-write transactions, whereas in Oracle even read-only transactions often have to go to the transaction log. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
