Managing database tables
This topic covers the following checks:
- Display size or statistics for database tables - when prompted by Perforce Technical Support
- Check the integrity of database tables - recommended routine maintenance
- Check the consistency between pairs of database tables - recommended routine maintenance
Display size or statistics for database tables
Because p4 dbstat blocks write access to the database while it scans the tables, you probably will not use this command unless instructed to do so by Perforce Technical Support to estimate disk seeks due to sequential database scans.
The dbstat command can be used to display statistics on the internal state of the Helix server database. For example,
$ p4 dbstat -a
You can also specify the name of a database file in your server’s root directory.
For details, see the dbstat command in Helix Core P4 Command Reference.
Check the integrity of database tables
- Just as we recommend the checkpoint process as part of your regular maintenance tasks, so we recommend that you periodically check the structural integrity of the tables in your database.
-
The check involves imposing a "read" lock on one or more database tables to ensure a clean check. Therefore, it is best to run the check at a "quiet" time when your end users will not be trying to run commands.
Two ways to check:
p4 dbverify | p4d -xv |
---|---|
can be run on a remote client |
must be run on the server
|
can be limited to a specified table, so you can quickly run it repeatedly, each time against a different table | runs against all tables, which typically takes a long time |
"operator" and "super" can run it | any admin can run it |
server must be running | server can be shut down |
Background information
Database tables are kept in a B-tree structure for rapid access. This structure uses offsets within the file to point to a pages or specific information within a page. Data on the pages is often relocated to accommodate new data or to reuse space from deleted items.
The structure can become damaged in the event of:
- an unexpected server halt (crash, power outage, hardware failure)
- insufficient disk space
- partial hardware failure (hard drives, controllers, bad memory)
You might suspect low-level database issues if you encounter any of the following:
- checkpoint creation fails -- the checkpoint file itself might be incomplete, and the journal file failed to truncate
- items appear in a checkpoint, but are not accessible by Perforce client commands
- P4D crashes during any operation that scans large portions of the database
- you receive any Perforce Server error messages with
dbscan
ordb write
errors
Running the check
To run the p4d -xv command, the syntax is:
p4d -r $P4ROOT -xv
After running the -xv
command, you will see a series of messages as it processes each table in the format:
Validating db.name
The length of time the command takes to validate each table varies according to the size of each table's db.tablename
file. For example, a typical db.protect
table will validate more quickly than db.have
table. In general, validating all your Perforce database tables takes about the same time as taking a checkpoint.
Tables are locked one at a time as they are being validated. This blocks other commands. Therefore, you probably want to schedule production downtime and shut down the server before running p4d -xv
.
The p4d -xv
command is read-only, so you can stop it at any time with CTRL+C
if you need to put the server back into production.
Some validation errors you might see:
- Pages beyond the size of the file.
- Circular free list.
- Pages which are not readable.
- Pages which are not connected to tree or freelist.
- Pages which are not valid or are uninterpretable.
- Pages which are visited multiple times in tree and freelist.
- Data is out of order - table restore required.
- B-tree does not have consistent level count.
- Pages of uninterpretable contents found.
- Items within pages have structural problems.
If you see any of these errors, restore from your most recent good checkpoint and journal files. For further assistance, contact Perforce Technical Support.
Special cases
Validation Message |
Description |
---|---|
Free list pages which are not marked as free. |
Can be safely ignored.
|
Pages which are not connect to tree or freelist. |
Sometimes displayed in the event a command has been interrupted, which can result in new pages being written, but old pages are not assigned to the database "freelist". Those pages are to be over-written with new data, so there is no risk of data loss or corruption. However, over time these "lost" pages will result in slightly larger table sizes because the space they take up is never reused. This can be corrected by restoring the database using the latest checkpoint. If circumstances make this impractical, the restore can be safely delayed until the next maintenance window. |
Checksum mismatch, possible data corruption. |
Usually due to a crash on the Server. The stored checksums within the database no longer match those calculated during the If this message continues to occur after restores, this can also indicate a hardware problem in memory or disk drive. The checksum error message is designed to catch hardware issues such as data degradation ("bit rot"), the gradual decay of storage media. The vendor for the hardware should have diagnostic tools to check the hardware integrity. |
For more details, see p4 dbverify in the Helix Core P4 Command Reference
Check the consistency between pairs of database tables
- Just as we recommend the checkpoint process as part of your regular maintenance tasks, so so we recommend that you periodically check the referential integrity between tables in your database.
-
The check involves imposing a "read" lock on one or more database tables to ensure a clean check. Therefore, it is best to run the check at a "quiet" time when your end users will not be trying to run commands.
Background information
Many user commands result in data being written to more than one database table. Occasionally events might occur that compromise the referential integrity between table pairs. This might prevent one or more users from being able to issue one or more commands.
Running the check
The syntax to run the command is:
p4d -xx db.table_1
[db.table_2
]
Run against | Example |
---|---|
all tables |
|
all tables paired to the specified table | p4d -xx db.working
|
only the specified pair of tables | p4d -xx db.working db.resolve
|
If any inconsistencies are found, you will see a message in the format:
Perforce server error: db.table_1/db.table_2 inconsistencies found.
and a special journal file will be created, jnl.fix
, which might contain a set of delete (@dv@
) journal records, create (@pv@
) journal records, or both delete and create journal records.
Do NOT automate the process of applying the jnl.fix
file!
To avoid the risk of serious problems, including loss of data, we strongly recommend seeking guidance from Perforce Technical Support in validating the jnl.fix
file.
If no inconsistencies are found, the jnl.fix
file can be deleted.