Biblioteca

Como vizualizar Firebird Trace?

Please note: tracing is safe, it can be started on the production, it does not affect the performance in case of using the recommended configuration below.

Tracing for Firebird 3.0 and 4.0

Step 1 - Configuration file

Copy the following configuration into the file fbtrace30.conf, or download the ready file from here.
database
{
	enabled = true
	time_threshold = 200
	log_connections = true
	log_statement_finish = true
	log_errors = true
	log_initfini = false
	print_plan = true
	#explain_plan = false
	print_perf = true
	max_sql_length = 65000
	max_arg_length = 240
	max_arg_count = 50
}
Store the file in the folder without spaces, for example, C:\temp\fbtrace30.conf, or /opt/fbtrace30.conf

Step 2 - Test the configuration

Run fbtracemgr to check that configuration works:

Windows

Open command prompt (run cmd.exe), go to the folder with Firebird (it can be C:\Program Files\Firebird\Firebird_3_0\ or C:\HQbird\Firebird30) and run (use your actual SYSDBA password instead of masterkey)
fbtracemgr.exe -se service_mgr -user SYSDBA -pass masterkey -start -conf C:\temp\fbtrace30.conf

Linux

Run terminal, and then fbtracemgr from Firebird bin folder (/opt/firebird/bin or /usr/local/bin)
./fbtracemgr -se service_mgr -user SYSDBA -pass masterkey -start -conf /opt/fbtrace30.conf   
As a result, you will see the output in the command prompt, something like this
Trace session ID 2 started
2021-05-26T10:23:48.5690 (2996:00000000018E0040) ATTACH_DATABASE
        C:\HQBIRD\FIREBIRD30\SECURITY3.FDB (ATT_6189, SYSDBA:NONE, NONE, )
Stop the tracing with Ctrl-C (and then Enter, if necessary).

Step 3 - Collect log

If you don't see errors in the previous step, run tracing to collect the output to the log file, c:\temp\log1.log in the example:

Windows

fbtracemgr.exe -se service_mgr -user SYSDBA -pass masterkey -start -conf C:\temp\fbtrace30.conf > c:\temp\log1.log

Linux

./fbtracemgr -se service_mgr -user SYSDBA -pass masterkey -start -conf /opt/fbtrace30.conf >    log1.log  
Keep it running to catch the problematic SQL queries for a while (the minimum is 3-4 minutes, recommended is 1 hour), and then interrupt the fbtracemgr with Ctrl-C.

Step 4. Upload logs

Upload the resulted log file to the Trace Visualization on cc.ib-aid.com
Please note, if the size of the lo file is more than 100Mb, zip it (with standard Windows zip or with Linux zip), and upload a compressed log instead.

Tracing for Firebird 2.5

Step 1 - Configuration file

Copy the following configuration into the file fbtrace25.conf, or download the ready file from here.

	enabled true
	log_connections true
	log_statement_finish true
	log_errors true
	print_plan true
	print_perf true
	time_threshold 200
	max_sql_length 65000
	max_blr_length 500
	max_dyn_length 500
	max_arg_length 80
	max_arg_count 50


Store the file in the folder withpout spaces, for example, C:\temp\fbtrace25.conf, or /opt/fbtrace25.conf

Step 2 - Test configuration

Run fbtracemgr to check that configuration works:

Windows

Open command prompt (run cmd.exe), go to the Bin folder of the Firebird (it can be C:\Program Files\Firebird\Firebird_2_5\bin or C:\HQbird\Firebird25\bin) and run (use your actual SYSDBA password instead of masterkey)
fbtracemgr.exe -se service_mgr -user SYSDBA -pass masterkey -start -conf C:\temp\fbtrace30.conf
As a result, you will see the output in the command prompt, something like this
Trace session ID 2 started
2021-05-26T10:23:48.5690 (2996:00000000018E0040) ATTACH_DATABASE
        C:\HQBIRD\FIREBIRD30\SECURITY3.FDB (ATT_6189, SYSDBA:NONE, NONE, )

Linux

Run terminal, and then run fbtracemgr from Firebird bin folder (/opt/firebird/bin or /usr/local/bin)
./fbtracemgr -se service_mgr -user SYSDBA -pass masterkey -start -conf /opt/fbtrace25.conf   

Step 3 - Collect logs

If you don't see errors on the screen, run tracing to collect the output of the data to the file, c:\temp\log1.log in the example:

Windows

fbtracemgr.exe -se service_mgr -user SYSDBA -pass masterkey -start -conf C:\temp\fbtrace25.conf > c:\temp\log1.log

Linux

./fbtracemgr -se service_mgr -user SYSDBA -pass masterkey -start -conf /opt/fbtrace25.conf > log1.log  
Keep it running to catch the problematic SQL queries for a while (the minimum is 3-4 minutes, recommended is 1 hour), and then interrupt the fbtracemgr with Ctrl-C.

Step 4. Upload results

Upload the resulted log file to the Trace Visualization on cc.ib-aid.com Please note, if the size of the lo file is more than 100Mb, zip it (with standard Windows zip or with Linux gz), and upload compressed log instead. The size of the compressed log is limited to 500Mb, larger log files will be deleted.
You need to have an account to log in.

After login, click on "Upload " in the upper right corner of "Traces" widget: 

Select Upload trace

Then, select trace log file (max 100Mb)  on disk, and click Upload:

select tarce log to upload


Uploading can take 1-2 minutes, depending on your Internet connection upload speed and general connectivity.
Then you will see the uploaded file as being "Processing":



Processing usually takes 1-2 minutes. You need to use the button "Refresh" to see the status of the processing.
After processing is complete, you will see links to the performance summary report (column "Trace summary report", link "Html"), and to the graphical representation of tracing (column and link "Graphs"): 


If you click on "Html", the new page with trace summary report will appear.
The report contains lists of top queries, sorted by duration (i.e., the longest queries), frequency (the most frequent queries), time-summary (SQL queries, which took the majority of time), fetches (most-CPU consuming queries), reads (disk reads), and writes (disk writes). 
Also, there is a process summary, where you can see the applications consumed the majority of resources.



If you click on "Graphs", you will see the graphical representation of trace log, with top queries, grouped by every minute (by the finishing moment of queries execution).
If you click on the specific time moment, you will see top queries from trace log, their plan, parameters, statistics: