Database Maintenance Procedures

[jwalin.pandya]

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

[huchangchun]

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.

[jwalin.pandya]

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

[huchangchun]

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