DBpulse Getting Started - UNIX

DBpulse: Getting Started - UNIX

Thanks for trying out DBpulse. As with any software package, user feedback is tremendously important. Please contact us on the Web at: www.dbpulse.com or by e-mail at: info@dbpulse.com.

This README file is divided into these sections:

  1. General Concepts
  2. Installation Guidelines
  3. Running the Software
  4. Additional Examples
  5. Uninstall Guidelines

Have fun!

DBpulse General Concepts

DBpulse is designed to serve three main purposes:

  1. diagnose SQL performance problems
  2. audit SQL transactions
  3. benchmark SQL applications

To accomplish this, it captures network packets, rebuilds sessions, decodes TDS, and writes output. The result of all this processing is simply two file types: RATING and THRESH.

The RATING files are used to benchmark applications, whereas the THRESH files are used to diagnose performance problems and audit SQL transactions. Both types of files are rotated on a 15 minute basis (i.e. the default RUN interval is 15 minutes but can be changed). Thus, every 15 minutes two new files are created and the contents of a particular file pertain to 15 minutes of activity.

The RATING file stores data for benchmarking purposes. It contains:

  1. Query type ([A]ttention, [L]ogin, [M]arker, [Q]uery, [R]PC)
  2. Query end time
  3. Query elapsed time
  4. Query status
  5. Source IP address
  6. Servername (Instance Name)
  7. Internal Session ID
  8. Server.Database.Username
  9. For type 'M': Marker name
  10. For type 'M': Marker start description
  11. For type 'M': Marker end description

Note that the RATING file does NOT store the actual query itself. The most important items in the RUN file are Query elapsed time and Query status. Those are used to calculate Rating and Load values. To find out more about Rating and Load values, see the white paper about performance management using DBpulse.

The THRESH file stores all the items in the RATING file and more. It contains:

  1. Query type ([A]ttention, [L]ogin, [M]arker, [Q]uery, [R]PC)
  2. Query end time
  3. Query elapsed time
  4. Query status
  5. Source IP address
  6. Servername (Instance Name)
  7. Internal Session ID
  8. Server.Database.Username
  9. Query threshold value
  10. Result set row count
  11. Packets to server
  12. Bytes to server
  13. Packets to client
  14. Bytes to client
  15. For type 'A': ATTENTION
    For type 'L': LOGIN
    For type 'Q': Actual query string
    For type 'R': Actual query string
    For type 'M': Marker name
  16. For type 'M': Marker start description
  17. For type 'M': Marker end description

The THRESH file is used to identify the most pathological queries (those queries that take the longest time to run). The default behavior of DBpulse is to log ALL (threshold = 0.0) queries, thus the THRESH file becomes an audit file of every query sent to the database server.

In its normal mode of operation, DBpulse only outputs to the THRESH file. To limit the queries that are written to the THRESH file, use the -t option.

For example: -t 5.0 sets the threshold at 5.0 seconds.

Again, for audit purposes, don't use the -t option (which sets the threshold to 0.0). To completely turn off the query output to the THRESH file, use the -no_thresh option. With this option set, no output will be sent to the THRESH file.

You can turn on the Rating feature by setting the -rating option.

NOTE: Rating is a premium feature of DBpulse and has an associated cost. This option tells DBpulse to write benchmarking data to the RATING files. It also keeps track of the interval and creates a new RATING file when the interval is up.

DBpulse provides real-time interaction through a TCP connection. Currently, the information it provides is read-only. That is, it reports on internal data structures such as packet capture statistics, TDS session values, and RAM buffer allocation. It does not allow a user to change the behavior of DBpulse. Currently, there is no user authentication so the data is viewable by any user with access to the appropriate port.

For example: -l 2500 sets the TCP listening port to 2500

To connect to DBpulse: telnet {hostname} 2500

There is a special case of the -l option, and that is to use -l by itself. This tells DBpulse to use the default TCP listening port of 21107.

For additional information about DBpulse and its configuration options, please review the man page (/foresight/man/man1/dbpulse.1) or html page (/foresight/docs/dbpulse.html).

Installation Guidelines for UNIX systems

The installation process generally takes less than 10 minutes. We recommend you become familiar with entire process before starting. Note that DBpulse requires a license file in order to run. An evaluation license file is provided free of charge.

Where should you install DBpulse?

The simplest place is on the database server itself. That way, all traffic can be monitored. For test purposes, you can install DBpulse on a client machine (Web/App server or PC); but you will only see queries made from that client machine. Or, if you can get access to a span/mirror port on a switch or your network uses a hub (that can see all traffic going in to and out of the database server) and you can connect a computer to it, that will work as well.
NOTE: DBpulse runs as the root user because it needs to set the network device to promiscuous mode in order to operate properly.

SOLARIS

  1. Log in as 'root'
  2. Run 'pkgadd' using the downloaded file as the argument to '-d':
        fsa01# pkgadd -d /var/tmp/DBpulse-3.1.6174-5.9.sparc.pkg
            
  3. Update root's .profile to include the K_HOME environment variable. Add the following lines to root's .profile:
        K_HOME=/foresight export K_HOME
    
        PATH=$PATH:$K_HOME/bin
        export PATH
    
        MANPATH=$MANPATH:$K_HOME/man
        export MANPATH
            

LINUX

  1. Log in as 'root'
  2. Run 'rpm' on the downloaded file
        fsa01# rpm -ivh /var/tmp/fsa-dbpulse-3.1.6246-centos4.i386.rpm
            
  3. Update root's .profile to include the K_HOME environment variable. Add the following lines to root's .profile:
        K_HOME=/foresight
        export K_HOME
    
        PATH=$PATH:$K_HOME/bin
        export PATH
    
        MANPATH=$MANPATH:$K_HOME/man
        export MANPATH
            
If there are no errors, your installation is complete.

Running DBpulse for UNIX systems

DBpulse requires a license in order to run. An evaluation license comes in the distribution tree, however, it limits the analysis to 2,000,000 packets. After 2,000,000 packets, DBpulse shuts down. If you wish to analyze the data stream further, you must re-start DBpulse (or purchase a registered license, see www.dbpulse.com).

Here's how to use the evaluation license:

  1. Log in as 'root'.
  2. Copy the evaluation license file into the real license file:
        fsa01# cd /foresight/etc
        fsa01# mv license_dbpulse license_dbpulse.old  (Optional)
        fsa01# cp eval_dbpulse.lic license_dbpulse
NOTE: if you already have a valid license in license_dbpulse, DO NOT overwrite it with the evaluation license without making a copy!

DBpulse can be run in two different ways:

  1. a standalone executable used to debug a few sessions (i.e. all session for a period of time)
  2. a daemon running 24x7 used to analyze all sessions for service assurance.

Before running DBpulse, you must edit the /foresight/etc/hosts_dbpulse file.

For example:

    #                       Host            TCP     Net             Rating
    # Servername            IP_address      Port    Proto   Thresh  Flag
    # --------------------  --------------  ------  ------  ------  ------
      dbms2                 192.168.2.52    1433    mssql   1.0     1
    #
This entry indicates you want to capture queries going to the 'dbms2' server. It's IP address is 192.168.2.52 and it's using the standard MS SQL Server port of 1433. The protocol is MS SQL Server. The long running query threshold time is one second and the rating flag is turned on.

To run DBpulse in standalone mode, type:

    fsa01# dbpulse -H hosts_dbpulse

Other common options include

-d
device
-f
configuration file
-t
threshold value
For Lunix, the device values include 'eth0', 'eth1', or 'eth2'. For Solaris, the device values include 'ce0', 'eri0', or 'hme0'. Most of the command line options can be put into a configuration file using the '-f' option. In addition, memory buffer size parameters are put into the configuration file (a common filename is /foresight/etc/dbpulse.cfg). Although the DBpulse hosts file has a column for the query threshold, you can set the default value on the command line and then override that value with the hosts_dbpulse file.

For example:

    fsa01# dbpulse -H hosts_dbpulse -d eth1 -t 5.0
This tells DBpulse to listen for packets on 'eth1' that are going to or coming from the hosts found in 'hosts_dbpulse' and to record all round trip queries that run 5.0 seconds or more. For a more complete list of options, please review the 'dbpulse' man page.

Alternatively, you could put these parameters into a configuration file (/foresight/etc/dbpulse.cfg) and run DBpulse like this:

    fsa01# dbpulse -f /foresight/etc/dbpulse.cfg
The configuration file would look like this:
    Hosts_File=hosts_dbpulse
    Pcap_Device=eth1
    Query_Threshold=5.0

Once DBpulse is running, you can use any client application (from a remote PC because DBpulse does not see traffic on the loopback interface) to test it. As soon as DBpulse sees database traffic, it processes the packets. To stop DBpulse in standalone mode, simply press Ctrl-C.

If you wish to interact with DBpulse when it is running, you must use the -l option (TCP listen port). When this option is set, DBpulse listens to a TCP port (limited to 32 concurrent sessions) and outputs information about its internal data structures. These internal structures contain information such as command line parameters, uptime, software version, RAM utilization, packet capture statistics, and query information.

To run DBpulse in listening mode, type:

    fsa01# dbpulse -l -f /foresight/etc/dbpulse.cfg
The default TCP listening port is 21107. To change this, enter a port number immediately behind the -l option. For example: -l 2500. Alternatively, you could put the listening port parameter into the configuration file rather than using the -l option. The configuration file would look like this:
    Hosts_File=hosts_dbpulse
    Listen_Port=2500
    Pcap_Device=eth1
    Query_Threshold=5.0

To connect to DBpulse, use telnet.

    fsa01# telnet localhost 21107
Once connected, type 'help' to see the available options. It's a simple interface. The main command is 'get'. Type: get info. This outputs the start-up parameters. Type: get perf. This outputs DBpulse performance data. Type: get stats all. This outputs all statistical data including packet capture numbers, RAM allocation, TDS session values, thread processing data, and string buffer usage. When done reviewing DBpulse internal data structures, type: quit to disconnect.

To run DBpulse in daemon mode, type:

    fsa01# dbpulse -b -f /foresight/etc/dbpulse.cfg
The '-b' option is for background. In this mode, dbpulse forks off a child process and detachs from its controlling terminal. Thus, it can no longer take input from the keyboard nor output to the display. You will get your shell prompt back immediately and DBpulse will be running in the background as a daemon.

To stop DBpulse in daemon mode, you need to send it a signal (via kill). For additional ways to stop DBpulse, see the dbpulse man page.

To start DBpulse at boot time, do the following:

    fsa01# cd /etc/rc3.d
    fsa01# ln -s /etc/init.d/dbpulse S99dbpulse
    

Additional EXAMPLES of DBpulse parameters

    dbpulse -h fsa01 -p 5500 -S SQL_PROD -x 255
    

This example turns on FULL debugging. In most cases, this is way too much information and slows down the packet capture tremendously. We recommend setting the debugging level above 3 ONLY for a short period of time or on a desktop PC (which doesn't affect SQL Server's performance). This example also performs a full audit. Every query sent to the database will be captured in the THRESH file.

NOTE: In general, you SHOULD NOT run in debugging mode. Outputing debug information can reduce efficiency by several orders of magnitude.
    dbpulse -h fsa01 -p 5500 -S SQL_PROD -t 10.0
    

This example tracks long running queries. That is, any query that takes longer than 10.0 seconds is written to the THRESH file.

    dbpulse -h fsa01 -p 5500 -S SQL_PROD -t 10.0 -rating
    

This example is a refinement of the one above. It still tracks long running queries, but also writes benchmarking data to the RATING files.

    dbpulse -h fsa01 -p 5500 -S SQL_PROD -o /tmp/fsa01.pcap
    

This example captures all packets coming in to and going out of fsa01 on port 5500 and writes them to a file named /tmp/fsa01.pcap. It performs no processing of the packet stream (other than writing it to a file). This file can be used in the future to playback a sequence of packets. Neither the RATING nor the THRESH files are updated.

NOTE: because all of these examples are in standalone mode, you will need to type Ctrl-C at the console to shut the program down.
    dbpulse -h fsa01 -p 5500 -S SQL_PROD -o /tmp/fsa01.pcap -c 100000
    

This is a refinement of the previous example where the program stops after capturing 100,000 packets.

    dbpulse -i /tmp/fsa01.pcap
    

This example opens a file named /tmp/fsa01.pcap and uses it rather than a network device for packet input. It processes the file as if the packets were coming from the network.

Uninstall Guidelines for UNIX systems

  1. Log in as 'root'.
  2. Remove the software distribution tree the way you would normally remove software:
        solaris# pkgrm FSAdbpulse
        linux# rpm -e fsa-dbpulse
            
  3. If you updated the root user's F<.profile>, you will need to remove the K_HOME environment variable from that file. Remove the following lines:
        K_HOME=/foresight
        export K_HOME
    
        PATH=$PATH:$K_HOME/bin
        export PATH
            
If you have other Foresight Automation products installed, the /foresight directory will not be empty. Also, any configuration files and data files generated will also remain. To remove the DBpulse data files, you should examine the directories in /foresight/var/dbpulse. This entire directory can be removed without affecting any other Foresight Automation software. If you have no other Foresight Automation software, you can be safe in removing the entire /foresight directory.