Reduce PostGreSQL DataBase size. Increase more than 1 GB/day.

[jordanvichev]

Hello,

We are using Alfresco Community with PostGreSQL database.

Community - 5.0.0 (d r99759-b2)

psql (PostgreSQL) 9.4.5

solr-spec4.9.1

Our database is too big - 500 GB. We do Full Vacuum every 3 months and reduce it around with 100 GB.

TrashCan is not working(Alfresco Share -> Administrator -> MyProfile -> Trashcan - Error loading items) and we cannot delete files in trash.

In Solr Index for "alfresco core" you can see how many Docs we have:

lastModified:less than a minute ago.
version:1697567.
numDocs:44254550.
maxDoc:44847057.
deletedDocs:592507

"archive core":

lastModified:2 minutes ago.
version:1966437.
numDocs:30422800.
maxDoc:30728112.
deletedDocs:305312

We are using auditing and workflows and uploading pictures in alfresco.

We do "partitioning" of alf_node_properties table because it has too many rows and it is the biggest table in database.

Every parted table alf_node_properties_x has 1 000 000 rows.

Summary of rows:

alfresco=> select count(*) from alf_node_properties;
count
-----------
943517340
(1 row)

Please give me a hint what we should do to reduce this big table?

There are so many rows and everything works with this table, so we see many locks in database.

Thank you!

Yordan Vichev

[kintu_barot]

You can configure the trashcan cleaner using the following

https://docs.alfresco.com/6.0/concepts/trashcan-cleaner.html

it will help you remove deleted files and associated records from the database.

This will reduce some amount of database size.

Regards,

Kintu

ContCentric

[jordanvichev]

Hello,

Thank you for your replay.

Unfortunately can't do this because my version is Community - 5.0.0 (d r99759-b2). In this version there is not such functionality.

Regards

Yordan

[cesarista]

Hi, probably your database is getting bigger and bigger because of auditing. 

Some time ago @fcorti writed about data implosion due auditing:

http://fcorti.com/2015/11/25/audit-data-explosion-in-alfresco/

Having many documents in archive does not help. If you do not have thrascan cleaner, you can use some JS scripting for deleting some batches on it. By the way, the problem may be related with an application that intensively deletes documents, leaving them in Thrascan (with sys:temporary aspect you can delete without passing through trashcan).

Regards.

--C.

[jordanvichev]

Hello,

Sometimes trashcan is opening without errors and I can delete a couple of documents. But I cannot understand why one time is open another time is not open - return error. May be it is related with database load I don't know.

In next days will do full vacuum, I hope this will help.

In parallel I have increased Read time out for alfresco, I hope increased time out also will help. Will see : ).

I'm an admin not dev person. Can do bash scripts but cannot do JS scripts : ).

Regards

Yordan