Re: vacuumdb causes memory drain.

[Sushil Shirodkar <sushilps@xxxxxxxxxxx>]

---

From: Tim Cross <theophilusx@xxxxxxxxx>
Sent: Monday, April 30, 2018 11:59 PM
To: Laurenz Albe
Cc: Sushil Shirodkar; don@xxxxxxxxx; pgsql-admin@xxxxxxxxxxxxxx
Subject: Re: vacuumdb causes memory drain.

 

On 1 May 2018 at 13:05, Laurenz Albe <laurenz.albe@xxxxxxxxxxx> wrote:

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

as another indicator that your not low on memory look at the 'swap' line. The kernel has not swapped anything out of main memory, so while the 'free' value might look low, there has been no demand for memory which exceeds what you have available. 

I too find the fact clearing out caches seems to improve performance - that seems counter to what I'd expect too.  What filesystem type is the server using for PG?

--

regards,

Tim

--

Tim Cross

Laurenz -  So far no issue seen after reboot,  will capture the outputs you recommended if this reoccurs. 

Tim - We are using ext4 filesystem on this server. 

 

Thanks both for your help. 

Rgs,

Sushil...

Thanks both for your help.