1 / 7

Database Maintenance

Database Maintenance. Karen Tian ktian@Stanford.EDU. Overview. Q: How long can we keep running? Our goal is to minimize downtime Do maintenance while running Challenges Very large database: a few TB Transaction intensive applications Currently we have SUMS and DRMS tables in the same DB

elliot
Download Presentation

Database Maintenance

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Database Maintenance Karen Tian ktian@Stanford.EDU

  2. Overview • Q: How long can we keep running? • Our goal is to minimize downtime • Do maintenance while running • Challenges • Very large database: a few TB • Transaction intensive applications • Currently we have SUMS and DRMS tables in the same DB • SUMS tables: insert, update, and delete • Must vacuum to reclaim disk space • DRMS tables: tables insert only, no update or delete, except for transient records • Static tables, need to vacuum to prevent XID wraparound

  3. Routine database maintenance tasks • Base backup and WAL file backup • Vacuum • Reindex • PostgreSQL upgrade

  4. Base backup and WAL file backup • Base backup: pg_start_backup() file system snapshot pg_stop_backup() snapshot dump to tape • Plan on weekly base backup • The longer the interval between base backups, the longer it takes to process xlog to catch up • These base backup along with appropriate WAL files can make a consistent DB snapshot, perhaps useful for other purpose. • Archive WAL files • Clean up WAL files after a base backup

  5. Vacuum • Purpose • Recovering disk space • Updating planner statistics • Preventing transaction ID wraparound failures • Obstacle • Long running transaction prevents vacuum from deleting dead rows Currently SUMS and DRMS tables reside in the same DB. DRMS module may start long running transaction that prevents vacuum from removed dead rows in SUMS tables. • Plans • Switch to warm stand-by and perform vacuum on the primary • Separate SUMS from DRMS, i.e., run them off different PostgreSQL server instances.

  6. Reindex • Why? • Index might become corrupted • Index might become bloated • A freshly-constructed index is faster because logically adjacent pages are usually also physically adjacent in a newly built index • Impact • Locks out writes but not reads of the index's parent table • Takes an exclusive lock on the specific index being processed, which will block reads that attempt to use that index

  7. PostgreSQL upgrade • The internal data storage format is subject to change between major releases • Require backing up data and restore it on the new server • Must take the DB server down and switch to stand-by

More Related