How can FBScanner help to identify and resolve Firebird or InterBase performance problem?
STEP 1: Install FBScanner, setup SQL traffic logging and then analyze log with FBScanner LogAnalyzer. "Server load" overview gives you the general picture of SQL queries performance:
STEP2: Select the time moment you need to investigate and open the list of SQL queries. Narrow your search if necessary and sort queries on EXECUTION_TIME in order to see the longest queries.
You can see SQL query text, parameters, count of fetched records, and also know who and when executed it: IP address, user name, start/end time, etc.
STEP 3: Open connection and transaction details (right click on query), and see complete SQL queries flow, transactions' parameters and connection's details:
Do you need more information to solve performance problem?
FBScanner 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.
this presentation to see how FBScanner
helped to migrate 75 Gb Firebird database with very complex business logic (500+ tables, 5000+ stored procedures and triggers, etc) from Firebird 1.5 to Firebird 2.5.
FBScanner can offer even more to Firebird/InterBase developers and administrators: it can be used to track and identify reasons of 10054 errors, it can help to manage SQL connections, and also it can track real-time connections, transactions and SQL execution.
FBScanner Viewer is a part of FBScanner which is responsible for real-time tracking of connection, queries and transactions:
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 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), InterBase (4.0, 5.x, 6.x, 7.0, 7.1, 7.5, 2007, 2009, XE, XE3).
It 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.
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 consists of two main parts: the service and the viewer. The FBScanner service works as a proxy of and examines all the traffic that takes place between the server instance and the client applications. The FBScanner service can work on the same computer as the database engine or on an alternative dedicated server. It analyzes all traffic between the application and the database and allows you to view and manage real-time database activity.
FBScanner V3.0 works on Windows only and requires the .NET 2.0 (or higher) Framework to be installed.
Issues that FBScanner can help to resolve
- Real-time monitoring of connections. FBScanner 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. 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.
- You can log the SQL queries. For debugging or for security reasons, FBScanner can log all the selected traffic to a special database for further analysis.
How it works
The FBScanner Service
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 Note: FBScanner works only with TCP connections, so clients using either local (XNET) and NetBeui (WNET) connection strings will bypass FBScanner.
If you choose the first installation option, all of the applications requests will pass through FBScanner Service.
FBScanner does not change anything in transferred traffic and works simply like a transparent proxy, so all applications will continue as normal. FBScanner consume approximately 25-50Mb of memory (monitoring 100-200 active clients) and will decrease database performance by 5 to 15% when in use.
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 wa minimal In most cases users were unaware of the monitoring.
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. A is old and stable application which does not require any tuning or monitoring, and B is a new application. It’s easy to setup connection string for the new B application to use a non-standard port (e.g server/3052:Disk:\Path\database.gdb) and route all requests from the B 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.
There are 3 types of licenses: Per server license, Unlimited FBScanner subscription and FBScanner Bundle.
In this case FBScanner is licensed on per server basis, i.e., each server requires a license to monitor it. FBScanner Viewer and FBScanner LogAnalyzer usage is unrestricted, so they can be used by any number of users to work with the FBScanner service and its log.
FBScanner 3 (1 Server License) - Buy now for EUR 199/USD 299
Unlimited FBScanner subscription (special offer for ISV!)
For companies which produce Firebird- and InterBase-based software and need to troubleshoot a lot of installations, IBSurgeon offers unlimited 1 year subscription for FBScanner for fixed price.
"Unlimited" means that there is no restrictions 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.
Also there is limitation that only customer's employees can work with FBScanner.
It's important to notice that though number of installations is unlimited, each FBScanner should be registered and activated.
Unlimited 1 year subscription for FBScanner - EUR 990/year.
FBScanner Bundle is the extension for Unlimited FBScanner Subscription, it's designed to bundle FBScanner with third-party software, in order to perform silent installation without activation.
Please send your enquiries regarding FBScanner Bundle to email@example.com.