Welcome Serena Central users! CLICK HERE
The migration of the Serena Central community is currently underway. Be sure to read THIS MESSAGE to get your new login set up to access your account.
ScorpionSting Absent Member.
Absent Member.
516 views

MongoDB / PostgreSQL Compacting


Are there built in processes to 'VACUUM'
(http://www.postgresql.org/docs/9.1/static/sql-vacuum.html) PostgreSQL
and 'compact'
(http://docs.mongodb.org/master/reference/command/compact/) MongoDB
database? If so, how often are they set to run?

Having space issues:


Code:
--------------------
novell@******:/var/opt/novell/sentinel/3rdparty> du -h --max-depth=1
1.7M ./elasticsearch
8.0K ./activemq
2.3G ./jetty
209G ./postgresql
14G ./mongodb
224G .
--------------------


I've tweeked the Alert retention to be as small as I dare go, but the
alerts.* files don't appear to have decreased in size but the "All
Alerts" display only shows ~35.....so it appears it took the space but
never gave it back.

The PostgreSQL is the rpt tables, which is a little more difficult to
trim.


--
-"Also now available in 'G+'
(http://plus.google.com/+BenWalter-Kiwi) and 'Website'
(https://www.isam.kiwi/) format".- 😉
------------------------------------------------------------------------
ScorpionSting's Profile: https://forums.netiq.com/member.php?userid=469
View this thread: https://forums.netiq.com/showthread.php?t=54304


Visit my Website for links to Cool Solution articles.
0 Likes
4 Replies
Knowledge Partner Knowledge Partner
Knowledge Partner

Re: MongoDB / PostgreSQL Compacting

On 09/16/2015 09:14 PM, ScorpionSting wrote:
>
> Are there built in processes to 'VACUUM'
> (http://www.postgresql.org/docs/9.1/static/sql-vacuum.html) PostgreSQL
> and 'compact'


The PostgreSQL database automatically vacuums itself, as I recall, but I'm
not sure that it does a FULL (space returned to the OS) vacuum, mostly for
performance reasons. You can do it, of course, but be aware it's a bigger
vacuum than I think normally happens. In most environments returning
space to the OS, likely to just have it requested again, doesn't make much
sense, especially considering the performance impact and the exclusive
lock as a table is vacuumed.

Also, keep in mind that some patches update the PostgreSQL code, so if you
have had Sentinel SPs do that you may want to go and see if any of the
directories under your database_files (going from memory) directory are
redundant. There is a script now, I believe, that will clean up old
databases that are duplicates created when upgrading Sentinel, but
basically you just need to find the old directory and delete it. Since
this is a copy of the database at patch time, it can be huge, and is (if
the patch works properly with Sentinel fully starting again) completely
redundant.

> (http://docs.mongodb.org/master/reference/command/compact/) MongoDB
> database? If so, how often are they set to run?


Again, I doubt it. The last time I checked, when Sentinel is freshly
installed it allocates 3 GiB to MongoDB, even before any data are put in
there, and leaves that there as a primarily-empty file to be filled as
time goes on.

> I've tweeked the Alert retention to be as small as I dare go, but the
> alerts.* files don't appear to have decreased in size but the "All
> Alerts" display only shows ~35.....so it appears it took the space but
> never gave it back.
>
> The PostgreSQL is the rpt tables, which is a little more difficult to
> trim.


Be sure that your data synchronization bits are set properly. A few years
ago some of the RDDs were defined to send all events to the database
unnecessarily. Combining that with the backup databases that can be made
during Sentinel patches makes for a lot of wasted space, and that could
still apply to you. Sending events to the database, which has its own
retention policy defined per RDD, just burns space and cycles unnecessarily.

--
Good luck.

If you find this post helpful and are logged into the web interface,
show your appreciation and click on the star below...
0 Likes
ScorpionSting Absent Member.
Absent Member.

Re: MongoDB / PostgreSQL Compacting


Yeah, I'd already cleaned the backup directories out....

I can't actually VACUUM PostgreSQL at the moment as there isn't enough
free space for VACUUM to duplicate the database..... I had ripped out as
many of the rpt sync as I could, and have validated the retention time
for those syncs....we're using IdT so that kind of rpt requirements just
blows PostgreSQL space into the giggity giggity bytes.

Mongo on the other hand is just wasting space..... As I'd said, I'd
changed the retention so the space *should* be freed as it *shouldn't*
be required again.... The 3GB preallocation is in the journal directory,
but as you can see from below, the previous alert retention policy has
exploded those alerts.* files (5G and not reduced them again:


Code:
--------------------
:/var/opt/novell/sentinel/3rdparty/mongodb/data # ls -alh *
-rw------- 1 novell novell 64M Aug 20 14:06 admin.0
-rw------- 1 novell novell 128M Jul 28 14:45 admin.1
-rw------- 1 novell novell 16M Jul 28 14:48 admin.ns
-rw------- 1 novell novell 64M Sep 18 07:54 alerts.0
-rw------- 1 novell novell 128M Sep 18 07:54 alerts.1
-rw------- 1 novell novell 256M Aug 28 19:02 alerts.2
-rw------- 1 novell novell 512M Sep 18 07:54 alerts.3
-rw------- 1 novell novell 1.0G Sep 12 11:28 alerts.4
-rw------- 1 novell novell 2.0G Sep 18 07:54 alerts.5
-rw------- 1 novell novell 2.0G Sep 12 11:28 alerts.6
-rw------- 1 novell novell 16M Sep 18 07:54 alerts.ns
-rw------- 1 novell novell 64M Aug 20 14:06 analytics.0
-rw------- 1 novell novell 128M Jul 28 14:44 analytics.1
-rw------- 1 novell novell 16M Aug 20 13:24 analytics.ns
-rw------- 1 novell novell 64M Sep 18 07:58 local.0
-rw------- 1 novell novell 2.0G Sep 18 07:54 local.1
-rw------- 1 novell novell 2.0G Sep 17 17:17 local.2
-rw------- 1 novell novell 16M Sep 18 07:58 local.ns
-rwxr-xr-x 1 novell novell 5 Sep 18 07:58 mongod.lock

journal:
total 3.1G
drwx------ 2 novell novell 4.0K Sep 18 07:58 .
drwx------ 3 novell novell 4.0K Sep 12 17:02 ..
-rw------- 1 novell novell 1.0G Sep 18 07:58 j._0
-rwx------ 1 novell novell 1.0G Jul 12 2011 prealloc.1
-rwx------ 1 novell novell 1.0G Jul 12 2011 prealloc.2
--------------------


I wish I'd had a better understanding of the /var/opt/novell/sentinel
structure when I built the appliance.... I gave /var/opt/novell its own
partition, but if I could do it over again I would have split
/var/opt/novell/sentinel/3rdparty and /var/opt/novell/sentinel/data into
their own so I could control the disk allocation a little bit better
without affecting its counterpart....

ab;260863 Wrote:
> On 09/16/2015 09:14 PM, ScorpionSting wrote:
> >
> > Are there built in processes to 'VACUUM'
> > (http://www.postgresql.org/docs/9.1/static/sql-vacuum.html) PostgreSQL
> > and 'compact'

>
> The PostgreSQL database automatically vacuums itself, as I recall, but
> I'm
> not sure that it does a FULL (space returned to the OS) vacuum, mostly
> for
> performance reasons. You can do it, of course, but be aware it's a
> bigger
> vacuum than I think normally happens. In most environments returning
> space to the OS, likely to just have it requested again, doesn't make
> much
> sense, especially considering the performance impact and the exclusive
> lock as a table is vacuumed.
>
> Also, keep in mind that some patches update the PostgreSQL code, so if
> you
> have had Sentinel SPs do that you may want to go and see if any of the
> directories under your database_files (going from memory) directory are
> redundant. There is a script now, I believe, that will clean up old
> databases that are duplicates created when upgrading Sentinel, but
> basically you just need to find the old directory and delete it. Since
> this is a copy of the database at patch time, it can be huge, and is (if
> the patch works properly with Sentinel fully starting again) completely
> redundant.
>
> > (http://docs.mongodb.org/master/reference/command/compact/) MongoDB
> > database? If so, how often are they set to run?

>
> Again, I doubt it. The last time I checked, when Sentinel is freshly
> installed it allocates 3 GiB to MongoDB, even before any data are put in
> there, and leaves that there as a primarily-empty file to be filled as
> time goes on.
>
> > I've tweeked the Alert retention to be as small as I dare go, but the
> > alerts.* files don't appear to have decreased in size but the "All
> > Alerts" display only shows ~35.....so it appears it took the space but
> > never gave it back.
> >
> > The PostgreSQL is the rpt tables, which is a little more difficult to
> > trim.

>
> Be sure that your data synchronization bits are set properly. A few
> years
> ago some of the RDDs were defined to send all events to the database
> unnecessarily. Combining that with the backup databases that can be
> made
> during Sentinel patches makes for a lot of wasted space, and that could
> still apply to you. Sending events to the database, which has its own
> retention policy defined per RDD, just burns space and cycles
> unnecessarily.
>
> --
> Good luck.
>
> If you find this post helpful and are logged into the web interface,
> show your appreciation and click on the star below...



--
-"Also now available in 'G+'
(http://plus.google.com/+BenWalter-Kiwi) and 'Website'
(https://www.isam.kiwi/) format".- 😉
------------------------------------------------------------------------
ScorpionSting's Profile: https://forums.netiq.com/member.php?userid=469
View this thread: https://forums.netiq.com/showthread.php?t=54304


Visit my Website for links to Cool Solution articles.
0 Likes
ScorpionSting Absent Member.
Absent Member.

Re: MongoDB / PostgreSQL Compacting


It's the RDD: [sentinel.identity.rdd] Identity Recent Activity taking
all the space....with only 14 day retention its still 200GB... According
to pgAdmin III, there are quite a few Dead Tuples in the table, so
attempting the following to try and decrease where I can:


Code:
--------------------
vacuumdb -v --full --username=dbauser --dbname=SIEM --table=evt_rpt_35003882
--------------------


--
-"Also now available in 'G+'
(http://plus.google.com/+BenWalter-Kiwi) and 'Website'
(https://www.isam.kiwi/) format".- 😉
------------------------------------------------------------------------
ScorpionSting's Profile: https://forums.netiq.com/member.php?userid=469
View this thread: https://forums.netiq.com/showthread.php?t=54304


Visit my Website for links to Cool Solution articles.
0 Likes
ScorpionSting Absent Member.
Absent Member.

Re: MongoDB / PostgreSQL Compacting


Got extra disk to do this.....went from 218G -> 135G compacting just the
1 table....


IN

E

FICIENT


--
-"Also now available in 'G+'
(http://plus.google.com/+BenWalter-Kiwi) and 'Website'
(https://www.isam.kiwi/) format".- 😉
------------------------------------------------------------------------
ScorpionSting's Profile: https://forums.netiq.com/member.php?userid=469
View this thread: https://forums.netiq.com/showthread.php?t=54304


Visit my Website for links to Cool Solution articles.
0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.