Intermittent aggressive use of SWAP space by PostgreSQL despite availability of HUGE amounts of RAM for a small database.
We have been seeing intermittent aggressive use of SWAP space by PostgreSQL despite availability of HUGE amounts of free RAM. We have a 100 GB database with 256 GB RAM on the server so there is no shortage of RAM nor shortage in PostgreSQL buffer cache. Our swappiness setting is set to "1".
What else can we do as far as PostgreSQL settings are concerned to make sure no/minimal swap is used. SWAP space is set at 8 GB. Is there a BUG related to this for which there has been a fix. Making changes to the application is too big of an effort which we cannot undertake at the moment.
Our PostgreSQL environment is as follows ...
PostgreSQL 14.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-16), 64-bit
Red Hat environment details ...
NAME="Red Hat Enterprise Linux"
VERSION="8.8 (Ootpa)"
ID="rhel"
ID_LIKE="fedora"
VERSION_ID="8.8"
PLATFORM_ID="platform:el8"
PRETTY_NAME="Red Hat Enterprise Linux 8.8 (Ootpa)"
ANSI_COLOR="0;31"
CPE_NAME="cpe:/o:redhat:enterprise_linux:8::baseos"
HOME_URL=https://www.redhat.com/
DOCUMENTATION_URL=https://access.redhat.com/documentation/en-us/red_hat_enterprise_linux/8
BUG_REPORT_URL=https://bugzilla.redhat.com/
REDHAT_BUGZILLA_PRODUCT="Red Hat Enterprise Linux 8"
REDHAT_BUGZILLA_PRODUCT_VERSION=8.8
REDHAT_SUPPORT_PRODUCT="Red Hat Enterprise Linux"
REDHAT_SUPPORT_PRODUCT_VERSION="8.8"
Hardware details ...
[root@newdb ~]# lshw -short
H/W path Device Class Description
============================================================
system PowerEdge R750 (SKU=090E;ModelName=PowerEdge R750)
/0 bus 01J4WF
/0/1 memory 64KiB BIOS
/0/400 processor Intel(R) Xeon(R) Gold 6346 CPU @ 3.10GHz
/0/400/700 memory 1280KiB L1 cache
/0/400/701 memory 20MiB L2 cache
/0/400/702 memory 36MiB L3 cache
/0/401 processor Intel(R) Xeon(R) Gold 6346 CPU @ 3.10GHz
/0/401/703 memory 1280KiB L1 cache
/0/401/704 memory 20MiB L2 cache
/0/401/705 memory 36MiB L3 cache
/0/1000 memory 256GiB System Memory
/0/1000/0 memory 32GiB DIMM DDR4 Synchronous Registered (Buffered) 3200 MHz (0.3 ns)
/0/1000/1 memory 32GiB DIMM DDR4 Synchronous Registered (Buffered) 3200 MHz (0.3 ns)
/0/1000/2 memory 32GiB DIMM DDR4 Synchronous Registered (Buffered) 3200 MHz (0.3 ns)
/0/1000/3 memory 32GiB DIMM DDR4 Synchronous Registered (Buffered) 3200 MHz (0.3 ns)
Hi,
We have been seeing intermittent aggressive use of SWAP space by PostgreSQL despite availability of HUGE amounts of free RAM. We have a 100 GB database with 256 GB RAM on the server so there is no shortage of RAM nor shortage in PostgreSQL buffer cache. Our swappiness setting is set to “1”.
[...]
Please provide your postgresql.conf.
Also, why do you need a swap on such a setup? I seriously doubt that
it serves any purpose.
--
Best regards,
Aleksander Alekseev
Configuration file is attached.
-----Original Message-----
From: Aleksander Alekseev <aleksander@timescale.com>
Sent: Tuesday, July 23, 2024 4:33 AM
To: pgsql-bugs@lists.postgresql.org
Cc: Mohammed Siddiqui <msiddiqui@net32.com>; Ken Sollars <Ken@net32.com>; Matt Elliott <melliott@net32.com>
Subject: Re: Intermittent aggressive use of SWAP space by PostgreSQL despite availability of HUGE amounts of RAM for a small database.
Hi,
We have been seeing intermittent aggressive use of SWAP space by PostgreSQL despite availability of HUGE amounts of free RAM. We have a 100 GB database with 256 GB RAM on the server so there is no shortage of RAM nor shortage in PostgreSQL buffer cache. Our swappiness setting is set to “1”.
[...]
Please provide your postgresql.conf.
Also, why do you need a swap on such a setup? I seriously doubt that it serves any purpose.
--
Best regards,
Aleksander Alekseev
Attachments:
Huge size of memory allocation and 3000 max_connection may caused swap
space issue.
2024년 7월 25일 (목) 오후 5:01, Mohammed Siddiqui <msiddiqui@net32.com>님이 작성:
Show quoted text
Configuration file is attached.
-----Original Message-----
From: Aleksander Alekseev <aleksander@timescale.com>
Sent: Tuesday, July 23, 2024 4:33 AM
To: pgsql-bugs@lists.postgresql.org
Cc: Mohammed Siddiqui <msiddiqui@net32.com>; Ken Sollars <Ken@net32.com>;
Matt Elliott <melliott@net32.com>
Subject: Re: Intermittent aggressive use of SWAP space by PostgreSQL
despite availability of HUGE amounts of RAM for a small database.Hi,
We have been seeing intermittent aggressive use of SWAP space by
PostgreSQL despite availability of HUGE amounts of free RAM. We have a 100
GB database with 256 GB RAM on the server so there is no shortage of RAM
nor shortage in PostgreSQL buffer cache. Our swappiness setting is set to
“1”.[...]
Please provide your postgresql.conf.
Also, why do you need a swap on such a setup? I seriously doubt that it
serves any purpose.--
Best regards,
Aleksander Alekseev
Hello Mohammed,
I reviewed your attached postgresql.conf file and observed some concerning
points. As you mentioned you have 256 GB RAM with 100 GB database size. In
your configuration file you use higher ranges as compared to your system
resources like ..
shared_buffers = 120000MB
...
work_mem = 2048MB
...
maintenance_work_mem = 2048MB
...
effective_cache_size = 120000MB
...
max_connection = 3000
The above configuration looks too much as per your available system
resources. It looks like you need some expert review to evaluate your
system resources and configure the server accordingly.
Thanks & Regards
Zaid
Bitnine Global Inc.
On Thu, Jul 25, 2024 at 5:01 PM Mohammed Siddiqui <msiddiqui@net32.com>
wrote:
Show quoted text
Configuration file is attached.
-----Original Message-----
From: Aleksander Alekseev <aleksander@timescale.com>
Sent: Tuesday, July 23, 2024 4:33 AM
To: pgsql-bugs@lists.postgresql.org
Cc: Mohammed Siddiqui <msiddiqui@net32.com>; Ken Sollars <Ken@net32.com>;
Matt Elliott <melliott@net32.com>
Subject: Re: Intermittent aggressive use of SWAP space by PostgreSQL
despite availability of HUGE amounts of RAM for a small database.Hi,
We have been seeing intermittent aggressive use of SWAP space by
PostgreSQL despite availability of HUGE amounts of free RAM. We have a 100
GB database with 256 GB RAM on the server so there is no shortage of RAM
nor shortage in PostgreSQL buffer cache. Our swappiness setting is set to
“1”.[...]
Please provide your postgresql.conf.
Also, why do you need a swap on such a setup? I seriously doubt that it
serves any purpose.--
Best regards,
Aleksander Alekseev
Zaid … Thanks for your feedback. Pls recommend exact changes so that we can try it out.
Regards.
Mohammed M Siddiqui
From: Zaid Shabbir <zaidshabbir@gmail.com>
Sent: Tuesday, July 30, 2024 9:16 AM
To: Mohammed Siddiqui <msiddiqui@net32.com>
Cc: Aleksander Alekseev <aleksander@timescale.com>; pgsql-bugs@lists.postgresql.org; Ken Sollars <Ken@net32.com>; Matt Elliott <melliott@net32.com>
Subject: Re: Intermittent aggressive use of SWAP space by PostgreSQL despite availability of HUGE amounts of RAM for a small database.
Hello Mohammed,
I reviewed your attached postgresql.conf file and observed some concerning points. As you mentioned you have 256 GB RAM with 100 GB database size. In your configuration file you use higher ranges as compared to your system resources like ..
shared_buffers = 120000MB
...
work_mem = 2048MB
...
maintenance_work_mem = 2048MB
...
effective_cache_size = 120000MB
...
max_connection = 3000
The above configuration looks too much as per your available system resources. It looks like you need some expert review to evaluate your system resources and configure the server accordingly.
Thanks & Regards
Zaid
Bitnine Global Inc.
On Thu, Jul 25, 2024 at 5:01 PM Mohammed Siddiqui <msiddiqui@net32.com<mailto:msiddiqui@net32.com>> wrote:
Configuration file is attached.
-----Original Message-----
From: Aleksander Alekseev <aleksander@timescale.com<mailto:aleksander@timescale.com>>
Sent: Tuesday, July 23, 2024 4:33 AM
To: pgsql-bugs@lists.postgresql.org<mailto:pgsql-bugs@lists.postgresql.org>
Cc: Mohammed Siddiqui <msiddiqui@net32.com<mailto:msiddiqui@net32.com>>; Ken Sollars <Ken@net32.com<mailto:Ken@net32.com>>; Matt Elliott <melliott@net32.com<mailto:melliott@net32.com>>
Subject: Re: Intermittent aggressive use of SWAP space by PostgreSQL despite availability of HUGE amounts of RAM for a small database.
Hi,
We have been seeing intermittent aggressive use of SWAP space by PostgreSQL despite availability of HUGE amounts of free RAM. We have a 100 GB database with 256 GB RAM on the server so there is no shortage of RAM nor shortage in PostgreSQL buffer cache. Our swappiness setting is set to “1”.
[...]
Please provide your postgresql.conf.
Also, why do you need a swap on such a setup? I seriously doubt that it serves any purpose.
--
Best regards,
Aleksander Alekseev
It depends on many factors as per the user environment but for your
information you can check following useful links
https://pgtune.leopard.in.ua
https://www.enterprisedb.com/postgres-tutorials/introduction-postgresql-performance-tuning-and-optimization
https://www.postgresql.org/docs/current/runtime-config.html
Thanks & Regards
Zaid
Bitnine Global Inc.
On Tue, Jul 30, 2024 at 6:35 PM Mohammed Siddiqui <msiddiqui@net32.com>
wrote:
Show quoted text
Zaid … Thanks for your feedback. Pls recommend exact changes so that we
can try it out.Regards.
Mohammed M Siddiqui
*From:* Zaid Shabbir <zaidshabbir@gmail.com>
*Sent:* Tuesday, July 30, 2024 9:16 AM
*To:* Mohammed Siddiqui <msiddiqui@net32.com>
*Cc:* Aleksander Alekseev <aleksander@timescale.com>;
pgsql-bugs@lists.postgresql.org; Ken Sollars <Ken@net32.com>; Matt
Elliott <melliott@net32.com>
*Subject:* Re: Intermittent aggressive use of SWAP space by PostgreSQL
despite availability of HUGE amounts of RAM for a small database.Hello Mohammed,
I reviewed your attached postgresql.conf file and observed some concerning
points. As you mentioned you have 256 GB RAM with 100 GB database size.
In your configuration file you use higher ranges as compared to your system
resources like ..shared_buffers = 120000MB
...
work_mem = 2048MB
...
maintenance_work_mem = 2048MB
...
effective_cache_size = 120000MB
...
max_connection = 3000
The above configuration looks too much as per your available system
resources. It looks like you need some expert review to evaluate your
system resources and configure the server accordingly.Thanks & Regards
Zaid
Bitnine Global Inc.
On Thu, Jul 25, 2024 at 5:01 PM Mohammed Siddiqui <msiddiqui@net32.com>
wrote:Configuration file is attached.
-----Original Message-----
From: Aleksander Alekseev <aleksander@timescale.com>
Sent: Tuesday, July 23, 2024 4:33 AM
To: pgsql-bugs@lists.postgresql.org
Cc: Mohammed Siddiqui <msiddiqui@net32.com>; Ken Sollars <Ken@net32.com>;
Matt Elliott <melliott@net32.com>
Subject: Re: Intermittent aggressive use of SWAP space by PostgreSQL
despite availability of HUGE amounts of RAM for a small database.Hi,
We have been seeing intermittent aggressive use of SWAP space by
PostgreSQL despite availability of HUGE amounts of free RAM. We have a 100
GB database with 256 GB RAM on the server so there is no shortage of RAM
nor shortage in PostgreSQL buffer cache. Our swappiness setting is set to
“1”.[...]
Please provide your postgresql.conf.
Also, why do you need a swap on such a setup? I seriously doubt that it
serves any purpose.--
Best regards,
Aleksander Alekseev
On Tue, Jul 23, 2024 at 11:33:05AM +0300, Aleksander Alekseev wrote:
Hi,
We have been seeing intermittent aggressive use of SWAP space by PostgreSQL despite availability of HUGE amounts of free RAM. We have a 100 GB database with 256 GB RAM on the server so there is no shortage of RAM nor shortage in PostgreSQL buffer cache. Our swappiness setting is set to “1”.
[...]Please provide your postgresql.conf.
I find running this query more helpful because it only shows non-default
settings, and potentially shows per user/database changes too:
-- show non-default server settings
SELECT name, current_setting(name), source
FROM pg_settings
WHERE source NOT IN ('default', 'override');
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Only you can decide what is important to you.