Thanks a lot Justin,
I used the link that you shared and I noticed that in most cases, when I was simulating the issue with simple SQL's, most of the RSS was actually shared. e.g. from /proc/pid/status RssAnon: 8672 kB RssFile: 4576 kB RssShmem: 4596656 kB And when I looked at /proc/pid/smaps Is so it as "Referenced" ( in /dev/zero (deleted) section ) Referenced: 4596624 kB And the change in server's free/available memory was not significant. But when running our application, the picture was different, most of it was Anon, and server's available memory was decreasing. from /proc/pid/status RssAnon: 14115188 kB RssFile: 4648 kB RssShmem: 282816 kB Eventually, we found out that the reason for this phenome is combination of tables with many partitions ( 2112 ) and specific SQL. We reduced the number of partitions from 2112 to 132 and the issue was resolved. It seems the PG is still struggling with tables with so many partitions. The application was written originally for Oracle, and these huge number of partition there was also abuse, but Oracle can handle it. Thanks, Shai -----Original Message----- From: Justin Pryzby <pry...@telsasoft.com> Sent: Wednesday, March 23, 2022 5:20 PM To: Shai Shapira <shai.shap...@amdocs.com> Cc: pgsql-performa...@postgresql.org Subject: Re: High process memory consumption when running sort CAUTION: This message was sent from outside of Amdocs. Please do not click links or open attachments unless you recognize the source of this email and know the content is safe. On Wed, Mar 23, 2022 at 02:42:06PM +0000, Shai Shapira wrote: > Hi, > > When running our application, we noticed that some processes are taking a lot > of memory ( 10, 15, 20GB or so, of RSS ). > It is also reproduced when running in psql. Note that RSS can include shared_buffers read by that backend. That's a linux behavior, not specific to postgres. It's what Andres was describing here: https://eur01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.postgresql.org%2Fmessage-id%2Fflat%2F20201003230149.mtd7fjsjwgii3jv7%40alap3.anarazel.de&data=04%7C01%7CShai.Shapira%40Amdocs.com%7C50cc35ffdc134d58920708da0ce0ba20%7Cc8eca3ca127646d59d9da0f2a028920f%7C0%7C0%7C637836456579176065%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=dEcakp0g6rUjw0aLhJJNnut4RhC7EQ0edRK%2FbzBzF%2F8%3D&reserved=0 You have effective_cache_size = 48GB, so this seems to be working as intended. (ecc is expected to include data cached not only by postgres but by the OS page cache, too). > Memory consumption: ( of case 2, application table, using system_stats > ) I'm not sure, but I guess this is just a postgres view of whatever the OS shows. > Using top: > PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND > 15298 postgres 20 0 16.8g 1.1g 1.1g S 0.0 1.7 0:02.63 postgres > PostgreSQL 12.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 > 20150623 (Red Hat 4.8.5-44), 64-bit Linux illin7504 > 3.10.0-1127.19.1.el7.x86_64 #1 SMP Tue Aug 11 19:12:04 EDT 2020 x86_64 > x86_64 x86_64 GNU/Linux > shared_buffers | configuration file | postmaster > | 2097152 | 8kB | 1024 > effective_cache_size | configuration file | user > | 6291456 | 8kB | 524288 > work_mem | configuration file | user > | 20480 | kB | 4096 This email and the information contained herein is proprietary and confidential and subject to the Amdocs Email Terms of Service, which you may review at https://www.amdocs.com/about/email-terms-of-service <https://www.amdocs.com/about/email-terms-of-service>
