Limiting database queries

Each Helix server group has an associated maxresults, maxscanrows, and maxlocktime value. The default for each is unset, but a superuser can use p4 group to limit it for any given group.

MaxResults prevents the server from using excessive memory by limiting the amount of data buffered during command execution. Users in limited groups are unable to run any commands that buffer more database rows than the group’s MaxResults limit. (For most sites, MaxResults should be larger than the largest number of files anticipated in any one user’s individual client workspace.)

Like MaxResults, MaxScanRows prevents certain user commands from placing excessive demands on the server. (Typically, the number of rows scanned in a single operation is roughly equal to MaxResults multiplied by the average number of revisions per file in the depot.)

Finally, MaxLockTime is used to prevent certain commands from locking the database for prolonged periods of time. Set MaxLockTime to the number of milliseconds for the longest permissible database lock.

To set these limits, fill in the appropriate fields in the p4 group form. If a user is listed in multiple groups, the highest of the MaxResults (or MaxScanRows, or MaxLockTime) limits (including unlimited, but not including the default unset setting) for those groups is taken as the user’s MaxResults (or MaxScanRows, or MaxLockTime) value.

Example   Effect of setting maxresults, maxscanrows, and maxlocktime

As an administrator, you want members of the group rocketdev to be limited to operations of 20,000 files or less, that scan no more than 100,000 revisions, and lock database tables for no more than 30 seconds:

Group:        rocketdev
MaxResults:   20000
MaxScanRows:  100000
MaxLockTime:  30000
Timeout:      43200

Suppose that Ruth has an unrestricted (loose) client view. She types:

$ p4 sync

Her sync command is rejected if the depot contains more than 20,000 files. She can work around this limitation either by restricting her client view, or, if she needs all of the files in the view, by syncing smaller sets of files at a time, as follows:

$ p4 sync //depot/projA/...
$ p4 sync //depot/projB/...

Either method enables her to sync her files to her workspace, but without tying up the server to process a single extremely large command.

Ruth tries a command that scans every revision of every file, such as:

$ p4 filelog //depot/projA/...

If there are fewer than 20,000 revisions, but more than 100,000 integrations (perhaps the projA directory contains 1,000 files, each of which has fewer than 20 revisions and has been branched more than 50 times), the MaxResults limit does not apply, but the MaxScanRows limit does.

Regardless of which limits are in effect, no command she runs will be permitted to lock the database for more than the MaxLockTime of 30,000 milliseconds.

To remove any limits on the number of result lines processed (or database rows scanned, or milliseconds of database locking time) for a particular group, set the MaxResults or MaxScanRows, or MaxLockTime value for that group to unlimited.

Because these limitations can make life difficult for your users, do not use them unless you find that certain operations are slowing down your server. Because some Helix server applications can perform large operations, you should typically set MaxResults no smaller than 10,000, set MaxScanRows no smaller than 50,000, and MaxLockTime to somewhere within the 1,000-30,000 (1-30 second) range.

For more information, including a comparison of Helix server commands and the number of files they affect, type:

$ p4 help maxresults
$ p4 help maxscanrows
$ p4 help maxlocktime

from the command line.

MaxResults, MaxScanRows and MaxLockTime for users in multiple groups

As mentioned earlier, if a user is listed in multiple groups, the highest numeric MaxResults limit of all the groups a user belongs to is the limit that affects the user.

The default value of unset is not a numeric limit; if a user is in a group where MaxResults is set to unset, he or she is still limited by the highest numeric MaxResults (or MaxScanRows or MaxLockTime) setting of the other groups of which he or she is a member.

A user’s commands are truly unlimited only when the user belongs to no groups, or when any of the groups of which the user is a member have their MaxResults set to unlimited.