Need to calculate PostgreSQL memory usage
| От | HEARNE, TIMOTHY S |
|---|---|
| Тема | Need to calculate PostgreSQL memory usage |
| Дата | |
| Msg-id | 00CF4A25E32E204B8968E067CE2BB0D90254ACB2@CAFRFD1MSGUSRJB.ITServices.sbc.com обсуждение исходный текст |
| Ответы |
Re: Need to calculate PostgreSQL memory usage
(Tom Lane <tgl@sss.pgh.pa.us>)
|
| Список | pgsql-admin |
I am attempting to determine the amount of memory which is being used by my 2 PostgreSQL instances (8.4.1 and 9.2.10).
OS Version: Red Hat Enterprise Linux Server release 5.10 (Tikanga) (Linux xxxxxxx 2.6.18-371.11.1.el5 #1 SMP Mon Jun 30 04:51:39 EDT 2014 x86_64 x86_64 x86_64 GNU/Linux)
When I use the top utility, I have recently observed memory usage of up to 190 GB but I am being told by our Server support team that it never uses more than about 12 GB of computational memory (including memory used by a small Oracle instance). I have read on-line that because PostgreSQL uses shared memory that top (and pmap) tends to overstate the actual memory in use.
top - 09:47:30 up 330 days, 23:12, 1 user, load average: 2.79, 3.37, 3.14
Tasks: 1525 total, 3 running, 1520 sleeping, 0 stopped, 2 zombie
Cpu(s): 3.0%us, 0.3%sy, 0.0%ni, 96.4%id, 0.4%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 264114472k total, 193001540k used, 71112932k free, 497484k buffers
Swap: 16779852k total, 697304k used, 16082548k free, 179534676k cached
Using the information found in Table 17-2. PostgreSQL Shared Memory Usage (http://www.postgresql.org/docs/9.2/interactive/kernel-resources.html), I have calculated the following for the instances:
8.4.1 instance:
| Usage | Approximate shared memory bytes required (as of 8.3) | megs | Observed | Observed (megs) | |
| Connections | (1800 + 270 * max_locks_per_transaction) * max_connections | 332,316,000 | 316.92 | ||
| Autovacuum workers | (1800 + 270 * max_locks_per_transaction) * autovacuum_max_workers | 3,323,160 | 3.17 | ||
| Prepared transactions | (770 + 270 * max_locks_per_transaction) * max_prepared_transactions | - | - | ||
| Shared disk buffers | (block_size + 208) * shared_buffers | 1,033,476,505,600 | 985,600.00 | 102,400.00 | 0.0977 |
| WAL buffers | (wal_block_size + 8) * wal_buffers | 16,777,216.00 | 16.00 | 2,048.00 | 0.0020 |
| Fixed space requirements | 770 *1024 | 788,480 | 0.75 | ||
| 1,033,494,071,296 | 985,616.75 | ||||
| Variables | |||||
| max_locks_per_transaction | 4096 | ||||
| Max_connections | 300 | ||||
| autovacuum_max_workers | 3 | ||||
| max_prepared_transactions | 0 | 0 means not used | |||
| block_size | 1024 | ||||
| shared_buffers | 838860800 | ||||
| wal_block_size | 8192 | ||||
| wal_buffers | 16777216 | ||||
| work_mem | 16777216 |
9.2.10 instance:
| Usage | Approximate shared memory bytes required (as of 8.3) | megs | Observed | Observed (megs) | |
| Connections | (1800 + 270 * max_locks_per_transaction) * max_connections | 332,316,000 | 316.92 | ||
| Autovacuum workers | (1800 + 270 * max_locks_per_transaction) * autovacuum_max_workers | 3,323,160 | 3.17 | ||
| Prepared transactions | (770 + 270 * max_locks_per_transaction) * max_prepared_transactions | - | - | ||
| Shared disk buffers | (block_size + 208) * shared_buffers | 2,645,699,854,336 | 2,523,136.00 | 262,144.00 | 0.2500 |
| WAL buffers | (wal_block_size + 8) * wal_buffers | 16,777,216.00 | 16.00 | 2,048.00 | 0.0020 |
| Fixed space requirements | 770 *1024 | 788,480 | 0.75 | ||
| 2,645,717,420,032 | 2,523,152.75 | ||||
| Variables | |||||
| max_locks_per_transaction | 4096 | ||||
| Max_connections | 300 | ||||
| autovacuum_max_workers | 3 | ||||
| max_prepared_transactions | 0 | 0 means not used | |||
| block_size | 1024 | ||||
| shared_buffers | 2147483648 | ||||
| wal_block_size | 8192 | 4.1015625 | |||
| wal_buffers | 16777216 | ||||
| work_mem | 67108864 |
--------------------
The observed values above for Shared disk buffers and WAL Buffers were captured using the “Current Value” in the Backend Configuration Editor of the pgAdmin III (v1.18) tool. They are a point in time value.
I question the calculation for Shared disk buffers as it is way too high. I saw another post stating the calculation could be off by the block size. If that is the case, the displayed values above would be divided by 1024. In the postgresql.conf files, the shared_buffer values were entered as 800MB and 2048MB.
The small Oracle mentioned above has the following SGA:
| Fixed Size | 2,254,952 |
| Variable Size | 1,392,510,872 |
| Database Buffers | 704,643,072 |
| Redo Buffers | 38,477,824 |
I have attempted to download a couple of different tools from the PostgreSQL site. One would not compile on our server, pg_top-3.7.0, and another was targeted for PostgreSQL 9.3 and later.
I have also used the solutions at http://www.depesz.com/2012/06/09/how-much-ram-is-postgresql-using/.
The original solution was:
ps -u <USERID> o pid= | sed 's#.*#/proc/&/smaps#' | xargs grep ^Pss: | awk '{A+=$2} END{print A}'
where <USERID> is the ID under which the PostgreSQL processes run
I removed the sudo before grep as I don’t have root on the server
In the feedback, a revised version of the solution was:
ps -u <USERID> o pid= | sed 's#[0-9]\+#/proc/&/smaps#' | sort | xargs grep ^Pss: | awk '{A+=$2} END{print A}'
where <USERID> is the ID under which the PostgreSQL processes run
Both solutions seem to support what our server support team is telling us.
Bottom line, does it seem reasonable based upon the above that the server could only be using around 12 GB of memory? Is there a better way to determine the actual memory being used by the instances?
Thank you,
Tim Hearne
PROPRIETARY INFORMATION
The Information contained herein is for use only by authorized employees of AT&T, and authorized
The Information contained herein is for use only by authorized employees of AT&T, and authorized
Affiliates of AT&T, and is not for general distribution within or outside the respective companies
В списке pgsql-admin по дате отправления: