The usl tool helps you perform capacity planning by automating some of the process explained in Dr. Neil J. Gunther's book Guerrilla Capacity Planning (presented in simplified form in this Percona white paper). Given an input file with two columns, concurrency and throughput, this tool fits the data into Gunther's Universal Scalability Law.

This tool depends on gnuplot for plotting and curve-fitting.

Command-Line Options and Environment Variables

The tool has the following command-line options, which must come first on the command-line, before any filenames:

-a

Sets the label on the X-axis to 'node count' instead of 'concurrency,' which is the default.

-c CONVERSION

Specifies a conversion function to run the input data through before feeding it into the model. See Conversion Functions for more details.

-e

Draws error lines on the final plot.

-i INTERVAL

When -c is given, specifies to aggregate the input data into intervals of -i seconds. This can be useful for smoothing data. See Conversion Functions for more details.

-k KEEPFILE

Specifies a file to hold the input data. The specified file will not be removed when the program finishes, so you can re-analyze it if you wish. This is handy when using -c to transform the data: you can run the transformation once, and then re-analyze the data many times.

-l LIMIT

Set the upper limit of the X axis in the final plot.

-L COLOR

The color for plotting points; when color is used, default is "lt rgb #80B000".

-m THREADS

When -c is given, enforces a maximum concurrency of THREADS in the input data. Useful for filtering outliers such as instantaneous spikes of very high concurrency.

-n ADJUSTMENT

When -c is given, subtracts ADJUSTMENT from the concurrency. Useful for compensating for tasks in the system that aren't really part of the workload you are measuring.

-o ONLY

Only produce plots specified in this comma-separated list.

-p PREFIX

A filename prefix for the generated images.

-P PORT

The port number for -c tcpdump.

-r

Render pdf and eps plots in color.

-R

Don't re-fit against the USL. Use the kappa and gamma computed from quadratic regression, not USL regression.

-t FILETYPE

Type of images to generate. The default is png, but you can also specify eps and pdf.

-T POINTTYPE

The gnuplot point type and options for the plots; default is 6.

-x ADJUSTMENT

Multiply the C(1) measurement by this factor. Useful if the tool's interpolated value for C(1) is wrong.

-X

Include C(1) as a fit parameter for USL regression.

Any additional arguments on the command-line are treated as file names containing the input data to plot and model.

How it Works

The usl tool works by accepting some input data with columns of measurements. Your input needs to have at least two columns: concurrency (N), and throughput (C). See Conversion Functions if you need to convert from a different input before running the model. Lines beginning with a # character are commented out.

The tool tries to find the C value for N=1. If this exists directly in the input file, it will use that value. If there are many measurements for N=1, it will use the average value. If there is no N=1 measurement, the tool will try to interpolate from the data it has, and you might need to adjust the result with -x.

After this step, the tool plots a number of graphs against the data. To make sense of these graphs, you need to be familiar with Dr. Gunther's scalability model. We will not document the meaning of the graphs in this user's manual.

Next, the tool transforms the input data, performs least-squares regression against it, and plots that. The coefficients for the resulting fitted curve are 'a' and 'b', respectively. The tool prints these out, along with the coefficient of determination to show how closely the line fits the points (the R-squared value).

The tool uses the 'a' and 'b' coefficients to approximate the 'sigma' and 'kappa' parameters to the Universal Scalability Law. It then performs another least-squares regression against the Law, with the approximated parameters as starting points. (It needs to generate reasonably good starting points, or gnuplot can have trouble with the regression; it can get stuck in a local maximum that is very wrong.) After the second least-squares regression, the tool captures the 'sigma' and 'kappa' parameters, and plots the source data and the model.

Conversion Functions

The -c option specifies a pre-processing function to convert the input data into the necessary concurrency-vs-throughput format. At present there is only one built-in converter. In the future we might add more.

If you want to save the converted output to inspect it, use -k.

globalstatus

This converter accepts input from MySQL's SHOW GLOBAL STATUS command, in the form generated by the following script:

while sleep 10; do date '+TS %s.%N' | tee -a status >> processlist mysql -e 'SHOW GLOBAL STATUS' >> status & mysql -e 'SHOW FULL PROCESSLIST\G' >> processlist & done

That script might create large files. For most purposes, the following is adequate:

mysql -e 'SHOW FULL PROCESSLIST\G' > processlist while sleep 10; do date '+TS %s.%N' >> status mysql -e 'SHOW GLOBAL STATUS' \ | grep -e Questions -e Uptime -e Threads_running >> status done

The converter looks at Questions, Uptime, and Threads_running. For each sample, it computes the queries per second as the throughput, and the average of the Threads_running from this sample and the previous sample. It has the following characteristics:

• It automatically subtracts 1 from Threads_running, because the thread doing SHOW GLOBAL STATUS will increase that variable by 1, but is not really part of the workload being measured.
• It applies -n to the Threads_running count as well. If you have replication slaves attached to this server, they will artificially raise Threads_running, but are again not really part of the server's workload in most cases.
• It respects -i, so you can aggregate over time windows longer than 10 seconds. If you do so, then Threads_running is computed as the average of all the samples of Threads_running seen over the interval, not just the beginning and end.
• It respects -m and will ignore any samples whose Threads_running is larger than this.

tcpdump

This converter accepts input from tcpdump, in the form generated by 'tcpdump -tttt -nnq'.

The converter watches for TCP conversations beginning with a message from some host/port combination to the port that you specify with -P. It remembers the timestamp of this message, and when the server sends a message back, it counts the time elapsed. As it processes input, it keeps track of the total time the system is busy, and the number of requests outstanding (unanswered) at each point, and computes a weighted average of the concurrency from that. It ignores TCP messages that carry no data, such as 'ack'.

It currently does not respect other command-line options such as -i, -m, and -n. These need to be factored out of the globalstatus converter so they can be applied to all converter functions. It is also a relatively new converter and might have bugs.

The default port number of -P is 3306.

Example Usage

Here is a sample dataset that we can model:

# threads read only read write 1 955.16 562.62 2 1878.91 1258.05 4 3548.68 2503.93 8 6531.08 4446.48 16 9897.24 6391.45

This data is a partial sample from this benchmark. The first line is commented out, and the usl tool will ignore it.

The tool will print out information on its findings after looking at the input and computing or finding C(1), for example:

Parameters to the model: min(N) 1 max(N) 16 max(C) 9897.24 C(1) 955.16 N=1 ??? 1

The above means that N=1 was present in the data, and the value for N=1 was found to be 955.16. Check the input data again to verify this.

The first graph to look at is the computed efficiency. Ensure that no points are shown with greater than unity efficiency, as this is unphysical. Click on the image for a larger version.

a 0.00131418 b 0.0164629 R^2 0.998991

Next you should look at the graphs to see how closely the polynomial fits the deviation from linearity, and ensure that it looks like the source data is actually parabolic and the line fits the points well. Inspect the residuals (the difference between the fitted curve and the transformed source data points). Ensure that the residuals appear randomly distributed, and that there is no apparent pattern that might indicate the source data should really not be accepted as an input. The residuals-squared is also plotted, and can help you identify outliers if you need to scrub the input data. And the residuals-squared is plotted against the Universal Scalability Law as well.

You can view the model-vs-actual graph to see the end result. The red and green lines are the estimated errors from the least-squares regression.