cancel
Showing results for 
Search instead for 
Did you mean: 

How to plan maintenance activity ?

 Database maintenance is about making your database run smoothly.

Monitoring systems are not a substitute for good planning. They alert you to unplanned
situations that need attention. The more unplanned things you respond to, the greater
the chance that you will need to respond to multiple emergencies at once. And when
that happens, something will break. Ultimately that is your fault. If you wish to take your
responsibilities seriously you should plan ahead.

Build a regular cycle of activity around the following tasks:

Capacity planning: Observing long term trends in system performance and keeping
track of growth of database volumes. Plan in to the schedule any new data feeds,
new projects that increase rates of change. Best done monthly, so you monitor what
has happened and what will happen.

Backups, recovery testing, and emergency planning: Organize regular reviews
of written plans, test scripts, check tape rotation, confirm that you still have the
password to the off-site backups, and so on. Some sysadmins run a test recovery
every night so they always know that a successful recovery is possible.

Vacuum and index maintenance: To reduce bloat, including collecting optimizer
statistics through ANALYZE.

Consider VACUUM again, with the need to manage the less frequent freezing
process. This is listed as a separate task so that you don't ignore this and have it bite
you later.

Server log file analysis: How many times has the server restarted? Are you sure you
know about each incident?

Security and intrusion detection: Has your database already been hacked? What did
they do?

Understanding usage patterns: If you don't know much about what your database is
used for then I'll wager it is not very well tuned or maintained.


Long term performance analysis: It's a common occurrence for me to get asked to
come and tune a system which is slow. Often what happens is that a database server
can get slower over a very long period. Nobody ever noticed any particular day when
it got slow, it just got slower over time. Keeping records of response times over time
can help confirm whether or not everything is as good now as it was months or years
previously. This activity is where you might reconsider current index choices.

Data quality: Are the contents of the database accurate and meaningful? Could the
data be enhanced?

Business intelligence: Is the data being used for everything that can bring value to
the organization?

Version history
Revision #:
1 of 1
Last update:
‎06-15-2017 08:19 PM
Updated by:
 
Contributors