Thread: Intermittent aggressive use of SWAP space by PostgreSQL despite availability of HUGE amounts of RAM for a small database.
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)
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 freeRAM. We have a 100 GB database with 256 GB RAM on the server so there is no shortage of RAM nor shortage in PostgreSQLbuffer 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
RE: Intermittent aggressive use of SWAP space by PostgreSQL despite availability of HUGE amounts of RAM for a small database.
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 smalldatabase. Hi, > We have been seeing intermittent aggressive use of SWAP space by PostgreSQL despite availability of HUGE amounts of freeRAM. We have a 100 GB database with 256 GB RAM on the server so there is no shortage of RAM nor shortage in PostgreSQLbuffer 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
Attachment
Re: Intermittent aggressive use of SWAP space by PostgreSQL despite availability of HUGE amounts of RAM for a small database.
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
Re: Intermittent aggressive use of SWAP space by PostgreSQL despite availability of HUGE amounts of RAM for a small database.
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
RE: Intermittent aggressive use of SWAP space by PostgreSQL despite availability of HUGE amounts of RAM for a small database.
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
Re: Intermittent aggressive use of SWAP space by PostgreSQL despite availability of HUGE amounts of RAM for a small database.
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
Re: Intermittent aggressive use of SWAP space by PostgreSQL despite availability of HUGE amounts of RAM for a small database.
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
freeRAM. We have a 100 GB database with 256 GB RAM on the server so there is no shortage of RAM nor shortage in
PostgreSQLbuffer 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.