pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedvacuumdb causes memory drain.
9+ messages / 3 participants
[nested] [flat]
* vacuumdb causes memory drain.
@ 2018-04-26 15:31 Sushil Shirodkar <sushilps@hotmail.com>
2018-04-26 15:33 ` Re: vacuumdb causes memory drain. Don Seiler <don@seiler.us>
0 siblings, 1 reply; 9+ messages in thread
From: Sushil Shirodkar @ 2018-04-26 15:31 UTC (permalink / raw)
To: pgsql-admin
Hello Gurus,
Looking for some help, and if anybody has come across this type of scenario.
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.
O/S: Ubuntu VERSION="16.04.4 LTS (Xenial Xerus)"
PG : PostgreSQL 9.5.12
CPUs: 2
Memory:4GB
Swap:4GB
max_connections = 10
shared_buffers = 1GB
effective_cache_size = 3GB
maintenance_work_mem = 256MB
min_wal_size = 2GB
max_wal_size = 4GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 4
effective_io_concurrency = 2
max_worker_processes = 2
work_mem = 104857kB
Thanks,
Sushil...
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: vacuumdb causes memory drain.
2018-04-26 15:31 vacuumdb causes memory drain. Sushil Shirodkar <sushilps@hotmail.com>
@ 2018-04-26 15:33 ` Don Seiler <don@seiler.us>
2018-04-26 15:47 ` Re: vacuumdb causes memory drain. Sushil Shirodkar <sushilps@hotmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Don Seiler @ 2018-04-26 15:33 UTC (permalink / raw)
To: Sushil Shirodkar <sushilps@hotmail.com>; +Cc: pgsql-admin
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.
--
Don Seiler
www.seiler.us
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: vacuumdb causes memory drain.
2018-04-26 15:31 vacuumdb causes memory drain. Sushil Shirodkar <sushilps@hotmail.com>
2018-04-26 15:33 ` Re: vacuumdb causes memory drain. Don Seiler <don@seiler.us>
@ 2018-04-26 15:47 ` Sushil Shirodkar <sushilps@hotmail.com>
2018-04-27 06:06 ` Re: vacuumdb causes memory drain. Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 9+ messages in thread
From: Sushil Shirodkar @ 2018-04-26 15:47 UTC (permalink / raw)
To: Don Seiler <don@seiler.us>; +Cc: pgsql-admin
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.
________________________________
From: Don Seiler <don@seiler.us>
Sent: Thursday, April 26, 2018 11:33 AM
To: Sushil Shirodkar
Cc: pgsql-admin@postgresql.org
Subject: Re: vacuumdb causes memory drain.
On Thu, Apr 26, 2018 at 10:31 AM, Sushil Shirodkar <sushilps@hotmail.com<mailto: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.
--
Don Seiler
www.seiler.us<http://www.seiler.us;
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: vacuumdb causes memory drain.
2018-04-26 15:31 vacuumdb causes memory drain. Sushil Shirodkar <sushilps@hotmail.com>
2018-04-26 15:33 ` Re: vacuumdb causes memory drain. Don Seiler <don@seiler.us>
2018-04-26 15:47 ` Re: vacuumdb causes memory drain. Sushil Shirodkar <sushilps@hotmail.com>
@ 2018-04-27 06:06 ` Laurenz Albe <laurenz.albe@cybertec.at>
2018-04-27 10:40 ` Re: vacuumdb causes memory drain. Sushil Shirodkar <sushilps@hotmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Laurenz Albe @ 2018-04-27 06:06 UTC (permalink / raw)
To: Sushil Shirodkar <sushilps@hotmail.com>; Don Seiler <don@seiler.us>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: vacuumdb causes memory drain.
2018-04-26 15:31 vacuumdb causes memory drain. Sushil Shirodkar <sushilps@hotmail.com>
2018-04-26 15:33 ` Re: vacuumdb causes memory drain. Don Seiler <don@seiler.us>
2018-04-26 15:47 ` Re: vacuumdb causes memory drain. Sushil Shirodkar <sushilps@hotmail.com>
2018-04-27 06:06 ` Re: vacuumdb causes memory drain. Laurenz Albe <laurenz.albe@cybertec.at>
@ 2018-04-27 10:40 ` Sushil Shirodkar <sushilps@hotmail.com>
2018-04-27 12:39 ` Re: vacuumdb causes memory drain. Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 9+ messages in thread
From: Sushil Shirodkar @ 2018-04-27 10:40 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Don Seiler <don@seiler.us>; pgsql-admin
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: vacuumdb causes memory drain.
2018-04-26 15:31 vacuumdb causes memory drain. Sushil Shirodkar <sushilps@hotmail.com>
2018-04-26 15:33 ` Re: vacuumdb causes memory drain. Don Seiler <don@seiler.us>
2018-04-26 15:47 ` Re: vacuumdb causes memory drain. Sushil Shirodkar <sushilps@hotmail.com>
2018-04-27 06:06 ` Re: vacuumdb causes memory drain. Laurenz Albe <laurenz.albe@cybertec.at>
2018-04-27 10:40 ` Re: vacuumdb causes memory drain. Sushil Shirodkar <sushilps@hotmail.com>
@ 2018-04-27 12:39 ` Laurenz Albe <laurenz.albe@cybertec.at>
2018-04-30 01:23 ` Re: vacuumdb causes memory drain. Sushil Shirodkar <sushilps@hotmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Laurenz Albe @ 2018-04-27 12:39 UTC (permalink / raw)
To: Sushil Shirodkar <sushilps@hotmail.com>; +Cc: Don Seiler <don@seiler.us>; pgsql-admin
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: vacuumdb causes memory drain.
2018-04-26 15:31 vacuumdb causes memory drain. Sushil Shirodkar <sushilps@hotmail.com>
2018-04-26 15:33 ` Re: vacuumdb causes memory drain. Don Seiler <don@seiler.us>
2018-04-26 15:47 ` Re: vacuumdb causes memory drain. Sushil Shirodkar <sushilps@hotmail.com>
2018-04-27 06:06 ` Re: vacuumdb causes memory drain. Laurenz Albe <laurenz.albe@cybertec.at>
2018-04-27 10:40 ` Re: vacuumdb causes memory drain. Sushil Shirodkar <sushilps@hotmail.com>
2018-04-27 12:39 ` Re: vacuumdb causes memory drain. Laurenz Albe <laurenz.albe@cybertec.at>
@ 2018-04-30 01:23 ` Sushil Shirodkar <sushilps@hotmail.com>
2018-04-30 05:43 ` Re: vacuumdb causes memory drain. Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 9+ messages in thread
From: Sushil Shirodkar @ 2018-04-30 01:23 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: don@seiler.us <don@seiler.us>; pgsql-admin
> 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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: vacuumdb causes memory drain.
2018-04-26 15:31 vacuumdb causes memory drain. Sushil Shirodkar <sushilps@hotmail.com>
2018-04-26 15:33 ` Re: vacuumdb causes memory drain. Don Seiler <don@seiler.us>
2018-04-26 15:47 ` Re: vacuumdb causes memory drain. Sushil Shirodkar <sushilps@hotmail.com>
2018-04-27 06:06 ` Re: vacuumdb causes memory drain. Laurenz Albe <laurenz.albe@cybertec.at>
2018-04-27 10:40 ` Re: vacuumdb causes memory drain. Sushil Shirodkar <sushilps@hotmail.com>
2018-04-27 12:39 ` Re: vacuumdb causes memory drain. Laurenz Albe <laurenz.albe@cybertec.at>
2018-04-30 01:23 ` Re: vacuumdb causes memory drain. Sushil Shirodkar <sushilps@hotmail.com>
@ 2018-04-30 05:43 ` Laurenz Albe <laurenz.albe@cybertec.at>
2018-04-30 15:03 ` Re: vacuumdb causes memory drain. Sushil Shirodkar <sushilps@hotmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Laurenz Albe @ 2018-04-30 05:43 UTC (permalink / raw)
To: Sushil Shirodkar <sushilps@hotmail.com>; +Cc: don@seiler.us <don@seiler.us>; pgsql-admin
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: vacuumdb causes memory drain.
2018-04-26 15:31 vacuumdb causes memory drain. Sushil Shirodkar <sushilps@hotmail.com>
2018-04-26 15:33 ` Re: vacuumdb causes memory drain. Don Seiler <don@seiler.us>
2018-04-26 15:47 ` Re: vacuumdb causes memory drain. Sushil Shirodkar <sushilps@hotmail.com>
2018-04-27 06:06 ` Re: vacuumdb causes memory drain. Laurenz Albe <laurenz.albe@cybertec.at>
2018-04-27 10:40 ` Re: vacuumdb causes memory drain. Sushil Shirodkar <sushilps@hotmail.com>
2018-04-27 12:39 ` Re: vacuumdb causes memory drain. Laurenz Albe <laurenz.albe@cybertec.at>
2018-04-30 01:23 ` Re: vacuumdb causes memory drain. Sushil Shirodkar <sushilps@hotmail.com>
2018-04-30 05:43 ` Re: vacuumdb causes memory drain. Laurenz Albe <laurenz.albe@cybertec.at>
@ 2018-04-30 15:03 ` Sushil Shirodkar <sushilps@hotmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Sushil Shirodkar @ 2018-04-30 15:03 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: don@seiler.us <don@seiler.us>; pgsql-admin
________________________________
From: Laurenz Albe <laurenz.albe@cybertec.at>
Sent: Monday, April 30, 2018 1:43 AM
To: Sushil Shirodkar
Cc: don@seiler.us; pgsql-admin@postgresql.org
Subject: Re: 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.
>
> 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
[https://www.cybertec-postgresql.com/wp-content/uploads/2016/10/cybertec-header-startseite.jpg]<https://www.cybertec-postgresql.com/;
Startseite - Cybertec<https://www.cybertec-postgresql.com/;
www.cybertec-postgresql.com
The PostgreSQL Company CYBERTEC – Ihr professioneller PostgreSQL-Partner für Support, Services, Updates und individuelle Schulungen. CYBERTEC PostgreSQL Services CYBERTEC PostgreSQL Trainings und Kurse CYBERTEC PostgreSQL Applikationen Durch unsere jahrelange Erfahrung mit PostgreSQL haben wir bereits eine Vielzahl an Applikationen ...
Hi Laurenz,
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, after running the following our application performance would get back to normal.
sync; echo 1 > /proc/sys/vm/drop_caches
Let us know if we should run any other commands to capture the output, if it reoccurs.
Thanks to you and Don for your input and help on this issue.
Rgs,
Sushil Shirodkar...
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2018-04-30 15:03 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-04-26 15:31 vacuumdb causes memory drain. Sushil Shirodkar <sushilps@hotmail.com>
2018-04-26 15:33 ` Don Seiler <don@seiler.us>
2018-04-26 15:47 ` Sushil Shirodkar <sushilps@hotmail.com>
2018-04-27 06:06 ` Laurenz Albe <laurenz.albe@cybertec.at>
2018-04-27 10:40 ` Sushil Shirodkar <sushilps@hotmail.com>
2018-04-27 12:39 ` Laurenz Albe <laurenz.albe@cybertec.at>
2018-04-30 01:23 ` Sushil Shirodkar <sushilps@hotmail.com>
2018-04-30 05:43 ` Laurenz Albe <laurenz.albe@cybertec.at>
2018-04-30 15:03 ` Sushil Shirodkar <sushilps@hotmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox