FBScanner (Firebird Scanner) is a tool to solve all types of performance problems with Firebird.
FBScanner is one of the most efficient IBSurgeon tools: it tracks SQL traffic between server and clients. FBScanner features:
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.
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".
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".
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.
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.
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 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.
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.
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.
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.