Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
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?