Обсуждение: Memory use

Поиск
Список
Период
Сортировка

Memory use

От
"HEARNE, TIMOTHY S"
Дата:
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 top, 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:
UsageApproximate shared memory bytes required (as of 8.3)megsObservedObserved (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 requirements770 *1024                             788,480                     0.75
         1,033,494,071,296        985,616.75
Variables
max_locks_per_transaction4096
Max_connections300
autovacuum_max_workers3
max_prepared_transactions0 0 means not used
block_size1024
shared_buffers838860800
wal_block_size8192
wal_buffers16777216
work_mem16777216
 
 
9.2.10 instance:
UsageApproximate shared memory bytes required (as of 8.3)megsObservedObserved (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 requirements770 *1024                             788,480                     0.75
         2,645,717,420,032    2,523,152.75
Variables
max_locks_per_transaction4096
Max_connections300
autovacuum_max_workers3
max_prepared_transactions0 0 means not used
block_size1024
shared_buffers2147483648
wal_block_size81924.1015625
wal_buffers16777216
work_mem67108864
 
--------------------
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
Bottom line, does it seem reasonable based upon the above that the server could only be using around 12 GB of memory?  Is there another 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
Affiliates of AT&T, and is not for general distribution within or outside the respective companies
 
 
 

Re: Memory use

От
"HEARNE, TIMOTHY S"
Дата:

This is my second plea for assistance.  Can anyone assist me?  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.

If you have any questions, please feel free to contact me.

Tim Hearne
CAST Application DBA
Principal DBA

Centralized Development
AT&T Services, Inc.
e-mail:  timothy.hearne@att.com
http://intranet.att.com/its/cdtworx/content.cfm/home/

Agile Bronze Certified

AT&T CAST team mailbox:  g01494@att.com

PROPRIETARY INFORMATION
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

 

From: pgadmin-support-owner@postgresql.org [mailto:pgadmin-support-owner@postgresql.org] On Behalf Of HEARNE, TIMOTHY S
Sent: Thursday, September 10, 2015 12:42 PM
To: pgadmin-support@postgresql.org
Cc: ANDERSON, RICHARD W; HIGGINS, BRIAN; Anil Kumar (a.kumar@castsoftware.com); WICKE, SCOTT W; Aditya Pai
Subject: [pgadmin-support] Memory use

 

***Security Advisory: This Message Originated Outside of AT&T ***
Reference http://cso.att.com/EmailSecurity/IDSP.html for more information.

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 top, 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

Bottom line, does it seem reasonable based upon the above that the server could only be using around 12 GB of memory?  Is there another 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

Affiliates of AT&T, and is not for general distribution within or outside the respective companies

 

 

 

Re: Memory use

От
Michael Rasmussen
Дата:
Hi Tim -

This mailing list is for support with the pgAdmin III gui tool, not for PostgreSQL server administration questions. Better help with your questions regarding memory usage is available in the PostgreSQL IRC channel, details at http://www.postgresql.org/community/irc/, or one of the PostgreSQL mailing lists at http://www.postgresql.org/list/.

-- 
Michael Rasmussen
Sr. Data Engineer
Porch

From: <pgadmin-support-owner@postgresql.org> on behalf of "HEARNE, TIMOTHY S"
Date: Tuesday, September 15, 2015 at 8:28 AM
To: "pgadmin-support@postgresql.org"
Cc: "ANDERSON, RICHARD W", "HIGGINS, BRIAN", "Anil Kumar (a.kumar@castsoftware.com)", "WICKE, SCOTT W", Aditya Pai
Subject: Re: [pgadmin-support] Memory use

This is my second plea for assistance.  Can anyone assist me?  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.

If you have any questions, please feel free to contact me.

Tim Hearne
CAST Application DBA
Principal DBA

Centralized Development
AT&T Services, Inc.
e-mail:  timothy.hearne@att.com
http://intranet.att.com/its/cdtworx/content.cfm/home/

Agile Bronze Certified

AT&T CAST team mailbox:  g01494@att.com

PROPRIETARY INFORMATION
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

 

From: pgadmin-support-owner@postgresql.org [mailto:pgadmin-support-owner@postgresql.org] On Behalf Of HEARNE, TIMOTHY S
Sent: Thursday, September 10, 2015 12:42 PM
To: pgadmin-support@postgresql.org
Cc: ANDERSON, RICHARD W; HIGGINS, BRIAN; Anil Kumar (a.kumar@castsoftware.com); WICKE, SCOTT W; Aditya Pai
Subject: [pgadmin-support] Memory use

 

***Security Advisory: This Message Originated Outside of AT&T ***
Reference http://cso.att.com/EmailSecurity/IDSP.html for more information.

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 top, 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

Bottom line, does it seem reasonable based upon the above that the server could only be using around 12 GB of memory?  Is there another 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

Affiliates of AT&T, and is not for general distribution within or outside the respective companies

 

 

 

Re: Memory use

От
Fernando Hevia
Дата:


On Tue, Sep 15, 2015 at 12:28 PM, HEARNE, TIMOTHY S <th1618@att.com> wrote:

This is my second plea for assistance.  Can anyone assist me?  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.

If you have any questions, please feel free to contact me.


Tim, by looking at the top output:

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 

I agree with your server guy in that the apps running on this server (including postgres) use about 14 GB RAM. The rest is used for caching filesystem's data.
The rough but simple math here is Mem used + Swap used - cached: 193 + ,7 - 179.5 = 14.2 GB 

I assume you want postgres to use more RAM directly (you should know it benefits a lot from the Linux disk cache but at a greater overhead cost).
Please, don't follow up here. Ask in the postgres Administration list and someone will be able to explain why your postgres DB configuration uses this amount of RAM.

Fernando.