Обсуждение: vacuumdb causes memory drain.

Sushil Shirodkar wrote:

> > On Thu, Apr 26, 2018 at 10:31 AM, Sushil Shirodkar <sushilps@hotmail.com> wrote:
> > > Running "vacuumdb -a -z -v" from the cron on one of our test environment, and 
> > > noticed that memory of the server goes down from 3.4GB free to 150MB.  Once 
> > > the process is over, memory is not released, is it normal or something needs to be 
> > > changed ? also other processes start running slow afterwards due to low memory.  
> >
> > How are you measuring free memory? Memory might be listed in cached/buffers instead of
> > free but is still available. Although that wouldn't then explain other processes being slow.
>
> I have put some small script which runs in a loop with "free -h" command,
> while I am running "vacuumdb".  Once I clear by "sync" or bounce PG,
> everything runs normal afterward.

Then I would say everything is fine.
It is normal for a Linux system to have almost no free memory; the memory is used
for the file system cache.

Do you experience any problems, like reduced performance or high I/O?

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Performance issue.

Can I restrict memory usage at session level in the script where I run
Vacuum DB ?

> On Apr 27, 2018, at 2:06 AM, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
>
> Sushil Shirodkar wrote:
>
>>>> On Thu, Apr 26, 2018 at 10:31 AM, Sushil Shirodkar <sushilps@hotmail.com> wrote:
>>>> Running "vacuumdb -a -z -v" from the cron on one of our test environment, and
>>>> noticed that memory of the server goes down from 3.4GB free to 150MB.  Once
>>>> the process is over, memory is not released, is it normal or something needs to be
>>>> changed ? also other processes start running slow afterwards due to low memory.
>>>
>>> How are you measuring free memory? Memory might be listed in cached/buffers instead of
>>> free but is still available. Although that wouldn't then explain other processes being slow.
>>
>> I have put some small script which runs in a loop with "free -h" command,
>> while I am running "vacuumdb".  Once I clear by "sync" or bounce PG,
>> everything runs normal afterward.
>
> Then I would say everything is fine.
> It is normal for a Linux system to have almost no free memory; the memory is used
> for the file system cache.
>
> Do you experience any problems, like reduced performance or high I/O?
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com

Sushil Shirodkar wrote:
> > > > > On Thu, Apr 26, 2018 at 10:31 AM, Sushil Shirodkar <sushilps@hotmail.com> wrote:
> > > > > Running "vacuumdb -a -z -v" from the cron on one of our test environment, and 
> > > > > noticed that memory of the server goes down from 3.4GB free to 150MB.  Once 
> > > > > the process is over, memory is not released, is it normal or something needs to be 
> > > > > changed ? also other processes start running slow afterwards due to low memory.  
> > > > 
> > > > How are you measuring free memory? Memory might be listed in cached/buffers instead of
> > > > free but is still available. Although that wouldn't then explain other processes being slow.
> > > 
> > > I have put some small script which runs in a loop with "free -h" command,
> > > while I am running "vacuumdb".  Once I clear by "sync" or bounce PG,
> > > everything runs normal afterward.
> > 
> > Then I would say everything is fine.
> > It is normal for a Linux system to have almost no free memory; the memory is used
> > for the file system cache.
> > 
> > Do you experience any problems, like reduced performance or high I/O?
> > 
> Performance issue.
> 
> Can I restrict memory usage at session level in the script where I run
> Vacuum DB ?  

Could you please bottom-post?

Once vacuumdb is done, the associated backend process is gone too, so
it cannot consume any memory.

Try to figure out which process consumes your memory.

What do you get for free -h?

How did you configure shared_buffers and maintenance_work_mem?

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

> On Apr 27, 2018, at 8:39 AM, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
>
> Sushil Shirodkar wrote:
>>>>>> On Thu, Apr 26, 2018 at 10:31 AM, Sushil Shirodkar <sushilps@hotmail.com> wrote:
>>>>>> Running "vacuumdb -a -z -v" from the cron on one of our test environment, and
>>>>>> noticed that memory of the server goes down from 3.4GB free to 150MB.  Once
>>>>>> the process is over, memory is not released, is it normal or something needs to be
>>>>>> changed ? also other processes start running slow afterwards due to low memory.
>>>>>
>>>>> How are you measuring free memory? Memory might be listed in cached/buffers instead of
>>>>> free but is still available. Although that wouldn't then explain other processes being slow.
>>>>
>>>> I have put some small script which runs in a loop with "free -h" command,
>>>> while I am running "vacuumdb".  Once I clear by "sync" or bounce PG,
>>>> everything runs normal afterward.
>>>
>>> Then I would say everything is fine.
>>> It is normal for a Linux system to have almost no free memory; the memory is used
>>> for the file system cache.
>>>
>>> Do you experience any problems, like reduced performance or high I/O?
>>>
>> Performance issue.
>>
>> Can I restrict memory usage at session level in the script where I run
>> Vacuum DB ?
>
> Could you please bottom-post?
>
> Once vacuumdb is done, the associated backend process is gone too, so
> it cannot consume any memory.
>
> Try to figure out which process consumes your memory.
>
> What do you get for free -h?
>
> How did you configure shared_buffers and maintenance_work_mem?
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com

Sorry for the delayed response.

Process which was running close to 99% memory was
 "/usr/lib/postgresql/9.5/bin/postgres -D /var/lib/postgresql/9.5/main -c
config_file=/etc/postgresql/9.5/main/postgresql.conf"

free -h - was showing between 100-150 mb free.

shared_buffers = 1GB
maintenance_work_mem = 256MB

Sushil Shirodkar wrote:
> > > > > > > On Thu, Apr 26, 2018 at 10:31 AM, Sushil Shirodkar <sushilps@hotmail.com> wrote:
> > > > > > > Running "vacuumdb -a -z -v" from the cron on one of our test environment, and 
> > > > > > > noticed that memory of the server goes down from 3.4GB free to 150MB.  Once 
> > > > > > > the process is over, memory is not released, is it normal or something needs to be 
> > > > > > > changed ? also other processes start running slow afterwards due to low memory.  
>
> Process which was running close to 99% memory was
>  "/usr/lib/postgresql/9.5/bin/postgres -D /var/lib/postgresql/9.5/main -c
config_file=/etc/postgresql/9.5/main/postgresql.conf"
> 
> free -h - was showing between 100-150 mb free. 
> 
> shared_buffers = 1GB
> maintenance_work_mem = 256MB

Can you share the "free -h" output?

What do you get for
ps -p <postmaster PID> -o rss,trs,drs,vsz,%mem
if you substitute the postmaster's process ID?

If you say that "sync" restored normal behavior, are you implying that there
was a high I/O load?

Still trying to figure out what exactly you are experiencing.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Sushil Shirodkar wrote:
> After reboot of Linux server this morning, we couldn't reproduce the issue.  Not sure if this will come up 
> again, and if it is good news or not,  will monitor and update forum if it reoccurs.  
> 
> Pl. see the output below which I had captured earlier from  "free -h" before and after
> running of  "vacuumdb".
> 
>               total                 used         free          shared     buff/cache   available
> Mem:           3.7G        158M        3.2G         62M        385M             3.3G
> Swap:          3.9G          0B             3.9G
> 
>                      total        used           free           shared      buff/cache   available
> Mem:           3.7G        169M        138M        117M        3.4G              3.2G
> Swap:           3.9G          0B            3.9G
> 
> 
> Was running the following command when slowness was observed.  The only issue I would see is memory
> getting close to full

That is totally normal.

You can observe that in the second output all the memory is shown as "buff/cache",
that is, it is used for the file system cache rather than remaining unused.
This memory is available, there is no shortage.

This is the normal state for a Linux system with buffered I/O activity.

> after running the following  our application performance would get back to normal.
> 
> sync;  echo 1 > /proc/sys/vm/drop_caches

Now that is surprising.

Sure, after running this command  your memory will show up as "free" again,
but I'd expect performance to get worse because no more files are cached.

Next time you run into trouble, try the following commands:

vmstat 1
iostat -mNx 1

These should show you how your overall CPU, memory and I/O situation is.
Maybe you can find your performance bottleneck that way.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com