- Start out with the default settings for maintenance and write-mode (Basic step)
maintenance time-of-day 03:00:00
- Decide how much information you want to store in the database
performance call-details (or call-rate)
sip-registers enabled (or disabled)
accounting-history 365 days
call-details-history 3 days
media-history 7 days
file-transfer-history 7 days
im-history 365 days
These decisions are based on the traffic load and call log usage. The database can scale to 10s of millions of records, but not to 100s of millions records.
- Move the database store to another disk
Net-Net OS-E / Net-Net 2600 > database initialize ?
Manage the local database with the following command:
database initialize [database-path]
The decision to move the database store depends on what else being stored on your Net-Net OS-E / Net-Net 2600 disk: accounting CDRs, media files, etc. For example, if the 2nd disk is being used for recorded media, it's best not to move the database to the 2nd disk.
- Net-Net OS-E / Net-Net 2600 is running in steady-state
Once the Net-Net OS-E / Net-Net 2600 is running steady-state keep an eye on the event log and disk usage.
If no errors are reported and disk usage does not increase, no additional maintenance is required.
If errors are reported or disk usage increases, additional database maintenance is required. The services/tasks settings can be used to schedule other periodic actions (a 'database delete' to periodically wipe out all records, or a 'database vacuum-full' to reclaim unused disk space).
- Managing Database Maintenance Failures
The Eclipse PostgreSQL database master-service, by default, runs database maintenance at 3am "box time" (configurable under services/master-services/database -> maintenance) for various reasons this maintenance job can fail.
- Monitoring Database Maintenance Failure
If the database-maintenance process fails, an error message is written to the "eventlog" (which is typically configured with the all->error log class/severity level filter):
2007-08-21T08:07:13+00:00 [error] 1:SIP [dosDatabase] system database maintenance failed during one or more maintenance stages 2008-04-10T04:52:10+12:00 [error] 1:SIP [dosDatabase] system database maintenance failed during vacuuming(db-snapshot-info): Could not vacuum database table.
If a "db" log file has been configured (which has typically just been the "db->debug" log class/severity level filter) with the "dosDatabase->info" log class/severity level filter then the following positive indication of success is seen:
2007-08-21T18:16:56-04:00 [info] 2:SIP [dosDatabase] system database maintenance completed successfully.
The best way to generate a notification to the appropriate parties is to configure a syslog server (under services/event-log/syslog) and send the all->error logClass/severity via the filter mechanism, and then set up a trigger on the syslog server to generate an alarm via whatever method (e.g. email, page, etc.) is desired to notify the interested parties that the process has failed. Another, albeit manual, method is to use the "show database-maintenance" command:
Net-Net OS-E / Net-Net 2600>show database-maintenance-status maintenance-status: idle
If the last run database-maintenance process was successful, then this will report "idle", otherwise it will report "failed". Note that other intermediate states (e.g. reindexing, vacuuming, etc.) will be reported while database-maintenance is running.
- Corrective Steps
As of release 3.3.X, but not 3.3.6 and above, the Net-Net OS-E / Net-Net 2600 will automatically vacuum database tables. The system (filter system->info) logs will show when a table has been vacuumed automatically:
2008-04-16T05:39:45+12:00 [notice] 1:SIP [system] An automatic VACUUM FULL was performed on database table SipMessage to reclaim 895300 unused pointers
If the CXC has deemed that it cannot vacuum the table itself, it will log a message:
2008-02-01T02:10:30+13:00 [crit] 1:SIP [system] Database table SipMessage has 187506143 unused pointers and requires a VACUUM FULL
2008-02-01T02:16:32+13:00 [crit] 1:SIP [system] Database table SpotliteTransportMsg has 7847143 unused pointers and requires a VACUUM FULL
If that is the case, then you will need to manually do a vacuum-full on the table in question: database vacuum-full system
If the above fails, contact AcmePacket Support to initiate an investigation into the cause.
- Database Preventative Maintenance
The normal database maintenance that is done on a nightly basis performs a "purge", "vacuum", "reindex", and "analyze." There is one additional process that should be done on a regular basis that is not performed automatically, which is a "vacuum-full." The normal vacuum process attempts to reclaim any unused space in the database (this is analogous to a hard drive defragmentation process, but on the database files) without locking any of the tables; as much as is possible without a lock. The vacuum-full process will lock each table, one at a time, and reclaim all possible disk space. Note that a table lock prevents the table in question from being written to by an application; i.e. the CXC.
The current recommendation is to perform a vacuum-full on a monthly basis by scheduling a "maintenance/outage window" and running the command:
database vacuum-full system
The reason that it is recommended that this be executed during a maintenance window is that this process will lock tables, and therefore prevent them from being written to by the CXC. This can affect the ability of a DOS rule from being triggered, and can affect call-logs, the recording of accounting data, and any other data that is written to the database. This will not affect the ability of the CXC to pass sip/media traffic, accept/delegate registrations, route calls, and perform other directly service-related tasks. If a site is logging a large volume of data, a vacuum-full may be needed on a more frequent (e.g. weekly) basis. If the amount of data that is being written to the database is substantial, please reference the "MOP-Database Reduced Logging.doc" which provides detailed steps for methods that will reduce the amount of data that is being logged to the database.
If any performance problems are noticed, the best methods to improve database performance (and database maintenance performance) are:
reduce that amount of data logged (vsp/default-session-config/log-alert -> invite-session-only)
reduce the number of days data is retained to 1 day in call-log details and 2-3 days in all other categories (via vsp/database
- If every message cannot be stored in the database due to load, consider setting policies to only produce call logs for specific traffic of interest (see Managing Database Maintenance Failures above)