4 Replies Latest reply on Aug 7, 2014 8:23 AM by richard.holmes

    Ways to Free up Space in the Analytics Database

      Afternoon All,

       

      Customer: Morgan Stanley

      Jive 6 - On Premise

       

      Case: Analytics DB storage

       

      Customer has raised the abovementioned case as they are running into issues with regards to the size of the Analytics Database.

      They have advised the following:

       

      Analytics DB seems to consume significant amount of data. It is by far the major storage consumer out of all kinds of data for Jive (including Storage Provider with NAS, the core database, the activity engine database, and the host-specific data).

      In order to reduce the data consumption and the growth rate, we are trying to set the retention period via Admin Console > System > Settings > Analytics > Data Load > Data Load Settings (in QA environment for now, not yet in production).

      Although we set the retention to 1 year and waited for the ETL job to complete, the data consumption was only reduced by 2GB out of 94GB in total. In addition, we further set it to 6 months and waited for the ETL job to complete again, the data consumption is still around 94GB.

      Are there any ways to free up more disk space and reduce the growth rate of the storage consumption? We are using PostgreSQL 9.2.4 for analytics DB. Any advice will be appreciated.

       

      I have searched the documentation and also JC and the KB but nothing detailing how to free up the DB and reduce the volume of data etc.

       

      Was hoping for some advice here as to whether we have an sort of best practices that can be provided to our customers.

       

      Thanks

       

      Richard

        • Re: Ways to Free up Space in the Analytics Database

          Hi josh.richau

           

          Not sure if you are the person to go t, but you are listed as one of the Space Admins so thought I would start somewhere.

          Do we have anyone that can provide some assistance to an On Premise customer with regards to their Analytics Database questions?

           

          Any and all help appreciated.

           

          Cheers

           

          Richard

            • Re: Ways to Free up Space in the Analytics Database

              If I had to guess, the tables taking up the most space are jivedw_activity_fact and jivedw_activity_agg_day.

               

              jivedw_activity_fact is the raw activity feed from the app. It is used to fill the rollup table jivedw_activity_agg_day (and week and month).

               

              If they don't mind the loss of fidelity, they can safely remove the older records from activity_fact (and potentially agg_day) - they would still have their monthly rollups from the past...

               

              As far as the built in functions, I am not sure what the expected functionality is there so can't comment...

                • Re: Ways to Free up Space in the Analytics Database

                  Thanks Josh,

                   

                  Much appreciated. I will go back to the customer with this information.

                   

                  Cheers

                   

                  Richard

                  • Re: Ways to Free up Space in the Analytics Database

                    Hi Josh,

                     

                    Further to my update of yesterday.

                    I took your information back to the customer and they have responded with the following:

                     

                    Hi Richard, thanks for the reply. It is a good step forward to identify the largest tables.

                    From the response though, I'm a little concerned how safe it is to remove the data, as we certainly do not want to break anything beyond putting the retention period in place which we expect to reduce the data usage.

                    Could you provide some more detailed information about the exact impacts of removing data from these tables? We'd like to be better convinced that manual removal of data from these tables will not introduce additional side-effects.

                    For example, has this ever been done elsewhere successfully?

                     

                    Is it possible to answer the customers concerns here?

                     

                    Cheers

                     

                    Richard