3 Replies Latest reply on Apr 18, 2013 2:14 AM by Changchun Hu

    Database Maintenance Procedures

    Jwalin Pandya Newbie

      Are there any postgresql rhq database maintanace procedures being run periodically? Procedure as explained here: http://www.postgresql.org/docs/8.4/static/maintenance.html

       

      I am interested 3.0.0 ga but would requrest to have infromation for 4.4+ also. Thanks

        • 1. Re: Database Maintenance Procedures
          Changchun Hu Newbie

          Hello Jwalin,

          RHQ executes PostgreSQL database maintanance everyday by scheduling task in Java code. Generally, user doesn't need run database maintenance procedures manually.

          Changchun.

          • 2. Re: Database Maintenance Procedures
            Jwalin Pandya Newbie

            Hi Changchun,

             

            I was inquiring this is because the porcedures have not run for last 17 days (as per the check_prosgres perl script I used). Can you tell me what procedures are run? like auto-vaccuuming is done or not. etc.

             

            Thank you

             

            Jwalin

            • 3. Re: Database Maintenance Procedures
              Changchun Hu Newbie

              Hello Jwalin,

              RHQ performs database maintenance automatically on PostgreSQL, including auto-vacuumming. You can refer to the java class "org.rhq.enterprise.server.scheduler.jobs.DataPurgeJob".  I attach the method below for your inforamtion.

              private void performDatabaseMaintenance(SystemManagerLocal systemManager, Properties systemConfig) {

                      long timeStart = System.currentTimeMillis();

                      LOG.info("Database maintenance starting at " + new Date(timeStart));

               

               

                      try {

                          // Once compression finishes, we need to check to see if database maintenance

                          // should be performed.  This is defaulted to 1 hour, so it should

                          // always run unless changed by the user.  This is only a safeguard,

                          // as usually an ANALYZE only takes a fraction of what a full VACUUM

                          // takes. VACUUM will occur every day at midnight.

               

               

                          String dataMaintenance = systemConfig.getProperty(RHQConstants.DataMaintenance);

                          if (dataMaintenance == null) {

                              LOG.error("No data maintenance interval found - will not perform db maintenance");

                              return;

                          }

                          long maintInterval = Long.parseLong(dataMaintenance);

               

               

                          // At midnight we always perform a VACUUM, otherwise we check to see if it is time to

                          // perform normal database maintenance. (On postgres we just rebuild indices using an ANALYZE)

                          Calendar cal = Calendar.getInstance();

                          if (cal.get(Calendar.HOUR_OF_DAY) == 0) {

                              LOG.info("Performing daily database maintenance");

                              systemManager.vacuum(LookupUtil.getSubjectManager().getOverlord());

               

               

                              String reindexStr = systemConfig.getProperty(RHQConstants.DataReindex);

                              boolean reindexNightly = Boolean.valueOf(reindexStr);

                              if (reindexNightly) {

                                  LOG.info("Re-indexing data tables");

                                  systemManager.reindex(LookupUtil.getSubjectManager().getOverlord());

                              } else {

                                  LOG.info("Skipping re-indexing of data tables");

                              }

                          } else if (TimingVoodoo.roundDownTime(timeStart, HOUR) == TimingVoodoo.roundDownTime(timeStart,

                              maintInterval)) {

                              LOG.info("Performing hourly database maintenance");

                              systemManager.analyze(LookupUtil.getSubjectManager().getOverlord());

                          } else {

                              LOG.debug("Not performing any database maintenance now");

                          }

                      } catch (Exception e) {

                          LOG.error("Failed to perform database maintenance. Cause: " + e, e);

                      } finally {

                          long duration = System.currentTimeMillis() - timeStart;

                          LOG.info("Database maintenance completed in [" + duration + "]ms");

                      }

               

               

                      return;

                  }

               

              Changchun