FBScanner 3.6

FBScanner (Firebird Scanner) is a tool to solve all types of performance problems with Firebird.

FBScanner Modules

FBScanner is one of the most complex IBSurgeon tools, it has several modules to cover all optimization methods and techniques. Developers or administrators can choose the most convenient module or their combination

  1. LogAnalyzer - tool for detailed analysis of SQL queries, plans, transactions and connections, it supports all versions of Firebird and InterBase.
  2. FBScanner Viewer - real-time viewer, it shows connections, queries and transactions which go through FBScanner Service, supports all versions of Firebird and InterBase.
  3. MON$Logger - tool to analyze monitoring (MON$) tables in Firebird 2.1 and 2.5
  4. TraceAPI Plugin - native plugin (Windows x32, x64, Linux x32, x64) for TraceAPI monitoring, with ability to store log into Firebird database, supports Firebird 2.5.2.

List of FBScanner's most exciting abilities are:

  • Logging SQL queries, connections and transactions with all parameters for detailed analysis (for the single or several workplaces), in order to find performance bottlenecks at Firebird (ALL versions) and InterBase (ALL versions).
  • Analysis of monitoring (MON$) tables in Firebird 2.1 and 2.5
  • Real-time monitoring of connections. FBScanner Viewer shows all connections to the selected database server: the IP/DNS name of connected client, database and connection time.
  • Real-time monitoring of SQL queries. For each connection FBScanner shows all the currently running SQL queries along with their transaction parameters.
  • Detection of the oldest connection and the oldest active transaction to allow you to analyze that may have non-optimal transaction behaviour, or incorrect transaction design or show users who might be using the application in a manner that may be affecting performance.
  • Client disconnects (10054 errors). Check that disconnections are taking place correctly and you can also use this software to disconnect users in order to perform maintenance or database upgrades.
  • FBScanner allows the routing of specific applications or particular users to allow you to zoom in on specific applications or users.
  • Important! FBScanner is the only tool to analyze SQL queries in production Firebird and InterBase databases in versions before Firebird 2.1, especially if the application has been developed by third-party's and there is no source code available.

     

Quick Tour

How can FBScanner help to identify and resolve Firebird or InterBase performance problem? Let's quickly review the most interesting features of FBScanner, and start with LogAnalyzer module.

STEP 1: LogAnalyzer "Server Load"

FBScanner Log Analyzer

Download FBScanner demo version and install it. Run "FBScanner Service Settings" from the Start menu, then click "Advanced options", tab "SQL Log ".

Create log database (it should be on another Firebird instance on this or another computer - please refer to "FBScanner User Guide", pages 14-16 for details). After that all SQL traffic which goes through FBScanner will be stored in the log database.

Run "FBScanner Log Analyzer " and connect to the log database. At the first tab, "Server load", you can see overview of SQL connections and statements. It is easy to see peaks of load and determine the moments where performance was bad.

You can zoom and scroll this performance graph, turn on and off parameters to show - in order to catch the moment when problem starts. After that right click on the point where you want to go into details and choose "Go to the SQL list on HH:mm".

 

STEP2: SQL Log in LogAnalyzer

FBScanner Log Analyzer

If you have selected tab "All Statements", subsequent list of all SQL queries will be shown. Please note that there are SQL queries from all connections which are logged by FBScanner (it is possible to setup FBScanner to monitor only the one connection, or group of computers, or only selected application - all you need is to use an appropriate connection string and/or ports - please read about it in "FBScanner User Guide").

You can narrow your search - set start and end moments and then queries on EXECUTION_TIME in order to see the longest queries.

For each query you can see SQL query text, its parameters, start/end time, count of fetched records, and also know who and when executed it: IP address, user name, etc. 

You can also force FBScanner to log SQL plans, for this you need to set an appropriate checkmark in "FBScanner Service Settings".

When you have identified the query which has problems, you can see its connection and transaction: for this right-click and choose "View connection and transactions for statement ID=...". It will bring you the tab "All connections and Logs".

STEP 3: Connections and Transactions

FBScanner Log Analyzer

Tab "All connections and Logs" contains list of all connections and related as master-detail transactions and statements.

When you click on the SQL query in the grid below, all statements in the frames of the same transaction will be marked bold. It gives you understanding of real transactions flow. Also, there are parameters of transaction (empty transaction parameters mean that this transaction has default "wait, write, snapshot" parameters).

Also, FBScanner LogAnalyzer allows to run selected query against another database version and compare its performance. It also can perform bulk SQL execution, which can be useful in case of migration between Firebird versions.

Important! Demo version of FBScanner stores some queries texts as "Demo", and it works only 7 days. Full version does not have these restrictions.

 

STEP 4: FBScanner Viewer

FBScanner Log Analyzer

FBScanner Viewer is a part of FBScanner which is responsible for real-time tracking of connections, queries and transactions. FBScanner Viewer connects directly to FBScanner Service and retrieves SQL traffic information.
FBScanner Viewer can be run on any machine in local network: it connects through port 30100 by default and pulls online data from FBScanner Service.
By default FBScanner Viewer refreshes its status every 0.5 sec, and also possible to see 20 most latest queries of the connection.
For slow (Internet) connections FBScanner Viewer can be set to refresh data each 5 second, for example.

Also FBScanner Viewer can be used for connections management: it can disconnect users (selected, all, by mask) with simulation of 10054 disconnect error, and supports white- and black-list functionality.

 

STEP 5: MON$Logger

FBScanner MON$Logger

MON$Logger is a tool to analyse monitoring (MON$) tables in Firebird 2.1 and 2.5. MON$Logger connects to the database and makes snapshot of MON$ tables for further detailed investigation. Series of such snapshots gives ability to catch queries with excessive IO consumption and non-optimal queries. In MON$ Logger it is easy to find bad queries and determine which workstation has run them.

Also, MON$Logger allows to view list of all active transactions and determine Oldest Active Transaction, as well as its origin - application and/or computer (IP address) which holds oldest active transaction.

Tips for successful optimization

Important! It is recommended to start performance problems investigation with MON$Logger: take several snapshots from the database during the heavy load time (when users experience the most difficult slowness), and try to figure out what business processes consume the most IO (attention to queries with high READ_SEQ_READS and RECORD_IDX_READS values).

Then install FBScanner Service on these workstations (or use dedicated server) and log all queries from suspicious workstations: investigate their EXECUTE and PREPARE times, check SQL plans, etc. Find and fix the problem, then check that new version runs faster than previous!

Use FBScanner Viewer for real-time viewing of queries executed on the server (this feature is also very useful for debugging complex SQLs).

FBScanner description

FBScanner is a powerful tool for Firebird and InterBase administrators and developers, designed for interception and analyzing of TCP/IP traffic between server and client applications.
FBScanner extracts in real-time a lot of details about connections, queries (statements), transactions, mark and track all these traffic objects with unique tags mechanism. FBScanner also can log all activity to the external Firebird database and perform a number of unique administrative tasks.

FBScanner (Firebird Scanner) is a tool that can monitor and view all traffic between Firebird and InterBase servers and their client applications.

FBScanner Service works as a gate (proxy) at the computer with Firebird/InterBase (Windows only), or can be installed at remote computer and work with Firebird at all supported operation system (Windows, Linux, Mac OS X, HP-UX, etc).

FBScanner can be used to profile database applications, monitor user activity, manage database connections (including client disconnection on both Classic and SuperServer architecture). It’s also ideal for troubleshooting INET errors (INET/inet_error: send errno = 10054), as well as auditing existing applications and performance tuning.

FBScanner supports Firebird (1.0, 2.0, 2.1, 2.5) and InterBase (4.0, 5.x, 6.x, 7.0, 7.1, 7.5, 2007, 2009, XE, XE3).

FBScanner is transparent as far as the database application is concerned and does not require any changes in application or database source code, logic or configuration.

FBScanner V3.x works on Windows only and requires the .NET 2.0 (or higher) Framework to be installed. 

Presentations about FBScanner

How FBScanner works

FBScanner Service

Important! In this section we don't describe work of MON$Logger and TraceAPI Plugin, it is devoted only to FBScanner Service, FBScanner Viewer and LogAnalyzer.

The FBScanner Service normally has to be installed on the same computer where the Firebird or InterBase server is running (as an option, you can install it on other computer and then redirect traffic to the actual Firebird or InterBase server). During installation the FBScanner configuration utility will ask you to choose: install FBScanner to the default port of InterBase or Firebird (3050) and change the configuration of Firebird service to 3052 (this can be adjusted), or install FBScanner to listen on another TCP port.

Important! FBScanner Service works only with TCP connections, so clients using either local (XNET) and NetBeui (WNET) connection strings will bypass FBScanner. To monitor such client applications you should use MON$Logger and TraceAPI Plugin.

If you choose the first installation option, all of the applications requests will pass through FBScanner Service.

Server-side installation
 
Remote installation
 
 

Performance impact

The usual scenario of performance optimization process includes installation of FBScanner on 1-2 typical workstations to catch the typical SQL traffic. There is no reason to intercept and log all SQL traffic from all workstations - there are too much information to analyze, and too many repeating queries.

However, if you want to install FBScanner as proxy for the whole server and log all operations there, make sure to provide the same IO bandwidth and comparable CPU and RAM as on the production server. For high-load databases FBScanner's log can grow by several Gb per day. In such cases we recommend dedicated (remote server) installation of FBScanner.

FBScanner Service does not change anything in transferred traffic and works simply like a transparent proxy, so all applications will continue as normal. FBScanner Service consumes approximately 125-150 Mb of memory for monitoring 50-70 active clients, and it will decrease database performance by 5 to 15% when in use. Memory consumption depends on the client application design. Required IO bandwidth depends on the SQL traffic intensity.

During testing we have checked FBScanner at work in several real-world environments and applications where the database has 30-200 active connections and noticed that the performance degradation was minimal. In many cases users were unable to figure out that monitoring is active.

Segmentation of Traffic

The use of FBScanner on a non-standard port allows it to segment different traffic from different applications. For example, there are applications A and B. B is old and stable application which does not require any tuning or monitoring, and A is a new application. It’s easy to setup connection string for the new application A to use a non-standard port (e.g server/3070:Disk:\Path\database.gdb) and route all requests from the A application via FBScanner in order to monitor SQL queries and the application behaviour.


Using this approach you can easily recognize and solve issues within a production environment, whether the issue comes from the new application, or even if it comes from particular segment of your local network.

Pricing 

Don't hesitate to contact us to discuss licensing options!

Product Description Purchase now:
FBScanner,
1 server
Optimization tool. Performance optimization: SQL queries, plans, transactions for all versions of Firebird and InterBase €149 $199
PACKS
Optimization Pack Pack includes FBScanner (1 server), FBDataGuard (1 server), IBTM (1 server) and IBAnalyst (1 workplace). €299 $409
IBSurgeon Enterprise Pack Pack includes FirstAID with license to recover 25 Firebird or InterBase databases, IBBackupSurgeon (2 workplaces), IBAnalyst (2 workplaces), IBUndelete (2 workplaces), FBDataGuard (2 servers), IBTM (2 servers) and FBScanner (2 servers). €549 $749
ANNUAL SUBSCRIPTIONS
FBScanner subscription for ISV Allows unlimited FBScanner installations at customers' locations for ISV's business applications (ERP, Billing, CRM, etc) €990 $1299
ISV Subscription Includes all IBSurgeon products for 200 servers/workplaces plus 10 recovery or optimization incidents per year. €3240 $4500
UPGRADES FOR PRODUCTS
Upgrade FBScanner Upgrade any license of FBScanner (including FBScanner in Packs) to the newest version (1 server). €99 $129

 

Important notes about licensing

  • What is ISV company? Companies which produce Firebird- and InterBase-based business software (like ERP, CRM, billing, etc).
  • What is Unlimited? "Unlimited" means that there is no restriction on how many FBScanner instances will be installed, and also it means that installed instances will work permanently. "1 year subscription" means that subscriber can install, reinstall and upgrade FBScanner installations during the validity of subscription.
    It's important to add that though number of installations is unlimited, each FBScanner should be registered and activated.