DBpulse Manual - dbpulse

NAME

dbpulse - utility that records SQL query information by capturing network packets


SYNOPSIS

dbpulse [-H hosts_file | -h hostname -p tcp_port -S server_name] [-a net_protocol] [-b] [-c packet_count_limit] [-d device] [-i input_file | -o output_file] [-l listen_port] [-no_thresh] [-r run_interval] [-rating] [-s skip_packet_count] [-t threshold] [-w worker_threads] [-x debug_value]


DESCRIPTION

dbpulse captures network (TCP) packets going in to and out of the database server. It de-multiplexes this traffic back into state-oriented connections. Thus, it can record the round trip activity of each query sent to the database. As it runs, it outputs this round trip activity into two types of files: RATING and THRESH files. The RATING file is used for benchmarking purposes (see rating_file(5)) and the THRESH file is used for audit and diagnostic purposes (see (thresh_file(5).

It functions like a passive firewall; one that doesn't impede traffic but still interrogates the packets and builds session tables. dbpulse can be run on any node of the network so long as that node has access to the appropriate network traffic. The simplest implementation is to run it on the database server itself. Alternatively, it can be run on the Web/App servers, or even on desktop PCs (Windows 2000 or higher).

dbpulse comes in two flavors along with a multiple worker thread option. The first flavor is the basic edition which allows auditing and diagnostic output but no benchmark output (no RATING file support). The second flavor is the basic edition plus Rating which adds benchmarking output. The the multiple worker threads option allows more than one worker thread to process packet data (without it, only a single worker thread is allowed). Multiple worker threads introduce non-determinism to the output files and is only necessary for high-volume servers with multiple processors.

Currently, dbpulse can decode TDS (Tabular Data Stream) packets. Eventually, it will decode TNS (Transparent Network Substrate), mySQL, and DB2.


OPTIONS

For Sybase ASE, the minimal set of options are -h , -p , and -S . For Microsoft SQL Server, the minimal option is -h because -p is defaulted to 1433 and -S is defaulted to the value of -h .

Alternatively, the -H option can be used to specify a hosts file where many instances can be configured. That is, rather than starting multiple copies of dbpulse, one copy can capture queries for many instances.

-H hosts_file
Specify the name of a file in the /foresight/etc directory that contains a list of hosts (instances) to capture queries from. A common name is hosts_dbpulse .

-h hostname
Specify the hostname of the database server. It can also be an IP address.

-p sniff_port
Specify the TCP port of the database server. For SQL Server, the default value is 1433.

-S servername
Specify the server name to use to identify this instance of dbpulse. This value is used to distinguish between multiple instances of captured data.

-a network_protocol
Valid network protocols include: mssql (for SQL 7 and higher), mssql6, and ase.

-b
Run in the background. For Windows, it's service mode. For Unix, it's daemon mode.

-c packet_count_limit
Specify the maximum number of packets to capture. After dbpulse sniffs this many packets, it terminates. Commonly used with the -i and -s options for debugging certain portions of the packet stream.

-d device_name
Specify the network device to use to capture packets. The default value is the first ethernet device found. For Unix, values include: lo (loopback; only supported under linux, other OSs optimize loopback performance and don't provide a standard API), eth0 (linux), hme0 (Solaris), and \Device\NPF_{DD215891-995C-44B6-9E7A-6EB3566D9E04} (Windows).

For a list of valid device names, run find_all_devices(1).

-i input_filename
Specify the input file to read network packets in from rather than sniffing a network device.

-l listen_port
Tells dbpulse to listen on the specified TCP port for interprocess communications requests (mainly for statistics gathering). Need to use this option if more than one instance of DBpulse is running on the same server. Without a port specified (just -l), the default value is 21107.

Note: this listen port is not meant for direct human interaction (i.e. telnet), but rather for program interaction. The output is designed to be easy to parse, but not necessarily easy to understand. The primary command is ``get''. This is followed by the `type' of information you want to retrieve. Available types are: date, hires_epoch, info, perf, and stats. The `stats' type has multiple sub-types. Available sub-types (for `stats') are: connect_q, pcap, pcap_session, rm, string, tds_session, work_q, and all (this option displays each of the `stats' one after another).

Output from the TCP connection will generally be of the form: XXX LABEL=VALUE. XXX is the result code. There will be a space or a dash after the result code. A space indicates end-of-data and a dash indicates more data to follow. The LABEL is any valid identifier ([A-Z,a-z,_][A-Z,a-z,_,0-9]*). The VALUE is URI encoded. String values are NOT quoted. The record separator has an empty LABEL and empty VALUE. For example:

    250 =

-o output_filename
Specify the output file to write network packets out to. When using this option, no processing is performed. Network packets are read from a network device and written immediately to the output file. Primarily used for debugging or capturing demo data. The -i option and -o option are mutually exclusive.

-no_thresh
Run with no thresholding. That is, don't write data to the THRESH file.

-r run_interval
Specify the run interval in minutes. That is, how often to write a new rating and thresh file. The default run interval is 15 minutes.

-rating
This parameter activates a feature called Rating and requires a special license. Without this license, the -rating option will be ignored.

-s packet_skip_count
Specify the number of packets to skip before starting to pro- cess. Commonly used with the -i and -c options for debugging certain portions of the packet stream.

-t threshold_level
Specify the elapsed time threshold in seconds (floating point). The default behavior sets the threshold value to 0.0 (audit mode), thus recording ALL queries to the THRESH file. If a value is given, it limits the queries recorded in the THRESH file. That is, only queries that runs longer than the specified threshold will be recorded in the THRESH file.

-w worker_threads
Specify the number of worker threads to start up. The default value is 1. This option allows multiple CPUs to assist in the decoding of packet data. NOTE: thread scheduling is non-deterministic, thus, setting this value greater than one (1) makes all output non-deterministic (i.e. the order of data in the RATING and THRESH files will differ given the same input data).

This parameter activates a feature called Multiple Worker Threads and requires a special license. Without this license, the -w option will be ignored.

-x debug_value
Specify the value of debugging output. It is a bitmap; not a sequential number (i.e. 4 doesn't output more debugging than 3). Valid values range from 1 to 255. Never use this option in a production setting unless it's for a very short period of time (less than 30 minutes). It will slow down the data capture and use up a lot of CPU.


LICENSING

dbpulse has two methods of licensing: evaluation and registered. Each has its limitations.

There is no fee to use the evaluation version. The evaluation version of dbpulse limits the recording of database information to 2,000,000 packets. That is, it sets the -c option to 2,000,000 and cannot be overridden. Other than that, it is fully functional.

The registered version requires a registration fee. The limitation of the registered version is that it is locked to a hostname and IP address. Thus, if either changes, you will need to obtain a new license.

In addition, if you acquire the basic edition, it doesn't allow benchmarking output (no Rating). Lastly, it regulates the number of worker threads. If you don't acquire the multiple worker thread option, it limits the -w option to 1 and cannot be overridden.

Both the evaluation and registered version have an expiration date. That is, the software will only run for a period of time (evaluation version is at least 90 days and registered version is one year). You can renew the registration fee or download a new version of software 24x7 online.

In addition, registered users can request technical support and will be notified of major releases of software. They can also sign up (fee involved) for more advanced services such as: Reporting (e.g. rating and load, top 10 worst queries, etc.) and Performance Tuning.


INSTALLATION ISSUES

The Unix version of dbpulse should run properly right out of the box. Please make sure you have root access. dbpulse will set the ethernet port to promiscuous mode in order to operate properly.

The Windows version of dbpulse requires a kernel-level packet filter and some packet capture DLLs to be installed before it will work properly. If you see a message about pcap.dll not found, then you may need to install the libpcap software. It is included with the distribution in C:/foresight/bin . In addition, you must have Administrator privileges to run dbpulse properly.


ENVIRONMENT

K_HOME
Tells where the head of the software distribution is. Typically this is /foresight for Unix and C:/foresight for Windows. Without this value set, dbpulse does not know where to access its configuration, license, and log files.


FILES

$K_HOME/etc/license_dbpulse
License file. dbpulse requires a license file (regardless of version) in order to run.

$K_HOME/var/dbpulse/log/dbpulse.startup
Log file that records dbpulse start up. All start-up errors can be found here.

$K_HOME/var/dbpulse/STOP.server
Stop file. To terminate dbpulse , create this file. There are easier ways to stop dbpulse .

$K_HOME/var/dbpulse/log/debug_dbpulse.Server
Debug log file for Server

$K_HOME/var/dbpulse/log/err_dbpulse.Server
Error log file for Server

$K_HOME/var/dbpulse/wip
Directory for the current working rating and thresh file. The current files should not be accessed until they are moved to fg. Old wip files indicate that DBpulse was shutdown and not restarted within a given 15-minute interval.

$K_HOME/var/dbpulse/fg
Directory for completed rating and thresh files. They start in wip and wind up in fg .

$K_HOME/var/dbpulse/done
Directory for processed rating and thresh files. Used in the reporting services.


SEE ALSO

find_all_devices(1), sniff_ip(1), service_ctrl(1), rating_file(5), thresh_file(5)


BUGS

The locking mechanism needs to be revisited to prevent instances of dbpulse from overwriting each others log, rating, and thresh files.

IP fragmentation has not been addressed and may potentially cause problems for packets that traverse multiple networks.