dbpulse - utility that records SQL query information by capturing
network packets
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]
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.
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.
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.
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.
- 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.
- $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.
find_all_devices(1), sniff_ip(1), service_ctrl(1),
rating_file(5), thresh_file(5)
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.
DBpulse Manual - dbpulse